כדי לספור ערכי טקסט ייחודיים בטווח, תוכל להשתמש בנוסחה המשתמשת במספר פונקציות: תדירות , התאמה , שׁוּרָה ו SUMPRODUCT בדוגמה המוצגת, הנוסחה ב- F5 היא:
= SUMPRODUCT (--( FREQUENCY ( MATCH (data,data,0), ROW (data)- ROW (data.firstcell)+1)>0))
שמחזירה 4, מכיוון שיש 4 שמות ייחודיים ב- B5: B14.
הערה: דרך נוספת לספור ערכים ייחודיים היא השתמש בפונקציה COUNTIF . זו נוסחה הרבה יותר פשוטה, אך היא יכולה לפעול לאט במערכות נתונים גדולות. עם Excel 365 , אתה יכול להשתמש ב- נוסחה פשוטה ומהירה יותר מבוסס על ייחודי .
הֶסבֵּר
נוסחה זו מסובכת יותר מאשר נוסחה דומה שמשתמשת ב- FREQUENCY לספור ערכים מספריים ייחודיים כי FREQUENCY אינו פועל עם ערכים שאינם מספריים. כתוצאה מכך, חלק גדול מהנוסחה פשוט הופך את הנתונים הלא-מספריים לנתונים מספריים ש- FREQUENCY יכול להתמודד איתם.
הפונקציה MATCH עובדת מבפנים ומבחוץ כדי לקבל את המיקום של כל פריט שמופיע בנתונים:
= SUMPRODUCT (--( FREQUENCY ( MATCH (B5:B14,B5:B14,0), ROW (B5:B14)- ROW (B5)+1)>0))
התוצאה מ- MATCH היא מַעֲרָך ככה:
MATCH (B5:B14,B5:B14,0)
כי MATCH תמיד מחזיר את המיקום של ה- ראשון התאמה, ערכים המופיעים יותר מפעם אחת בנתונים מחזירים את אותה המיקום. לדוגמה, מכיוון ש'ג'ים 'מופיע 3 פעמים ברשימה, הוא מופיע במערך זה 3 פעמים כמספר 1.
מערך זה מוזרם ל- FREQUENCY כ- מערך נתונים טַעֲנָה.ה bins_array הטיעון בנוי מחלק זה של הנוסחה:
איך לדרג את הדברים במצטיין
{1114466699}
אשר בונה א רשימת מספרים עוקבים לכל ערך בנתונים:
ROW (B5:B14)- ROW (B5)+1)
בשלב זה, FREQUENCY מוגדר כך:
{12345678910}
FREQUENCY מחזיר מערך מספרים המציין ספירה לכל מספר במערך הנתונים, המאורגן לפי bin. כאשר מספר כבר נספר, FREQUENCY יחזיר אפס. זוהי תכונה מרכזית בפעולה של נוסחה זו. התוצאה מ- FREQUENCY היא מערך כזה:
נוסחת Excel להוספת מספר תאים
FREQUENCY ({1114466699},{12345678910})
הערה: FREQUENCY תמיד מחזיר מערך עם פריט אחד יותר מזה bins_array .
כעת נוכל לשכתב את הנוסחה כך:
{30020300200} // output from FREQUENCY
לאחר מכן, אנו בודקים אם יש ערכים גדולים מאפס (> 0), הממיר את המספרים ל- TRUE או FALSE, ולאחר מכן משתמשים בשלילי כפול (-) כדי להמיר את ערכי TRUE ו- FALSE ל- 1s ו- 0s. עכשיו יש לנו:
= SUMPRODUCT (--({30020300200}>0))
לבסוף, SUMPRODUCT פשוט מוסיף את המספרים ומחזיר את הסכום, שבמקרה זה הוא 4.
טיפול בתאים ריקים
תאים ריקים בטווח יגרמו לנוסחה להחזיר שגיאת #N/A. כדי לטפל בתאים ריקים, תוכל להשתמש בנוסחת מערך מסובכת יותר המשתמשת בפונקציה IF כדי לסנן ערכים ריקים:
= SUMPRODUCT ({10010100100})
הערה: הוספת IF הופכת את זה ל נוסחת מערך הדורש שליטה-shift-enter.
למידע נוסף, ראה דף זה .
מ של מייק גיבין ספר מעולה על נוסחאות מערך, Control-Shift-Enter.דרכים אחרות לספור ערכים ייחודיים
אם יש לך Excel 365, תוכל להשתמש ב- פונקציה ייחודית ל לספור ערכים ייחודיים עם נוסחה הרבה יותר פשוטה.
ל טבלת ציר היא גם דרך מצוינת לספור ערכים ייחודיים.
הסופר דייב בראנס