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

سيوضح لك هذا البرنامج التعليمي كيفية جمع القيم عندما تحتوي خلايا في عمود آخر على نصٍّ معيّن أو جزء منه. فعلى سبيل المثال، باستخدام نطاق البيانات أدناه، يمكنك الحصول على إجمالي الكميات الخاصة بالمنتجات التي يتضمّن اسمها النص «T-Shirt» — ويمكنك تحقيق ذلك بسهولة باستخدام إحدى الدالتين: SUMIF أو SUMPRODUCT في Excel.

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

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

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

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

• range: الـ نطاق البيانات الذي تريد تقييمه باستخدام المعايير؛
• *text*: المعيار الذي تريد جمع القيم بناءً عليه. استخدم الرمز * هنا للعثور على أي عدد من الأحرف. وللمطابقة مع جميع العناصر التي تحتوي على نص معيّن، ضع ذلك النص بين رمزَي *. ()لاحظ أنه يجب إحاطة النص والرمز * بعلامتي اقتباس مزدوجتين.)
• sum_range: نطاق الخلايا التي تحتوي على القيم الرقمية المطلوب جمعها عند تطابق الشروط.

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

• range: الـ نطاق البيانات الذي تريد تقييمه باستخدام المعايير؛
• «*»&cell&«*»: المعيار الذي تريد جمع القيم استنادًا إليه؛
  • *: الواصلة متعددة الاستخدامات التي تطابق أي عدد من الأحرف.
  • خلية: الخلية التي تحتوي على النص المحدد الذي تبحث عنه.
  • &: يُستخدم عامل الربط (&) هذا لدمج مرجع الخلية مع العلامات النجمية.
• sum_range: نطاق الخلايا التي تحتوي على القيم الرقمية المطابقة والتي تريد جمعها.

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

ملاحظة: دالةSUMIFلا يُميّز بين الأحرف الكبيرة والصغيرة.

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

يمكنك أيضًا الاعتماد على دالة SUMPRODUCT لإتمام هذه المهمة دون الحاجة إلى أي واصِلة متعددة الاستخدامات. في هذا السياق، ما عليك سوى تضمين دالتَي ISNUMBER وSEARCH داخل دالة SUMPRODUCT، وذلك وفق الصيغة العامة التالية:

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

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

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

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(«T-Shirt»,$A$2:$A$12))))

• SEARCH(«T-Shirt»,$A$2:$A$12): تُرجع دالة SEARCH موضع النص المطلوب «T-Shirt» ضمن نطاق البيانات في العمود A2:A12، مما يُنتج مصفوفة كالتالي: {5؛#VALUE!؛#VALUE!؛7؛#VALUE!؛7؛#VALUE!؛#VALUE!؛#VALUE!؛#VALUE!؛7}.
• ISNUMBER(SEARCH(«T-Shirt»,$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(«T-Shirt»,$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(«T-Shirt»,$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}): أخيرًا، تقوم دالةSUMPRODUCTبجمع جميع القيم في المصفوفة، يُحصل على الناتج: 1605.

الدالة ذات الصلة المستخدمة:

مقالات أخرى: