במאמר זה, נבחן את היסודות של יצירת נוסחאות חישוב עבור עמודות מחושבותומידות ב- Power Pivot. אם אתה משתמש חדש ב- DAX, הקפד לבדוק את ההתחלה המהירה: למד את העקרונות הבסיסיים של DAX ב- 30 דקות.
נוסחאות - עקרונות בסיסיים
Power Pivot מספק Data Analysis Expressions (DAX) ליצירת חישובים מותאמים אישית בטבלאות Power Pivot ובטבלאות PivotTable של Excel. DAX כולל כמה מהפונקציות הנמצאות בשימוש בנוסחאות של Excel, ופונקציות נוספות המיועדות לעבוד עם נתונים יחסיים ולבצע צבירה דינאמית.
להלן כמה נוסחאות בסיסיות שניתן להשתמש בהן בעמודה מחושבת:
| נוסחה | תיאור |
|---|---|
| =TODAY() | מוסיף את התאריך של היום בכל שורה בעמודה. |
| =3 | מוסיף את הערך 3 בכל שורה של העמודה. |
| =[Column1] + [Column2] | חיבור הערכים באותה שורה של [Column1] ו- [Column2] והצבת התוצאות באותה שורה של העמודה המחושבת. |
באפשרותך ליצור נוסחאות Power Pivot עבור עמודות מחושבות כפי שאתה יוצר נוסחאות ב- Microsoft Excel.
השתמש בשלבים הבאים בעת יצירת נוסחה:
- כל נוסחה חייבת להתחיל בסימן שוויון.
- באפשרותך להקליד או לבחור שם פונקציה או להקליד ביטוי.
- התחל להקליד את האותיות הראשונות של הפונקציה או השם הרצויים, וההשלמה האוטומטית תציג רשימה של פונקציות, טבלאות ועמודות זמינות. הקש TAB כדי להוסיף לנוסחה פריט מרשימת ההשלמה האוטומטית.
- לחץ על הלחצן Fx כדי להציג רשימה של פונקציות זמינות. כדי לבחור פונקציה מהרשימה הנפתחת, השתמש במקשי החצים כדי לסמן את הפריט ולאחר מכן לחץ על אישור כדי להוסיף את הפונקציה לנוסחה.
- ספק את הארגומנטים לפונקציה על-ידי בחירתם מרשימה נפתחת של טבלאות ועמודות אפשריות, או על-ידי הקלדת ערכים או פונקציה אחרת.
- בדוק אם קיימות שגיאות תחביר: ודא שכל הסוגריים סגורים ושההפניה לעמודות, טבלאות וערכים מתבצעת כראוי.
- הקש ENTER כדי לקבל את הנוסחה.
הערה
בעמודה מחושבת, ברגע שאתה מקבל את הנוסחה, העמודה מאוכלסת בערכים. במידה, הקשה על ENTER שומרת את הגדרת המידה.
יצירת נוסחה פשוטה
| כדי ליצור עמודה מחושבת עם נוסחה פשוטה SalesDateקטגוריית משנהמוצרמכירותכמות1/5/2009אביזריםתיק נשיאה254995681/5/2009אביזריםמטען סוללות מיני1099.56441/5/2009דיגיטליSlim Digital6512441/6/2009אביזריםעדשת המרת טלפוטו1662.5181/6/2009אביזריםחצובה938.34181/6/2009אביזריםכבל USB1230.2526
|
|---|
עצות לשימוש בהשלמה אוטומטית
- באפשרותך להשתמש בהשלמה אוטומטית של נוסחה באמצע נוסחה קיימת עם פונקציות מקוננות. הטקסט שמופיע מיד לפני נקודת הכניסה משמש להצגת ערכים ברשימה הנפתחת, וכל הטקסט שאחרי נקודת הכניסה נותר ללא שינוי.
- 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 מסמן באפור את העמודה כדי לציין שהעמודה נמצאת במצב לא מעובד.