كيف يمكن حساب المتوسط لكل 5 صفوف أو أعمدة في Excel؟
عند العمل مع مجموعات بيانات ضخمة في Excel، غالبًا ما تحتاج إلى حساب المتوسطات لكل مجموعة من الصفوف أو الأعمدة—مثل كل 5 صفوف أو كل 5 أعمدة. وعلى الرغم من إمكانية إدخال صيغ يدويًا مثل=AVERAGE(A1:A5)،=AVERAGE(A6:A10)،=AVERAGE(A11:A15)، وهكذا دواليك، فإن هذه الطريقة تصبح غير عملية بسرعة إذا احتوت قائمتك على مئات أو حتى آلاف الخلايا. فالتكرار اليدوي لهذه العمليات يستغرق وقتًا طويلاً ويزيد من احتمالية الوقوع في الأخطاء. لحسن الحظ، يوفّر Excel عدة طرق ذكية لأتمتة هذه المهمة، مما يجعل تحليل البيانات أكثر كفاءة وأقل رتابة. سيعرض لك هذا المقال أفضل الطرق العملية لحساب متوسط كل 5 صفوف أو أعمدة، بما في ذلك الحلول القائمة على الصيغ، ووظائف Excel الإضافية، وأتمتة VBA، وتقنيات الجداول المحورية—لمساعدتك على اختيار الأنسب لاحتياجاتك.
حساب المتوسط لكل 5 صفوف أو أعمدة باستخدام الصيغ
حساب المتوسط لكل 5 صفوف باستخدام Kutools لـ 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 لـ Excel
Kutools لـ Excelيوفّر حلاً رسوميًا مريحًا إذا كنت بحاجة متكررة إلى حساب متوسط مجموعات الصفوف دون التعامل مع صيغ معقدة. ومن خلال دوالإدراج فواصل الصفحات في كل صف آخروإحصاءات صفحة البيانات، يمكنك تقسيم بياناتك بسرعة وحساب المتوسطات الدفعية بنقرات قليلة. وتكون هذه الطريقة مفيدة بشكل خاص إذا أردت تطبيق المتوسطات على فترات قابلة للتكرار وتصوّر التقسيم مباشرةً في ورقة العمل الخاصة بك.
بعدتنزيل وتثبيتKutools لـ Excel، يُرجى اتباع الخطوات التالية:
1. انقر فوقKUTOOLS PLUS > الطباعة > إدراج فواصل الصفحات في كل صف آخر. انظر لقطة الشاشة:
2. في مربع الحوارإدراج فواصل الصفحات في كل صف آخر، حدد الفاصل الزمني (مثلًا،)5) لإدراج فاصل صفحات بعد كل 5 صفوف. يُمكّنك Kutools من تقسيم بياناتك تلقائيًا! انظر لقطة الشاشة:
3. بعد ذلك، انقر فوقKUTOOLS PLUS > الطباعة > إحصاءات صفحة البيانات. انظر لقطة الشاشة:
4. في مربع الحوارإحصاءات صفحة البيانات، حدد العمود (أو الأعمدة) الذي تريد حساب متوسطه، ثم اخترالمتوسطكطريقة للحساب. انظر لقطة الشاشة:
5. انقر فوقموافق، وسيقوم Kutools فورًا بإدراج صفوف المجموع الفرعي مع قيم المتوسطات عند كل فاصل زمني مكوَّن من 5 صفوف. انظر لقطة الشاشة:
نزّل Kutools لـ Excel وجربه مجانًا الآن!
يجعل Kutools مهام تجميع البيانات المتكررة وتحليلها سهلة الإنجاز دون الحاجة إلى تعديل الصيغ أو كتابة أي برمجيات نصية. ومع ذلك، يُرجى ملاحظة أن فواصل الصفحات المُدرجة قد تؤثر على تخطيط الطباعة والعرض، لذا قد ترغب في إزالتها بعد الاستخدام إذا لم تكن ضرورية لتقريرك.
حساب المتوسط لكل 5 صفوف أو أعمدة باستخدام كود VBA
إذا كنت بحاجة إلى حساب المتوسط بشكل متكرر لكل عدد ثابت من الصفوف أو الأعمدة عبر مجموعات بيانات كبيرة أو متغيرة باستمرار، فإن أتمتة هذه المهمة باستخدام VBA توفر لك جهدًا يدويًّا كبيرًا. يمكّنك VBA من التنقّل عبر بياناتك، وتجميعها حسب الحاجة، ثم إخراج متوسط كل دفعة تلقائيًّا. وتُعد هذه الطريقة مثالية خصوصًا للمستخدمين المتقدمين أو لأولئك الذين يعملون مع كتل بيانات ديناميكية، إذ تجنّبك ازدحام ورقة العمل بالصيغ. فيما يلي ماكرو VBA عام الغرض يمكنك تكييفه بسهولة لتلبية احتياجاتك.
أتمتة حساب المتوسط لكل 5 صفوف:
1. انقر فوقالمطور > Visual Basic لفتح نافذةMicrosoft Visual Basic for Applications. بعد ذلك، انقر فوقإدراج > وحدة البرمجة، والصق الكود أدناه في الوحدة:
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 Sub2.لتنفيذ الكود، انقر فوق الزر
أو اضغط علىF5. حدد نطاق البيانات الخاص بك (عمود واحد)، ثم اختر الخلية الابتدائية للإخراج، وعيّن حجم المجموعة (مثلًا، 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.أضف عمود «فهرس» أو «مجموعة» بجانب بياناتك لتمييز كل مجموعة مكوَّنة من 5 صفوف. في أول صف بيانات ()B2)، أدخل:
=INT((ROW()-ROW($A$2))/5)+1تصنّف هذه الصيغة كل صف تلقائيًا وتعيّن نفس رقم المجموعة لكل خمسة صفوف متتالية. اسحبها لأسفل بجانب مجموعة بياناتك لتطبيق التصنيف بسهولة!
2. حدد بياناتك وعمود الفهرس الجديد، ثم انقرإدراج > جدول محوري. في مربع حوار إنشاء الجدول المحوري، تأكد من نطاق البيانات واختر المكان الذي تريد وضع الجدول المحوري فيه.
3.في قائمة الحقول الجديدة لجدول البيانات المحوري، اسحب حقل «المجموعة» إلى منطقةالصفوف، واسحب حقل القيم الخاص بك (مثل «المبيعات») إلى منطقةالقيم.
4. انقر القائمة المنسدلة في منطقة القيم، واخترقيمة إعدادات الحقول، ثم اخترمتوسط.
الآن، يعرض جدولك المحوري المتوسط لكل 5 صفوف من البيانات الأصلية، مجمَّعة بشكل مريح حسب عمود المساعد.
تكمن الفوائد الرئيسية لجدول البيانات المحوري في مرونته العالية وسهولة تحديثه تلقائيًا عند تغيّر البيانات الأصلية. ومع ذلك، فإنه يتطلب إضافة عمود مساعد، وقد لا يكون الخيار الأمثل في الحالات التي يجب أن تظل فيها البيانات منسَّقة بدقة أو خالية تمامًا من أي تعديل.
مقالات ذات صلة:
كيف يمكن حساب متوسط آخر 5 قيم في عمود تلقائيًا عند إدخال أرقام جديدة؟
كيف يمكن حساب متوسط أعلى 3 قيم أو أقل 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل