استفاده از معیارها در Microsoft Excel

Microsoft Excel نه تنها یک ویرایشگر صفحه گسترده ، بلکه یک برنامه قدرتمند برای محاسبات مختلف است. آخر ، این مهم به لطف توابع داخلی ساخته شده است. به کمک برخی از توابع (اپراتورها) حتی می توانید شرایط محاسبه را تعیین کنید که به آنها معیار گفته می شود. بیایید با جزئیات بیشتری یاد بگیریم که چگونه می توانید هنگام کار در اکسل از آنها استفاده کنید.

معیارهای کاربرد

معیارها شرایطی است که تحت آن برنامه اقدامات خاصی را انجام می دهد. آنها در تعدادی از توابع داخلی استفاده می شوند. نام آنها اغلب حاوی عبارت است اگر. اول از همه به این گروه از اپراتورها لازم است که نسبت داده شود شمارش, COUNTIMO, خلاصه, SUMMESLIMN. علاوه بر اپراتورهای داخلی ، از معیارهای موجود در اکسل برای قالب بندی مشروط نیز استفاده می شود. استفاده از آنها را هنگام کار با ابزارهای مختلف این پردازنده جدول با جزئیات بیشتر در نظر بگیرید.

شمارش

وظیفه اصلی اپراتور شمارشمتعلق به یک گروه آماری ، شمارش اشغال شده توسط مقادیر مختلف سلول است که شرایط خاصی را برآورده می کند. نحو آن به شرح زیر است:

= COUNTIF (دامنه ؛ معیار)

همانطور که مشاهده می کنید ، این اپراتور دو آرگومان دارد. "محدوده" آدرس آرایه عناصر روی برگه را نشان می دهد که باید در آن شمارش شوند.

"ملاک" - این استدلالی است که شرایطی را تعیین می کند که دقیقاً سلول های منطقه مشخص شده باید درج شوند تا در شمارش قرار گیرند. به عنوان یک پارامتر می توان از عبارات عددی ، متن یا پیوندی به سلول که معیار در آن موجود است استفاده کرد. در این حالت ، برای نشان دادن ملاک ، می توانید از کاراکترهای زیر استفاده کنید: "<" (کمتر), ">" (بیشتر), "=" (برابر است), "" (مساوی نیست) به عنوان مثال ، اگر یک عبارت را مشخص کنید "<50"، سپس فقط عناصر مشخص شده توسط آرگومان هنگام محاسبه در نظر گرفته می شوند "محدوده"، که در آن مقادیر عددی کمتر از 50 است. استفاده از این علائم برای نشان دادن پارامترها برای سایر گزینه ها مرتبط خواهد بود ، که در این درس در زیر توضیح داده خواهد شد.

حال بیایید به یک مثال مشخص از نحوه عملکرد این اپراتور در عمل نگاه کنیم.

بنابراین ، یک جدول وجود دارد که درآمد پنج فروشگاه در هفته ارائه می شود. باید تعداد روزهای این دوره را دریابیم که در آن در فروشگاه 2 درآمد حاصل از فروش از 15000 روبل فراتر رفته است.

1. عنصر ورق را انتخاب کنید که در آن عملگر نتیجه محاسبه را به دست می آورد. پس از آن ، بر روی آیکون کلیک کنید "درج عملکرد".



2. شروع کردن جادوگران عملکرد. ما به بلوک حرکت می کنیم "آماری". در آنجا نام را پیدا کرده و برجسته می کنیم "COUNTIF". سپس بر روی دکمه کلیک کنید. "خوب".



3. پنجره آرگومان جمله بالا فعال می شود. در زمینه "محدوده" لازم است مساحت سلولها را نشان دهیم که محاسبه در آن انجام خواهد شد. در مورد ما باید محتوای خط را برجسته کنیم "فروشگاه 2"، که در آن مقادیر درآمد روز به روز واقع شده اند. مکان نما را در قسمت مشخص شده قرار می دهیم و با نگه داشتن دکمه سمت چپ ماوس ، آرایه مربوطه را در جدول انتخاب می کنیم. آدرس آرایه انتخاب شده در پنجره نمایش داده می شود.

   در قسمت بعدی "ملاک" فقط باید پارامتر انتخاب فوری را تنظیم کنید. در مورد ما ، فقط باید آن عناصر جدول را که در آن مقدار از 15000 تجاوز می کند ، محاسبه کنیم. بنابراین ، با استفاده از کیبورد ، عبارت را در قسمت مشخص شده قرار می دهیم ">15000".

   پس از تمام دستکاری های فوق ، روی دکمه کلیک کنید "خوب".



4. برنامه نتیجه را در عنصر برگه ای که قبل از فعال سازی انتخاب شده است ، شمارش و نمایش می دهد جادوگران عملکرد. همانطور که مشاهده می کنید در این حالت نتیجه برابر است با 5. این به این معنی است که در آرایه انتخابی در پنج سلول مقادیر بیش از 15000 وجود دارد.یعنی می توان نتیجه گرفت که در فروشگاه 2 در پنج روز از هفت مورد تجزیه و تحلیل ، درآمد بیش از 15000 روبل است.

درس: جادوگر ویژگی های اکسل

COUNTIMO

عملکرد بعدی که با معیارها کار کند ، است COUNTIMO. همچنین به گروه آماری اپراتورها تعلق دارد. وظیفه COUNTIMO شمارش سلول ها در یک مجموعه مشخص است که مجموعه ای از شرایط خاص را برآورده می کند. این واقعیت است که شما می توانید نه یک ، بلکه چندین پارامتر را مشخص کنید ، و این اپراتور را از مورد قبلی متمایز کنید. نحو به شرح زیر است:

= COUNTIME (condition_range1؛ condition1؛ condition_range2؛ condition2؛ ...)

"محدوده شرایط" با اولین استدلال بیانیه قبلی یکسان است. یعنی پیوندی به ناحیه ای است که در آن سلول ها شمارش می شوند که شرایط مشخص شده را برآورده می کنند. این اپراتور به شما امکان می دهد چندین منطقه را به طور همزمان مشخص کنید.

"شرط" معیاری را نشان می دهد که تعیین می کند کدام عناصر از مجموعه داده های مربوطه شمارش می شوند و کدام ها نیست. هر منطقه داده داده شده باید جداگانه مشخص شود ، حتی اگر منطبق باشد. ضروری است که تمام آرایه ها که به عنوان مناطق شرط استفاده می شوند دارای تعداد ردیف و ستون یکسان باشند.

برای تنظیم چندین پارامتر از همان ناحیه داده ، به عنوان مثال ، برای شمارش تعداد سلولهایی که مقادیر آنها بیشتر از یک عدد مشخص است ، اما کمتر از یک عدد دیگر است ، باید به عنوان یک آرگومان در نظر گرفته شود. "محدوده شرایط" چندین مرتبه همین آرایه را مشخص کنید. اما در همان زمان ، به عنوان استدلال مناسب "شرط" معیارهای مختلف باید نشان داده شود

با استفاده از نمونه ای از همان جدول با درآمد فروش هفتگی ، اجازه دهید نحوه عملکرد آن را ببینیم. باید تعداد روزهای هفته را دریابیم که درآمد همه فروشگاههای خرده فروشی مشخص به استانداردی که برای آنها تعیین شده رسیده است. استانداردهای درآمد به شرح زیر است:

• خرید 1 - 14،000 روبل؛
• فروشگاه 2 - 15،000 روبل؛
• خرید 3 - 24،000 روبل؛
• فروشگاه 4 - 11،000 روبل؛
• خرید 5 - 32،000 روبل.

1. برای انجام کار فوق ، عنصر صفحه کار را با مکان نما انتخاب کنید ، جایی که نتیجه پردازش داده ها نمایش داده می شود COUNTIMO. روی نماد کلیک کنید "درج عملکرد".



2. رفتن به جادوگر ویژگیدوباره به بلوک بروید "آماری". لیست باید نام را پیدا کند COUNTIMO و آن را انتخاب کنید. پس از انجام عمل مشخص شده ، باید دکمه را فشار دهید "خوب".



3. پس از اجرای الگوریتم فوق اقدامات ، پنجره argument باز می شود COUNTIMO.

   در زمینه "محدوده شرایط 1" آدرس خطی را که داده های مربوط به درآمد فروشگاه 1 برای هفته در آن قرار دارد وارد کنید. برای این کار مکان نما را در قسمت فیلد قرار داده و سطر مربوطه را در جدول انتخاب کنید. مختصات در پنجره نمایش داده می شوند.

   با توجه به اینکه برای فروشگاه 1 نرخ درآمد روزانه 14000 روبل است ، سپس در این زمینه قرار می گیرید "شرط 1" بیان را بنویسید ">14000".

   داخل مزارع "محدوده شرایط 2 (3،4،5)" مختصات خطوط با درآمد هفتگی فروشگاه 2 ، فروشگاه 3 ، فروشگاه 4 و فروشگاه 5 به ترتیب باید وارد شود .عمل براساس همان الگوریتم انجام می شود که برای اولین آرگومان این گروه انجام می شود.

   داخل مزارع "شرط 2", "شرط 3", "شرط 4" و "شرط 5" به همین ترتیب مقادیر را وارد می کنیم ">15000", ">24000", ">11000" و ">32000". همانطور که ممکن است حدس بزنید ، این مقادیر با بازه درآمدی که بیش از هنجار برای فروشگاه مربوطه است مطابقت دارد.

   پس از وارد کردن تمام داده های لازم (کل 10 فیلد) ، بر روی دکمه کلیک کنید "خوب".



4. برنامه نتیجه را روی صفحه حساب می کند و نمایش می دهد. همانطور که می بینید ، برابر با عدد 3 است.

حالا بیایید کار را تغییر دهیم. ما باید تعداد روزهایی را که فروشگاه 1 درآمد بیش از 14000 روبل دریافت کرده ، اما کمتر از 17،000 روبل محاسبه کنیم.

1. ما مکان نما را در عنصری قرار می دهیم که خروجی روی برگه نتایج شمارش تولید شود. روی نماد کلیک کنید "درج عملکرد" بیش از منطقه کاری ورق.



2. از آنجا که ما اخیراً فرمول را اعمال کردیم COUNTIMO، حالا دیگر لازم نیست به گروه بروید "آماری" جادوگران عملکرد. نام این اپراتور را می توان در این دسته یافت "10 مورد استفاده اخیر". آن را انتخاب کرده و بر روی دکمه کلیک کنید. "خوب".



3. پنجره استدلال اپراتور آشنا باز می شود. COUNTIMO. مکان نما را در قسمت میدان قرار دهید "محدوده شرایط 1" و با نگه داشتن دکمه سمت چپ ماوس ، سلولهای حاوی درآمد را تا روز فروشگاه 1 انتخاب کنید. آنها در این خط واقع شده اند ، که "فروشگاه 1". پس از آن مختصات منطقه مشخص شده در پنجره بازتاب می یابد.

   بعد ، مکان نما را در قسمت تنظیم کنید "شرط 1". در اینجا باید حد پایین مقادیر موجود در سلول ها را نشان دهیم که در محاسبه شرکت خواهند کرد. یک عبارت را مشخص کنید ">14000".

   در زمینه "محدوده شرایط 2" همان آدرس را وارد کنید به همان روشی که در فیلد وارد شده است "محدوده شرایط 1"، یعنی دوباره وارد مختصات سلولها با مقادیر درآمد برای اولین خروجی می شویم.

   در زمینه "شرط 2" حد بالایی انتخاب را مشخص کنید: "<17000".

   پس از تمام اقدامات مشخص شده ، بر روی دکمه کلیک کنید "خوب".



4. این برنامه نتیجه محاسبه را می دهد. همانطور که مشاهده می کنید ، مقدار نهایی 5 است. این بدان معنی است که در 5 روز از هفت مورد مطالعه ، درآمد در فروشگاه اول بین 14000 تا 17،000 روبل بود.

خلاصه

اپراتور دیگری که از معیارها استفاده می کند خلاصه. بر خلاف توابع قبلی ، به بلوک ریاضی اپراتورها تعلق دارد. وظیفه آن جمع بندی داده ها در سلول هایی است که با یک شرایط خاص مطابقت دارند. نحو به شرح زیر است:

= SUMMES (دامنه ؛ معیار ؛ [sum_range])

استدلال "محدوده" مساحت سلول ها را نشان می دهد که برای انطباق با شرایط بررسی می شوند. در واقع ، آن را با همان اصل استدلال تابع با همین نام تنظیم می شود شمارش.

"ملاک" - یک آرگومان موردنیاز است که انتخاب سلول ها را از منطقه داده مشخص شده برای افزودن مشخص می کند. اصول مشخص سازی همان استدلال مشابه اپراتورهای قبلی است که در بالا بررسی کردیم.

"محدوده جمع بندی" این یک استدلال اختیاری است. این قسمت خاص آرایه ای را نشان می دهد که جمع بندی در آن انجام خواهد شد. اگر آن را حذف کنید و آن را مشخص نکنید ، به طور پیش فرض در نظر گرفته می شود که برابر با مقدار آرگومان موردنیاز است "محدوده".

حال ، مثل همیشه ، کاربرد این اپراتور را در عمل در نظر بگیرید. براساس همین جدول ، ما با محاسبه میزان درآمد در فروشگاه 1 برای دوره شروع 11 مارس 2017 ، روبرو هستیم.

1. سلولی را انتخاب کنید که در آن نتیجه حاصل شود. روی نماد کلیک کنید "درج عملکرد".



2. رفتن به جادوگر ویژگی در بلوک "ریاضی" نام را پیدا و برجسته کنید خلاصه. روی دکمه کلیک کنید "خوب".



3. پنجره آرگومان عملکرد شروع می شود خلاصه. این سه زمینه مطابق با آرگومان های اپراتور مشخص شده است.

   در زمینه "محدوده" وارد منطقه از جدول که در آن مقادیر مورد بررسی برای مطابقت با شرایط قرار گرفته است. در مورد ما ، این یک رشته خرما خواهد بود. مکان نما را در این قسمت قرار داده و تمام سلول های حاوی خرما را انتخاب کنید.

   از آنجا که ما فقط باید درآمد حاصل از 11 مارس را در این زمینه اضافه کنیم "ملاک" مقدار را هدایت کنید ">10.03.2017".

   در زمینه "محدوده جمع بندی" شما باید منطقه ای را مشخص کنید که مقادیر آن با معیارهای مشخص شده خلاصه شود. در مورد ما ، اینها مقادیر درآمد خط هستند "Shop1". آرایه مربوط به عناصر ورق را انتخاب کنید.

   پس از وارد کردن تمام داده های مشخص شده ، روی دکمه کلیک کنید "خوب".



4. پس از آن ، نتیجه پردازش داده ها توسط عملکرد در عنصر قبلی که در صفحه کار مشخص شده است نمایش داده می شود. خلاصه. در مورد ما برابر است با 47921.53. این بدان معناست که از 11 مارس 2017 و تا پایان دوره تحلیل شده ، کل درآمد فروشگاه 1 به 47.921.53 روبل رسیده است.

SUMMESLIMN

ما مطالعه اپراتورهایی را که از معیارها استفاده می کنند با تمرکز روی توابع به پایان می رسانیم SUMMESLIMN. هدف از این عملکرد ریاضیات ، جمع بندی مقادیر مناطق نشان داده شده از جدول است که با توجه به چندین پارامتر انتخاب شده است. نحو اپراتور مشخص شده به شرح زیر است:

= SUMMER (sum_range؛ condition_range1؛ condition1؛ condition_range2؛ condition2؛ ...)

"محدوده جمع بندی" - این استدلال است ، که آدرس آرایه ای است که در آن سلول هایی که ملاک خاصی را برآورده می کنند اضافه می شوند.

"محدوده شرایط" - استدلالی که مجموعه ای از داده ها است ، برای انطباق با شرایط بررسی می شود.

"شرط" - استدلالی که معیار انتخاب را برای نمایندگی ارائه می دهد.

این عملکرد به معنای عملیاتی با چندین مجموعه از اپراتورهای مشابه به طور همزمان است.

بیایید ببینیم این اپراتور برای حل مشکلات در زمینه جدول درآمد فروش ما در خرده فروشی ها چگونه کاربرد دارد. ما نیاز به محاسبه درآمدی داریم که فروشگاه 1 برای دوره زمانی از 09 مارس تا 13 مارس 2017 به دست آورد. در این حالت ، هنگام جمع کردن درآمد ، فقط باید آن روزها را در نظر گرفت که در آن درآمد از 14000 روبل فراتر رفت.

1. باز هم ، سلول را برای نمایش کل انتخاب کنید و بر روی آیکون کلیک کنید "درج عملکرد".



2. در جادوگر عملکرداول از همه ، ما به بلوک حرکت می کنیم "ریاضی"، و در آنجا یک مورد را به نام انتخاب می کنیم SUMMESLIMN. روی دکمه کلیک کنید "خوب".



3. پنجره آرگومان های اپراتور راه اندازی شده است ، نام آن در بالا نشان داده شده است.

   مکان نما را در قسمت زمینه تنظیم کنید "محدوده جمع بندی". برخلاف استدلال های زیر ، این نوع از نوع به آرایه ای از مقادیر نیز اشاره می کند که داده های متناسب با معیارهای مشخص در آن خلاصه می شوند. سپس منطقه ردیف را انتخاب کنید "Shop1"، که در آن مقادیر درآمد خروجی مربوطه قرار دارد.

   بعد از نمایش آدرس در پنجره ، به قسمت فیلد بروید "محدوده شرایط 1". در اینجا ما نیاز به نمایش مختصات رشته با تاریخ ها داریم. دکمه سمت چپ ماوس را فشار داده و تمام تاریخ های موجود در جدول را انتخاب کنید.

   مکان نما را در قسمت میدان قرار دهید "شرط 1". شرط اول این است که ما داده ها را زودتر از 09 مارس خلاصه می کنیم. بنابراین مقدار را وارد کنید ">08.03.2017".

   ما به سمت استدلال حرکت می کنیم "محدوده شرایط 2". در اینجا شما باید همان مختصاتی را که در این زمینه ضبط شده است وارد کنید "محدوده شرایط 1". ما این کار را به همان روش انجام می دهیم ، یعنی با برجسته کردن خط با خرما.

   مکان نما را در قسمت زمینه تنظیم کنید "شرط 2". شرط دوم این است که روزهایی که درآمد حاصل از آن اضافه می شود باید حداکثر تا سیزدهم مارس باشد. بنابراین ، عبارت زیر را می نویسیم: "<14.03.2017".

   به میدان بروید "محدوده شرایط 2". در این حالت ، باید همان آرایه ای را انتخاب کنیم که آدرس آن به عنوان آرایه جمع بندی وارد شده باشد.

   پس از نمایش آدرس آرایه مشخص شده در پنجره ، به قسمت زمینه بروید "شرط 3". با توجه به اینکه فقط مقادیر ارزش آنها از 14000 روبل بیشتر خواهد بود ، در جمع بندی شرکت خواهیم کرد ، ما این ذات را وارد ذیل می کنیم: ">14000".

   پس از انجام آخرین عمل ، روی دکمه کلیک کنید "خوب".



