كيف يمكن جمع القيم الفريدة وفقًا لمعايير محددة في Excel؟
عند العمل مع مجموعات بيانات Excel—مثل سجلات الطلبات أو السجلات المالية أو نتائج الاستبيانات—قد تجد نفسك غالبًا بحاجة إلى حساب مجموع القيم الفريدة من عمود واحد بناءً على مرشحات أو معايير موجودة في عمود آخر. على سبيل المثال، تخيل جدول بيانات يحتوي على عمودين: الاسموالطلب. إذا أردت حساب مجموع قيمالطلبالفريدة لكلاسم (مع تجاهل القيم المكررة)، فكيف يمكنك تحقيق ذلك بكفاءة في Excel؟ هذا متطلب شائع في العديد من السيناريوهات التجارية وتحليلات البيانات، حيث يؤدي جمع جميع العناصر المطابقة ببساطة إلى نتائج مبالغ فيها بسبب التكرارات.
تُظهر لقطة الشاشة التالية سيناريوًّا نموذجيًّا: لديك قائمة بأسماء مع قيم الطلبات المقابلة لها—بما في ذلك القيم المكرَّرة—وتحتاج إلى تلخيص البيانات عبر جمع قيم الطلبات الفريدة لكل اسم على حدة.
تشمل التحديات الشائعة في هذه المهمة تحديد الإدخالات الفريدة وفقًا لمعايير محددة، والتأكد من احتساب أول ظهور فقط، وتجنب الأخطاء اليدوية الناتجة عن نسخ ولصق البيانات المُرشَّحة. ولحسن الحظ، يوفّر Excel عدة طرق عملية لحل هذه المشكلة—مثل صيغ المصفوفة وأداة Kutools واستعلام Power Query—كلٌّ منها مصمم ليناسب سيناريوهات استخدام مختلفة.
- جمع القيم الفريدة بناءً على معيار واحد أو أكثر باستخدام صيغ المصفوفة
- جمع القيم الفريدة بناءً على المعايير باستخدام Kutools لـ Excel’s دمج متقدم للصفوف
- طرق Excel المضمنة الأخرى: استخدم جدول بيانات محوري لتحليل الجمع الفريد
<h4">جمع القيم الفريدة بناءً على معيار واحد أو أكثر باستخدام صيغ المصفوفة
إحدى الطرق الفعّالة والمرنة هي استخدام صيغ المصفوفة، التي تتيح لك تلخيص القيم الفريدة المستوفية لمعايير محددة، وتعمل هذه الطريقة بشكل ممتاز خصوصًا عندما تريد تحديث الحساب تلقائيًّا مع أي تغيّر في البيانات أو المعايير.
لجمع القيم الفريدة فقط في عمود وفقًا للمرشح أو الشرط في عمود آخر، يمكنك تطبيق الصيغة التالية:
1. في خلية فارغة (مثل)E2)، أدخل هذه الصيغة:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))قبل تأكيد الصيغة، تحقق بعناية من أن:
- A2:A12: النطاق الذي يحتوي على المعايير (الأسماء في هذا المثال).
- D2: الخلية التي يقع فيها شرطك المستهدف (مثل اسمٍ معيّن).
- B2:B12: نطاق القيم التي ترغب في جمعها بشكل فريد.
يمكنك تعديل هذه النطاقات حسب احتياجاتك بناءً على هيكل بياناتك الفعلي. تأكد من أن جميع النطاقات متساوية في الطول لتجنب أخطاء الصيغ.
2. لتفعيل صيغة المصفوفة هذه، اضغط علىCtrl + Shift + Enter في نفس الوقت بعد كتابتها. ستظهر أقواس مجعّدة حول الصيغة، مما يدل على أنها صيغة مصفوفة. بعد ذلك، اسحب مقبض التعبئة لأسفل لنسخ الصيغة إلى كل خلية مقابلة في عمود الملخص، ليحصل كل عنصر تلقائيًّا على مجموعه الفريد الصحيح.
نصيحة عملية:إذا كنت تستخدم Excel 365 أو Excel 2021، فقد تُبسِّط وظائف المصفوفة الديناميكية الجديدة مثلUNIQUE وSUMIFS بعض هذه الحسابات أكثر، لكن الصيغة أعلاه تعمل بشكل موثوق عبر العديد من إصدارات Excel.
=SUMIF(A2:B12, UNIQUE(D2), B2:B12)
التعامل مع معايير إضافية: =SUM(SUMIFS(نطاق_الجمع، نطاق_المعيار1، UNIQUE(نطاق_المعيار1)، [نطاق_المعيار2، معيار2]، ...)
احتياطات:
- تأكد من استخدام إدخال صيغة المصفوفة ()Ctrl + Shift + Enter) إذا كنت تستخدم Excel 2019 أو إصدارًا أقدم. أما في Excel 365/2021، فقد يكفي الضغط على مفتاح Enter العادي للصيغ الديناميكية.
- إذا كان نطاقك واسعًا جدًّا، فقد يؤدي استخدام أسلوب المصفوفة إلى تباطؤ الأداء؛ لذا نوصي بتصفية بياناتك أولًا أو اللجوء إلى طرق بديلة عند التعامل مع مجموعات بيانات ضخمة.
- انتبه جيدًا إلى المسافات الزائدة أو عدم اتساق نوع البيانات، فقد يؤدي التنسيق غير المتناسق للنصوص أو الأرقام إلى أخطاء في التطابق.
نصائح: إذا كنت بحاجة إلى جمع جميع القيم الفريدة بناءً علىمعيارين، فيمكن استخدام الصيغة الموسّعة التالية لمصفوفة الصيغ:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))هذه الصيغة مماثلة من حيث المبدأ، لكنها تدعم مرشحًا إضافيًّا منالعمود B(الآن مقارَنًا بـ)F2 كشرط ثانوي)، وتجمع القيم الفريدة منالعمود C. بعد إدخال هذه الصيغة في خلية الملخص المختارة، استخدمCtrl + Shift + Enter لتأكيدها، ثم طبّقها على صفوف الملخّص الأخرى حسب الحاجة.
اقتراح تلخيصي:رغم أن صيغ المصفوفة تُنتج نتائج دقيقة في معظم السيناريوهات، تأكد دائمًا من وجود تكرارات مخفية (مثل تلك التي تحتوي على مسافات زائدة أو اختلافات في تنسيق النص)، وتحقق من أن مناطق الملخص تستخرج من قوائم مُرشَّحة بدقة.
افتح سحر إكسل مع KUTOOLS AI
- التنفيذ الذكي: نفِّذ عمليات الخلايا، وحلِّل البيانات، وأنشئ المخططات البيانية — كل ذلك بأوامر بسيطة!
- الصيغ المخصصة: أنشئ صيغًا مخصصة لتبسيط سير عملك.
- برمجة VBA: اكتب وأَنفِذ أكواد VBA بسلاسة تامة.
- تفسير الصيغ: افهم الصيغ المعقدة بسهولة!
- ترجمة النصوص: اكسر الحواجز اللغوية في جداولك الإلكترونية!
جمع القيم الفريدة بناءً على المعايير باستخدام Kutools لـ Excel’s دمج متقدم للصفوف
Kutools لـ Excel’s دمج متقدم للصفوفيجعل جمع القيم الفريدة فقط بناءً على شرط معيّن أمرًا سهلًا للغاية! فببضع نقرات فقط، يمكنك تجميع بياناتك بذكاء وتطبيق منطق تلخيص مخصص—بدون صيغ، بدون تعقيد، فقط نتائج دقيقة.
الخطوة 1: حدد جدول البيانات الخاص بك
قم بتمييز الجدول بالكامل، بما في ذلك العناوين.
الخطوة 2: انتقل إلىKutools > المحتوى > دمج متقدم للصفوف.
الخطوة 3: قم بتعيين عمود التجميع
في مربع الحوار المنبثق، اختر العمود الذي تريد التجميع حسبه (مثل: الفاكهة)، وعيّنه كـمفتاح رئيسيفي قسمالعملية.
الخطوة 4: قم بتعيين الحقل لمجموع القيم الفريدة
حدد عمود المبيعات، ثم اختر نوعالحسابالذي تريده (مثل: المجموع) في قسمالعملية.
تلميح: يمكنك معاينة النتيجة المدمجة مباشرةً في مربع الحوار.
الخطوة 5: انقر علىموافق. أصبح الجدول الآن مجمّعًا حسب العميل، مع مجموع مبالغ المنتجات الفريدة لكل عميل.
طرق Excel المضمنة الأخرى: استخدم جدول بيانات محوري لتحليل المجموع الفريد
توفر ميزةPivotTable في Excel نهجًا مضمنًا وقويًا لتلخيص البيانات بناءً على معايير محددة. وعلى الرغم من أن PivotTables لا تحسب مجموع القيم الفريدة افتراضيًا، فقد أضاف إصدار Excel 2013 فصاعدًا دعمًا لحسابعدد القيم المميزة، ما يمكّنك من تحليل عدد الإدخالات الفريدة لحقل معين بسهولة. ورغم أن هذه الميزة لا تحسب مجموع القيم الفريدة مباشرةً، يمكنك دمجعدد القيم المميزةمع تعديل يدوي أو حقل محسوب للحصول على تلخيص مشابه.
المزايا:لا تتطلب PivotTables كتابة أكواد VBA أو حفظ الصيغ، بل توفّر واجهات سحب وإفلات مرنة للغاية. وهي مثالية للتقارير الدورية، والتحليلات المجمعة، والحصول على نظرة عامة سريعة، وكذلك عند التعاون بين الفرق. ومع ذلك، فهي الأنسب للتلخيص والتحليل، وليس لبناء صيغ تُستخدم في الحسابات الإضافية أو الأتمتة.
إليك كيفية استخدام PivotTable لتحليل المجموع الفريد:
- اختر نطاق البيانات الخاص بك (على سبيل المثال،)A1:B12 بما في ذلك العناوين)، ثم انتقل إلىإدراج > جدول محوري (PivotTable). في مربع الحوار، حدد ما إذا كنت تريد وضع الجدول المحوري في ورقة عمل جديدة أو في ورقة موجودة مسبقًا.
- في قائمة حقول الجدول المحوري، اسحبالاسمإلى منطقةالصفوف، واسحبالطلبإلى منطقةالقيم.
- لإدخالاتالطلبفي منطقةالقيم، انقر على السهم المنسدل > قيمة إعدادات الحقول > وعيّنها علىالمجموع (لعرض مجموع الطلبات الكلي بما في ذلك القيم المكررة).
القيود:
- ميزة العد الفريد (Distinct Count) متوفرة فقط في Excel 2013 والإصدارات الأحدث، بينما تتطلب الإصدارات السابقة جهدًا يدويًّا أكبر.
باختصار، بينما تُعد PivotTable ممتازة للتلخيص والتحليل التفاعلي، فكّر في دمجها مع الأساليب القائمة على الصيغ أو VBA إذا كنت بحاجة إلى حساب مجموع القيم الفريدة الحقيقي.
مقالات ذات صلة إضافية:
- جمع أعمدة متعددة بناءً على معيار واحد في Excel
- في Excel، قد تحتاج أحيانًا إلى جمع قيم أعمدة متعددة بناءً على معيار واحد. على سبيل المثال، لديك نطاق من البيانات كما في لقطة الشاشة التالية، وتريد الآن الحصول على المجموع الإجمالي لقيم «KTE» خلال الأشهر الثلاثة: يناير وفبراير ومارس.
- البحث الرأسي (Vlookup) وجمع القيم المطابقة في الصفوف أو الأعمدة في Excel
- يساعدك الجمع بين دالة البحث الرأسي (VLOOKUP) ودالة الجمع (SUM) على العثور بسرعة على المعيار المحدد وجمع القيم المقابلة في آنٍ واحد. في هذه المقالة، سنعرض لك طريقتين لاستخدام البحث الرأسي مع الجمع لإيجاد أول قيمة مطابقة أو جميع القيم المطابقة عبر الصفوف أو الأعمدة في Excel.
- جمع القيم بناءً على الشهر والسنة في Excel
- إذا كان لديك نطاق من البيانات يحتوي العمود A فيه على تواريخ، والعمود B على عدد الطلبات، وتحتاج الآن إلى جمع الأرقام بناءً على شهر وسنة من عمود التواريخ، فعلى سبيل المثال، تريد حساب إجمالي طلبات يناير 2016 للحصول على النتيجة المطلوبة. وفي هذه المقالة، سأعرض بعض الحيل الفعّالة لإنجاز هذه المهمة في Excel.
- جمع القيم بناءً على معايير نصية في Excel
- في Excel، هل سبق أن حاولت جمع القيم بناءً على معايير نصية موجودة في عمود آخر؟ على سبيل المثال، لديك نطاق من البيانات في ورقة العمل كما هو موضح في لقطة الشاشة التالية، وتريد الآن جمع جميع الأرقام في العمود B التي تتوافق مع القيم النصية في العمود A وفقًا لمعيار معيّن—مثل جمع الأرقام إذا كانت الخلايا في العمود A تحتوي على «KTE».
- جمع القيم بناءً على اختيار قائمة منسدلة في Excel
- كما هو موضح في لقطة الشاشة أدناه، لديك جدول يحتوي على عمود الفئة (Category) وعمود المبلغ (Amount)، وقد أنشأت قائمة منسدلة للتحقق من صحة البيانات يحتوي على جميع الفئات. عند اختيار أي فئة من قائمة منسدلة، تريد جمع جميع قيم الخلايا المقابلة في العمود B ووضع الناتج في خلية محددة. على سبيل المثال، عند اختيار الفئة CC من قائمة منسدلة، تحتاج إلى جمع القيم في الخليتين B5 وB8 والحصول على المجموع الكلي 40+70=110. كيف يمكنك تحقيق ذلك؟ الطريقة الموضحة في هذه المقالة يمكن أن تساعدك.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل