كيفية حساب متوسط الخلايا من أوراق عمل مختلفة في Excel؟

إذا كنت بحاجة إلى حساب متوسط نفس النطاق عبر عدة أوراق عمل - على سبيل المثال، لإيجاد متوسط المبيعات في النطاق A1:A10 عبر الأوراق المسماة Sheet1 إلى Sheet5 - يوفر Excel حلًا مباشرًا يعتمد على الصيغة. هذه الطريقة تعمل بشكل أفضل عندما تكون جميع الأوراق لديها نفس الهيكل وأسماء متسقة.

الخطوات:

حدد خلية فارغة حيث تريد النتيجة (على سبيل المثال، الخلية C3)، وأدخل الصيغة التالية:

=AVERAGE(Sheet1:Sheet5!A1:A10)

بعد الضغط على Enter، سيعيد Excel قيمة المتوسط للنطاق المحدد عبر جميع الأوراق من Sheet1 إلى Sheet5.

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

=AVERAGE(A1:A5, Sheet2!A3:A6, Sheet3!A7:A9, Sheet4!A2:A10, Sheet5!A4:A7)

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

Kutools لـ Excel يعزز قدرتك على استخراج ودمج القيم من نفس الخلية أو النطاق عبر أوراق عمل متعددة باستخدام ميزة زيادة تلقائية لمراجع ورقة العمل. هذا مفيد بشكل خاص عند التعامل مع عدد كبير من الأوراق ذات التصميم الموحد.

خطوات الاستخدام:

1. افتح ورقة عمل جديدة (مثل ورقة ملخص)، ثم حدد الخلية التي تريد حساب المتوسط فيها - على سبيل المثال، D7.

2. اذهب إلى Kutools > More (ضمن Formula المجموعة) > زيادة تلقائية لمراجع ورقة العمل.

3. في مربع الحوار:
  - اختر ترتيب التعبئة من Fill order القائمة المنسدلة (على سبيل المثال، تعبئة حسب العمود، ثم حسب الصف).
  - في قائمة ورقة العمل, تحقق من الأوراق التي تحتوي على الخلية التي تريد حساب المتوسط لها.
  - انقر Fill Range, ثم أغلق مربع الحوار.

4. سيتم سرد قيم الخلايا المختارة في النطاق (على سبيل المثال، D7:D11). ثم أدخل الصيغة التالية في خلية فارغة أخرى لحساب المتوسط:

=AVERAGE(D7:D11)

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

في بعض الحالات، قد تحتاج إلى حساب متوسط عدة خلايا مقابلة عبر أوراق عمل متعددة في وقت واحد - على سبيل المثال، تلخيص النتائج لـ A1 وB1 وC1 من كل ورقة عمل. يمكن أن يصبح هذا الأمر مرهقًا باستخدام الصيغ القياسية، لكن أداة الجمع (الأوراق والمصنفات) في Kutools لـ Excel تبسط هذه العملية بشكل كبير.

كيفية استخدام هذه الوظيفة:

1. انقر Kutools Plus > Combine لفتح معالج الجمع للأوراق.

2. في المعالج (الخطوة 1 من 3):
تحقق تجميع وحساب البيانات من عدة مصنفات إلى ورقة عمل واحدة, ثم انقر التالي لمتابعة.

3. في الخطوة 2 من 3:
  - حدد الأوراق التي تريد تضمينها ضمن قائمة ورقة العمل.
  - استخدم زر تصفح لزر لتحديد النطاق لحساب المتوسط.
  - انقر نفس النطاق إذا كانت النطاقات متطابقة بين جميع الأوراق.
  - انقر التالي لمتابعة.

4. في الخطوة 3 من 3:
حدد متوسط من Function القائمة المنسدلة. بشكل اختياري، قم بتكوين تسميات الصفوف/الأعمدة حسب الحاجة، ثم انقر إنهاء.

5. سيظهر مربع حوار يسأل ما إذا كنت ترغب في حفظ الإعدادات الحالية كسيناريو لاستخدامه في المستقبل. اختر نعم أو لا بناءً على احتياجاتك.

الآن، ستُظهر كل خلية في النطاق المخرج الذي حددته متوسط الخلايا المقابلة من جميع الأوراق المحددة. هذه الطريقة مفيدة بشكل خاص للعمليات المتكررة أو عند دمج كميات كبيرة من البيانات المنظمة بسرعة.

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

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

كيفية إعداد واستخدام هذا الحل باستخدام VBA:

1. انتقل إلى تبويب Developer في Excel. إذا لم يكن مرئيًا، قم بتمكينه عبر File > Options > Customize Ribbon. انقر Visual Basic لفتح المحرر. ثم انتقل إلى Insert > Module والصق الكود التالي:

Sub AverageAcrossSheets()
    Dim xSheetNames As String
    Dim xCellRange As String
    Dim xArr As Variant
    Dim xSheet As Worksheet
    Dim xTotal As Double
    Dim xCount As Long
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    xSheetNames = Application.InputBox("Enter sheet names separated by commas (e.g., Sheet1,Sheet3,Summary):", xTitleId, Type:=2)
    If xSheetNames = "" Then Exit Sub
    
    xCellRange = Application.InputBox("Enter cell or range to average (e.g., A1 or A1:B10):", xTitleId, Type:=2)
    If xCellRange = "" Then Exit Sub
    
    xArr = Split(xSheetNames, ",")
    xTotal = 0
    xCount = 0
    
    For i = LBound(xArr) To UBound(xArr)
        Set xSheet = Nothing
        Set xSheet = ThisWorkbook.Sheets(Trim(xArr(i)))
        
        If Not xSheet Is Nothing Then
            If Not IsError(Application.WorksheetFunction.Average(xSheet.Range(xCellRange))) Then
                xTotal = xTotal + Application.WorksheetFunction.Sum(xSheet.Range(xCellRange))
                xCount = xCount + xSheet.Range(xCellRange).Count
            End If
        End If
    Next i
    
    If xCount = 0 Then
        MsgBox "No valid data found!", vbExclamation, xTitleId
    Else
        MsgBox "The average across selected sheets and range is: " & xTotal / xCount, vbInformation, xTitleId
    End If
End Sub

2. لتشغيل الماكرو، اضغط F5 في المحرر، أو أغلقه وانتقل إلى Developer > Macros، حدد AverageAcrossSheets، ثم انقر Run.

3. عند المطالبة، أدخل قائمة بفاصلة بين أسماء الأوراق (مثل Sheet1,Sheet3,Summary)، ثم حدد النطاق (مثل A1:A10).

4. سيقوم الماكرو بحساب الإجمالي والعدد من كل ورقة صحيحة وعرض المتوسط في مربع رسالة.

ملاحظات حول المعاملات:

• أسماء الأوراق غير حساسة لحالة الأحرف ولكن يجب أن تتطابق تمامًا.
• النطاق يمكن أن يكون خلية واحدة، عمودًا كاملًا (مثل B:B)، أو نطاقًا مستطيلي الشكل (مثل D2:E12).
• الأوراق غير الصالحة أو المفقودة سيتم تجاوزها بصمت.