كيفية حساب متوسط نتائج 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))

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

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

إذا كنت غالبًا ما تحتاج إلى تلخيص وتجميع البيانات بناءً على القيم المكررة، توفر Kutools لـ Excel حلًا عمليًا من خلال أداة Advanced Combine Rows. يمكن لهذه الأداة دمج أو حساب القيم مثل المتوسط، المجموع، أو العدد للسجلات المتطابقة في خطوة واحدة، مما يجعلها مناسبة للغاية للمجموعات الكبيرة من البيانات أو التقارير الدورية.

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

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

• حدد العمود الذي يحتوي على قيم البحث وانقر على Primary Key.
• اختر العمود الذي يحتوي على القيم المستهدفة، ثم انقر على Calculate > Average.
• قم بتعيين قواعد الجمع أو الحساب للنطاقات الأخرى عند الحاجة - مثل دمج النصوص باستخدام الفواصل أو تطبيق الجمع، الحد الأقصى، أو الحد الأدنى.

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

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

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

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

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

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

التعليمات:

• حدد مجموعة البيانات بأكملها، بما في ذلك العناوين.
• انتقل إلى Insert > PivotTable > From Table or Range. اختر وضع الجدول المحوري في ورقة عمل جديدة أو موجودة حسب الحاجة.
• في لوحة PivotTable Fields، اسحب العمود الذي يحتوي على قيم البحث إلى منطقة الصفوف.
• اسحب العمود الذي تريد حساب المتوسط له إلى منطقة القيم. انقر على حقل القيمة، حدد Value Field Settings، ثم ضبط نوع الحساب على المتوسط.

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

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

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

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

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

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

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

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 أو انقر على Run. عند المطالبة، حدد عمود البحث، العمود الذي يجب حساب متوسط قيمه، وأدخل قيمة البحث. سيعرض الماكرو المتوسط المحسوب في مربع رسالة.

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

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

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

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

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