סכום עם קריטריונים מרובים המבוססים על לוגיקה של OR ו-AND

בעת עבודה בגליונות עבודה ב-Excel, ייתכן שתצטרך לסכום ערכים בהתבסס על קריטריונים מרובים. לפעמים הקריטריונים המרובים מגיעים מאותה עמודה (לוגיקת OR), ולפעמים מעמודות שונות (לוגיקת AND). במקרה זה, כיצד תוכל להתמודד עם המשימה הזו ב-Excel?

• סכום עם קריטריונים מרובים המבוססים על לוגיקת OR
  • על ידי שימוש בנוסחה SUMIF + SUMIF +…
  • על ידי שימוש בפונקציות SUM ו-SUMIF
• סכום עם קריטריונים מרובים המבוססים על לוגיקת AND באמצעות הפונקציה SUMIFS

סכום עם קריטריונים מרובים המבוססים על לוגיקת OR

נניח שיש לך רשימת מוצרים המכילה את הספק והכמות כפי שמוצג בתמונה למטה, כעת ברצונך לקבל את כל הכמויות הכוללות שסופקו על ידי הספקים ג'ון ודיוויד. כאן, אני אציג שתי נוסחאות פשוטות עבורך.

על ידי שימוש בנוסחה SUMIF + SUMIF +…

אם ברצונך לסכום מספרים התואמים אחד מהקריטריונים (לוגיקת OR) מתוך קריטריונים מרובים, אתה יכול לחבר מספר פונקציות SUMIF יחדיו בתוך נוסחה אחת, התחביר הכללי הוא:

• criteria_range: טווח התאים שברצונך להתאים לקריטריונים;
• criteria1: הקריטריון הראשון המשמש לקביעת אילו תאים יש לסכם;
• criteria2: הקריטריון השני המשמש לקביעת אילו תאים יש לסכם;
• sum_range: טווח התאים שברצונך לסכום מהם.

כעת, אנא העתק או הזן אחת מהנוסחאות הבאות לתא ריק, ולאחר מכן לחץ על מקש Enter כדי לקבל את התוצאה:

הסבר לנוסחה:

=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)

• הפונקציה הראשונה SUMIF(B4:B14,"John",C4:C14) מאתרת את השורות של ג'ון וסוכמת את הכמויות הכוללות;
• הפונקציה השנייה SUMIF(B4:B14,"David",C4:C14) מאתרת את השורות של דיוויד וסוכמת את הכמויות הכוללות;
• לאחר מכן, חבר בין שתי נוסחאות SUMIF אלו כדי לקבל את כל הכמויות הכוללות שסופקו על ידי ג'ון ודיוויד.

על ידי שימוש בפונקציות SUM ו-SUMIF

הנוסחה לעיל נוחה מאוד לשימוש אם ישנם רק כמה קריטריונים, אך אם ברצונך לסכום ערכים עם תנאים מרובים של OR, הנוסחה לעיל עשויה להיות מיותרת. במקרה זה, נוסחה טובה יותר שנוצרה על בסיס פונקציות SUM ו-SUMIF עשויה לעזור לך. התחביר הכללי הוא:

נוסחה כללית עם טקסט קבוע:

• criteria_range: טווח התאים שברצונך להתאים לקריטריונים;
• criteria1: הקריטריון הראשון המשמש לקביעת אילו תאים יש לסכם;
• criteria2: הקריטריון השני המשמש לקביעת אילו תאים יש לסכם;
• sum_range: טווח התאים שברצונך לסכום מהם.

נוסחה כללית עם התייחסויות לתאים:

• criteria_range: טווח התאים שברצונך להתאים לקריטריונים;
• criteria_cells: התאים המכילים את הקריטריונים שברצונך להשתמש בהם;
• sum_range: טווח התאים שברצונך לסכום מהם.

אנא, הזן או העתק אחת מהנוסחאות הבאות שאתה צריך לתוך תא ריק, ולאחר מכן получи את התוצאה:

הסבר לנוסחה:

=SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))

> SUMIF(B4:B14, {"John","David"}, C4:C14):

• {"John","David"}: קבוע מערך שהוא קבוצה של קריטריונים מרובים המסופקים בסוגריים מסולסלים.
• SUMIF(B4:B14,"David",C4:C14) קבוע המערך באמצעות לוגיקת OR מכריח את הפונקציה SUMIF לסכום מספרים ב-C4:C14 בהתבסס על אחד מהקריטריונים המרובים ("ג'ון" ו"דיוויד"), והוא יחזיר שני תוצאות נפרדות: {410,1520}.

> SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))=SUM({410,1520}): לבסוף, פונקציית SUM מחברת את תוצאות המערך הללו כדי להחזיר את התוצאה: 1930.

סכום עם קריטריונים מרובים המבוססים על לוגיקת AND באמצעות הפונקציה SUMIFS

אם ברצונך לסכום ערכים עם קריטריונים מרובים בעמודות שונות, תוכל להשתמש בפונקציית SUMIF כדי לפתור את המשימה במהירות. התחביר הכללי הוא:

• sum_range: טווח התאים שברצונך לסכום מהם;
• criteria_range1: הטווח שבו מופעל criteira1;
• criteria1: הקריטריון הראשון שנבדק מול criteria_range1 וקובע אילו תאים להוסיף; (סוג הקריטריון יכול להיות: מספר, ביטוי לוגי, התייחסות לתא, טקסט, תאריך או פונקציית Excel אחרת.)
• criteria_range2, criteria2…: טווחים נוספים והקריטריונים המשויכים להם. (ניתן להגדיר עד 127 זוגות של criteria_range ו-criteria בנוסחת SUMIFS.)

נניח שיש לי טבלה עם עמודות מוצר, ספק, כמות ומחיר כפי שמוצג בתמונה למטה. כעת, אני רוצה למצוא את סכום המחיר הכולל של המוצר תפוח והכמות שהיא גדולה או שווה ל-200.

אנא החל одну מהנוסחאות הבאות לתוך תא ריק, ולאחר מכן לחץ על מקש Enter כדי להחזיר את התוצאה:

הסבר לנוסחה:

=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")

• טווח A4:A14 בודק את המוצר “תפוח”, וטווח C4:C14 מחלץ את כל הכמויות הגדולות או שוות ל-200, לאחר מכן הפונקציה SUMIFS מסכמת רק את התאים התואמים מתוך הטווח D4:D14.

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

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