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

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

• ספירת מספר תאים שאינם שווים למספר ערכים עם נוסחה 1
• ספירת מספר תאים שאינם שווים למספר ערכים עם נוסחה 2

ספירת מספר תאים שאינם שווים למספר ערכים עם נוסחה 1

כדי לספור תאים שאינם שווים למספר ערכים מוגדרים, ניתן להשתמש בשילוב של הפונקציות MATCH, ISNA ו-SUMPRODUCT, והתחביר הכללי הוא:

• range: רשימת התאים שברצונך לספור;
• exclude_values: הערכים להתעלם מהם ברשימת הטווח.

1. הקלד או העתק את הנוסחה הבאה לתא ריק שבו תרצה להציג את התוצאה:

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

הסבר על הנוסחה:

• MATCH(A2:A13,C2:C4,0): הפונקציה MATCH מחזירה את המיקום של כל הערכים התואמים כמספר, ומחזירה את הערך השגוי #N/A כאשר לא נמצא התאמה. כלומר, כאשר הערכים ב-A2:A13 שווים לערכים ב-C2:C4, היא תחזיר מספר, אחרת היא תחזיר שגיאה #N/A. כאן התוצאה היא: {1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}.
• ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): הפונקציה ISNA ממירה את הערכים השגויים ל-TRUE, ואת המספרים ל-FALSE, כך שתקבל את זה: {FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}.
• --(ISNA(MATCH(A2:A13,C2:C4,0))))=--({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}): האופרטור -- הכפול ממיר את כל ה-TRUE ל-1 ואת ה-FALSE ל-0 כך: SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))=SUMPRODUCT({0;1;1;0;0;0;1;1;0;1;1;1;}).
• לאחר מכן הפונקציה SUMPRODUCT משמשת לסיכום המספרים במערך ותקבל את התוצאה: 7.

ספירת מספר תאים שאינם שווים למספר ערכים עם נוסחה 2

כדי לפתור את המשימה הזו ב-Excel, ניתן גם ליישם שילוב של הפונקציות COUNTA, SUMPRODUCT ו-COUNTIF, והתחביר הכללי הוא:

• range: רשימת התאים שברצונך לספור;
• exclude_values: הערכים להתעלם מהם ברשימת הטווח.

1. אנא הקלד או העתק את הנוסחה הבאה לתוך תא ריק כדי להכניס את התוצאה:

2. לאחר מכן, לחץ על מקש Enter כדי לקבל את התוצאה כפי שמוצג בתמונה למטה:

הסבר על הנוסחה:

• COUNTA(A2:A13): הפונקציה COUNTA מחזירה את מספר כל התאים שאינם ריקים בטווח A2:A13. והתוצאה תהיה: 12.
• COUNTIF(A2:A13,C2:C4): הפונקציה COUNTIF סופרת את התאים בטווח A2:A13 שהם שווים לקריטריון ב-C2:C4, כך שתקבל את התוצאה הבאה: {2;1;2} שמשמעותה Apple מופיע פעמיים, Grape מופיע פעם אחת ו-Lemon מופיע פעמיים.
• SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): הפונקציה SUMPRODUCT מסכמת את כל הפריטים במערך ומחזירה תוצאה סופית: 5.
• COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 :התוצאה (5) של הפונקציה SUMPRODUCT מופחתת מספירת כל התאים שאינם ריקים (12) כדי לקבל את התוצאה הסופית שאתה צריך.

הערה: שתי הנוסחאות לעיל אינן רגישות לאותיות גדולות או קטנות.

הפונקציות הרלוונטיות בשימוש:

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