جمع القيم حسب المجموعة في Excel

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

• جمع القيم حسب المجموعة – إجماليات جزئية في جدول البيانات الأصلي
• جمع القيم حسب المجموعة – إجماليات جزئية في موقع آخر

جمع القيم حسب المجموعة – إجماليات جزئية في جدول البيانات الأصلي

الصيغة العامة لجمع القيم استنادًا إلى المجموعة في Excel يجب أن تكون كالتالي:

• group_name: الخلية التي تحتوي على اسم المجموعة الذي تريد جمعه؛
• cell_above_group_name: الخلية الموجودة أعلى اسم المجموعة؛
• group_range: نطاق الخلايا الذي يحتوي على اسم المجموعة؛
• sum_rangeنطاق الخلايا المطابق لمعيار اسم المجموعة المحدَّد لتجميعها معًا.

إذا تم فرز البيانات حسب عمود التجميع، فسيتم تجميع نفس المنتج معًا كما هو موضح في لقطة الشاشة أدناه. ولحساب الإجماليات الجزئية لكل مجموعة، يمكنك إنشاء صيغة باستخدام دالتَي IF وSUMIF.

1. يُرجى نسخ الصيغة التالية ولصقها في خلية فارغة بجانب بياناتك:

• ملاحظة: في الصيغة:
• A1هي خلية العنوان، وA2هي أول خلية تحتوي على اسم المنتج الذي تريد استخدامه؛
• A2:A13هو القائمة التي تحتوي على أسماء المنتجات التي تريد الجمع استنادًا إليها؛
• B2:B13 هو عمود البيانات الذي تريد حساب الإجمالي الجزئي له.

2. بعد ذلك، اسحب مقبض التعبئة لأسفل إلى الخلايا التي تريد تطبيق الصيغة عليها، وستُحسب الإجماليات الجزئية تلقائيًا لكل اسم منتج. انظر لقطة الشاشة:

شرح الصيغة:

=IF(A2=A1,«»,SUMIF($A$2:$A$13,A2,$B$2:$B$13))

• SUMIF($A$2:$A$13,A2,$B$2:$B$13): تقوم دالةSUMIF هذه بجمع القيم في النطاقB2:B13 فقط إذا كانت القيم المقابلة لها في النطاقA2:A13 تساوي المعيارA2.
• IF(A2=A1,«»,SUMIF($A$2:$A$13,A2,$B$2:$B$13)): تتحقق دالة IF هذه من كل قيمة في العمود A لمعرفة ما إذا كانت مطابقة للقيمة الموجودة في الخلية التي تعلوها. فعلى سبيل المثال، إذا كانت قيمة الخلية A2 تساوي قيمة A1، فستُرجع خلية فارغة («»)، أما إذا اختلفت، فستُرجع نتيجة دالة SUMIF.

جمع القيم حسب المجموعة – إجماليات جزئية في موقع آخر

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

1. أولًا، استخرج اسم المجموعة الفريدة باستخدام صيغة المصفوفة التالية، ثم اضغط على مفاتيحCtrl + Shift + Enter معًا للحصول على أول نتيجة.

• ملاحظة: في الصيغة:
• A2:A13هو نطاق الخلايا الذي سيتم استخراج جميع القيم الفريدة منه؛
• D1 هي الخلية الواقعة أعلى الصيغة التي أدخلتها.

2. بعد ذلك، حدد خلية الصيغة واسحب مقبض التعبئة لأسفل عبر الخلايا حتى تظهر جميع أسماء المنتجات، كما في لقطة الشاشة:

3. الآن، يمكنك جمع القيم بناءً على اسم المجموعة التي قمت باستخراجها. في هذه الحالة، سيتم استخدام دالة SUMIF؛ يُرجى إدخال الصيغة أدناه في خلية فارغة—وفي هذا المثال، سأضعها في الخلية E2.

4. بعد ذلك، اسحب مقبض التعبئة لأسفل لنسخ هذه الصيغة وإرجاع إجمالي الطلبات للمجموعات الأخرى. انظر لقطة الشاشة:

الدالة ذات الصلة المستخدمة:

مقالات أخرى: