ניתוח הפונקציה INDIRECT לפי דוגמאות

במבט ראשון (במיוחד בעת קריאת העזרה), הפונקציה עקיף (עקיף) נראה פשוט ואפילו מיותר. המהות שלו היא להפוך טקסט שנראה כמו קישור לקישור מלא. הָהֵן. אם אנחנו צריכים להתייחס לתא A1, אז נוכל ליצור קישור ישיר באופן רגיל (הזן סימן שוויון ב-D1, לחץ על A1 והקש Enter), או שנוכל להשתמש עקיף לאותה מטרה:

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

"טוב, בסדר," אתה אומר. "ומה התועלת?" 

אבל אל תשפוט לפי הרושם הראשוני - זה מטעה. תכונה זו יכולה לעזור לך בהרבה מצבים.

דוגמה 1. Transpose

קלאסיקה של הז'אנר: אתה צריך להפוך את הדיאטה האנכית

חריץ לאופקי (טרנספוזיציה). כמובן, אתה יכול להשתמש בתוספת מיוחדת או פונקציה TRANSP (לְשַׁרבֵּב) בנוסחת מערך, אבל אתה יכול להסתדר עם שלנו עקיף:

ההיגיון פשוט: כדי לקבל את הכתובת של התא הבא, אנו מדביקים את האות "A" עם התו המיוחד "&" ומספר העמודה של התא הנוכחי, שהפונקציה נותנת לנו COLUMN (טור).

ההליך ההפוך עדיף לעשות קצת אחרת. מכיוון שהפעם אנחנו צריכים ליצור קישור לתאים B2, C2, D2 וכו', נוח יותר להשתמש במצב הקישור R1C1 במקום "קרב הים" הקלאסי. במצב זה, התאים שלנו יהיו שונים רק במספר העמודה: B2=R1C2, C2=R1C3, D2=R1C4 וכו '

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

דוגמה 2. סכום לפי מרווח

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

דוגמה 3. רשימה נפתחת של טבלה חכמה

לפעמים Microsoft Excel לא מתייחס לשמות טבלאות ועמודות חכמות כאל קישורים מלאים. כך, למשל, כאשר מנסים ליצור רשימה נפתחת (tab נתונים - אימות נתונים) מבוסס על עמודה עובדים משולחן חכם אֲנָשִׁים נקבל שגיאה:

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

דוגמה 4. קישורים בלתי שבירים

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

אם תשים קישורים רגילים (הזן =B2 בתא הירוק הראשון ותעתיק אותו למטה), אז כשתמחק, למשל, Dasha, נקבל את ה-#LINK! שגיאה בתא הירוק המתאים לה. (#REF!). במקרה של שימוש בפונקציה ליצירת קישורים עקיף לא תהיה בעיה כזו.

דוגמה 5: איסוף נתונים ממספר גיליונות

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

הבה נניח שהצורה, הגודל, המיקום ורצף הסחורות והחודשים בכל הטבלאות זהים - רק המספרים שונים.

אתה יכול לאסוף נתונים מכל הגיליונות (אל תסכם אותם, אלא תכניס אותם זה לזה ב"ערימה") עם נוסחה אחת בלבד:

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

חסרונות

אם אתה משתמש עקיף (עקיף) אתה צריך לזכור את החולשות שלו:

• אם מקשרים לקובץ אחר (על ידי הדבקת שם הקובץ בסוגריים מרובעים, שם הגיליון וכתובת התא), אז זה עובד רק כשהקובץ המקורי פתוח. אם נסגור אותו, נקבל את השגיאה #LINK!
• INDIRECT לא יכול להתייחס לטווח בעל שם דינמי. על סטטי - אין בעיה.
• INDIRECT היא פונקציה נדיפה או "נדיפה", כלומר היא מחושבת מחדש עבור כל שינוי בתא כלשהו של הגיליון, ולא רק משפיעה על תאים, כמו בפונקציות רגילות. יש לכך השפעה רעה על הביצועים ועדיף לא להיסחף עם טבלאות INDIRECT גדולות.

• כיצד ליצור טווח דינמי עם התאמה אוטומטית
• סיכום על חלון טווח על גיליון עם פונקציית OFFSET