دالة SUMIFS، كيفية استخدام الدالة لجمع شروط متعددة في Excel

تعتبر وظيفة Sumifs في Excel مفيدة للغاية عندما يتعين عليك جمع العديد من الشروط. فيما يلي تفاصيل حول كيفية استخدام الدالة Sumifs في Microsoft Excel .

تعد وظيفة SUMIFS في Excel إحدى وظائف Excel الأساسية ، وهي وظيفة حسابية شائعة الاستخدام في Excel. لحساب الإجمالي في إكسيل سنستخدم الدالة SUM ، وإذا أردنا إضافة شرط معين إلى الدالة الإجمالي سنستخدم الدالة SUMIF . في الحالات التي يتطلب فيها جدول البيانات مجموعًا متعدد الشروط، يجب علينا استخدام الدالة SUMIFS. سترشدك المقالة أدناه إلى كيفية استخدام الدالة SUMIFS في Excel.

• MS Excel - الدرس 5: صيغ ووظائف Excel
• كيفية الجمع بين وظيفة Sumif ووظيفة Vlookup في Excel
• 3 طرق لحساب الإجماليات في Excel
• كيفية إصلاح خطأ الدالة SUM التي لا تتم إضافتها في Excel

جدول محتويات المقال

• بناء جملة الدالة SUMIFS في Excel
• لاحظ عند استخدام الدالة SUMIFS في Excel
• تعليمات لاستخدام الدالة SUMIFS في Excel
  • استخدم أحرف البدل
  • استخدم النطاقات المسماة مع الدالة SUMIFS
  • مثال على وظيفة SUMIFS في Excel
• حدود الدالة SUMIFS

بناء جملة الدالة SUMIFS في Excel

بناء جملة الدالة SUMIFS في Excel هو =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...).

هناك:

• Sum_range: هي الخلايا التي يجب جمعها في جدول البيانات، ويتم تجاهل القيم الفارغة والقيم النصية، والمعلمات المطلوبة.
• Criteria_range1: النطاق الذي يجب اختباره باستخدام معيار المعيار 1، هو قيمة مطلوبة.
• Criteria1: الشرط المطبق على معيار_النطاق1، يمكن أن يكون رقمًا أو تعبيرًا أو مرجع خلية لتحديد الخلايا الموجودة في معيار_النطاق1 التي سيتم جمعها، وهي أيضًا قيمة مطلوبة.
• Criteria_range2، ومعايير 2،…: نطاقات وشروط إضافية اختيارية، بحد أقصى 127 زوجًا من المعايير_النطاق، المعايير.

لاحظ عند استخدام الدالة SUMIFS في Excel

• بالنسبة لكل خلية في النطاق، سيتم جمع نطاق sum_range عند استيفاء جميع الشروط المقابلة التي تم تحديد صحتها لتلك الخلية.
• الخلايا الموجودة في sum_range التي تحتوي على TRUE تعتبر 1، والخلايا التي تحتوي على FALSE تعتبر 0.
• يجب أن يكون لكل معيار_نطاق نفس حجم التحديد مثل sum_range، ويجب أن يكون لدى معايير_النطاق وsum_range نفس عدد الصفوف ونفس عدد الأعمدة.
• يمكن أن تستخدم المعايير حرف علامة الاستفهام (؟) بدلاً من حرف واحد، والعلامة النجمية (*) بدلاً من السلسلة. إذا كان الشرط عبارة عن علامة استفهام أو علامة نجمية، فيجب عليك إدخال علامة ~ في المقدمة، وقيمة الشرط هي النص المضمن في ".
• يتم تطبيق شروط متعددة باستخدام منطق AND، أي المعايير 1 والمعايير 2، وما إلى ذلك.
• يجب أن يحتوي كل نطاق إضافي على نفس عدد الصفوف والأعمدة مثل النطاق الإجمالي، ولكن ليس من الضروري أن تكون النطاقات متجاورة. إذا قمت بتوفير نطاقات غير متطابقة، فسوف تحصل على خطأ #VALUE.
• يجب أن تكون السلسلة النصية في المعايير محاطة بعلامات اقتباس ("")، أي "apple"، ">32"، "jap*"
• لا يمكن تضمين مراجع الخلايا في المعايير بين علامتي اقتباس، على سبيل المثال "<>
• يمكن لـ SUMIF وSUMIFS التعامل مع النطاقات، لكن لا يمكنهما التعامل مع المصفوفات. هذا يعني أنه لا يمكنك استخدام دالات أخرى مثل YEAR في نطاق المعايير لأن النتيجة عبارة عن مصفوفة. إذا كنت بحاجة إلى هذه الوظيفة، استخدم الدالة SUMPRODUCT.
• يختلف ترتيب الوسائط بين الدالتين SUMIFS وSUMIF. نطاق المجموع هو الوسيطة الأولى في SUMIFS، ولكنه الوسيطة الثالثة في SUMIF.

