كيف تحسب متوسط نتائج دوال VLOOKUP المتعددة في Excel؟

عندما تحتاج إلى البحث عن عدة قيم مرتبطة بنفس عنصر البحث وحساب متوسطها، تُعد الصيغة المباشرة إحدى أسرع الطرق وأكثرها مرونة. وتتولى دالةAVERAGEIF أو صيغة الصفيف هذا المهمة بسهولة، دون الحاجة إلى إنشاء أعمدة إضافية.

أدخل الصيغة التالية في خلية فارغة (على سبيل المثال، F2):

=AVERAGEIF(A1:A24,E2,C1:C24)

بعد كتابة الصيغة، اضغط على مفتاحEnter. ستحصل فورًا على متوسط جميع القيم في العمود C التي تتطابق القيمة المقابلة لها في العمود A مع قيمة البحث الموجودة في الخلية E2. راجع الرسم التوضيحي أدناه:

شرح المعاملات ونصائح:

• A1:A24: النطاق الذي يحتوي على قيمة البحث الخاصة بك.
• E2: القيمة المحددة التي ترغب في البحث عنها.
• C1:C24: النطاق الذي تريد حساب متوسط القيم المطابقة فيه.

نهج بديل (للمستخدمين المعتادين على صيغ الصفائف):

أدخل الصيغة التالية في خلية فارغة واستخدمCtrl+Shift+Enterلتأكيدها:

=AVERAGE(IF(A1:A24=E2,C1:C24))

تعالج صيغ الصفائف كل مقارنة على حدة، وهو أمرٌ بالغ الفائدة في إصدارات Excel التي لا تدعم الصفائف الديناميكية. تأكد بعناية تامة من أن النطاقات متطابقة تمامًا في الحجم لتجنب وقوع الأخطاء.

سيناريوهات عملية وملاحظات:
- الأنسب لمجموعات البيانات غير المُرشَّحة التي تتطلّب بحثًا مباشرًا.
- إذا احتوى أيٌّ من النطاقات على خلايا فارغة، فسيتم تجاهلها تلقائيًّا عند حساب المتوسط.
- يُفضَّل استخدام مراجع الجداول لإنشاء صيغ أكثر متانة، خاصةً في الجداول الديناميكية أو عند إضافة بيانات جديدة.
- احترس من عدم تطابق نطاقات الخلايا عن طريق الخطأ، فهو سببٌ شائعٌ للمتوسطات الخاطئة أو الأخطاء.

إذا كنت غالبًا بحاجة إلى تلخيص ودمج البيانات استنادًا إلى القيم المكررة، فإنKutools لـ Excelيوفّر لك حلاً عمليًا من خلال أداةدمج متقدم للصفوف. فباستخدام هذه الأداة، يمكنك دمج القيم أو حسابها (مثل المتوسط أو المجموع أو العد) للسجلات المطابقة في خطوة واحدة—مما يجعلها مثالية لمجموعات البيانات الكبيرة والتقارير الدورية!

1. حدد نطاق جدول البيانات الخاص بك، بما في ذلك عمود البحث والقيم التي تريد حساب متوسطها. ثم انتقل إلىKutools > المحتوى > دمج متقدم للصفوف. راجع لقطة الشاشة:

2. في مربع الحوار الذي يظهر:

• حدد العمود الذي يحتوي على نطاق قيمة البحث الخاص بك، ثم انقر علىالمفتاح الأساسي.
• اختر العمود الذي يحتوي على القيم المستهدفة، ثم انقر علىحساب > المتوسط.
• عيّن قواعد الدمج أو الحساب للأعمدة الأخرى حسب الحاجة—مثل دمج النصوص باستخدام فواصل، أو تطبيق الجمع، أو تحديد القيمة العظمى أو الصغرى.

3. انقر علىموافقلتطبيق الإعدادات.

لقد تم الآن دمج الصفوف التي تحتوي على قيم بحث مكررة، مع حساب متوسط القيم في العمود المحدد تلقائيًا لكل قيمة بحث فريدة — مما يُعدّ حلًّا مثاليًّا لإعداد تقارير موجزة أو تكثيف البيانات بكفاءة.

نصيحة عملية: يقلل استخدام دمج الصفوف المتقدم من الحاجة إلى الحساب اليدوي ويخفّض احتمالية الوقوع في الأخطاء. وتُعد هذه الأداة الخيار الأمثل للمستخدمين الذين يتعاملون بانتظام مع بيانات تحتوي على قيم بحث متكررة ويرغبون في الحصول على ملخصات قابلة للتنفيذ بسرعة. تأكد دائمًا من تحديد الأعمدة الصحيحة قبل الدمج، خاصةً إذا طرأ تغيير على هيكل البيانات.

عرض توضيحي: حساب متوسط نتائج VLOOKUP المتعددة باستخدام Kutools لـ Excel

الجداول المحوريةتوفر نهجًا ديناميكيًا وبصريًا لتلخيص وتحليل البيانات. باستخدام جدول محوري، يمكنك تجميع الإدخالات تلقائيًا حسب قيمة البحث وعرض متوسط العمود المستهدف لكل مجموعة، مما يمنحك ملخصًا تفاعليًا يتجدد تلقائيًا مع أي تغيّر في البيانات.

السيناريوهات الأكثر فاعلية:يناسب هذا النهج بشكل مثالي عندما تحتاج إلى ملخص شامل لجميع نطاقات قيم البحث دفعة واحدة، بدلاً من التركيز على قيمة بحث واحدة فقط. كما أن الجداول المحورية ممتازة لاستكشاف البيانات بسرعة، وإنشاء التقارير، وعرض النتائج بتنسيق قابل للفرز والتوسيع.

