دالة SUMIF في Excel وتلخيصها بشروط متعددة

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

اليوم سنتحدث عن إحدى هذه الوظائف - سوميسليم.

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

الوصف التفصيلي للوظيفة SUMIFS

قبل النظر في الوظيفة سوميسليم، يجب أن تفهم أولاً ما هي النسخة الأبسط منه - سوميسلي، نظرًا لأن الوظيفة التي نفكر فيها تستند إلى ذلك. ربما يكون كل واحد منا على دراية بوظيفتين غالبًا ما يتم استخدامهما - مجموع (يقوم بجمع القيم) و إذا (يختبر قيمة مقابل شرط محدد).

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

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

الصيغة نفسها هي:

SUMIFS (sum_range ، condition_range1 ، condition1 ، [condition_range2 ، condition2] ، ...)

في الأماكن المناسبة ، من الضروري وضع مصفوفات من الخلايا المناسبة لحالة معينة. 

دعونا نلقي نظرة على الحجج بمزيد من التفصيل:

1. Sum_range. هذه الوسيطة ، بالإضافة إلى نطاق الشرط 1 والشرط 1 ، مطلوبة. إنها مجموعة من الخلايا التي يجب تلخيصها.
2. الشرط_النطاق 1. هذا هو النطاق الذي سيتم فيه فحص الشرط. يتم إقرانها مع الوسيطة التالية - Condition1. يتم إجراء جمع القيم المقابلة للمعيار داخل الخلايا المحددة في الوسيطة السابقة.
3. الشرط 1. تحدد هذه الوسيطة المعايير التي سيتم التحقق منها. يمكن ضبطه ، على سبيل المثال ، بهذه الطريقة: "> 32".
4. نطاق الشرط 2 ، الحالة 2 ... هنا ، يتم تعيين الشروط التالية بنفس الطريقة. إذا كانت هناك حاجة إلى تحديد أكثر من بضعة شروط ، فسيتم إضافة وسيطات نطاق الشرط 3 والشرط 3. بناء الجملة هو نفسه بالنسبة للوسيطات التالية.

تسمح الوظيفة بأقصى معالجة لما يصل إلى 127 زوجًا من الشروط والنطاقات. 

يمكنك استخدامه في عدة مناطق في وقت واحد (سنقدم القليل منها ، القائمة أطول في الواقع):

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

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

1. القدرة على وضع معايير متعددة. لماذا هذه ميزة؟ يمكنك استخدام الوظيفة العادية سوميسلي! وكل ذلك لأنه مناسب. ليست هناك حاجة لإجراء حسابات منفصلة لكل معيار. يمكن برمجة جميع الإجراءات مسبقًا ، حتى قبل ذلك. كيف سيتم تشكيل جدول البيانات. هذا هو توفير الوقت العظيم.
2. أتمتة. العصر الحديث هو عصر الأتمتة. فقط الشخص الذي يعرف كيفية أتمتة عمله بشكل صحيح يمكنه أن يكسب الكثير. هذا هو السبب في القدرة على إتقان Excel والوظيفة سوميسليم على وجه الخصوص ، مهم جدًا لأي شخص يريد بناء مستقبل مهني. تتيح لك معرفة وظيفة واحدة القيام بعدة إجراءات في وقت واحد ، كإجراء واحد. وهنا ننتقل إلى الفائدة التالية لهذه الميزة.
3. توفير الوقت. فقط بسبب حقيقة أن وظيفة واحدة تؤدي عدة مهام في وقت واحد.
4. بساطة. على الرغم من حقيقة أن بناء الجملة ثقيل جدًا للوهلة الأولى بسبب ضخامته ، في الواقع ، فإن منطق هذه الوظيفة بسيط للغاية. أولاً ، يتم تحديد مجموعة من البيانات ، ثم نطاق من القيم ، والتي سيتم التحقق منها للتأكد من مطابقتها لشرط معين. وبالطبع ، يجب أيضًا تحديد الشرط نفسه. وهكذا عدة مرات. في الواقع ، تعتمد هذه الوظيفة على بناء منطقي واحد فقط ، مما يجعلها أبسط من المعروفة VPR على الرغم من إمكانية استخدامه لنفس الأغراض ، مع مراعاة عدد أكبر من المعايير. 

ميزات استخدام وظيفة SUMIFS

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

