סיכום אם התאים מכילים טקסט מסוים בעמודה אחרת

הדרכה זו תראה לך כיצד לסכום ערכים אם התאים מכילים טקסט מסוים או חלקי בעמודה אחרת. קח את טווח הנתונים הבא כדוגמה, כדי לקבל את הסכום הכולל של המוצרים המכילים את הטקסט "חולצת טי", הן הפונקציה 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.

פונקציות קשורות:

מאמרים נוספים: