M365Con 2027 – כנס אונליין חינמי · 11–22 Jan 2027 · 120 sessions→ m365con.net

בלוג · Mirko Peters

Power Query ב-Excel: ייבוא, ניקוי, מיזוג ורענון של נתונים

By · · Updated

Power Query ב-Excel מייבא ומנקה נתונים ללא נוסחאות. למדו איפה למצוא אותו, From Table/Range, Applied Steps, ההבדל בין מיזוג לצירוף, טעינה ורענון.

Automatically translated with AI from the original post. Read the English original

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

עיקרי הדברים

  • Power Query ב-Excel נמצא בכרטיסייה Data, בקבוצה Get & Transform Data. הוא מתחבר לנתונים, מעצב אותם וטוען אותם לגיליון או ל-Data Model.
  • כל שינוי שאתם מבצעים נרשם כשלב ב-Applied Steps. מקור הנתונים המקורי אינו משתנה, והשלבים מורצים מחדש בכל רענון.
  • Append מערים שורות מטבלאות עם אותן עמודות. Merge מחבר שתי טבלאות לפי עמודות מפתח תואמות, ולעמודות התואמות חייב להיות אותו סוג נתונים.
  • השתמשו ב-Data, Refresh All כדי להריץ מחדש את השאילתות. ב-Connection Properties אפשר להגדיר רענון במרווחי זמן או כשהקובץ נפתח.
  • Excel ל-Mac וגרסת Excel באינטרנט תומכים בחלק מ-Power Query. לפי דפי Microsoft, יש הבדלים בין מה שכל אחד מהם יכול ליצור, ולכן בדקו את הגרסה שלכם.

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

מהו Power Query ואיפה למצוא אותו

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

היכן הוא נמצא תלוי בגרסת Excel, לפי סקירת Power Query ב-Excel של Microsoft:

  • Excel 2016, 2019 ו-Microsoft 365 ל-Windows. מובנה. עוברים ל-Data ומסתכלים על הקבוצה Get & Transform Data.
  • Excel 2010 ו-2013 ל-Windows. Power Query היה תוסף חינמי עם כרטיסייה משלו בסרט הכלים. לפי Microsoft הוא הוצא משימוש, אם כי ייתכן שהוא עדיין יעבוד.
  • Excel ל-Mac. נתמך לרענון ממקורות רבים. הוא אינו זמין ב-Excel 2016 או 2019 ל-Mac.
  • Excel באינטרנט. נתמך בחלקו. פירוט בהמשך.

ב-Windows, Microsoft מציינת גם את .NET Framework 4.7.2 ומעלה, ואת WebView2 Runtime עבור המחבר לאינטרנט, כדרישות. אם הפקודות חסרות במחשב עבודה, שאלו את צוות ה-IT אם הגרסה מעודכנת לפני שתניחו שהתכונה הוסרה.

איך משתמשים ב-Power Query ב-Excel: השאילתה הראשונה שלכם

ההתחלה הקלה ביותר היא עם נתונים שכבר יש לכם בחוברת עבודה.

  1. לחצו על כל תא בטבלת Excel שלכם. אם הנתונים שלכם הם טווח רגיל, לחצו תחילה על Ctrl+T כדי להפוך אותם לטבלה.
  2. עברו אל נתונים > קבל והמר נתונים > מטבלה/טווח.
  3. עורך Power Query Editorייפתח עם תצוגה מקדימה של הנתונים שלכם.
  4. בצעו את השינויים הנדרשים (ראו את הסעיף הבא).
  5. בחרו Home > Close & Load.

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

כדי להתחבר למקור אחר, השתמשו ב-Data > Get Data ובחרו מקור: קובץ, מסד נתונים, שירות מקוון או האינטרנט. לאחר הבחירה תקבלו תצוגה מקדימה ותוכלו לבחור בין Load (ישירות ל-Excel) לבין Transform Data (פתיחת העורך קודם). לדעתי, כמעט תמיד כדאי לבחור ב-Transform Data, גם כשנדמה לכם שהנתונים נקיים. בעורך מגדירים את סוגי העמודות, ובחירה שגויה של סוגים גורמת לרוב שגיאות הרענון בהמשך.

אם אתם רוצים התחלה ריקה, באותו עמוד מופיעים Data > Get Data > From Other Sources > Blank Query, או Launch Power Query Editor תחת Get Data.

העורך במילים פשוטות

  • תצוגת הנתונים המקדימה מציגה את הטבלה שלכם ומתעדכנת after כל שינוי.
  • החלונית Query Settings מציגה את APPLIED STEPS. כל פעולה שאתם מבצעים מוסיפה שלב, כמו Source או Changed Type. אפשר לבחור שלב כדי לראות את הנתונים באותה נקודה, לשנות שלב או למחוק אותו.
  • החלונית Queries מציגה את כל השאילתות בחוברת העבודה. פתחו אותה בעזרת החץ שליד התצוגה המקדימה.
  • ה-Advanced Editor מאפשר לראות ולערוך את הסקריפט הבסיסי בשפה Power Query M. Microsoft מציינת שכל שלב שנבנה בממשק נכתב ב-M. אינכם חייבים לגעת בו כדי להפיק תועלת מהכלי.

ניקוי ועיצוב נתונים בעזרת שלבים

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

שגרה שעובדת על רוב קובצי הייצוא:

  1. קבעו את הסוגים תחילה. בדקו את סמל הסוג שליד כל כותרת עמודה. תאריכים, מספרים וטקסט מתנהגים אחרת בשלבים הבאים ובמיזוגים.
  2. הסירו מה שאינכם צריכים. פחות עמודות משמעותן תוצאה קטנה ומהירה יותר, ופחות הפתעות כששהמקור משתנה.
  3. סננו שורות. השתמשו בחץ בכותרת העמודה כדי להסיר ערכים ריקים או ערכים שאינכם רוצים.
  4. תקנו טקסט. החליפו שגיאות כתיב וקודים ישנים, בצעו Trim, או פצלו עמודה שמכילה שני פריטי מידע.
  5. שנו את שם השאילתה בחלונית Query Settings כדי למצוא אותה בקלות בהמשך.

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

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

שילוב נתונים: Append ו-Merge

שתי הפקודות האלה עושות דברים שונים, ואנשים נוטים לבלבל ביניהן.

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

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

  1. ודאו שיש לכם לפחות שתי שאילתות עם עמודה להתאמה. לעמודות ההתאמה צריך להיות אותו סוג נתונים, למשל טקסט או מספר.
  2. בעורך, עברו אל Home > Merge Queries. התוצאה הופכת לשלב חדש בשאילתה הנוכחית. אם אתם מעדיפים שאילתה נפרדת, פתחו את החץ שליד Merge Queries ובחרו ב-Merge Queries as New.
  3. בחרו את הטבלה הראשית ואת עמודת המפתח שלה, ואחר כך את הטבלה המקושרת ואת עמודת המפתח שלה. אם יש כמה עמודות מפתח, בחרו אותן באותו סדר בשתי הטבלאות.
  4. בדקו את מספר ההתאמות שמוצג בתיבת הדו-שיח. אם הוא נמוך מהצפוי, כנראה שעמודות המפתח נבדלות באיות, ברווחים או בסוג הנתונים.
  5. בחרו Join Kind (ראו להלן) ולחצו על OK.
  6. נוצרת עמודה חדשה. לחצו על אייקון Expand שליד הכותרת שלה, וסמנו את העמודות המקושרות שברצונכם להוסיף.

סוגי החיבור לפי Microsoft:

  • Left outer שומר את כל השורות של הטבלה הראשית ואת ההתאמות מהטבלה המקושרת.
  • Right outer שומר את כל השורות של הטבלה המקושרת ואת ההתאמות מהטבלה הראשית.
  • Full outer שומר את כל השורות משתי הטבלאות.
  • Inner שומר רק שורות שיש להן התאמה בשתי הטבלאות.
  • Left anti שומר שורות מהטבלה הראשית שאין להן התאמה. Right anti שומר שורות מהטבלה המקושרת שאין להן התאמה.
  • Cross מזווג כל שורה בטבלה הראשית עם כל שורה בטבלה המקושרת.

סדר הטבלאות חשוב, משום ש-left ו-right תלויים בטבלה שבחרתם ראשונה. תיבת הסימון Use fuzzy matching to perform the merge מתאימה ערכים משוערים, ולפי Microsoft היא פועלת רק על עמודות טקסט. אם נהגתם להשתמש ב-lookup, מיזוג מסוג left outer הוא הקרוב ביותר לכך. ו-left anti הוא דרך מהירה לראות אילו שורות אין להן התאמה, למשל הזמנות ללא רשומת לקוח.

Microsoft מזכירה גם רמות פרטיות. Power Query מסווג כל מקור כ-Public, Organizational או Private, ומשתמש בסיווג הזה כדי למנוע שילוב בלתי מכוון של נתונים, באופן שעלול לחשוף מידע פרטי למקור אחר. אם מיזוג מבקש מכם לבחור רמות פרטיות, קראו את ההודעה לפני שאתם ממשיכים.

טעינת שאילתה ורענון שלה

סגרו את העורך באמצעות Home > Close & Load. בעמוד של Microsoft על יצירה, טעינה ועריכה של שאילתה מוסבר על היעדים הבאים:

  • Close & Load שולח את התוצאה לגיליון עבודה.
  • Close & Load To מאפשר לבחור יעד אחר, כמו מודל הנתונים (Data Model). בתיבת הדו-שיח Import Data, סמנו את Add this data to the Data Model כשאתם מתכננים לבנות PivotTables על פני כמה טבלאות.
  • אם Load To אינו זמין, השתמשו ב-Close & Load, ואז עברו אל Data > Queries & Connections, לחצו לחיצה ימנית על השאילתה בלשונית Queries ובחרו Load To.

כדי לשנות את ברירות המחדל, עברו אל File > Options and settings > Query Options > Data Load. אפשר להשתמש בהגדרות הטעינה הרגילות או להגדיר ברירות מחדל מותאמות אישית לטעינה לגיליון עבודה או למודל הנתונים.

כדי לערוך שאילתה מאוחר יותר, בחרו תא בנתונים שלה ובחרו Query > Edit, או לחצו לחיצה ימנית על השאילתה ב-Queries & Connections ובחרו Edit.

רענון

רענון מריץ מחדש את כל השלבים מול המקור הנוכחי. הדרכים, מתוך עמוד הרענון של Microsoft:

  • Data > Refresh All מעדכן את כל התוכן בחוברת העבודה (Ctrl+Alt+F5). החץ שלצידו מציע את Refresh עבור הפריט הנבחר (Alt+F5). Esc עוצר רענון.
  • לרענון אוטומטי, עברו אל Data > Queries & Connections, פתחו את הכרטיסייה Connections, לחצו לחיצה ימנית על חיבור ובחרו Properties. בכרטיסייה Usage אפשר להגדיר את Refresh every (בדקות) ואת Refresh data when opening the file.
  • Enable background refresh באותה כרטיסייה מאפשר להמשיך לעבוד בזמן שהשאילתה רצה. האפשרות אינה זמינה עבור שאילתות OLAP או חיבורים שמזינים את מודל הנתונים.

סיסמאות שמורות אינן מוצפנות, ו-Microsoft ממליצה שלא לשמור אותן. בטלו את הסימון של Save password בכרטיסייה Definition, ו-Excel יבקש את הסיסמה פעם אחת בכל סשן.

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

Power Query ב-Mac וב-Excel for the web

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

Excel for the web. דף של Microsoft מציין שניתן לייבא ממקורות כגון חוברת Excel, Text/CSV, XML, JSON, SQL Server, רשימה של SharePoint Online ו-OData, וכן טבלה ריקה או שאילתה ריקה. אפשר לפתוח את Power Query Editor (Data > Get Data > Launch Power Query Editor), לטעון עם Close & Load, ולרענן עם Data > Refresh All. הצפייה בשאילתות וריענונן זמינים לכל מנויי Microsoft 365, ואילו פונקציונליות נוספת דורשת תוכנית עסקית או תוכנית ארגונית (E3 ומעלה). חלק מהתכונות דורשות הפעלה של קובצי Cookie של צד שלישי בדפדפן.

Excel for Mac. דף אחד של Microsoft מציין שמתחילים מ-Data > Get Data > Get Data (Power Query), שעיצוב הנתונים דורש Microsoft 365 וגרסה 16.69 ומעלה, ושאת העורך פותחים באמצעות Transform Data. באותו דף, בסעיף נפרד, נכתב שכתיבת שאילתות בעורך אינה זמינה ב-Excel for Mac, ומתואר כתיבת שאילתות ב-VBA ב-Windows וייבוא המודול ב-Mac. אני לא מצליח ליישב בין שתי הקביעות האלה, ולכן כדאי לבדוק בעצמכם על Mac לפני שבונים על כך תהליך. ייבוא מ-SQL Server מתואר כזמין רק בגרסת Insiders Beta.

מתי Power Query אינו מספיק

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

מגבלות נוספות שכדאי להכיר:

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

טעויות נפוצות

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

נושאים קשורים לבדיקה

  • הסרת כפילויות בעורך. Power Query יכול להסיר כפילויות בטבלה כשלב, וזה ניתן לחזרה, בניגוד לפקודה החד-פעמית בסרט הכלים.
  • ביטול ציר של עמודות (Unpivot). הפכו עמודות חודשים לשורות לפני שבונים PivotTable. Microsoft מזכירה את Power Query בדיוק למטרה הזו.
  • פרמטרים. ל-Microsoft יש דף על שאילתות עם פרמטרים, לשימוש חוזר בשאילתה עם קלטים שונים.
  • שפת M. למדו לקרוא את הסקריפט בעורך המתקדם, החל מהעמוד Power Query M.
  • Power BI. למדו איך אותו עורך פועל ב-Power BI Desktop כשהנתונים שלכם גדלים מעבר ל-Excel.

הצעד הבא

בחרו יצוא אחד שאתם מנקים ביד מדי שבוע. הפכו אותו לטבלה, הריצו אותו דרך From Table/Range, הגדירו את סוגי העמודות וטענו אותו לגיליון חדש. בשבוע הבא, הדביקו את היצוא החדש לתוך הטבלה ולחצו על Refresh All. זה Power Query ב-Excel עושה את העבודה שלו.

מקורות

עקבו אחרי הפודקאסטSpotify

Entities in this article

Microsoft Excel Power Query Power Query M formula language Power BI Desktop

Related articles

RSS