במאמר זה, נבחן את היסודות של יצירת נוסחאות חישוב עבור עמודות מחושבות ומידות גם עבור Power Pivot . אם אתה משתמש חדש ב- DAX, הקפד לבדוק את 'התחלה מהירה': למד את יסודות DAX ב- 30 דקות.
יסודות הנוסחאות
Power Pivot מספק Data Analysis Expressions (DAX) ליצירת חישובים מותאמים אישית בטבלאות Power Pivot ובטבלאות PivotTable של Excel. DAX כולל כמה מהפונקציות המשמשות בנוסחאות של Excel, ופונקציות נוספות שנועדו לעבוד עם נתונים יחסיים ולבצע צבירה דינאמית.
להלן כמה נוסחאות בסיסיות שניתן להשתמש הבאות בעמודה מחושבת:
|
נוסחה |
תיאור |
|
הוספת התאריך של היום בכל שורה בעמודה. |
|
הוספת הערך 3 בכל שורה בעמודה. |
|
מחברת את הערכים באותה שורה של [Column1] ו- [Column2] ומציבה את התוצאות באותה שורה של העמודה המחושבת. |
באפשרותך ליצור Power Pivot עבור עמודות מחושבות כפי שאתה יוצר נוסחאות ב- Microsoft Excel.
השתמש בשלבים הבאים בעת יצירת נוסחה:
-
כל נוסחה חייבת להתחיל בסימן שוויון.
-
באפשרותך להקליד או לבחור שם פונקציה, או להקליד ביטוי.
-
התחל להקליד את האותיות הראשונות של הפונקציה או השם הרצויים, והשלמה אוטומטית תציג רשימה של פונקציות, טבלאות ועמודות זמינות. הקש TAB כדי להוסיף פריט מרשימת ההשלמה האוטומטית לנוסחה.
-
לחץ על לחצן Fx כדי להציג רשימה של פונקציות זמינות. כדי לבחור פונקציה מהרשימה הנפתחת, השתמש במקשי החצים כדי לסמן את הפריט ולאחר מכן לחץ על אישור כדי להוסיף את הפונקציה לנוסחה.
-
ספק את הארגומנטים לפונקציה על-ידי בחירתם מתוך רשימה נפתחת של טבלאות ועמודות אפשריות, או על-ידי הקלדה בערכים או בפונקציה אחרת.
-
בדוק אם קיימות שגיאות תחביר: ודא שכל הסוגריים סגורים ושעמודות, טבלאות וערכים מופניים כראוי.
-
הקש ENTER כדי לקבל את הנוסחה.
הערה: בעמודה מחושבת, ברגע שאתה מקבל את הנוסחה, העמודה מאוכלסת בערכים. מדיד, הקשה על ENTER תחסוך את הגדרת המידה.
Create נוסחה פשוטה
|
כדי ליצור עמודה מחושבת עם נוסחה פשוטה
לאחר מכן, הערכים מאוכלסים בעמודה המחושבת החדשה עבור כל השורות. |
עצות לשימוש בהשלמה אוטומטית
-
באפשרותך להשתמש בהשלמה אוטומטית של נוסחה באמצע נוסחה קיימת עם פונקציות מקוננות. הטקסט מיד לפני נקודת הכניסה משמש להצגת ערכים ברשימה הנפתחת, וכל הטקסט לאחר נקודת הכניסה נשאר ללא שינוי.
-
Power Pivot להוסיף את הסוגריים הסוגריים של הפונקציות או להתאים באופן אוטומטי סוגריים. עליך לוודא שכל פונקציה נכונה בצורה תחבירית או שאין באפשרותך לשמור את הנוסחה או להשתמש בה. Power Pivot כן מסמן סוגריים, כך שיהיה קל יותר לבדוק אם הם סגורים כראוי.
עבודה עם טבלאות ועמודות
Power Pivot טבלאות דומות לטבלאות Excel, אך הן שונות באופן שבו הן פועלות עם נתונים עם נוסחאות:
-
נוסחאות ב- Power Pivot פועלות רק עם טבלאות ועמודות, ולא עם תאים בודדים, הפניות לטווחים או מערכים.
-
נוסחאות יכולות להשתמש בקשרי גומלין כדי לקבל ערכים מטבלאות קשורות. הערכים המאוחזרים קשורים תמיד לערך השורה הנוכחי.
-
לא ניתן להדביק Power Pivot נוסחאות בגליון עבודה של Excel ולהיפך.
-
לא ניתן לכלול נתונים לא סדירים או "משוננים", כפי שאתה עושה בגליון עבודה של Excel. כל שורה בטבלה חייבת להכיל מספר עמודות זהה. עם זאת, ניתן לכלול ערכים ריקים בעמודות מסוימות. טבלאות נתונים של Excel Power Pivot נתונים אינן ניתנות להעברה, אך באפשרותך לקשר לטבלאות Excel מ- Power Pivot ולהדביק נתוני Excel Power Pivot. לקבלת מידע נוסף, ראה הוספת נתוני גליון עבודה למודל נתונים באמצעות טבלה מקושרת והעתקה והדבקה של שורות במודל נתונים ב- Power Pivot.
הפניה לטבלאות ולעמודות בנוסחאות ובביטויים
באפשרותך להפנות לכל טבלה ועמודה באמצעות שמה. לדוגמה, הנוסחה הבאה ממחישה כיצד להפנות לעמודות משתי טבלאות באמצעות השם המלא:
=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
בעת הערכה של נוסחה, Power Pivot בודק תחילה אם יש תחביר כללי ולאחר מכן בודק את שמות העמודות והטבלאות שאתה מספק מול עמודות וטבלאות אפשריות בהקשר הנוכחי. אם השם רב-משמעי או אם העמודה או הטבלה לא נמצאות, תקבל שגיאה בנוסחה (מחרוזת #ERROR במקום ערך נתונים בתאים שבהם מתרחשת השגיאה). לקבלת מידע נוסף אודות דרישות מתן שמות עבור טבלאות, עמודות באובייקטים אחרים, ראה "דרישות מתן שמות במפרט תחביר DAX עבור Power Pivot.
הערה: הקשר הוא תכונה חשובה של Power Pivot נתונים המאפשרים לך ליצור נוסחאות דינאמיות. ההקשר נקבע על-ידי הטבלאות במודל הנתונים, קשרי הגומלין בין הטבלאות ומסננים שהוחלו. לקבלת מידע נוסף, ראה הקשר בנוסחאות DAX.
קשרי גומלין בין טבלאות
טבלאות יכולות להיות קשורות לטבלאות אחרות. על-ידי יצירת קשרי גומלין, אתה יכול לחפש נתונים בטבלה אחרת ולהשתמש בערכים קשורים כדי לבצע חישובים מורכבים. לדוגמה, באפשרותך להשתמש בעמודה מחושבת כדי לחפש את כל רשומות המשלוח הקשורות למשווק הנוכחי ולאחר מכן לסכם את עלויות המשלוח עבור כל אחד מהם. האפקט דומה לשאילתה עם פרמטרים: באפשרותך לחשב סכום שונה עבור כל שורה בטבלה הנוכחית.
פונקציות DAX רבות דורשות שקיימים קשרי גומלין בין הטבלאות, או בין טבלאות מרובות, כדי לאתר את העמודות שהפנית ולהחזיר תוצאות הגיוניות. פונקציות אחרות ינסה לזהות את קשר הגומלין; עם זאת, לקבלת התוצאות הטובות ביותר, תמיד עליך ליצור קשר גומלין כאשר הדבר אפשרי.
בעת עבודה עם טבלאות PivotTable, חשוב במיוחד לחבר את כל הטבלאות המשמשות ב- PivotTable כך שניתן יהיה לחשב את נתוני הסיכום כראוי. לקבלת מידע נוסף, ראה עבודה עם קשרי גומלין בטבלאות PivotTable.
פתרון בעיות של שגיאות בנוסחאות
אם אתה מקבל שגיאה בעת הגדרת עמודה מחושבת, הנוסחה עשויה להכיל שגיאה תחבירית או שגיאה סמנטית.
שגיאות תחביר הן השגיאות הקלות ביותר לפתרון. בדרך כלל הם כוללים סוגריים או פסיק חסרים. לקבלת עזרה עם התחביר של פונקציות בודדות, ראה חומר עזר לפונקציה DAX.
סוג השגיאה האחר מתרחש כאשר התחביר נכון, אך הערך או העמודה שאליהם מתבצעת הפניה אינם הגיוניים בהקשר של הנוסחה. שגיאות סמנטיות כאלה עשויות להיגרם עקב אחת מהבעיות הבאות:
-
הנוסחה מפנה לעמודה, לטבלה או לפונקציה שאינם קיימים.
-
נראה שהנוסחה נכונה, אך כאשר Power Pivot מביא את הנתונים שהיא מוצאת אי-התאמה של סוג, ומעלה שגיאה.
-
הנוסחה מעבירה מספר או סוג פרמטרים שגוי לפונקציה.
-
הנוסחה מפנה לעמודה אחרת המכילה שגיאה, ולכן הערכים שלה אינם חוקיים.
-
הנוסחה מפנה לעמודה שלא עובדה. מצב זה עשוי להתרחש אם שינית את חוברת העבודה למצב ידני, ביצעת שינויים ולאחר מכן מעולם לא רעננת את הנתונים או עדכנת את החישובים.
בארבעת המקרים הראשונים, DAX מסמן בדגל את העמודה כולה המכילה את הנוסחה הלא חוקית. במקרה האחרון, DAX מופיע באפור בעמודה כדי לציין שהעמודה נמצאת במצב לא מ מעבד.
