אם גליון העבודה של Excel שלך כולל חישובים המבוססים על טווח תאים משתנה, השתמש בפונקציות SUM ו-OFFSET יחד בנוסחת SUM OFFSET כדי לפשט את המשימה של שמירת החישובים מעודכנים.
ההוראות במאמר זה חלות על Excel עבור Microsoft 365, Excel 2019, Excel 2016, Excel 2013 ו-Excel 2010.
צור טווח דינמי עם הפונקציות SUM ו-OFFSET
אם אתה משתמש בחישובים לתקופת זמן המשתנה ללא הרף - כמו קביעת מכירות לחודש - השתמש בפונקציית OFFSET באקסל כדי להגדיר טווח דינמי שמשתנה עם הוספת נתוני המכירות של כל יום.
כשלעצמה, פונקציית SUM יכולה בדרך כלל להכיל את ההכנסה של תאים חדשים של נתונים לטווח המסוכם. חריג אחד מתרחש כאשר הנתונים מוכנסים לתא שבו הפונקציה נמצאת כעת.
בדוגמה למטה, נתוני המכירות החדשים עבור כל יום מתווספים בתחתית הרשימה, מה שמאלץ את הסכום להזיז ללא הרף תא אחד למטה בכל פעם עם הוספת הנתונים החדשים.
כדי לעקוב אחר הדרכה זו, פתח גיליון עבודה ריק של Excel והזן את הנתונים לדוגמה. גליון העבודה שלך לא צריך להיות מעוצב כמו בדוגמה, אבל הקפד להזין את הנתונים באותם תאים.
אם רק הפונקציה SUM משמשת לסיכום הנתונים, יהיה צורך לשנות את טווח התאים המשמש כארגומנט הפונקציה בכל פעם שנוספו נתונים חדשים.
על ידי שימוש בפונקציות SUM ו-OFFSET ביחד, הטווח שמצטבר הופך לדינמי ומשתנה כדי להכיל תאים חדשים של נתונים. הוספה של תאים חדשים של נתונים לא גורמת לבעיות מכיוון שהטווח ממשיך להתאים ככל שמתווסף כל תא חדש.
תחביר וארגומנטים
בנוסחה זו, הפונקציה SUM משמשת לסיכום טווח הנתונים שסופקו כארגומנט. נקודת ההתחלה עבור טווח זה היא סטטית ומזוהה כהפניה לתא למספר הראשון שיש לסכם על ידי הנוסחה.
פונקציית OFFSET מקוננת בתוך הפונקציה SUM ויוצרת נקודת קצה דינמית לטווח הנתונים המסוכמים על ידי הנוסחה. זה מושג על ידי הגדרת נקודת הקצה של הטווח לתא אחד מעל המיקום של הנוסחה.
תחביר הנוסחה הוא:
=SUM(Range Start:OFFSET(Reference, Rows, Cols))
הטיעונים הם:
- Range Start: נקודת ההתחלה של טווח התאים שיצטברו על ידי הפונקציה SUM. בדוגמה זו, נקודת ההתחלה היא תא B2.
- Reference: הפניה לתא הנדרשת המשמשת לחישוב נקודת הסיום של הטווח. בדוגמה, הארגומנט Reference הוא הפניה לתא עבור הנוסחה מכיוון שהטווח מסתיים תא אחד מעל הנוסחה.
- Rows: נדרש מספר השורות מעל או מתחת לארגומנט Reference המשמש לחישוב ההיסט. ערך זה יכול להיות חיובי, שלילי או מוגדר לאפס. אם מיקום ההיסט הוא מעל הארגומנט Reference, הערך שלילי. אם ההיסט הוא מתחת, הארגומנט Rows חיובי. אם ההיסט ממוקם באותה שורה, הארגומנט הוא אפס. בדוגמה זו, ההיסט מתחיל בשורה אחת מעל הארגומנט Reference, כך שהערך עבור הארגומנט הוא אחד שלילי (-1).
- Cols: מספר העמודות משמאל או ימין לארגומנט Reference המשמש לחישוב ההיסט. ערך זה יכול להיות חיובי, שלילי או מוגדר לאפס. אם מיקום ההיסט נמצא משמאל לארגומנט Reference, ערך זה שלילי. אם ההיסט הוא ימינה, הטיעון של Cols חיובי. בדוגמה זו, הנתונים המסוכמים נמצאים באותה עמודה כמו הנוסחה, כך שהערך עבור ארגומנט זה הוא אפס.
השתמש בנוסחת SUM OFFSET לסיכום נתוני מכירות
דוגמה זו משתמשת בנוסחת SUM OFFSET כדי להחזיר את הסכום הכולל של נתוני המכירות היומיים המפורטים בעמודה B של גליון העבודה. בתחילה, הנוסחה הוזנה לתא B6 וסכמה את נתוני המכירות במשך ארבעה ימים.
השלב הבא הוא להעביר את נוסחת ה-SUM OFFSET למטה שורה אחת כדי לפנות מקום לסך המכירות של היום החמישי. זה מושג על ידי הוספת שורה 6 חדשה, שמזיזה את הנוסחה לשורה 7.
כתוצאה מהמעבר, Excel מעדכן אוטומטית את הארגומנט Reference לתא B7 ומוסיף את תא B6 לטווח המסוכם בנוסחה.
- בחר תא B6, שהוא המיקום שבו תוצאות הנוסחה יוצגו בתחילה.
-
בחר את הכרטיסייה נוסחאות של הסרט.
Image -
בחר Math & Trig.
Image -
בחר SUM.
Image - בתיבת הדו-שיח Function Arguments, מקם את הסמן בתיבת הטקסט Number1.
-
בגליון העבודה, בחר בתא B2 כדי להזין הפניה לתא זה בתיבת הדו-שיח. מיקום זה הוא נקודת הקצה הסטטית של הנוסחה.
Image - בתיבת הדו-שיח Function Arguments, מקם את הסמן בתיבת הטקסט Number2.
-
הזן OFFSET(B6, -1, 0). פונקציית OFFSET זו יוצרת את נקודת הקצה הדינמית עבור הנוסחה.
Image -
בחר OK כדי להשלים את הפונקציה ולסגור את תיבת הדו-שיח. הסכום הכולל מופיע בתא B6.
Image
הוסף את נתוני המכירות של היום הבא
כדי להוסיף את נתוני המכירות של היום הבא:
- לחץ לחיצה ימנית על כותרת השורה עבור שורה 6.
-
בחר Insert כדי להוסיף שורה חדשה לגליון העבודה. הנוסחה SUM OFFSET עוברת שורה אחת למטה לתא B7 ושורה 6 ריקה כעת.
Image - בחר תא A6 והזן את המספר 5 כדי לציין שמזינים את סך המכירות ליום החמישי.
-
בחר תא B6, הזן $1458.25, ולאחר מכן הקש Enter.
Image - Cell B7 עדכונים לסכום החדש של $7137.40.
כאשר אתה בוחר בתא B7, הנוסחה המעודכנת מופיעה בשורת הנוסחאות.
=SUM(B2:OFFSET(B7, -1, 0))
לפונקציה OFFSET יש שני ארגומנטים אופציונליים: Height ו- Width, שלא נעשה בהם שימוש בדוגמה זו. ארגומנטים אלה אומרים לפונקציית OFFSET את צורת הפלט במונחים של מספר השורות והעמודות.
על ידי השמטת הארגומנטים הללו, הפונקציה משתמשת במקום זאת בגובה וברוחב של ארגומנט Reference, שבדוגמה זו הוא בשורה אחת בגובה ובעמודה אחת ברוחב.