1. يمكنك استخدام هذه الأنواع من القيم كشرط لتحديد الخلايا لإضافة المزيد من القيم المضمنة فيها: القيم الرقمية والتعبيرات المنطقية ومراجع الخلايا وما إلى ذلك. 
2. إذا تم التحقق من النص أو التعبيرات المنطقية أو العلامات الرياضية ، فسيتم تحديد هذه المعايير من خلال علامات الاقتباس.
3. لا يمكن استخدام مصطلحات أطول من 255 حرفًا.
4. من الممكن استخدام معايير تقريبية لاختيار القيم باستخدام أحرف البدل. تُستخدم علامة الاستفهام لاستبدال حرف واحد ، وعلامة الضرب (علامة النجمة) ضرورية لاستبدال أحرف متعددة. 
5. يتم تحويل القيم المنطقية الموجودة في نطاق الجمع تلقائيًا إلى قيم رقمية وفقًا لنوعها. وبالتالي ، تتحول القيمة "TRUE" إلى قيمة واحدة ، و "FALSE" - إلى صفر. 
6. إذا كان الخطأ #VALUE! يظهر خطأ في خلية ، فهذا يعني أن عدد الخلايا في نطاقات الشرط والتجميع مختلف. تحتاج إلى التأكد من أن أحجام هذه الحجج هي نفسها. 

أمثلة على استخدام دالة SUMIFS

المسمى الوظيفي سوميسليم ليس معقدًا كما يبدو للوهلة الأولى ، اتضح. ولكن لمزيد من الوضوح ، دعنا نلقي نظرة على بعض الأمثلة العملية لكيفية استخدام الوظيفة سوميسليم. سيسهل هذا الخوض في الموضوع.

النطاق الديناميكي لتجميع الشرط

لنبدأ بالمثال الأول. لنفترض أن لدينا جدولًا يحتوي على معلومات حول كيفية تعامل الطلاب مع المنهج في موضوع معين. هناك مجموعة من الدرجات ، ويتم تقييم الأداء على مقياس مكون من 10 نقاط. تتمثل المهمة في العثور على الدرجة الخاصة بامتحان الطلاب الذين تبدأ أسماؤهم بالحرف A ، والحد الأدنى من درجاتهم هو 5.

الجدول يبدو مثل هذا.

لكي نحسب النتيجة الإجمالية بناءً على المعايير الموضحة أعلاه ، نحتاج إلى تطبيق الصيغة التالية.

دعنا نصف الحجج بمزيد من التفصيل:

1. C3: C14 هو نطاق الجمع الخاص بنا. في حالتنا ، فإنه يتزامن مع نطاق الشرط. منه سيتم تحديد النقاط المستخدمة لحساب المبلغ ، ولكن فقط تلك التي تندرج تحت معاييرنا.
2. "> 5" هو شرطنا الأول.
3. B3: B14 هو نطاق التجميع الثاني الذي تتم معالجته لمطابقة المعيار الثاني. نرى أنه لا توجد مصادفة مع نطاق الجمع. من هذا نستنتج أن نطاق الجمع ونطاق الشرط قد يكون أو لا يكون متطابقًا. 
4. "A *" هو النطاق الثاني ، والذي يحدد اختيار العلامات فقط للطلاب الذين تبدأ أسماؤهم بالحرف A. في حالتنا ، تعني العلامة النجمية أي عدد من الأحرف. 

بعد الحسابات نحصل على الجدول التالي.

كما ترى ، لخصت الصيغة القيم بناءً على النطاق الديناميكي واستناداً إلى الشروط المحددة من قبل المستخدم.

الجمع الانتقائي حسب الشرط في Excel

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

الجدول نفسه يشبه هذا. 

لتحديد النتيجة النهائية ، نحتاج إلى مثل هذه الصيغة.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

نتيجة الحسابات التي أجرتها هذه الصيغة ، نحصل على النتيجة التالية.

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

دالة SUMIFS لجمع القيم عبر شروط متعددة

الآن دعنا نعطي مثالاً آخر للتوضيح. في هذه الحالة ، يظل الجدول كما هو في الحالة السابقة. 

نستخدم الصيغة التالية (لكننا نكتبها كصيغة صفيف ، أي أننا ندخلها من خلال مجموعة المفاتيح CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

بعد الوظيفة سوميسليم سوف يجمع مجموعة القيم بناءً على المعايير المحددة في الصيغة (أي دولتي الصين وجورجيا) ، يتم جمع المصفوفة الناتجة بالدالة المعتادة مجموع، والتي تتم كتابتها كصيغة مصفوفة.

إذا تم تمرير الشروط كثابت صفيف لأكثر من زوج واحد ، فستعطي الصيغة نتيجة غير صحيحة.

لنلقِ الآن نظرة على الجدول الذي يحتوي على المجاميع.

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

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