פורמולה

השתמש ב- SUM וב- OFFSET כדי למצוא סיכומים עבור טווחים דינמיים של נתונים

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Cols - (נדרש) את מספר העמודות משמאל או משמאל בארגומנט Reference המשמש לחישוב הקיזוז. ערך זה יכול להיות חיובי, שלילי או מוגדר לאפס

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

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

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

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

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

זה נעשה על ידי הוספת שורה חדשה 6, אשר מעביר את הנוסחה עד שורה 7.

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

כניסה ל - SUM OFFSET

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

סה"כ $ 5679.15 מופיע בתא B7.

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

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

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

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

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

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

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

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

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