פונקציית Excel INDEX - דוגמאות לנוסחאות + סרטון חינם

פונקציית Excel INDEX (דוגמאות + וידאו)

מתי להשתמש בפונקציית INDEX של Excel

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

מה זה מחזיר

הוא מחזיר את הערך מטבלה עבור מספר השורה שצוין ומספר העמודה.

תחביר

= INDEX (מערך, שורה_מספר, [col_num])
= INDEX (מערך, שורה_מספר, [col_num], [area_num])

לפונקציה INDEX יש 2 תחביר. הראשון משמש ברוב המקרים, אולם במקרה של חיפושים תלת כיווניים, נעשה שימוש בשני (מכוסה בדוגמה 5).

טענות קלט

• מערך - א טווח תאים או קבוע מערך.
• מספר_מספר - מספר השורה שממנו יש להביא את הערך.
• [col_num] - מספר העמודה שממנה יש להביא את הערך. אמנם זהו טיעון אופציונלי, אך אם לא מסופק row_num, יש לתת אותו.
• [area_num] - (אופציונלי) אם ארגומנט מערך מורכב מטווחים מרובים, מספר זה ישמש לבחירת ההפניה מכל הטווחים.

הערות נוספות (דברים משעממים … אך חשוב לדעת)

• אם מספר השורה או מספר העמודה הוא 0, הוא מחזיר את הערכים של כל השורה או העמודה בהתאמה.
• אם נעשה שימוש בפונקציה INDEX מול הפניה לתא (כגון A1 :) היא מחזירה הפניה לתא במקום ערך (ראה דוגמאות להלן).
• נמצא בשימוש נרחב ביותר יחד עם פונקציית MATCH.
• שלא כמו VLOOKUP, הפונקציה INDEX יכולה להחזיר ערך משמאל לערך החיפוש.
• לפונקציה INDEX יש שתי צורות - טופס מערך וטופס הפניה
  • 'טופס מערך' הוא המקום בו אתה מביא ערך המבוסס על מספר שורה וטור מתוך טבלה נתונה.
  • 'טופס הפניה' הוא המקום בו יש מספר טבלאות, ואתה משתמש בארגומנט area_num כדי לבחור את הטבלה ולאחר מכן להביא ערך בתוכו באמצעות השורה ומספר העמודות (ראה דוגמה חיה למטה).

פונקציית Excel INDEX - דוגמאות

להלן שש דוגמאות לשימוש בפונקציית INDEX של Excel.

דוגמה 1 - מציאת סימני טום בפיזיקה (חיפוש דו כיווני)

נניח שיש לך מערך נתונים כפי שמוצג להלן:

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

= INDEX ($ B $ 3: $ E $ 10,3,2)

נוסחת INDEX זו מציינת את המערך כ- $ B $ 3: $ E $ 10 בעל הסימנים לכל הנושאים. ואז הוא משתמש במספר השורה (3) ובמספר העמודה (2) כדי להביא את הציונים של טום בפיזיקה.

דוגמה 2 - הפיכת ערך LOOKUP לדינמי באמצעות פונקציית MATCH

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

משהו כפי שמוצג להלן:

ניתן לעשות זאת באמצעות שילוב של INDEX ופונקציית MATCH.

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

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

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

• מספר השורה הדינמית ניתן על ידי החלק הבא של הנוסחה - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). הוא סורק את שם התלמידים ומזהה את ערך החיפוש ($ G $ 5 במקרה זה). לאחר מכן הוא מחזיר את מספר השורה של ערך החיפוש במערך הנתונים. לדוגמה, אם ערך החיפוש הוא מאט, הוא יחזיר 1, אם הוא בוב, הוא יחזיר 2 וכן הלאה.
• מספר העמודה הדינמית ניתן על ידי החלק הבא של הנוסחה - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). הוא סורק את שמות הנושאים ומזהה את ערך החיפוש ($ H $ 4 במקרה זה). לאחר מכן הוא מחזיר את מספר העמודה של ערך החיפוש במערך הנתונים. לדוגמה, אם ערך החיפוש הוא מתמטיקה, הוא יחזיר 1, אם הוא פיזיקה, הוא יחזיר 2 וכן הלאה.

דוגמה 3 - שימוש ברשימות נפתחות כערכי חיפוש

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

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

משהו כפי שמוצג להלן:

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

כיצד להכין זאת:

הנוסחה המשמשת במקרה זה היא אותה המשמשת בדוגמה 2.

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

ערכי החיפוש הוסבו לרשימות נפתחות.

להלן השלבים ליצירת הרשימה הנפתחת של Excel:

• בחר את התא שבו אתה רוצה את הרשימה הנפתחת. בדוגמה זו, ב- G4, אנו רוצים את שמות התלמידים.
• עבור אל נתונים -> כלי נתונים -> אימות נתונים.
• בתיבה דיאלוג אימות נתונים, בכרטיסיה הגדרות, בחר רשימה מהתפריט הנפתח אפשר.
• במקור, בחר $ A $ 3: $ A $ 10
• לחץ על אישור.

כעת תהיה לך הרשימה הנפתחת בתא G5. באופן דומה, אתה יכול ליצור אחד ב- H4 עבור הנבדקים.

דוגמה 4 - החזרת ערכים משורה/עמודה שלמה

בדוגמאות שלעיל, השתמשנו בפונקציית Excel INDEX כדי לבצע חיפוש דו-כיווני ולקבל ערך יחיד.

עכשיו, מה אם אתה רוצה לקבל את כל הציונים של סטודנט. זה יכול לאפשר לך למצוא את הציון המקסימלי/מינימלי של אותו תלמיד, או את הציונים הכוללים שנקלעו בכל המקצועות.

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

הנה הטריק.

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

אז הנוסחה לכך תהיה:

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)

עכשיו הנוסחה הזו. אם נעשה שימוש כפי שהוא, יחזיר את הערך #! שְׁגִיאָה. למרות שהוא מציג את השגיאה, בחלק האחורי הוא מחזיר מערך המכיל את כל הציונים עבור טום - {57,77,91,91}.

אם תבחר את הנוסחה במצב העריכה ולחץ על F9, תוכל לראות את המערך שהיא מחזירה (כפי שמוצג להלן):

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

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

= SUM (INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))

בקווים דומים, כדי לחשב את הציון הגבוה ביותר, אנו יכולים להשתמש במקס/גדול ולחישוב המינימום, אנו יכולים להשתמש ב- MIN/SMALL.

דוגמא 5 - בדיקת שלוש כיוונים באמצעות INDEX/MATCH

פונקציית Excel INDEX בנויה להתמודדות עם חיפושים תלת-כיווניים.

מהו חיפוש תלת כיווני?

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

עכשיו, נניח שבעוד שנה יש לסטודנט שלוש רמות בחינות שונות, מבחן יחידה, אמצע ובחינה אחרונה (זה מה שהיה לי כשהייתי סטודנט).

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

להלן דוגמא לחיפוש תלת כיווני:

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

להלן הנוסחה המשמשת בתא H4:

= INDEX (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0), MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0), IF ($ H $ 2 = "מבחן יחידה", 1, IF ($ H $ 2 = "אמצע טווח", 2,3)))

בואו נשבור את הנוסחה הזו כדי להבין כיצד היא פועלת.

נוסחה זו דורשת ארבעה טיעונים. INDEX היא אחת הפונקציות האלה ב- Excel שיש לה יותר מתחביר אחד.

= INDEX (מערך, שורה_מספר, [col_num])
= INDEX (מערך, שורה_מספר, [col_num], [area_num])

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

כעת נראה כל חלק בנוסחה המבוסס על התחביר השני.

• מערך - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): במקום להשתמש במערך יחיד, במקרה זה, השתמשנו בשלושה מערכים בתוך סוגריים.
• row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): פונקציית MATCH משמשת לאיתור המיקום של שם התלמיד בתא $ G $ 4 ברשימת שם התלמיד.
• col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): פונקציית MATCH משמשת לאיתור המיקום של שם הנושא בתא $ H $ 3 ברשימת שם הנבדק.
• [area_num] - IF ($ H $ 2 = "בדיקת יחידה", 1, IF ($ H $ 2 = "אמצע טווח", 2,3)): ערך מספר האזור מספר לפונקציה INDEX באיזה מערך לבחור. בדוגמה זו, יש לנו שלושה מערכים בטיעון הראשון. אם תבחר בדיקת יחידה מהתפריט הנפתח, הפונקציה IF מחזירה 1 ופונקציות INDEX בוחרות מערך ראשון מתוך שלושת המערכים (שהם $ B $ 3: $ E $ 7).

דוגמה 6 - יצירת הפניה באמצעות הפונקציה INDEX (טווחי שמות דינמיים)

זהו שימוש פרוע אחד בפונקציית Excel INDEX.

ניקח דוגמא פשוטה.

יש לי רשימת שמות כפי שמוצג להלן:

עכשיו אני יכול להשתמש בפונקציית INDEX פשוטה כדי לקבל את שם המשפחה ברשימה.

להלן הנוסחה:

= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

פונקציה זו פשוט סופרת את מספר התאים שאינם ריקים ומחזירה את הפריט האחרון מרשימה זו (היא פועלת רק כאשר אין ריקים ברשימה).

עכשיו, מה כאן מגיע הקסם.

אם תשים את הנוסחה מול הפניה לתא, הנוסחה תחזיר הפניה לתא של הערך התואם (במקום הערך עצמו).

= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

היית מצפה שהנוסחה לעיל תחזור = A2: "ג'וש" (כאשר ג'וש הוא הערך האחרון ברשימה). עם זאת, הוא מחזיר = A2: A9 ומכאן שאתה מקבל מערך שמות כפי שמוצג להלן:

דוגמה מעשית אחת שבה טכניקה זו יכולה להועיל היא יצירת טווחים בעלי שם דינמי.

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

הערה: ניסיתי כמיטב יכולתי לקרוא את ההדרכה הזו בהוכחה, אך במקרה שתמצא שגיאות או שגיאות כתיב, אנא יידע אותי 🙂

פונקציית Excel INDEX - הדרכת וידאו

• פונקציית VLOOKUP של Excel.
• פונקציית HLOOKUP של Excel.
• פונקציית INDIRECT של Excel.
• פונקציית Excel MATCH.
• פונקציית קיזוז Excel.

אולי תאהב גם את מדריכי האקסל הבאים:

• VLOOKUP Vs. אינדקס/התאמה
• התאמת אינדקס Excel
• ערכי חיפוש והחזרה בשורה/עמודה שלמה.