استفاده از معیارهای مایکروسافت اکسل

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

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

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

COUNTES

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

=СЧЁТЕСЛИ(диапазон;критерий)

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

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

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

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

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

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

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

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

درس: کارشناسی ارشد توابع در اکسل

COUNTERSILN

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

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)

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

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

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

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

• فروشگاه 1 - 14000 روبل؛
• فروشگاه 2 - 15000 روبل؛
• فروشگاه 3 - 24000 روبل؛
• فروشگاه 4 - 11000 روبل؛
• فروشگاه 5 - 32000 روبل.

1. برای انجام کار فوق، عنصر برگه با مکان نما، جایی که نتیجه پردازش داده COUNTLESSN نمایش داده خواهد شد. ما بر روی نماد "Insert function" کلیک میکنیم.
2. در حال تبدیل به کارشناسی ارشد توابع ، ما به بلوک "آماری" برگشتیم. در لیست شما باید ACCOUNTABLES را پیدا کنید و انتخاب آن را انتخاب کنید. پس از انجام اقدام مشخص شده، باید روی دکمه "OK" کلیک کنید.
3. پس از اجرای الگوریتم فوق عملیات، پنجره استدلال COUNTLESS است .

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

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

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

   در فیلدهای Condition2 ، Condition3 ، Condition4 و Condition5، مقدار "> 15000 "، "> 24000" ، "> 11000" و "> 32000" را وارد کنید . همانطور که ممکن است حدس بزنید، این مقادیر با بازده درآمد بیش از حد معمول برای فروشگاه مربوطه مطابقت دارد.

   پس از وارد كردن تمام اطلاعات لازم (10 كليد در كل)، بر روي دکمه "OK" كليك كنيد.

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

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

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

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

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

   در قسمت "Conditions2" ما حاشیه انتخاب بالا را نشان می دهد: "<17000" .

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

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

مبلغ

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

=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])

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

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

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

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

1. سلول را انتخاب کنید که در آن نتیجه نمایش داده خواهد شد. روی نماد «قرار دادن عملکرد» کلیک کنید.
2. تبدیل به کارشناسی ارشد توابع در بلوک "ریاضی"، ما پیدا و نام "SUMMESLI" را انتخاب می کنیم . روی دکمه OK کلیک کنید.
3. پنجره استدلال عملکرد SUMMESLI راه اندازی شده است. این سه فیلد مربوط به استدلال اپراتور مشخص شده است.

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

   از آنجایی که ما فقط باید درآمد حاصل از 11 مارس را اضافه کنیم، در قسمت «معیارها » مقدار «> 03/10/2017 » را وارد کنید.

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

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

4. پس از آن، نتیجه پردازش داده ها توسط عملکرد SUMMESLI در عنصر قبلا مشخص شده برگه نمایش داده می شود. در مورد ما، آن برابر با 47921.53 است. این بدان معنی است که با شروع از 2013/2013 و تا پایان دوره تحلیل شده، درآمد کل فروشگاه 1 به 47،921.53 روبل رسید.

SUMMESLIMN

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

=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)

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

"محدوده وضعیت" یک استدلال است که نشان دهنده آرایه ای از داده ها است که برای رعایت شرایط بررسی می شود؛

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

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

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

1. باز هم سلول را برای نمایش مجموع و سلول روی نماد "Insert Function" کلیک کنید.
2. در کارشناسی ارشد توابع ، ابتدا ما به بلوک "ریاضی" حرکت می کنیم و سپس آیتم "SUMMESLIMN" را انتخاب می کنیم . روی دکمه "OK" کلیک کنید.
3. پنجره استدلال اپراتور، که نام آن در بالا مشخص شد، راه اندازی می شود.

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

   پس از آدرس در پنجره نمایش داده می شود، به فیلد "Condition Range1" بروید . در اینجا ما باید مختصات رشته با تاریخ را نمایش دهیم. ما یک کلیپ از دکمه سمت چپ ماوس ایجاد می کنیم و تمام تاریخ ها را در جدول انتخاب می کنیم.

   مکان را در قسمت "Condition1" قرار دهید . اولین شرط این است که ما داده ها را قبل از 09 مارس جمع بندی کنیم. بنابراین، ما مقدار "> 03/08/2017" را وارد می کنیم .

   به بحث "شرط 2" بروید . در اینجا شما باید همان مختصات را وارد کنید که در فیلد "Range of Condition1" ضبط شده است. ما این کار را به همان شیوه انجام می دهیم، یعنی با برجسته کردن خط با تاریخ.

   مکان نما را در قسمت "Condition2" تنظیم کنید . دومین شرط این است که روزهایی که درآمد آنها اضافه می شود نباید بعد از 13 مارس باشد. بنابراین، عبارت زیر را می نویسیم: "<03/14/2017" .

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

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

   پس از آخرین اقدام، روی دکمه "OK" کلیک کنید.

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

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

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

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

1. مجموع آرایه ای از عناصر در جدول را انتخاب کنید که درآمد خرده فروشی ها را روزانه نشان می دهد.
2. به تب خانه بروید ما بر روی نماد «قالب بندی قراردادی» که در بلوک «Styles» در نوار قرار دارد کلیک میکنیم. یک لیست از اقدامات باز می شود. ما در موقعیت "ایجاد یک قانون ..." کلیک میکنیم.
3. پنجره تولید قوانین قالب بندی فعال شده است. در منطقه انتخاب نوع قانون، نام "Format Only Cells that contain" را انتخاب کنید . در قسمت اول بلوک وضعیت از لیست گزینه های ممکن، "مقدار سلول" را انتخاب کنید. در فیلد بعدی، موقعیت "بیشتر" را انتخاب کنید . در آخر، ما مقدار خود را نشان می دهیم، بزرگتر از آن است که عناصر جدول را فرمت کنیم. ما 14000. برای انتخاب نوع قالب بندی، دکمه "Format ..." را کلیک کنید.
4. پنجره قالب بندی فعال شده است. به برگه «پر کردن» بروید از گزینه های رنگ پر رنگ پیشنهاد شده، آبی را با کلیک بر روی آن با دکمه سمت چپ ماوس انتخاب کنید. پس از انتخاب رنگ در قسمت "نمونه" نمایش داده می شود، روی دکمه "OK" کلیک کنید.
5. این به طور خودکار به پنجره تولید قوانین قالب بندی بازگشت می کند. همچنین یک رنگ آبی را در منطقه نمونه نشان می دهد . در اینجا ما باید یک عمل واحد را انجام دهیم: روی دکمه "OK" کلیک کنید.
6. بعد از آخرین عمل، تمام سلولهای آرایه انتخاب شده که حاوی عدد بیشتر از 14000 است، با رنگ آبی پر می شود.

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

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

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

• ← وابستگی های گرافیکی در مایکروسافت اکسل
• ایجاد گرافیک شبکه در مایکروسافت اکسل →