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

ייבוא מקורות נתונים

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

1. בחר נתונים>קבל נתונים.

   

2. כדי לבחור את מקור הנתונים הרצוי, בחר קבל נתונים (Power Query).

3. בתיבת הדו-שיח בחירת מקור נתונים, בחר אחד ממקורות הנתונים הזמינים.

   

4. התחבר למקור הנתונים. לקבלת מידע נוסף על אופן ההתחברות לכל מקור נתונים, ראה ייבוא נתונים ממקורות נתונים.

5. בחר את הנתונים שברצונך לייבא.

6. טען את הנתונים על-ידי לחיצה על לחצן 'טען '.

Result

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

השלבים הבאים

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

עיצוב נתונים באמצעות עורך Power Query

הליך

1. בחר נתונים>קבל נתונים (Power Query).

2. כדי לפתוח את עורך Power Query, בחר הפעל עורך Power Query.

   

   עצה

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

3. עצב את הנתונים והמר אותם באמצעות עורך השאילתות כפי שהיית עושה ב- Excel עבור Windows.

   

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

4. לאחר שתסיים, בחר סגור את הבית>& טען.

Result

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

רענון מקורות נתונים

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

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

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

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

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

כדי לרענן:

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

הזנה וניקוי של אישורים

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

הזנת אישורים

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

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

לדוגמה:

• אישורי SharePoint:

  

• אישורי SQL Server:

  

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

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

עריכת קוד VBA ב- Power Query והעברתו

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

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

1. ב- Excel עבור Windows, חבר שאילתות באמצעות VBA. קוד VBA המשתמש בישויות הבאות במודל האובייקטים של Excel פועל גם ב- Excel עבור Mac: אובייקט Queries, האובייקט WorkbookQuery, המאפיין Workbook.Queries. לקבלת מידע נוסף, ראה חומר עזר בנושא Excel VBA.

2. ב- Excel, ודא שעורך ה- Visual Basic פתוח על-ידי הקשה על ALT+F11.

3. לחץ באמצעות לחצן העכבר הימני על המודול ולאחר מכן בחר ייצוא קובץ. תיבת הדו-שיח ייצוא מופיעה.

4. הזן שם קובץ, ודא שסיומת הקובץ היא bas. ולאחר מכן בחר שמור.

5. העלה את קובץ ה- VBA לשירות מקוון כדי להפוך את הקובץ לנגיש מה- Mac.

   באפשרותך להשתמש ב- Microsoft OneDrive. לקבלת מידע נוסף, ראה סינכרון קבצים באמצעות OneDrive ב- Mac OS X.

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

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

ייבוא ורענון של קבצי TXT או CSV

1. פתח את חוברת העבודה של Excel.

2. אם אתה מקבל אזהרת אבטחה לגבי הפיכת חיבורי נתונים חיצוניים ללא זמינים, בחר הפוך תוכן לזמין.

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

4. בחר נתונים מטקסט>(דור קודם). תיבת הדו-שיח Finder מופיעה.

5. אתר את .txt או .csv, ובחר פתח.אשף ייבוא הטקסט מופיע.

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

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

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

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

7. בעמוד השני, בצע את הפעולות הבאות:

   מופרד

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

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

   במזהה טקסט, בחר את התו המקיף ערכים בקובץ הטקסט, שהוא לרוב תו המרכאות הכפולות (").
   רוחב קבוע

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

8. בעמוד השלישי, בצע את הפעולות הבאות:

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

   בחר סיום. תיבת הדו-שיח ייבוא נתונים תופיע.

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

10. בחר אישור.
    כדי לוודא שהחיבור פועל, הזן נתונים מסוימים ולאחר מכן בחר רענון>חיבורים.

התחברות למסד נתונים של SQL ‏(ODBC)

1. בחר נתונים>SQL Server ODBC. תיבת הדו-שיח התחבר למקור נתונים של ODBC של SQL Server מופיעה.

   

2. הזן את השרת בתיבה שם שרת , ואם תרצה, הזן את מסד הנתונים בתיבה שם מסד נתונים.

   קבל מידע זה ממנהל מסד הנתונים.

3. תחת אימות, בחר שיטה מהרשימה: שם משתמש/סיסמה, Kerberos או NTLM.

4. הזן אישורים בתיבות שם משתמשוסיסמה .

5. בחר באפשרות התחבר. תיבת הדו-שיח נווט מופיעה.

6. בחלונית הימנית, נווט אל הטבלה הרצויה ולאחר מכן בחר אותה.

7. אשר את משפט ה- SQL בחלונית השמאלית. באפשרותך לשנות את משפט ה- SQL כרצונך.

8. כדי להציג את הנתונים בתצוגה מקדימה, בחר הפעל.

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

   

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

11. כדי להגדיר מאפייני חיבור בכרטיסיות שימושוהגדרה של תיבת הדו-שיח מאפיינים, בחר מאפיינים. לאחר ייבוא הנתונים, באפשרותך גם > לבחור חיבורינתונים ולאחר מכן, בתיבת הדו-שיח מאפייני חיבור, בחר מאפיינים.

12. בחר אישור.

13. כדי לוודא שהחיבור פועל, הזן נתונים מסוימים ולאחר מכן בחר רענן הכל>נתונים.

התחברות למסד נתונים אחר (Microsoft Query)

אם ברצונך להשתמש במקור חיצוני שאינו מסד נתונים של SQL (לדוגמה, FileMaker Pro), באפשרותך להשתמש במנהל התקן של קישוריות מסד נתונים פתוחה (ODBC) המותקן ב- Mac. מידע על מנהלי התקנים זמין בעמוד אינטרנט זה. לאחר התקנת מנהל ההתקן עבור מקור הנתונים שלך, בצע את הפעולות הבאות:

1. בחר נתונים ממסד>נתונים (Microsoft Query).

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

3. בבקשה לאישורי SQL Server, הזן את שיטת האימות, את שם המשתמש ואת הסיסמה.

   

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

5. בחר את החץ שלצד מסד הנתונים הרצוי.

6. בחר את הטבלה הרצויה.

7. כדי להציג את הנתונים בתצוגה מקדימה, בחר הפעל.

8. כשתהיה מוכן, בחר באפשרות החזר נתונים.

9. בתיבת הדו-שיח ייבוא נתונים, בחר היכן ברצונך למקם את הנתונים: בגיליון קיים, בגיליון חדש או ב- PivotTable.

10. בחר אישור.

11. כדי לוודא שהחיבור פועל, הזן נתונים מסוימים ולאחר מכן בחר רענן הכל>נתונים.

ניקוי אישורי הכניסה שלך

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

1. בחר חיבורי>נתונים. תיבת הדו-שיח חיבורי חוברת עבודה תופיע.

2. בחר את החיבור הרצוי מהרשימה ולאחר מכן בחר נקה הרשאות.