ייבוא נתונים ועיצוב צורתם ב- Excel עבור Mac (Power Query)

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

הערה: ניתן לייבא את מקור הנתונים 'מסד נתונים של SQL Server' רק בגרסת ביתא של משתתפי Insider.

באפשרותך לייבא נתונים ל- Excel באמצעות Power Query ממגוון רחב של מקורות נתונים: חוברת עבודה של Excel, טקסט/CSV‏, XML,‏ JSON, מסד נתונים של SQL Server, רשימה של SharePoint Online,‏ OData, טבלה ריקה ושאילתה ריקה.

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

Result

הנתונים המיובאים יופיעו בגיליון חדש.

תוצאות אופייניות של שאילתה

השלבים הבאים

כדי לעצב ולהמיר נתונים באמצעות עורך Power Query, בחר המרת נתונים. לקבלת מידע נוסף, ראה עיצוב נתונים באמצעות עורך Power Query.

הערה: תכונה זו זמינה בדרך כלל למנויי Microsoft 365 המשתמשים ב- Excel עבור Mac מגירסה 16.69 (23010700) ואילך. אם אתה מנוי של Microsoft 365, ודא שיש לך את הגירסה העדכנית ביותר של Office.

הליך

בחר נתונים> קבל נתונים (Power Query).
כדי לפתוח את עורך השאילתות, בחר הפעל את עורך Power Query.

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

Result

הנתונים שיובאו יופיעו בגיליון חדש.

תוצאות אופייניות של שאילתה

באפשרותך לרענן את מקורות הנתונים הבאים: קבצי SharePoint, רשימות SharePoint, תיקיות SharePoint, OData, קבצי טקסט/CSV, חוברות עבודה של Excel (.xlsx), קבצי XML ו- JSON, טבלאות וטווחים מקומיים, מסד נתונים של Microsoft SQL Server ותיקיות.

רענון בפעם הראשונה

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

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

רענון בפעמים הבאות

כדי לרענן:

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

בפעם הראשונה שאתה ניגש אל SharePoint,‏ SQL Server, ‏OData או מקורות נתונים אחרים הדורשים הרשאה, עליך לספק אישורים מתאימים. ייתכן שתרצה גם לנקות את האישורים כדי להזין אישורים חדשים.

הזנת אישורים

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

אם יש צורך בכניסה, תופיע תיבת הדו-שיח הזן אישורים.

לדוגמה:

אישורי SharePoint:
אישורי SQL Server:

ניקוי אישורים

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

למרות שעריכה בעורך Power Query אינה זמינה ב- Excel עבור Mac, ‏VBA תומך בעריכת Power Query. העברת מודול קוד VBA בקובץ מ- Excel עבור Windows ל- Excel עבור Mac היא תהליך דו-שלבי. תוכנית לדוגמה מוצגת בסוף מקטע זה.

שלב ראשון: Excel עבור Windows

ב- Excel עבור Windows, חבר שאילתות באמצעות VBA. קוד VBA המשתמש בישויות הבאות במודל האובייקטים של Excel פועל גם ב- Excel עבור Mac: אובייקט Queries, האובייקט WorkbookQuery, המאפיין Workbook.Queries.לקבלת מידע נוסף, ראה חומר עזר בנושא Excel VBA.
ב- Excel, ודא שעורך ה- Visual Basic פתוח על-ידי הקשה על ALT+F11.
לחץ באמצעות לחצן העכבר הימני על המודול ולאחר מכן בחר ייצוא קובץ. תיבת הדו-שיח ייצוא תופיע.
הזן שם קובץ, ודא שסיומת הקובץ היא bas. ולאחר מכן בחר שמור.
העלה את קובץ ה- VBA לשירות מקוון כדי להפוך את הקובץ לנגיש מה- Mac. באפשרותך להשתמש ב- Microsoft OneDrive. לקבלת מידע נוסף, ראה סינכרון קבצים באמצעות OneDrive ב- Mac OS X.

שלב שני: Excel עבור Mac

הורד את קובץ ה- VBA לקובץ מקומי; הכוונה היא לקובץ ה- VBA ששמרת ב"שלב 1: Excel עבור Windows" והעלית אותו לשירות מקוון.
ב- Excel עבור Mac, בחר כלים > מאקרו > עורך Visual Basic. החלון של עורך Visual Basic יופיע.
לחץ באמצעות לחצן העכבר הימני על אובייקט בחלון 'פרויקט' ולאחר מכן בחר ייבוא קובץ. תיבת הדו-שיח ייבוא קובץ תופיע.
אתר את קובץ ה- VBA ולאחר מכן בחר פתח.

קוד לדוגמה

הנה קוד בסיסי שניתן להתאים אותו ולהשתמש בו. זוהי שאילתה לדוגמה אשר יוצרת רשימה עם ערכים מ- 1 עד 100.

Sub CreateSampleList()
  ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
    "let" & vbCr & vbLf & _
      "Source = {1..100}," & vbCr & vbLf & _
      "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
      "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
    "in" & vbCr & vbLf & _
      "RenamedColumns"
  ActiveWorkbook.Worksheets.Add
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [SampleList]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "SampleList"
    .Refresh BackgroundQuery:=False
  End With
End Sub