=OFFSET(A1,3,2) מחזירה את התא שנמצא 3 שורות למטה ו-2 עמודות הלאה מ-A1, כלומר C4. תנו לה גם גובה ורוחב והיא מחזירה טווח שלם, ולזה משתמשים ב-OFFSET בעיקר: סכומים וממוצעים על טווח שזז או גדל.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
3 שורות למטה ו-2 הלאה מ-A1 נוחתים על C4, המחיר של Carrot, $0.80. הגדירו את Cols ל-0 בשביל השם Carrot, או את Rows ל-5 בשביל השורה של Milk. שורות ועמודות יכולות להיות שליליות כדי לזוז למעלה או אחורה, ותזוזה מעבר לקצה העליון או הצדדי של הגיליון היא #REF!.
התחביר של OFFSET
=OFFSET(reference, rows, cols, [height], [width])
reference: תא ההתחלה (או טווח).rows,cols: כמה רחוק לזוז. 0 פירושו להישאר.height,width: הגודל של הטווח שיוחזר, כשסופרים מהתא שאליו זזו. כשהם מושמטים, הם בגודל שלreference.
לבדה בתא, OFFSET שמחזירה כמה תאים נשפכת ב-Excel 365; גרסאות ישנות יותר מציגות בדרך כלל #VALUE!. בתוך SUM, AVERAGE, COUNT או MAX היא עובדת כטווח.
סכום של N השורות האחרונות
העבודה הקלאסית של OFFSET: סכום שתמיד מכסה את השורות האחרונות, לא משנה כמה נוספו. COUNT מוצאת כמה ערכים יש, OFFSET זזה למטה אל הראשון מבין N האחרונים, והגובה לוקח N שורות.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
יש 7 ערכים, ולכן OFFSET מתחילה 7-3+1, כלומר 5 שורות מתחת ל-B1, ב-B6, ולוקחת 3 שורות: May עד Jul, 14,900. הקלידו 4900 ב-B9 (אוגוסט) והסכום עובר ל-Jun, Jul ו-Aug, כי COUNT מוצאת עכשיו 8. הטווח B2:B13 משאיר מקום לשאר השנה. בעמודה לא יכולים להיות תאים ריקים באמצע, אחרת COUNT סופרת פחות מדי והחלון נוחת במקום הלא נכון.
ממוצע מתגלגל
כשמעתיקים אותה למטה לאורך עמודה, OFFSET עם היסט שורות שלילי נותנת לכל שורה חלון של השורות שמעליה: כאן הממוצע של החודש הנוכחי ושני החודשים שלפניו.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
C4 מחשב ממוצע של B2:B4 (Jan עד Mar), 4,300. כל שורה מתחת מזיזה את החלון אחת למטה. שנו את ה-3 ל-6 ואת ה--2 ל--5 בשביל ממוצע של שישה חודשים (ואז התחילו את הנוסחה בשורה 7). המקרה הזה בדיוק לא צריך OFFSET בכלל: =AVERAGE(B2:B4) שמועתקת למטה מ-C4 עושה את אותו הדבר, כי הפניות יחסיות כבר זזות. OFFSET מצדיקה את מקומה כשגודל החלון מגיע מתא.
למה INDEX לעתים קרובות הבחירה הטובה יותר
OFFSET היא נדיפה: אקסל מחשב מחדש כל OFFSET אחרי כל עריכה בכל מקום בחוברת העבודה, כי הוא לא יכול לדעת מראש לאילו תאים היא תצביע. גיליון עם אלפים כאלה נעשה איטי. גם INDEX מחזירה הפניה, וטווח שנכתב כ-start:INDEX(...) גדל באותה דרך בלי להיות נדיף:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
שתיהן קוראות את E2 השורות הראשונות של העמודה. גם קשה יותר לבקר את OFFSET: 'עקוב אחר תקדימים' (Trace Precedents) והמסגרות הצבעוניות שאקסל מצייר בזמן עריכת הנוסחה מראים את תא ההתחלה ואת הארגומנטים, לא את הטווח ש-OFFSET מחזירה בסוף. השתמשו ב-OFFSET למודל מהיר או לטווח של תרשים; העדיפו את INDEX בחוברות עבודה גדולות. ב-INDEX יש עוד על החזרת טווחים, ו-INDIRECT היא פונקציית ההפניה הנדיפה האחרת.
תרגול: סכום של N החודשים הראשונים
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
תורכם: ב-F2, השתמשו ב-OFFSET בתוך SUM כדי לסכם את N החודשים הראשונים, כש-N נמצא ב-E2.
שאלות נפוצות
מה OFFSET עושה באקסל?
היא מחזירה הפניה שנמצאת במרחק מסוים של שורות ועמודות מתא התחלה, ואפשר גם לשנות את הגודל שלה. =OFFSET(A1,3,2) היא התא שנמצא 3 שורות למטה ו-2 עמודות ימינה מ-A1, כלומר C4.
איך מסכמים את N השורות האחרונות באקסל?
התחילו מהכותרת וזוזו למטה אל הראשון מבין N הערכים האחרונים: =SUM(OFFSET(B1,COUNT(B2:B100)-N+1,0,N,1)). COUNT מוצאת כמה ערכים יש, והגובה N לוקח את מספר השורות הזה. זה עובד רק כשאין בעמודה רווחים.
למה OFFSET נדיפה?
אקסל מחשב מחדש כל OFFSET אחרי כל שינוי בחוברת העבודה, כי התאים שהיא מצביעה עליהם ידועים רק אחרי שהיא רצה. בחוברות עבודה גדולות זה מאט את העבודה. טווח שנבנה עם INDEX, כמו B2:INDEX(B2:B100,N), עושה את אותה עבודה בלי להיות נדיף.
מה הארגומנטים של OFFSET?
OFFSET(reference, rows, cols, [height], [width]): תא ההתחלה, כמה שורות למטה (שלילי זה למעלה), כמה עמודות הלאה (שלילי זה אחורה), ובאופן אופציונלי הגודל של הטווח שיוחזר.
