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