הקשר מאפשר לך לבצע ניתוח דינאמי, שבו התוצאות של נוסחה יכולות להשתנות כדי לשקף את בחירת השורה או התא הנוכחית וגם את הנתונים הקשורים. הבנת הקשר והשימוש בהקשר חשובים מאוד לבניית נוסחאות בביצועים גבוהים, ניתוחים דינאמיים ולפתרון בעיות בנוסחאות.
סעיף זה מגדיר את הסוגים השונים של הקשר: הקשר שורה, הקשר שאילתה והקשר מסנן. הוא מסביר כיצד מוערך הקשר עבור נוסחאות בעמודות מחושבות ובטבלאות 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 יתנו לך לעבור בלולאה בין טבלאות על-ידי ביצוע חישובים, תוך הפניה לערך מהלולאה הפנימית. אם אתה מכיר את הרעיון של רקורסיה עם לולאות פנימיות ו outer, תוכל להעריך את העוצמה שהפונקציות EARLIER ו- EARLIEST מספקות. אם אתה משתמש חדש ברעיונות אלה, עליך לבצע את השלבים בדוגמה בקפידה כדי לראות כיצד נעשה שימוש בהקשרים הפנימיים וחישובים.
שלמות הקשרים
סעיף זה דן בכמה מושגים מתקדמים הקשורים לערכים חסרים בטבלאות Power Pivot המחוברות באמצעות קשרי גומלין. מקטע זה עשוי להיות שימושי עבורך אם יש לך חוברות עבודה עם טבלאות מרובות ונוסחאות מורכבות ואתה מעוניין לעזרה בהבנת התוצאות.
אם אתה חדש ברעיונות של נתונים יחסיים, מומלץ לקרוא תחילה את נושא המבוא, 'קשרי גומלין' מבט כולל.
שלמות הקשרים Power Pivot 'קשרי גומלין'
Power Pivot אינו דורש כי שלמות הקשרים נאכפת בין שתי טבלאות כדי להגדיר קשר גומלין חוקי. במקום זאת, נוצרת שורה ריקה בקצה ה"אחד" של כל קשר גומלין של אחד לרבים ומ משמשת כדי לטפל בכל השורות שאינן תואמות מהטבלה הקשורה. היא מתנהגת ביעילות כצירוף SQL 3.
בטבלאות PivotTable, אם אתה מקיבוץ נתונים לצד אחד של קשר הגומלין, כל הנתונים הבלתי תואמים בצד הרב של קשר הגומלין מקובצים יחד והם ייכללו בסכומים עם כותרת שורה ריקה. הכותרת הריקה שווה בערך ל"חבר לא ידוע".
הבנת החבר הלא ידוע
הרעיון של החבר הלא ידוע מוכר לך ככל הנראה אם עבדת עם מערכות מסד נתונים רב-מימדיות, כגון SQL Server Analysis Services. אם המונח חדש לך, הדוגמה הבאה מסבירה מהו החבר הלא ידוע ואופן ההשפעות שלו על חישובים.
נניח שאתה יוצר חישוב שסיכום מכירות חודשיות עבור כל חנות, עמודה בטבלה Sales חסר ערך עבור שם החנות. בהתחשב בכך שהטבלאות עבור החנותוהמכירות מחוברות לפי שם החנות, מה היית מצפה שיקרה בנוסחה? כיצד יש להציג את קבוצת ה- PivotTable או להציג את נתוני המכירות שלא קשורים לחנות קיימת?
בעיה זו היא בעיה נפוצה במחסני נתונים, שבה טבלאות גדולות של נתוני עובדות חייבות להיות קשורות באופן לוגי לטבלאות ממדים המכילות מידע אודות חנויות, אזורים ותכונות אחרות המשמשות לקטגוריות ול לחישוב עובדות. כדי לפתור את הבעיה, כל העובדות החדשות שלא קשורות לישות קיימת מוקצות באופן זמני לחבר הלא ידוע. לכן עובדות לא קשורות יופיעו מקובצות ב- PivotTable תחת כותרת ריקה.
טיפול בערכים ריקים לעומת השורה הריקה
ערכים ריקים שונים מהשורות הריקות שנוספו כדי להכיל את החבר הלא ידוע. הערך הריק הוא ערך מיוחד המשמש לייצג ערכי Null, מחרוזות ריקות וערכים חסרים אחרים. לקבלת מידע נוסף אודות הערך הריק, כמו גם סוגי נתונים אחרים של DAX, ראה סוגי נתונים במודלי נתונים.
