לְהִצטַיֵן

חלץ מספר התאמות לעמודות נפרדות

Extract Multiple Matches Into Separate Columns

נוסחת Excel: חלץ מספר התאמות לעמודות נפרדותסיכום

כדי לחלץ התאמות מרובות לתאים נפרדים, בעמודות נפרדות, תוכל להשתמש בנוסחת מערך המבוססת על INDEX ו- SMALL. בדוגמה המוצגת, הנוסחה ב- F5 היא:



 
{= IFERROR ( INDEX (names, SMALL ( IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1), COLUMNS ($E:E5))),'')}

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

כיצד להציג שורה כוללת ב- Excel

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





הֶסבֵּר

הערה: נוסחה זו משתמשת בשניים טווחים בשם : 'שמות' מתייחס ל- C5: C11, ו'קבוצות 'מתייחס ל- B5: B11. שמות אלה מוגדרים גם בצילום המסך למעלה.

עיקרה של נוסחה זו הוא זה: אנו משתמשים בפונקציה SMALL ליצירת מספר שורה המתאים ל'התאמה נ ''. ברגע שיש לנו את מספר השורה, אנחנו פשוט מעבירים אותו לפונקציית הפונקציה INDEX, שמחזירה את הערך בשורה זו.



הטריק הוא ש- SMALL עובד עם מערך שנבנה באופן דינמי על ידי IF בקטע הזה:

 
 IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1)

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

 
 ROW (names)- MIN ( ROW (names))+1

התוצאה הסופית היא מערך המכיל מספרים שבהם יש התאמה, ו- FALSE היכן שלא:

כיצד לחשב את ההבדל בין שני תאריכים ושעות באקסל

{1FALSEFALSEFALSEFALSE6FALSE}

מערך זה נכנס ל- SMALL. ערך k עבור SMALL (nth) מגיע מ- הרחבת טווח :

 
 COLUMNS ($E:E5)

כאשר הוא מועתק על פני טבלת התוצאות, הטווח מתרחב וגורם ל- k (nth) לעלות. הפונקציה SMALL מחזירה כל מספר שורה תואם, המסופק לפונקציה INDEX בשם row_num, כאשר הטווח בשם 'שמות' הוא המערך.

שגיאות טיפול

כאשר COLUMNS מחזיר ערך עבור k שאינו קיים, SMALL זורק שגיאת #NUM. זה קורה לאחר שכל ההתאמות התרחשו. כדי לדכא את השגיאה, אנו עוטפים את הנוסחה בפונקציית IFERROR כדי לתפוס שגיאות ולהחזיר מחרוזת ריקה ('').

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


^