جمع الصفوف في Excel حسب الشرط. الجمع الانتقائي على أساس معيارين

وظيفة سوميفيتم استخدامه عندما يكون من الضروري جمع قيم النطاق الذي يستوفي شرطًا محددًا. على سبيل المثال، لنفترض أنك تريد جمع القيم الأكبر من 5 فقط في عمود من الأرقام. للقيام بذلك، يمكنك استخدام الصيغة التالية: =SUMIF(B2:B25،">5")

يعد هذا الفيديو جزءًا من البرنامج التعليمي لإضافة أرقام في Excel 2013.

نصيحة:

بناء الجملة

SUMIF(النطاق، الشرط، [sum_range])

الحجج الوظيفية سوميفموصوفة أدناه.

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

حالة. الحجة المطلوبة. شرط، على شكل رقم، أو تعبير، أو مرجع خلية، أو نص، أو دالة، يحدد الخلايا التي يجب جمعها. على سبيل المثال، يمكن تمثيل الشرط على النحو التالي: 32 أو ">32" أو B5 أو "32" أو "apples" أو TODAY().

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

Sum_range. حجة اختيارية. الخلايا التي يتم جمع قيمها إذا كانت تختلف عن الخلايا المحددة كـ يتراوح . إذا كانت الحجة sum_range تم حذفه، يقوم Excel بجمع الخلايا المحددة في الوسيطة يتراوح (نفس الخلايا التي ينطبق عليها الشرط).

في الحجة حالة يمكنك استخدام أحرف البدل: علامة الاستفهام ( ? ) والنجمة ( * ). تتطابق علامة الاستفهام مع أي حرف منفرد، كما تتطابق العلامة النجمية مع أي تسلسل من الأحرف. إذا كنت تريد العثور على علامة استفهام (أو علامة النجمة) مباشرة، فأنت بحاجة إلى وضع علامة التلدة أمامها ( ~ ).

ملحوظات

ترجع الدالة SUMIF نتائج غير صحيحة عند استخدامها لمطابقة سلاسل أطول من 255 حرفًا أو عند تطبيقها على السلسلة #VALUE!.

دعوى sum_range قد لا يكون بنفس حجم الوسيطة يتراوح . عند تحديد الخلايا الفعلية المراد جمعها، يتم استخدام الخلية العلوية اليسرى للوسيطة كخلية البداية sum_range ، ثم يتم جمع خلايا جزء النطاق المقابل للوسيطة في الحجم يتراوح . مثال:

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

أمثلة

مثال 1

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

مثال 2

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

مساء الخير

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

أستطيع أن أقول جنبًا إلى جنب أنه يمكن إجراء العديد من العمليات الحسابية والحسابية بدون استخدام مختلف أو SUMPRODUCT، ولكن إذا كنت بحاجة إلى إجراء تحديد معقد باستخدام علامات "أحرف البدل"، فأنت بالتأكيد بحاجة إلى استخدام بطل مقالتنا.

أولاً، دعونا نلقي نظرة على بناء الجملة الذي يستخدم:

=BDSUMM(نطاق قاعدة البيانات الخاصة بك؛ حقل البحث؛ حالة البحث)، أين

عند العمل مع وظيفة بدسومتجدر الإشارة إلى عدة شروط يجب الانتباه إليها عند العمل:

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

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

وظيفة BDSUMM معمعيار عددي واحد

لذا، دعونا أولاً نفكر في مثال بسيط بمعيار رقمي واحد، للقيام بذلك، حدد عمود "العائد" وأشر إلى أننا بحاجة إلى أشجار ذات عائد ">=10". للحصول على النتيجة، نحتاج إلى صيغة من هذا النوع (أوصي باستخدام المراجع المطلقة):

=BDSUMM($B$6:$G$12،E6،E2:E3),

حيث $B$6:$G$12 هو النطاق الذي سنجمع فيه، وE6 هو العمود الذي سنجمع فيه، وE2:E3 هو النطاق الذي أدخلنا فيه معايير الجمع. ونتيجة لذلك، وجدت الصيغة 3 مناصب ليصبح المجموع 34.

للحصول على نتيجة مماثلة، يمكنك أيضًا استخدام الصيغ التالية:

=SUMIF(E7:E12،">=10")

=SUMIF(E7:E12,E3)

