תרחישים של DAX ב- Power Pivot

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

סעיף זה מספק קישורים לדוגמאות שמדגימות את השימוש בנוסחאות DAX בתרחישים הבאים.

  • ביצוע חישובים מורכבים

  • עבודה עם טקסט ותאריכים

  • ערכים מותנים ובדיקה אם יש שגיאות

  • שימוש בבינת זמן

  • דירוג והשוואה של ערכים

במאמר זה

תחילת העבודה

נתונים לדוגמה

משאבים נוספים

תרחישים: ביצוע חישובים מורכבים

יצירת חישובים מותאמים אישית עבור PivotTable

החלת מסנן על נוסחה

הסרה סלקטיבית של מסננים ליצירת יחס דינאמי

שימוש בערך מלולאה חיצונית

תרחישים: עבודה עם טקסט ותאריכים

יצירת עמודת מפתח על-ידי שרשור

יצירת תאריך בהתבסס על חלקי טקסט שחולצו מתאריך טקסט

הגדרת תאריך מותאם אישית או תבנית מספר

שינוי סוגי נתונים באמצעות נוסחה

תרחיש: ערכים מותנים ובדיקה אם יש שגיאות

יצירת ערך המבוסס על תנאי

בדיקה אם יש שגיאות בנוסחה

תרחישים: שימוש בבינת זמן

חישוב מכירות מצטברות

השוואת ערכים לאורך זמן

חישוב ערך בטווח תאריכים מותאם אישית

תרחישים: דירוג והשוואה של ערכים

הצגת עשרת הפריטים העליונים בלבד ב- PivotTable

סידור דינאמי של פריטים באמצעות נוסחה

תחילת העבודה

נתונים לדוגמה

אם אין לך ניסיון בעבודה עם נוסחאות DAX, מומלץ להתחיל לסקור את הדוגמאות בנתונים לדוגמה עבור Power Pivot. לקבלת מידע נוסף, ראה קבלת נתונים לדוגמה עבור ערכות הלימוד של DAX ושל מודל נתונים.

משאבים נוספים

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

תרחישים: ביצוע חישובים מורכבים

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

יצירת חישובים מותאמים אישית עבור PivotTable

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

החלת מסנן על נוסחה

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

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

הסרה סלקטיבית של מסננים ליצירת יחס דינאמי

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

  • מה היתה התרומה של מכירות המוצר הנוכחי לסה"כ המכירות השנה?

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

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

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

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

שימוש בערך מלולאה חיצונית

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

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

תרחישים: עבודה עם טקסט ותאריכים

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

יצירת עמודת מפתח על-ידי שרשור

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

יצירת תאריך בהתבסס על חלקי טקסט שחולצו מתאריך טקסט

Power Pivot משתמש בסוג הנתונים תאריך/שעה של SQL Server כדי לעבוד עם תאריכים; לכן, אם הנתונים החיצוניים מכילים תאריכים המעוצבים אחרת - לדוגמה, אם התאריכים כתובים בתבנית תאריך אזורית שאינה מזוהה על-ידי מנוע הנתונים של Power Pivot, או אם הנתונים משתמשים במפתחות חלופיים שלמים - ייתכן שיהיה עליך להשתמש בנוסחה של DAX כדי לחלץ את חלקי התאריך ולאחר מכן לחבר את החלקים לייצוג חוקי של תאריך/שעה.

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

=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))

Value1

תוצאה

01032009

1/3/2009

12132008

12/13/2008

06252007

6/25/2007

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

הגדרת תאריך מותאם אישית או תבנית מספר

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

שינוי סוגי נתונים באמצעות נוסחה

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

  • כדי להמיר תאריך או מחרוזת מספרים למספר, הכפל אותם ב- 1.0. לדוגמה, הנוסחה הבאה מחשבת את התאריך הנוכחי פחות 3 ימים, ולאחר מכן מחזירה את הערך השלם המתאים.

    = (TODAY ()-3) * 1.0

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

    = "" & Today)

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

המרת מספרים ממשיים למספרים שלמים

תרחיש: ערכים מותנים ובדיקה אם יש שגיאות

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

יצירת ערך המבוסס על תנאי

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

בדיקה אם יש שגיאות בנוסחה

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

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

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

תרחישים: שימוש בבינת זמן

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

לקבלת רשימה של כל פונקציות בינת הזמן, ראה פונקציות בינת זמן (DAX). לקבלת עצות לשימוש יעיל בתאריכים ובשעות בניתוח של Power Pivot, ראה תאריכים ב- Power Pivot.

חישוב מכירות מצטברות

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

השוואת ערכים לאורך זמן

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

חישוב ערך בטווח תאריכים מותאם אישית

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

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

  • הפונקציה PARALLELPERIOD

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

תרחישים: דירוג והשוואה של ערכים

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

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

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

לכל שיטה יש יתרונות וחסרונות.

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

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

הצגת עשרת הפריטים העליונים בלבד ב- PivotTable

כדי להציג את הערכים העליונים או התחתונים ב- PivotTable

  1. ב- PivotTable, לחץ על החץ למטה בכותרת תוויות שורה.

  2. בחר מסנני ערכים> 10 העליונים.

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

    1. בחר עליונים כדי לראות את התאים עם הערכים הגבוהים ביותר, או בחר תחתונים כדי לראות את התאים עם הערכים הנמוכים ביותר.

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

    3. בחר את האופן שבו ברצונך להציג ערכים:

שם

תיאור

פריטים

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

אחוז

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

סכום

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

  1. בחר את העמודה שמכילה את הערכים שברצונך לדרג.

  2. לחץ על אישור.

סידור דינאמי של פריטים באמצעות נוסחה

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

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

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

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

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

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

×