סה"כ פועל באקסל

שיטה 1. נוסחאות

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

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

החסרונות של גישה זו ברורים:

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

שיטה 2. טבלת ציר

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

אנו ממירים את הטבלה המקורית שלנו לקיצור מקשים "חכם" (דינמי). Ctrl+T או צוות בית - עיצוב כטבלה (בית - עיצוב כטבלה), ואז אנחנו בונים עליו טבלת ציר עם הפקודה הוספה - PivotTable (הוסף - טבלת ציר). שמנו את התאריך באזור השורות בסיכום, ואת מספר הסחורות שנמכרו באזור הערכים:

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

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

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

היתרונות של גישה זו:

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

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

שיטה 3: Power Query

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

לאחר מכן נבצע את השלבים הבאים:

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

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

3. כמו כן, כדי לחשב את הסכום הרצוי, אנו זקוקים להתייחסות לעמודה נמכרים, איפה הנתונים המסוכמים שלנו נמצאים. ב-Power Query עמודות נקראות גם רשימות (רשימה) וכדי לקבל קישור אליה יש ללחוץ לחיצה ימנית על כותרת העמודה ולבחור בפקודה טיפוח לרכב (הצג פרטים). הביטוי שאנו צריכים יופיע בשורת הנוסחאות, המורכב מהשם של השלב הקודם #"אינדקס נוסף", משם אנו לוקחים את הטבלה ואת שם העמודה [מכירות] מהטבלה הזו בסוגריים מרובעים:

העתק את הביטוי הזה ללוח לשימוש נוסף.

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

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

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

נותר למחוק את עמודת האינדקס שאנחנו כבר לא צריכים ולהעלות את התוצאות בחזרה לאקסל עם הפקודה Home – Close & Load.

הבעיה נפתרה.

מהיר ועצבני

באופן עקרוני אפשר היה לעצור את זה, אבל יש זבוב קטן במשחה - הבקשה שיצרנו עובדת במהירות של צב. לדוגמה, במחשב הלא חלש שלי, טבלה של 2000 שורות בלבד מעובדת תוך 17 שניות. מה אם יש יותר נתונים?

כדי להאיץ, ניתן להשתמש ב-buffing באמצעות הפונקציה המיוחדת List.Buffer, אשר טוענת את הרשימה (הרשימה) שניתנת לה כארגומנט ל-RAM, מה שמאיץ מאוד את הגישה אליו בעתיד. במקרה שלנו, הגיוני לאמץ את רשימת #"Added index"[Sold], שאליה יש לגשת ל-Power Query בעת חישוב הסכום הרצוי בכל שורה בטבלה בת 2000 השורות שלנו.

לשם כך, בעורך Power Query בכרטיסייה הראשית, לחץ על כפתור העורך המתקדם (בית - עורך מתקדם) כדי לפתוח את קוד המקור של השאילתה שלנו בשפת M המובנית ב-Power Query:

ואז הוסף שם שורה עם משתנה הרשימה שלי, שערכו מוחזר על ידי פונקציית ה-buffing, ובשלב הבא נחליף את הקריאה לרשימה במשתנה הזה:

לאחר ביצוע השינויים הללו, השאילתה שלנו תהפוך למהירה משמעותית ותתמודד עם טבלה של 2000 שורות תוך 0.3 שניות בלבד!

דבר נוסף, נכון? 🙂

• תרשים פארטו (80/20) וכיצד לבנות אותו באקסל
• חיפוש מילות מפתח בטקסט ואגירת שאילתות ב-Power Query