دالة BDSUMM بمعيار نصي واحد

الآن دعونا نلقي نظرة على كيفية تصرف الدالة BDSUMM مع معايير النص، بشكل عام، يبقى كل شيء كما هو في المثال السابق، باستثناء كيفية الإشارة إلى معيار النص، ويتم الإشارة إليه فقط في هذا النموذج: ==s.Serovo " وبعد ذلك سوف تحصل على النتيجة، وإلا فلن تتمكن الصيغة من التعرف على المعيار الخاص بك. الآن نستبدل هذا المعيار في الصيغة ونحصل على:

=BDSUMM($B$6:$G$12،E6،C2:C3)كما نرى، حدثت تغييرات فقط في نطاق المعيار.

للحصول على نتيجة مماثلة ستحتاج:

=SUMIF(C7:C12,"s.Serovo";E7:E12)

الجمع بمعيارين في أعمدة مختلفة

المثال معقد من خلال استخدام معيارين، لكننا لن نستخدم أي شيء جديد بشكل أساسي، سنشير إلى المعيار النصي “قرية سيروفو” والمعيار العددي “>=10”، مع ترك حقل الجمع “الحصاد”، سنقوم الحصول على تغيير في الصيغة فقط للوسيطة الأخيرة، نتيجة لذلك. الآن سوف تبدو صيغتنا كما يلي:

=BDSUMM($B$6:$G$12،E6،C2:E3)، مرة أخرى ترى فقط التغييرات في عنوان نطاق المعايير.

يمكن الحصول على خيار بديل باستخدام ومثل هذا:

=SUMIFS(E7:E12,C7:C12,C3,E7:E12,E3)

=SUMIFS(E7:E12;C7:C12;"قرية سيروفو";E7:E12;">=10")

الجمع على أحد الشرطين في عمود واحد

دعونا نفكر في خيار آخر لكيفية استخدامه، لكننا الآن لن نستخدم معيارًا واحدًا، بل معيارًا مزدوجًا، ولكن لحقل واحد. ويجب أن يكون المعياران على خطوط مختلفة. يكمن جوهر الصيغة في حقيقة أنها تمر عبر نفس النطاق مرتين، مع حساب كل معيار على حدة. في هذا المثال، ستبدو الصيغة كما يلي:

=BDSUMM($B$6:$G$12،E6،C2:C4)، هنا نقوم بتغيير نطاق المعيار مرة أخرى، ولكن ليس في العرض، ولكن في الارتفاع.

وكبديل أيضًا، يمكنك استخدام مجموع الدالة SUMIF:

=SUMIF(C7:C12,C3,E7:E12)+SUMIF(C7:C12,C4,E7:E12).

الجمع على أحد الشرطين في عمودين مختلفين

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

=BDSUMM($B$6:$G$12,D6,C2:D4)، يتم الحفاظ على مبدأ تكوين الصيغة، باستثناء نطاق المعيار الذي يتضمن ثلاثة أسطر: عنوان ومعياران.

الجمع حسب معيارين نصيين في عمودين

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

=BDSUMM($B$6:$G$12,D6,B2:C3).

استخدام نتيجة الصيغة للحصول على معايير الاختيار والجمع

في هذا المثال للدالة BDSUMM، أستخدم صيغة لتحديد معيار الاختيار والجمع، في هذه الحالة سنجد الأشجار التي تجلب لنا الدخل ولتحديد الوسيطة الشرطية سنأخذ القيمة المتوسطة لمبيعات الفاكهة من الأشجار وكل ما هو فوق المتوسط ​​يهمنا. لتحديد القيمة المتوسطة التي ستصبح معيارنا "الحقيقي"، نقوم بإنشاء صيغة:

=G7>المتوسط($G$7:$G$12)، لا تنس إصلاح النطاق بحيث لا تنزلق لأسفل عندما تمر الصيغة عبر القيم، ولكن يجب أن تنزلق قيمة G7 فوق النطاق بأكمله لتحديد ما إذا كانت "FALSE" أو "TRUE".

ومن المهم جدًا أيضًا ألا تكون أسماء العناوين مكررة وفيها اختلاف، لذلك سأسمي حقل المعايير "متوسط". وبعد ذلك ستبدأ الصيغة في العمل، وسوف تمر عبر النطاق بأكمله $G$7:$G$12 لوجود قيمة متوسطة وعندما تتلقى نتيجة إيجابية "TRUE" سيتم جمعها. الصيغة التالية سوف تقوم بهذه المهمة:

