פונקציית קיזוז Excel

הפונקציה Excel OFFSET מחזירה הפניה לטווח שנבנה עם חמש כניסות: (1) נקודת התחלה, (2) קיזוז שורה, (3) קיזוז עמודה, (4) גובה בשורות, (5) רוחב ב- עמודות. OFFSET שימושי בנוסחאות הדורשות טווח דינמי.

• התייחסות - נקודת המוצא, המסופקת כהפניה לתא או כטווח.
• שורות - מספר השורות לקזז מתחת להפניה ההתחלתית.
• קולס - מספר העמודות שיש לקזז מימין להפניה ההתחלתית.
• גוֹבַה - [אופציונלי] הגובה בשורות של ההפניה המוחזרת.
• רוֹחַב - [אופציונלי] הרוחב בעמודות של ההפניה המוחזרת.

הפונקציה Excel OFFSET מחזירה טווח דינמי שנבנה עם חמש כניסות: (1) נקודת התחלה, (2) קיזוז שורה, (3) קיזוז עמודה, (4) גובה בשורות, (5) רוחב בעמודות.

נקודת המוצא (ה התייחסות טיעון) יכול להיות תא אחד או טווח תאים. ה שורות ו קולס ארגומנטים הם מספר התאים ל'קיזוז 'מנקודת ההתחלה. ה גוֹבַה ו רוֹחַב ארגומנטים הם אופציונליים וקובעים את גודל הטווח שנוצר. מתי גוֹבַה ו רוֹחַב מושמטים, הם ברירת המחדל לגובה ולרוחב של התייחסות .

דוגמא לערך נוכחי נטו באקסל

לדוגמה, להפניה C5 המתחילה ב- A1, התייחסות הוא A1, שורות הוא 4 ו קולס הוא 2:

 
= OFFSET (A1,4,2) // returns reference to C5

להתייחסות ל- C1: C5 מ- A1, התייחסות הוא A1, שורות הוא 0, קולס הוא 2, גוֹבַה הוא 5, ו רוֹחַב הוא 1:

 
= OFFSET (A1,0,2,5,1) // returns reference to C1:C5

הערה: ניתן להשמיט את הרוחב מכיוון שהוא יהיה ברירת מחדל ל -1.

מקובל לראות OFFSET עטוף בפונקציה אחרת המצפה לטווח. לדוגמה, ל- SUM C1: C5, החל מ- A1:

 
= SUM ( OFFSET (A1,0,2,5,1)) // SUM C1:C5

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

הערה: מציין תיעוד אקסל גוֹבַה ו רוֹחַב לא יכול להיות שלילי, אך נראה כי ערכים שליליים עבדו בסדר מתחילת שנות התשעים . הפונקציה OFFSET ב- Google Sheets לא תאפשר ערך שלילי לארגומנטים של גובה או רוחב.

כיצד להגן על תא באקסל

דוגמאות

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

דוגמה מס '1

במסך למטה, אנו משתמשים ב- OFFSET כדי להחזיר את הערך השלישי (מרץ) בעמודה השנייה (מערב). הנוסחה ב- H4 היא:

 
= OFFSET (B3,3,2) // returns D6

דוגמה מס '2

במסך למטה, אנו משתמשים ב- OFFSET כדי להחזיר את הערך האחרון (יוני) בעמודה השלישית (צפון). הנוסחה ב- H4 היא:

 
= OFFSET (B3,6,3) // returns E9

דוגמה מס '3

להלן, אנו משתמשים ב- OFFSET כדי להחזיר את כל הערכים בעמודה השלישית (צפון). הנוסחה ב- H4 היא:

 
= OFFSET (B3,1,3,6) // returns E4:E9

דוגמה מס '4

להלן, אנו משתמשים ב- OFFSET כדי להחזיר את כל הערכים לחודש מאי (שורה חמישית). הנוסחה ב- H4 היא:

 
= OFFSET (B3,5,1,1,4) // returns C8:F8

דוגמה מס '5

להלן, אנו משתמשים ב- OFFSET כדי להחזיר את הערך של אפריל, מאי ויוני לאזור המערב. הנוסחה ב- H4 היא:

 
= OFFSET (B3,4,2,3,1) // returns D7:D9

דוגמה מס '6

להלן, אנו משתמשים ב- OFFSET להחזרת ערך אפריל, מאי ויוני עבור מערב וצפון. הנוסחה ב- H4 היא:

כיצד למצוא מספר ימים בין שני תאריכים

 
= OFFSET (B3,4,2,3,2) // returns D7:E9

הערות

• OFFSET רק מחזיר הפניה, לא מועברים תאים.
• שניהם שורות ו קולס ניתן לספק כמספרים שליליים כדי להפוך את כיוון הקיזוז הרגיל שלהם - שלילי קולס קיזוז שמאלה, ושלילי שורות קיזוז למעלה.
• קיזוז הוא ' פונקציה נדיפה ' - הוא יחשב מחדש עם כל שינוי גליון עבודה. פונקציות נדיפות יכולות לגרום לחוברות עבודה גדולות ומורכבות יותר לפעול לאט.
• OFFSET יציג את ה- #REF! ערך השגיאה אם ​​הקיזוז נמצא מחוץ לקצה גליון העבודה.
• כאשר גובה או רוחב מושמטים, הגובה והרוחב של התייחסות משמש.
• ניתן להשתמש ב- OFFSET עם כל פונקציה אחרת המצפה לקבל הפניה.
• כתוב בתיעוד אקסל גוֹבַה ו רוֹחַב לא יכול להיות שלילי, אבל ערכים שליליים אכן עובדים.