פונקציית GROUPBY באקסל 365 – קיבוץ וסיכום נתונים דינמי
פונקציית GROUPBY באקסל 365 מזכירה במידה רבה את טבלאות הציר (Pivot Tables) המוכרות, בכך שהיא מאפשרת לקבץ ולסכם נתונים לפי קטגוריות שונות.
אולם, בניגוד לשימוש בטבלאות ציר, הפונקציה מאפשרת לבצע חישובים על קבוצות נתונים בצורה דינמית, תוך שהיא מתעדכנת אוטומטית עם כל שינוי בנתונים, מבלי שנצטרך לרענן.
התחביר הבסיסי של GROUPBY
=GROUPBY(row_fields, values, function)
row_fields – העמודות שעל פיהן רוצים לקבץ את הנתונים.
values – העמודות שמכילה את הערכים המספריים שעליהם תבוצע הפעולה החישובית.
function – הפעולה החישובית שתבוצע על הנתונים המקובצים (כגון סכום, ממוצע, ספירה ועוד).
בואו נראה דוגמאות:
בטבלת האקסל הנ"ל מתועדות מכירות רכבים במגרש מכוניות:
נשתמש בפונקציה GROUPBY כדי לנתח את המידע:
סך המכירות לפי דגם מכונית
=GROUPBY(B1:B15, G1:G15, SUM)
הסבר: הפרמטר הראשון הוא העמודה שעל פיה נרצה לקבץ את הנתונים. מכיוון שאנחנו רוצים לקבץ על פי דגם, נסמן את המידע שנמצא בעמודה B
הפרמטר השני הוא העמודה שאותה נרצה לסכם, ובמקרה שלנו – עמודה G
ואילו הפרמטר האחרון הוא החישוב. מכיוון שאנחנו רוצים לחשב, בחרנו ב SUM.
וזו התוצאה שהתקבלה:
איך נדע אילו סוגי חישובים קיימים?
כאשר תקלידו את הפונקציה ותגיעו לפרמטר הרלוונטי, תיפתח רשימה של כל אפשרויות החישוב:
ממוצע מחיר מכירה לפי ארץ ייצור
=GROUPBY(D1:D15, G1:G15, AVERAGE)
וזו התוצאה שהתקבלה:
הצגה וסכימה על פי שתי עמודות
כדי להציג שתי קטגוריות, נסמן את שתי העמודות, באופן הבא:
=GROUPBY(B1:C17,G1:G17,SUM,3)
וזו התוצאה שתתקבל:
(שימו לב לפרמטר האחרון שמשמעותו היא הצגת כותרות. נדבר עליו בהמשך המאמר)
כדי לערוך חישוב על שתי עמודות, נסמן את שתיהן, באופן הבא:
=GROUPBY(B1:B15,D1:E15,AVERAGE)
וזו התוצאה שתתקבל:
שימו לב שהעמודות חייבות להיות רציפות.
(עבור עמודות שאינן רציפות צריך לערוך מניפולציה, שחורגת מתכולתו של מאמר זה).
פרמטרים אופציונליים ב-GROUPBY
מעבר לשלושת הפרמטרים הראשונים, אותם חובה להזין, ניתן לבחור גם בפרמטרים הבאים:
field_headers – מאפשר להציג כותרות או להשמיט אותן:
כדי להציג את הכותרות, בחרו באפשרות מס' 3 – 'כן, והצג'
total_depth – אם בחרנו בשתי כותרות, נוכל להציג את סיכומי הביניים:
נוכל לבחור להציג את כל הסיכומים בסוף, או אחרי כל קטגוריה, לפי בחירתנו.
sort_order – מאפשר לקבוע את סדר המיון של התוצאות על ידי הקלדת מספר העמודה שאותה נרצה למיין. מספר חיובי ימיין בסדר עולה, ומספר שלילי בסדר יורד.
שימו לב, מספר העמודה הוא מספרה בתוך הפונקציה, ולא מספרה בטבלה, כך שאם נרצה להציג את סך המכירות בסדר עולה, על פי דגם, נכתוב 2, מכיוון שזו העמודה השנייה בטבלה שנוצרה:
filter_array – מאפשר לסנן את השורות המוצגות לפי תנאים מותאמים אישית, לדוגמה – הצגת כל הדגמים, פרט לפיאט:
=GROUPBY(B1:B15,E1:E15,SUM,3,,,B1:B15<>"פיאט")
וזו התוצאה שהתקבלה:
