הקשר מאפשר לך לבצע ניתוח דינאמי, שבו התוצאות של נוסחה יכולות להשתנות כדי לשקף את בחירת השורה או התא הנוכחית וגם את כל הנתונים הקשורים. הבנת הקשר ושימוש בהקשר חשובים מאוד לבניית נוסחאות בעל ביצועים טובים, ניתוחים דינאמיים ולפתרון בעיות בנוסחאות.
מקטע זה מגדיר את סוגי ההקשר השונים: הקשר שורה, הקשר שאילתה והקשר סינון. הוא מסביר כיצד ההקשר מוערך עבור נוסחאות בעמודות מחושבות ובטבלאות PivotTable.
החלק האחרון במאמר זה מספק קישורים בדוגמאות מפורטות שממחישות כיצד התוצאות של נוסחאות משתנות בהתאם להקשר.
הבנת ההקשר
נוסחאות ב- Power Pivot יכולות להיות מושפעות מהמסנן שהוחלו על PivotTable, על-ידי קשרי גומלין בין טבלאות ועל-ידי מסננים המשמשים בנוסחאות. הקשר הוא מה שהופך את ביצוע הניתוח הדינאמי לזמין. הבנת הקשר חשובה לבנייה ולפתרון בעיות של נוסחאות.
קיימים סוגים שונים של הקשר: הקשר שורה, הקשר שאילתה והקשר סינון.
ניתן לחשוב על הקשר שורה כ"השורה הנוכחית". אם יצרת עמודה מחושבת, הקשר השורה מורכב מהערכים בכל שורה וערכים בעמודות הקשורות לשורה הנוכחית. יש גם כמה פונקציות (EARLIER ו- EARLIEST) המ מקבלות ערך מהשורה הנוכחית ולאחר מכן משתמשות בערך זה בעת ביצוע פעולה בטבלה שלמה.
הקשר שאילתה מתייחס לערכת המשנה של הנתונים שנוצרים באופן משתמע עבור כל תא ב- PivotTable, בהתאם לכותרות השורות והעמודות.
הקשר מסנן הוא קבוצת הערכים המותרים בכל עמודה, בהתבסס על אילוצי סינון שהוחלו על השורה או שהוגדרו על-ידי ביטויי מסנן בתוך הנוסחה.
הקשר שורה
אם אתה יוצר נוסחה בעמודה מחושבת, הקשר השורה עבור נוסחה זו כולל את הערכים מכל העמודות בשורה הנוכחית. אם הטבלה קשורה לטבלה אחרת, התוכן כולל גם את כל הערכים מטבלה אחרת זו הקשורים לשורה הנוכחית.
לדוגמה, נניח שאתה יוצר עמודה מחושבת, =[Freight] + [Tax], שמוסיף יחד שתי עמודות מאותה טבלה. נוסחה זו מתנהגת כמו נוסחאות בטבלת Excel, המפנה באופן אוטומטי לערכים מאותה שורה. שים לב שטבלאות שונות מטווחים: אין באפשרותך להפנות לערך מהשורה לפני השורה הנוכחית באמצעות הסימון בטווח, ולא ניתן להפנות לערך בודד שרירותי כלשהו בטבלה או בתא. עליך לעבוד תמיד עם טבלאות ועמודות.
הקשר השורה עוקב באופן אוטומטי אחר קשרי הגומלין בין טבלאות כדי לקבוע אילו שורות בטבלאות קשורות משויכת לשורה הנוכחית.
לדוגמה, הנוסחה הבאה משתמשת בפונקציה RELATED כדי להביא ערך מס מטבלה קשורה, בהתבסס על האזור שבו נשלחה ההזמנה. ערך המס נקבע על-ידי שימוש בערך עבור אזור בטבלה הנוכחית, חיפוש האזור בטבלה הקשורה ולאחר מכן קבלת שיעור המס עבור אזור זה מהטבלה הקשורה.
= [Freight] + RELATED('Region'[TaxRate])
נוסחה זו פשוט מקבלת את שיעור המס עבור האזור הנוכחי, מהטבלה Region. אין צורך לדעת או לציין את המפתח שמחבר את הטבלאות.
הקשר של שורות מרובות
בנוסף, DAX כולל פונקציות האיטרטיביות של חישובים מעל טבלה. פונקציות אלה יכולות לכלול מספר שורות נוכחיות והקשרי שורה נוכחיים. במונחי תיכנות, באפשרותך ליצור נוסחאות חוזרות על לולאה פנימית ופרטית.
לדוגמה, נניח שחוברת העבודה שלך מכילה טבלת Productsוטבלת מכירות. ייתכן שתרצה לעבור על טבלת המכירות כולה, הכוללת עסקאות הכוללות מוצרים מרובים, ולחפש את הכמות הגדולה ביותר שהוזמנו עבור כל מוצר בטרנזקציה אחת.
ב- Excel, חישוב זה דורש סידרה של סיכומי ביניים, שיהיה עליך ליצור מחדש אם הנתונים ישתנו. אם אתה משתמש מצליח של Excel, ייתכן שתוכל לבנות נוסחאות מערך שיבצעו את המשימה. לחלופין, במסד נתונים יחסי, באפשרותך לכתוב בחירת משנה מקוננת.
עם זאת, באמצעות DAX באפשרותך לבנות נוסחה בודדת המחזירה את הערך הנכון, והתוצאות מתעדכנות באופן אוטומטי בכל פעם שאתה מוסיף נתונים לטבלאות.
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
לקבלת הדרכה מפורטת של נוסחה זו, עיין בפונקציה EARLIER.
באופן קצר, הפונקציה EARLIER מאחסן את הקשר השורה מהפעולה שלפני הפעולה הנוכחית. בכל עת, הפונקציה מאחסנת שתי קבוצות של הקשר בזיכרון: ערכה אחת של הקשר מייצגת את השורה הנוכחית עבור הלולאה הפנימית של הנוסחה, וערכה אחרת של הקשר מייצגת את השורה הנוכחית עבור הלולאה העליונה של הנוסחה. DAX מזין באופן אוטומטי ערכים בין שתי הלולאות כדי שתוכל ליצור צבירות מורכבות.
הקשר שאילתה
הקשר שאילתה מפנה אל קבוצת המשנה של הנתונים המאוחזרים באופן משתמע עבור נוסחה. בעת שחרור מדידה או שדה ערך אחר בתא ב- PivotTable, מנגנון Power Pivot בוחן את כותרות השורות והעמודות, כלי הפריסה ומסנני הדוחות כדי לקבוע את ההקשר. לאחר מכן, Power Pivot את החישובים הדרושים כדי לאכלס כל תא ב- PivotTable. ערכת הנתונים המאוחזרת היא הקשר השאילתה עבור כל תא.
מאחר שההקשר יכול להשתנות בהתאם למקום שבו אתה ממקם את הנוסחה, תוצאות הנוסחה משתנות גם הן בהתאם לשאלה אם אתה משתמש בנוסחה ב- PivotTable עם קבץ ומסננים רבים, או בעמודה מחושבת ללא מסננים והקשר מינימלי.
לדוגמה, נניח שאתה יוצר נוסחה פשוטה זו שסיכום הערכים בעמודה רווח בטבלהSales :
=SUM('Sales'[Profit])
אם אתה משתמש בנוסחה זו בעמודה מחושבת בתוך הטבלה Sales , התוצאות של הנוסחה יהיו זהות עבור הטבלה כולה, מכיוון שהקשר השאילתה עבור הנוסחה הוא תמיד ערכת הנתונים כולה של הטבלה Sales . התוצאות שלך יהיו רווח עבור כל האזורים, כל המוצרים, כל השנים וכן הלאה.
עם זאת, בדרך כלל אינך מעוניין לראות את אותה תוצאה מאות פעמים, אך במקום זאת ברצונך לקבל את הרווח עבור שנה מסוימת, מדינה או אזור מסוימים, מוצר מסוים או שילוב כלשהו של אלה, ולאחר מכן לקבל סכום כולל.
ב- PivotTable, קל לשנות הקשר על-ידי הוספה או הסרה של כותרות עמודות ושורות ועל-ידי הוספה או הסרה של כלי פריסה. באפשרותך ליצור נוסחה כמו זו שלעיל, מדידה ולאחר מכן לשחרר אותה ב- PivotTable. בכל פעם שאתה מוסיף כותרות עמודה או שורה ל- PivotTable, אתה משנה את הקשר השאילתה שבו המידה מוערכת. פעולות שכפול וסינון משפיעות גם הן על ההקשר. לכן, אותה נוסחה, המשמשת ב- PivotTable, מוערכת בהקשר שאילתה אחר עבור כל תא.
הקשר מסנן
הקשר מסנן נוסף כאשר אתה מציין אילוצי סינון בערכת הערכים המותרים בעמודה או בטבלה, באמצעות ארגומנטים לנוסחה. הקשר מסנן חל על-גבי הקשרים אחרים, כגון הקשר שורה או הקשר שאילתה.
לדוגמה, PivotTable מחשב את הערכים שלו עבור כל תא בהתבסס על כותרות השורות והעמודות, כמתואר בסעיף הקודם בהקשר שאילתה. עם זאת, בתוך המודדים או העמודות המחושבות שאתה מוסיף ל- PivotTable, באפשרותך לציין ביטויי מסנן כדי לשלוט בערכים המשמשים את הנוסחה. באפשרותך גם לנקות באופן סלקטיבי את המסננים בעמודות מסוימות.
לקבלת מידע נוסף אודות אופן היצירה של מסננים בתוך נוסחאות, עיין בפונקציות הסינון.
לקבלת דוגמה לאופן שבו ניתן לנקות מסננים כדי ליצור סכומים כוללים, עיין בפונקציה ALL.
לקבלת דוגמאות לאופן ניקוי והחלה סלקטיבית של מסננים בתוך נוסחאות, ראה הפונקציה ALLEXCEPT.
לכן, עליך לסקור את ההגדרה של מדידים או נוסחאות המשמשות ב- PivotTable כדי להיות מודע להקשר מסנן בעת פירוש תוצאות הנוסחאות.
קביעת הקשר בנוסחאות
בעת יצירת נוסחה, Power Pivot Excel מחפש תחילה תחביר כללי ולאחר מכן בודק את שמות העמודות והטבלאות שאתה מספק מול עמודות וטבלאות אפשריות בהקשר הנוכחי. אם Power Pivot אין אפשרות למצוא את העמודות והטבלאות שצוינו על-ידי הנוסחה, תקבל שגיאה.
ההקשר נקבע כמתואר בסעיפים הקודמים, באמצעות הטבלאות הזמינות בחוברת העבודה, קשרי הגומלין בין הטבלאות וכל המסננים שהוחלו.
לדוגמה, אם זה עתה ייבאת נתונים מסוימים לטבלה חדשה ולא החלת מסננים כלשהם, כל קבוצת העמודות בטבלה מהווה חלק מההקשר הנוכחי. אם יש לך טבלאות מרובות המקושרות באמצעות קשרי גומלין ואתה עובד ב- PivotTable שמסנן על-ידי הוספת כותרות עמודות והשימוש בכלי פריסה, ההקשר כולל את הטבלאות הקשורות וכל המסננים בנתונים.
הקשר הוא רעיון רב-עוצמה שעלול גם להקל על פתרון בעיות בנוסחאות. מומלץ להתחיל עם נוסחאות וקשרי גומלין פשוטים כדי לראות כיצד ההקשר פועל, ולאחר מכן להתחיל להתנסות בנוסחאות פשוטות בטבלאות PivotTable. הסעיף הבא מספק גם כמה דוגמאות לאופן שבו נוסחאות משתמשות בסוגים שונים של הקשר כדי להחזיר תוצאות באופן דינאמי.
דוגמאות להקשר בנוסחאות
-
הפונקציה RELATED מרחיבה את ההקשר של השורה הנוכחית כדי לכלול ערכים בעמודה קשורה. הדבר מאפשר לך לבצע בדיקות מידע. הדוגמה בנושא זה ממחישה את האינטראקציה של סינון ושורות.
-
הפונקציה FILTER מאפשרת לך לציין את השורות שייכללו בהקשר הנוכחי. הדוגמאות בנושא זה ממחישות גם כיצד להטביע מסננים בתוך פונקציות אחרות שמבצעות צבירה.
-
הפונקציה ALL מגדירה הקשר בתוך נוסחה. באפשרותך להשתמש בו כדי לעקוף מסננים המוחלים כתוצאה מהקשר שאילתה.
-
הפונקציה ALLEXCEPT מאפשרת לך להסיר את כל המסננים פרט לאחד שתציין. שני הנושאים כוללים דוגמאות שמנחה אותך לאורך בניית נוסחאות והבנת הקשרים מורכבים.
-
הפונקציות EARLIER ו- EARLIEST מאפשרות לך לעבור בלולאה בין טבלאות על-ידי ביצוע חישובים, תוך הפניה לערך בלולאה פנימית. אם אתה מכיר את המושג של רקורסיה ולולאות פנימיות ופרטיות, תעריך את העוצמה שהפונקציות EARLIER ו- EARLIEST מספקות. אם אתה משתמש חדש ומושגים אלה, עליך לבצע את השלבים בדוגמה בקפידה כדי לראות כיצד נעשה שימוש בהקשרים הפנימיים וחישובים.
שלמות הקשרים
סעיף זה דן בכמה מושגים מתקדמים הקשורים לערכים חסרים בטבלאות Power Pivot המחוברות באמצעות קשרי גומלין. מקטע זה עשוי להיות שימושי עבורך אם יש לך חוברות עבודה עם טבלאות מרובות ונוסחאות מורכבות וברצונך לקבל עזרה בהבנת התוצאות.
אם אתה חדש ומושגי נתונים יחסיים, מומלץ לקרוא תחילה את נושא המבוא, מבט כולל על קשרי גומלין.
שלמות הקשרים וקשרי גומלין Power Pivot קשר
Power Pivot אינה דורשת אכיפת שלמות הקשרים בין שתי טבלאות כדי להגדיר קשר גומלין חוקי. במקום זאת, נוצרת שורה ריקה בקצה ה"אחד" של כל קשר גומלין של אחד לרבים ומשמשת לטיפול בכל השורות שאינן תואמות מהטבלה הקשורה. הוא פועל באופן יעיל כצירוף חיצוני של SQL.
בטבלאות PivotTable, אם אתה מקיבוץ נתונים לצד אחד של קשר הגומלין, כל הנתונים הלא תואמים בצד הרבים של קשר הגומלין מקובצים יחד וכלולים בסרגלים הכוללים כותרת שורה ריקה. הכותרת הריקה שווה ערך בערך ל"חבר לא ידוע".
הכרת החבר הלא ידוע
הרעיון של החבר הלא ידוע מוכר לך ככל הנראה אם עבדת עם מערכות מסדי נתונים רב-ממדיות, כגון SQL Server Analysis Services. אם המונח חדש לך, הדוגמה הבאה מסבירה מהו החבר הלא ידוע וכיצד הוא משפיע על חישובים.
נניח שאתה יוצר חישוב שסיכום מכירות חודשי עבור כל מאגר, אך בעמודה בטבלה Sales חסר ערך עבור שם החנות. בהתחשב בכך שהטבלאות עבור החנותוהמכירות מחוברות לפי שם החנות, מה היית מצפה שיקרה בנוסחה? כיצד יש להציג את קבוצת ה- PivotTable או את נתוני המכירות שאינם קשורים לחנות קיימת?
בעיה זו היא אחת נפוצה במחסנים של נתונים, שבה טבלאות גדולות של עובדות חייבות להיות קשורות מבחינה לוגית לטבלאות ממד המכילות מידע אודות חנויות, אזורים ותכונות אחרות המשמשות לחלוקה לקטגוריות ולחישוב עובדות. כדי לפתור את הבעיה, כל העובדות החדשות שאינם קשורים לישות קיימת מוקצות באופן זמני לחבר הלא ידוע. לכן עובדות לא קשורות יופיעו מקובצות ב- PivotTable תחת כותרת ריקה.
טיפול בערכים ריקים לעומת שורה ריקה
ערכים ריקים שונים מהשורות הריקות שנוספות כדי להכיל את החבר הלא ידוע. הערך הריק הוא ערך מיוחד המשמש לייצוג ערכי Null, מחרוזות ריקות וערכים חסרים אחרים. לקבלת מידע נוסף אודות הערך הריק, וכן סוגי נתונים אחרים של DAX, ראה סוגי נתונים במודלים של נתונים.
