ספירת ערכים ייחודיים ב- Excel באמצעות פונקציית COUNTIF

במדריך זה תלמד כיצד לספור ערכים ייחודיים ב- Excel באמצעות נוסחאות (פונקציות COUNTIF ו- SUMPRODUCT).

כיצד לספור ערכים ייחודיים ב- Excel

נניח שיש לנו מערך נתונים כפי שמוצג להלן:

לצורך הדרכה זו, אקרא לטווח A2: A10 בשם NAMES. בהמשך נשתמש בטווח בשם זה בנוסחאות.

ראה גם: כיצד ליצור טווחי שמות ב- Excel.

במערך נתונים זה, ישנה חזרה בטווח NAMES. כדי לקבל את ספירת השמות הייחודיים ממערך הנתונים הזה (A2: A10), נוכל להשתמש בשילוב של פונקציות COUNTIF ו- SUMPRODUCT כפי שמוצג להלן:

= SUMPRODUCT (1/COUNTIF (NAMES, NAMES))

כיצד פועלת נוסחה זו?

בואו נשבור את הנוסחה הזו כדי להבין טוב יותר:

• COUNTIF (NAMES, NAMES)
  • חלק זה של הנוסחה מחזיר מערך. בדוגמה שלמעלה, זה יהיה {2; 2; 3; 1; 3; 1; 2; 3; 2}. המספרים כאן מציינים כמה פעמים ערך מתרחש בטווח התאים הנתון.
    לדוגמה, השם הוא בוב, המופיע פעמיים ברשימה, ומכאן שהוא יחזיר את המספר 2 עבור בוב. באופן דומה, סטיב מתרחש שלוש פעמים ולכן 3 מוחזרים עבור סטיב.
• 1/COUNTIF (NAMES, NAMES)
  • חלק זה של הנוסחה יחזיר מערך - {0.5; 0.5; 0.333333333333333; 1; 0.333333333333333; 1; 0.5; 0.333333333333333; 0.5}
    מכיוון שחילקנו 1 במערך, הוא מחזיר מערך זה.
    לדוגמה, האלמנט הראשון של המערך שהוחזר למעלה היה 2. כאשר 1 מחולק ב -2, הוא מחזיר .5.
• SUMPRODUCT (1/COUNTIF (NAMES, NAMES))
  • SUMPRODUCT פשוט מוסיף את כל המספרים האלה. שים לב שאם בוב מופיע פעמיים ברשימה, המערך הנ"ל מחזיר .5 בכל מקום בו הופיע שם בוב ברשימה. באופן דומה, מכיוון שסטיב מופיע שלוש פעמים ברשימה, המערך מחזיר .3333333 בכל פעם שמופיע שמו של סטיב. כאשר נוסיף את המספרים עבור כל שם, הוא תמיד יחזיר 1. ואם נוסיף את כל המספרים, הוא יחזיר את מספר השמות הייחודיים ברשימה.

נוסחה זו עובדת מצוין עד שאין לך תאים ריקים בטווח. אבל אם יש לך תאים ריקים, הוא יחזיר #DIV/0! שְׁגִיאָה.

כיצד לטפל בתאים ריקים?

הבה נבין תחילה מדוע הוא מחזיר שגיאה כאשר יש תא ריק בטווח. נניח שיש לנו את מערך הנתונים כפי שמוצג להלן (כאשר תא A3 ריק):

עכשיו אם נשתמש באותה נוסחה שהשתמשנו למעלה, החלק COUNTIF בנוסחה מחזיר מערך {2; 0; 3; 1; 3; 1; 2; 3; 1}. מכיוון שאין תא בתא A3, ספירתו מוחזרת כ- 0.

ומכיוון שאנו מחלקים 1 בכל המערך הזה, הוא מחזיר #DIV/0! שְׁגִיאָה.

כדי לטפל בשגיאת חלוקה זו במקרה של תאים ריקים, השתמש בנוסחה שלהלן:

= SUMPRODUCT ((1/COUNTIF (NAMES, NAMES & ””)))

שינוי אחד שעשינו בנוסחה זו הוא חלק הקריטריונים של הפונקציה COUNTIF. השתמשנו ב- NAMES & ”” במקום NAMES. על ידי כך, הנוסחה תחזיר את ספירת התאים הריקים (קודם לכן היא החזירה 0 היכן שהיה תא ריק).

הערה: נוסחה זו תספור תאים ריקים כערך ייחודי ותחזיר אותו בתוצאה.

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

להלן הנוסחה המטפלת בתאים הריקים ואינה סופרת אותה בתוצאה הסופית:

= SUMPRODUCT ((NAMES ””)/COUNTIF (NAMES, NAMES & ””))

בנוסחה זו, במקום 1 כמניין, השתמשנו ב- NAMES "". זה מחזיר מערך של TRUEs ו- FALSE. הוא מחזיר FALSE בכל פעם שיש תא ריק. מכיוון ש- TRUE משווה ל -1 ו- FALSE שווה ל- 0 בחישובים, תאים ריקים אינם נספרים מכיוון שהמונה הוא 0 (FALSE).

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

כיצד לספור ערכים ייחודיים ב- Excel שהם טקסט

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

להלן הנוסחה שתספור ערכי טקסט ייחודיים ב- Excel:

= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””)))

כל מה שעשינו הוא להשתמש בנוסחה ISTEXT (NAMES) כמניין. הוא מחזיר TRUE כאשר התא מכיל טקסט, ו- FALSE אם לא. הוא לא יספור תאים ריקים, אלא יספור תאים שיש להם מחרוזת ריקה ("").

כיצד לספור ערכים ייחודיים ב- Excel שהם מספריים

להלן הנוסחה שתספור ערכים מספריים ייחודיים ב- Excel

= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAMES, NAMES & ””))

כאן אנו משתמשים ב- ISNUMBER (NAMES) כמניין. הוא מחזיר TRUE כאשר התא מכיל סוג נתונים מספרי, ו- FALSE אם לא. זה לא סופר תאים ריקים.