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

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

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

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

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

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

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

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

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

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

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

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

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

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

פונקציה

שימוש

ממוצע

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

AVERAGEA

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

ספירת

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

COUNTA

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

MAX

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

MAXX

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

MIN

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

MINX

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

סכום

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

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

פונקציות בינת זמן של DAX

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

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

פונקציה

שימוש

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

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

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

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

TOTALMTD

TOTALYTD

TOTALQTD

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

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

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

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

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

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

×