חיפוש והחזרת עמודה שלמה

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

חיפוש והחזרת עמודה שלמה בהתבסס על ערך מסוים
סיכום עמודה שלמה בהתבסס על ערך מסוים
ניתוח נוסף של עמודה שלמה בהתבסס על ערך מסוים

חיפוש והחזרת עמודה שלמה בהתבסס על ערך מסוים

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

תחביר כללי

=INDEX(return_range,0,MATCH(lookup_value,lookup_array,0))

√ הערה: זוהי נוסחה מערך שדורשת ממך להזין עם Ctrl + Shift + Enter.

כדי לקבל רשימה של מכירות Q2, אנא העתק או הזן את הנוסחה למטה בתא I6, לחץ Ctrl + Shift + Enter, ולאחר מכן לחץ פעמיים על התא והקש F9 כדי לקבל את התוצאה:

=INDEX(C5:F11,0,MATCH("Q2",C4:F4,0))

או השתמש בהתייחסות לתא כדי להפוך את הנוסחה לדינמית:

=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))

הסבר לנוסחה

=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))

• MATCH(I5,C4:F4,0): match_type 0 כופה על הפונקציה MATCH להחזיר את המיקום של Q2, הערך ב-I5, בטווח C4:F4, שהוא 2.
• INDEX(C5:F11C5:F11,0,MATCH(I5,C4:F4,0)) = INDEX(C5:F11C5:F11,0,2): הפונקציה INDEX מחזירה את כל הערכים בעמודה שניה של הטווח C5:F11 במערך כזה: {7865;4322;8534;5463;3252;7683;3654}. שים לב שכדי להפוך את המערך גלוי באקסל, עליך ללחוץ פעמיים על התא שבו הזנת את הנוסחה, ולאחר מכן להקיש F9.

סיכום עמודה שלמה בהתבסס על ערך מסוים

מכיוון שיש לנו עכשיו את רשימת המכירות ביד, יהיה קל לנו לקבל את נפח המכירות הכולל של Q2. כל מה שעלינו לעשות הוא להוסיף את פונקציית SUM לנוסחה כדי לסכם את כל ערכי המכירות מהרשימה.

תחביר כללי

=SUM(INDEX(return_range,0,MATCH(lookup_value,lookup_array,0)))

בדוגמה זו, כדי לקבל את נפח המכירות הכולל של Q2, אנא העתק או הזן את הנוסחה למטה בתא I8, והקש Enter כדי לקבל את התוצאה:

=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))

הסבר לנוסחה

=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))

• MATCH(I5,C4:F4,0): match_type 0 כופה על הפונקציה MATCH להחזיר את המיקום של Q2, הערך ב-I5, בטווח C4:F5, שהוא 2.
• INDEX(INDEX(C5:F11C5:F11,0,,0,MATCH(I5,C4:F4,0))) = INDEX(INDEX(C5:F11C5:F11,0,,0,2)): הפונקציה INDEX מחזירה את כל הערכים בעמודה שניה של הטווח C5:F11 במערך כזה: {7865;4322;8534;5463;3252;7683;3654}.
• SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0))) = SUM({7865;4322;8534;5463;3252;7683;3654}): הפונקציה SUM מסכמת את כל הערכים במערך, ואז מקבלת את נפח המכירות הכולל של Q2, $40,773.

ניתוח נוסף של עמודה שלמה בהתבסס על ערך מסוים

לעיבוד נוסף של רשימת מכירות Q2, אתה יכול פשוט להוסיף פונקציות אחרות כגון SUM, AVERAGE, MAX, MIN, LARGE וכו' לנוסחה.

לדוגמה, כדי לקבל נפח מכירות ממוצע במהלך Q2, תוכל להשתמש בנוסחה:

=AVERAGE(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))

כדי לגלות את המכירות הגבוהות ביותר במהלך Q2, השתמש באחת מהנוסחאות הבאות:

=MAX(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
או
=LARGE(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)),1)

פונקציות קשורות

נוסחאות קשורות