تحسب =AVERAGEIF(A2:A7,"North",C2:C7) متوسط المبيعات في C2:C7 في الصفوف التي يكون فيها العمود A يساوي North. تعمل مثل SUMIF، إلا أنها تقسم الإجمالي على عدد الصفوف المطابقة.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Average | |
| 2 | North | Apple | 120 | North | 90 | |
| 3 | South | Pear | 45 | North, Apple | 80 | |
| 4 | North | Pear | 110 | Over 50 | 120 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 195 | |||
| 7 | North | Apple | 40 |
تحسب F2 متوسط صفوف North الثلاثة، 120 و110 و40، وتعرض 90. وتحتاج F3 إلى شرطين، North وApple، لذا تستخدم AVERAGEIFS: (120 + 40) / 2 = 80. وليس في F4 نطاق متوسط منفصل، لذا تحسب متوسط المبيعات المطابقة نفسها.
صيغة AVERAGEIF وAVERAGEIFS
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
ترتيب الوسائط هو الفخ نفسه الذي في SUMIF وSUMIFS: تضع AVERAGEIF النطاق المراد حساب متوسطه في الآخر (وتسمح لك بحذفه)، وتضعه AVERAGEIFS أولًا. وتُكتب الشروط بالطريقة نفسها في الدالتين: "North" و">50" و"<>0" و"*apple*"، أو عامل مقارنة مربوط بخلية، ">"&F5. وتُتخطى الخلايا الفارغة والنصوص في نطاق المتوسط.
المتوسط مع تجاهل الأصفار
تحتسب AVERAGE الصفر قيمة، لذا يخفض طالبان غائبان بدرجة 0 متوسط الصف. أما الخلايا الفارغة فمختلفة: تتخطاها AVERAGE. وتتخطى =AVERAGEIF(B2:B7,"<>0") الأصفار أيضًا.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Method | Result | |
| 2 | Ana | 80 | AVERAGE | 48 | |
| 3 | Ben | 0 | Ignore zeros | 80 | |
| 4 | Cara | 90 | Count of zeros | 2 | |
| 5 | Dan | Count of numbers | 5 | ||
| 6 | Eva | 70 | |||
| 7 | Finn | 0 |
تقسم AVERAGE المجموع 240 على 5، لأن خلية Dan الفارغة مستبعدة لكن الصفرين محتسبان، وتعرض 48. وتقسم AVERAGEIF مع "<>0" المجموع 240 على 3 وتعرض 80. اكتب 60 في B5 فتتغير الاثنتان؛ واكتب 0 في B5 فلا تتغير إلا AVERAGE. ولتجاهل الأصفار والأرقام السالبة معًا، استخدم ">0".
لماذا تعيد AVERAGEIF الخطأ #DIV/0!
عندما لا يتطابق شيء، لا تجد AVERAGEIF ما تقسم عليه فتعيد #DIV/0!. ضعها داخل IFERROR لتعرض شرطة أو رسالة أو خلية فارغة بدلًا من ذلك.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | West average | #DIV/0! | |
| 3 | South | Pear | 45 | With IFERROR | No sales | |
| 4 | North | Pear | 110 | North max | 120 | |
| 5 | East | Apple | 55 | North min | 40 | |
| 6 | South | Apple | 195 | Apple max | 195 | |
| 7 | North | Apple | 40 |
#DIV/0! تقسم الصيغة على صفر أو على خلية فارغة.لا يوجد صف West، لذا تعرض F2 الخطأ #DIV/0! وتعرض F3 الرسالة. غيّر A3 إلى West فتعرض الاثنتان 45.
دالتا MAXIFS وMINIFS
تجد الخلايا من F4 إلى F6 في الورقة أعلاه أكبر قيمة وأصغر قيمة بشرط. وتستخدمان ترتيب AVERAGEIFS، أي النطاق المراد البحث فيه أولًا: تعيد =MAXIFS(C2:C7,A2:A7,"North") القيمة 120 وتعيد =MINIFS(C2:C7,A2:A7,"North") القيمة 40. وعلى عكس AVERAGEIF، تعيدان 0 عندما لا يتطابق شيء، لا خطأ.
تحتاج MAXIFS وMINIFS إلى Excel 2019 أو أحدث، أو Microsoft 365. وفي Excel 2016 وما قبله، تفعل =MAX(IF(A2:A7="North",C2:C7)) الشيء نفسه؛ اضغط Ctrl+Shift+Enter (Cmd+Shift+Enter على Mac) لإدخالها في تلك الإصدارات.
تمرين: المتوسط بشرطين
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Class | Score | Condition | Average | |
| 2 | Ana | A | 80 | Class A, no zeros | ||
| 3 | Ben | B | 75 | |||
| 4 | Cara | A | 0 | |||
| 5 | Dan | B | 60 | |||
| 6 | Eva | A | 90 | |||
| 7 | Finn | B | 0 | |||
| 8 | Gus | A | 70 |
دورك: احسب متوسط درجات الصف A، مع استبعاد الأصفار (الطلاب الغائبين). اكتب الصيغة في F2.
متوسط المتوسطات: خطأ شائع
حساب متوسط متوسطات مجموعات مختلفة الحجم يعطي متوسطًا إجماليًا خاطئًا. في North ثلاثة صفوف وفي South صفان، لذا يحظى كل صف من South بوزن أكبر مما يستحق في متوسط المتوسطين.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Formula | Result | |
| 2 | North | 120 | North | 90 | |
| 3 | South | 45 | South | 120 | |
| 4 | North | 110 | Average of the two | 105 | |
| 5 | South | 195 | All rows | 102 | |
| 6 | North | 40 |
تعرض E4 القيمة 105، وتعرض E5 المتوسط الحقيقي للصفوف الخمسة، 102. وعندما تختلف المجموعات في الحجم، احسب متوسط الصفوف نفسها بدالة AVERAGEIFS واحدة، أو اقسم SUMIFS على COUNTIFS بالشروط نفسها:
=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")
والدرجة المرجّحة بالساعات المعتمدة أو بالكمية حساب مختلف أيضًا: وهو المتوسط المرجح.
الأسئلة الشائعة
ما الفرق بين AVERAGEIF وAVERAGEIFS؟
تأخذ AVERAGEIF شرطًا واحدًا وتضع نطاق المتوسط في الآخر: =AVERAGEIF(A2:A7,"North",C2:C7). وتأخذ AVERAGEIFS عدة شروط وتضع نطاق المتوسط أولًا: =AVERAGEIFS(C2:C7,A2:A7,"North",B2:B7,"Apple").
كيف أحسب المتوسط في الاكسل مع تجاهل الأصفار؟
استخدم =AVERAGEIF(B2:B7,"<>0"). فهي تحسب متوسط الخلايا التي لا تساوي 0 فقط. أما الخلايا الفارغة فتتخطاها AVERAGE وAVERAGEIF أصلًا، لذا لا تحتاج إلى الشرط إلا الأصفار الحقيقية.
لماذا تعيد AVERAGEIF الخطأ #DIV/0!؟
لأنه لم تطابق أي خلية الشرط، فيقسم الاكسل مجموعًا قدره 0 على عدد قدره 0. ضعها داخل دالة تعرض شيئًا آخر: =IFERROR(AVERAGEIF(A2:A7,"West",C2:C7),"No data").
كيف أجد أكبر قيمة بشرط؟
استخدم MAXIFS، مع النطاق المراد البحث فيه أولًا: تعيد =MAXIFS(C2:C7,A2:A7,"North") أكبر قيمة لـ North. وتعمل MINIFS بالطريقة نفسها لأصغر قيمة. وتحتاج الدالتان إلى Excel 2019 أو أحدث، أو Microsoft 365.
