Applies ToMac 版 Microsoft 365 Excel

Mac 版 Excel 結合了 Power Query (也稱為「取得及轉換」) 技術,可在匯入、重新整理和驗證資料來源、管理 Power Query 資料來源、清除認證、更改檔案式資料來源的位置、以及將資料形成符合您需求的表格時提供更強大的功能。 也可以使用 VBA 建立 Power Query 査詢。 

附註: 只能在測試人員 Beta 中匯入 SQL Server 資料庫資料來源。

您可以使用 Power Query 從各種資料來源將資料匯入 Excel:Excel 活頁簿、文字/CSV、XML、JSON、SQL Server 資料庫、SharePoint Online 清單、OData、空白資料表和空白査詢。

  1. 選取 [資料] > [取得資料] PQ Mac 取得資料 (Power Query).png

  2. 若要選取所需的資料來源,請選取 [取得資料 (Power Query)]

  3. [選擇資料來源] 對話方塊中,選取可用的資料來源。  在對話方塊中要選取的資料來源範例

  4. 連線至資料來源。 深入了解如何連線至每個資料來源,請參閱從資料來源匯入資料

  5. 選擇要匯入的資料。

  6. 按一下 [載入] 按鈕載入資料。

結果

匯入的資料將顯示在新工作表中。

查詢的一般結果

後續步驟

若要使用 Power Query 編輯器結合和轉換資料,請選取 [轉換資料]。 如需詳細資訊,請參閲使用 Power Query 編輯器結合資料

附註: 此功能已向執行版本 16.69 (23010700) 或更新版本的 Mac 版 Excel 的 Microsoft 365 使用者正式推出。 如果您是 Microsoft 365 訂閱者,請確定您有最新版的 Office。

程序

  1. 選取 [資料] > [取得資料 (Power Query)]

  2. 若要開啟 Query 編輯器,請選取 [啟動Power Query 編輯器] PQ Mac Editor.png

    提示: 您也可以存取 Query 編輯器,方法是選取 [取得資料 (Power Query)],選擇資料來源,然後按一下 [下一步]

  3. 像在 Windows 版 Excel 中一樣,使用 Query 編輯器來結合和轉換資料。 Power Query 編輯器 如需詳細資訊,請參閱 [適用於 Excel 的 Power Query 說明]

  4. 完成後,選取 [常用] > [關閉並載入]

結果

新匯入的資料將顯示在新工作表中。

查詢的一般結果

您可以重新整理以下資料來源:SharePoint 檔案、SharePoint 清單、SharePoint 資料夾、OData、文字/CSV 檔案、Excel 活頁簿 (.xlsx)、XML 和 JSON 檔案、本機表格和範圍以及 Microsoft SQL Server 資料庫。

第一次重新整理

第一次嘗試重新整理活頁簿査詢中檔案式資料來源時,可能需要更新檔案路徑。

  1. 依次選取 [資料] [取得資料] 旁邊的箭頭、然後選取 [資料來源設定]。 顯示 [資料來源設定] 對話方塊。

  2. 選取連線,然後選取 [變更檔案路徑]

  3. [檔案路徑] 對話方塊中,選取新位置,然後選取 [取得資料]

  4. 請選取 [關閉]。

後續重新整理

若要重新整理:

  • 活頁簿中的所有資料來源,選取 [資料] > [全部重新整理]

  • 特定資料來源,以滑鼠右鍵按一下工作表上的査詢表,然後選取 [重新整理]

  • 樞紐分析表,請在樞紐分析表中選取儲存格,然後選取 [樞紐分析表分析] > [重新整理資料]

首次存取 SharePoint、SQL Server、OData 或其他要求權限的資料來源時,必須提供相應認證。 您可能還需要清除認證以輸入新的認證。

輸入驗證

當您首次重新整理査詢時,系統可能會要求您登入。 選取驗證方法並指定登入認證以連線至資料來源並繼續重新整理。

如果需要登入,則會顯示 [輸入認證] 對話方塊。

例如:

  • SharePoint 認證: Mac 上的 SharePoint 認證提示

  • SQL Server 認證: 用於輸入伺服器、資料庫和認證的 [SQL Server] 對話方塊

清除驗證

  1. 選取 [資料] > [取得資料] > [資料來源設定]

  2. [資料來源設定] 對話方塊中,選取所需連線。

  3. 在底部,選取 [清除權限]

  4. 確認這是您想要執行的動作,然後選取 [删除]

雖然在 Mac 版 Excel 中無法使用 Power Query 編輯器進行撰寫,但 VBA 確實支援 Power Query 撰寫。 將檔案中的 VBA 程式碼模組從 Windows 版 Excel 傳輸到 Mac 版 Excel 需要兩個步驟。 本節末尾提供了範例程式。

步驟一:使用 Windows 版 Excel

  1. 在 Excel Windows 中,使用 VBA 開發査詢。 在 Excel 的物件模型中使用以下實體的 VBA 程式碼也可以在 Mac 版 Excel 中使用:Queries 物件、WorkbookQuery 物件、Workbook.Queries 屬性。如需詳細資訊,請參閱 Excel VBA 參考

  2. 在 Excel 中,請確保已按 ALT+F11 開啟 Visual Basic 編輯器處於開啟狀態。

  3. 以滑鼠右鍵按一下模組,然後選取 [匯出檔案][匯出] 對話方塊就會出現。

  4. 輸入檔案名稱,確保副檔名為 .bas,然後選取 [儲存]

  5. 將 VBA 檔案上傳至線上服務,以便從 Mac 存取檔案。 您可以使用 Microsoft OneDrive。 如需詳細資訊,請參閱在 Mac OS X 上與 OneDrive 同步檔案

步驟二:Mac 版 Excel

  1. 將 VBA 檔案下載到本機檔案,即您在「第一步:Windows 版 Excel」中儲存並上傳至線上服務的 VBA 檔案。

  2. 在 Mac 版 Excel 中,選取 [工具] > [巨集] > [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

  1. 開啟 Excel 活頁簿。

  2. 如果取得有關外部資料連線被停用的安全性警告,請選取 [啟用內容]

  3. 如果出現 [授與檔案存取權] 對話方塊,請選取 [選取],然後針對包含資料來源檔案的頂層資料夾的存取權選取 [授與]

  4. 選取 [資料] > [從文字 (舊版)]。 會出現 [尋找工具] 對話方塊。

  5. 尋找 .txt 或 .csv 檔案,然後選取 [開啟]。會出現 [文字匯入精靈]提示    重複檢查 [選取的資料預覽] 窗格以確認您的選擇。

  6. 在第一頁中,執行以下作業:

    檔案類型    若要選擇文字檔案的類型,請選取 [分隔][固定寬度]列號    在 [起始列號] 中,選取列號以指定要匯入的第一列資料。字元集    在 [檔案來源] 中,選取文字檔案中使用的字元集。 在大多數情况下,您可以將此設定保留為預設設定。

  7. 在第二頁中,執行以下動作:[分隔] 如果在第一頁上選擇了 [分隔],請在 [分隔符號] 下選取分隔符號,或使用 [其他] 核取方塊輸入未列出的分隔符號。 如果資料在資料欄位之間包含一個以上字元的分隔符號,或者資料包含多個自訂分隔符號,請選取 [連續分隔符號視為單一處理][文字辨識符號] 中,選取文字檔案中包含值的字元,通常是引號 (") 字元。

    固定寬度 如果在第一頁上選擇了 [固定寬度],請遵循指示在 [預覽選取的資料] 方塊中建立、删除或移動分欄線。

  8. 在第三頁中,執行以下動作: 對於 [預覽選取的資料] 下的每一欄,選取它,然後根據需要將其變更為不同的欄格式。 您可以進一步設定日期格式,然後選取 [進階] 來變更數值資料設定。 您也可以在匯入資料後對其進行轉換。 選取 [完成]。 [匯入資料]對話方塊隨即出現。

  9. 選擇要新增資料的位置:在現有工作表、新工作表或樞紐分析表中。

  10. 選取 [確定]

    若要確保連線正常,請輸入一些資料,然後選取 [連線] > [重新整理]

  1. 選取 [資料] > [來源] [SQL Server ODBC]。 [連線至 SQL Server ODBC 資料來源] 對話方塊隨即出現。 用於輸入伺服器、資料庫和認證的 [SQL Server] 對話方塊

  2. [伺服器名稱] 方塊中輸入伺服器,也可以選擇性在 [資料庫名稱] 方塊中輸入資料庫。 從資料庫管理員處取得此資訊。

  3. [驗證] 下,從清單中選取方法:使用者名稱/密碼KerberosNTLM

  4. [使用者名稱][密碼] 方塊中輸入認證。

  5. 選取 [連線][導覽器] 對話方塊會出現。

  6. 在左窗格中,瀏覽至所需表格,然後選取它。

  7. 確認右窗格中的 SQL 陳述式。 您可以視需要變更 SQL 陳述式。

  8. 若要預覽資料,請選取 [執行]

  9. 準備好後,請選取 [傳回資料]。 [匯入資料]對話方塊隨即出現。  用來尋找資料的 [輸入資料] 對話方塊

  10. 選擇要新增資料的位置:在現有工作表、新工作表或樞紐分析表中。

  11. 要在 [屬性] 對話方塊的 [使用情況][定義] 索引標籤上設定連線屬性,請選取 [屬性]。 匯入資料後,還可以選取 [資料] > [連線],然後在 [連線屬性] 對話方塊中選取 [屬性]

  12. 選取 [確定]

  13. 若要確保連線正常,請輸入一些資料,然後選取 [資料] > [全部重新整理]

如果要使用 SQL Database 的外部來源 (例如 FileMaker Pro),可以使用 Mac 上安裝的開放式資料庫連接 (ODBC) 驅動程式。 此網頁會提供驅動程式的資訊。 安裝資料來源的驅動程式後,請執行以下步驟:

  1. 選取 [資料] [從資料庫 (Microsoft Query)]

  2. 新增資料庫的資料來源,然後選取 [確定]

  3. 在 SQL Server 認證提示下,輸入驗證方法、使用者名稱和密碼。 替代文字

  4. 在左側,選取伺服器旁邊的箭頭以查看資料庫。

  5. 選取所需資料庫旁邊的箭頭。

  6. 選取所需的表格。

  7. 若要預覽資料,請選取 [執行]

  8. 準備好後,請選取 [傳回資料]

  9. [匯入資料] 對話方塊中,選擇資料位置:現有的工作表、新的工作表或樞紐分析表。

  10. 選取 [確定]。

  11. 若要確保連線正常,請輸入一些資料,然後選取 [資料] > [全部重新整理]

如果您的權限無法正常運作,您可能不需要先清除它們,然後登入。

  1. 選取 [資料] > [連線]。 會出現 [活頁簿連線] 對話方塊。

  2. 在清單中選取所需的連線,然後選取 [清除權限] 移除儲存在 Mac 上資料連線的認證

另請參閱

適用於 Excel 的 Power Query 說明

與 Mac 版 Excel 相容的 ODBC 驅動程式

建立樞紐分析表來分析工作表的資料

需要更多協助嗎?

想要其他選項嗎?

探索訂閱權益、瀏覽訓練課程、瞭解如何保護您的裝置等等。

社群可協助您詢問並回答問題、提供意見反應,以及聆聽來自具有豐富知識的專家意見。