הקשר בנוסחאות DAX

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

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

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

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

הכרת הקשר

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

קיימים סוגים שונים של הקשר: שורה, הקשר שאילתה, והקשר מסנן.

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

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

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

לראש הדף

הקשר שורה

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

לדוגמה, נניח שאתה יוצר עמודה מחושבת, = [Freight] + [מס],

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

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

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

= [Freight] + RELATED('Region'[TaxRate])

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

הקשרי שורה מרובים

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

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

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

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

=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])

לקבלת הנחיות מפורטות בנוגע לנוסחה זו, ראה הפונקציה EARLIER.

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

לראש הדף

הקשר שאילתה

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

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

לדוגמה, נניח שתיצור נוסחה פשוטה זו שמסכמת את הערכים בעמודה Profit של table:=SUM('Sales'[Profit]) המכירות .

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

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

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

לראש הדף

הקשר מסנן

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

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

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

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

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

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

לראש הדף

קביעת הקשר בנוסחאות

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

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

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

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

דוגמאות להקשר בנוסחאות

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

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

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

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

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

לראש הדף

שלמות הקשרים

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

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

שלמות הקשרים וקשרי גומלין ב- Power Pivot

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

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

הכרת המושג 'חבר לא ידוע'

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

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

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

טיפול בערכי Blank לעומת שורה ריקה

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

לראש הדף

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

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

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

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

×