ספירת מספר תאים שאינם מכילים ערכים רבים

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

כיצד לספור תאים שאינם מכילים ערכים רבים?

כמו שניתן לראות בתמונה למטה, כדי לספור תאים ב-B3:B11 שאינם מכילים ערכים המופיעים ב-D3:D4, ניתן לפעול באופן הבא.

נוסחה כללית

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

ארגומנטים

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

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

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

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

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

איך הנוסחאות הללו עובדות?

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4): פונקציית TRANSPOSE משנה את האוריינטציה של D3:D4 ומחזירה {“count”,”blank”};
• SEARCH({“count”,”blank”},B3:B11): פונקציית SEARCH כאן מאתרת את מיקום תת-המחרוזת “count” ו-“blank” מתוך הטווח B3:B11, ומחזירה מערך כמו {#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,
  #VALUE!;1,7}.
• במקרה זה, כל תא ב-B3:B11 ייסרק פעמיים מכיוון שישנם שני ערכים שברצונך להוציא בעת ספירת תאים, כך שתקבל 18 ערכים במערך. כל מספר במערך מצביע על מיקום התו הראשון של “count” או “blank” בכל תא של B3:B11.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}: פונקציית ISNUMBER מחזירה TRUE אם היא נתקלת במספרים במערך, ומחזירה FALSE אם היא נתקלת בשגיאות. כאן מוחזר התוצאה כ {FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;
  TRUE,TRUE}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,
  FALSE;TRUE,TRUE}): שני הסימנים המינוס ממירים “TRUE” ל-1 וממירים “FALSE” ל-0. כאן תקבל מערך חדש כ {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2) ROW(D3:D4)^0: פונקציית ROW מחזירה את מספרי השורות של הפניה לתא: {3;4}, ולאחר מכן אופרטור ההעלאה בחזקה (^) מחזיר את התוצאה של המספרים 3 ו-4 שהועלו בחזקת 0, ולבסוף מחזיר את התוצאה כ-{1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): פונקציית MMULT מחזירה את המכפלה המטריציונית של שני המערכים הללו: {0;0;1;1;1;0;0;1;2} כדי לעמוד בנתוני המקור. כל מספר שאינו אפס במערך מייצג ערך שבו נמצא לפחות אחד מהמחרוזות המוצעות, ואפס מצביע על שלא נמצאו מחרוזות מוצעות.

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0: כאן בודקים אם כל מספר במערך גדול מ-0. אם מספר גדול מ-0, מחזירים TRUE, אם לא, מחזירים FALSE. לאחר מכן תקבל מערך חדש כ-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: מכיוון שאנו רוצים לספור רק את התאים שאינם מכילים את הערכים שצוינו, עלינו להפוך את הערכים במערך על ידי חיסורם מ-1. כאן האופרטור המתמטי ממיר אוטומטית ערכי TRUE ו-FALSE ל-1 ול-0, ולבסוף מחזיר את התוצאה כ-{1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}: פונקציית SUM מסכמת את כל המספרים במערך ומחזירה את התוצאה הסופית כ-4.

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

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