كيف يمكن حساب المتوسط في عمود بناءً على معايير محددة في عمود آخر في Excel؟
في العديد من سيناريوهات Excel العملية، يُعد حساب متوسط القيم في عمودٍ ما—المجمَّعة أو المُرشَّحة وفقًا للإدخالات المطابقة في عمود آخر—أمرًا ضروريًّا في أغلب الأحيان. على سبيل المثال، قد ترغب في معرفة متوسط المبيعات لكل مندوب مبيعات أو لكل منطقة، كما يظهر في لقطة الشاشة أدناه. يُستخدم هذا النوع من الحسابات بشكل متكرر في إعداد التقارير الموجزة، وتحليل الأداء، ومراجعات البيانات. وفي هذه المقالة، سنعرض لك عدة طرق فعّالة لتحقيق هذه النتيجة، لتتمكّن من اختيار الأسلوب الأنسب لاحتياجاتك ومستوى خبرتك.
حساب المتوسط في عمود استنادًا إلى قيمة متطابقة في عمود آخر باستخدام الصيغ
حساب المتوسط في عمود استنادًا إلى قيمة متطابقة في عمود آخر باستخدام Kutools لـ Excel
حساب المتوسط حسب المجموعة باستخدام جدول بيانات محوري
أتمتة حساب المتوسط المجمّع باستخدام ماكرو VBA
حساب المتوسط في عمود استنادًا إلى قيمة متطابقة في عمود آخر باستخدام الصيغ
تُعد إحدى أكثر الطرق مباشرة لحساب المتوسط لمجموعة بيانات في Excel بناءً على عمود آخر هي استخدام الصيغ الشرطية مثل AVERAGEIF أو AVERAGEIFS. ويُعد هذا النهج مثاليًا عندما تسعى للحصول على نتائج دقيقة وفق معايير محددة، كمتوسط المبيعات لمدينة معينة أو مندوب مبيعات واحد.
1. حدد خلية فارغة لعرض النتيجة فيها، ثم أدخل الصيغة التالية واضغطEnter:
=AVERAGEIF(B2:B13,E2,C2:C13)
شرح المعلمات:في الصيغة أعلاه، يمثلB2:B13 النطاق الذي يحتوي على المعايير المراد التحقق منها (مثل المدينة أو مندوب المبيعات)، وتشيرE2إلى القيمة المحددة التي تريد المقارنة بها (مثل «أونتون»)، بينما يمثلC2:C13 النطاق الذي يحتوي على القيم الرقمية التي تريد حساب متوسطها.
بعد الضغط على Enter، ستحصل فورًا على متوسط المجموعة المحددة في الخلية E2 (مثل متوسط مبيعات «أونتون»).
إذا كنت بحاجة إلى حساب المتوسط لكل قيمة فريدة في عمود المعايير، فما عليك سوى تعديل القيمة في خلية المعيار (E2) وفقًا لذلك، أو نسخ الصيغة لأسفل إذا كانت لديك قائمة بالقيم الفريدة.
نصيحة عملية:في مجموعات البيانات الكبيرة أو عند وجود عدد كبير من القيم الفريدة، يمكن أن يُسهم دمج هذه الصيغة مع قائمة بالقيم الفريدة (باستخدام أدوات مثل «إزالة التكرارات» أو دالة)UNIQUEفي Excel ضمن Office 365 وExcel 2021، يمكنك تسريع حساب جميع المتوسطات المجمّعة دفعة واحدة. تأكد مرتين من أن نطاقات صيغتك تشمل جميع البيانات المطلوبة وتظل متوافقة عند نسخ الصيغة.
الأخطاء الشائعة واستكشاف الأخطاء وإصلاحها:
- إذا ظهرت لك رسالة الخطأ #DIV/0!، فتأكد من أن قيمة المعيار الخاصة بك موجودة فعليًّا ضمن نطاق التحديد.
- تأكد من أن نطاق القيم الرقمية يحتوي فقط على أرقام صالحة، لأن الخلايا النصية أو الفارغة قد تؤثر في الحساب.
حساب المتوسط في عمود استنادًا إلى قيمة متطابقة في عمود آخر باستخدام Kutools لـ Excel
إذا كنت ترغب في حساب المتوسط تلقائيًا لجميع القيم الفريدة في عمود واحد دون الحاجة إلى إدخال صيغ مرارًا أو تصفية يدويًا، فإن Kutools لـ Excel يوفر طريقة مبسّطة لأداء هذه المهمة. ويكون هذا مفيدًا بشكل خاص عند التعامل مع قوائم كبيرة أو مجموعات بيانات معقدة.
1. حدد النطاق الكامل للبيانات الذي يشمل عمود المجموعة والعمود الرقمي الذي تريد حساب متوسطه، ثم انتقل إلىKutools > دمج وتقسيم > دمج متقدم للصفوف.
2. في مربع الحواردمج الصفوف استنادًا إلى العمود، اتبع الخطوات التالية:
- حدد العمود الذي تريد التجميع حسبه (مثل المدينة أو اسم مندوب المبيعات)، ثم انقر على زرالمفتاح الأساسيلتحديده كحقل تجميع.
- حدد العمود الرقمي الذي ترغب في حساب متوسطه، ثم انقر علىحساب > المتوسط.
تلميح:بالنسبة لأي أعمدة أخرى (مثل التواريخ)، يمكنك تحديد طريقة دمج قيمها (على سبيل المثال، ربطها بفاصلة).[[TN_52_END]] - انقر علىموافقلمعالجة العملية.
سيقوم Kutools فورًا بتجميع البيانات بناءً على المفتاح الذي حددته، ويُرجع المتوسط لكل مجموعة في العمود الرقمي.
يُعد معالجة الدُفعات في Kutools الحل المثالي للمستخدمين الذين يحللون بانتظام إحصائيات مجمَّعة، مثل التقارير الشهرية أو الملخصات القِسْمية أو الحسابات متعددة المجموعات الأخرى. وبصفته إضافةً، لا يغيّر Kutools هيكل بياناتك الأصلي، وتتيح لك وظيفة المعاينة مراجعة التجميعات قبل تطبيق أي تغييرات.
ملاحظات ونصائح:
- تأكد من خلو نطاق التحديد من أي صفوف فارغة قبل تشغيل الأداة.
- إذا كنت بحاجة إلى نقل المتوسطات المجمَّعة إلى مكان آخر، فاستخدم النسخ واللصق لنقل النتائج بعد إتمام الحساب.
- في مجموعات البيانات الكبيرة جدًّا، تأكد من تحديد أعمدة التجميع والقيم الرقمية بشكلٍ دقيق لتجنب أي التباس.
Kutools لـ Excel- عزِّز Excel بقوة أكثر من 300 أداة أساسية، لتجعل عملك أسرع وأسهل، واستفد من ميزات الذكاء الاصطناعي لمعالجة البيانات بشكل أكثر ذكاءً وزيادة الإنتاجية.احصل عليه الآن
حساب المتوسط حسب المجموعة باستخدام جدول بيانات محوري
تقدم جدول بيانات محوري طريقة قوية ومدمجة لتلخيص البيانات وتجميعها وتحليلها—بما في ذلك حساب المتوسطات حسب المجموعة—دون الحاجة إلى صيغ أو إضافات خارجية. تكون هذه التقنية مثالية عندما تريد عرضًا تفاعليًا للمتوسطات والإجماليات حسب فئات مختلفة، وهي مناسبة لكل من مجموعات البيانات الصغيرة والكبيرة جدًّا.
كيفية تهيئة جدول بيانات محوري لحساب المتوسطات حسب المجموعة:
- حدد أي خلية ضمن مجموعة البيانات الخاصة بك، ثم انتقل إلىإدراج > جدول محوري. في مربع الحوار، اختر المكان الذي ترغب في ظهور الجدول المحوري فيه (ورقة جديدة أو ورقة عمل موجودة)، ثم انقر علىموافق.
- في جزء حقول الجدول المحوري، اسحب العمود الذي تريد التجميع حسبه إلى منطقةالصفوف(مثل «المدينة» أو «مندوب المبيعات»).
- اسحب العمود الرقمي الذي تريد حساب متوسطه (مثل «المبيعات») إلى منطقةالقيم. قد يحسب Excel المجموع تلقائيًا؛ ولتغيير ذلك، انقر على حقل القيمة، ثم اخترإعدادات الحقل إعدادات الحقول، وحددالمتوسط.
سيعرض جدول بيانات محوري الخاص بك فورًا القيمة المتوسطة لكل مجموعة. يمكنك تصفية التقرير أو فرزه أو تنسيقه بسرعة حسب الحاجة. هذه الطريقة سهلة الاستخدام وتتجنب خطر حدوث أخطاء في الصيغ.
المزايا:تفاعلية، وتتعامل بكفاءة مع أحجام البيانات الكبيرة، وتلخّص إحصائيات متعددة في آنٍ واحد.
العيوب:تُعرض النتيجة بتنسيق جدول بيانات محوري، وليس كقائمة بسيطة؛ وقد تتطلب تحديثًا دوريًّا إذا تغيّرت البيانات الأصلية.
تلميح:يؤدي النقر المزدوج على أي خلية ملخّصة في جدول بيانات محوري إلى فتح ورقة عمل جديدة تحتوي على البيانات الأساسية لتلك المجموعة، مما يسهّل مراجعة التفاصيل أو استكشاف التناقضات وإصلاحها.
المشكلات الشائعة:
- إذا لم تتمكن من الحصول على المتوسط، فتأكد من ضبط حقل القيمة على «المتوسط» في إعدادات الحقول.
- تحقق من البيانات الأصلية بحثًا عن أي صفوف فارغة أو أعمدة إضافية قد تؤدي إلى مشكلات في تخطيط الجدول المحوري.
أتمتة حساب المتوسط المجمّع باستخدام ماكرو VBA
بالنسبة للمستخدمين الذين يحتاجون بشكل متكرر إلى حساب المتوسطات للعديد من المجموعات أو يرغبون في إنشاء ملخص تلقائيًا، يمكن أن يؤدي كتابة ماكرو VBA إلى توفير جهد يدوي كبير. يكون VBA مفيدًا بشكل خاص عندما تتوقع أن يظل هيكل بياناتك ثابتًا أو عندما تريد إنشاء تقارير ملخصة متكررة بنقرة زر واحدة.
قبل أن تبدأ، تأكد من حفظ ملفك وتمكين وحدات الماكرو. إليك كيفية البدء:
1. انقر فوقأدوات المطور > Visual Basic لفتح محرر VBA. ثم في المحرر، انقر فوقإدراج > وحدة نمطيةلإنشاء وحدة كود جديدة. الصق الكود أدناه في الوحدة النمطية:
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2. بعد إدراج الكود، أغلق محرر VBA. عُد إلى Excel، واضغط علىAlt+F8، ثم اخترGroupAverageSummary من القائمة، وانقر فوقتشغيل. سيطلب منك الماكرو تحديد عمود المجموعة (المعايير) وعمود القيم (الرقمي). وبمجرد إتمام التحديدات، سيولّد تلقائيًا ورقة عمل جديدة باسم «Group Average Summary» تعرض كل مجموعة فريدة ومتوسط قيمها المقابل.
ملاحظات حول المعاملات والعمليات:
- تأكد من أن عمودَي التجميع والقيم متساويان في الطول ويتضمنان بيانات صالحة (أي تجنّب التحديد الجزئي).
- يمكنك تعديل هذا الماكرو لإنشاء تجميعات أكثر تقدمًا أو إحصائيات موجزة إضافية وفقًا لاحتياجاتك.
- إذا كانت ورقتك تحتوي بالفعل على ملخص «متوسط المجموعة» باسم ورقة العمل، فسيُنشئ الماكرو ورقة عمل جديدة باسم الورقة الافتراضي.
استكشاف الأخطاء وإصلاحها:
- إذا ظهرت لك رسالة مثل «فهرس خارج النطاق» أو ما شابهها، فتأكد من أن نطاقات التحديد الخاصة بك متوافقة بشكل صحيح وتقع ضمن نفس ورقة العمل.
- للحصول على أفضل النتائج، تأكد من أن عمود القيم يحتوي على بيانات رقمية فقط—فالماكرو سيتجاهل الخلايا النصية أو الفارغة ضمن النطاق الرقمي.
يُعد استخدام هذا الماكرو مثاليًا للمعالجة الدفعية، أو التقارير الآلية، أو الحالات التي تحتاج فيها غالبًا إلى تلخيص مجموعات بيانات جديدة أو مُحدَّثة.
عرض توضيحي: احسب المتوسط في عمود استنادًا إلى القيمة نفسها في عمود آخر باستخدام Kutools لـ Excel
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل