Microsoft Excel 提供了多種用於操作資料的工具和功能，而 Power Query 是最好的工具和功能之一。此業務分析工具可讓您從各種來源匯入數據，並根據需要在 Excel 中輕鬆轉換和操作資料。基本上，它消除了重複性任務，有助於減少工作量並節省時間。
Power Query 的一個主要優點是您不需要任何編碼專業知識或知識即可使用它。讓我們看看如何使用它來操作 Microsoft Excel 中的資料。
存取電源查詢
從 Excel 2010 開始，Power Query 可在所有版本的 Microsoft Excel 中使用。從 Excel 2016 開始，它已直接融入應用程式中。
在 Excel 2016 及更高版本中
- 啟動新的 Excel 工作表並點擊功能表列上的「資料」標籤。
- 從「資料」標籤中的選項中，按一下功能表列下方左上角的「取得資料」選項。
- 其中包含用於匯入和轉換資料的所有 Power Query 工具和選項。
在 Excel 2013 和 2010 中
對於 Excel 2013 和 2010 版本，Power Query 以免費外掛程式提供，您可以從 Microsoft 網站下載。
- 前往Power Query 下載頁面並點擊「下載」按鈕開始下載工具。
- 當您按一下「下載」按鈕時，您將看到幾個選項，您可以根據您的系統從中選擇合適的選項。
- 選擇正確的選項後，按一下「下載」按鈕下載工具。
使用電源查詢工具
開啟 Excel 工作表後，您可以從「資料」標籤然後「取得資料」選項存取 Power Query 工具。
導入數據
- 當您按一下「取得資料」選項時，它將顯示您可以匯入資料的各種來源。其中包括 Excel 工作簿、文字或 CSV 檔案、XML 和JSON 檔案。除此之外，您還可以從 SQL Server 和 Microsoft Access 等線上資料庫匯入資料。您可以匯入資料的其他來源包括 Microsoft Azure 和線上服務，例如 Salesforce 和 Facebook。
- 若要匯入數據，請按一下任意選項，例如「從檔案」、「從資料庫」、「從 Azure」、「從線上服務」和「從其他來源」。
- 匯入資料時，Excel 將顯示一個彈出窗口，其中顯示將載入的資料的預覽。點擊底部的“加載”按鈕完成資料導入。
- 現在，您將在 Excel 工作表中看到數據，並可以對其套用不同的轉換。
Power Query 編輯器的元件
- 您需要 Power Query 編輯器根據需要轉換導入的資料。點選「取得資料」按鈕後，按一下「啟動 Power Query 編輯器」。
- 這將啟動“Power Query 編輯器”，它由六個主要組件組成。在頂部，您將找到“查詢編輯器功能區”，其中包含不同選項卡下的各種命令。
- 左側“查詢編輯器功能區”下方是“查詢清單”，其中顯示工作簿中的所有查詢。中心還將有一個「資料預覽」部分，其中顯示應用於資料的所有轉換。
- 「公式欄」允許編輯轉換步驟的 M 程式碼。所有轉換都會被記錄並在「應用的步驟」區域中顯示為步驟。
- “屬性”部分可讓您提供帶有名稱的查詢。
應用轉換
您可以對 Power Query 編輯器中匯入的資料套用各種轉換。其中包括文本構成、修剪、轉置等。
文字轉換
將文字匯入編輯器後，可以將其轉換為大寫或小寫。
- 在 Power Query 編輯器中，轉到頂部的「轉換」選項卡，您將看到幾個選項，例如「轉置」、「替換值」等。
- 「格式」選項位於中心，「分割列」選項旁。按一下它以查看可用的格式選項。
- 按一下任何選項（例如“小寫”或“大寫”），將所選列中的文字轉換為小寫或大寫。同樣，點擊其他選項也會相應地改變文字。
- 「格式」選項還允許您使用「修剪」選項刪除所有空白。當您按一下「修剪」按鈕時，它將刪除文字中的所有多餘空格。
拆分列
除了轉換文字之外，Power Query 編輯器還允許以各種方式拆分列。
- 將資料匯入 Power Query 編輯器後，按一下列標題以選擇整個列。
- 然後點擊“格式”按鈕左側的“拆分列”按鈕。這將為您提供允許以不同方式拆分所選列的選項清單。
- 若要按分隔符號拆分列，請按一下對應的選項。這將顯示按分隔符號分割的彈出窗口，您可以在其中選擇分隔符，例如逗號、冒號、等號等。
- 按一下「確定」按鈕根據需要拆分列，您將看到該列已被拆分。
轉置數據
透過「轉置」選項，使用者可以將資料從行切換到列，反之亦然。為此，首先將資料匯入 Power Query 編輯器，如前所述。
- 加載資料後，轉到頂部的“轉換”選項卡，您將在其中找到“轉置”選項。
- 按一下“轉置”選項將行轉換為列。
組合查詢
Power Query 可讓您使用「合併」和「追加」選項輕鬆組合多個資料集。
使用合併選項
合併操作可讓您透過組合現有查詢來建立新查詢。
- 首先，將資料從文件、資料庫或其他來源匯入 Excel 工作表。在這種情況下，您不需要將資料載入到 Power Query 編輯器中，但需要匯入多個資料集。
- 您將在匯入資料的選項下方看到另一個選項「組合查詢」。將遊標指向此選項，將出現兩個可用選項 – 追加和合併。
- 點擊“合併”按鈕將顯示一個新的彈出窗口，您可以在其中選擇必須合併的資料集。
- 選擇資料集將向您顯示預覽。在左下角，您可以選擇合併資料集的方式，然後按一下「確定」按鈕。
使用追加選項
「附加」選項可讓您透過組合先前查詢的行來建立新表。
- 請按照與上述相同的步驟將資料集新增至 Excel 工作表，然後前往「組合查詢」部分中的「附加」選項。
- 在出現的彈出視窗中，選擇需要合併資料的表，然後按一下「確定」按鈕。使用者可以合併兩個表或三個或更多表中的資料。
- 合併的資料將顯示在 Power Query 編輯器視窗中，您可以使用左上角的「關閉並載入」按鈕將其匯入工作表中。
將資料載入到工作表
在 Power Query 編輯器中完成所有操作後，您需要將資料載入到 Excel 工作表中。
- 有多種方法可以將轉換後的資料載入到 Excel 工作表中，例如載入到資料透視圖、資料透視表、表格或查詢連線中。點擊左上角的「關閉並載入」選項，您將看到兩個選項—「關閉並載入」和「關閉並載入到」。
- 點擊第二個選項將顯示用於將資料載入到工作表中的各種選項。
- Excel 可讓您選擇位置，例如現有工作表中的儲存格或將自動建立的新工作表。還有一個選項“將此數據添加到數據模型”。
使用公式和函數
Power Query 也允許使用類似 Excel 工作表的公式和函數。這需要新增自訂列，您可以在其中新增公式和函數。
- 從「取得資料」標籤啟動 Power Query 編輯器，然後轉到頂部的「新增列」標籤。
- 在左側，您將看到您的查詢。按一下選擇一個，「自訂列」將變為活動狀態。按一下“自訂列”選項以建立新列。
- 在建立自訂列的對話方塊中，提供該列的名稱。
- 在「自訂列公式」部分中，新增用於建立列的公式。例如，使用類似 的公式
[First Name]&""&[Last Name]。 Power Query 編輯器將驗證公式中是否有錯誤。
- 如果沒有錯誤，請按一下「確定」按鈕，編輯器將建立一個列。
- 若要使用功能，請重複這些步驟，直到出現「自訂列」彈出視窗。在「自訂列公式」部分中，新增一個函數，例如
Text.Upper([Full Name])，它將建立所有大寫的名稱。
- 若要完成新增列，請按一下「確定」按鈕以建立名稱為大寫的列。
這就是開始使用 Power Query 所需了解的全部內容。該工具可以非常輕鬆地根據需要在 Microsoft Excel 中轉換數據，因此您可以輕鬆分析並得出結論。它可用於組合不同的資料集、變更其格式以及執行其他操作。您甚至可以透過編輯器使用 Excel 函數和公式，這使得它更加有用。
發佈留言