לְהִצטַיֵן

ספרו ערכי טקסט ייחודיים בטווח

Count Unique Text Values Range

נוסחת Excel: ספור ערכי טקסט ייחודיים בטווחנוסחה כללית | _+_ | סיכום

כדי לספור ערכי טקסט ייחודיים בטווח, תוכל להשתמש בנוסחה המשתמשת במספר פונקציות: תדירות , התאמה , שׁוּרָה ו 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, תוכל להשתמש ב- פונקציה ייחודית ל לספור ערכים ייחודיים עם נוסחה הרבה יותר פשוטה.

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

הסופר דייב בראנס


^