צבירות ב- Power Pivot

חשוב: מאמר זה תורגם בתרגום מכונה, ראה כתב ויתור. תוכל למצוא את הגרסה באנגלית של המאמר כאן לעיונך.

צבירות מציעות דרך לכיווץ, סיכום או קיבוץ נתונים. כאשר תתחיל עם נתונים גולמיים מטבלאות או מקורות נתונים אחרים, הנתונים יהיו בדרך כלל שטוחים, כלומר קיימים פרטים רבים, אך הם לא מאורגנים או מקובצים בדרך כלשהי. העדר המבנה או הסיכומים עלול להקשות על גילוי דפוסים של הנתונים. חלק חשוב ביצירת מודלים של נתונים הוא להגדיר צבירות שמפשטות, מבהירות או מסכמות את הדפוסים כתשובה לשאלה עסקית ספציפית.

צבירות הנפוצות ביותר, כגון כאלה המשתמשים ממוצע, ספירת, DISTINCTCOUNT, MAX, MINאו SUM שניתן ליצור מידה באופן אוטומטי על-ידי שימוש בסכום אוטומטי. סוגים אחרים של צבירות, כגון AVERAGEX, COUNTX, COUNTROWSאו SUMXלהחזיר טבלה ודרוש נוסחה שנוצרו באמצעות Data Analysis Expressions (DAX).

הכרת הצבירות ב- Power Pivot

בחירת קבוצות לצבירה

כאשר תצבור נתונים, תקבץ את הנתונים לפי תכונות כגון מוצר, מחיר, אזור או תאריך, ולאחר מכן תגדיר נוסחה שפועלת על כל הנתונים בקבוצה. לדוגמה, כאשר תיצור סכום כולל במשך שנה, תיצור צבירה. אם לאחר מכן תיצור יחס בין שנה זו לשנה הקודמת ותציג אחוזים אלה, סוג הצבירה יהיה שונה.

ההחלטה כיצד לקבץ את הנתונים מתקבלת על-סמך השאלה העסקית. לדוגמה, צבירות יכולות להשיב על השאלות הבאות:

ספירות   כמה עסקאות בוצעו בחודש?

ממוצעים    מה היה ממוצע המכירות בחודש זה לכל איש מכירות?

ערכי מינימום ומקסימום    אילו מחוזות שבהם נערכו מכירות הם חמשת המחוזות המובילים מבחינת יחידות שנמכרו?

כדי ליצור חישוב שעונה על שאלות אלה, דרושים לך נתונים מפורטים שמכילים את המספרים שיש לספור או לסכם, ונתונים מספריים אלה חייבים להיות קשורים באותה דרך לקבוצות שבהן תשתמש כדי לארגן את התוצאות.

אם הנתונים אינה מכילה כבר הערכים שבהם באפשרותך להשתמש לצורך קיבוץ, כגון קטגוריית מוצר או השם לאזור גיאוגרפי שבו ממוקם לחנות, ייתכן שתרצה להציג קבוצות לנתונים שלך על-ידי הוספת קטגוריות. בעת בניית קבוצות ב- Excel, עליך באופן ידני הקלד או בחר את הקבוצות שאליהן ברצונך להשתמש מבין העמודות בגליון העבודה שלך. עם זאת, במערכת יחסית, הירארכיות כגון קטגוריות עבור מוצרי לעתים קרובות מאוחסנים בטבלה אחרת מאשר העובדה או ערך בטבלה. בדרך כלל הטבלה קטגוריה מקושר לנתונים fact לפי סוג כלשהו של מפתח. לדוגמה, נניח שתמצא את הנתונים שלך מכילים מזהי מוצר, אך לא את שמות המוצרים או קטגוריות שלהם. כדי להוסיף את הקטגוריה גליון עבודה של Excel שטוחה, בדרך כלל עליך להעתיק בעמודה שהכיל את שמות הקטגוריות. עם Power Pivot, באפשרותך לייבא את טבלת קטגוריית המוצר למודל הנתונים שלך, יצירת קשר גומלין בין הטבלה המכילה את הנתונים מספר הרשימה קטגוריה המוצר ולאחר מכן השתמש בקטגוריות כדי לקבץ נתונים. לקבלת מידע נוסף, ראה יצירת קשר גומלין בין טבלאות.

בחירת פונקציה לצבירה

לאחר שזיהית והוספת את הקיבוצים שבהם ברצונך להשתמש, עליך להחליט באילו פונקציות מתמטיות להשתמש לצבירה. לעתים קרובות המילים 'צבירה' משמשת כמילה נרדפת לפעולה מתמטית או סטטיסטית שנמצאת בשימוש בצבירות, כגון סכומים, ממוצעים, ערכי מינימום או ספירות. עם זאת, Power Pivot מאפשר לך ליצור נוסחאות מותאמות אישית לצבירה, בנוסף לצבירות הסטנדרטיות שנמצאות ב- Power Pivot וב- Excel.

לדוגמה, בהינתן אותה קבוצת ערכים וקיבוצים שבהם נעשה שימוש בדוגמאות הקודמות, תוכל ליצור צבירות מותאמות אישית שמספקות תשובות לשאלות הבאות:

