INDEX ו-MATCH על פני מספר עמודות

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

כיצד לחפש ערך על ידי התאמה על פני מספר עמודות?

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

תחביר כללי

=INDEX(טווח_החזרה,(MATCH(1,MMULT(--(מערך_חיפוש=ערך_חיפוש),TRANSPOSE(COLUMN(מערך_חיפוש)^0)),0)))

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

כדי למצוא את הכיתה של ג'ימי, אנא העתק או הזן את הנוסחה למטה בתא H5, והקש Ctrl + Shift + Enter כדי לקבל את התוצאה:

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

√ הערה: הסימנים דולר ($) לעיל מציינים הפניות מוחלטות, מה שאומר שהטווחים של השמות והכיתות בנוסחה לא ישתנו כאשר תעביר או תעתיק את הנוסחה לתאים אחרים. שים לב שלא כדאי להוסיף סימני דולר להפניה לתא המייצגת את ערך החיפוש, מכיוון שאתה רוצה שהיא תהיה יחסית כאשר תעתיק אותה לתאים אחרים. לאחר שתכניס את הנוסחה, גרור את הידית למלא כלפי מטה כדי להחיל את הנוסחה על התאים מטה.

הסבר של הנוסחה

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5): קטע זה בודק כל ערך בטווח $C$5:$E$7 אם הם שווים לערך בתא G5, ויוצר מערך של TRUE ו-FALSE כך:
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  המינוס הכפול ימיר את ה-TRUEs וה-FALSEs ל-1s ו-0s כדי לייצר מערך כזה:
  {1,0,0;0,0,0;0,0,0}.
• COLUMN($C$5:$E$7): הפונקציה COLUMN מחזירה את מספרי העמודות עבור הטווח $C$5:$E$7 במערך כזה: {3,4,5}.
• TRANSPOSE(TRANSPOSE(COLUMN($C$5:$E$7)^0)^0) = TRANSPOSE(TRANSPOSE({3,4,5}^0)^0): לאחר העלאת החזקה ל-0, כל המספרים במערך {3,4,5} יומרו ל-1: {1,1,1}. הפונקציה TRANSPOSE לאחר מכן ממירה את מערך העמודות למערך שורות כך: {1;1;1}.
• MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPOSE(COLUMN($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): הפונקציה MMULT מחזירה את המכפלה המטריצית של שני המערכים כך: {1;0;0}.
• MATCH(1,MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0),0) = MATCH(1,MATCH(1,{1;0;0},0),0): סוג ההתאמה 0 מכריח את הפונקציה MATCH להחזיר את המיקום של ההתאמה הראשונה של 1 במערך {1;0;0}, שהוא 1.
• INDEX($B$5:$B$7$B$5:$B$7,,,(MATCH(1,MMULT(--($C$5:$E$7=G5),,,TRANSPOSE(COLUMN($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7$B$5:$B$7,,,1): הפונקציה INDEX מחזירה את הערך הראשון בטווח הכיתות $B$5:$B$7, שהוא A.

כדי לחפש בקלות ערך על ידי התאמה על פני מספר עמודות, תוכל גם להשתמש בתוסף האקסל המקצועי שלנו Kutools For Excel.  ראה את ההנחיות כאן כדי להשלים את המשימה.

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

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