5 דרכים קלות לחישוב סך הריצה ב- Excel (סכום מצטבר)

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

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

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

ב- Microsoft Excel קיימות מספר דרכים שונות לחישוב סכומי ריצה.

השיטה שתבחר תלויה גם באופן שבו הנתונים שלך בנויים.

לדוגמה, אם יש לך נתונים פשוטים בטבלאות, תוכל להשתמש בנוסחת SUM פשוטה, אך אם יש לך טבלת Excel, עדיף להשתמש בהפניות מובנות. תוכל גם להשתמש ב- Power Query לשם כך.

במדריך זה, אני הולך לכסות את כל השיטות השונות האלה לחשב סכומי ריצה ב- Excel.

אז בואו נתחיל!

חישוב ריצה כוללת עם נתונים טבליים

אם יש לך נתונים טבלאיים (כלומר, טבלה ב- Excel שאינה מומרת לטבלת Excel), תוכל להשתמש בכמה נוסחאות פשוטות לחישוב הסיכומים הרצים.

שימוש במפעיל התוספות

נניח שיש לך נתוני מכירות לפי תאריך ואתה רוצה לחשב את סך הריצה בעמודה C.

להלן השלבים לביצוע פעולה זו.

שלב 1 - בתא C2, שהוא התא הראשון שבו אתה רוצה את סך הריצה, הזן

= B2

זה פשוט יקבל את אותם ערכי המכירה בתא B2.

שלב 2 - בתא C3, הזן את הנוסחה שלהלן:

= C2+B3

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

זה ייתן לך את התוצאה כפי שמוצג להלן.

זו שיטה ממש פשוטה ועובדת היטב ברוב המקרים.

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

יש רק חיסרון אחד - במקרה שתמחק את כל השורות הקיימות במערך הנתונים הזה, כל התאים למטה שיחזירו שגיאת התייחסות (#REF!)

אם זו אפשרות עם מערך הנתונים שלך, השתמש בשיטה הבאה שמשתמשת בנוסחת SUM

שימוש ב- SUM עם הפניה לתא נעול חלקית

נניח שיש לך נתוני מכירות לפי תאריך ואתה רוצה לחשב את סך הריצה בעמודה C.

להלן נוסחת SUM שתיתן לך את סך הריצה.

= SUM ($ B $ 2: B2)

תן לי להסביר כיצד נוסחה זו עובדת.

בנוסחת SUM לעיל, השתמשתי בהפניה כדי להוסיף $ B $ 2: B2

• $ B $ 2 - זוהי הפניה מוחלטת, כלומר כשאני מעתיק את אותה נוסחה בתאים למטה, הפניה זו לא תשתנה. אז בעת העתקת הנוסחה בתא למטה, הנוסחה תשתנה ל- SUM ($ B $ 2: B3)
• B2 - זהו החלק השני של ההפניה שהוא הפניה יחסית, מה שאומר שזה יתאים כאשר אני מעתיק את הנוסחה כלפי מטה או ימינה. אז בעת העתקת הנוסחה בתא למטה, ערך זה יהפוך ל- B3

קרא גם: הפניות לתאים מוחלטים, יחסיים ומעורבים באקסל

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

חישוב סך הריצה בטבלת Excel

כאשר עובדים עם נתונים טבלאריים ב- Excel מומלץ להמיר אותם לטבלת Excel. זה מקל מאוד על ניהול הנתונים ומאפשר גם שימוש קל בכלים כגון Power Query ו- Power Pivot.

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

למרות שאתה עדיין יכול להשתמש בנוסחה שלעיל שהראתי לך בטבלת Excel, הרשה לי להראות לך כמה שיטות טובות יותר לעשות זאת.

נניח שיש לך טבלת Excel כפי שמוצג להלן וברצונך לחשב את סך הריצה בעמודה C.

להלן הנוסחה שתעשה זאת:

= SUM (נתוני מכירות [[#כותרות], [מכירה]]: [@מבצע])

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

לדוגמה, SalesData [[#Headers], [Sale]] מתייחס לכותרת המכירה בטבלת SalesData (SalesData הוא שם הטבלה של Excel שנתתי כשיצרתי את הטבלה)

ו- [@Sale] מתייחס לערך בתא באותה שורה בעמודה Sale.

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

להלן השלבים לביצוע פעולה זו:

1. בתא C2, הזן = SUM (
2. בחר תא B1, שהוא הכותרת של העמודה בעלת ערך המכירה. אתה יכול להשתמש בעכבר או באמצעות מקשי החצים. תבחין כי Excel מזין באופן אוטומטי את ההפניה המובנית של התא ההוא
3. הוסף a: (סמל נקודתיים)
4. בחר תא B2. Excel שוב יכניס אוטומטית את ההפניה המובנית לתא
5. סגור את הסוגר והקש enter

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

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

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

חישוב ריצה כוללת באמצעות שאילתת חשמל

Power Query הוא כלי מדהים בכל הנוגע לחיבור עם מסדי נתונים, חילוץ הנתונים ממספר מקורות והפיכתם לפני הכנסתם לאקסל.

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

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

נניח שיש לך טבלת Excel כפי שמוצג להלן וברצונך להוסיף את הסיכומים הרצים לנתונים אלה:

להלן השלבים לביצוע פעולה זו:

1. בחר תא כלשהו בטבלת Excel
2. לחץ על נתונים
3. בכרטיסיה קבל ושינוי, לחץ על הסמל מתוך טבלה/טווח. פעולה זו תפתח את הטבלה בעורך Power Query
4. [אופציונלי] אם עמודת התאריך שלך לא ממוינת עדיין, לחץ על סמל המסנן בעמודה תאריך ולאחר מכן לחץ על מיין עולה
5. לחץ על הכרטיסייה הוסף עמודה בעורך Power Query
6. בקבוצה כללית, לחץ על התפריט הנפתח 'עמוד אינדקס' (אל תלחץ על סמל עמודת האינדקס, אלא על החץ הקטן והטה השחור לידו כדי להציג אפשרויות נוספות)
7. לחץ על האפשרות 'מ -1'. פעולה זו תוסיף עמודת אינדקס חדשה שתתחיל מאחת ותזין מספרים בהגדלה של 1 בעמודה כולה
8. לחץ על סמל 'עמודה מותאמת אישית' (הנמצא גם בכרטיסייה הוסף עמודה)
9. בתיבת הדו -שיח של עמודה מותאמת אישית שנפתחת, הזן שם לעמודה החדשה. בדוגמה זו אשתמש בשם 'ריצה כוללת'
10. בשדה נוסחת העמודה המותאמת אישית, הזן את הנוסחה שלהלן: List.Sum (List.Range (מספר "אינדקס נוסף" [מכירה], 0, [אינדקס]))
11. ודא שיש תיבת סימון בתחתית תיבת הדו -שיח שאומרת - 'לא זוהו שגיאות תחביר'
12. לחץ על אישור. זה יוסיף עמוד כולל כולל חדש
13. הסר את עמודת האינדקס
14. לחץ על הכרטיסייה קובץ ולאחר מכן לחץ על 'סגור וטען'

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

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

אם כבר יש לך מערך נתונים וכל מה שאתה צריך לעשות הוא להוסיף סכומים פועלים, עדיף לא להשתמש ב- Power Query.

השימוש ב- Power Query הגיוני היכן עליך לחלץ נתונים ממסד נתונים או לשלב נתונים מחוברות עבודה רבות ובתוך כך גם להוסיף לו סכומי ריצה.

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

איך זה עובד?

עכשיו הרשה לי להסביר במהירות מה קורה בשיטה זו.

הדבר הראשון שאנחנו עושים בעורך Power Query הוא הכנסת עמודת אינדקס המתחילה מאחת ומתגברת באחת כשהיא יורדת בתאים.

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

לאחר מכן אנו מכניסים עמודה מותאמת אישית ומשתמשים בנוסחה שלהלן

List.Sum (List.Range (#"אינדקס נוסף" [מכירה], 0, [אינדקס]))

זוהי נוסחת List.Sum שתתן לך את סכום הטווח שצוין בתוכה.

וטווח זה מצוין באמצעות הפונקציה List.Range.

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

אז, לתא הראשון. List.Sum היה נותן לך רק את סכום ערך המכירה הראשון, ולתא השני, הוא היה נותן לך את הסכום עבור שני ערכי המכירה הראשונים וכן הלאה.

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

חישוב סך ריצות בהתבסס על קריטריונים

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

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

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

ניתן לעשות זאת באמצעות נוסחת SUMIF המחשבת את סך הריצה תוך הקפדה על התנאי שצוין.

להלן הנוסחה שתעשה זאת עבור עמודות המדפסת:

= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)

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

= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)

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

הנוסחה לוקחת שלושה טיעונים:

1. טווח: זהו טווח הקריטריונים שייבדק מול הקריטריונים שצוינו
2. קריטריונים: זה הקריטריונים שיבדקו רק אם יתקיים קריטריון זה אז יתווספו הערכים בארגומנט השלישי, שהוא טווח הסכום.
3. [sum_range]: זהו טווח הסכום שממנו יתווספו הערכים אם יתקיימו הקריטריונים

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

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

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

ריצה כוללת בטבלאות ציר

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

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

להלן השלבים להוספת עמודה נוספת שתציג את סך המכירות לפי תאריך:

1. גרור את שדה המכירה והכנס אותו לאזור ערך.
2. פעולה זו תוסיף עמודה נוספת עם ערכי המכירות
3. לחץ על סכום המכירה 2 באזור ערך
4. לחץ על האפשרות 'הגדרות שדה ערך'
5. בתיבת הדו -שיח הגדרות שדה ערך, שנה את השם המותאם אישית ל'הסכמות פועלות '
6. לחץ על הכרטיסייה 'הצג ערך בשם'
7. בתפריט הנפתח Show as as, בחר באפשרות 'ריצת סך הכל'
8. באפשרויות השדה בסיס, ודא שנבחר תאריך
9. לחץ על אישור

השלבים שלעיל ישנו את עמודת המכירה השנייה לעמודה ריצה כוללת.

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

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

אני מקווה שמצאת הדרכה שימושית.