كيف يمكن عد البيانات حسب المجموعة في Excel؟
في Excel، يُعد عد إجمالي عدد القيم في نطاقٍ ما مهمةً بسيطة وشائعة لمعظم المستخدمين. لكن في العديد من السيناريوهات الواقعية—مثل تقارير المبيعات أو قوائم الجرد أو بيانات الموظفين—غالبًا ما تكون المعلومات مُنظَّمة في مجموعات، كفئات المنتجات أو الإدارات أو المناطق. وفي هذه الحالات، قد تحتاج ليس فقط إلى معرفة العدد الإجمالي، بل أيضًا إلى تحديد عدد مرات ظهور كل مجموعة على حدة. ويُعد هذا المطلب شائعًا جدًّا عند تلخيص البيانات المجمَّعة وتحليلها لإعداد التقارير أو اتخاذ قرارات مدعومة بالبيانات. وعلى الرغم من أن Excel لا يحتوي على زرٍ واحد مباشر أو دالة مضمنة تحمل اسم «العد حسب المجموعة» صراحةً، إلا أن هناك عدة طرق موثوقة وفعّالة لتحقيق هذا الهدف.
فيما يلي طرق عملية يمكنك استخدامها لعد البيانات في كل مجموعة في Excel، لتلبية احتياجات ومستويات متنوعة من المستخدمين—بدءًا من الميزات المضمنة، مرورًا بالحلول القائمة على الصيغ، ووصولًا إلى الحلول الآلية باستخدام VBA. ولكل طريقة مزاياها الخاصة، مما يسهّل عليك اختيار الأسلوب الأنسب لسيناريو ورقة العمل الخاص بك.
عد البيانات حسب المجموعة باستخدام جدول بيانات محوري
عد البيانات حسب المجموعة باستخدام كود VBA
عد البيانات حسب المجموعة باستخدام صيغ Excel (COUNTIF/COUNTIFS)
عد البيانات حسب المجموعة باستخدام جدول بيانات محوري
تُعد جداول Pivot المثالية لتلخيص مجموعات البيانات الكبيرة بسرعة حسب الفئات المختلفة، بما في ذلك عد العناصر في كل مجموعة. فعلى سبيل المثال، إذا كانت لديك قائمة طويلة بمعاملات المبيعات وتريد معرفة عدد مرات بيع كل منتج، فإن الجدول المحوري يُعد أحد أسرع الأدوات العملية التي يمكنك الاعتماد عليها.
افترض أن لديك مجموعة بيانات مشابهة للمثال أدناه وترغب في عد عدد مرات الظهور حسب المجموعة (مثل عد عدد مرات ظهور كل عنصر أو فئة):
1. حدد نطاق البيانات بالكامل الذي يحتوي على المجموعات والبيانات التي تريد عدها. انقر فوقإدراج > جدول بيانات محوري > جدول بيانات محوريفي شريط Excel. راجع لقطة الشاشة:
2. في مربع الحوارإنشاء جدول بيانات محوري، اختر ما إذا كنت تريد وضع الجدول في ورقة عمل جديدة أو في ورقة عمل موجودة. وإذا اخترتورقة عمل موجودة، فتأكد من تحديد خلية فارغة لا تتداخل مع بياناتك الحالية. راجع لقطة الشاشة:
3. انقر فوقموافق. على الجانب الأيمن من نافذة Excel، تظهر لوحةقائمة حقول PivotTable. اسحب رأس عمود المجموعة (مثل «العنصر» أو «الفئة») إلى كلٍّ من قسميتسميات الصفوفوالقيم. بشكل افتراضي، يستخدم قسم القيم دالة «العد»، مما يعرض عدد مرات ظهور كل إدخال. راجع لقطة الشاشة:
ستشاهد فورًا تقريرًا قامت فيه 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) في «ورقة العمل 1»، ويحسب عدد مرات ظهور كل مجموعة، ثم يعرض النتيجة الموجزة في العمودين C وD بدءًا من الصف 2.
ملاحظات:يمكنك تعديل «ورقة العمل 1» ومراجع الأعمدة ومواقع الإخراج حسب احتياجاتك الخاصة بمصنّفك. إذا كانت بياناتك تحتوي على خلايا فارغة أو حالات استثنائية، فتأكد من مراجعة النتائج للتحقق من دقتها. وإذا احتوى اسم المجموعة المكررة على تهجئات مختلفة (مثل «تفاحة» مقابل «تفاحة» بحروف صغيرة)، فسيتم التعامل مع هذه الحالات كمجموعات منفصلة. وللحصول على تجميع مخصص—مثل عدم التمييز بين الأحرف الكبيرة والصغيرة، أو إخراج مرتب، أو تجميعات أكثر تعقيدًا—فقد تحتاج إلى إضافة تعديلات إضافية على كود VBA.
يُعد VBA الخيار الأمثل لأتمتة المهام القابلة للتكرار—خاصةً عند التعامل مع مجموعات بيانات كبيرة أو التي يتم تحديثها باستمرار، حيث يصبح التلخيص اليدوي استنزافًا للوقت. إذا واجهت أخطاءً مثل «متغير كائن غير مضبوط» أو «فهرس خارج النطاق»، فتأكد من أن مراجع الورقة والنطاق تتوافق تمامًا مع هيكل بياناتك الفعلي.
عد البيانات حسب المجموعة باستخدام صيغ Excel (COUNTIF/COUNTIFS)
بالنسبة للمستخدمين الذين يفضلون العمل مباشرةً على شبكات أوراق العمل أو يحتاجون إلى حلول ديناميكية قائمة على الصيغ لمزيد من الحسابات والإحالات، فإن دالتي COUNTIF وCOUNTIFS في Excel توفّران حلاً فعّالًا. تكون هذه الصيغ مفيدة بشكل خاص عندما تريد أن يتم تحديث أعداد المجموعات تلقائيًا مع تغيّر بياناتك الأساسية، أو إذا كنت بحاجة إلى النتائج بجانب بياناتك مباشرةً كمرجع أو لتحليل إضافي.
سيناريو مثال:افترض أن بياناتك موجودة في العمودين A (اسم المجموعة) وB (القيمة)، وترغب في عد عدد مرات ظهور كل مجموعة.
1.في عمود جديد بجانب بياناتك (مثل الخلية C2)، أدخل الصيغة أدناه لحساب العدد لكل مجموعة:
=COUNTIF($A$2:$A$100, A2)2. بعد إدخال الصيغة، اضغط علىEnter. ولتطبيق هذه الصيغة على جميع الصفوف، اسحب مقبض الملء لأسفل من الخلية C2 لملء الخلايا المجاورة لبياناتك، أو انقر نقرًا مزدوجًا على مقبض الملء للتعبئة التلقائية. وستُعيد الصيغة عدد مرات ظهور المجموعة في ذلك الصف.
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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل