=COUNTA(UNIQUE(A2:A9)) סופרת כמה ערכים שונים יש ב-A2:A9. UNIQUE מחזירה כל ערך פעם אחת, ו-COUNTA סופרת את הרשימה הזו. היא דורשת Excel 2021 או Microsoft 365; גרסאות ישנות יותר מוסברות בהמשך.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Unique list | Count | |
| 2 | Ana | Ana | 5 | |
| 3 | Ben | Ben | ||
| 4 | Ana | Cara | ||
| 5 | Cara | Dan | ||
| 6 | Ben | Eva | ||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
שמונה הזמנות הגיעו מחמישה לקוחות. C2 שופך את רשימת השמות של UNIQUE כדי שתוכלו לראות מה נספר, ו-D2 סופר אותה בלי שהרשימה תצטרך להופיע בגיליון. שנו את A9 ל-Ana והספירה יורדת ל-4; הקלידו שם חדש והיא עולה.
UNIQUE מתעלמת מגודל אותיות, ולכן Ana ו-ana נספרים כלקוח אחד.
ספירת ערכים ייחודיים בגרסאות ישנות של אקסל
ב-Excel 2019 ובגרסאות קודמות אין UNIQUE. הנוסחה הקלאסית היא:
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
COUNTIF עם הטווח כולו כקריטריון מחזירה, לכל שורה, כמה פעמים הערך של השורה מופיע. שם שמופיע 3 פעמים מקבל 3 בכל אחת מהשורות שלו, ולכן 1/3 מתווסף שלוש פעמים והשם מסתכם בדיוק ב-1. עמודה B מציגה את הספירה של כל שורה ועמודה C את השבר.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Times | 1/Times | Count | |
| 2 | Ana | 3 | 0.33 | 5 | |
| 3 | Ben | 2 | 0.50 | 5.00 | |
| 4 | Ana | 3 | 0.33 | ||
| 5 | Cara | 1 | 1.00 | ||
| 6 | Ben | 2 | 0.50 | ||
| 7 | Dan | 1 | 1.00 | ||
| 8 | Ana | 3 | 0.33 | ||
| 9 | Eva | 1 | 1.00 |
כל אחת משלוש השורות של Ana מוסיפה 0.33, כל אחת משתי השורות של Ben מוסיפה 0.50, ושלושת השמות הבודדים מוסיפים 1 כל אחד: 5 בסך הכול, כמו ה-SUM של עמודת העזר. על עשרות אלפי שורות הנוסחה הזו איטית, כי COUNTIF סורקת את כל הטווח פעם אחת לכל שורה; ל-UNIQUE אין את המחיר הזה.
ערכים שונים מול ייחודיים: ערכים שמופיעים רק פעם אחת
"ייחודי" משמש לשתי ספירות שונות. זו שלמעלה סופרת ערכים שונים: כל שם פעם אחת. השנייה סופרת את הערכים שמופיעים בדיוק פעם אחת, למשל לקוחות שהזמינו רק פעם אחת. UNIQUE עושה את זה כשהארגומנט השלישי שלה, exactly_once, מוגדר ל-TRUE.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Count | Result | |
| 2 | Ana | Distinct | 5 | |
| 3 | Ben | Exactly once | 3 | |
| 4 | Ana | Exactly once, older Excel | 3 | |
| 5 | Cara | |||
| 6 | Ben | |||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
חמישה לקוחות שונים, אבל רק שלושה מהם, Cara, Dan ו-Eva, הזמינו פעם אחת. הגרסה לאקסל הישן סופרת את השורות שה-COUNTIF שלהן הוא בדיוק 1. אם כל ערך חוזר, UNIQUE עם exactly_once מחזירה #CALC! ו-COUNTA סופרת את השגיאה הזו כ-1; הגרסה עם SUMPRODUCT נותנת 0.
ספירת ערכים ייחודיים עם תנאי
כדי לספור את הלקוחות השונים באזור אחד, קודם סננו את השורות ואז ספרו את מה שנשאר. FILTER שומרת את השורות של North ו-UNIQUE מסירה את החזרות.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Region | Region | Customers | |
| 2 | Ana | North | North | 3 | |
| 3 | Ben | South | South | 3 | |
| 4 | Ana | North | North, older Excel | 3 | |
| 5 | Cara | North | |||
| 6 | Ben | North | |||
| 7 | Dan | South | |||
| 8 | Ana | North | |||
| 9 | Eva | South |
ל-North יש חמש הזמנות משלושה לקוחות: Ana, Cara ו-Ben. E3 סופר את South באותה דרך. E4 היא הגרסה ל-Excel 2019 ולגרסאות קודמות: COUNTIFS סופרת כל זוג של לקוח ואזור, והתנאי משאיר רק את השברים של North.
אם אף שורה לא מתאימה, FILTER מחזירה #CALC!, ו-COUNTA סופרת את השגיאה הזו כערך אחד: הקלידו West ב-D2 ו-E2 יציג 1, לא 0. עטיפת הנוסחה ב-IFERROR לא עוזרת, כי COUNTA לא מחזירה שגיאה. ספרו במקום זאת את השורות של התוצאה, שכן מעבירה את השגיאה הלאה: =IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="West"))),0) מחזירה 0.
ספירת ערכים ייחודיים בלי תאים ריקים
תא ריק בטווח הופך ל"ערך" נוסף. UNIQUE מחזירה אותו כ-0 ו-COUNTA סופרת את ה-0 הזה, ולכן עבור Ana, תא ריק, Ben, Ana, תא ריק, Cara ו-Ben, אקסל נותן:
=COUNTA(UNIQUE(A2:A8)) 4 three names plus the 0 for the empty cells
בנוסחה הישנה, שורה ריקה גורמת ל-COUNTIF להחזיר 0, ולכן 1/0 נותן #DIV/0!. הסירו קודם את התאים הריקים:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Formula | Count | |
| 2 | Ana | Skip blanks | 3 | |
| 3 | Older Excel | 3 | ||
| 4 | Ben | |||
| 5 | Ana | |||
| 6 | ||||
| 7 | Cara | |||
| 8 | Ben |
שתי הנוסחאות סופרות את שלושת הלקוחות. FILTER עם A2:A8<>"" מוציאה את התאים הריקים לפני ש-UNIQUE רואה אותם. בנוסחה הישנה, A2:A8&"" הופך כל תא ריק למחרוזת ריקה כך ש-COUNTIF אף פעם לא מחזירה 0, ו-(A2:A8<>"") נותן לשורות האלה משקל 0.
תרגול: ספירת המוצרים
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Count | Result | |
| 2 | 1001 | Apple | Products | ||
| 3 | 1002 | Pear | |||
| 4 | 1003 | Apple | |||
| 5 | 1004 | Plum | |||
| 6 | 1005 | Pear | |||
| 7 | 1006 | Apple | |||
| 8 | 1007 | Plum | |||
| 9 | 1008 | Fig |
תורכם: ספרו כמה מוצרים שונים מופיעים ב-B2:B9. כתבו את הנוסחה ב-E2.
איזו נוסחה לגרסת האקסל שלכם
| ספירה | Excel 365 / 2021 | Excel 2019 וגרסאות קודמות |
|---|---|---|
| ערכים שונים | =COUNTA(UNIQUE(A2:A9)) | =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)) |
| ערכים שמופיעים פעם אחת | =COUNTA(UNIQUE(A2:A9,,TRUE)) | =SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1)) |
| ערכים שונים, עם תנאי | =COUNTA(UNIQUE(FILTER(A2:A9,B2:B9="North"))) | =SUMPRODUCT((B2:B9="North")/COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9)) |
| ערכים שונים, בלי תאים ריקים | =COUNTA(UNIQUE(FILTER(A2:A9,A2:A9<>""))) | =SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&"")) |
בטבלת ציר, הסיכום "Distinct Count" (ספירת ערכים שונים) עושה את אותה עבודה בלי נוסחה, אבל רק כשטבלת הציר נוצרת כשהאפשרות "Add this data to the Data Model" (הוספת הנתונים למודל הנתונים) מסומנת. כדי למחוק את החזרות במקום לספור אותן, ראו הסרת כפילויות.
שאלות נפוצות
איך סופרים ערכים ייחודיים באקסל?
ב-Excel 365 או 2021, השתמשו ב-=COUNTA(UNIQUE(A2:A9)): UNIQUE מציגה כל ערך פעם אחת ו-COUNTA סופרת את הרשימה. בגרסאות ישנות יותר השתמשו ב-=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)).
איך סופרים ערכים שמופיעים רק פעם אחת?
הגדירו את הארגומנט השלישי של UNIQUE, exactly_once, ל-TRUE: =COUNTA(UNIQUE(A2:A9,,TRUE)). עבור Ana, Ana, Ben היא נותנת 1, כי רק Ben מופיע פעם אחת. ב-Excel 2019 ובגרסאות קודמות השתמשו ב-=SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1)).
איך סופרים ערכים ייחודיים עם תנאי?
קודם סננו, ואז ספרו: =COUNTA(UNIQUE(FILTER(A2:A9,B2:B9="North"))) סופרת את הלקוחות השונים בשורות של North. אם אף שורה לא מתאימה, COUNTA סופרת את שגיאת #CALC! של FILTER כ-1, ולכן כשזה יכול לקרות השתמשו ב-=IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="North"))),0).
איך סופרים ערכים ייחודיים ומתעלמים מתאים ריקים?
הסירו את התאים הריקים לפני UNIQUE: =COUNTA(UNIQUE(FILTER(A2:A9,A2:A9<>""))). בגרסאות ישנות של אקסל, =SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&"")) מדלגת עליהם.
