كيفية حساب البيانات حسب المجموعة في Excel؟
في Excel، يعد حساب العدد الإجمالي للقيم في نطاق بسيط مهمة شائعة وسهلة بالنسبة لمعظم المستخدمين. ومع ذلك، في العديد من السيناريوهات الواقعية—مثل تقارير المبيعات، قوائم الجرد، أو بيانات الموظفين—غالبًا ما يتم تنظيم المعلومات إلى مجموعات، مثل فئات المنتجات، الأقسام، أو المناطق. في هذه الحالات، قد تحتاج ليس فقط إلى حساب الإجمالي الكلي، ولكن أيضًا تحديد عدد التكرارات داخل كل مجموعة. هذا الطلب شائع جدًا لتلخيص وتحليل البيانات المجمعة لإعداد التقارير أو اتخاذ القرارات القائمة على البيانات. وعلى الرغم من عدم وجود زر مباشر أو دالة مضمنة مسمى "العد حسب المجموعة" في Excel، إلا أن هناك عدة طرق موثوقة وفعالة لتحقيق هذا الهدف.
أدناه بعض الطرق العملية التي يمكنك استخدامها لحساب البيانات في كل مجموعة في Excel، والتي تلبي احتياجات المستخدمين المختلفة والمستويات—من الميزات المدمجة إلى الحلول المعتمدة على الصيغ وحتى حلول VBA الآلية. كل طريقة لها مزاياها مما يجعل من السهل اختيار النهج الأكثر ملاءمة لسيناريو ورقة العمل الخاص بك.
حساب البيانات حسب المجموعة باستخدام جدول محوري
حساب البيانات حسب المجموعة باستخدام كود VBA
حساب البيانات حسب المجموعة باستخدام صيغ Excel (COUNTIF/COUNTIFS)
حساب البيانات حسب المجموعة باستخدام جدول محوري
تعد الجداول المحورية مثالية لتلخيص مجموعات البيانات الكبيرة بسرعة بناءً على فئات مختلفة، بما في ذلك عد العناصر في كل مجموعة. على سبيل المثال، إذا كان لديك قائمة طويلة من معاملات البيع وتريد معرفة عدد مرات بيع كل منتج، فإن الجدول المحوري هو واحد من أسرع الأدوات العملية التي يمكنك استخدامها.
لنفترض أن لديك مجموعة بيانات مثل المثال أدناه وتريد حساب عدد التكرارات حسب المجموعة (مثل حساب عدد مرات ظهور كل عنصر أو فئة):
1. حدد النطاق الكامل للبيانات الذي يشمل المجموعات والبيانات التي تريد عدها. انقر فوق إدراج > جدول محوري > جدول محوري في شريط أدوات Excel. شاهد لقطة الشاشة:
2. في نافذة إنشاء جدول محوري، اختر ما إذا كنت ستضع الجدول المحوري في ورقة عمل جديدة أو في الورقة الحالية. إذا اخترت ورقة عمل موجودة، تأكد من اختيار خلية فارغة لن تؤثر على بياناتك الموجودة. شاهد لقطة الشاشة:
3. انقر فوق موافق. سيظهر الجزء قائمة حقول الجدول المحوري على الجانب الأيمن من نافذة Excel. اسحب رأس العمود الذي يتضمن المجموعة (على سبيل المثال، "العنصر" أو "الفئة") إلى كلا من التصنيفات الصفية ومنطقة القيم. بشكل افتراضي، سيتم استخدام وظيفة "العد" في منطقة القيم، مما يعرض عدد مرات ظهور كل إدخال. شاهد لقطة الشاشة:
سترى على الفور تقريرًا حيث قامت Excel بتجميع بياناتك وأظهرت العدد لكل مجموعة. هذا مفيد جدًا لتصور توزيع العناصر أو المنتجات أو السجلات عبر الفئات. إذا قمت بتحديث مجموعة البيانات الأصلية، تذكر تحديث الجدول المحوري ليعكس أي تغييرات.
نصائح وملاحظات: إذا كان نطاق مصدر بياناتك يحتوي على صفوف أو أعمدة فارغة، تأكد من استبعادها عند إنشاء الجدول المحوري، لأن ذلك قد يؤثر على دقة التجميع. توفر الجداول المحورية طريقة سهلة لتلخيص البيانات، ولكن إذا كنت بحاجة إلى إعادة استخدام النتائج في أماكن أخرى ضمن الصيغ أو بين الأوراق، أو ترغب في تقارير أكثر مرونة، ففكر في الحلول المعتمدة على الصيغ أو VBA أدناه.
حساب البيانات حسب المجموعة باستخدام كود VBA
إذا كانت مصنفات العمل تتطلب عدًا وإعداد تقارير متكررة حول بيانات مجمعة بشكل مختلف، أو إذا كنت بحاجة إلى أتمتة هذه العملية لمجموعات بيانات كبيرة أو مهام متكررة، يمكنك استخدام سكريبت VBA. يتيح VBA (Visual Basic for Applications) تخصيص وأتمتة عمليات Excel، مما يمكّنك من إنشاء تقارير، تلخيص المعلومات، أو تصدير أعداد المجموعات بأقل قدر من التدخل اليدوي.
احتياط: احفظ دائمًا عملك قبل تشغيل أي كود VBA جديد. يمكن لـ VBA تمكين أتمتة قوية ولكنه قد يؤدي إلى الكتابة فوق البيانات إذا لم يتم تكوينه بعناية.
1. انتقل إلى علامة التبويب مطور > Visual Basic لفتح محرر VBA. في النافذة التي تظهر، انقر فوق إدراج > وحدة، والصق الكود التالي في نافذة الوحدة:
Sub GroupCount()
Dim dict As Object
Dim lastRow As Long
Dim groupCol As Range
Dim groupCell As Range
Dim outputRow As Long
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Change Sheet1 and column as needed
With Worksheets("Sheet1")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set groupCol = .Range("A2:A" & lastRow)
For Each groupCell In groupCol
If Not dict.Exists(groupCell.Value) Then
dict(groupCell.Value) = 1
Else
dict(groupCell.Value) = dict(groupCell.Value) + 1
End If
Next groupCell
outputRow = 2
.Cells(1, "C").Value = "Group"
.Cells(1, "D").Value = "Count"
For Each key In dict.Keys
.Cells(outputRow, "C").Value = key
.Cells(outputRow, "D").Value = dict(key)
outputRow = outputRow + 1
Next key
End With
End Sub2. لتنفيذ الكود، اضغط F5 أو انقر فوق الزر 
"تشغيل" بينما تكون في محرر VBA. سيقوم البرنامج بمسح بيانات المجموعة في العمود A (ابتداءً من A2) في "Sheet1"، وسيجمع العدد لكل مجموعة، وينتج النتائج الموجزة في الأعمدة C و D بدءًا من الصف 2.
ملاحظات: يمكنك تعديل "Sheet1"، مراجع الأعمدة ومواقع الإخراج حسب الحاجة لملف العمل الخاص بك. إذا كانت بياناتك تحتوي على خلايا فارغة أو حالات خاصة، راجع النتائج للتأكد من الدقة. إذا كانت أسماء المجموعات المكررة تحتوي على تهجئات مختلفة (مثل "Apple" مقابل "apple")، فسيتم التعامل معها كمجموعات منفصلة. للمزيد من التخصيص (عدم التحسس لحالة الأحرف، الإخراج المصنف، أو تجميعات أكثر تعقيدًا)، قد تكون هناك حاجة إلى إضافات أخرى للكود VBA.
VBA هو الأنسب لأتمتة المهام المتكررة—خاصة عند التعامل مع مجموعات بيانات كبيرة أو يتم تحديثها بشكل متكرر حيث يكون التلخيص اليدوي مرهقًا. إذا واجهت أخطاء مثل "لم يتم تعيين متغير الكائن" أو "الموقع خارج النطاق"، تأكد من أن مراجع الأوراق والنطاقات تتطابق مع هيكل بياناتك الفعلي.
حساب البيانات حسب المجموعة باستخدام صيغ Excel (COUNTIF/COUNTIFS)
بالنسبة للمستخدمين الذين يفضلون العمل مباشرة على شبكات أوراق العمل أو يحتاجون إلى حلول ديناميكية قائمة على الصيغ لإجراء المزيد من الحسابات والإشارات، توفر دوال COUNTIF و COUNTIFS في Excel حلولاً فعالة. هذه الصيغ مفيدة بشكل خاص عندما تريد أن يتم تحديث عدد المجموعات تلقائيًا عند تغيير البيانات الأساسية، أو إذا كنت بحاجة إلى النتائج بجانب بياناتك للرجوع إليها أو تحليلها لاحقًا.
سيناريو مثال: لنفترض أن بياناتك في الأعمدة A (اسم المجموعة) و B (القيمة)، وتريد حساب عدد مرات ظهور كل مجموعة.
1. في عمود جديد، بجانب بياناتك (مثل الخلية C2)، أدخل الصيغة أدناه لحساب العدد لكل مجموعة:
=COUNTIF($A$2:$A$100, A2)2. بعد إدخال الصيغة، اضغط Enter. لتطبيق هذه الصيغة على جميع الصفوف، اسحب مقبض التعبئة من الخلية C2 لملء الخلايا بجانب بياناتك، أو انقر نقراً مزدوجاً على مchandle التعبئة للتعبئة التلقائية. ستعيد الصيغة عدد التكرارات للمجموعة في هذا الصف.
3. إذا كنت ترغب في الحصول على قائمة فريدة لجميع المجموعات وأعدادها المقابلة، استخرج أسماء المجموعات المميزة أولاً (على سبيل المثال، باستخدام ميزة إزالة التكرارات أو صيغة UNIQUE، حسب إصدار Excel الخاص بك)، ثم قم بتطبيق صيغة COUNTIF على القائمة الفريدة.
شرح المعلمات: في الصيغة أعلاه، $A$2:$A$100 هو النطاق الذي يحتوي على أسماء مجموعتك. قم بتعديل هذا النطاق ليتناسب مع بياناتك الفعلية. A2 هو مرجع الخلية للقيمة الحالية للمجموعة في هذا الصف.
هذا الحل المعتمد على الصيغ مرنة للغاية: يمكنك استخدامه لقوائم المرشحة، البيانات المصنفة، أو بجانب حسابات أخرى. ومع ذلك، كن حذرًا بشأن الأداء إذا تم تطبيقه على مجموعات بيانات كبيرة جدًا، حيث قد يستغرق إعادة الحساب وقتًا إضافيًا.
COUNTIFS يسمح بالعد حسب معايير متعددة إذا كانت عملية التجميع الخاصة بك أكثر تعقيدًا (على سبيل المثال، التجميع حسب الفئة والمنطقة).
مقالات ذات صلة:
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في 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 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد