צבירות הן דרך לצמצם, לסכם או לקבץ נתונים. כאשר אתה מתחיל עם נתונים גולמיים מטבלאות או ממקורות נתונים אחרים, הנתונים לרוב שטוחים, כלומר יש הרבה פרטים, אך הם לא אורגנו או קובצו בשום צורה. היעדר סיכומים או מבנה עלול להקשות על גילוי דפוסים בנתונים. חלק חשוב במידול נתונים הוא להגדיר צבירות שמפשטות, מפשטות או מסכמות דפוסים בתשובה לשאלה עסקית ספציפית.
ניתן ליצור את רוב הצבירות, כגון אלה המשתמשות ב- AVERAGE,COUNT,DISTINCTCOUNT,MAX, MIN או SUM, בסכום אוטומטי באמצעות סכום אוטומטי. סוגים אחרים של צבירות, כגון AVERAGEX, COUNTX, COUNTROWS או SUMX, מחזירים טבלה ודורשים נוסחה שנוצרת באמצעות Data Analysis Expressions (DAX).
הכרת צבירות ב- Power Pivot
בחירת קבוצות לצבירה
בעת צבירת נתונים, אתה מקבץ נתונים לפי תכונות כגון מוצר, מחיר, אזור או תאריך ולאחר מכן מגדיר נוסחה שפועלת על כל הנתונים בקבוצה. לדוגמה, בעת יצירת סכום כולל עבור שנה, אתה יוצר צבירה. אם אתה יוצר אז יחס של השנה הנוכחית לעומת השנה הקודמת ומציג אותם כאחוזים, זה סוג אחר של צבירה.
ההחלטה כיצד לקבץ את הנתונים מונעת על-ידי השאלה העסקית. לדוגמה, צבירות יכולות לענות על השאלות הבאות:
ספירות כמה עסקאות היו בחודש?
ממוצעים מה היו ממוצע המכירות בחודש זה, לפי איש מכירות?
ערכי מינימום ומקסימום אילו מחוזות מכירות היו חמשת המובילים מבחינת יחידות שנמכרו?
כדי ליצור חישוב שעונה על שאלות אלה, דרושים לך נתונים מפורטים המכילים את המספרים שיש לספור או לסכם, ונתונים מספריים אלה חייבים להיות קשורים בדרך כלשהי לקבוצות שבהן תשתמש לארגון התוצאות.
אם הנתונים אינם מכילים עדיין ערכים שבהם באפשרותך להשתמש לקיבוץ, כגון קטגוריית מוצר או שם האזור הגיאוגרפי שבו ממוקמת המאגר, מומלץ להכניס קבוצות לנתונים שלך על-ידי הוספת קטגוריות. בעת בניית קבוצות ב- Excel, עליך להקליד או לבחור את הקבוצות שבהן ברצונך להשתמש באופן ידני מתוך העמודות בגליון העבודה שלך. עם זאת, במערכת יחסית, הירארכיות כגון קטגוריות עבור מוצרים מאוחסנות לעתים קרובות בטבלה שונה מטבלת העובדות או הערכים. בדרך כלל, טבלת הקטגוריות מקושרת לנתוני העובדות באמצעות מפתח כלשהו. לדוגמה, נניח שהנתונים שלך מכילים מזהי מוצרים, אך לא את שמות המוצרים או הקטגוריות שלהם. כדי להוסיף את הקטגוריה לגליון עבודה שטוח של Excel, יהיה עליך להעתיק בעמודה המכילה את שמות הקטגוריות. באמצעות Power Pivot, באפשרותך לייבא את טבלת קטגוריות המוצרים למודל הנתונים שלך, ליצור קשר גומלין בין הטבלה עם נתוני המספרים ורשימת קטגוריות המוצרים ולאחר מכן להשתמש בקטגוריות כדי לקבץ נתונים. לקבלת מידע נוסף, ראה יצירת קשר גומלין בין טבלאות.
בחירת פונקציה לצבירה
לאחר שזיהית והוספת את הקיבוצים שבהם יש להשתמש, עליך להחליט באילו פונקציות מתמטיות להשתמש לצבירה. לעתים קרובות, המילה 'צבירה' משמשת כמילה נרדפת לפעולות מתמטיות או סטטיסטיות המשמשות בצבירה, כגון סכומים, ממוצעים, מינימום או ספירות. עם זאת, Power Pivot מאפשר לך ליצור נוסחאות מותאמות אישית לצבירה, בנוסף לצבירה הרגילה הנמצאת הן ב- Power Pivot והן ב- Excel.
לדוגמה, בהינתן אותה ערכת ערכים וקבוצות ששימשו בדוגמה הקודמת, באפשרותך ליצור צבירות מותאמות אישית העונות על השאלות הבאות:
ספירות מסוננות כמה עסקאות היו בחודש, לא כולל חלון התחזוקה של סוף החודש?
יחסי שימוש בממוצעים לאורך זמן מה היה אחוז הצמיחה או הירידה במכירות בהשוואה לתקופה המקבילה אשתקד?
ערכי מינימום ומקסימום מקובצים אילו מחוזות מכירות דורגו במקום הראשון עבור כל קטגוריית מוצר, או עבור כל קידום מכירות?
הוספת צבירה לנוסחאות ול- PivotTables
לאחר שתקבל מושג כללי לגבי האופן שבו יש לקבץ את הנתונים שלך כדי שתהיה להם משמעות, ולגבי הערכים שאיתם ברצונך לעבוד, תוכל להחליט אם לבנות PivotTable או ליצור חישובים בתוך טבלה. Power Pivot מרחיב ומשפר את היכולת המקורית של Excel ליצור צבירות כגון סכומים, ספירות או ממוצעים. באפשרותך ליצור צבירות מותאמות אישית ב- Power Pivot מתוך חלון Power Pivot או בתוך אזור PivotTable של Excel.
- בעמודה מחושבת, באפשרותך ליצור צבירות המתחשבות בהקשר השורה הנוכחי כדי לאחזר שורות קשורות מטבלה אחרת ולאחר מכן לסכם, לספור או לחשב ממוצע של ערכים אלה בשורות הקשורות.
- כמדיד, באפשרותך ליצור צבירות דינאמיות המשתמשות הן במסננים המוגדרים בנוסחה והן במסננים שנכפים על-ידי עיצוב ה- PivotTable ובבחירה של כלי פריסה, כותרות עמודות וכותרות שורה. ניתן ליצור מדידות המשתמשות בצבירות רגילות ב- Power Pivot באמצעות סכום אוטומטי או על-ידי יצירת נוסחה. באפשרותך גם ליצור מדידים משתמעים באמצעות צבירות רגילות ב- PivotTable ב- Excel.
הוספת קיבוצים ל- PivotTable
בעת עיצוב PivotTable, עליך לגרור שדות המייצגים קבוצות, קטגוריות או הירארכיות למקטע 'עמודות ושורות' של ה- PivotTable כדי לקבץ את הנתונים. לאחר מכן, גרור שדות המכילים ערכים מספריים לאזור הערכים כדי שניתן יהיה לספור אותם, לחשב ממוצע שלהם או לסכם אותם.
אם אתה מוסיף קטגוריות ל- PivotTable, אך נתוני הקטגוריות אינם קשורים לנתוני העובדות, אתה עלול לקבל שגיאה או תוצאות מוזרות. בדרך כלל, Power Pivot ינסה לפתור את הבעיה על-ידי זיהוי והצעה אוטומטיים של קשרי גומלין. לקבלת מידע נוסף, ראה עבודה עם קשרי גומלין בטבלאות PivotTable.
באפשרותך גם לגרור שדות לתוך כלי פריסה כדי לבחור קבוצות נתונים מסוימות להצגה. כלי פריסה מאפשרים לך לקבץ, למיין ולסנן באופן אינטראקטיבי את התוצאות ב- PivotTable.
עבודה עם קיבוצים בנוסחה
באפשרותך גם להשתמש בקיבוצים ובקטגוריות כדי לצבור נתונים המאוחסנים בטבלאות על-ידי יצירת קשרי גומלין בין טבלאות ולאחר מכן יצירת נוסחאות המנצלות קשרי גומלין אלה כדי לחפש ערכים קשורים.
במילים אחרות, אם ברצונך ליצור נוסחה המקבצת ערכים לפי קטגוריה, עליך להשתמש תחילה בקשר גומלין כדי לחבר בין הטבלה המכילה את נתוני הפירוט והטבלאות המכילות את הקטגוריות ולאחר מכן לבנות את הנוסחה.
לקבלת מידע נוסף על יצירת נוסחאות המשתמשות בבדיקות מידע, ראה בדיקות מידע בנוסחאות של Power Pivot.
שימוש במסננים בצבירות
תכונה חדשה ב- Power Pivot היא היכולת להחיל מסננים על עמודות וטבלאות של נתונים, לא רק בממשק המשתמש ובתוך PivotTable או תרשים, אלא גם על הנוסחאות שבהן אתה משתמש לחישוב צבירה. ניתן להשתמש במסננים בנוסחאות הן בעמודות מחושבות והן ב- s.
לדוגמה, בפונקציות הצבירה החדשות של DAX, במקום לציין ערכים שיש לסכם או לספור, באפשרותך לציין טבלה שלמה בתור הארגומנט. אם לא החלת מסננים על אותה טבלה, פונקציית הצבירה תפעל מול כל הערכים בעמודה שצוינה בטבלה. עם זאת, ב- DAX ניתן ליצור מסנן דינאמי או סטטי בטבלה, כך שהצבירה תפעל מול ערכת משנה שונה של נתונים בהתאם לתנאי המסנן ולהקשר הנוכחי.
על-ידי שילוב תנאים ומסננים בנוסחאות, באפשרותך ליצור צבירות המשתנות בהתאם לערכים המסופקים בנוסחאות, או משתנות בהתאם לבחירה של שורות, כותרות וכותרות עמודות ב- PivotTable.
לקבלת מידע נוסף, ראה סינון נתונים בנוסחאות.
השוואה בין פונקציות צבירה של Excel ופונקציות צבירה של DAX
הטבלה הבאה מפרטת כמה מפונקציות הצבירה הסטנדרטיות שמספק Excel, ומספקת קישורים ליישום של פונקציות אלה ב- Power Pivot. אופן הפעולה של גירסת DAX של פונקציות אלה זהה לזה של גירסת Excel, עם כמה הבדלים קלים בתחביר ובטיפול בסוגי נתונים מסוימים.
פונקציות צבירה Standard
| פונקציה | שימוש |
|---|---|
| AVERAGE | החזרת הממוצע (ממוצע חשבוני) של כל המספרים בעמודה. |
| AVERAGEA | הפונקציה מחזירה את הממוצע (ממוצע חשבוני) של כל הערכים בעמודה. מטפל בטקסט ובערכים שאינם מספריים. |
| COUNT | ספירת הערכים המספריים בעמודה. |
| COUNTA | ספירת מספר הערכים בעמודה שאינם ריקים. |
| MAX | הפונקציה מחזירה את הערך המספרי הגדול ביותר בעמודה. |
| MAXX | החזרת הערך הגדול ביותר מתוך ערכת ביטויים המוערכים על-פני טבלה. |
| MIN | הפונקציה מחזירה את הערך המספרי הקטן ביותר בעמודה. |
| מינקס | החזרת הערך הקטן ביותר מתוך ערכת ביטויים המוערכים על-פני טבלה. |
| SUM | חיבור כל המספרים בעמודה. |
פונקציות צבירה של DAX
DAX כולל פונקציות צבירה המאפשרות לך לציין טבלה שבה יש לבצע את הצבירה. לכן, במקום פשוט להוסיף או לחשב ממוצע של הערכים בעמודה, פונקציות אלה מאפשרות לך ליצור ביטוי המגדיר באופן דינאמי את הנתונים שיש לצבור.
הטבלה הבאה מפרטת את פונקציות הצבירה הזמינות ב- DAX.
| פונקציה | שימוש |
|---|---|
| AVERAGEX | חישוב ממוצע של ערכת ביטויים המוערכים בטבלה. |
| קונסולה | מניית ערכת ביטויים המוערכת בטבלה. |
| COUNTBLANK | ספירת מספר הערכים הריקים בעמודה. |
| COUNTX | ספירת מספר השורות הכולל בטבלה. |
| ספירות | מונה את מספר השורות המוחזרות מפונקציית טבלה מקוננת, כגון הפונקציה filter. |
| סומקס | הפונקציה מחזירה את הסכום של ערכת ביטויים המוערכים בטבלה. |
הבדלים בין פונקציות צבירה של DAX ו- Excel
למרות שלפונקציות אלה יש שמות זהים לשמות מקבילותיהן ב- Excel, הן משתמשות במנגנון הניתוח המוצג בזיכרון של Power Pivot ועברו שכתוב כדי לעבוד עם טבלאות ועמודות. לא ניתן להשתמש בנוסחת DAX בחוברת עבודה של Excel, ולהיפך. ניתן להשתמש בהם רק בחלון Power Pivot ובטבלאות PivotTable המבוססים על נתוני Power Pivot. כמו כן, על אף שלפונקציות יש שמות זהים, אופן הפעולה עשוי להיות מעט שונה. לקבלת מידע נוסף, עיין בנושאי העזר הספציפיים לפונקציות.
האופן שבו עמודות מוערכות בצבירה שונה גם הוא מהאופן שבו Excel מטפל בצבירה. דוגמה עשויה לעזור להמחיש.
נניח שברצונך לקבל סכום של הערכים בעמודה Amount בטבלה Sales, ולכן תיצור את הנוסחה הבאה:
=SUM('Sales'[Amount])
במקרה הפשוט ביותר, הפונקציה מקבלת את הערכים מעמודה אחת לא מסוננת והתוצאה זהה לזו של Excel, שתמיד רק מחברת את הערכים בעמודה Amount. עם זאת, ב- Power Pivot, הנוסחה מתפרשת כך: 'קבל את הערך בסכום (Amount עבור כל שורה של הטבלה Sales) ולאחר מכן חבר ערכים בודדים אלה. Power Pivot מעריך כל שורה שבה מתבצעת הצבירה, מחשב ערך סקלרי בודד עבור כל שורה, ולאחר מכן מבצע צבירה על ערכים אלה. לכן, התוצאה של נוסחה יכולה להיות שונה אם מסננים הוחלו על טבלה, או אם הערכים מחושבים בהתבסס על צבירות אחרות שעשויות להיות מסוננות. לקבלת מידע נוסף, ראה הקשר בנוסחאות DAX.
פונקציות בינת זמן של DAX
בנוסף לפונקציות צבירת הטבלאות המתוארות בסעיף הקודם, DAX כולל פונקציות צבירה הפועלות עם תאריכים ושעות שאתה מציין, כדי לספק בינת זמן מוכללת. פונקציות אלה משתמשות בטווחים של תאריכים כדי לקבל ערכים קשורים ולצבור את הערכים. באפשרותך גם להשוות ערכים בין טווחי תאריכים.
הטבלה הבאה מפרטת את פונקציות בינת הזמן שבהן ניתן להשתמש לצבירה.
| פונקציה | שימוש |
|---|---|
|
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER שנת סגירה |
חישוב ערך בסוף התקופה הקלנדרית הנתונה. |
|
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR |
חישוב ערך בסוף לוח השנה של התקופה הקודמת לתקופה הנתונה. |
|
TOTALMTD TOTALYTD TOTALQTD |
הפונקציה מחשבת ערך במרווח הזמן שמתחיל ביום הראשון של התקופה ומסתיים בתאריך המאוחר ביותר בעמודת התאריך שצוינה. |
הפונקציות האחרות בסעיף הפונקציה 'בינת זמן' (פונקציות בינת זמן) הן פונקציות שניתן להשתמש בהן כדי לאחזר תאריכים או טווחים מותאמים אישית של תאריכים לשימוש בצבירה. לדוגמה, באפשרותך להשתמש בפונקציה DATESINPERIOD כדי להחזיר טווח תאריכים, ולהשתמש בערכת תאריכים זו כארגומנט לפונקציה אחרת לחישוב צבירה מותאמת אישית עבור תאריכים אלה בלבד.