חלץ פריטים ייחודיים מרשימה

כדי לחלץ רק ערכים ייחודיים מרשימה או מעמודה, תוכל להשתמש בנוסחת מערך המבוססת על INDEX, MATCH ו- COUNTIF. בדוגמה המוצגת, הנוסחה ב- D5, שהועתקה למטה, היא:

{= INDEX (list, MATCH (0, COUNTIF (uniques,list),0))}

היכן 'הרשימה' נמצאת טווח בשם B5: B11.

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

מחק שורות ריקות ב- Excel Excel

הליבה של נוסחה זו היא חיפוש בסיסי עם INDEX:

 
{= INDEX (list, MATCH (0, COUNTIF ($D:D4,list),0))}

במילים אחרות, תן ל- INDEX את הרשימה ומספר השורות, ו- INDEX יחזיר ערך להוספה לרשימה הייחודית.

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

 
= INDEX (list,row)

הנה, גOUNTIF סופר כמה פעמים פריטים הנמצאים ברשימה הייחודית מופיעים ברשימת האב, באמצעות התייחסות מרחיבה לטווח,$ D $ 4: D4.

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

שימו לב להתייחסותמתחיל ב- D4,שורה אחת מֵעַל הערך הייחודי הראשון, ברשימה הייחודית. זה בכוונה - אנחנו רוצים לספור פריטים * כבר * ברשימה הייחודית, ולא נוכל לכלול את התא הנוכחי מבלי ליצור הפניה מעגלית. אז, אנו מתחילים בשורה למעלה.

חשוב: ודא שהכותרת לרשימה הייחודית אינה מופיעה ברשימת האבנים.

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

 
 COUNTIF ($D:D4,list)

הערה: COUNTIF מטפל בקריטריונים מרובים עם קשר 'OR' (כלומר COUNTIF (טווח, {'אדום', 'כחול', 'ירוק'}) סופר אדום, כחול או ירוק.

לחשב את מספר הימים מהיום

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

 
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // row 8

MATCH מאתרת פריטים על ידי חיפוש ספירה של אפס (כלומר חיפוש פריטים שטרם מופיעים ברשימה הייחודית). זה עובד, כי MATCH תמיד מחזיר את ההתאמה הראשונה כשיש כפילויות.

לבסוף, המיקומים מוזנים ל- INDEX כמספרי שורות, ו- INDEX מחזיר את השם במיקום זה.

גרסת non -array עם LOOKUP

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

 
 MATCH (0,{0000000},0) // 1 (Joe)  MATCH (0,{1000100},0) // 2 (Bob)  MATCH (0,{1100101},0) // 3 (Sue)  MATCH (0,{1111101},0) // 6 (Aya)

מבנה הנוסחה דומה לנוסחת INDEX MATCH לעיל, אך LOOKUP יכול להתמודד עם פעולת המערך באופן מקורי.

• COUNTIF מחזיר ספירות של כל ערך מתוך 'רשימה' ב- הרחבת טווח $ D $ 4: D4
• השוואה לאפס יוצרת מערך של ערכי TRUE ו- FALSE
• המספר 1 מחולק במערך, ויוצר מערך של 1s ושגיאות DIV/0
• מערך זה הופך להיות וקטור החיפוש בתוך LOOKUP
• ערך החיפוש של 2 גדול מכל הערכים ב- lookup_vector
• LOOKUP יתאים לערך ה- non-error האחרון במערך החיפוש
• LOOKUP מחזיר את הערך המתאים ב- result_vector, הטווח בשם 'רשימה'

חלץ פריטים שמופיעים פעם אחת בלבד

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

 
= LOOKUP (2,1/( COUNTIF ($D:D4,list)=0),list)

התוספת היחידה היא הביטוי COUNTIF השני:

כיצד להמיר מספרים לטקסט

 
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)

כאן, COUNTIF מחזיר מערך של ספירות פריטים כך:

 
 COUNTIF (list,list)=1

אשר מושווים ל -1, וכתוצאה מכך מערך של ערכים TRUE/FALSE:

 
{2222212}

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

ב Excel 365 , ה פונקציה ייחודית היא הדרך הטובה ביותר לחלץ ערכים ייחודיים.