פורמולה

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

יצירת טווח דינמי עם פונקציות SUM ו OFFSET

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

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

חריג אחד מתרחש כאשר הנתונים מוכנסים לתא שבו הפונקציה נמצאת כעת.

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

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

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

תחביר וארגומנטים

עיין בתמונה למעלה כדי לעקוב אחר ערכת לימוד זו.

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

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

תחביר הנוסחה:

= SUM (טווח התחלה: OFFSET (הפניה, שורות, Cols))

• טווח התחלה: נקודת המוצא עבור טווח התאים שיווצרו על ידי פונקציית SUM נדרשת. בתמונה לדוגמה, זהו תא B2.
• התייחסות: הפניה התאית הנדרשת המשמשת לחישוב נקודת הקצה של הטווח ממוקמת בשורה ארוכה של שורות ועמודות. בתמונה לדוגמה, הארגומנט Reference הוא הפניה לתא עבור הנוסחה עצמה, מכיוון שתמיד ברצונך שהטווח יסתיים בתא אחד מעל הנוסחה.
• שורות: מספר השורות מעל או מתחת לארגומנט Reference המשמש בחישוב הקיזוז נדרש. ערך זה יכול להיות חיובי, שלילי או מוגדר לאפס. אם המיקום של הקיזוז מעל התייחסות , הערך הוא שלילי. אם הוא נמצא למטה, הארגומנט שורות הוא חיובי. אם הקיזוז ממוקם באותה שורה, הארגומנט הוא אפס. בדוגמה זו, הקיזוז מתחיל שורה אחת מעל הארגומנט Reference, ולכן הערך עבור ארגומנט זה הוא שלילי אחד (-1).
• קול: מספר העמודות משמאל או מימין לארגומנט Reference המשמש בחישוב הקיזוז נדרש. ערך זה יכול להיות חיובי, שלילי או מוגדר לאפס. אם המיקום של ההיסט הוא משמאל לארגומנט Reference, ערך זה הוא שלילי. אם מימין, הטיעון של קולס הוא חיובי. בדוגמה זו, הנתונים המסתכמים נמצאים באותה עמודה כמו הנוסחה, כך שהערך עבור ארגומנט זה הוא אפס.

שימוש בנוסחת SUM OFFSET ל סך נתוני המכירות

דוגמה זו משתמשת בנוסחת SUM OFFSET כדי להחזיר את הסכום הכולל עבור נתוני המכירות היומיים הרשומים בעמודה B של גליון העבודה.

בתחילה, הנוסחה הוכנסה לתא B6 והסתכמה בנתוני המכירות במשך ארבעה ימים.

השלב הבא הוא להזיז את נוסחת SUM OFFSET לאורך שורה כדי לפנות מקום למכירות של היום החמישי. זה נעשה על ידי הוספת שורה חדשה 6, אשר מעביר את הנוסחה עד שורה 7.

כתוצאה מהמהלך, Excel מעדכן אוטומטית את ארגומנט Reference לתאB7 ומוסיף תאB6 אל טווח מסומל על ידי הנוסחה.

כניסה ל - SUM OFFSET

1. לחץ על תא B6, שהוא המיקום שבו התוצאות של הנוסחה יוצגו בתחילה.
2. הקלק על הנוסחאות הכרטיסייה של תפריט הסרט.
3. בחרמתמטיקה & Trigמהרצועה כדי לפתוח את הרשימה הנפתחת של הפונקציות.
4. לחץ עלSUM ברשימה כדי להציג את תיבת הדו-שיח של הפונקציה.
5. בתיבת הדו-שיח, לחץ עלמספר 1קו.
6. לחץ על התא B2 כדי להזין את הפניה תא לתוך תיבת הדו שיח. מיקום זה הוא נקודת הקצה הסטטית של הנוסחה.
7. בתיבת הדו-שיח, לחץ עלמספר 2קו.
8. הזן את הפונקציה OFFSET הבאה: OFFSET (B6, -1,0) כדי ליצור את נקודת הקצה הדינמית עבור הנוסחה.
9. לחץ על בסדר כדי להשלים את הפונקציה ולסגור את תיבת הדו שיח.

סך הכל מופיע בתא B7.

הוספת נתוני המכירות של יום המסחר הבא

כדי להוסיף את נתוני המכירות של היום הבא:

1. לחץ לחיצה ימנית על כותרת השורה עבור שורה 6 כדי לפתוח את תפריט ההקשר.
2. בתפריט, לחץ עלהוסף כדי להוסיף שורה חדשה לגליון העבודה.
3. כתוצאה מכך, נוסחת ה- SUM OFFSET נעה למטה לתא B7 ושורה 6 ריקה כעת.
4. לחץ על התא A6.
5. הזן את המספר5 כדי לציין כי סך המכירות עבור היום החמישי הוא נכנס.
6. לחץ על תא B6.
7. הקלד את המספר$1458.25 ולחץ עללהיכנס מפתח במקלדת.

Cell B7 עדכונים על סך הכל$7137.40.

כאשר אתה לוחץ על התא B7, הנוסחה המעודכנת= SUM (B2: OFFSET (B7, -1,0)) מופיע בסרגל הנוסחה.

לפונקציה OFFSET יש שני ארגומנטים אופציונליים: גובה ורוחב , אשר לא היו בשימוש בדוגמה זו.

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

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