جمع القيم إذا احتوت الخلايا على نص محدد في عمود آخر

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

• جمع القيم إذا احتوت الخلية على نص محدد أو جزئي باستخدام دالة SUMIF
• جمع القيم إذا احتوت الخلية على نص محدد أو جزئي باستخدام دالة SUMPRODUCT

جمع القيم إذا احتوت الخلية على نص محدد أو جزئي باستخدام دالة SUMIF

لجمع القيم إذا احتوت الخلية على نص محدد في عمود آخر، يمكنك استخدام دالة SUMIF مع رمز بدل (*)، الصيغة العامة هي:

الصيغة العامة مع النص الثابت:

• النطاق: نطاق البيانات الذي تريد تقييمه باستخدام المعيار؛
• *نص*: المعيار الذي تريد جمع القيم بناءً عليه. هنا، يتم استخدام الرمز * للعثور على أي عدد من الأحرف، لمطابقة جميع العناصر التي تحتوي على نص محدد، ضع النص بين الرمزين *. (لاحظ أنه يجب وضع النص والرمز بين علامات اقتباس مزدوجة).
• نطاق_الجمع: نطاق الخلايا الذي يحتوي على القيم الرقمية المتطابقة التي تريد جمعها.

الصيغة العامة مع مرجع الخلية:

• النطاق: نطاق البيانات الذي تريد تقييمه باستخدام المعيار؛
• "*"&الخلية&"*": المعيار الذي تريد جمع القيم بناءً عليه؛
  • *: الرمز الذي يجد أي عدد من الأحرف.
  • الخلية: الخلية التي تحتوي على النص المحدد للبحث عنه.
  • &: مشغل التسلسل (&) يستخدم لربط مرجع الخلية بالرموز النجمية.
• نطاق_الجمع: نطاق الخلايا الذي يحتوي على القيم الرقمية المتطابقة التي تريد جمعها.

بعد معرفة التشغيل الأساسي للدالة، يرجى استخدام أي واحدة من الصيغ التالية حسب الحاجة، ثم اضغط على مفتاح Enter للحصول على النتيجة:

ملاحظة: دالة SUMIF ليست حساسة لحالة الأحرف.

جمع القيم إذا احتوت الخلية على نص محدد أو جزئي باستخدام دالة SUMPRODUCT

يمكن أيضًا لدالة SUMPRODUCT مساعدتك في إنجاز هذه المهمة دون استخدام أي رمز بدل. هنا، يجب أن تضع دوال ISNUMBER و SEARCH داخل دالة SUMPRODUCT، الصيغة العامة هي:

• نطاق_الجمع: نطاق الخلايا الذي يحتوي على القيم الرقمية المتطابقة التي تريد جمعها؛
• المعيار: المعيار الذي تريد جمع القيم بناءً عليه. يمكن أن يكون مرجع خلية أو نص محدد قمت بتحديده؛
• النطاق: نطاق البيانات الذي تريد تقييمه باستخدام المعيار؛

يرجى استخدام أي واحدة من الصيغ التالية في خلية فارغة، واضغط على مفتاح Enter للحصول على النتيجة:

شرح لهذه الصيغة:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("تي شيرت",$A$2:$A$12))))

• SEARCH("تي شيرت",$A$2:$A$12): تقوم دالة SEARCH بإرجاع موقع النص المحدد "تي شيرت" من نطاق البيانات A2:A12، لذلك ستحصل على مصفوفة مثل هذه: {5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}.
• ISNUMBER(SEARCH("تي شيرت",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): تُستخدم دالة ISNUMBER لاختبار القيم الرقمية، وتُعيد مصفوفة جديدة: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
• $B$2:$B$12*(ISNUMBER(SEARCH("تي شيرت",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: لضرب هاتين المصفوفتين، فإن العملية الرياضية تجبر القيم TRUE و FALSE على أن تصبح 1 و 0. وبالتالي، سيكون ضرب المصفوفتين كما يلي: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1;0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
• SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("تي شيرت",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}): أخيرًا، تقوم دالة SUMPRODUCT بجمع جميع القيم في المصفوفة للحصول على النتيجة: 1605.

الدوال ذات الصلة:

مقالات أخرى: