צבירות ב- Power Pivot

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

פונקציה

שימוש

ממוצע

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

AVERAGEA

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

ספירה

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

COUNTA

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

MAX

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

MAXX

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

MIN

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

מרשעת

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

סכום

מוסיף את כל המספרים בעמודה.

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

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

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

פונקציה

שימוש

AVERAGEX

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

COUNTAX

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

COUNTBLANK

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

COUNTX

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

COUNTROWS

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

SUMX

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

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

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

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

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

=SUM('Sales'[Amount])

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

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

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

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

פונקציה

שימוש

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

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

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

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

TOTALMTD

TOTALYTD

TOTALQTD

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

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

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

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

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

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

×