לְהִצטַיֵן

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

Count Unique Values With Criteria

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

כדי לספור ערכים ייחודיים עם תנאי אחד או יותר, תוכל להשתמש בנוסחה המבוססת על ייחודי ו לְסַנֵן . בדוגמה המוצגת, הנוסחה ב- H7 היא:



= SUM (--( LEN ( UNIQUE ( FILTER (range,criteria,'')))>0))

שמחזירה 3, שכן ישנם שלושה שמות ייחודיים ב- B6: B15 הקשורים לפרויקט אומגה.

הערה: נוסחה זו מחייבת נוסחאות מערך דינמי , זמין רק ב- Excel 365 . עם גרסה ישנה יותר של Excel, אתה יכול להשתמש נוסחאות חלופיות מורכבות יותר .





הֶסבֵּר

בבסיס, נוסחה זו משתמשת בפונקציה UNIQUE כדי לחלץ ערכים ייחודיים, ופונקציית FILTER מחילה קריטריונים.

מה הפירוש של רף בתשחץ?

עובדים מבפנים החוצה, ה פונקציית פילטר משמש ליישום קריטריונים וחילוץ רק שמות המשויכים לפרויקט 'אומגה':



 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,C6:C15=H6,'')))>0))

התוצאה מ- FILTER היא מַעֲרָך ככה:

 
 FILTER (B6:B15,C6:C15=H6) // Omega names only

לאחר מכן, ה פונקציה ייחודית משמש להסרת כפילויות:

 
{'Jim''Jim''Carl''Sue''Carl'}

מה שמביא למערך חדש כזה:

 
 UNIQUE ({'Jim''Jim''Carl''Sue''Carl'})

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

 
{'Jim''Carl''Sue'} // after UNIQUE

ה פונקציית LEN מקבל את אורך כל פריט ברשימה ומחזיר מערך אורכים:

 
= SUM (--( LEN ({'Jim''Carl''Sue'})>0))

לאחר מכן, אנו בודקים אם אורכים גדולים מאפס:

 
 LEN ({'Jim''Carl''Sue'}) // returns {343}

והשתמשו בא שלילי כפול כדי לכפות את ערכי TRUE ו- FALSE ל- 1s ו- 0s:

 
 LEN ({343)>0 // returns {TRUETRUETRUE}

לבסוף, אנו מוסיפים את התוצאות עם פונקציית SUM :

כיצד להסיר כפילויות ב- Excel 2010
 
--({TRUETRUETRUE}) // returns {111}

מערך זה מועבר ישירות לפונקציית COUNTA, המחזירה ספירה סופית:

 
= SUM ({111}) // returns 3

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

ספירה ייחודית עם מספר קריטריונים

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

 
= COUNTA ({'Jim''Carl''Sue'}) // returns 3

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

הכנס תאריך ושעה באקסל

לפרטים נוספים ראו סרטון הדרכה זה: כיצד לסנן עם מספר קריטריונים .

COUNTA

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

 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,(C6:C15=H6)*(D6:D15='june'))))>0))

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

אין מערכים דינמיים

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

נוסחאות מערך דינמי זמינים ב- משרד 365 רק. הסופר דייב בראנס


^