كيفية حساب متوسط كل 5 صفوف أو أعمدة في Excel؟
عند العمل مع مجموعات بيانات كبيرة في Excel، من الشائع الحاجة إلى حساب المتوسط لكل مجموعة من الصفوف أو الأعمدة — مثل كل 5 صفوف أو كل 5 أعمدة. على الرغم من أنه يمكنك إدراج صيغ يدويًا مثل =AVERAGE(A1:A5)
، =AVERAGE(A6:A10)
، =AVERAGE(A11:A15)
وما إلى ذلك، فإن هذا يصبح غير عملي بسرعة إذا كانت قائمتك تحتوي على مئات أو آلاف الخلايا. تكرار هذه العمليات يدويًا يستهلك وقتًا ويعرضك للأخطاء. لحسن الحظ، توفر Excel طرقًا متعددة لأتمتة هذه المهمة، مما يجعل تحليل البيانات أكثر كفاءة وأقل رتابة. ستحدد هذه المقالة عدة طرق عملية لحساب المتوسط لكل 5 صفوف أو أعمدة، بما في ذلك الأساليب المعتمدة على الصيغ، الإضافات الخاصة بـ Excel، أتمتة VBA، وتقنيات جدول Pivot Table، لمساعدتك في اختيار الحل الأنسب لسيناريو عملك.
احسب المتوسط لكل 5 صفوف أو أعمدة باستخدام الصيغ
احسب المتوسط لكل 5 صفوف باستخدام Kutools for Excel
احسب المتوسط لكل 5 صفوف أو أعمدة باستخدام شفرة VBA
احسب المتوسط لكل 5 صفوف باستخدام جدول محوري
احسب المتوسط لكل 5 صفوف أو أعمدة باستخدام الصيغ
إذا كنت تفضل استخدام صيغ Excel القياسية، يمكنك أتمتة الحساب لكل 5 صفوف أو أعمدة دون الحاجة إلى إضافات أو برمجيات. هذا النهج مناسب بشكل خاص لمجموعات البيانات الثابتة حيث تحتاج ببساطة إلى إنشاء مجاميع من المتوسطات لدعم تحليلك. ومع ذلك، يجب أن تكون حذرًا عند الرجوع إلى بياناتك والتعامل مع أي فراغات أو فواصل غير منتظمة.
يوضح المثال التالي كيفية حساب المتوسط لكل 5 صفوف في عمود:
1. أدخل الصيغة التالية في أول خلية تريد نتيجة فيها (على سبيل المثال، C2):
=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))
هنا، A2 هي الخلية الأولى من عمود بياناتك، C2 هي خلية ناتج الصيغة، و5 هو الفاصل الزمني (عدد الصفوف لحساب المتوسط). تأكد من تعديل هذه المراجع وفقًا لمجموعة بياناتك الفعلية.
بعد إدخال الصيغة، اضغط على Enter. سيتم عرض النتيجة الأولى للمتوسط. انظر للقطة الشاشة:
2. حدد الخلية التي تحتوي على الصيغة، ثم اسحب مقبض التعبئة لأسفل حتى تواجه قيمة خطأ (مثل #DIV/0!
إذا كان هناك أقل من 5 قيم في البيانات المتبقية). سيقوم هذا تلقائيًا باستخراج المتوسطات لكل مجموعة من 5 صفوف. انظر للقطة الشاشة:
نصائح وملاحظات: يمكنك استخدام وظائف التعامل مع الأخطاء مثل IFERROR()
لإخفاء قيم الخطأ إذا لم تناسب بياناتك مجموعات ذات أحجام دقيقة، على سبيل المثال:
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")
لحساب المتوسط لكل 5 أعمدة عبر صف، قم بتطبيق الصيغة التالية (ضعها في A3 واسحبها عبر الصف):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))
هنا، A1 هي الخلية الأولى، A3 هي خلية ناتج الصيغة، و5 هو عدد الأعمدة في كل مجموعة. قم بتعديل مراجع الخلايا حسب حاجتك لتصميم بياناتك.
بعد إدخال الصيغة والضغط على Enter، اسحب مقبض التعبئة إلى اليمين حتى تصل إلى قيمة خطأ. انظر للقطة الشاشة:
هذه الطريقة القائمة على الصيغ مثالية لحسابات سريعة أو عندما لا تريد استخدام أدوات إضافية. ومع ذلك، مع تغير حجم أو شكل بياناتك، قد تحتاج إلى تعديل الصيغ أو تحديث نطاقات الخلايا يدويًا، وقد يتطلب التعامل مع المجاميع غير الكاملة عناية إضافية.
احسب المتوسط لكل 5 صفوف باستخدام Kutools for Excel
يوفر Kutools for Excel حلًّا رسوميًا مريحًا إذا كنت بحاجة بشكل متكرر إلى حساب المتوسط لمجموعات الصفوف دون إدارة صيغ معقدة. من خلال وظائف إدراج فواصل الصفحات في كل صف وإحصاءات الصفحات، يمكنك تقسيم بياناتك بسرعة وحساب المتوسطات الدُفعية بنقرات قليلة فقط. هذه الطريقة مفيدة بشكل خاص إذا كنت ترغب في تطبيق المتوسطات على فترات متكررة وتوضيح التجميع مباشرة في ورقة العمل الخاصة بك.
بعد تنزيل وتثبيت Kutools for Excel، يرجى اتباع الخطوات التالية:
1. انقر على Kutools Plus > Printing > Insert Page Break Every Row. انظر للقطة الشاشة:
2. في مربع الحوار Insert Page Break Every Row، حدد الفاصل الزمني (على سبيل المثال، 5) لإدراج فاصل صفحة بعد كل 5 صفوف. هذا يسمح لـ Kutools بتقسيم بياناتك تلقائيًا. انظر للقطة الشاشة:
3. بعد ذلك، انقر على Kutools Plus > Printing > Paging Subtotals. انظر للقطة الشاشة:
4. في مربع الحوار Paging Subtotals، حدد الأعمدة التي ترغب في حساب المتوسط لها، ثم اختر Average كطريقة الحساب. انظر للقطة الشاشة:
5. انقر على OK، وسيقوم Kutools بإدراج صفوف الإجماليات الفرعية مع قيم المتوسط عند كل فترة تتكون من 5 صفوف. انظر للقطة الشاشة:
قم بتنزيل Kutools for Excel الآن وجرّبه مجانًا!
يجعل Kutools تجميع البيانات المتكرر والتحليل سهلًا دون الحاجة إلى تعديل الصيغ أو كتابة أي سكريبتات. ومع ذلك، يرجى ملاحظة أن فواصل الصفحات المضافة قد تؤثر على تخطيط الطباعة والمظهر، لذا قد ترغب في إزالتها بعد الاستخدام إذا لم تكن ضرورية لتقريرك.
احسب المتوسط لكل 5 صفوف أو أعمدة باستخدام شفرة VBA
إذا كنت بحاجة إلى حساب متوسط القيم بشكل متكرر لكل عدد ثابت من الصفوف أو الأعمدة عبر مجموعات بيانات كبيرة أو متغيرة باستمرار، يمكن أن يوفر لك أتمتة هذه العملية باستخدام VBA الكثير من الجهد اليدوي. يسمح لك VBA بالمرور عبر بياناتك، تجميعها كما تشاء، وإخراج متوسط النتائج لكل دفعة. هذه الطريقة مناسبة بشكل خاص للمستخدمين المتقدمين أو الذين يتعاملون مع كتل بيانات ديناميكية، وتتجنب ازدحام ورقة العمل بالصيغ. فيما يلي ماكرو VBA متعدد الاستخدامات يمكنك تعديله بسهولة.
أتمتة حساب المتوسط لكل 5 صفوف:
1. انقر على Developer > Visual Basic لفتح نافذة Microsoft Visual Basic for Applications. بعد ذلك، انقر على Insert > Module والصق الكود أدناه في الوحدة:
Sub AverageEvery5Rows()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Integer, i As Integer, j As Integer
Dim LastRow As Long, StartRow As Long
Dim SumValue As Double, CountValue As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the data range to average (single column)", xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output", xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
LastRow = DataRange.Rows.Count
StartRow = 1
i = 0
Do While StartRow <= LastRow
SumValue = 0
CountValue = 0
For j = 0 To GroupSize - 1
If (StartRow + j) <= LastRow Then
SumValue = SumValue + DataRange.Cells(StartRow + j, 1).Value
CountValue = CountValue + 1
End If
Next j
If CountValue > 0 Then
OutputCell.Offset(i, 0).Value = SumValue / CountValue
Else
OutputCell.Offset(i, 0).Value = ""
End If
StartRow = StartRow + GroupSize
i = i + 1
Loop
End Sub
2. لتنفيذ الكود، انقر على الزر أو اضغط على F5. حدد نطاق بياناتك (عمود واحد)، ثم اختر الخلية الأولى لناتج الإخراج، وحدد حجم المجموعة (على سبيل المثال، 5). سيقوم الماكرو بإخراج متوسط كل مجموعة من 5 صفوف، واحدة أسفل الأخرى في عمود الإخراج المحدد.
يمكنك استخدام ماكرو مشابه لحساب المتوسط لكل 5 أعمدة عبر صف.
أتمتة حساب المتوسط لكل 5 أعمدة:
Sub AverageEveryNColumns()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Long
Dim totalCols As Long, totalRows As Long
Dim startCol As Long, endCol As Long, outCol As Long
Dim v As Variant
Dim r As Long, c As Long
Dim sumVal As Double, cntVal As Long
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set DataRange = Application.InputBox("Select the data range (single rows)", _
xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output (results will spill to the right)", _
xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
If GroupSize < 1 Then
MsgBox "Group size must be >= 1.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim prevCalc As XlCalculation
prevCalc = Application.Calculation
Application.Calculation = xlCalculationManual
totalCols = DataRange.Columns.Count
totalRows = DataRange.Rows.Count
v = DataRange.Value
outCol = 0
For startCol = 1 To totalCols Step GroupSize
endCol = startCol + GroupSize - 1
If endCol > totalCols Then endCol = totalCols
sumVal = 0
cntVal = 0
For r = 1 To totalRows
For c = startCol To endCol
If Not IsEmpty(v(r, c)) Then
If IsNumeric(v(r, c)) Then
sumVal = sumVal + CDbl(v(r, c))
cntVal = cntVal + 1
End If
End If
Next c
Next r
If cntVal > 0 Then
OutputCell.Offset(0, outCol).Value = sumVal / cntVal
Else
OutputCell.Offset(0, outCol).Value = ""
End If
outCol = outCol + 1
Next startCol
CleanExit:
Application.Calculation = prevCalc
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
احسب المتوسط لكل 5 صفوف باستخدام جدول محوري
طريقة أخرى عملية لحساب متوسط المجموعات كل 5 صفوف هي استخدام جدول محوري، مع الجمع بينه وبين عمود ترقيم أو فهرس لتجميع البيانات. هذه الطريقة مفيدة بشكل خاص للمستخدمين الذين يعملون مع بيانات مهيكلة قائمة على الجداول ويحتاجون إلى ملخص سريع وتفاعلي دون كتابة صيغ أو استخدام إضافات. يتعامل الجدول المحوري بشكل ديناميكي مع تغييرات البيانات ويدعم التجميع المرن — رائع لمجموعات البيانات الكبيرة أو مهام التقارير المنتظمة.
فيما يلي كيفية تنفيذ هذه العملية باستخدام عمود مساعد وجدول محوري:
1. أضف عمود "Index" أو "Group" بجانب بياناتك لتحديد كل مجموعة من 5 صفوف. في أول صف بيانات (B2)، أدخل:
=INT((ROW()-ROW($A$2))/5)+1
هذه الصيغة تعطي كل صف رقمًا تسلسليًا، وتخصص نفس رقم المجموعة لكل 5 صفوف. املأ هذه الصيغة عبر مجموعة بياناتك.
2. حدد بياناتك والعمود الجديد Index، ثم انقر على Insert > PivotTable. في مربع حوار إنشاء الجدول المحوري، تأكد من نطاق بياناتك واختر مكان وضع الجدول المحوري.
3. في قائمة مجالات الجدول المحوري الجديد، اسحب حقل "Group" إلى منطقة الصفوف، وحقل القيمة الخاص بك (مثل "Sales") إلى منطقة القيم.
4. انقر على القائمة المنسدلة في منطقة القيم، اختر إعدادات حقل القيمة، وحدد المتوسط.
الآن، يعرض جدول المحوري المتوسط لكل 5 صفوف من البيانات الأصلية، مرتبة براحة بواسطة العمود المساعد.
من بين الفوائد الرئيسية لطريقة الجدول المحوري هي مرونتها وسهولة تحديثها عند تغيير بيانات المصدر. ومع ذلك، تتطلب إضافة عمود مساعد، وقد لا تكون مناسبة في الحالات التي تحتاج فيها البيانات إلى البقاء مصاغة بدقة أو دون تغيير.
مقالات ذات صلة:
كيفية حساب متوسط آخر 5 قيم في عمود عند إدخال أرقام جديدة؟
كيفية حساب متوسط أعلى أو أدنى 3 قيم في 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 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد