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

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

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

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

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

תחביר כללי

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

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

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

=INDEX(C5:F11,MATCH("Jimmy",B5:B11,0),0)

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

=INDEX(C5:F11,MATCH(I5,B5:B11,0),0)

הסבר לנוסחה

=INDEX(C5:F11,MATCH(I5,B5:B11,0),0)

• MATCH(I5,B5:B11,0): match_type 0 כופה על הפונקציה MATCH להחזיר את המיקום של ג'ימי, הערך ב-I5, בטווח B5:B11, שהוא 7 מכיוון שג'ימי נמצא במקום ה-7.
• INDEX(C5:F11C5:F11,MATCH(I5,B5:B11,0),0) = INDEX(C5:F11C5:F11,7,0): הפונקציה INDEX מחזירה את כל הערכים בשורה ה7 של הטווח C5:F11 במערך כזה: {6678,3654,3278,8398}. שים לב שכדי להפוך את המערך לגלויה ב-Excel, עליך ללחוץ פעמיים על התא שבו הזנת את הנוסחה, ולאחר מכן להקיש F9.

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

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

תחביר כללי

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

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

=SUM(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))

הסבר לנוסחה

=SUM(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))

• MATCH(I5,B5:B11,0): match_type 0 כופה על הפונקציה MATCH להחזיר את המיקום של ג'ימי, הערך ב-I5, בטווח B5:B11, שהוא 7 מכיוון שג'ימי נמצא במקום ה-7.
• INDEX(INDEX(C5:F11C5:F11,,MATCH(I5,B5:B11,0),0),0) = INDEX(INDEX(C5:F11C5:F11,,7,0),0): הפונקציה INDEX מחזירה את כל הערכים בשורה ה7 של הטווח C5:F11 במערך כזה: {6678,3654,3278,8398}. שים לב שכדי להפוך את המערך לגלויה ב-Excel, עליך ללחוץ פעמיים על התא שבו הזנת את הנוסחה, ולאחר מכן להקיש F9.
• SUM(INDEX(C5:F11,MATCH(I5,B5:B11,0)),0) = SUM({6678,3654,3278,8398}): הפונקציה SUM מסכמת את כל הערכים במערך, ולאחר מכן מקבלת את נפח המכירות השנתי שביצע ג'ימי, $22,008.

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

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

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

=AVERAGE(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))

כדי לגלות את המכירות הנמוכות ביותר שביצע ג'ימי, השתמש באחת מהנוסחאות הבאות:

=MIN(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))
או
=SMALL(INDEX(C5:F11,MATCH(I5,B5:B11,0),0),1)

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

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