ספירת תאים עם לוגיקה OR עבור קריטריונים מרובים ב-Excel

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

• ספירת תאים עם תנאים OR ב-Excel
  • ספירת תאים עם תנאים OR באמצעות פונקציות SUM ו-COUNTIFS
  • ספירת תאים עם תנאים OR באמצעות פונקציית SUMPRODUCT
• ספירת תאים עם מספר קבוצות של תנאים OR ב-Excel
  • ספירת תאים עם שתי קבוצות של תנאים OR באמצעות פונקציות SUM ו-COUNTIFS
  • ספירת תאים עם מספר קבוצות של תנאים OR באמצעות פונקציית SUMPRODUCT

ספירת תאים עם תנאים OR ב-Excel

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

ספירת תאים עם תנאים OR באמצעות פונקציות SUM ו-COUNTIFS

ב-Excel, כדי לספור עם מספר תנאים OR, ניתן להשתמש בפונקציות SUM ו-COUNTIFS עם קבוע מערך, התחביר הכללי הוא:

• טווח: טווח הנתונים שמכיל את הקריטריונים בהם תספור תאים;
• קריטריון1, קריטריון2, קריטריון3…: התנאים שברצונך לספור תאים בהתבסס עליהם.

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

הסבר לנוסחה:

=SUM(COUNTIFS(B2:B13,{"עפרון","סרגל"}))

• {"עפרון","סרגל"}: ראשית, עליך לעטוף את כל התנאים בקבוע מערך כזה: {"עפרון","סרגל"}, הפרד את הפריטים עם פסיקים.
• COUNTIFS(B2:B13,{"עפרון","סרגל"}): פונקציית COUNTIFS תקבל ספירות נפרדות עבור "עפרון" ו"סרגל", ותקבל את התוצאה הזו: {2,3}.
• SUM(COUNTIFS(B2:B13,{"עפרון","סרגל"}))=SUM({2,3}): לבסוף, פונקציית SUM מסכמת את כל הפריטים במערך ומחזירה את התוצאה: 5.

טיפים: ניתן גם להשתמש בהתייחסויות לתאים עבור קריטריונים, אנא השתמש בנוסחת מערך הבאה, ולאחר מכן לחץ על מקשי Ctrl + Shift + Enter יחד כדי לקבל את התוצאה הנכונה:

ספירת תאים עם תנאים OR באמצעות פונקציית SUMPRODUCT

כאן ישנה נוסחה נוספת שנוצרה על ידי פונקציית SUMPRODUCT שתוכל לעזור לספור תאים עם לוגיקת OR גם כן. התחביר הכללי הוא:

• טווח: טווח הנתונים שמכיל את הקריטריונים בהם תספור תאים;
• קריטריון1, קריטריון2, קריטריון3…: התנאים שברצונך לספור תאים בהתבסס עליהם.

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

הסבר לנוסחה:

=SUMPRODUCT(1*(B2:B13={"עפרון","סרגל"}))

• B2:B13={"עפרון","סרגל"}: הביטוי הזה משווה כל קריטריון "עפרון", "סרגל" עם תא הטווח B2:B13. אם הקריטריון מתקיים, הוא מחזיר TRUE, אחרת FALSE יוצג, תקבל את התוצאה הזו: {TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE}.
• 1*(B2:B13={"עפרון","סרגל"}): הכפלת המספרים מעבירה את הערכים הלוגיים – TRUE ו-FALSE ל-1 ו-0, כך שתקבל את התוצאה הזו: {1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}.
• SUMPRODUCT(1*(B2:B13={"עפרון","סרגל"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): לבסוף, פונקציית SUMPRODUCT מסכמת את כל המספרים במערך כדי לקבל את התוצאה: 5.

ספירת תאים עם מספר קבוצות של תנאים OR ב-Excel

לפעמים, ייתכן שיהיה צורך לספור תאים עם שתיים או יותר קבוצות של תנאים OR, במקרה זה, ניתן להשתמש הן ב-SUM ו-COUNTIFS עם קבוע מערך והן ב-SUMPRODUCT עם פונקציות ISNUMBER MATCH.

ספירת תאים עם שתי קבוצות של תנאים OR באמצעות פונקציות SUM ו-COUNTIFS

כדי להתמודד עם רק שתי קבוצות של קריטריוני OR, אתה רק צריך להוסיף קבוע מערך נוסף לנוסחת COUNTIFS.

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

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

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

ספירת תאים עם מספר קבוצות של תנאים OR באמצעות פונקציית SUMPRODUCT

הנוסחה למעלה עובדת רק עבור שתי קבוצות של קריטריוני OR, אם יש צורך לספור עם קריטריונים נוספים, נוסחת SUMPRODUCT מורכבת יחד עם פונקציות ISNUMBER MATCH יכולה לעזור לך.

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

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

הסבר לנוסחה:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"עפרון","סרגל"},0))*ISNUMBER(MATCH(C2:C13,{"נמסר","במשלוח"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))

ISNUMBER(MATCH(B2:B13,{"עפרון","סרגל"},0)):

• MATCH(B2:B13,{"עפרון","סרגל"},0): פונקציית MATCH משמשת להשוות כל תא בטווח B2:B13 עם קבוע המערך המתאים. אם נמצא התאמה, היא מחזירה את המיקום היחסי של הערך במערך, אחרת מוצגת ערך שגיאה. אז, תקבל את רשימת המערך הזו: {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}.
• ISNUMBER(MATCH(B2:B13,{"עפרון","סרגל"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): פונקציית ISNUMBER ממירה את המספרים ל-TRUEs ואת ערכי השגיאה ל-FALSEs כמו זה: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}.

הלוגיקה למעלה יכולה להיות מיושמת גם על הביטויים השני והשלישי של ISNUMBER.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"עפרון","סרגל"},0))*ISNUMBER(MATCH(C2:C13,{"נמסר","במשלוח"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):

• לאחר מכן, שלושת המערכים הללו מוכפלים יחד בתוך SUMPRODUCT, אשר באופן אוטומטי ממיר ערכי TRUE ו-FALSE ל-1s ו-0s כחלק מהפעולה המתמטית ככה: SUMPRODUCT({1;0;0;1;0;1;0;1;1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1} *{1;0;0;0;0;0;0;1;0;0;1;0})=SUMPRODUCT({1;0;0;0;0;0;0;1;0;0;1;0}).
• לבסוף, פונקציית SUMPRODUCT מסכמת את כל המספרים במערך כדי לקבל את התוצאה: 3.

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

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