מי צריך PivotTable? הכירו את GROUPBY ו-PIVOTBY ב-Excel ! 🧩
היי חברים ! היום אנחנו הולכים לדבר על שתי פונקציות ב-Excel שיכולות לעזור לנו לנתח נתונים בצורה פשוטה ומהירה יותר: GROUPBY ו-PIVOTBY. אם אתם כבר משתמשים ב-Pivot Table הוותיק של Excel, אתם תשמחו לגלות שהפונקציות האלו יכולות לחסוך לכם זמן ולהפוך את העבודה ליותר קלה במעט.
בפוסט הזה, אני אסביר לכם מה זה GROUPBY ו-PIVOTBY, מה היתרונות שלהם, ואיך הם משתווים ל-PivotTable הרגיל, וכמובן נעבור גם על החסרונות ביחס ל-PIVOT. נראה צעד-צעד כיצד להשתמש בפונקציות האלה תוך כדי דוגמאות מעשיות. בואו נתחיל ! 🚀
שימו לב שנוסחאות אלו זמינות רק למי שיש לו Office 365 (כלומר אקסל 2024 ומעלה). ⚠️
מה זה GROUPBY ו-PIVOTBY ולמה כדאי להשתמש בהם?
GROUPBY ו-PIVOTBY הן פונקציות ב- Excel שמאפשרות לנו לסכם ולנתח נתונים ישירות בגליון העבודה, בלי צורך בכלי נפרד כמו ב-PIVOT.
אם נשווה את הנוסחאות GROUPBY ו-PIVOTBY ל-PivotTable הרגיל,
- PivotTable: כלי מעולה ומוכר שיוצר טבלה נפרדת לסיכום נתונים. הוא חזק, אבל לפעמים דורש יותר עבודה – צריך לבחור שדות, לגרור אותם למקומות הנכונים, וללחוץ ידנית על “רענן” בכל פעם שהנתונים מתעדכנים - שזה טירחה מסויימת ופתח לטעויות (קורה המון ששוכחים ללחוץ רענון).
- GROUPBY ו-PIVOTBY: הן נוסחאות רגילות של EXCEL. אין צורך ללחוץ על כפתור ולגרור שדות, אלא רק לכתוב נוסחה. התוצאות מופיעות.
היתרון העיקרי של GROUPBY ו- PIVOTBY לדעתי הוא - שהן דינמיות, משתנות מייד בהתאם לנתונים. ⚡
כמו כל דבר בחיים, יש לפונקציות האלו גם חסרונות.
החסרונות של GROUPBY ו-PIVOTBY 😕
- צריך לכתוב נוסחה ולא לגרור שדות, עבור חלק מאיתנו זה מסובך יותר.
- התוצאה שלהן, מופיעה בעיצוב רגיל, ולא מעוצבת מראש. למשל, סיכומי ביניים לא יופיעו מודגשים.
- בשביל לסכם לפי כמה עמודות, צריך לעשות טריק קטן.
- בשביל להשתמש בהן עם סלייסרים על הטבלה, צריך גם כן טריק.
כלומר, יש 2 צדדים למטבע. לדעתי היתרון של הדינמיות גובר על כל החסרונות. ולכן, בואו נבין איך משתמשים בפונקציות האלו. נתחיל עם GROUPBY בדוגמה פשוטה ומשם נתקדם, ואסביר גם איך להתגבר על החסרונות.
הסבר על GROUPBY עם דוגמה פשוטה
GROUPBY היא פונקציה שמאפשרת לנו:
- לסכם נתונים לפי קבוצות.
נניח שיש לנו טבלת מכירות של גאדג’טים ב-2025, ואנחנו רוצים לדעת כמה יחידות נמכרו מכל מוצר בסך הכול. בואו נתחיל עם הדוגמה הפשוטה הזו, בלי להסתבך עם פרמטרים נוספים.
הנתונים שלנו כוללים עמודות כמו:
- מוצר (למשל: “גאדג’ט A”, “גאדג’ט B”)
- כמות נמכרת (מספר היחידות שנמכרו בכל עסקה)
- ועוד כמה עמודות שאנחנו נשתמש בהן בהמשך.
הנוסחה הבסיסית של GROUPBY נראית ככה:
=GROUPBY(a-לפי-מה-מסכמים, b-את-מה-מסכמים, c-איזה-פונקציה-להשתמש)
נניח בדוגמה שלנו:
- לפי מה מסכמים? לפי מוצר.
- את מה מסכמים? את הכמות נמכרת.
- באיזה פונקציה להשתמש לסיכום? אנחנו בחרנו בפונקציה SUM, כי אנחנו רוצים את הכמות הכוללת לפי כל מוצר.
מה נקבל? טבלה קטנה עם שתי עמודות:
- עמודה אחת עם שמות המוצרים.
- עמודה שנייה עם סך הכמות הנמכרת לכל מוצר.
זו התוצאה של GROUPBY
=GROUPBY(sales[מוצר],sales[כמות נמכרת],SUM)
פשוט וקל. עכשיו, בואו נראה עכשיו איך אפשר לסכם לפי שתי עמודות ביחד. 🔗
איך להשתמש ב-GROUPBY וב-PIVOTBY כדי לסכם לפי 2 עמודות או יותר?
כשמסכמים נתונים לפי קבוצות, לפעמים מסכמים לפי משהו שכולל כמה עמודות יחד.
למשל, לסכם מכירות לפי מוצר+איש מכירות, או דוגמה נוספת: מוצר+איזור מכירה.
אז בטבלת PIVOT רגילה, זה אפשרי בקלות, כי פשוט נגרור גם את העמודה הנוספת.
בנוסחאות GROUPBY ו-PIVOTBY, זה אפשרי אומנם בקלות אם 2 העמודות (או יותר) צמודות אחת לשנייה,
במקרה כזה פשוט נסמן את 2 העמודות (או יותר), או פשוט נכתוב את העמודות עם סימן : בין לבין.
אבל, אם העמודות לא צמודות אחת לשניה - זו בעיה.
אז כדי לעשות זאת, כלומר לסכם לפי 2 (או יותר) עמודות שלא צמודות אחת לשניה, כדי לקבל את אותה תוצאה בנוסחאות GROUPBY ו- PIVOTBY, נצטרך להכניס לתמונה פונקציה נוספת שנקראת HSTACK, שמאפשרת לנו לשלב שתי עמודות (או יותר) בתוך ה- PIVOTBY GROUPBY.
מה עושה נוסחת HSTACK?
נוסחת HSTACK בעצם “מדביקה יחד” כמה העמודות, כך שמבחינת GROUPBY או PIVOTBY, סט הנתונים שנוצר מתוך ה-HSTACK - הוא בעצם טבלת הקבוצות שלנו. 🧱
דוגמה בפועל, אני רוצה לסכם לפי 2 עמודות מוצר+איזור מכירה.
אז יצרתי נוסחת HSTACK שמחזירה “טבלה” של 2 עמודות יחד. והנוסחת HSTACK שיצרתי משולבת בתוך נוסחת GROUPBY.
=GROUPBY(HSTACK(sales[מוצר],sales[אזור מכירה]),sales[כמות נמכרת],SUM)
עכשיו, בואו נראה איך אפשר לשדרג את ה-GROUPBY עם מיון וסינון.
מיון וסינון ב-GROUPBY וב-PIVOTBY
עד עכשיו השתמשנו ב-GROUPBY בצורה בסיסית, אבל יש לה אפשרות להוסיף פרמטרים שיכולים לעזור לנו להתאים את התוצאה בדיוק למה שאנחנו צריכים. שניים מהם הם מיון וסינון.
מיון
נניח שאנחנו רוצים לראות את המוצרים מסודרים לפי הכמות הנמכרת, מהגבוה לנמוך. אז בפרמטר שנקרא sort, נרשום את מספר העמודה שרוצים למיין לפיה.
כאשר כברירת מחדל - המיון הוא בסדר עולה.
אבל אם נרשום את המספר עם מינוס, זה ימיין את העמודה בסדר יורד. 🔽
למשל, בדוגמה הזו, מיינתי לפי העמודה הראשונה והיחידה - עמודת מוצר, ומכיוון שהשתמשתי בסימן מינוס, אז המיון יהיה בסדר יורד, כלומר מהאות ת’ לאות א'.
=GROUPBY(sales[מוצר],sales[כמות נמכרת],SUM,,,-1)
וזו התוצאה של נוסחת GROUPBY עם מיון בסדר יורד:
ככה אנחנו יכולים לראות מיד מה המוצרים הכי נמכרים.
סינון ב- GROUPBY וב-PIVOTBY
מה אם אנחנו רוצים להציג רק מוצרים שנמכרו ביותר מ-1000 יחידות? אפשר להוסיף סינון, בפרמטר שנקרא Filter.
למשל, בואו נסכם רק מכירות שלא בוצעו בינואר, אז כדי לעשות זאת, בפרמטר שנקרא filter הוספתי תנאי שאומר להתעלם, לסנן החוצה את ינואר . 🎯
=GROUPBY(sales[מוצר],sales[כמות נמכרת],SUM,,,,sales[חודש]<>"ינואר")
ובאמת, התוצאה הייתה טבלה קטנה שסיכמה את הנתונים בלי הנתונים של חודש ינואר.
זה ממש עוזר כשאנחנו רוצים להתמקד בנתונים ספציפיים. עד כה ראינו איך להשתמש במיון וסינון, על GROUPBY (זה עובד אותו דבר עבור PIVOTBY )
עכשיו, בואו נעבור ל-PIVOTBY.
הסבר על PIVOTBY עם דוגמה של סיבוב ציר
הפונקציה PIVOTBY היא פונקציה דומה ל-GROUPBY, אבל היא לא רק מסכמת, אלא גם יוצרת טבלת PIVOT כלומר, זו נוסחה שמאפשרת לנו לראות סיכום של הנתונים בכל מיני ח
נחזור לנתונים שלנו. נניח שאנחנו רוצים לראות את הכמות הנמכרת לפי מוצר (בשורות) ולפי אזור מכירה (בעמודות). הנוסחה תיראה ככה:
=PIVOTBY(a-איזה-עמודות-יהיו-השורות-של-הטבלה
b-איזה-עמודות-יהיו-העמודות-של-הטבלה,
c-איזה-עמודות-יהיו-הערכים-שהטבלה-תסכם,
d-הפונקציה-שאיתה-נסכם)
אז בהתאם לטבלה שלנו, נעשה כך:
- איזה עמודות יהיו השורות של הטבלה: “ - העמודה מוצר
- איזה עמודות יהיו העמודות של הטבלה: “אזור מכירה” העמודה אזור מכירה
- איזה עמודות יהיו הערכים שהטבלה תסכם: “כמות נמכרת” זו העמודה שאנחנו מסכמים.
- הפונקציה שאיתה נסכם: “SUM”.
מה היתרון של “סיבוב הציר”?
במקום לראות את הנתונים בצורה שטוחה (רק לפי מוצר, למשל), אנחנו יכולים לראות איך הם מתפלגים בין שני חתכים – מוצרים ואזורים. זה נותן לנו תמונה ברורה יותר של המצב, ואנחנו יכולים בקלות להחליף בין השורות לעמודות כדי לראות את הנתונים מזווית אחרת. 🔄
=PIVOTBY(sales[מוצר],sales[אזור מכירה],sales[כמות נמכרת],SUM)
טריק להדגשת שורות “סיכום ביניים” ב-PIVOTBY
לשימוש ב-PIVOTBY יש אומנם יתרון גדול לעומת טבלת PIVOT - שהטבלה מתרעננת אוטומטית עם כל שינוי בנתונים. מצד שני, יש לה גם חיסרון גדול, שהטבלה שנוצרת כתוצאה מ-PIVOTBY, היא טבלה ללא עיצוב. לנו כבני אדם, העיצוב חשוב. למשל, אנחנו מאוד אוהבים את זה ששורות סיכום הביניים מודגשות, זה ממש הפך לסטנדרט. אז איך נוסיף את העיצוב הזה כשאנחנו משתמשים ב-PIVOTBY? התשובה תהיה - עם עיצוב מותנה.
הרעיון הוא כזה: בשורות הסיכום, יש תא ריק בעמודה שלצד התת-כותרת. למשל, אם העמודה הראשונה (A) היא “מוצר”, בעמודה השנייה (B) יהיה תא ריק בשורת הסיכום. אפשר לנצל את זה.
איך לעשות את זה?
- בחרו את הטווח של הטבלה שיצאה מה-PIVOTBY.
- לכו ל-“עיצוב מותנה” (Conditional Formatting) > “כלל חדש” > “השתמש בנוסחה”.
- כתבו נוסחה כמו: =ISBLANK(B2) (זה בודק אם התא בעמודה B ריק).
- בחרו עיצוב – למשל, מילוי צהוב – והחילו אותו.
עכשיו, כל שורת סיכום תודגש, ויהיה קל יותר לזהות את המבנה של הטבלה. זה טריק קטן, אבל ממש משפר את הקריאות של הטבלה שנוצרה עם PIVOTBY. ✨
סיכום: מה למדנו עם GROUPBY ועל PIVOTBY
אז מה ראינו היום? למדנו איך להשתמש ב-GROUPBY ו-PIVOTBY, שתי פונקציות ב-Excel שיכולות לשדרג את הדרך שבה אנחנו עובדים עם נתונים. ראינו איך GROUPBY מסכמת נתונים בקלות, איך אפשר לשלב עמודות עם HSTACK, ואיך מיון וסינון עוזרים להתאים את התוצאה של GROUPBY ושל PIVOTBY. אחר כך למדנו איך להשתמש ב-PIVOTBY וראינו איך היא “מסובבת” את הנתונים ויוצרת לנו סיכום לפי כמה חתכים שונים, ולבסוף למדנו טריק קטן לעיצוב מותנה שמשפר את הקריאות של הטבלה שנוצרת ב-PIVOTBY.
היתרונות של PIVOTBY ושל GROUPBY בקצרה:
- חיסכון בזמן: נוסחה אחת והכול מוכן – בלי להתעסק עם כפתור וגרירת שדות. ⏱️
- גמישות: אפשר לשלב עם פונקציות אחרות ולשלוט בתוצאה. 🛠️
- עדכון אוטומטי: הנתונים תמיד מתעדכנים בלי מאמץ. 🔁
אז למה אתם מחכים? תנסו את GROUPBY ו-PIVOTBY עם הנתונים שלכם ותראו איך הם יכולים לייעל את העבודה שלכם. זה לא רק חוסך זמן – זה גם כיף לראות את הנתונים מתארגנים כל כך מהר ! 😃