تعليمات لاستخدام الدالة SUMIFS في Excel

استخدم أحرف البدل

أحرف البدل مثل "*" و"؟" يمكن استخدامها في وسيطة المعايير عند استخدام الدالة SUMIFS. سيساعدك استخدام أحرف البدل هذه في العثور على تطابقات مشابهة ولكن ليست تامة.

العلامة النجمية (*) - تتطابق مع أي سلسلة من الأحرف، والتي يمكن استخدامها بعد أو قبل أو مع المعايير المحيطة للسماح بالاستخدام الجزئي لمعايير البحث.

على سبيل المثال، إذا قمت بتطبيق المعايير التالية في الدالة SUMIFS:

• N * - يتضمن كافة الخلايا الموجودة في النطاق الذي يبدأ بـ N
• * N - يتضمن جميع الخلايا في النطاق المنتهي بـ N
• *N* - الخلايا التي تحتوي على N

علامة الاستفهام (؟) - تتطابق مع أي حرف واحد. لنفترض أنك قمت بتطبيق N?r كمعيار. "؟" هنا سوف يحل محل حرف واحد. سوف يتوافق N?r مع North وNo وما إلى ذلك. ومع ذلك، فإنه لن يأخذ في الاعتبار الاسم.

ماذا لو كانت البيانات المقدمة تحتوي على علامة النجمة أو علامة استفهام حقيقية؟

في هذه الحالة، يمكنك استخدام التلدة (~). تحتاج إلى كتابة "~" قبل علامة الاستفهام في هذه الحالة.

استخدم النطاقات المسماة مع الدالة SUMIFS

النطاق المسمى هو الاسم الوصفي لمجموعة من الخلايا أو نطاق من الخلايا في ورقة العمل. يمكنك استخدام النطاقات المسماة أثناء استخدام الدالة SUMIFS.

مثال على وظيفة SUMIFS في Excel

سنقوم بحساب الإجمالي من خلال جدول البيانات أدناه مع عدد من الشروط المختلفة.

مثال 1 : احسب إجمالي المنتجات التي يبيعها موظف Hoai بسعر وحدة أقل من 400000 دونج فيتنامي.

في مربع إدخال النتيجة، ندخل الصيغة =SUMIFS(D2:D7,C2:C7,Hoai),E2:E7,<400000") ثم ="" اضغط على ="">

هناك:

• D2:D7 هي المنطقة التي يجب حساب العنصر الإجمالي فيها.
• C2:C7 هو نطاق قيمة شرط اسم الموظف.
• "Hoai" هو شرط اسم الموظف الذي يحتاج إلى حساب عدد العناصر الموجودة في المنطقة C2:C7.
• E2:E7 هي حالة العنصر.
• "400,000" هو الشرط لاحتواء العناصر في المناطق E2:E7.

والنتيجة هي العدد الإجمالي للعناصر كما هو موضح أدناه.

مثال 2 : احسب إجمالي عدد العناصر التي يبيعها الموظفون الروس بالرقم التسلسلي <>

في مربع إدخال النتيجة، نقوم بإدخال الصيغة =SUMIFS(D2:D7,C2:C7,=russia),A2:A7,<5") ثم ="" اضغط على ="" enter.="" في = "" اسم = "" الضرب = "" قرص = "" "nga" = "أدخل = "" علامة = "أو" إزالة = "" علامة = "حتى">

ونتيجة لذلك، نحصل على إجمالي عدد العناصر التي باعها الموظفون الروس بأرقام تسلسلية < 5 = "" وهو 550 عنصر = "">

مثال 3 : احسب إجمالي عدد المنتجات التي يبيعها الموظفون الروس، باستثناء منتجات الأوشحة.

في مربع إدخال النتيجة، نقوم بإدخال الصيغة =SUMIFS(D2:D7,C2:C7,“russia”،B2:B7,“<>scarf”). حيث يتم استخدام العلامة <> لاستبعاد كائن معين في منطقة البيانات كشرط.

والنتيجة هي العدد الإجمالي للمنتجات كما هو موضح أدناه.

مثال 4:

احسب إجمالي جميع الإحصائيات للجيل الأول من البوكيمون من النوع المائي:

الشرط هنا هو أن الماء والتوليد يساويان 1.

ملاحظة: تستمر مجموعة البيانات الكاملة بعد الصف 14، وصولاً إلى الصف 759.

فيما يلي التفاصيل خطوة بخطوة:

1. حدد الخلية H3 .
2. النوع =SUMIFS.
3. انقر نقرًا مزدوجًا فوق الأمر SUMIFS.
4. حدد النطاق الإجمالي كـ C2:C759.
5. يكتب,
6. تحديد نطاق للشرط الأول B2:B759(قيمة النوع 1)
7. تحديد المعايير (الخلية F3، ذات القيمة Water)
8. يكتب ،
9. تحديد نطاق للشرط الثاني من D2:D759(قيمة التوليد)
10. يكتب,
11. تحديد المعايير (الخلية G3 لها القيمة 1).
12. اضغط دخول .

ملاحظة: يمكنك إضافة المزيد من الشروط عن طريق تكرار الخطوات من 9 إلى 12 قبل الضغط على Enter.

تقوم هذه الوظيفة الآن بحساب مجموع الإحصائيات الإجمالية للجيل الأول من بوكيمون الماء. ويمكن تكرار هذه الوظيفة للأجيال القادمة لمقارنتها.

فيما يلي بعض الأمثلة حول كيفية استخدام الدالة SUMIFS، وحساب القيمة الإجمالية مع العديد من الشروط المدمجة. يجب علينا ترتيب النطاقات والشروط المصاحبة لها بشكل صحيح حتى يتمكن Excel من التعرف على صيغة الدالة للحساب.

حدود الدالة SUMIFS

تحتوي الدالة SUMIFS على بعض القيود التي يجب أن تكون على دراية بها:

• يتم دمج الشروط في SUMIFS باستخدام المنطق AND. بمعنى آخر، يجب أن تكون جميع الشروط صحيحة حتى يتم تضمين الخلية في الإجمالي. لتجميع الخلايا باستخدام منطق OR، يمكنك استخدام حل بديل في الحالات البسيطة.
• تتطلب الدالة SUMIFS نطاقات فعلية لجميع وسيطات النطاق؛ لا يمكنك استخدام مصفوفة. هذا يعني أنه لا يمكنك القيام بأشياء مثل استخراج السنة من تاريخ داخل الدالة SUMIFS. لتغيير القيم التي تظهر في وسيطة النطاق قبل تطبيق المعايير، تعد الدالة SUMPRODUCT حلاً مرنًا.
• SUMIFS غير حساس لحالة الأحرف. لجمع القيم بناءً على شرط حساس لحالة الأحرف، يمكنك استخدام صيغة تعتمد على الدالة SUMPRODUCT مع الدالة EXACT.

الطريقة الأكثر شيوعًا للتغلب على القيود المذكورة أعلاه هي استخدام الدالة SUMPRODUCT. في الإصدارات الحالية من Excel، هناك خيار آخر وهو استخدام الدالتين BYROW وBYCOL الأحدث.

كيفية إصلاح الخطأ #VALUE في دالة SUMIFS في Excel

المشكلة: تشير الصيغة إلى الخلايا الموجودة في مصنف مغلق

ستؤدي الدالة SUMIF التي تشير إلى خلية أو نطاق من الخلايا في مصنف مغلق إلى حدوث خطأ #VALUE!.

ملاحظة: هذا خطأ شائع. غالبًا ما تواجه هذه الرسالة أيضًا عند استخدام وظائف Excel الأخرى مثل COUNTIF وCOUNTIFS وCOUNTBLANK...

حل:

افتح المصنف الموجود في هذه الصيغة، ثم اضغط على F9 لتحديث الصيغة. يمكنك أيضًا حل المشكلة باستخدام الدالتين SUM وIF في صيغة صفيف.

المشكلة: سلسلة المعايير أطول من 255 حرفًا

تقوم الدالة SUMIFS بإرجاع نتائج خاطئة عند محاولة دمج سلاسل أطول من 255 حرفًا.

حل:

قم بتقصير السلسلة إن أمكن. إذا لم يكن ذلك ممكنًا، فاستخدم الدالة CONCATENATE أو عامل التشغيل & لتقسيم القيمة إلى سلاسل متعددة. على سبيل المثال:

=SUMIF(B2:B12,"long string"&"another long string")

المشكلة: في SUMIFS، الوسيطة معايير_النطاق غير متوافقة مع الوسيطة مجموع_النطاق.

يجب أن تكون وسيطة النطاق هي نفسها دائمًا في SUMIFS. ويعني ذلك أن الوسيطتين Standard_range وsum_range يجب أن تشيرا إلى نفس عدد الصفوف والأعمدة.

في المثال التالي، تُرجع الصيغة إجمالي مبيعات التفاح اليومية في بلفيو. ومع ذلك، فإن الوسيطة sum_range (C2:C10) لا تتوافق مع عدد الصفوف والأعمدة في المعايير_النطاق (A2:A12 & B2:B12). سيؤدي استخدام الصيغة =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)إلى ظهور خطأ #VALUE.

حل:

قم بالتغيير sum_rangeإلى C2:C12 وحاول الصيغة مرة أخرى.

أتمنى لك النجاح!