كيفية جمع القيم الفريدة بناءً على معايير في Excel؟
عند العمل مع مجموعات بيانات Excel—مثل سجلات الطلبات، السجلات المالية، أو نتائج الاستبيانات—قد تواجه غالبًا الحاجة إلى حساب مجموع القيم الفريدة من عمود واحد بناءً على فلاتر أو معايير في عمود آخر. على سبيل المثال، فكر في جدول بيانات يحتوي على عمودين: الاسم والطلب. إذا كنت تريد حساب مجموع القيم الفريدة فقط للطلبات لكل اسم (مع تجاهل أي قيم متكررة)، كيف يمكنك تحقيق ذلك بفعالية في Excel؟ هذا هو مطلب شائع في العديد من سيناريوهات الأعمال أو تحليل البيانات حيث يؤدي جمع جميع العناصر المتطابقة ببساطة إلى نتائج مبالغ فيها بسبب التكرارات.
اللقطة الشاشة التوضيحية أدناه توضح سيناريو نموذجي: بالنظر إلى قائمة من الأسماء وقيم الطلبات المقابلة لها—including التكرارات—تريد تلخيص البيانات عن طريق جمع القيم الفريدة لكل اسم على حدة.
تشمل التحديات الشائعة في هذه المهمة تحديد الإدخالات الفريدة بناءً على معايير معينة، وضمان احتساب الحدوث الأول فقط، ومنع الأخطاء اليدوية التي قد تنشأ عن نسخ ولصق البيانات المرشحة. يمكن أن تساعدك عدة طرق عملية في Excel، بما في ذلك صيغ الصفائف، Kutools، واستعلام الطاقة، في حل هذه المشكلة، وكل منها مناسب لسيناريوهات استخدام مختلفة.
- جمع القيم الفريدة بناءً على معيار أو أكثر باستخدام صيغ الصفائف
- جمع القيم الفريدة بناءً على معايير باستخدام دمج الصفوف المتقدم من Kutools لـ Excel
- طرق 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(sum_range, criteria_range1, UNIQUE(criteria_range1), [criteria_range2, criteria2], ...)
احتياطات:
- تأكد من استخدام إدخال صيغة الصفائف (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 لتأكيدها، ثم طبقها على صفوف الملخص الأخرى حسب الحاجة.
اقتراح ملخص: بينما توفر صيغ الصفائف نتائج دقيقة لأغلب السيناريوهات، دائمًا قم بمراجعة وجود التكرارات المخفية (مثل تلك التي تحتوي على مسافات إضافية أو اختلافات في تنسيق النصوص) وتأكد من أن مناطق الملخص الخاصة بك تسحب من القوائم المرشحة بدقة.
اكتشف سحر Excel مع Kutools AI
- التنفيذ الذكي: قم بعمليات الخلية، وتحليل البيانات، وإنشاء المخططات - كل ذلك يتم من خلال أوامر بسيطة.
- الصيغ المخصصة: قم بإنشاء صيغ مصممة خصيصًا لتسهيل سير عملك.
- برمجة VBA: كتابة وتطبيق أكواد VBA بسهولة.
- تفسير الصيغ: فهم الصيغ المعقدة بسهولة.
- ترجمة النصوص: تجاوز الحواجز اللغوية داخل جداول البيانات الخاصة بك.
جمع القيم الفريدة بناءً على معايير باستخدام دمج الصفوف المتقدم من Kutools لـ Excel
ميزة دمج الصفوف المتقدم من Kutools لـ Excel يجعل جمع القيم الفريدة فقط بناءً على شرط معين أمرًا سهلاً بلا عناء! بنقرات قليلة، يقوم بذكاء بتجميع بياناتك وتطبيق منطق تلخيص مخصص—بدون صيغ، بدون متاعب، فقط نتائج دقيقة.
الخطوة 1: اختر جدول بياناتك
قم بتحديد الجدول بالكامل بما في ذلك العناوين.
الخطوة 2: اذهب إلى Kutools > Content > دمج الصفوف المتقدم.
الخطوة 3: تعيين عمود التجميع
في مربع الحوار المنبثق، اختر العمود الذي تريد التجميع بناءً عليه (مثل الفاكهة)، واضبطه كمفتاح رئيسي في قسم العملية.
الخطوة 4: تعيين الحقل لجمع القيم الفريدة
اختر عمود المبيعات، واضبط الحساب الذي تريده (مثل الجمع) في قسم العملية.
نصيحة: يمكنك معاينة النتيجة المدمجة في مربع الحوار.
الخطوة 5: انقر OK أصبح الجدول الآن مجمّعًا حسب العميل، كل واحد مع مجموع القيم الفريدة للمبالغ المنتج.
طرق Excel المدمجة الأخرى: استخدم جدول محوري لتحليل الجمع الفريد
توفر ميزة جدول PivotTable في Excel نهجًا مدمجًا قويًا آخر لتلخيص البيانات بناءً على معايير. بينما لا تقوم جداول PivotTables بجمع القيم الفريدة مباشرةً بشكل افتراضي، فإنها تدعم حساب العد المميز بعد Excel 2013، مما يساعدك على تحليل عدد الإدخالات المميزة لحقل محدد. على الرغم من أنه لا يحسب مجموع القيم الفريدة مباشرةً، يمكنك استخدام العد المميز مع التعديل اليدوي أو حقلاً محسوبًا لتسهيل ملخص مشابه.
المزايا: لا تتطلب جداول PivotTables حفظ الصيغ أو كتابة VBA، وتقدم واجهات سحب وإسقاط مرنة للغاية. مناسبة للتقارير الدورية، التحليل الجماعي، ملخصات سريعة، أو عند التعاون عبر الفرق. ومع ذلك، فهي الأفضل للملخصات والتحليل، وليس لبناء الصيغ للمزيد من الحسابات أو الأتمتة.
إليك كيفية استخدام PivotTable لتحليل الجمع الفريد:
- اختر نطاق بياناتك (على سبيل المثال، A1:B12 بما في ذلك العناوين)، واذهب إلى إدراج > PivotTable. في مربع الحوار، اختر ما إذا كنت تريد وضع PivotTable في ورقة عمل جديدة أو واحدة موجودة.
- في قائمة حقول PivotTable، اسحب الاسم إلى منطقة الصفوف، والطلب إلى منطقة القيم.
- بالنسبة لإدخالات الطلب في منطقة القيم، انقر فوق السهم المنسدل > إعدادات حقل القيمة > اضبط على الجمع (يُظهر مجموع الطلبات الإجمالي بما في ذلك التكرارات).
الحدود:
- وظيفة العد المميز متاحة فقط في Excel 2013 أو الأحدث؛ الإصدارات السابقة تتطلب عملًا يدويًا أكثر.
باختصار، بينما تتميز PivotTable بأنها ممتازة للتلخيص والتحليل التفاعلي، فكر في استخدامها مع أساليب الصيغ أو VBA إذا كنت بحاجة إلى حسابات مجموع فريدة حقيقية.
مقالات ذات صلة:
- جمع أعمدة متعددة بناءً على معيار واحد في Excel
- في Excel، قد تحتاج دائمًا إلى جمع أعمدة متعددة بناءً على معيار واحد. على سبيل المثال، لدي مجموعة من البيانات كما هو مبين في لقطة الشاشة التالية، الآن أريد الحصول على القيم الإجمالية لـ KTE في ثلاثة أشهر - يناير، فبراير ومارس.
- Vlookup وجمع التطابقات في الصفوف أو الأعمدة في Excel
- استخدام دالة vlookup وجمع القيم يساعدك بسرعة في العثور على المعايير المحددة وجمع القيم المقابلة في الوقت نفسه. في هذه المقالة، سنعرض لك طريقتين للبحث عن القيم المطابقة الأولى أو كل القيم المتطابقة في الصفوف أو الأعمدة في Excel.
- جمع القيم بناءً على الشهر والسنة في Excel
- إذا كان لديك نطاق من البيانات، يحتوي العمود A على بعض التواريخ والعمود B على عدد الطلبات، الآن تحتاج إلى جمع الأرقام بناءً على الشهر والسنة من عمود آخر. في هذه الحالة، أريد حساب إجمالي الطلبات لشهر يناير 2016 للحصول على النتيجة التالية. وفي هذه المقالة، سأتحدث عن بعض الحيل لحل هذه المهمة في Excel.
- جمع القيم بناءً على معايير نصية في Excel
- في Excel، هل جربت جمع القيم بناءً على معايير نصية في عمود آخر؟ على سبيل المثال، لدي نطاق من البيانات في ورقة العمل كما هو مبين في لقطة الشاشة التالية، الآن أريد جمع كل الأرقام في العمود B المقابلة للقيم النصية في العمود A التي تفي بشروط معينة، مثل جمع الأرقام إذا كانت الخلايا في العمود A تحتوي على KTE.
- جمع القيم بناءً على اختيار قائمة منسدلة في Excel
- كما هو موضح في لقطة الشاشة أدناه، لديك جدول يحتوي على عمود "الفئة" وعمود "المبلغ"، وقد أنشأت قائمة منسدلة للتحقق من صحة البيانات تحتوي على جميع الفئات. عند اختيار أي نوع من الفئات من القائمة المنسدلة، تريد جمع جميع القيم المقابلة للخلايا في العمود B وتعبئة النتيجة في خلية محددة. على سبيل المثال، عند اختيار الفئة CC من القائمة المنسدلة، تحتاج إلى جمع القيم في الخلية B5 وB8 والحصول على الرقم الإجمالي 40+70=110. كيف يمكن تحقيق ذلك؟ الطريقة في هذه المقالة يمكن أن تساعدك.
أفضل أدوات زيادة الإنتاجية لمجموعة Office
عزز مهاراتك في Excel مع Kutools لـ Excel، وتمتع بكفاءة غير مسبوقة. يقدم Kutools لـ Excel أكثر من300 ميزة متقدمة لتعزيز الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
Office Tab يجلب تجربة التبويبات إلى Office، ويجعل عملك أسهل بكثير
- فعّل تحرير وقراءة المستندات عبر التبويبات في Word وExcel وPowerPoint
- افتح وأنشئ مستندات متعددة في تبويبات جديدة ضمن نفس النافذة، بدلاً من فتحها في نوافذ جديدة.
- يمنحك زيادة إنتاجية تصل إلى50% ويوفر عليك مئات النقرات يومياً!