צבירה היא דרך לקבץ, לסכם או לקבץ נתונים. כאשר אתה מתחיל עם נתונים גולמיים מטבלאות או ממקורות נתונים אחרים, הנתונים לעתים קרובות שטוחים, כלומר, ישנם פרטים רבים, אך הם לא מאורגנים או מקובצים בשום אופן. חוסר זה בסיכומים או במבנה עשוי לקשה על גילוי תבניות בנתונים. חלק חשוב של מידול נתונים הוא להגדיר צבירה המפשטת, מופשטת או מסכמת דפוסים בתשובה לשאלה עסקית ספציפית.
ניתן ליצור באופן אוטומטי את צבירות הנפוצות ביותר, כגון אלה המשתמשות ב- AVERAGE, COUNT, DISTINCTCOUNT, MAX, MINאו SUM באופן אוטומטי באמצעות סכום אוטומטי. סוגים אחרים של צבירות, כגון AVERAGEX, COUNTX, COUNTROWSאו SUMXמחזירים טבלה ודורשים נוסחה שנוצרה באמצעות ביטויי ניתוח נתונים (DAX).
הבנת צבירות ב- Power Pivot
בחירת קבוצות עבור צבירה
בעת צבירת נתונים, אתה מקבץ נתונים לפי תכונות כגון מוצר, מחיר, אזור או תאריך ולאחר מכן מגדיר נוסחה שעובדת על כל הנתונים בקבוצה. לדוגמה, בעת יצירת סכום כולל במשך שנה, אתה יוצר צבירה. אם לאחר מכן תיצור יחס של שנה זו במהלך השנה הקודמת ותציג את האחוזים כאחוזים, זהו סוג אחר של צבירה.
ההחלטה כיצד לקבץ את הנתונים מונחית על-ידי השאלה העסקית. לדוגמה, צבירות יכולות לענות על השאלות הבאות:
ספירות כמה עסקאות היו בחודש?
ממוצעים מה היו המכירות הנו אומרות בחודש זה, לפי איש מכירות?
ערכים מינימליים ומרביים אילו מחוזות מכירות היו חמשת היחידות המובילות שנמכרו?
כדי ליצור חישוב התשובות לשאלות אלה, חייבים להיות לך נתונים מפורטים המכילים את המספרים שיש לספור או לסכם, ונתונים מספריים אלה חייבים להיות קשורים בדרך כלשהי לקבוצות שתמשיך הבאות לארגון התוצאות.
אם הנתונים אינם מכילים עדיין ערכים שתרצה להשתמש בהם לקיבוץ, כגון קטגוריית מוצר או שם האזור הגיאוגרפי שבו נמצאת החנות, ייתכן שתרצה להציג קבוצות לנתונים שלך על-ידי הוספת קטגוריות. בעת בניית קבוצות ב- Excel, עליך להקליד או לבחור באופן ידני את הקבוצות שברצונך להשתמש הבאות מבין העמודות בגליון העבודה. עם זאת, במערכת יחסית, הירארכיות כגון קטגוריות עבור מוצרים מאוחסנות לעתים קרובות בטבלה שונה מהעובדה או מטבלת הערכים. בדרך כלל, טבלת הקטגוריות מקושרת לנתונים של העובדה באמצעות מפתח כלשהו. לדוגמה, נניח שהנתונים שלך מכילים מזהי מוצרים, אך לא את שמות המוצרים או את הקטגוריות שלהם. כדי להוסיף את הקטגוריה לגליון Excel עבודה שטוח, יהיה עליך להעתיק בתיבת עמודה המכילה את שמות הקטגוריות. באמצעות Power Pivot, באפשרותך לייבא את טבלת קטגוריית המוצר למודל הנתונים שלך, ליצור קשר גומלין בין הטבלה עם נתוני המספר ורשימת קטגוריות המוצר ולאחר מכן להשתמש בקטגוריות כדי לקבץ נתונים. לקבלת מידע נוסף, ראה יצירת קשר גומלין בין טבלאות.
בחירת פונקציה עבור צבירה
לאחר שזיהית והוספת את הקיבוץ שבו יש להשתמש, עליך להחליט בא באיזה פונקציות מתמטיות להשתמש עבור צבירה. לעתים קרובות צבירה של המילה משמשת כמילה נרדפת עבור הפעולות המתמטיות או הסטטיסטיות המשמשות צבירה, כגון סכומים, ממוצעים, מינימום או ספירות. עם זאת, Power Pivot מאפשר לך ליצור נוסחאות מותאמות אישית עבור צבירה, בנוסף לאגרגיות הסטנדרטיות שנמצאו הן ב- Power Pivot וב- Excel.
לדוגמה, בהינתן אותה קבוצה של ערכים וקיבוץ שהיו בשימוש בדוגמאות הקדימות, באפשרותך ליצור צבירות מותאמות אישית העונים על השאלות הבאות:
ספירות מסוננים כמה עסקאות היו שם בחודש, למעט חלון התחזוקה בסוף החודש?
יחסי גודל באמצעות ממוצעים לאורך זמן מה היתה הצמיחה או הירידה באחוזים במכירות בהשוואה לאותה תקופה בשנה שעברה?
ערכים מינימליים ומרביים מקובצים אילו מחוזות מכירות דורגו בראש כל קטגוריית מוצר, או עבור כל קידום מכירות?
הוספת צבירות לנוסחאות ולטבלאות PivotTable
כאשר יש לך מושג כללי כיצד יש לקבץ את הנתונים שלך כדי להיות בעלי משמעות, והערכים שאיתם ברצונך לעבוד, באפשרותך להחליט אם לבנות PivotTable או ליצור חישובים בתוך טבלה. Power Pivot מרחיב ומשפר את היכולת המקורית של Excel ליצור צבירה כגון סכומים, ספירות או ממוצעים. באפשרותך ליצור צבירות מותאמות אישית ב- Power Pivot בתוך חלון Power Pivot, או בתוך אזור Excel PivotTable.
-
בטבלה מחושבת עמודה, באפשרותך ליצור צבירה המחזירה בחשבון את הקשר השורה הנוכחי כדי לאחזר שורות קשורות מטבלה אחרת ולאחר מכן לסכם, לספור או לממוצע ערכים אלה בשורות הקשורות.
-
במידה,באפשרותך ליצור צבירה דינאמית המשתמשת בשני המסננים המוגדרים בתוך הנוסחה ומסננים שנכפים על-ידי עיצוב ה- 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, עם כמה הבדלים משניים בתחביר ובטיפול בסוגי נתונים מסוימים.
פונקציות צבירה סטנדרטיות
פונקציה |
שימוש |
החזרת הממוצע (ממוצע אריתמטי) של כל המספרים עמודה. |
|
החזרת הממוצע (ממוצע אריתמטי) של כל הערכים עמודה. מטפל בטקסט ובערכים שאינם מספריים. |
|
ספירת מספר הערכים המספריים עמודה. |
|
ספירת מספר הערכים עמודה ריקים. |
|
החזרת הערך המספרי הגדול ביותר עמודה. |
|
החזרת הערך הגדול ביותר מתוך קבוצת ביטויים המוערכים מעל טבלה. |
|
החזרת הערך המספרי הקטן ביותר עמודה. |
|
החזרת הערך הקטן ביותר מתוך קבוצת ביטויים המוערכים מעל טבלה. |
|
הוספת כל המספרים עמודה. |
פונקציות צבירה של DAX
DAX כולל פונקציות צבירה, אשר יתנו לך לציין טבלה שבה יש לבצע את צבירה. לכן, במקום רק להוסיף או ליצור ממוצע של הערכים ב- עמודה, פונקציות אלה נותנות לך ליצור ביטוי המגדיר באופן דינאמי את הנתונים ל צבירה.
הטבלה הבאה מפרטת את פונקציות צבירה הזמינות ב- DAX.
פונקציה |
שימוש |
ממוצע ערכה של ביטויים המוערכים מעל טבלה. |
|
ספירת קבוצת ביטויים המוערכים מעל טבלה. |
|
ספירת מספר הערכים הריקים עמודה. |
|
ספירת מספר השורות הכולל בטבלה. |
|
ספירת מספר השורות המוחזרים מפונקציית טבלה מקוננת, כגון פונקציית סינון. |
|
הפונקציה מחזירה את הסכום של קבוצת ביטויים המוערכים מעל טבלה. |
הבדלים בין הפונקציות DAX Excel צבירה
למרות שלפונקציות אלה יש שמות זהים לזה של Excel שלהם, הם משתמשים במנוע הניתוח של Power Pivot בזיכרון והם נכתבו מחדש כדי לעבוד עם טבלאות ועמודות. לא ניתן להשתמש בנוסחת DAX בחוברת Excel עבודה, ולהיפך. ניתן להשתמש בהם רק בחלון Power Pivot ובטבלאות PivotTable המבוססות על Power Pivot נתונים. כמו כן, למרות שלפונקציות יש שמות זהים, אופן הפעולה עשוי להיות מעט שונה. לקבלת מידע נוסף, עיין בנושאי העיון הבודדים לפונקציה.
האופן בו עמודות מוערכות בצרגה שונה גם מהאופן Excel מטפל צבירה. דוגמה עשויה לעזור להמחיש.
נניח שברצונך לקבל סכום של הערכים ברשימה סכום עמודה בטבלה Sales, כך שתיצור את הנוסחה הבאה:
=SUM('Sales'[Amount])
במקרה הפשוט ביותר, הפונקציה מקבלת את הערכים מ- עמודה אחד, והתוצאה זהה לתוצאה ב- Excel, אשר תמיד מוסיפה את הערכים ב- עמודה, סכום. עם זאת, Power Pivot, הנוסחה מפורשת כ"קבל את הערך בסכום עבור כל שורה בטבלה Sales ולאחר מכן הוסף ערכים בודדים אלה. Power Pivot הערכה של כל שורה שבה מתבצעת צבירה ומחשבת ערך סקארי יחיד עבור כל שורה ולאחר מכן מבצעת צבירה בערכים אלה. לכן, התוצאה של נוסחה עשויה להיות שונה אם מסננים הוחלו על טבלה, או אם הערכים מחושבים בהתבסס על צבירה אחרת שעשויה להיות מסוננים. לקבלת מידע נוסף, ראה הקשר בנוסחאות DAX.
הפונקציות של בינה זמן של DAX
בנוסף לפונקציות צבירה בטבלה המתוארות בסעיף הקודם, DAX כולל פונקציות צבירה שיפעלו עם תאריכים והזמנים שציינת, כדי לספק בינה זמן מוכללת. פונקציות אלה משתמשות בטווחי תאריכים כדי לקבל ערכים קשורים ול צבירה של הערכים. באפשרותך גם להשוות ערכים בין טווחי תאריכים.
הטבלה הבאה מפרטת את פונקציות בינה הזמן קיימות לצובר.
פונקציה |
שימוש |
חישוב ערך בסוף לוח השנה של התקופה הנתונה. |
|
חישוב ערך בסוף לוח השנה של התקופה לפני התקופה הנתונה. |
|
חישוב ערך לאורך המרווח שמתחיל ביום הראשון של התקופה ומסתיים בתאריך האחרון בתאריך שצוין עמודה. |
הפונקציות האחרות במקטע הפונקציה 'בינה זמן' (פונקציות בינהזמן) הן פונקציות ה יכולות לשמש לאחזור תאריכים או טווחים מותאמים אישית של תאריכים לשימוש צבירה. לדוגמה, באפשרותך להשתמש בפונקציה DATESINPERIOD כדי להחזיר טווח תאריכים ולהשתמש בערכה זו של תאריכים כארגומנט לפונקציה אחרת כדי לחשב צבירה מותאמת אישית עבור תאריכים אלה.