נוסחת מערך חכמה: התייחס לכותרת העמודה בתא האחרון שאינו ריק בשורה

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

שלב את ארבעת הפונקציות IFERROR (), INDEX (), MAX () ו- IF ()

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

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

{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}

תסתכל איך זה עובד צעד אחר צעד

התחל בתנאי IF () המובנה בנוסחת המערך: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}

התנאי IF () יוצר קו עזר פיקטיבי בנוסחת המערך ובודק את התאים B2 עד M2בין אם אלה ריקים או לא. אם תא ריק, הערך -1 מוחזר אחרת באמצעות הפונקציה COLUMN (), מספר העמודה המתאימה בניכוי הערך 1.
הפחתה של 1 נדרשת בנוסחה מכיוון שהעמודה הראשונה בטבלה אינה מכילה את שם החודש, אלא את שם המוצר. להלן תלמד כיצד להשתמש בפונקציה INDEX () להצגת שם החודש המתאים, אשר - אם לא הפחתת את 1 - בגלל העמודה הנוספת המשמשת א. יהיה שגוי בדיוק בעמודה אחת.

אם כל התאים בטווח B2: M2 אם הם ריקים, הערך -1 (ללא מכירות מוצרים) יוצר שגיאה בה אנו משתמשים לייצוג תא ריק. ניתן לראות את קו העזר הפעיל באיור ③ בשורה 3.

בשלב הבא אתה קורא את הערך הגדול ביותר עם הפונקציה MAX (), שבו התנאי IF () מקונן. זהו הערך 12 בשורה 3 (עמודה 13 מינוס 1; ראה קו עזר באיור ③):

{= IFERROR (INDEX ($ B $ 1: $ M $ 1;מקס (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}

אתה מעביר ערך MAX זה לפונקציה INDEX (). החודש הקלנדרי המתאים נקרא בשורה 1. אזור הנתונים של הפונקציה INDEX () הוא האזור $ B $ 1: $ M $ 1. ערך ה- MAX שעבר - בדוגמה 12 - פירושו שהערך השתים -עשרה ברשימה, כלומר דצמבר לחודש דצמבר:

{= IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}

אם כל התאים בטווח החודשים של הטבלה ריקים, הערך הגדול ביותר הוא -1 (ראה מצב IF () בהתחלה). אם הערך -1 מועבר לפונקציית INDEX (), הדבר מוביל בהכרח לערך שגיאה, שכן אזור הרשימה של הפונקציה INDEX () מכיל רק שתים עשרה ערכים ולכן אינו יכול למצוא את הערך -1. אתה תופס ערך שגיאה זה באמצעות הפונקציה IFERROR () ובמקום זאת מחזיר מחרוזת ריקה. בדוגמה, זהו המקרה של מוצר C בשורה 4, מכיוון שלא ניתן היה לסיים חוזה נוסף על מוצר זה:

{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}

מכיוון שזו נוסחת מערך, השלם את רשומת הנוסחה עם שילוב המקשים Ctrl + Shift + Enter.