תוכן
מזל טוב! הגעת ליום האחרון של המרתון 30 פונקציות אקסל ב-30 ימים. זה היה מסע ארוך ומעניין שבמהלכו למדת דברים שימושיים רבים על פונקציות Excel.
ביום ה-30 למרתון נקדיש את לימוד הפונקציה עקיף (INDIRECT), המחזירה את הקישור שצוין במחרוזת הטקסט. עם פונקציה זו, אתה יכול ליצור רשימות נפתחות תלויות. לדוגמה, בעת בחירת מדינה מתוך רשימה נפתחת קובע אילו אפשרויות יופיעו ברשימה הנפתחת של העיר.
אז בואו נסתכל מקרוב על החלק התיאורטי של הפונקציה עקיף (עקיף) ולחקור דוגמאות מעשיות ליישום שלה. אם יש לך מידע נוסף או דוגמאות, אנא שתף אותם בתגובות.
פונקציה 30: עקיפה
פונקציה עקיף (INDIRECT) מחזירה את הקישור שצוין במחרוזת הטקסט.
כיצד ניתן להשתמש בפונקציה INDIRECT?
מאז הפונקציה עקיף (INDIRECT) מחזיר קישור שניתן על ידי מחרוזת טקסט, אתה יכול להשתמש בו כדי:
- צור קישור ראשוני שאינו משתנה.
- צור הפניה לטווח סטטי בשם.
- צור קישור באמצעות מידע על גיליון, שורה ועמודה.
- צור מערך של מספרים שאינו משתנה.
תחביר INDIRECT (עקיף)
פונקציה עקיף (INDIRECT) יש את התחביר הבא:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) הוא הטקסט של הקישור.
- a1 - אם שווה ל-TRUE (TRUE) או לא צוין, אזי הסגנון של הקישור ישמש A1; ואם FALSE (FALSE), אז הסגנון R1C1.
מלכודות INDIRECT (עקיף)
- פונקציה עקיף (עקיף) מחושב מחדש בכל פעם שהערכים בגליון העבודה של Excel משתנים. זה יכול להאט מאוד את חוברת העבודה שלך אם הפונקציה משמשת בנוסחאות רבות.
- אם הפונקציה עקיף (INDIRECT) יוצר קישור לחוברת עבודה אחרת של Excel, חוברת עבודה זו חייבת להיות פתוחה אחרת הנוסחה תדווח על שגיאה #REF! (#קישור!).
- אם הפונקציה עקיף (INDIRECT) מתייחס לטווח החורג ממגבלת השורות והעמודות, הנוסחה תדווח על שגיאה #REF! (#קישור!).
- פונקציה עקיף (INDIRECT) לא יכול להתייחס לטווח בעל שם דינמי.
דוגמה 1: צור קישור ראשוני ללא תזוזה
בדוגמה הראשונה, עמודות C ו-E מכילות את אותם המספרים, הסכומים שלהם מחושבים באמצעות הפונקציה SUM (SUM) גם זהים. עם זאת, הנוסחאות מעט שונות. בתא C8, הנוסחה היא:
=SUM(C2:C7)
=СУММ(C2:C7)
בתא E8, הפונקציה עקיף (INDIRECT) יוצר קישור לתא ההתחלתי E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
אם תכניס שורה בראש הגיליון ותוסיף את הערך עבור ינואר (ינואר), אז הסכום בעמודה C לא ישתנה. הנוסחה תשתנה, בתגובה להוספת שורה:
=SUM(C3:C8)
=СУММ(C3:C8)
עם זאת, הפונקציה עקיף (בלתי ישיר) מתקן את E2 כתא ההתחלה, כך שחודש ינואר נכלל אוטומטית בחישוב הסכומים של העמודה E. תא הקצה השתנה, אך תא ההתחלה לא הושפע.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
דוגמה 2: קישור לטווח בעל שם סטטי
פונקציה עקיף (INDIRECT) יכול ליצור הפניה לטווח בעל שם. בדוגמה זו, התאים הכחולים מרכיבים את הטווח NumList. בנוסף, נוצר גם טווח דינמי מהערכים בעמודה B NumListDyn, בהתאם למספר המספרים בעמודה זו.
ניתן לחשב את הסכום עבור שני הטווחים על ידי מתן השם שלו כארגומנט לפונקציה SUM (SUM), כפי שניתן לראות בתאים E3 ו-E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
במקום להקליד שם טווח בפונקציה SUM (SUM), אתה יכול להתייחס לשם הכתוב באחד מתאי גליון העבודה. למשל, אם השם NumList כתוב בתא D7, אז הנוסחה בתא E7 תהיה כך:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
לצערי הפונקציה עקיף (INDIRECT) לא יכול ליצור הפניה לטווח דינמי, כך שכאשר תעתיק נוסחה זו לתא E8, תקבל שגיאה #REF! (#קישור!).
דוגמה 3: צור קישור באמצעות מידע על גיליון, שורה ועמודה
אתה יכול בקלות ליצור קישור המבוסס על מספרי השורה והעמודות, כמו גם להשתמש בערך FALSE (FALSE) עבור ארגומנט הפונקציה השני עקיף (עקיף). כך נוצר קישור הסגנון R1C1. בדוגמה זו, הוספנו בנוסף את שם הגיליון לקישור - 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
דוגמה 4: צור מערך מספרים שאינו משתנה
לפעמים אתה צריך להשתמש במערך של מספרים בנוסחאות אקסל. בדוגמה הבאה, נרצה לעשות ממוצע של 3 המספרים הגדולים ביותר בעמודה B. ניתן להזין את המספרים בנוסחה, כפי שנעשה בתא D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
אם אתה צריך מערך גדול יותר, לא סביר שתרצה להזין את כל המספרים בנוסחה. האפשרות השנייה היא להשתמש בפונקציה שׁוּרָה (ROW), כפי שנעשה בנוסחת המערך שהוזנה בתא D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
האפשרות השלישית היא להשתמש בפונקציה שׁוּרָה (STRING) יחד עם עקיף (עקיף), כפי שנעשה עם נוסחת המערך בתא D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
התוצאה עבור כל 3 הנוסחאות תהיה זהה:
עם זאת, אם יוכנסו שורות בראש הגיליון, הנוסחה השנייה תחזיר תוצאה לא נכונה בשל העובדה שההפניות בנוסחה ישתנו יחד עם הסטת השורה. כעת, במקום הממוצע של שלושת המספרים הגדולים ביותר, הנוסחה מחזירה את הממוצע של המספרים ה-3, ה-4 וה-5 בגודלם.
שימוש בפונקציות עקיף (INDIRECT), הנוסחה השלישית שומרת את הפניות השורות הנכונות וממשיכה להציג את התוצאה הנכונה.