פונקציית INDIRECT של Excel (מוסברת עם דוגמאות + וידאו)

פונקציית Excel INDIRECT - סקירה כללית

ניתן להשתמש בפונקציה INDIRECT ב- Excel כאשר יש לך הפניה של תא או טווח כמחרוזת טקסט וברצונך לקבל את הערכים מהפניות אלה.

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

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

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

תחביר פונקציה בלתי ישירה

= INDIRECT (ref_text, [a1])

טענות קלט

• ref_text - מחרוזת טקסט המכילה את ההפניה לתא או לטווח בשם. זו חייבת להיות הפניה לתא חוקית, אחרת הפונקציה תחזיר #REF! שְׁגִיאָה
• [a1] - ערך לוגי המציין לאיזה סוג הפניה להשתמש טקסט ref. זה יכול להיות TRUE (המציין הפניה לסגנון A1) או FALSE (המציין הפניה בסגנון R1C1). אם הוא הושמט, הוא נכון כברירת מחדל.

הערות נוספות

• INDIRECT היא פונקציה נדיפה. המשמעות היא שהוא מחושב מחדש בכל פעם שחוברת העבודה של Excel פתוחה או בכל פעם שמתבצע חישוב בגליון העבודה. זה מוסיף את זמן העיבוד ומאט את חוברת העבודה שלך. למרות שאתה יכול להשתמש בנוסחה העקיפה עם מערכי נתונים קטנים עם השפעה מועטה או ללא השפעה על המהירות, ייתכן שתראה שהיא הופכת את חוברת העבודה שלך לאיטית יותר בעת השימוש בה עם מערכי נתונים גדולים.
• טקסט ההפניה (ref_text) יכול להיות:
  • הפניה לתא שמכיל בתורו הפניה בפורמט הפניה בסגנון A1 או R1C1.
  • התייחסות לתא במרכאות כפולות.
  • טווח בשם המחזיר הפניה

דוגמאות כיצד להשתמש בפונקציה עקיפה ב- Excel

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

דוגמה 1: השתמש בהפניה לתא כדי להביא את הערך

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

הנוסחה בתא C1 היא:

= INDIRECT ("A1")

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

עכשיו אם אתה חושב, למה אני לא פשוט משתמש ב- = A1 במקום להשתמש בפונקציה INDIRECT, יש לך שאלה תקפה.

הנה הסיבה…

כאשר אתה משתמש = A1 אוֹ = $ A $ 1, זה נותן לך את אותה התוצאה. אך כאשר תוסיף שורה מעל השורה הראשונה, תבחין כי הפניות התא ישתנו אוטומטית לחשבון עבור השורה החדשה.

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

דוגמה 2: השתמש בהתייחסות לתא בתא כדי להביא את הערך

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

בדוגמה לעיל, לתא A1 יש את הערך 123.

לתא C1 יש את ההתייחסות לתא A1 (כמחרוזת טקסט).

כעת, כאשר אתה משתמש בפונקציה INDIRECT ומשתמש ב- C1 כארגומנט (שבתורו יש כתובת תא כמחרוזת טקסט), הוא יהפוך את הערך בתא A1 להפניה תא חוקית.

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

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

כמו כן, במידה ומחרוזת הטקסט בתא C1 אינה הפניה לתא תקפה, הפונקציה העקיפה תחזיר את ה- #REF! שְׁגִיאָה.

דוגמה 3: יצירת הפניה באמצעות ערך בתא

באפשרותך גם ליצור הפניה לתא באמצעות שילוב של אלפבית העמודה ומספר השורה.

לדוגמה, אם תא C1 מכיל את המספר 2 ואתה משתמש בנוסחה = INDIRECT ("A" ו- C1) אז הוא יתייחס לתא A2.

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

