סך הכל (מוכר גם בשםסכום מצטבר) נמצא בשימוש נפוץ מאוד במקרים רבים.זהו מדד שאומר לך מה סכום הערכים עד כה.
לדוגמה, אם יש לך נתוני מכירות חודשיים, סך הכל יגיד לך כמה מכירות בוצעו מהיום הראשון של החודש ועד לתאריך מסוים.
ישנם מצבים נוספים בהם משתמשים לרוב בסיכומים, כגון חישוב יתרות מזומנים בדפי חשבון בנק/פנקסי חשבונות, חישוב קלוריות בתכניות ארוחות וכו'.
ב-Microsoft Excel, ישנן מספר דרכים שונות לחישוב סכומים רצים.
השיטה שתבחר תלויה גם במבנה הנתונים.
לדוגמה, אם יש לך נתונים טבלאיים פשוטים, אתה יכול להשתמש בנוסחת SUM פשוטה, אבל אם יש לך טבלת אקסל, עדיף להשתמש בהפניות מובנות.אתה יכול גם להשתמש ב- Power Query כדי לעשות זאת.
במדריך זה, אכסה את כולםחשב סה"כ רץ באקסלשיטות שונות.
אז בואו נתחיל!
תוכן
חשב סכומים רצופים באמצעות נתונים טבלאיים
אם יש לך נתונים טבלאיים (כלומר, טבלאות באקסל שאינן מומרות לטבלאות אקסל), תוכל להשתמש בכמה נוסחאות פשוטות כדי לחשב סכומים רצים.
השתמש באופרטור התוספת
נניח שיש לך נתוני מכירות לפי תאריך ואתה רוצה在 בעמודה גלחשבסכום כולל.
להלן השלבים לעשות זאת.
שלב 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
הדבר הנחמד בגישה זו הוא שאם אתה מוחק שורות כלשהן במערך הנתונים, הנוסחה הזו מתאימה ועדיין נותנת לך את הסכום הרצוי הנכון.
חשב סה"כ רץ בגיליון אקסל
בעבודה עם נתונים טבלאיים באקסל, עדיף להמיר אותם לטבלת אקסל.זה מקל על ניהול הנתונים, וזה גם מקל על השימוש בכלים כמו Power Query ו-Power Pivot.
שימוש בטבלת אקסל מספק מספר יתרונות, כמו הפניות מובנות (המקלות מאוד על הפניה לנתונים בטבלה והשימוש בהם בנוסחאות), והתאמה אוטומטית של הפניות תוך כדי הוספה או הסרה של נתונים מהטבלה.
למרות שאתה עדיין יכול להשתמש בנוסחה לעיל שהראיתי לך בגיליון Excel, הרשו לי להראות לכם כמה דרכים טובות יותר לעשות זאת.
נניח שיש לך טבלת אקסל כמו זו למטה, ואתה רוצה לחשב סה"כ רץ בעמודה C.
הנה הנוסחה לעשות זאת:
=SUM(SalesData[[#Headers],[Sale]]:[@Sale])
הנוסחה שלעיל אולי נראית קצת ארוכה, אבל אתה לא צריך לכתוב אותה בעצמך.מה שאתה רואה בנוסחת סיכום נקרא הפניה מובנית, וזו דרך יעילה עבור Excel להתייחס לנקודות נתונים ספציפיות בטבלת Excel.
לדוגמה, SalesData[[#Headers],[Sale]] מתייחס לכותרת Sales בטבלת SalesData (SalesData הוא השם של טבלת ה-Excel שנתתי כשיצרתי את הטבלה)
ואילו [@Sale] מתייחס לערך בתא באותה שורה של העמודה מכירה.
אני רק מסביר את זה כאן להבנתך, אבל גם אם אתה לא יודע כלום על הפניות מובנות, אתה עדיין יכול ליצור את הנוסחה הזו בקלות.
להלן השלבים לעשות זאת:
- בתא C2, הזן =SUM(
- בחר בתא B1, שהוא הכותרת של העמודה עם ערך המכירה.אתה יכול להשתמש בעכבר או להשתמש במקשי החצים.תבחין ש- Excel מזין באופן אוטומטי הפניה מובנית לתא
- הוסף: (נקודתיים)
- בחר תא B2.Excel יוסיף אוטומטית הפניות מובנות לתאים שוב
- סגור את הסוגר והקש Enter
כמו כן, תשים לב שאינך צריך להעתיק את הנוסחה בכל העמודה, גיליון האקסל עושה זאת עבורך באופן אוטומטי.
יתרון נוסף של גישה זו הוא שאם אתה מוסיף רשומה חדשה למערך נתונים זה, גיליון ה-Excel מחשב אוטומטית סך רץ עבור כל הרשומות החדשות.
למרות שכללנו את כותרות העמודות בנוסחה, זכור שהנוסחה מתעלמת מטקסט הכותרת ומתחשבת רק בנתונים בעמודה
חשב סכומים רצים עם Power Query
Power Query הוא כלי מדהים בכל הקשור לחיבור לבסיסי נתונים, חילוץ נתונים ממקורות מרובים והפיכתם לפני הכנסתם לאקסל.
אם אתה כבר משתמש ב-Power Query, זה יעיל יותר להוסיף סכומים רצים בעת שינוי הנתונים ב-Power Query Editor עצמו (במקום לקבל תחילה את הנתונים ב-Excel ולאחר מכן להוסיף סכומים רצים בכל אחת מהשיטות שלמעלה).
אמנם אין פונקציונליות מובנית ב-Power Query להוספת סכומים רצים (הלוואי שהיו), אתה עדיין יכול לעשות זאת עם נוסחה פשוטה.
נניח שיש לך טבלת אקסל שנראית כך, וברצונך להוסיף סכומים רצים לנתונים האלה:
להלן השלבים לעשות זאת:
- בחר תא כלשהו בטבלת Excel
- לחץ על נתונים
- בכרטיסייה קבל והמרה, לחץ על הסמל מתוך טבלה/טווח.פעולה זו תפתח את הטבלה ב- Power Query Editor
- [אופציונלי] אם עמודת התאריך שלך עדיין לא ממוינת, לחץ על סמל המסנן בעמודת התאריך ולאחר מכן לחץ על מיין עולה
- לחץ על הכרטיסייה הוסף עמודה בעורך השאילתות Power
- בקבוצה כללי, לחץ על התפריט הנפתח עמודת אינדקס (אל תלחץ על סמל עמודת האינדקס, אלא על החץ השחור הקטן והלוכסן שלידו כדי לחשוף אפשרויות נוספות)
- לחץ על האפשרות "מ-1".פעולה זו תוסיף עמודת אינדקס חדשה שתתחיל ב-1 ותזין מספרים המוגדלים ב-1 לאורך העמודה
- לחץ על סמל עמודות מותאמות אישית (גם בכרטיסייה הוסף עמודות)
- בתיבת הדו-שיח 'עמודות מותאמות אישית' שנפתחת, הזן שם לעמודה החדשה.בדוגמה זו אשתמש בשם "Running Total"
- בשדה נוסחת העמודה המותאמת אישית, הזן את הנוסחה הבאה:List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
- ודא שיש תיבת סימון בתחתית תיבת הדו-שיח האומרת "לא זוהו שגיאות תחביר"
- לחץ על אישור.פעולה זו תוסיף עמודת סה"כ חדשה
- עמודת ירידה אינדקס
- לחץ על הכרטיסייה קובץ ולאחר מכן לחץ על סגור וטען
השלבים שלעיל יוסיפו גליון עבודה חדש לחוברת העבודה שלך עם טבלה עם סיכומים רצים.
עכשיו, אם אתה חושב שאלו יותר מדי שלבים בהשוואה לגישה הקודמת עם נוסחאות פשוטות, אתה צודק.
אם כבר יש לך מערך נתונים וכל מה שאתה צריך לעשות הוא להוסיף סיכומים רצים, אז עדיף לא להשתמש ב- Power Query.
השימוש ב- Power Query הגיוני במצבים שבהם עליך לחלץ נתונים ממסד נתונים או לשלב נתונים ממספר חוברות עבודה שונות וגם להוסיף להם סיכומים רצים בתהליך.
כמו כן, ברגע שתהפוך את זה לאוטומטי עם Power Query, בפעם הבאה שמערך הנתונים שלך משתנה, אתה לא צריך לעשות זאת שוב, אתה יכול פשוט לרענן את השאילתה והיא תיתן לך תוצאות על סמך מערך הנתונים החדש.
איך זה עובד?
עכשיו הרשו לי להסביר במהירות מה קורה עם הגישה הזו.
הדבר הראשון שאנו עושים ב-Power Query Editor הוא להוסיף עמודת אינדקס שמתחילה ב-1 ומצטברת ככל שהיא יורדת במורד התא.
אנו עושים זאת מכיוון שאנו צריכים להשתמש בעמודה זו בעת חישוב הסכום הרצוי בעמודה אחרת המוכנסת בשלב הבא.
לאחר מכן נוסיף עמודה מותאמת אישית ונשתמש בנוסחה הבאה
List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
זוהי נוסחת 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 אשר ייתן לי סכום בטווח כאשר התנאים שצוינו מתקיימים.
הנוסחה לוקחת שלושה פרמטרים:
- רכס: זהו טווח התנאים שייבדק מול התנאי שצוין
- הקריטריונים: זה הקריטריון שייבדק רק אם קריטריון זה מתקיים, אז הערך בפרמטר השלישי, טווח הסכום יתווסף
- [טווח_סכום]: זהו הטווח של סכום הערכים שיש להוסיף כאשר התנאי מתקיים
בנוסף, ברכסו -טווח_sumפרמטר, נעלתי את החלק השני של ההפניה כך שככל שאנו נעים למטה בתא, הטווח ממשיך להתרחב.זה מאפשר לנו רק לשקול ולהוסיף ערכים עד לטווח זה (ולכן סה"כ רצף).
בנוסחה זו, אני משתמש בעמודות הכותרת (מדפסות וסורקים) כקריטריונים.אתה יכול גם לקודד קשה את התקן אם כותרות העמודות שלך אינן זהות לחלוטין לטקסט הסטנדרטי.
אם אתה צריך לבדוק מספר תנאים, אתה יכול להשתמש בנוסחת SUMIFS.
סיכומים רצים בטבלת ציר
אם ברצונך להוסיף סכום שוטף לתוצאות PivotTable שלך, תוכל לעשות זאת בקלות באמצעות הפונקציונליות המובנית ב- PivotTables.
נניח שיש לך טבלת ציר כמו זו למטה עם תאריכים בעמודה אחת וערכי מכירות באחרת.
להלן השלבים להוספת עמודה נוספת שתציג את המכירות המצטברות לפי תאריך:
- גרור את שדה המכירה ושחרר אותו באזור הערך.
- זה יוסיף עמודה נוספת עם ערך המכירה
- לחץ על האפשרות סך מכירות 2 באזור ערך
- לחץ על האפשרות הגדרות שדה ערך
- בתיבת הדו-שיח הגדרות שדה ערך, שנה את השם המותאם אישית ל-Running Total
- לחץ על הכרטיסייה "הצג ערך כ".
- בתפריט הנפתח 'הצג ערך כמו', בחר באפשרות "הצגה סה"כ".
- באפשרויות השדות הבסיסיים, ודא שנבחר תאריך
- לחץ על אישור
השלבים שלעיל ישנו את עמודת המכירות השנייה לעמודת "סה"כ רץ".
אז אלו הן כמה מהשיטות בהן תוכל להשתמש כדי לחשב סכומים רצים ב- Excel.אם יש לך נתונים טבלאיים, אתה יכול להשתמש בנוסחאות פשוטות, ואם יש לך טבלאות אקסל, אתה יכול להשתמש בנוסחאות המשתמשות בהפניות מובנות.
כיסיתי גם כיצד לחשב סכומים רצים באמצעות Power Query ו- PivotTables.
אני מקווה שמצאת את המדריך הזה שימושי.