التعليمات:

• حدد مجموعة البيانات بالكامل، بما في ذلك العناوين.
• انتقل إلىإدراج > جدول محوري > من جدول أو نطاق. واختر وضع الجدول المحوري في ورقة عمل جديدة أو موجودة حسب حاجتك.
• في لوحة حقول الجدول المحوري، اسحب العمود الذي يحتوي على نطاق قيمة البحث إلى منطقةالصفوف.
• اسحب العمود الذي تريد حساب متوسطه إلى منطقةالقيم. ثم انقر على حقل القيمة، وحددإعدادات الحقول لحقل القيمة، وعيّن نوع الحساب إلىالمتوسط.

ينتج عن ذلك جدول ملخص يعرض كل قيمة بحث فريدة مع متوسط البيانات المرتبطة بها. ويمكنك بسهولة تعديل التجميع أو التصفية أو التنقّل إلى التفاصيل متى احتجتَ لذلك.

المزايا:لا تحتاج إلى صيغ، وتدعم التحديثات الديناميكية، ومثالية لإعداد التقارير واستكشاف البيانات.

العيوب:تتطلب خطوات إضافية للتحديث بعد تغيير البيانات، وتُعدّ أقل ملاءمة لاستخراج قيمة واحدة مباشرةً في صيغ أخرى، كما أن الإعداد الأولي يحتاج إلى دراية أساسية بالجداول المحورية.

نصائح استكشاف الأخطاء وإصلاحها:إذا ظهرت القيم كأعداد أو مجاميع بدلًا من المتوسطات، فتحقق من إعداد حساب الحقل. وللحصول على أفضل النتائج، تأكد من أن الأعمدة تحتوي على عناوين واضحة، وقم بتوضيح أي أسماء أعمدة مكررة قبل إنشاء جدولك المحوري.

يتيح استخدام ماكرو VBA للمستخدمين المتقدمين، والذين يديرون بيانات يتم تحديثها بانتظام، أتمتة عملية حساب المتوسط لجميع الإدخالات المطابقة لقيمة بحث معيّنة. تعتمد هذه الطريقة على تكرار البيانات للعثور على كل تطابق وحساب المتوسط، مما يجعلها مثالية لمجموعات البيانات الكبيرة أو عند الحاجة إلى سير عمل قابل للتكرار.

السيناريوهات القابلة للتطبيق والملاحظات:يُعدّ VBA خيارًا مثاليًا عندما تحتاج إلى حساب المتوسط بشكل متكرر، أو ترغب في أتمتة إعداد التقارير، أو تتطلّب نهجًا مرنًا يمكن تكييفه مع تخطيطات بيانات غير تقليدية. وتؤدّي ماكروات VBA أداءً أفضل عندما تكون مرتاحًا لتمكين الماكروات في مصنفك وتحتاج إلى مخرجات مخصصة.

1. انتقل إلى تبويبالمطور، واخترVisual Basic أو اضغط على مفاتيحAlt + F11 لفتح محرر VBA، ثم انقر علىإدراج > وحدة نمطية. انسخ والصق الكود أدناه في الوحدة النمطية الجديدة:

Sub AverageVlookupMatches()
    Dim lookupCol As Range
    Dim avgCol As Range
    Dim lookupValue As Variant
    Dim total As Double
    Dim count As Long
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set lookupCol = Application.InputBox("Select the lookup column", xTitleId, Selection.Address, Type:=8)
    Set avgCol = Application.InputBox("Select the column to average", xTitleId, , Type:=8)
    lookupValue = Application.InputBox("Enter lookup value", xTitleId, , Type:=2)
    
    Application.ScreenUpdating = False
    total = 0
    count = 0
    
    For i = 1 To lookupCol.Rows.Count
        If lookupCol.Cells(i, 1).Value = lookupValue Then
            If IsNumeric(avgCol.Cells(i, 1).Value) Then
                total = total + avgCol.Cells(i, 1).Value
                count = count + 1
            End If
        End If
    Next i
    
    If count > 0 Then
        MsgBox "Average of all matches: " & total / count, vbInformation, "Result"
    Else
        MsgBox "No matches found.", vbExclamation, "Result"
    End If
    
    Application.ScreenUpdating = True
End Sub

2. بعد لصق الكود، أغلق محرر VBA. ولتشغيل الماكرو، عُد إلى Excel ثم اضغط مفتاحF5 أو انقرتشغيل. عند ظهور المطالبة، اختر عمود البحث وعمود القيم التي تريد حساب متوسطها، ثم أدخل قيمة البحث. وسيعرض الماكرو المتوسط المحسوب في مربع رسالة.

نصائح عملية واحتياطات:تأكد من أن عمودَي البحث والقيم يحتويان على نفس عدد الصفوف، ولا توجد أي صفوف فارغة داخل النطاقات المحددة. سيتم تجاهل الإدخالات التي تحتوي على قيم غير رقمية في عمود الهدف. ولأفضل أتمتة ممكنة، عدّل النطاقات المسماة أو منطق الماكرو حسب الحاجة ليتناسب تمامًا مع تنسيق ورقة العمل الخاصة بك.

استكشاف الأخطاء وإصلاحها:إذا ظهرت لك رسالة «لم يتم العثور على تطابقات»، فتحقق من وجود مسافات زائدة في البداية أو النهاية، أو عدم اتساق في نوع البيانات بعمود البحث. ولا تنسَ التأكد من تمكين الماكروات للتنفيذ.

حساب متوسط/معدل النمو السنوي المركب في Excel

حساب المتوسط المتحرك/المتدحرج في Excel

حساب المتوسط لكل يوم/شهر/ربع ساعة/ساعة باستخدام جدول بيانات محوري في Excel