אם מחרוזת הטקסט שבה אתה משתמש בנוסחה נותנת הפניה שאקסל לא מבין, היא תחזיר את שגיאת ref (#REF!).

דוגמה 4: חישוב סכום של טווח תאים

תוכל גם להתייחס לטווח תאים באותו אופן שבו אתה מתייחס לתא בודד באמצעות הפונקציה INDIRECT ב- Excel.

לדוגמה, = INDIRECT ("A1: A5") יתייחס לטווח A1: A5.

לאחר מכן תוכל להשתמש בפונקציית SUM כדי למצוא את הסכום הכולל או את הפונקציה LARGE/SMALL/MIN/MAX לביצוע חישובים אחרים.

בדיוק כמו הפונקציה SUM, אתה יכול גם להשתמש בפונקציות כגון LARGE, MAX/MIN, COUNT וכו '.

דוגמה 5: יצירת הפניה לגיליון באמצעות הפונקציה INDIRECT

הדוגמאות לעיל כיסו כיצד להפנות תא באותו גליון עבודה. תוכל גם להשתמש בנוסחה INDIRECT כדי להתייחס לתא בדף עבודה אחר או בחוברת עבודה אחרת.

הנה משהו שאתה צריך לדעת על ההתייחסות לגיליונות אחרים:

• נניח שיש לך דף עבודה עם השם גיליון 1, ובתוך הגיליון בתא A1, יש לך את הערך 123. אם אתה עובר לדף אחר (נניח גיליון 2) ומתייחס לתא A1 בגיליון 1, הנוסחה תהיה: = גיליון 1! A1

אבל…

• אם יש לך דף עבודה המכיל שתיים או יותר משתי מילים (עם תו רווח ביניהן), ואתה מתייחס לתא A1 בגיליון זה מגליון אחר, הנוסחה תהיה: = 'מערך נתונים'! A1

במקרה של מספר מילים, Excel מוסיף אוטומטית מרכאות בודדות בתחילת ובסוף שם הגיליון.

כעת נראה כיצד ליצור פונקציה INDIRECT להתייחסות לתא בגליון עבודה אחר.

נניח שיש לך גיליון בשם Dataset ותא A1 בו יש את הערך 123.

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

= INDIRECT ("'מערך נתונים'! A1")

כפי שאתה יכול לראות, ההתייחסות לתא צריכה להכיל גם את שם גליון העבודה.

אם יש לך את שם גליון העבודה בתא (נניח A1), תוכל להשתמש בנוסחה הבאה:

= INDIRECT ("'" & A1 & "'! A1")

אם יש לך את שם גליון העבודה בתא A1 וכתובת התא בתא A2, אז הנוסחה תהיה:

= INDIRECT ("'" & A1 & "'!" & A2)

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

זה יכול להיות שימושי כאשר אתה מנסה ליצור גיליון סיכום ששואב את הנתונים מרבדים שונים.

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

דוגמה 6: התייחסות לטווח בשם באמצעות פורמולה INDIRECT

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

לדוגמה, נניח שיש לך את הציונים ל -5 תלמידים בשלושה נושאים כפי שמוצג להלן:

בדוגמה זו, בואו נקרא לתאים:

• B2: B6: מתמטיקה
• C2: C6: פיזיקה
• D2: D6: כימיה

כדי לתת שם לטווח תאים, פשוט בחר את התאים ועבור לתיבת השמות, הזן את השם והקש enter.

כעת תוכל להתייחס לטווחים אלה בשם באמצעות הנוסחה:

= INDIRECT ("טווח עם שם")

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

= ממוצע (INDIRECT ("מתמטיקה"))

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

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

דוגמה 7: יצירת רשימה נפתחת תלויה באמצעות פונקציית Excel INDIRECT

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

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

עכשיו כדי ליצור רשימה נפתחת תלויה, עליך ליצור שני טווחים בשם, A2: A5 עם השם US ו- B2: B5 עם השם India.

כעת בחר תא D2 וצור רשימה נפתחת עבור הודו וארה"ב. זו תהיה הרשימה הנפתחת הראשונה שבה המשתמש מקבל את האפשרות לבחור מדינה.

כעת כדי ליצור רשימה נפתחת תלויה:

• בחר תא E2 (התא שבו ברצונך לקבל את הרשימה הנפתחת התלויה).
• לחץ על הכרטיסייה נתונים
• לחץ על אימות נתונים.
• בחר רשימה כקריטריוני האימות והשתמש בנוסחה הבאה בשדה המקור: = INDIRECT ($ D $ 2)
• לחץ על אישור.

כעת, כאשר אתה נכנס לארה"ב בתא D2, התפריט הנפתח בתא E2 יציג את המדינות בארה"ב.

וכאשר אתה נכנס להודו בתא D2, התפריט הנפתח בתא E2 יציג את המדינות בהודו.

אז אלו כמה דוגמאות לשימוש בפונקציה INDIRECT ב- Excel. דוגמאות אלה יעבדו על כל גירסאות Excel (Office 365, Excel2021-2022/2016/2013/2013)

אני מקווה שמצאת הדרכה שימושית.

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