وظيفة الخلاصة ، وكذلك الخلاطات بمعايير اثنين | اكسل للجميع
وظيفة SUMMESLE ، وكذلك SUMMESLES بمعيارين
تسلل »11 يونيو 2011 ديمتري 243582 وجهات النظر تخيل جدولاً تُدرج فيه أسماء الإدارات (أو الحسابات ، أو أي شيء آخر) في صفوف متتالية. 
جمع الخلايا حسب المعيار
من الضروري حساب المبلغ الإجمالي لكل قسم. كثيرون يفعلون ذلك مع مرشح والكتابة مع الأقلام في الخلايا.
على الرغم من أنه يمكن القيام به بسهولة وببساطة مع وظيفة واحدة فقط - SUMMESLI .
SUMMESLES (SUMIF) - يلخص الخلايا التي تلبي حالة معينة (يمكن تحديد شرط واحد فقط). يمكن أيضًا استخدام هذه الوظيفة إذا تم تقسيم الجدول إلى أعمدة حسب الفترات (شهريًا ، في كل شهر ، ثلاثة أعمدة - الدخل | النفقات | الفرق) وتحتاج إلى حساب المبلغ الإجمالي لجميع الفترات فقط حسب الدخل والمصروفات والفرق.
هناك ثلاث وسيطات في المجموع لـ SUMMESLI: النطاق ، المعيار ، Range_Summing .
= الموجز (A1: A20000 ؛ A1 ؛ B1: B20000)
= SUMIF (A1: A20000 ، A1 ، B1: B20000)
- النطاق (A1: A20000) - يشير إلى النطاق مع المعايير. أي العمود الذي سيتم فيه البحث عن القيمة المشار إليها بواسطة وسيطة المعيار .
- المعيار (A1) هو القيمة (نص أو رقمي ، وكذلك التاريخ) التي يجب العثور عليها في النطاق . قد تحتوي على أحرف البدل "*" و "؟". أي تحديد المعيار "* الكتلة *" لتلخيص القيم التي تحدث فيها كلمة "الكتلة". في الوقت نفسه ، يمكن أن تحدث كلمة "الكتلة" في أي مكان في النص ، أو يمكن أن يكون هناك كلمة واحدة فقط في الخلية. وتحديد "الكتلة *" ، سيتم تلخيص جميع القيم التي تبدأ ب "الكتلة". "؟" - يستبدل حرفًا واحدًا فقط ، أي عند تحديد "mas؟ a" ، يمكنك تلخيص الخطوط ذات القيمة "mass" و "mask" بالقيمة ، إلخ.
إذا كان المعيار مكتوبًا في خلية ولا تزال بحاجة إلى استخدام أحرف البدل ، فيمكنك إنشاء رابط إلى هذه الخلية عن طريق إضافة الحرف الضروري. افترض أنك بحاجة إلى تلخيص القيم التي تحتوي على كلمة "الإجمالي". تتم كتابة الكلمة "total" في الخلية A1 ، بينما في العمود A قد تكون هناك قيم إملائية متنوعة تحتوي على الكلمة "total": "الإجماليات لشهر يونيو" ، "الإجماليات لشهر يوليو" ، "الإجماليات لشهر مارس". الصيغة يجب أن تبدو كما يلي:
= SUMMERS (A1: A20000؛ "*" & A1 & "*"؛ B1: B20000)
"*" & A1 & "*" - تجمع علامة & (علامة الضم) عدة قيم في واحدة. أي ستكون النتيجة "* النتيجة *".
لفهم مبدأ كيفية عمل الصيغ بشكل أفضل ، من الأفضل استخدام أداة " حساب الصيغة" : كيفية عرض الخطوات لحساب الصيغ
يجب إرفاق جميع المعايير والمعايير النصية ذات العلامات المنطقية والرياضية بعلامات اقتباس مزدوجة (= SUMMESLI (A1: A20000؛ "total"؛ B1: B20000)). إذا كان المعيار رقمًا ، فلن تكون علامات الاقتباس مطلوبة. إذا كنت تريد العثور على علامة استفهام أو علامة نجمية مباشرةً ، فأنت بحاجة إلى وضع علامة التلدة (~) أمامها.
حول التلدة وميزاته يمكن العثور عليها في هذه المقالة: كيفية استبدال / إزالة / العثور على النجمة؟ - يحدد Sum_Range (B1: B20000) (وسيطة اختيارية) - نطاق المبالغ أو القيم الرقمية المطلوب جمعها.
كيف تعمل: تقوم الدالة بالبحث في النطاق عن القيمة المحددة بواسطة وسيطة معيار ، وعندما يتم العثور على تطابق ، تجمع البيانات المشار إليها بواسطة وسيطة Range_Amount. أي إذا كان لدينا اسم قسم في العمود A ومبلغ في العمود B ، فإن تحديد إدارة التطوير كمعيار سينتج عنه مجموع جميع قيم العمود B ، في مقابل العثور على قسم التطوير في العمود A. في الواقع ، قد لا يكون SumArrangement بنفس حجم وسيطة Range وهذا لن يتسبب في حدوث خطأ في الوظيفة نفسها. ومع ذلك ، عند تعريف الخلايا للتجميع ، سيتم استخدام الخلية اليسرى العلوية للوسيطة Range_Amount كخلية بداية للتجميع ، ثم يتم جمع الخلايا المقابلة في الحجم والشكل لوسيطة Range.
بعض الميزات
الوسيطة الأخيرة للدالة (Sum_And_Band: B1: B20000) اختيارية. هذا يعني أنه لا يمكن تحديدها. إذا لم تقم بتحديدها ، فستقوم الوظيفة بإضافة القيم المحددة بواسطة وسيطة النطاق . ما هذا؟ على سبيل المثال ، تحتاج إلى الحصول على مجموع الأرقام التي تزيد عن الصفر فقط. في العمود أ من المبلغ. ثم ستبدو الوظيفة هكذا:
= SUMMERS (A1: A20000؛ "> 0")
ما يجب مراعاته: يجب أن يكون range_summing والنطاق مساوٍ في عدد الأسطر. خلاف ذلك ، يمكنك الحصول على نتيجة خاطئة. على النحو الأمثل ، إذا كان سيبدو في الصيغ التي قدمتها: يبدأ نطاق ومجموع الملخصات من سطر واحد لهما نفس عدد الخطوط: A1: A20000 ؛ B1: B20000
الجمع على اثنين أو أكثر من المعايير
ولكن ماذا تفعل عندما تكون معايير الجمع 2 وأكثر؟ لنفترض أنك بحاجة إلى تلخيص المبالغ التي تخص إدارة واحدة وفقط لتاريخ معين. يمكن للمالكين السعداء لإصدارات Office 2007 وما فوق استخدام وظيفة SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50 ؛ $ A $ 2: $ A $ ؛ $ I $ 3 ؛ $ B $ 2: $ B $ 50 ؛ $ H8)
$ C $ 2: $ C $ 50 - range_summing. تحدد الوسيطة الأولى نطاق الخلايا التي تحتوي على الكميات التي سيتم جمعها في واحدة.
$ A $ 2: $ A $ 50 ، $ B $ 2: $ B $ 50 - Range_Criteria. يحدد نطاق الخلايا التي تريد البحث فيها عن تطابق حسب المعيار.
$ I $ 3 ، H8 $ - المعيار. هنا ، كما هو الحال في SUMMESLI ، يُسمح باستخدام أحرف البدل * و ؟ وهم يعملون بنفس الطريقة.
تفاصيل تحديد الوسائط: أولاً ، يتم تحديد نطاق المعيار (يتم ترقيمها) ، ثم تتم الإشارة إلى القيمة (المعيار) مباشرةً بفاصلة منقوطة ، والتي يجب العثور عليها في هذا النطاق - $ A $ 2: $ A $ ؛ $ I $ 3. ولا شيء غير ذلك. يجب ألا تحاول تحديد جميع النطاقات أولاً ، ثم المعايير الخاصة بها - إما أن تقوم الوظيفة بإعطاء خطأ ، أو لن تلخص ما هو ضروري.
تتم مقارنة جميع الشروط وفقًا للمبدأ الأول. وهذا يعني أنه إذا تم استيفاء جميع الشروط المذكورة. في حالة عدم تلبية شرط واحد على الأقل ، تتخطى الوظيفة الخط ولا تضيف أي شيء.
بالنسبة إلى SUMMERS ، يجب أن يكون نطاقات الجمع والمعايير متساوية في عدد الصفوف.
لأن ظهرت SUMMESLIMN فقط في إصدارات Excel ، بدءًا من عام 2007 ، فكيف يمكن أن يكون تعيس مستخدمي الإصدارات السابقة في مثل هذه الحالات؟ بسيط جدا: استخدم وظيفة أخرى - SUMPRODUCT. لن أرسم الحجج ، لأنه هناك الكثير منهم وكلهم صفائف القيم. هذه الوظيفة تضاعف المصفوفات المشار إليها بواسطة الوسائط. سأحاول وصف المبدأ العام لاستخدام هذه الوظيفة لتلخيص البيانات على عدة شروط.
لحل مشكلة الجمع بواسطة عدة معايير ، ستبدو الوظيفة بالشكل التالي:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5) ؛ $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ - نطاق التاريخ. $ I $ 3 هو تاريخ المعيار الذي من الضروري جمع البيانات فيه.
$ B $ 2: $ B $ 50 - أسماء الأقسام. H5 - اسم القسم ، البيانات التي يجب تلخيصها.
$ C $ 2: $ C $ 50 - النطاق بمبالغ.
نحن نحلل المنطق ، لأن للكثيرين ، سيكون غير واضح تماما فقط من خلال النظر في هذه الوظيفة. إذا فقط لأنه في مساعدة لم يتم وصف هذا التطبيق. لمزيد من سهولة القراءة ، قم بتقليل حجم النطاقات:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5) ؛ $ C $ 2: $ C $ 5)
لذلك ، التعبير ($ A $ 2: $ A $ 5 = $ I $ 3) و ($ B $ 2: $ B $ 5 = H5) منطقيتان وإرجاع صفائف FALSE و TRUE منطقية. صواب إذا كانت خلية النطاق $ A $ 2: $ A $ 5 تساوي قيمة الخلية $ I $ 3 وكانت خلية النطاق $ B $ 2: $ B $ 5 تساوي قيمة الخلية H5. أي لدينا ما يلي:
= SUMPRODUCT ({FALSE؛ TRUE؛ TRUE؛ FALSE} * {FALSE؛ FALSE؛ TRUE؛ FALSE}؛ $ C $ 2: $ C $ 50)
كما ترون ، في المصفوفة الأولى هناك مباراتان للشرط ، والثانية. علاوة على ذلك ، يتم ضرب هاتين الصفيفتين (علامة الضرب (*) مسؤولة عن هذا). عند حدوث الضرب ، يحدث التحويل الضمني للصفيفين FALSE و TRUE إلى ثوابت رقمية 0 و 1 ، على التوالي ({0 ؛ 1 ؛ 1 ؛ 0} * {0 ؛ 0 ؛ 1 ؛ 0}). كما تعلم ، عندما نضرب بصفر ، نحصل على صفر. والنتيجة هي مجموعة واحدة:
= SUMPRODUCT ({0؛ 0؛ 1؛ 0}؛ $ C $ 2: $ C $ 50)
ثم يتم ضرب الصفيف {0؛ 0؛ 1؛ 0} بمجموعة من الأرقام في النطاق $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0؛ 0؛ 1؛ 0}؛ {10؛ 20؛ 30؛ 40})
ونتيجة لذلك ، نحصل على 30. ما نحتاجه - نحصل فقط على المبلغ الذي يفي بالمعايير. إذا كان هناك أكثر من مبلغ واحد يفي بالمعايير ، فسيتم تلخيصها.
الاستفادة من SUMMYROIZV
إذا كانت الوسيطات تحتوي على علامة الجمع بدلاً من علامة الضرب:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
ستتم مقارنة الشروط وفقًا لمبدأ OR: سيتم تلخيص إجمالي المبالغ إذا تم استيفاء شرط واحد على الأقل: إما $ A $ 2: $ A $ 5 تساوي قيمة الخلية $ I $ 3 أو خلية النطاق $ B $ 2: $ B $ 5 تساوي قيمة الخلية H5.
هذه هي ميزة SUMMPRODUCT عبر SUMMESLIMN. لا يمكن لـ SUMMESLIMN تلخيص القيم وفقًا لمبدأ OR ، فقط وفقًا لمبدأ AND (يجب استيفاء جميع الشروط).
القصور
SUMPRODUCT لا يمكن استخدام أحرف البدل * و؟ من الممكن استخدام أكثر دقة ، لكن لن يتم اعتبارهم أحرفًا خاصة ، ولكن كنجمة وعلامة استفهام. أعتقد أن هذا هو عيب كبير. وعلى الرغم من إمكانية تجاوز هذا الأمر ، إلا أنني أستخدم وظائف أخرى داخل SUMPRODUCT - سيكون من الرائع أن تستخدم الدالة أحرف البدل بطريقة أو بأخرى.
في المثال ، ستجد مثالين للوظائف لفهم أفضل لما هو مكتوب أعلاه.
تحميل مثال
المبلغ بعدة معايير (41.5 كيلوبايت ، 10،477 التنزيلات)
انظر أيضا:
جمع الخلايا عن طريق ملء اللون
جمع الخلايا حسب لون الخط
جمع الخلايا حسب تنسيق الخلية
حساب كمية الخلايا عن طريق ملء اللون
حساب كمية الخلايا حسب لون الخط
كيفية تلخيص البيانات من عدة أوراق ، بما في ذلك حسب الحالة
دروس الفيديو {"الشريط السفلي": {"textstyle": "static" ، "textpositionstatic": "bottom" ، "textautohide": true ، "textpositionmarginstatic": 0 ، "textpositiondynamic": "bottomleft" ، "textpositionmarginleft": 24 ، " textpositionmarginright ": 24 ،" textpositionmargintop ": 24 ،" textpositionmarginbottom ": 24 ،" texteffect ":" slide "،" texteffecteasing ":" easyOutCubic "،" texteffectduration ": 600 ،" texteffectslidedirection ":" left "،" texteffectduration ": 600 : 30 ، "texteffectdelay": 500 ، "texteffectseparate": false ، "texteffect1": "slide" ، "texteffectslidedirection1": "right" ، "texteffectslidedistance1": 120 ، "texteffecteasing1": "easyOutCubic" ، "texteffectduration1": ، "texteffectdelay1": 1000 ، "texteffect2": "slide" ، "texteffectslidedirection2": "right" ، "texteffectslidedistance2": 120 ، "texteffecteasing2": "easyOutCubic" ، "texteffectduration2": 600 ، "texteffectdelay2": 600 ، "texteffectdelay2" textcss ":" display: block ؛ padding: 12px ؛ محاذاة text: left ؛ "،" textbgcss ":" display: block ؛ موضع: مطلق ؛ أعلى: 0px ؛ يسار: 0px ؛ العرض: 100٪ ؛ الارتفاع: 100٪ ؛ لون الخلفية: # 333333 ؛ عتامة: 0.6 ؛ مرشح: أ lpha (العتامة = 60) ؛ "،" titlecss ":" display: block؛ الموقف: نسبي font: bold 14px \ "Lucida Sans Unicode \"، \ "Lucida Grande \"، sans-serif، Arial؛ اللون: #fff؛ "،" descriptioncss ":" display: block؛ الموقف: نسبي font: 12px \ "Lucida Sans Unicode \"، \ "Lucida Grande \"، sans-serif، Arial؛ اللون: # ff؛ أعلى الهامش: 8px ؛ "،" buttoncss ":" display: block ؛ الموقف: نسبي الهامش العلوي: 8px ؛ "،" texteffectresponsive ": true ،" texteffectresponsivesize ": 640 ،" titlecssresponsive ":" font-size: 12px؛ "،" descriptioncssresponsive ":" display: none: important؛ "،" buttoncssresponsive ": "" ، "addgooglefonts": false ، "googlefonts": ""، "textleftrightpercentforstatic": 40}}