ספירת תאים המכילים את x או y בטווח ב-Excel

כדי לספור תאים המכילים ערך אחד או אחר, חלק ממשתמשי Excel עשויים להוסיף שתי פונקציות COUNTIF יחד כדי להתמודד עם הבעיה, אך שיטה זו סופרת תאים עם לוגיקה של AND (מכילים גם x וגם y) במקום לוגיקה של OR (מכילים את x או y). כדי למנוע ספירה כפולה של הקריטריונים, מדריך זה יציג כיצד ליישם נוסחה המבוססת על הפונקציה SUMPRODUCT כדי לספור את מספר התאים בטווח מסוים המכילים את x או y ב-Excel.

כיצד לספור תאים המכילים את x או y בטווח ב-Excel

כפי שמוצג בתמונה למטה, יש טווח נתונים B3:B9, כדי לספור את מספר התאים בטווח זה שמכיל את 'KTE' או 'KTO', יש להחיל את הנוסחה הבאה כדי לבצע זאת.

נוסחה כללית

=SUMPRODUCT(--((ISNUMBER(FIND("criteria1",rng)) + ISNUMBER(FIND("criteria2",rng)))>0))

ארגומנטים

איך להשתמש בנוסחה זו?

1. בחר תא ריק כדי להציג את התוצאה.

2. הזן את הנוסחה הבאה לתוך התא והקש על מקש Enter כדי לקבל את התוצאה.

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

איך تعمل הנוסחאות האלו?

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

• 1. FIND(D3,B3:B9)הפונקציה FIND בודקת אם הערך 'KTE' ב-D3 קיים בטווח שצוין (B3:B9), ומחזירה מערך: {1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,1,#VALUE!}.
  במערך זה, שני המספרים 1 מייצגים כי התא הראשון והתא השני מלמטה ברשימה B3:B9 מכילים את הערך 'KTE', הסימנים #VALUE! מייצגים שהערך 'KTE' לא נמצא בתאים האחרים.
• 2. ISNUMBER{1,#VALUE!, #VALUE!, #VALUE!, #VALUE!,1, #VALUE!}: הפונקציה ISNUMBER מחזירה TURE אם נתקלת במספר במערך, ומחזירה FALSE אם נתקלת בשגיאות, ולבסוף מחזירה מערך חדש כמו {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE}.
• 3. FIND(D4,B3:B9): גם הפונקציה FIND הזו בודקת אם הערך 'KTO' ב-D4 קיים בטווח שצוין (B3:B9), ומחזירה מערך: {#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}.
• 4. ISNUMBER{#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}: הפונקציה ISNUMBER מחזירה TURE אם נתקלת במספר במערך, ומחזירה FALSE אם נתקלת בשגיאות, ולבסוף מחזירה מערך חדש כמו {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• 5. {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE} + {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}: כאן שני המערכים מחזירים את התוצאה בתור {1;1;0;0;0;1;0}.
• 6. {1;1;0;0;0;1;0}>0: כאן בודקים האם כל מספר במערך גדול מ-0 ומחזירים את התוצאה בתור {TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE}.
• 7. --({TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE}): שני הסימנים מינוס ממירים 'TRUE' ל-1 ו-'FALSE' ל-0. כאן תקבל מערך חדש בתור {1;1;0;0;0;1;0}.
• 8. =SUMPRODUCT({1;1;0;0;0;1;0}): הפונקציה SUMPRODUCT מסכמת את כל המספרים במערך ומחזירה את התוצאה הסופית בתור 3 במקרה זה.

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

נוסחאות קשורות