יצירת נוסחאות של Power Query ב- Excel

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

הערה: Power Query ידוע בשם קבלה והמרה ב- Excel 2016. המידע המסופק כאן חל על שניהם. לקבלת מידע נוסף, ראה קבלה והמרה ב- Excel 2016.

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

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

יצירת נוסחה פשוטה

עבור נוסחה פשוטה לדוגמה, נמיר ערך טקסט לרישיות המתאימה באמצעות הנוסחה Text.Proper() .

  1. בכרטיסיית רצועת הכלים POWER QUERY, בחר ממקורות אחרים > שאילתה ריקה.

    רצועת הכלים של Power Query
  2. ב שורת הנוסחאות של עורך השאילתות, הקלד = Text.Proper("text value") ולאחר מכן הקש Enter או בחר את סמל Enter. עורך נוסחאות

  3. Power Query מציג את התוצאות בחלונית תוצאות הנוסחה.

  4. כדי להציג את התוצאה בגליון עבודה של Excel, בחר סגור וטען.

התוצאה תיראה כך בגליון עבודה:

Text. proper

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

יצירת נוסחה מתקדמת

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

הערה: נושא זה הוא מבוא לנוסחאות מתקדמות של Power Query. לקבלת מידע נוסף על נוסחאות של Power Query, ראה למד על הנוסחאות של Power Query.

לדוגמה, נניח שיש לך טבלת Excel עם שמות מוצרים שברצונך להמיר לרישיות המתאימה.

הטבלה המקורית נראית כך:

לפני

אתה מעוניין שהטבלה המתקבלת תיראה כך:

אחרי

אנו נבנה את שלבי נוסחת השאילתה כדי לשנות את הטבלה המקורית כך שהערכים העמודה ProductName יהיו ברישיות המתאימה.

דוגמה לשאילתה מתקדמת באמצעות העורך המתקדם

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

  • צור סידרה של שלבי נוסחת שאילתה שמתחילים במשפט let. שים לב ששפת הנוסחאות של Power Query תלויית רישיות.

  • כל שלב נוסחת שאילתה מתבסס על השלב הקודם על-ידי הפניה לשלב לפי שמו.

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

שלב 1 – פתיחת העורך המתקדם

  1. בכרטיסיית רצועת הכלים POWER QUERY, בחר ממקורות אחרים > שאילתה ריקה.

  2. בעורך השאילתות, בחר עורך מתקדם.

    עורך מתקדם

  3. יוצג עורך מתקדם.

    עורך מתקדם2

שלב 2 – הגדרת המקור המקורי

בעורך מתקדם:

  1. השתמש במשפט let שמקצה את המקור באמצעות הנוסחה Source = Excel.CurrentWorkbook()‎. נוסחה זו תשתמש בטבלת Excel כמקור הנתונים. לקבלת מידע נוסף על הנוסחה Excel.CurrentWorkbook()‎, ראה Excel.CurrentWorkbook.

  2. הקצה את Source לתוצאה של in.

    let Source =
    Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
    in Source
  3. השאילתה המתקדמת שלך תיראה כך בעורך מתקדם.

    עורך מתקדם 3
  4. כדי להציג את התוצאות בגליון עבודה:

    1. לחץ על בוצע.

    2. ברצועת הכלים של עורך השאילתות, לחץ על סגור וטען.

שלב 1 - עורך מתקדם

התוצאה נראית כך בגליון עבודה:

שלב 1 - תוצאה

שלב 3 – קידום השורה הראשונה לכותרות

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

  1. הוסף את הנוסחה ‎#"First Row as Header" = Table.PromoteHeaders()‎ לשלבי נוסחת השאילתה וצור הפניה ל- Source כמקור הנתונים. לקבלת מידע נוסף על הנוסחה Table.PromoteHeaders()‎, ראה Table.PromoteHeaders.

  2. הקצה את ‎#"First Row as Header"‎ לתוצאה של in.

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
        #"First Row as Header" = Table.PromoteHeaders(Source)
    in
        #"First Row as Header"

התוצאה נראית כך בגליון עבודה:

שלב 3 - תוצאה

שלב 4 – שינוי כל אחד מהערכים בעמודה לרישיות המתאימה

כדי להמיר כל אחד מהערכים בעמודה ProductName לטקסט רגיל, עליך להשתמש בנוסחה Table.TransformColumns()‎ וליצור הפניה לשלב נוסחת השאילתה ‎"First Row as Header"‎. עליך לעשות זאת בעורך מתקדם:

  1. הוסף את הנוסחה ‎#"Capitalized Each Word" = Table.TransformColumns()‎ לשלבי נוסחת השאילתה וצור הפניה ל- ‎#"First Row as Header"‎ כמקור הנתונים. לקבלת מידע נוסף על הנוסחה Table.TransformColumns()‎, ראה Table.TransformColumns.

  2. הקצה את ‎#"Capitalized Each Word"‎ לתוצאה של in.

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    #"First Row as Header" = Table.PromoteHeaders(Source),
    #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
in
    #"Capitalized Each Word"

התוצאה הסופית תשנה כל אחד מהערכים בעמודה ProductName לרישיות המתאימה ותיראה כך בגליון עבודה:

שלב 4 - תוצאה

באמצעות שפת הנוסחאות של Power Query, באפשרותך ליצור שאילתות נתונים פשוטות או מתקדמות כדי לגלות, לשלב ולמקד נתונים. לקבלת מידע נוסף על Power Query, ראה עזרה עבור Microsoft Power Query for Excel.

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

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

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

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

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

×