=INDIRECT(E2) קוראת את התא שהכתובת שלו כתובה כטקסט ב-E2. אם ב-E2 כתוב C4, הנוסחה מחזירה את הערך שב-C4. אפשר גם לבנות את הכתובת מחלקים: =INDIRECT("C"&E3) קוראת את עמודה C במספר השורה שב-E3.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
F2 קורא את C4, המחיר של Carrot, $0.80. שנו את E2 ל-C3 או ל-B5 ו-F2 מתעדכן בהתאם. F3 מחבר את "C" ואת ה-6 שב-E3 לכתובת C6 ומחזיר $1.10. שנו את E3 ל-2 בשביל המחיר של Apple.
התחביר של INDIRECT
=INDIRECT(ref_text, [a1])
ref_text: טקסט שמאיית הפניה:"C4","B2:B6","Prices!A2","'Price list'!A2:B9".a1:TRUEאו מושמט לכתובות בסגנון A1.FALSEקורא סגנון R1C1, שבו"R4C3"פירושו שורה 4, עמודה 3, וזה מתאים כשגם השורה וגם העמודה הן מספרים.
אם הטקסט אינו כתובת תקינה, התוצאה היא #REF!. INDIRECT מחזירה הפניה אמיתית, ולכן היא עובדת בתוך SUM, COUNTIF, VLOOKUP וכל פונקציה שמקבלת טווח.
בניית טווח ממספרים
הכתובת יכולה להיות טווח שלם. חיבור מספר לתוכה נותן טווח שהגודל שלו מגיע מתא.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
עם 3 ב-E2 הטקסט הופך ל-B2:B4, ו-F2 מחבר את Jan עד Mar: 12,900. הגדירו את E2 ל-6 בשביל חצי השנה, 27,900. ה-1+E2 נמצא שם כי הנתונים מתחילים בשורה 2. אפשר לכתוב את אותו סכום בלי INDIRECT, =SUM(B2:INDEX(B2:B7,E2)), שאינה נדיפה; העמוד של OFFSET משווה בין האפשרויות.
הפניה לגיליון ששמו כתוב בתא
גם שם הגיליון יכול להגיע מתא. זה הופך נוסחת סיכום אחת לחיפוש בין גיליונות: כל שורה קוראת את הגיליון ששמו בעמודה A. גרשיים בודדים סביב השם שומרים על הנוסחה עובדת גם לשמות עם רווחים.
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
B2 בונה את הטקסט 'Jan'!B2:B4 ומסכם אותו: 12,500. B3 ו-B4 הם אותה נוסחה שהועתקה למטה, ולכן הם קוראים את Feb (12,200) ואת Mar (13,700). פתחו את הלשונית Feb ושנו מספר: הסיכום מתעדכן. הקלידו Feb במקום Jan ב-A2 ו-B2 מסכם עכשיו את Feb. ה-B2:B4 שבתוך המירכאות הוא טקסט, ולכן הוא לא משתנה כשהנוסחה מועתקת למטה; רק ההפניה ל-A2 משתנה.
רשימות נפתחות תלויות
רשימה נפתחת שנייה שהפריטים שלה תלויים בראשונה היא העבודה הקלאסית של INDIRECT. באקסל ההגדרה הרגילה היא:
- שימו את הפריטים של כל קטגוריה בעמודה ותנו לכל טווח שם לפי הקטגוריה שלו: בחרו את העמודות עם הכותרות שלהן והשתמשו ב'נוסחאות > צור מתוך בחירה > שורה עליונה'. זה יוצר את השמות
Fruit,Vegetableו-Dairy. - תנו ל-A2 רשימה של הקטגוריות: 'נתונים > אימות נתונים', 'אפשר: רשימה', מקור
Fruit,Vegetable,Dairy. - תנו ל-B2 רשימה עם המקור
=INDIRECT(A2). כשב-A2 כתוב Fruit, הרשימה קוראת את הטווח שנקרא Fruit.
הגיליון למטה בונה את אותו הדבר עם גיליון לכל קטגוריה במקום טווח עם שם. D2 משתמש ב-INDIRECT כדי לשפוך את הפריטים של הגיליון ששמו ב-A2, והרשימה של B2 קוראת את D2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
בחרו Dairy ב-A2: D2:D4 עוברים ל-Milk, Butter, Cheese, וכך גם האפשרויות ב-B2. B2 שומר את הערך הישן שלו עד שבוחרים ערך חדש; אקסל מתנהג באותה דרך, ולכן טפסים מוסיפים לעתים קרובות בדיקה כמו =COUNTIF(D2:D4,B2)>0 ליד הפריט. ב-Excel 365 אפשר לוותר על הטווחים עם השם ולהפנות את הרשימה השנייה לנוסחה נשפכת, למשל =INDIRECT("'"&A2&"'!A2:A4") בתא עזר ו-=D2# כמקור. בעמוד של הרשימה הנפתחת יש את שאר ההגדרה.
INDIRECT נדיפה, והיא מתעלמת משורות שנוספו
שתי תופעות לוואי נובעות מכך ש-INDIRECT קוראת טקסט במקום הפניה:
- היא מחושבת מחדש בכל שינוי. אקסל לא יכול לדעת לאילו תאים קטע טקסט יצביע, ולכן הוא מחשב מחדש כל INDIRECT אחרי כל עריכה בכל מקום בחוברת העבודה. כמה עשרות לא מזיקות; עשרות אלפים הופכים כל הקשה לאיטית. INDEX עם מספר שורה (
=INDEX(C:C,E3)) נותנת את אותה תוצאה כמו=INDIRECT("C"&E3)ומחושבת מחדש רק כשהקלטים שלה משתנים. - הכתובת לא זזה. הוסיפו שורה מעל שורה 4 ו-
=C4הופכת ל-=C5, אבל=INDIRECT("C4")עדיין קוראת את C4, שהיא עכשיו שורה אחרת. לפעמים זו בדיוק המטרה, הפניה שחייבת להישאר על תא קבוע לא משנה מה קורה לגיליון. לרוב זה באג שמחכה שמישהו יוסיף שורה.
INDIRECT לחוברת עבודה אחרת עובדת רק כל עוד חוברת העבודה הזו פתוחה; כשהיא סגורה, היא מחזירה #REF!.
תרגול: מחיר לפי מספר שורה
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
תורכם: ב-F2, השתמשו ב-INDIRECT כדי להחזיר את המחיר בעמודה C במספר השורה שכתוב ב-E2.
שאלות נפוצות
מה INDIRECT עושה באקסל?
היא הופכת טקסט להפניה. =INDIRECT("C4") מחזירה את הערך של C4, ו-=INDIRECT(E2) מחזירה את הערך של התא שהכתובת שלו כתובה ב-E2. אפשר לבנות את הכתובת עם &, ולכן =INDIRECT("C"&E2) קוראת את עמודה C במספר השורה שב-E2.
איך מפנים לגיליון אחר שהשם שלו נמצא בתא?
בנו את הכתובת עם שם הגיליון בגרשיים בודדים: =INDIRECT("'"&A2&"'!B2"). הגרשיים שומרים על הנוסחה עובדת גם לשמות עם רווחים. =SUM(INDIRECT("'"&A2&"'!B2:B4")) מסכמת טווח בגיליון הזה.
למה INDIRECT מחזירה #REF!?
הטקסט אינו כתובת תקינה, או שהוא נותן שם של גיליון שלא קיים, או שהוא מצביע לחוברת עבודה אחרת שסגורה. בדקו את הטקסט שהנוסחה בונה על ידי כתיבת אותו ביטוי בתא לבדו, בלי INDIRECT.
האם INDIRECT נדיפה?
כן. אקסל מחשב מחדש כל INDIRECT בכל שינוי בכל מקום בחוברת העבודה, כי הוא לא יכול לדעת מראש לאילו תאים הטקסט יצביע. כמה מהן לא מזיקות; אלפים מאטים את חוברת העבודה. לעתים קרובות INDEX יכולה לעשות את אותה עבודה בלי להיות נדיפה.