=BDSUMM($B$6:$G$12,G6,$G$2:$G$3)

وإذا كنت مهتمًا جدًا بحل بديل للسؤال، فجرّب الخيار باستخدام الدالة SUMIF بهذا النموذج:

=SUMIF($G$7:$G$12،">"&AVERAGE($G$7:$G$12))

دالة BDSUMM تعتمد على ثلاثة معايير

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

=BDSUMM($B$6:$G$12,G6,$C$2:$G$4)

جمع حساس لحالة الأحرف حسب معيار النص

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

=صدفة("قرية إيفانوفو";C7)

لكن يمكننا الآن كتابة دالة تتحقق من مدى وجود المعيار المحدد، وعند استلام القيمة "TRUE"، ستجري عملية الجمع. في المثال، أشرت مرة واحدة على وجه التحديد حسب الشرط، وكما ترون، نجحت الصيغة في تحديد جميع التسويات ووجدت التسويات الصحيحة وحصلت على النتيجة "9". ولهذا الغرض تم استخدام الصيغة:

=BDSUMM($B$6:$G$12,D6,$C$2:$C$3)

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

حظا سعيدا لك في مساعيك!

"مقابل المال، يمكنك بالطبع الشراء كلب رائعتينولكن لا يمكن لأي مبلغ من المال أن يجعله يهز ذيله بسعادة.
"
د. بيلينغز

صياغة المشكلة

لدينا جدول المبيعات، على سبيل المثال، مثل هذا:

مهمة : تلخيص جميع الطلبات التي نفذها المدير غريغورييف لمتجر كوبيكا.

الطريقة الأولى: الدالة SUMIF

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

الطريقة الثانية. عمود المؤشر

دعونا نضيف عمودًا آخر إلى جدولنا، والذي سيكون بمثابة نوع من المؤشر: إذا كان الطلب من Kopeyka ومن Grigoriev، فستكون للخلية الموجودة في هذا العمود القيمة 1، وإلا فستكون 0. الصيغة التي يجب إدخالها في هذا العمود بسيط جدًا:

=(A2="كوبيكا")*(B2="غريغورييف")

تنتج المساواة المنطقية بين قوسين القيم TRUE أو FALSE، والتي هي نفسها في Excel مثل 1 و0. لذا، بما أننا نقوم بضرب هذه التعبيرات، فلن يصبح الواحد واحدًا إلا إذا كان كلا الشرطين صحيحين. الآن كل ما تبقى هو ضرب قيم المبيعات بقيم العمود الناتج وجمع المبالغ الناتجة:

الطريقة الثالثة: صيغة المصفوفة السحرية

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

SUM((A2:A26="Kopeyka")*(B2:B26="Grigoriev")*D2:D26)

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

الطريقة الرابعة. وظيفة قاعدة البيانات BDSUMM

في فئة قواعد البيانات يمكنك العثور على الوظيفةدسوم ، والتي يمكن أن تساعدنا أيضًا في حل مشكلتنا:

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

بدسوم (A1:D26;D1;F1:G2)

الطريقة الخامسة: معالج المجموع الجزئي

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

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

وأخيرًا، في الخطوتين الثالثة والرابعة، نشير إلى الخلية التي يجب أن يتم عرض النتيجة فيها. وننتهي إلى ما يلي:

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

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

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

ونرى ذلك في مثال SRT وGTR، اللذين تحولا إلى نوع من الدين الذي يتحكم في العلم والرأي العام. لا يمكن لأي قدر من الحقائق التي تتعارض معها التغلب على صيغة أينشتاين: "إذا كانت الحقيقة لا تتوافق مع النظرية، قم بتغيير الحقيقة" (في نسخة أخرى، "هل الحقيقة لا تتوافق مع النظرية؟ - الأسوأ بكثير بالنسبة للحقيقة" ").

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

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

أخبار المنتدى فرسان نظرية الأثير

10.30.2017 - 06:17: -> - كريم_خيداروف. 19/10/2017 - 04:24: -> - كريم_خيداروف. 10/11/2017 - 05:10: -> - كريم_خيداروف. 05.10.2017 - 11:03: