جمع القيم حسب المجموعة في 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. أولاً ، يرجى استخراج اسم المجموعة الفريد باستخدام صيغة الصفيف التالية ، ثم الضغط على كترل + شيفت + إنتر مفاتيح معًا للحصول على النتيجة الأولى.
- ملاحظات: في الصيغة:
- A2: A13 هو نطاق الخلايا حيث يتم استخراج جميع القيم الفريدة ؛
- D1 هي الخلية الموجودة أعلى الصيغة التي أدخلتها.
2. وبعد ذلك ، حدد خلية الصيغة واسحب مقبض التعبئة لأسفل إلى الخلايا حتى يتم عرض جميع أسماء المنتجات ، انظر لقطة الشاشة:
3. الآن ، يمكنك جمع القيم حسب اسم المجموعة الذي قمت باستخراجه. في هذه الحالة ، سيتم استخدام دالة SUMIF هنا ، يرجى إدخال الصيغة أدناه في خلية فارغة ، في هذا المثال ، سأضعها في الخلية E2.
4. بعد ذلك ، اسحب مقبض التعبئة لأسفل لنسخ هذه الصيغة لإرجاع الترتيب الإجمالي للمجموعات الأخرى ، انظر الصورة:
الوظيفة النسبية المستخدمة:
- SUMIF:
- يمكن أن تساعد الدالة SUMIF في جمع الخلايا بناءً على معيار واحد.
- IF:
- تختبر الدالة IF شرطًا معينًا وتُرجع القيمة المقابلة التي توفرها لـ TRUE أو FALSE.
المزيد من المقالات:
- إجمالي مبالغ الفاتورة حسب العمر
- لتلخيص مبالغ الفاتورة بناءً على العمر كما هو موضح أدناه قد تكون مهمة شائعة في Excel ، سيوضح هذا البرنامج التعليمي كيفية إجمالي مبالغ الفواتير حسب العمر باستخدام وظيفة SUMIF عادية.
- مجموع القيم الأصغر أو السفلية
- في Excel ، من السهل علينا جمع نطاق من الخلايا باستخدام وظيفة SUM. في بعض الأحيان ، قد تحتاج إلى جمع الأرقام الأصغر أو السفلية 3 أو 5 أو n في نطاق البيانات كما هو موضح أدناه لقطة الشاشة. في هذه الحالة ، يمكن أن يساعدك SUMPRODUCT مع وظيفة SMALL في حل هذه المشكلة في Excel.
- مجموع القيم الأصغر أو الأدنى N بناءً على المعايير
- في البرنامج التعليمي السابق ، ناقشنا كيفية جمع أصغر قيم n في نطاق البيانات. في هذه المقالة ، سنجري عملية متقدمة أخرى - لجمع أقل قيم n بناءً على معيار واحد أو أكثر في Excel.
أفضل أدوات إنتاجية المكتب
كوتولس ل إكسيل - يساعدك على التميز من بين الحشود
Kutools for Excel يضم أكثر من 300 ميزة، التأكد من أن ما تحتاجه هو مجرد نقرة واحدة...
علامة تبويب Office - تمكين القراءة والتحرير المبوب في Microsoft Office (بما في ذلك Excel)
- ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
- قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
- يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
- يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel)، تمامًا مثل Chrome وEdge وFirefox.
