פונקציית GROUPBY באקסל – עיצובים וטיפים אחרים…
במאמר הזה למדנו על GROUPBY באקסל 365, שיכול להחליף במקרים מסוימים שימוש בטבלאות ציר, באמצעות שימוש בפונקציה.
אני מזכירה לכם את התחביר הבסיסי של הפונקציה:
=GROUPBY(row_fields, values, function)
וכבר כאן אנחנו נתקלים בבעיה הראשונה, כשאנחנו רוצים להציג יותר מאשר עמודת נתונים אחת.
בואו נביט בטבלת הנתונים הזו:
אנחנו רוצים לסכום את סך המכירות על פי דגם וארץ יצור, ולכן נשתמש בפונקציה:
=GROUPBY(B1:C15,F1:F15,SUM)
וזו התוצאה שהתקבלה:
אבל מה אם נרצה לסכום על פי דגם וצבע? את זה הפונקציה כבר לא יודעת לעשות, מכיוון ששתי העמודות אינן רציפות.
אז מה הפתרון?
האמת היא שאני יכולה להציע לכם שני פתרונות: האחד על ידי שימוש ב CHOOSCOLS, והשני על ידי שימוש ב HSTACK, שעליו למדנו כאן
פתרון באמצעות CHOOSECOLS
הפונקציה מאפשרת לבחור עמודות מתוך טווח, במקרה שלנו, מתוך כל הטבלה, אנחנו מעוניינים לבחור את עמודות הדגם והצבע.
מכיוון שפונקציות באקסל מחושבות מבפנים החוצה, נתחיל בבנייה של הטווח הרצוי, ולאחר מכן נעטוף אותו בפונקציה GROUPBY:
זו הפונקציה:
=CHOOSECOLS(B1:D15,1,3)
וזו התוצאה:
כעת נעטוף את התוצאה בפונקציה GROUPBY:
=GROUPBY(CHOOSECOLS(B1:D15,1,3),F1:F15,SUM)
שימו לב, שכאשר אני מסמנת את ה row_fields, הפונקציה CHOOSECOLS מודגשת:
וזו התוצאה שהתקבלה:
פתרון באמצעות HSTACK
כמו קודם, נבנה את הפונקציה מבפנים החוצה:
נצרף את שתי העמודות זו לזו:
=HSTACK(B1:B15,D1:D15)
ולאחר מכן נעטוף בפונקציה GROUPBY:
ונקבל את אותה התוצאה שקיבלנו בשימוש בפונקציה CHOOSECOLS
עיצוב מספר
שימו לב שהמספרים מתקבלים ללא עיצוב.
בטבלאות ציר קל לעצב את הערכים בלחיצה ימנית על השדה ובחירה ב 'תבנית מספר'
אני מאוד מקווה שאתם לא מסמנים את העמודה ומעצבים באמצעות התפריט, או בוחרים ב'עיצוב תאים'.
בחירה בתבנית מספר תבטיח שגם אם יתווספו נתונים, הם יקבלו את העיצוב שבחרתם, ואם תגררו לעמודות שדה, שיציג את התוצאה בשתי עמודות, העיצוב שבחרתם בתבנית מספר יישמר
אז איך מעצבים את התאים כשמשתמשים בפונקציה:
אפשר בשיטה האינטואיטיבית שהיא סימון הנתונים (כדאי לסמן כמה שורות אקסטרה, למקרה שהטבלה תגדל), ואפשר לעטוף את כל הפונקציה בפונקציה TEXT שמאפשרת לעצב את התוצאה בתוך הפונקציה.
זו הפונקציה:
=TEXT(GROUPBY(B1:B15,E1:E15,SUM),"#,#")
וזו התוצאה שהתקבלה:
להסבר מפורט יותר על עיצוב תאים, ראו כאן
שימוש בעיצוב מותנה
כאשר אנחנו מבקשים להציג שתי רמות נתונים עם סיכומי ביניים, קשה להבחין בעין מתי מדובר בסכום שורה ומתי בסכום ביניים.
בתא K1 כתבתי את הפונקציה:
=GROUPBY(C1:D15,F1:F15,SUM,3)
וזו התוצאה שהתקבלה:
כדי להבחין את סיכומי הביניים משאר התוצאות, נשתמש בעיצוב מותנה:
נסמן טווח שגדול מעט יותר מהתוצאה, למקרה שבעתיד טווח התוצאות יגדל, ונקליד את הנוסחה הבאה:
וזו התוצאה שהתקבלה: