توابع SUMIF، AVERAGEIF، SUMIFS، AVERAGEIFS، MAXIFS و MINIFS

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل که در فصل گذشته به بررسی توابع COUNTIF،COUNTIFS،COUNT،COUNTA و COUNTBLANK پرداختیم، در این فصل به توابع SUMIF، AVERAGEIF، SUMIFS، AVERAGEIFS،MAXIFS و MINIFS می پردازیم.

سؤال‌هایی که در این فصل پاسخ داده می‌شوند:

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

حال چگونه می‌توانم به سؤالات زیر پاسخ دهم؟

• مقدار کل فروش هر شخص چند دلار است؟
• چه تعداد از کالاها عودت داده شده‌اند؟
• کل فروش پس از سال 2005 چند دلار می‌باشد؟
• چه تعداد برق لب فروخته شده؟ فروش برق لب‌ها چه مقدار درآمد داشته است؟
• جمع مقادیری که توسط شخصی به‌غیراز Jen فروخته شده چند دلار است؟
• میانگین تعداد کالاهای هریک از معاملات که توسط فروشنده خاصی به فروش رسیده است چقدر است؟
• تعداد کل رژلب‌هایی که توسط فروشنده خاصی (Jen) فروخته شده چقدر است؟
• میانگین تعداد (به واحد) رژلب‌های هر معامله انجام شده توسط فروشنده‌ای خاص (Zaret) چقدر است؟
• در میان معاملات فروش 50 واحدی انجام شده، میانگین تعداد رژلب‌هایی هر فروش صورت‌گرفته توسط فروشنده‌ای خاص (Zaret) چقدر است؟
• در میان مبادلات فروش بیش از 100 دلاری، مبلغ کل رژلب‌های فروخته شده توسط فروشنده‌ای خاص چه مقدار است؟ در مبادلات کمتر از صد دلار همین مبلغ چه اندازه است؟
• آیا اکسل می‌تواند حداقل یا حداکثر مشروط را محاسبه کند.

چنانچه می‌خواهید تمام ارقام وارد شده در یک ستون (یا ردیف) که با معیارها یا شروط در ستون (یا ردیف) دیگری مطابقت دارند را جمع بزنید، تابعSUMIF این کار را برای شما انجام می‌دهد. دستور زبان تابع SUMIF عبارت است از: SUMIF(range,criterion,[sumrange])

Range: کل محدوده : سلول‌هایی است که می‌خواهید با معیار یا شرط مورد ارزشیابی قرار دهید.

Criterion: ملاک یا معیار : عدد، تاریخ و یا نوشته‌ای است که مشخص می‌کند یک سلول خاص به محدوده جمع اضافه می‌شود یا نه.

Sum range: محدوده جمع :محدوده سلول‌هایی است که باید جمع زده شوند. اگر حذف شود، کل محدوده در نظر گرفته می شود.

قوانین مربوط به معیارها و شرط‌هایی که می‌توانید با تابع SUMIF استفاده کنید دقیقاً شبیه به قوانین مرتبط به تابع COUNTIF هستند. برای اطلاعات بیشتر درباره تابع COUNTIF فصل 20: توابع COUNTIF،COUNTIFS،COUNT،COUNTA وCOUNTBLANK  را بررسی کنید.

تابعAVERAGEIF میانگین محدوده سلول‌هایی که مطابق معیار یا شرط موردنظر هستند را حساب می‌کند. تابع AVERAGEIF از دستور زبان AVERAGEIF(range,criterion,[average_range]) استفاده می‌کند.

مایکروسافت اکسل 2019 دارای سه تابع می‌باشد (که در اکسل 2007 معرفی شده بودند) که می‌توان با استفاده از آنها ردیف‌هایی که معیارهای چندگانه بر آنها اعمال می‌شود را مشخص نماید. توابع COUNTIFS (در فصل 20 درباره آن صحبت شد.) SUMIFS و AVERAGEIFS.

سایر توابعی که می‌توانید برای محاسبه‌هایی شامل معیارهای چندگانه از آنها استفاده کنید در فصل 50 به نام خلاصه کردن داده‌ها با توابع آماری بانک داده موردبحث قرار گرفته‌اند. از توابع آرایه (فصل 91 توابع و فرمول‌های آرایه‌ای) نیز می‌توان برای کارکردن بر محاسباتی که دارای معیارهای چندگانه هستند استفاده نمود.

دستور زبان تابع SUMIFS عبارت است از:

SUMIFS(sumrange,range1,criterion1,range2,criterion2,…,rangeN,criterionN)

تابع SUMIFS هریک از ارقام ورودی در محدوده sumrange را که criterion1 (بر اساسrange1)، criterion 2 (بر اساس range2)، و … criterionN (بر اساس rangeN) بر آنها وارد است را جمع می‌زند.  تابع AVERAGEIFS به شیوه مشابهی دارای دستور زبان:

AVERAGEIFS(sumrange,range1, criterion1,range2,criterion2,…,rangeN,criterionN) می‌باشد.

تابع AVERAGEIFS میانگین همه ارقام ورودی در محدوده sumrange را که معیارهایcriterion1 (بر اساسrange1)، criterion 2 (بر اساس range2)، و … criterionN (بر اساس rangeN) بر آنها وارد است را محاسبه می‌کند.

نرم‌افزارهای Office 365 و Excel2019 قابلیت دسترسی به توابع جدید MAXIFS وMINIFS را در اختیار شما قرار داده‌اند که شما را قادر می‌سازد تا ارقام حداقل و حداکثر مشروط را محاسبه کنید. دستور زبان تابع MAXIFS عبارت است از:

MAXIFS(maxrange,range1,criterion1,range2,criterion2,…,rangeN,criterion)

این تابع مقادیر دارای ارزش حداکثر در میان ارقام وارد شده‌ای که با معیار موردنظر همخوان هستند را به شما نشان می‌دهد. چنانچه تمایل داشته باشید می‌توانید تنها از یک معیار یا شرط استفاده کنید. تابع MINIFS نیز به شکل مشابهی عمل می‌کند.

پاسخ به سؤالات این فصل:

مقدار کل فروش هر شخص چند دلار است؟

محاسبات انجام شده این فصل در فایلی بنام Makeup2007.Xls می‌باشد. تصویر 1-21 گروهی از داده‌های مرتبط را نشان می‌دهد:

تصویر 1-21 داده‌هایی استفاده شده برای مثال‌های تابع SUMIF

 طبق معمول داده‌های ستون G الی L را با نام‌های مرتبط با سلول‌های G4:L4 نام‌گذاری می‌کنیم. مثلاً محدوده‌ای که Products نام‌گذاری شده مرتبط با محدوده J5:J1904 می‌باشد. برای محاسبه کردن کل مبلغ فروخته شده توسط هر فروشنده ( تصویر 2-21 را ببینید) به‌سادگی از سلول B5 فرمول =SUMIF(Name,A5,Dollars) را به محدوده B6:B13 کپی می‌کنیم. این فرمول هریک از ارقام وارد شده در ستوان Dollars که حاوی نام Emille در ستون نام‌ها می‌باشد را جمع زده و نشان می‌دهد که امیل 25258.87 دلار لوازم آرایشی فروخته است. البته فرمول: =SUMIF(Name,”Emilee”,Dollars) نیز همین مقدار را برمی‌گرداند.

تصویر 21-2 نتیجه محاسبات تابع SUMIF

 چه تعداد از کالاها عودت داده شده‌اند؟

در سلول B16( از فایلی بنام Makeup2007.xlsx) فرمول =SUMIF(Units,”<0″,Units)

همه ارقام کمتر از صفر از ستون Units (ستون K) را جمع می‌زند. نتیجه 922- است. قراردادن علامت منفی در ابتدای فرمول SUMIF نشان می‌دهد که 922 واحد از کالاها برگشت‌خورده اند. به‌خاطر داشته باشید هنگامی‌که آرگومان sum range از تابع SUMIF حذف شود، اکسل فرض می‌کند که  sum range  برابر است با range. در اینجا فرمول SUMIF(Units,”<0″)‌– نیز عدد 922 را به ما می‌دهد.

کل فروش پس از سال 2005 چند دلار می‌باشد؟

فرمول =SUMIF(Date,”>=1/1/2005″,Dollars) در سلول B17   تمامی ارقام ورودی در ستون Dollars( ستون L) را که حاوی تاریخ روز 1/1/2005 یا تاریخ بعد از آن در ستون Date است را جمع می‌زند. فرمول سلول B17 نشان می‌دهد که مقدار 157854.32 دلار لوازم آرایشی در تاریخ موردنظر سال 2005 و یا بعد از آن فروخته شده‌اند.

چه تعداد برق لب فروخته شده؟ فروش برق لب‌ها چه مقدار درآمد داشته است؟

فرمول SUMIF(Product,”lip gloss”,Units) در سلول B18 همه سلول‌های ستون Units را که حاوی متن Lip gloss( برق لب) است در ستونProducts (ستونJ) جمع می‌زند. می‌توانید ببینید که 16333 تعداد از برق لب‌ها فروخته شده‌اند. این میزان فروش خالص است، معامله‌هایی که واحدهای فروخته شده برق لب در آنها عودت داده شده‌اند به‌عنوان فروش منفی محاسبه شده‌اند.

فرمول =SUMIF(Product,”lip gloss”,Dollars) در سلول B19  به شکل مشابهی نشان می‌دهد که مبلغ خالص 49834.64دلار برق لب فروخته شده است. این محاسبات پرداخت‌های مرتبط با موارد عودتی را به شکل درآمد منفی محاسبه نموده است.

جمع مقادیری که توسط شخصی به‌غیراز ( Jen) فروخته شده چند دلار است؟

فرمول =SUMIF(Name,”<>Jen”,Dollars) مقدار در آمد کسب شده معاملات را که در ستونName با نام Jen مرتبط نیست را جمع می‌زند. به این صورت درمی‌یابیم فروشنده‌هایی به‌غیراز Jen 211786.51 دلار لوازم آرایشی فروخته‌اند.

میانگین تعداد کالاهای هریک از معاملات که توسط فروشنده خاصی (مثلاً Jen) به فروش رسیده است چقدر است؟

این مسئله را با تابع AVERAGEIF می‌توان حل کرد. برای انجام این کار در سلولB26  فرمول:

=AVERAGEIF(Name,”Jen”,Units) را وارد می‌کنیم تا میانگین تمامی مقادیر وارد شده در ستون Units را که حاوی نام Jen از ستون Name است را محاسبه نماید. میانگین تعداد معاملات انجام شده توسط Jen 43.548 واحد است. این نتیجه را می‌توان در سلول C26 با فرمول =SUMIF(Name,”Jen”,Units)/COUNTIF(Name,”Jen”) تأیید کرد.

تعداد کل رژلب‌هایی که توسط فروشنده خاصی(مثلاً Jen) فروخته شده چقدر است؟

این محاسبه حاوی دو معیار (Name=”Jen” and Product=”lipstick”)( نام شخص و نام محصول یعنی رژلب) بنابراین می‌بایست با فرمول =SUMIFS(Dollars,Name,”Jen”, Product,”lipstick”) مقداری که به دنبال آن هستید را در سلول B21  محاسبه نمایید. مبلغ کل تمامی معاملاتی که Jen در آن رژلب فروخته است 3953 دلار است.

میانگین تعداد (به واحد) رژلب‌های هر معامله انجام شده توسط فروشنده‌ای خاص(Zaret) چقدر است؟

این محاسبه به تابع AVERAGIFS نیازمند است. این مقدار را در سلول B22  با استفاده از فرمول: =AVERAGEIFS(Units,Name,”Zaret”,Product,”lipstick”) محاسبه کرده‌ایم. در معاملات فروشی که Zaret در آن رژ لب فروخته است، میانگین واحدهای فروخته شده 33 عدد است.

در میان معاملات فروش 50 واحدی انجام شده، میانگین تعداد رژلب‌هایی هر فروش صورت‌گرفته توسط فروشنده‌ای خاص (Zaret) چقدر است؟

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

=AVERAGEIF(Units,Name,”Zaret”, Product,”lipstick”,Units,”>=50″) را در سلول B23 وارد می‌کنیم. مشاهده می‌شود در تمامی مبادلات مالی که Zaret حداقل 50 رژلب فروخته است، میانگین مقدار مبادله 68 واحد می‌باشد.

در میان مبادلات فروش بیش از 100 دلاری، مبلغ کل رژلب‌های فروخته شده توسط فروشنده‌ای خاص چند دلار است؟ در مبادلات کمتر از صد دلار همین مبلغ چه اندازه است؟

ازآنجایی‌که معیارها و شروط ما Name=Jen, Product=lipstick و برخی عبارت‌ها در باره  مقدار دلار هر سفارش هستند، می‌بایست از تابع SUMIFS استفاده شود. ما در سلول B24 میزان کل مبادلاتی که در طی آن Jen رژلب فروخته که میزان آن حداقل 100 دلار بوده را با فرمول:

=SUMIFS(Dollars,Name,”Jen”,Product,”lipstick”,Dollars”>=100″) محاسبه کرده‌ایم. مشخص شده که Jen 3583 دلار رژلب در چنین معاملاتی فروخته است. در چنین مبادلاتی چنانچه فروش رژلب کمتر از صد دلار باشد، می‌بینید که در سلول B25( با فرمول =SUMIFS(Dollars,Name,”Jen”,Product,”lipstick”,Dollars,”<100″)) پاسخ با رقم 370 دلار نشان‌داده‌شده است. توجه کنید که 370 دلار به‌اضافه 3583 دلار برابر با کل درآمدهای کسب شده توسط Jen از فروش رژلب می‌باشد( که در سلول B21 محاسبه شده است).

آیا اکسل می‌تواند حداقل یا حداکثر مشروط (Conditional Maximus and minimus) را محاسبه کند.

نرم‌افزار Office365 و اکسل 2019  دارای توابع MAXIFS و MINIFS می‌باشند که می‌توان از آنها برای محاسبه مقادیر حداقل یا حداکثر مشروط استفاده کرد. فایلی بنام Maxifsminiifs.xlsx (تصویر 3-21 را ببینید) نحوه استفاده از این توابع را به شما نشان می‌دهد. این فایل حاوی نام شخص، نام تیم، پست بازیکن در تیم، و تعداد امتیازهای کسب شده توسط هر بازیکن در فصل‌های بازی سال 2016 الی 2017 لیگ ملی بسکتبال امریکا (NBA) می‌باشد. می‌خواهیم در هر ترکیب از نام تیم و پست بازیکن بیشترین و کمترین امتیازهای کسب شده را پیدا کنیم.

داده‌های ستون I-K را با نام‌های ردیف 1 نام‌گذاری می‌کنیم. در کاربرگی بنام Maxifsminifs.xlsx می‌توان با کپی‌کردن فرمول = MAXIFS(PTS,Tm,$A4,Pos,B$3) از سلول B4 به محدوده B4:F33 حداکثر امتیازهای کسب شده توسط هر تیم را به دست آوریم. با این فرمول درمی‌یابیم که مثلاً بیشترین امتیازها توسط بازیکن امتیازگیر تیم هیوستون یعنی جیمز هاردن به میزان 2085 کسب شده است. کاربرگی بنام Minifs استفاده از تابع MINIFS را به ما نشان می‌دهد. در فصل 91 از فرمول‌های آرایه‌ای برای محاسبه حداکثر و حداقل مشروط استفاده خواهیم کرد.

تصویر 3-21 مثالی از تابع MAXIFS

 مسئله‌های این فصل

در فایل Makeup2007.Xls

برای هر کالایی، تعداد واحدهای فروخته شده و رقم کل فروش به دلار را حساب کنید

کل درآمد کسب شده پیش‌ از تاریخ 10 دسامبر 2005 را محاسبه کنید.

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

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

چند واحد از کالاها توسط اشخاصی به جز Colleen فروخته شده است؟

چه تعداد واحد از کالاهای آرایشی از تاریخ 15 ژانویه سال 2004 تا 15 فوریه سال 2005 فروخته شده است؟

فایلی بنام NBA.xlsx حاوی اطلاعات زیر می‌باشد:

1. ستون‌های A و B نام هریک از  تیم‌های لیگ بسکتبال ملی آمریکا و شماره شناسه هر تیم را فهرست کرده‌اند. مثلاً تیمA تیم آتلانتا است و به همین روال.
2. ستون C نام تیم میزبان هر بازی را در خود فهرست کرده است.
3. ستون D نام تیم‌های مهمان هر بازی را در خود دارد
4. ستون E امتیازهای کسب شده توسط تیم میزبان را در خود دارد
5. ستون F امتیازهای تیم میزبان را در خود درج کرده است.

با استفاده از این داده‌ها برای هر تیم میانگین تعداد امتیازهای کسب شده در هر بازی و میانگین تعداد امتیازهای ازدست‌رفته هر تیم را محاسبه کنید.

فایلی بنام Toysrus.xlsx حاوی درآمدهای ناشی از فروش (به میلیون دلار) در طی هر فصل از سال‌های1997 الی 2001 و همچنین دو فصل از سال 2002 می‌باشد. از این داده‌ها برای محاسبه شاخص فصلی هر فصل از سال استفاده کنید. مثلاً اگر متوسط فروش در طول اولین فصل 80 درصد از متوسط کل فروش فصل باشد، اولین فصل دارای شاخص فصلی 0.8 می‌باشد.

فایلی بنام Sumifrows.xlsx حاوی داده‌های فروش چندین فصل زمستان، بهار، تابستان و پاییز می‌باشد. متوسط فروش در طول فصل‌های زمستان، بهار، تابستان و پاییز را محاسبه کنید.

بازهم با استفاده از فایلی بنام Makeup2007.xlsx (مربوط به سؤال‌های 16-10) محاسبه کنید که از مبادلات فروشی که حداقل در آن 50 رژلب فروخته شده چه مقدار درآمد کسب شده است؟

Cici در سال 2004 چند عدد برق لب فروخته است؟

میانگین تعداد واحد کرم پایه فروخته شده توسط Emily چند عدد است؟

میانگین مبلغ کرم پایه فروخته شده توسط Betsy بعد از پایان سال 2004 چند دلار است؟

در معامله‌هایی که در آن Ashley 40 واحد برق لب فروخته کل مبلغی که کسب شده چند دلار است؟

جدولی رسم کنید که حاوی فروش‌های محصولات توسط هریک از اشخاص باشد.

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

در فایلی بنام Numbers.xlsx تعداد کل رقم‌های بین 5 و 15 را کلاً محاسبه کنید.

در فایلی بنام Numbers.xlsx میانگین تمام عددهای 10 و 25 را محاسبه کنید.

فایلی بنام Problem19data.xlsx دمای هر ایالت آمریکا را در طول هر ماه  در طی سال‌های 1895 تا 2015 نشان می‌دهد. میانگین دمای هر ماه از سال در این دوره زمانی را محاسبه کنید.

فایلی بنام Problem20data.xlsx امتیازهایی را که با حمله و پاس دادن با آن برای بازیکن‌های لیگ ملی فوتبال آمریکا (NFL) کسب شده است را نشان می‌دهد. مشخص کنید میانگین امتیازهای کسب شده برای هر بازیکن هر تیم با حمله و پاس دادن آن چه مقدار است.

فایلی بنام Problem21data.xlsx حاوی آمار بازیکنان کوارتر بک لیگ ملی فوتبال آمریکای است. تعداد کامل پاس‌های گرفته شده و حمله‌های هریک از تیم‌های لیگ ملی فوتبال آمریکایی را مشخص نمایید. به‌خاطر داشته باشید که هر تیم ممکن است بیش از یک کوارتر بک داشته باشد.

با استفاده از داده‌های فایلی بنام Problem22data.xlsx تعداد کل واحدهای هر محصول فروخته شده توسط هریک از فروشنده‌ها را مشخص کنید.

فایلی بنام problem23_24.xlsx داده‌های 2000 شرکت بزرگ در دنیا را در اختیار ما قرار داده است. تعداد شرکت‌های بانکداری، میانگین درآمد شرکت‌های بانکداری فهرست شده و عدد کسری تمامی درآمد کسب شده توسط بانک‌ها با درآمدهای کمتر از 20 میلیارد را محاسبه کنید.

فایلی بنامProblem23_24data.xlsx داده‌هایی درباره 2000 شرکت بزرگ در دنیا در اختیار قرار داده است. درآمد و نام بزرگ‌ترین شرکت خرده‌فروشی فرانسوی را پیدا نمایید.