4. برنامه نتیجه را بر روی یک صفحه نمایش می دهد. برابر است با 62491،38. این بدان معنی است که برای دوره 9 مارس تا 13 مارس 2017 ، مبلغ درآمد هنگام اضافه کردن آن برای روزهایی که در آن از 14000 روبل فراتر رود ، 62،491.38 روبل است.

قالب بندی مشروط

آخرین ابزاری که توصیف کردیم ، هنگام کار با معیارها ، قالب بندی مشروط است. نوع مشخصی از سلولهای قالب بندی را انجام می دهد که شرایط مشخص را برآورده می کند. به نمونه ای از کار با قالب بندی مشروط نگاهی بیندازید.

ما آن سلول ها را در جدول به رنگ آبی انتخاب می کنیم ، جایی که مقادیر روزانه بیش از 14000 روبل است.

1. ما مجموعه ای از عناصر را در جدول انتخاب می کنیم ، که نشان می دهد درآمد رسانه ها به طور روزانه نشان می دهد.



2. به برگه بروید "خانه". روی نماد کلیک کنید قالب بندی شرطیدر بلوک قرار داده شده است سبک ها روی نوار لیستی از اقدامات باز می شود. بر روی موقعیت کلیک کنید "ایجاد یک قانون ...".



3. پنجره تولید قانون قالب بندی فعال می شود. در قسمت انتخاب نوع قاعده ، نام را انتخاب کنید "فقط سلولهایی را شامل می شوند". در قسمت اول بلوک وضعیت ، از لیست گزینه های ممکن ، را انتخاب کنید "مقدار سلول". در قسمت بعدی موقعیت را انتخاب کنید بیشتر. در آخر - مقدار خود را مشخص کنید ، بیشتر از آن می خواهید عناصر جدول را قالب بندی کنید. ما آن را 14000 داریم. برای انتخاب نوع قالب بندی ، روی دکمه کلیک کنید "قالب ...".



4. پنجره قالب بندی فعال می شود. به برگه بروید "پر". از گزینه های پیشنهادی برای پر کردن رنگ ها ، با کلیک راست روی آن ، رنگ آبی را انتخاب کنید. پس از نمایش رنگ انتخابی در منطقه نمونهبر روی دکمه کلیک کنید "خوب".



5. پنجره تولید قانون قالب بندی به طور خودکار برمی گردد. در آن نیز در زمینه نمونه رنگ آبی نمایش داده می شود. در اینجا ما باید یک عمل واحد را انجام دهیم: بر روی دکمه کلیک کنید "خوب".



6. پس از آخرین عمل ، تمام سلولهای آرایه انتخاب شده ، که عددی بزرگتر از 14000 را شامل می شود ، به رنگ آبی پر می شوند.

اطلاعات بیشتر در مورد قابلیت های قالب بندی شرطی در مقاله ای جداگانه مورد بحث قرار گرفته است.

درس: قالب بندی مشروط در اکسل

همانطور که مشاهده می کنید ، با استفاده از ابزاری که در کار خود از معیارها استفاده می کنند ، اکسل می تواند مشکلات کاملاً متنوعی را حل کند. این می تواند به عنوان محاسبه مقادیر و مقادیر و قالب بندی و همچنین اجرای بسیاری از کارهای دیگر باشد. ابزار اصلی کار در این برنامه با معیارها ، یعنی با شرایط خاصی که این عمل فعال می شود ، مجموعه ای از توابع داخلی و همچنین قالب بندی مشروط است.