اكسل

وظيفة Excel SUMPRODUCT

Excel Sumproduct Function

وظيفة Excel SUMPRODUCTملخص

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





الغرض الضرب ، ثم جمع المصفوفات القيمة المرجعة نتيجة المصفوفات المضاعفة والمجمعة بناء الجملة = SUMPRODUCT (array1، [array2]، ...) وسيطات
  • مجموعة 1 - المصفوفة الأولى أو النطاق المطلوب ضربه ، ثم إضافته.
  • array2 - [اختياري] المصفوفة الثانية أو النطاق المطلوب ضربه ، ثم جمعه.
إصدار برنامج Excel 2003 ملاحظات الاستخدام

تعمل الدالة SUMPRODUCT مع المصفوفات ، ولكنها لا تتطلب بناء جملة الصفيف العادي (Ctrl + Shift + Enter) للدخول. الغرض من الدالة SUMPRODUCT هو ضرب المصفوفات ثم جمعها. إذا تم توفير مصفوفة واحدة فقط ، فسيقوم SUMPRODUCT بجمع العناصر الموجودة في المصفوفة. يمكن توفير ما يصل إلى 30 صفيفًا.

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





لتوضيح كيفية عمل SUMPRODUCT ، إليك بعض الأمثلة الشائعة.

SUMPRODUCT للمبالغ والتهم المشروطة

افترض أن لديك بعض بيانات الطلب في A2: B6 ، مع الحالة في العمود A ، والمبيعات في العمود B:



إلى ب
1 ولاية مبيعات
2 خارج 75
3 ماذا او ما 100
4 TX 125
5 ماذا او ما 125
6 TX 150

باستخدام SUMPRODUCT ، يمكنك ذلك عدد إجمالي مبيعات تكساس ('TX') بهذه الصيغة:

 
= SUMPRODUCT (--(A2:A6='TX'))

ويمكنك مجموع إجمالي مبيعات تكساس ('TX') بهذه الصيغة:

 
= SUMPRODUCT (--(A2:A6='TX'),B2:B6)

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

كيفية تسمية نطاق خلية في Excel

بالنسبة لمثال المجموع أعلاه ، يوجد هنا تمثيل افتراضي للمصفوفتين كما تمت معالجتهما أولاً بواسطة SUMPRODUCT:

مجموعة 1 array2
خاطئة 75
خاطئة 100
حقيقية 125
خاطئة 125
حقيقية 150

كل مجموعة لديها 5 عناصر. تحتوي المصفوفة الأولى على قيم TRUE / FALSE الناتجة عن التعبير A2: A6 = 'TX' ، والمصفوفة الثانية هي محتويات B2: B6. سيتم ضرب كل عنصر في المصفوفة الأولى بالعنصر المقابل في المصفوفة الثانية. ومع ذلك ، في الحالة الحالية ، ستكون نتيجة SUMPRODUCT صفرًا لأنه سيتم التعامل مع القيمتين TRUE و FALSE على أنها صفر. نحتاج إلى أن تكون العناصر الموجودة في المصفوفة 1 رقمية - يجب 'إجبارها' على تكوين الآحاد والأصفار. هذا هو المكان الذي يأتي فيه السالب المزدوج.

باستخدام السالب المزدوج - (أحادي مزدوج ، لأنواع التقنية الخاصة بك) يمكننا إجبار TRUE / FALSE على القيم الرقمية واحد وصفر ، كما هو موضح في التمثيل الظاهري أدناه. يمثل العمود الأخير 'المنتج' نتيجة ضرب المصفوفتين معًا. النتيجة المجمعة ، 275 ، هي القيمة التي تُرجعها SUMPRODUCT.

مجموعة 1 array2 المنتج
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
مجموع 275

باستخدام بنية القوس المتعرج للمصفوفات ، يبدو المثال كالتالي بعد الإكراه:

ماذا يعني المتوسط ​​في التفوق
 
= SUMPRODUCT ({0,0,1,0,1},{75,100,125,125,150}) 

ومثل هذا بعد الضرب:

 
= SUMPRODUCT ({0,0,125,0,150})

هذا المثال يتوسع في الأفكار أعلاه بمزيد من التفاصيل.

SUMPRODUCT مع وظائف أخرى

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

على سبيل المثال ، افترض أن لديك 10 قيم نصية مختلفة في A1: A10 وتريد حساب إجمالي الأحرف لجميع القيم العشر. يمكنك إضافة عمود مساعد في العمود B يستخدم هذه الصيغة: LEN (A1) لحساب الأحرف في كل خلية. ثم يمكنك استخدام SUM لجمع كل الأرقام العشرة. ومع ذلك ، باستخدام SUMPRODUCT ، يمكنك كتابة صيغة مثل هذه:

 
= SUMPRODUCT ( LEN (A1:A10))

عند استخدامها مع نطاق مثل A1: A10 ، سترجع LEN مصفوفة من 10 قيم. ثم سيقوم SUMPRODUCT بجمع كل القيم وإرجاع النتيجة دون الحاجة إلى عمود مساعد.

انظر الأمثلة أدناه العديد من الطرق الأخرى لاستخدام SUMPRODUCT.

ملحوظات:

  1. يعامل SUMPRODUCT العناصر غير الرقمية في المصفوفات كأصفار.
  2. يجب أن تكون وسيطات الصفيف بنفس الحجم. خلاف ذلك ، سينشئ SUMPRODUCT قيمة #VALUE! قيمة الخطأ.
  3. ستنشئ الاختبارات المنطقية داخل المصفوفات قيم TRUE و FALSE. في معظم الحالات ، سترغب في إجبارها على 1 و 0.
  4. غالبًا ما يستخدم SUMPRODUCT نتيجة الدالات الأخرى مباشرةً (انظر أمثلة الصيغ أدناه)


^