לְהִצטַיֵן

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

Count Unique Text Values With Criteria

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

כדי לספור ערכי טקסט ייחודיים בטווח עם קריטריונים, תוכל להשתמש בנוסחת מערך המבוססת על תדירות ו התאמה פונקציות. בדוגמה המוצגת, הנוסחה ב- G6 היא:



{= SUM (--( FREQUENCY ( IF (criteria, MATCH (vals,vals,0)), ROW (vals)- ROW (vals.first)+1)>0))}

שמחזיר 3, שכן שלושה אנשים שונים עבדו על פרויקט אומגה.

הערה: זהו נוסחת מערך ויש להזין אותו עם שליטה + shift + enter.





עם Excel 365 , אתה יכול להשתמש ב- נוסחה הרבה יותר פשוטה מבוסס על ה פונקציה ייחודית . הֶסבֵּר

זו נוסחה מורכבת שמשתמשת ב- FREQUENCY לספירת ערכים מספריים הנגזרים באמצעות הפונקציה MATCH. פועל מבפנים החוצה, הפונקציה MATCH משמשת לקבלת המיקום של כל ערך המופיע בנתונים:

 
{= SUM (--( FREQUENCY ( IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0)), ROW (B5:B11)- ROW (B5)+1)>0))}

התוצאה מ- MATCH היא מערך כזה:



 
 MATCH (B5:B11,B5:B11,0)

כי MATCH תמיד מחזיר את המיקום של ה- ראשון התאמה, ערכים המופיעים יותר מפעם אחת בנתונים מחזירים את אותה המיקום. לדוגמה, מכיוון ש'ג'ים 'מופיע 4 פעמים ברשימה, הוא מופיע במערך זה 4 פעמים כמספר 1.

מחוץ לפונקציית MATCH, ה- פונקציית IF משמש ליישום קריטריונים, שבמקרה זה כרוך בבדיקה אם הפרויקט הוא 'אומגה' (מתא G5):

 
{1131167}

הפונקציה IF פועלת כמו מסנן, ומאפשרת לערכים מ- MATCH לעבור רק אם הם קשורים ל'אומגה '. התוצאה היא מערך כזה:

 
 IF (C5:C11=G5 // filter on 'omega'

המערך המסונן מועבר ישירות לפונקציית FREQUENCY כ- מערך נתונים טַעֲנָה. לאחר מכן, ה פונקציית ROW משמש לבניית א רשימת מספרים עוקבים לכל ערך בנתונים:

 
{FALSEFALSEFALSE1167} // after filtering

זה יוצר מערך כזה:

 
 ROW (B3:B12)- ROW (B3)+1

שהופך ל bins_array ויכוח ב- FILTER. בשלב זה, יש לנו:

 
{12345678910}

FREQUENCY מחזיר מערך מספרים המציין ספירה לכל ערך במערך הנתונים, המאורגן לפי bin. כאשר מספר כבר נספר, FREQUENCY יחזיר אפס. התוצאה מ- FREQUENCY היא מערך כזה:

 
 FREQUENCY ({FALSEFALSEFALSE1167},{1234567})

הערה: FREQUENCY תמיד מחזיר מערך עם פריט אחד יותר מזה bins_array .

האם מצטיין משתמש ברדיאנים או במעלות

בשלב זה נוכל לשכתב את הנוסחה כך:

 
{20000110} // result from FREQUENCY

אנו בודקים אם יש ערכים גדולים מאפס, מה שהופך את המספרים ל- TRUE או FALSE:

 
= SUM (--({20000110}>0))

לאחר מכן אנו משתמשים ב- שלילי כפול כדי לכפות את הערכים ההגיוניים לשניות 1 ו -0:

 
= SUM (--({TRUEFALSEFALSEFALSEFALSETRUETRUEFALSE}))

סוף - סוף, ה פונקציית SUM מחזירה 3 כתוצאה הסופית.

הערה: זוהי נוסחת מערך ויש להזין אותה באמצעות Control + Shift + Enter.

טיפול בתאים ריקים בטווח

אם תאים כלשהם בטווח ריקים, יהיה עליך להתאים את הנוסחה כדי למנוע מעבר תאים ריקים לפונקציית MATCH, דבר שיזרוק שגיאה. תוכל לעשות זאת על ידי הוספת פונקציית IF מקוננת נוספת כדי לבדוק אם יש תאים ריקים:

 
= SUM ({10000110})

עם שני קריטריונים

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

 
{= SUM (--( FREQUENCY ( IF (B5:B11'', IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0))), ROW (B5:B11)- ROW (B5)+1)>0))}

איפה c1 = קריטריונים 1, ג 2 = קריטריונים 2 ו וַלס = טווח הערכים.

עם היגיון בוליאני

עם היגיון בוליאני , אתה יכול להפחית IF מקננים :

 
{= SUM (--( FREQUENCY ( IF (c1, IF (c2, MATCH (vals,vals,0))), ROW (vals)- ROW (vals.1st)+1)>0))}

זה מקל על הוספה וניהול של קריטריונים נוספים.

מותאם מ של מייק גיבין ספר מעולה על נוסחאות מערך, Control-Shift-Enter. הסופר דייב בראנס


^