איך מחשבים סך מצטבר ב-Excel
סך מצטבר, או סכום מצטבר, מציג את הסכום עד כה בכל שורה. הדרך האמינה ביותר לחשב אותו ב-Excel היא טווח שמתרחב: =SUM($B$2:B2) בשורה הראשונה, ואז גוררים למטה. סימני הדולר נועלים את תחילת הטווח, והסוף זז שורה אחת בכל פעם.
המדריך מציג את טווח ההתרחבות, את הנוסחה הקצרה "הסך הקודם ועוד הערך הזה", וסך מצטבר לכל קטגוריה. הוא נשען על איך פועלות הפניות לתאים, ואפשר לתרגל אותן בשיעורים שבסוף.
1. טווח SUM שמתרחב
בתא C2 מקלידים =SUM($B$2:B2) וגוררים למטה. בתא C3 הנוסחה הופכת ל-=SUM($B$2:B3), בתא C4 ל-=SUM($B$2:B4), וכן הלאה. התא הראשון הוא מוחלט, כלומר נעול, והשני הוא יחסי, כלומר זז עם השורה.
דוגמה: סך מצטבר בשורה השלישית
| A | B | C | |
|---|---|---|---|
| 1 | יום | מכירות | סך מצטבר |
| 2 | 1 | 10 | 10 |
| 3 | 2 | 20 | 30 |
| 4 | 3 | 5 | 35 |
| 5 | 4 | 15 | |
| 6 | 5 | 30 |
הטווח מתרחב ל-B2:B4, ו-10 + 20 + 5 = 35.
2. הסך הקודם ועוד הערך הזה
דרך קצרה יותר היא להוסיף את הערך של השורה לסך שמעליה: =C3+B4. בשורה הראשונה אין סך מעליה, ולכן כותבים שם =B2. זה קל לקריאה, אבל אם מוחקים שורה באמצע, השרשרת עלולה להישבר, ולכן טווח שמתרחב בטוח יותר בגיליון שמשתנה.
דוגמה: הוספה לסך הקודם
| A | B | C | |
|---|---|---|---|
| 1 | יום | מכירות | סך מצטבר |
| 2 | 1 | 10 | 10 |
| 3 | 2 | 20 | 30 |
| 4 | 3 | 5 | 35 |
| 5 | 4 | 15 | |
| 6 | 5 | 30 |
הסך עד כה ב-C3 הוא 30, ומוסיפים את ה-5 של היום ומקבלים 35.
3. סך מצטבר לכל קטגוריה
כשהרשימה מערבבת קטגוריות, משתמשים ב-SUMIF עם טווח שמתרחב, כך שכל שורה מוסיפה רק את הערכים של הקטגוריה שלה שנמצאו עד כה. נועלים את החלק העליון של שני הטווחים, ונותנים לחלק התחתון לזוז.
דוגמה: סך מצטבר לצפון
| A | B | C | |
|---|---|---|---|
| 1 | אזור | מכירות | סך מצטבר |
| 2 | צפון | 10 | |
| 3 | דרום | 20 | |
| 4 | צפון | 5 | |
| 5 | צפון | 15 | 30 |
| 6 | דרום | 30 |
עד שורה 5, צפון מופיע בשורות 2, 4 ו-5 עם 10, 5 ו-15, שביחד הם 30.
4. שהנוסחה תגדל עם הנתונים
אם הופכים את הנתונים לטבלה של Excel עם Ctrl+T, נוסחה שנכתבת בשורה הראשונה של עמודה חדשה נגררת למטה אוטומטית, ושורות חדשות מקבלות את אותה נוסחה. ב-Microsoft 365 אפשר גם לחשב את העמודה כולה בנוסחת מערך דינמית אחת, כמו שמוסבר בשיעור המערכים הדינמיים.
טעויות נפוצות
שכחת את סימני הדולר. =SUM(B2:B2) שנגרר למטה הופך ל-=SUM(B3:B3), וזה לא סך מצטבר.
תיקון: נועלים את תחילת הטווח: =SUM($B$2:B2).
כתיבת השורה הראשונה כמו השאר. =C1+B2 מוסיף את טקסט הכותרת שב-C1 ומחזיר #VALUE!.
תיקון: בשורה הראשונה משתמשים ב-=B2 או ב-SUM המתרחב, שמטפל בזה נכון.
מיון אחרי החישוב. מיון שורות שמכילות נוסחה של סך קודם יכול לשנות לאן כל נוסחה מצביעה.
תיקון: ממיינים קודם ורק אחר כך מזינים את הנוסחה, או משתמשים ב-SUM המתרחב, ששורד מיון טוב יותר.
שאלות נפוצות
איך יוצרים סך מצטבר שמתאפס כל חודש?
משתמשים בגרסת SUMIF עם החודש כקטגוריה, או מוסיפים תנאי שני עם SUMIFS.
איך מציגים סך מצטבר בטבלת Pivot?
לוחצים לחיצה ימנית על ערך, בוחרים הצג ערכים כ, ואז סך מצטבר ב, ובוחרים את השדה שאורכו הסכום יתקדם.
אפשר להציג את זה כתרשים?
כן. תרשים קו של עמודת הסך המצטבר מראה איך נתון נבנה לאורך הזמן. מומלץ לקרוא את המדריך על יצירת גרף.
פונקציות קשורות
- SUMSUM מחברת מספרים, תאים וטווחים שלמים בנוסחה אחת.
- SUMIFSUMIF מחברת את הערכים שעונים על תנאי אחד, כמו כל המכירות של אזור אחד או כל הסכומים מעל 100.
- SUMIFSSUMIFS מחברת ערכים שעונים על כמה תנאים בבת אחת, למשל הזמנות ששולמו מאזור אחד.
- SEQUENCESEQUENCE יוצרת רשימה או רשת של מספרים עוקבים שדולפת לתאים הסמוכים.
עוד על סכומים מצטברים
המדריך נותן את הטווח המתרחב ואת נוסחת הסכום הקודם. החלק הזה מוסיף מה לעשות כשהשורות לא מסודרות, איך להציג רק את הסכום המצטבר האחרון, ואיך לבנות סכום מצטבר בנוסחה אחת.
כשהשורות לא מסודרות לפי תאריך
סכום מצטבר מוסיף את השורות שמעליו, ולכן הוא מועיל רק כשהשורות בסדר הנכון. אם התאריכים מבולבלים, מיינו את הטבלה לפי תאריך קודם. נוסחה שמבוססת על תאריך לא תלויה בסדר: סכמו את כל המכירות עד התאריך של השורה הזו כולל.
=SUMIFS($B$2:$B$100,$A$2:$A$100,"<="&A2)הצגת הסכום האחרון בלבד
דוח צריך לרוב רק את המספר הסופי. זה זהה ל-=SUM(B2:B100) הרגיל, ולכן עמודת סכום מצטבר צריכה להסכים איתו בשורה האחרונה שלה. השתמשו בזה כבדיקה: אם הסכום המצטבר האחרון שונה מסכום העמודה, נוסחה באמצע נדרסה, או שורה הוכנסה מחוץ לטווח.
נוסחה אחת לכל העמודה
ב-Microsoft 365 אפשר לכתוב טווח מתרחב פעם אחת לכל העמודה, בעזרת נוסחת מערך דינמי שמשתמשת ב-SEQUENCE כדי לבנות את מספרי השורות, ובחיפוש שלוקח את n הערכים הראשונים. זה קשה יותר לקריאה, ולכן השתמשו בו רק כשצריך עמודה בלי רווחים או נוסחאות שבורות. לרוב עדיף הסכום המתרחב הפשוט בטבלה, שמתמלא לבד כלפי מטה.
כדי לשרטט את התוצאה, בחרו את עמודת התאריך ואת הסכום המצטבר, והוסיפו תרשים קו, כמו ב[מדריך על יצירת תרשים](/guides/make-a-graph-in-excel). לחלקים הנעולים והנעים של הטווח, ראו [איך פועלות הפניות לתאים](/guides/cell-references).