ספירות מסוננות   כמה עסקאות בוצעו בחודש, לא כולל חלון תחזוקת סוף החודש?

חישובי יחס המשתמשים בממוצעים לאורך זמן    מה היה אחוז הגידול או הירידה במכירות בהשוואה לאותה תקופה בשנה שעברה?

ערכי מינימום ומקסימום מקובצים    אילו מחוזות מכירות דורגו כמובילים עבור כל קטגוריית מוצר, או עבור כל מבצע קידום מכירות?

הוספת צבירות לנוסחאות וטבלאות PivotTable

כאשר יש לך רעיון כללי לגבי האופן שבו יש לקבץ את הנתונים כדי להעניק להם משמעות, ולגבי הערכים שאתם ברצונך לעבוד, תוכל להחליט אם לבנות PivotTable או ליצור חישובים בתוך טבלה. Power Pivot מרחיב ומשפר את היכולת המקורית של Excel ליצירת צבירות כגון סכומים, ספירות או ממוצעים. באפשרותך ליצור צבירות מותאמות אישית ב- Power Pivot בתוך חלון Power Pivot או בתוך אזור ה- PivotTable של Excel.

  • בעמודה מחושבת, באפשרותך ליצור צבירות שלוקחות בחשבון את הקשר השורה הנוכחית כדי לאחזר שורות קשורות מטבלה אחרת, ולאחר מכן לסכם, לספור או לחשב ממוצע של ערכים אלה בשורות הקשורות.

  • מידה, תוכל ליצור צבירות דינאמיים המשתמשות הן מסננים המוגדרים שבתוך הנוסחה ומסננים שנכפו על-ידי העיצוב של PivotTable ואת הבחירה של כלי פריסה, כותרות עמודות וכותרות שורה. ניתן ליצור מידות באמצעות צבירות רגיל ב- Power Pivot באמצעות סכום אוטומטי או על-ידי יצירת נוסחה. באפשרותך גם ליצור מידות משתמע באמצעות צבירות רגיל ב- PivotTable ב- Excel.

הוספת קיבוצים לטבלת PivotTable

בעת תכנון PivotTable, עליך לגרור שדות שמייצגים קיבוצים, קטגוריות או הירארכיות למקטע העמודות והשורות של ה- PivotTable כדי לקבץ את הנתונים. לאחר מכן עליך לגרור שדות שמכילים ערכים מספריים לאזור הערכים, כך שניתן יהיה לספר אותם, לחשב ממוצע שלהם או לסכם אותם.

אם תוסיף קטגוריות ל- PivotTable, אך נתוני הקטגוריות לא יהיו קשורים לנתוני העובדות, ייתכן שתקבל שגיאה או תוצאות מוזרות. בדרך כלל Power Pivot ינסה לפתור את הבעיה על-ידי זיהוי אוטומטי והצעת קשרי גומלין. לקבלת מידע נוסף, ראה עבודה עם קשרי גומלין בטבלאות PivotTbale.

באפשרותך גם לגרור שדות לכלי פריסה, כדי לבחור קיבוצים מסוימים של נתונים לתצוגה. כלי פריסה מאפשרים לקבץ, למיין ולסנן באופן אינטראקטיבי את התוצאות ב- PivotTable.

עבודה עם קיבוצים בנוסחה

באפשרותך גם להשתמש בקיבוצים וקטגוריות כדי לצבור נתונים המאוחסנים בטבלאות על-ידי יצירת קשרי גומלין בין טבלאות, ולאחר מכן יצירת נוסחאות שממנפות קשרי גומלין אלה לחיפוש ערכים קשורים.

במילים אחרות, אם ברצונך ליצור נוסחה שמקבצת ערכים לפי קטגוריה, השתמש תחילה בקשר גומלין כדי לחבר בין הטבלה שמכילה את נתוני הפרטים לבין הטבלאות שמכילות את הקטגוריות, ולאחר מכן בנה את הנוסחה.

לקבלת מידע נוסף אודות אופן היצירה של נוסחאות המשתמשות בבדיקות מידע, ראה בדיקות מידע בנוסחאות של PowerPivot.

שימוש במסננים וצבירות

תכונה חדשה ב- Power Pivot היא היכולת להחיל מסנני עמודות וטבלאות של נתונים, לא רק בממשק המשתמש ובתוך PivotTable או תרשים, אלא גם בנוסחאות מאוד המשמש לחישוב צבירות. ניתן להשתמש במסננים בנוסחאות בעמודות מחושבות והן ב- s.

לדוגמה, בפונקציות הצבירה החדשות של DAX, במקום לציין ערכים שייכללו בסיכום או בספירה, תוכל לציין טבלה שלמה כארגומנט. אם לא החלת מסננים כלשהם על טבלה זו, פונקציית הצבירה תפעל על כל הערכים בעמודת הטבלה שצוינה. עם זאת, ב- DAX תוכל ליצור מסנן דינאמי או סטטי בטבלה, כך שהצבירה תפעל כנגד ערכת משנה אחרת של נתונים, בהתאם לתנאי המסנן ולהקשר הנוכחי.

