פונקציית OFFSET ב-Excel

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

תחביר

=OFFSET (הפניה, שורות, עמודות, [גובה], [רוחב])

ארגומנטים

ערך החזרה

הפונקציה OFFSET תחזיר הפניה לתא המרחיקה מנקודת התחלה נתונה.

הערות פונקציה

1. ערך השגיאה #VALUE! יוחזר כאשר ההתייחסות מתייחסת לטווח תאים לא רציף.
2. ערך השגיאה #REF! יוחזר כאשר ההסטה בשורות ועמודות חורגת מגבולות גיליון העבודה.

דוגמאות

דוגמה 1: שימוש בסיסי בפונקציית OFFSET

החזר הפניה לתא עם הנוסחה הבאה:
=OFFSET(B2,3,1)

במקרה זה, B2 הוא נקודת ההתחלה, המספרים 3 ו-1 אומרים לזוז 3 שורות למטה ועמודה אחת ימינה מ-B2, ולבסוף להחזיר את הערך ב-C5. ראה צילום מסך:

החזר הפניה לטווח תאים עם הנוסחה הבאה:
=OFFSET(B2,3,1,2,2)

במקרה זה, תקבל תוצאות של טווח 2 x 2 הממוקם 3 שורות מתחת ועמודה אחת ימינה מ-B2.

הערה: שגיאת #VALUE! תתרחש כאשר בוחרים רק תא אחד כדי להחיל את פונקציית OFFSET על מנת להחזיר טווח תאים. עליך לבחור טווח 2 x 2 (נאמר 4 תאים ריקים), להכניס את הנוסחה וללחוץ על מקשי Ctrl + Shift + Enter כדי לקבל את התוצאות.

דוגמה 2: השתמש בפונקציית OFFSET לסכום טווח ערכים

כפי שהזכרנו קודם, אם תנסה להשתמש בפונקציית OFFSET כמו =OFFSET(B2,3,1,2,2) לבדה בתא יחיד, היא תחזיר שגיאת #VALUE!. עם זאת, אם תשלב את פונקציות SUM ו-OFFSET כפי שמוצג בתמונה למטה, היא תחזיר ישירות את סכום הערכים בטווח C5:D6.

1. בחר תא ריק, העתק את הנוסחה הבאה אליו והקש על Enter כדי לקבל את התוצאה.
=SUM(OFFSET(B2,3,1,2,2)))

דוגמה 3: סיכום עמודות על פי קריטריונים מסוימים

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

1. בחר תא ריק, העתק את הנוסחה הבאה אליו והקש על Enter כדי לקבל את התוצאה.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

הערות:

1. בנוסחה לעיל, MATCH(G4,B3:B8) מחפשת אחר מנגו ומחזירה את מיקומו בטווח B3:B8. בואו נראה, מנגו ממוקם 5 שורות מתחת לתא B2, אז הוא מחזיר את המספר 5;
2. מכיוון שהתוצאה של MATCH היא 5, והפסיק כאן מייצג 0, כעת הפונקציה OFFSET מוצגת כ- OFFSET(C2:E2,5,0), מה שאומר שההיסט מתחיל מטווח C2:E2, זז 5 שורות למטה ו-0 עמודות ימינה כדי לקבל את ההתייחסות לטווח C7:E7;
3. לאחר מכן הפונקציה SUM מוצגת כ- SUM(C7:E7), ולבסוף מחזירה את סכום הערכים בטווח C7:E7.

עוד דוגמאות

כיצד לסכם כל n שורות למטה ב-Excel?

כיצד ליצור טווח שמות דינאמי ב-Excel?

כיצד להחזיר ערך תא כל חמש או כל n שורות ב-Excel?