עולם ה־Excel וה־Power BI לא מסתכם רק בטבלאות, תרשימים ומדדים. מאחורי הקלעים מסתתרות שלוש שפות שונות – לכל אחת תפקיד משלה, גישה ייחודית ותחום אחריות ברור: M, DAX ו־VBA. במאמר הזה נעשה סדר: מהי כל שפה, איפה משתמשים בה, למה, ואפילו נביא דוגמאות.
🔹 שפת M – Power Query Formula Language
מה זה:
שפת M (קיצור של Mashup) היא השפה שמריצה את Power Query – מנוע שליפה, ניקוי והכנת נתונים שקיים גם ב־Excel וגם ב־Power BI.
איפה משתמשים:
- חלון Power Query ב־Excel
- Power BI Desktop (בשלב טעינת הנתונים)
- Dataflows בענן
למה משתמשים:
כדי לחבר מקורות מידע שונים (Excel, SQL, CSV, API), לנקות נתונים, לשנות טיפוסים ולבנות בסיס מסודר לניתוח.
דוגמה:
- חיבור ל־API של בנק ישראל כדי למשוך שערי מט"ח
- הסרת עמודות מיותרות
- מיזוג שתי טבלאות שונות לטבלה אחת נקייה
🔹 שפת DAX – Data Analysis Expressions
מה זה:
שפת נוסחאות לחישובים וניתוחים על מודל הנתונים. היא מזכירה נוסחאות Excel, אבל נבנתה במיוחד לעבודה עם מודלים מרובי־טבלאות.
איפה משתמשים:
- Power BI (במודל הנתונים)
- Power Pivot בתוך Excel
למה משתמשים:
כדי ליצור מדדים, חישובי סיכום וניתוחי זמן (Time Intelligence). זו השפה שנותנת לנו לענות על שאלות עסקיות ישירות מהמודל.
דוגמה:
- חישוב מכירות מצטברות מתחילת השנה (Year-to-Date)
- השוואה בין חודש נוכחי לחודש מקביל בשנה קודמת
- בניית KPI שמציג אחוז עמידה ביעד
🔹 שפת VBA – Visual Basic for Applications
מה זה:
שפת תכנות ותיקה של מיקרוסופט, שמאפשרת לכתוב מאקרו ולאוטומט תהליכים בתוך Excel (וגם Word, Outlook ושאר חבילת Office).
איפה משתמשים:
- קבצי Excel מקומיים
- כלים פנים־ארגוניים שדורשים אוטומציה בהתנהגות הגיליון
למה משתמשים:
כדי להפעיל אוטומציה מתקדמת, להוסיף ממשקי משתמש (UserForms), לשלוח מיילים אוטומטיים, לייצר PDF בלחיצת כפתור, ועוד.
דוגמה:
- כפתור באקסל ששולח דוח יומי במייל למנהל
- יצירת חלון קלט למשתמש (UserForm) להזנת נתונים
- הפקת דוחות חודשיים בלחיצה אחת
🧩 איך זה מתחבר ביחד?
אפשר לדמיין את זה כשרשרת:
- M – מכין את הנתונים, מנקה ומסדר.
- DAX – מחשב ומנתח על בסיס הנתונים.
- VBA – מוסיף שליטה, אינטראקציה ואוטומציה למשתמש.
ביחד – שלוש השפות נותנות לאנליסט/מפתח BI שליטה מלאה על כל התהליך: מהנתונים הגולמיים ועד לדוחות אינטראקטיביים ואוטומטיים.
✍️ לסיכום:
אם אתם עובדים באקסל או ב־Power BI – סביר מאוד שכבר פגשתם לפחות אחת מהשפות האלו. כדאי לדעת לא רק איך לעבוד איתן, אלא גם מתי להשתמש בכל אחת:
- M לניקוי והכנה,
- DAX לניתוח וחישובים,
- VBA לאוטומציה והתאמה אישית.
דוגמאות קוד קצרות לכל שפה, עם הסבר בדיוק מה כל קטע עושה ואיפה משתמשים בו.
M (Power Query)
דוגמה 1 – טעינת CSV, קידום כותרות וסינון שורות
מה עושה: טוען קובץ CSV, מקדם את השורה הראשונה לכותרות, מסנן רק שורות שהסכום מעל 100.
let
Source = Csv.Document(
File.Contents("C:\Data\sales.csv"),
[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]
),
Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
ChangedTypes = Table.TransformColumnTypes(Promoted, {{"Date", type date}, {"Amount", type number}}),
Filtered = Table.SelectRows(ChangedTypes, each [Amount] > 100)
in
Filtered
איפה שמים: Power Query → New Source → Blank Query → Advanced Editor.
דוגמה 2 – קריאה ל־API (בנק ישראל), טווח עד היום
מה עושה: מושך שערים היסטוריים רשמיים (OF00) מול ILS בפורמט CSV, הופך לעמודות נקיות.
let
TodayText = Date.ToText(Date.From(DateTime.LocalNow()), "yyyy-MM-dd"),
Url = "https://edge.boi.gov.il/FusionEdgeServer/sdmx/v2/data/dataflow/BOI.STATISTICS/EXR/1.0/?"
& "c%5BDATA_TYPE%5D=OF00&c%5BQUOTE_CURRENCY%5D=ILS"
& "&startperiod=1900-01-01&endperiod=" & TodayText
& "&format=csv&Labels=both&bom=include&locale=he",
CsvBin = Web.Contents(Url),
CsvTbl = Csv.Document(CsvBin, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(CsvTbl, [PromoteAllScalars=true]),
Renamed = Table.RenameColumns(Promoted, {{"TIME_PERIOD","Date"}, {"OBS_VALUE","Rate"}, {"BASE_CURRENCY","BaseCurrency"}}, MissingField.Ignore),
Typed = Table.TransformColumnTypes(Renamed, {{"Date", type date}, {"Rate", type number}, {"BaseCurrency", type text}})
in
Typed
איפה שמים: Power Query → New Source → Blank Query.
DAX (Power BI / Power Pivot)
נניח שיש טבלת fact בשם Sales עם עמודות: Amount, Date, ועמודת מפתח ל־Date table.
את הקוד מוסיפים בחלון Modeling → New Measure.
1) סכום מכירות כולל
Total Sales :=
SUM ( Sales[Amount] )
מה עושה: יוצר מדד בסיסי שמסכם את Amount (מכירות).
2) מכירות מתחילת שנה (YTD)
Total Sales YTD :=
TOTALYTD ( [Total Sales], 'Date'[Date] )
מה עושה: צובר את המכירות מתחילת השנה עד התאריך המסונן.
3) מכירות שנה קודמת (Same Period Last Year)
Total Sales LY :=
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
מה עושה: מחשב את אותו טווח תאריכים אך עבור השנה הקודמת.
4) אחוז שינוי משנה קודמת (YoY %)
YoY % :=
VAR Prev = [Total Sales LY]
RETURN
IF ( NOT ISBLANK(Prev), DIVIDE ( [Total Sales] - Prev, Prev ) )
מה עושה: מחשב שינוי יחסי לעומת השנה הקודמת, עם הגנה מחלוקה באפס.
VBA (Excel)
דוגמה – ייצוא הגיליון הפעיל ל-PDF בשם אוטומטי
מה עושה: שומר את הגיליון הפעיל כ-PDF בתיקייה של הקובץ, עם חותמת תאריך-שעה.
Sub ExportActiveSheetToPDF()
Dim p As String, fname As String
If Len(ThisWorkbook.Path) = 0 Then
MsgBox "שמור את הקובץ לפני הייצוא.", vbExclamation: Exit Sub
End If
p = ThisWorkbook.Path & Application.PathSeparator
fname = ActiveSheet.Name & "_" & Format(Now, "yyyy-mm-dd_hh.nn") & ".pdf"
On Error GoTo ErrH
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=p & fname, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "נשמר: " & vbCrLf & p & fname, vbInformation
Exit Sub
ErrH:
MsgBox "שגיאה בשמירה ל-PDF: " & Err.Description, vbCritical
End Sub
איפה שמים: Excel → ALT+F11 → Insert → Module → הדבק → להריץ עם F5 או לקשר לכפתור.
מתי להשתמש במה?
- M – כשצריך להביא, לנקות ולארגן נתונים לפני טעינה.
- DAX – כשצריך לחשב מדדים וניתוחים על המודל (YTD, YoY, KPI).
- VBA – כשצריך אוטומציה בממשק אקסל: כפתורים, טפסים, יצוא, שליחה, ועוד.
| שפה | איפה משתמשים בה | תפקיד עיקרי | דוגמאות לשימוש | דמיון/הבדלים |
|---|---|---|---|---|
| M (Power Query Formula Language) | Excel (Power Query), Power BI (Power Query), Dataflows בענן | שלב ETL – שליפה, ניקוי והכנת נתונים לפני הטעינה | – חיבור ל־API- סינון שורות- שינוי טיפוסי עמודות- איחוד טבלאות ממקורות שונים | דקלרטיבית: מתארת "מה רוצים" שיקרה על הנתונים. |
| DAX (Data Analysis Expressions) | Power BI (מודל נתונים), Power Pivot באקסל | חישובים במודל – יצירת מדדים, חישובי סיכום ויחסים בין טבלאות | – חישוב Year-to-Date- ניתוח מכירות מול שנה קודמת- KPI דינמיים | שפת חישובית: מתמקדת באגרגציה וחישובים על מודל הנתונים. |
| VBA (Visual Basic for Applications) | Excel, Word, Outlook וכל חבילת Office (מקומית) | אוטומציה – בניית מאקרו להפעלה אוטומטית של פעולות | – יצירת כפתורים וממשקי משתמש (UserForms)- שליחת מיילים מאאוטלוק- הפקת דוחות PDF בלחיצה אחת | פרוצדורלית: נותנת שליטה מלאה על פעולות באקסל ומעבר לו. |
