אם אתם מחפשים לשדרג את ניתוח הנתונים והדו"חות שלכם באופן מקצועי ?
אני ממליץ בחום לשקול את השירות שלנו של תותח Excel.
המומחיות שלנו באקסל תורמת להגשמת היעדים ולפתרון של בעיות מורכבות בצורה יעילה ומהירה.
עם מומחה Excel, אתם יכולים להיות בטוחים שתהליך העבודה שלכם יתנהל בצורה מקצועית.
ליצירת קשר לחצו כאן.
איך ליצור דוחות אקסל אינטראקטיבים ללא צורך ב- Pivot.
מה שאני הולך להראות הפעם, הוא שיטה פשוטה יחסית, שבה אני משתמש הרבה. אני בעצם יוצר מעל לטבלה, כמה סלייסרים, וכמה נוסחאות סיכום פשוטות, שלמעשה מייתרות את הצורך ב- Pivot. מה זה נותן ? מה היתרונות של זה ?
היתרון הגדול הוא שאפשר לראות בעצם במסך אחד, ביחד עם נתוני המקור אנחנו יכולים לראות גם את הסלייסרים שמסננים לנו את הנתונים בזמן אמת, וגם את הסיכומים של הטבלה.
יתרון שני חשוב לא פחות, הוא שבניגוד לטבלת Pivot שמחייבת אותנו לרענן אותה, בכל פעם שהנתונים השתנו, אז באמצעות השיטה הזאת, הכל מתרענן מייד.
כמו לכל דבר, לשיטה הזו, יש גם חסרונות, והחסרון שלה, הוא שבניגוד ל- Pivot, שאפשר פשוט ליצור בכמה קליקים, אז בשיטה הזו, כדי להגיע לתוצאה הרצויה, לוקח כמה דקות של הגדרות פשוטות ונוסחאות אקסל.
לדעתי האישית, זה שווה את זה, כיוון שאחרי שבונים את זה פעם אחת, התוצאה, יותר טובה לעומת טבלת Pivot, כיוון שהיא מתרעננת אוטומטית. ורואים את נתוני המקור מייד ב- Live.
בואו נראה כיצד עושים זאת.
נקודת ההתחלה - טבלת אקסל עם נתונים
אנחנו מתחילים מנקודה בה יש לנו טבלה אקסל כלשהיא, היא חייבת להיות מוגדרת בתור טבלה.
במקרה שלי, יש לי נתונים של מכירות של חנות “הכל לבית”.
בין אם אתה בעל עסק קטן או מנהל מחלקה בחברה גדולה, המומחים שלנו לאקסל שלנו יכולים לשנות את האופן שבו אתה עובד.
עם ידע עמוק וניסיון רב שנים באקסל, המומחים שלנו יסייע לך למקסם את הפוטנציאל של הנתונים שלך.
חסוך זמן ומשאבים בעזרת קבצי אקסל משוכללים, דוחות אוטומטיים וממשקים חכמים.
צא לדרך לעסק יעיל וממוקד נתונים עוד היום - צור קשר עכשיו!
שלב שני - ניצור סלייסרים לסינון הנתונים
איך מוסיפים סלייסרים באקסל ?
כעת אני יוצר סלייסרים שיאפשרו לי לסנן את הטבלה לפי כל עמודה שנרצה.
נא תשומת לב שאני יוצר את הסלייסרים מעל הטבלה עם הנתונים באקסל.
- נעמוד בתא כלשהוא בתוך טבלת האקסל
- נלחץ על הוספה
- ואז על כפתור כלי פריסה
הוספת סלייסרים לטבלת אקסל - כעת נסמן ב-V כל עמודה שנרצה ליצור עבורה סלייסר.
- ונעצב את הסלייסר לפי הטעם האישי (אני הורדתי כותרת עליונה ויצרתי עיצוב מותאם אישית לצבעים של הטבלה).
התוצאה בשלב זה נראית כך :
רוצה ליצור דוחות ותרשימים מתקדמים בעזרת Excel?
שירות של מומחה Excel יכול להציע לך פתרונות מותאמים לצרכים שלך.
צור קשר עכשיו
שלב שני - הוספת נוסחאות סיכום שיודעות להתעלם משורות מוסתרות
כעת, נשאר לי רק דבר אחד לעשות , להוסיף נוסחאות שמבצעות סיכום לטבלה. כמובן אפשר גם לעשות כל מדד אחר שתרצו, ולא חייבים דווקא SUM, אלא אפשר גם ממוצע, סטיית תקן, מינימום, מקסימום וכדומה…
אני אדגים על סיכום, כי זה המדד הנפוץ.
אבל, יש פה טריק מסויים, אנחנו רוצים שהסיכום של הטבלה, יתייחס רק לשורות שמוצגות כרגע על המסך.
במילים אחרות, אנחנו רוצים, שאם לחצנו על כמה סינונים בסלייסרים, והטבלה כעת מציגה רק שורות מסוננות, אז הסיכום יתייחס רק לשורות המסוננות.
יש 2 נוסחאות שיכולות לעשות זאת עבורנו : SUBTOTAL, AGGREGATE.
לצורך הדוגמה, אני אדגים עם SUBTOTAL. אבל אציין, שמי שרוצה יותר אפשרויות, מוזמן ללמוד על נוסחת AGGREGATE שמאפשרת מעט יותר גמישות.
איך משתמשים בנוסחת SUBTOTAL של אקסל ?
נוסחת SUBTOTAL מורכבת כך :
=SUBTOTAL( function: איזה סוג סיכום לבצע , data : איזה עמודה בטבלה לסכם )
הפרמטר הראשון - סוג הסיכום - יש כל מיני אפשרויות, ולכל אפשרות יש קוד.
מה שחשוב לשים לב הוא שלכל סוג של סיכום - יש 2 קודים.
- קוד אחד מאפשר לכלול את השורות המוסתרות.
- קוד אחר מאפשר להתעלם מהשורות המוסתרות.
| סוג הסיכום הרצוי | הקוד שצריך להקליד אם רוצים לכלול גם את השורות המוסתרות | הקוד שצריך להקליד אם רוצים להתעלם מהשורות שמוסתרות |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
אז לצורך הדוגמה, אנחנו רוצים לסכם (SUM) , אבל להתעלם מהשורות המוסתרות, ולכן נבחר בקוד 109.
ולכן הנוסחה תהיה:
=SUBTOTAL(109,מכירות[סכום מכירה])
אני שם תא עם הנוסחה הזאת מעל הטבלה, מתחת לסלייסרים.
וזהו - הגענו ליעד. יש לנו עכשיו אפשרות:
- לסנן את הנתונים בלחיצות על הסלייסרים
- לראות מייד את השורות המסוננות הרלוונטיות
- לראות מיד את הסיכום לפי סינון מסויים.
התוצאה הסופית אצלי נראית כך:
רוצה להפוך את עבודתך ב-Excel לפחות מסובכת ויותר יעילה?
מומחה Excel יכול להפוך תהליכים לאוטומטים ולבנות עבורך אקסלים יעילים.
צור קשר עכשיו
טיפ נוסף - איך להסיר סינון מכל הסלייסרים בבת אחת
כתוצאה מעבודה עם לקוחות, אני מביא כאן עוד טיפ קטן ומועילץ
הרי אם לוחצים על כמה סלייסרים, למעשה “הלבשנו” כמה סינונים ביחד על הטבלה. אבל, כשסיימנו, איך אפשר להוריד את כל הסינונים בבת אחת ? בלי הטירחה שכרוכה במעבר על כל הסלייסרים ?
אז עושים זאת בקלות : עומדים על תא כלשהוא בטבלה > נתונים > נקה.
וכדי לעשות את זה עוד יותר קל, תלחצו על הכפתור הזה מקש ימני, ואז “הוספה לסרגל הכלים לגישה מהירה”, וזה יעבור לסרגל שנמצא בראש המסך.
שימו לב! אם הכפתור הזה מופיע אצלכם באפור (=לא פעיל) זה כיוון שאתם לא עומדים על תא בתוך הטבלה.
סיכום - איך ליצור דוחות אינטראקטיבים כתחליף לטבלת Pivot
ראינו פה שיטה ממש פשוטה, לייצר דוחות אינטראקטיבים, שגם מאפשרים לסנן את המידע בכמה לחיצות פשוטות, וגם לראות את כל הסה"כ ועוד מדדים, תוך התייחסות לשורות שמסוננות.
אתם מוזמנים להרחיב את זה גם לגרפים וכדומה…
בהצלחה!