על-ידי שילוב של תנאים ומסננים בנוסחאות, תוכל ליצור צבירות שמשתנות בהתאם לערכים המסופקים בנוסחאות, או משתנות בהתאם לבחירה של כותרות שורות וכותרות עמודות ב- PivotTable.

לקבלת מידע נוסף, ראה סינון נתונים בנוסחאות.

השוואה בין פונקציות צבירה של Excel לפונקציות צבירה של DAX

הטבלה הבאה מפרטת חלק מפונקציות הצבירה הסטנדרטיות המסופקות על-ידי Excel, ומספקת קישורים להטמעה של פונקציות אלה ב- Power Pivot. גירסת DAX של פונקציות אלה דומה במידה רבה לגירסת Excel, פרט לכמה הבדלים מינוריים בתחביר ובטיפול בסוגי נתונים מסוימים.

פונקציות צבירה סטנדרטיות

פונקציה

שימוש

AVERAGE

פונקציה זו מחזירה את הממוצע (ממוצע חשבוני) של כל המספרים בעמודה.

AVERAGEA

פונקציה זו מחזירה את הממוצע (ממוצע חשבוני) של כל הערכים בעמודה. מטפלת בערכי טקסט וערכים לא מספריים.

COUNT

פונקציה זו סופרת את מספר הערכים המספריים בעמודה.

COUNTA

פונקציה זו סופרת את מספר הערכים בעמודה שאינם ריקים.

MAX

פונקציה זו מחזירה את הערך המספרי הגדול ביותר בעמודה.

MAXX

פונקציה זו מחזירה את הערך הגדול ביותר מקבוצת ביטויים המוערכים על טבלה.

MIN

פונקציה זו מחזירה את הערך המספרי הקטן ביותר בעמודה.

MINX

פונקציה זו מחזירה את הערך הקטן ביותר מקבוצת ביטויים המוערכים על טבלה.

SUM

פונקציה זו מחברת את כל המספרים בעמודה.

פונקציות צבירה של DAX

DAX כולל פונקציות צבירה שמאפשרות לציין את שם הטבלה, או ביטוי שמציין את הטבלה שבה יש לבצע את הצבירה. לכן, במקום רק לסכם או לחשב ממוצע של הערכים בטבלה, פונקציות אלה מאפשרות ליצור ביטוי שמגדיר באופן דינאמי את הנתונים לצבירה.

הטבלה הבאה מפרטת את פונקציות הצבירה הזמינות ב- DAX.

פונקציה

שימוש

AVERAGEX

פונקציה זו מחשבת ממוצע של קבוצת ביטויים המוערכים על טבלה.

COUNTAX

פונקציה זו סופרת קבוצת ביטויים המוערכים על טבלה.

COUNTBLANK

פונקציה זו סופרת את מספר הערכים הריקים בעמודה.

COUNTX

ספירת מספר השורות הכולל בטבלה.

COUNTROWS

פונקציה זו סופרת את מספר השורות המוחזר מפונקציית טבלה מקוננת, כגון פונקציית מסנן.

SUMX

פונקציה זו מחזירה את הסכום של קבוצת ביטויים המוערכים על טבלה.

ההבדלים בין פונקציות צבירה של DAX ושל Excel

אף על פי פונקציות אלה יש שם זהה המקבילים Excel, הם לנצל Power Pivot של מנגנון analytics בזיכרון ולאחר נכתבו כדי לעבוד עם טבלאות ועמודות. לא ניתן להשתמש נוסחת 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

CLOSINGBALANCEYEAR

פונקציה זו מחשבת ערך בסוף לוח השנה של התקופה הנתונה.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

פונקציה זו מחשבת ערך בסוף לוח השנה של התקופה שלפני התקופה הנתונה.

TOTALMTD

TOTALYTD

TOTALQTD

פונקציה זו מחשבת ערך במרווח הזמן שמתחיל ביום הראשון של התקופה ומסתיים בתאריך האחרון בעמודת התאריך שצוינה.

הפונקציות האחרות בסעיף של פונקציות בינת הזמן (Time Intelligence Functions) הן פונקציות שניתן להשתמש בהן לאחזור תאריכים או טווחי תאריכים מותאמים אישית לשימוש בצבירה. לדוגמה, באפשרותך להשתמש בפונקציה DATESINPERIOD כדי להחזיר טווח תאריכים, ולהשתמש בקבוצת תאריכים זו כארגומנט לפונקציה אחרת לחישוב צבירה מותאמת אישית עבור תאריכים אלה בלבד.

הערה: כתב ויתור בנוגע לתרגום מכונה: מאמר זה תורגם על-ידי מערכת מחשב, ללא התערבות אדם. Microsoft מציעה את תרגומי המכונה כדי לסייע למשתמשים שאינם דוברי אנגלית ליהנות מתוכן בנושא מוצרים, שירותים וטכנולוגיות של Microsoft. מכיוון שהמאמר תורגם על-ידי מכונה, הוא עלול להכיל שגיאות באוצר המילים, בתחביר או בדקדוק.

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×