如何在 Excel 中使用超級查詢

儘管技術工具提供了各種各樣的資料管理解決方案，但整合多個來源的資訊仍然是一項挑戰。 理解不同的數據表通常依賴手動操作。

這就是 Power query 可以提供幫助的地方，它可以將來自不同來源的資料整合到一個整合視圖中。

身為行銷顧問，我與客戶企業的多個團隊合作。 例如，為了真實了解 SaaS 公司收入管道的情況，我通常需要來自行銷、銷售、客戶成功和產品的資料。

然而，我需要的數據通常是在多個位置以不同的格式收集的。 將所有內容拼湊在一起意味著將所有內容以相同的格式放在一個位置，並且能夠以任何需要的方式進行操作。

所以，多年來，power query 對我來說非常有用，而且它並不像你想像的那麼複雜。

透過 power query，您可以匯入、清理、轉換和合併來自多個來源的資料集。 一旦您知道如何使用它，收集和解釋來自不同來源的數據就會變得容易得多。

什麼是電量查詢？

Power query 本質上是一種可以在 Excel 中使用的技術，用於將 Excel 電子表格中的資料集連接在一起。 您可以使用 Power Query 從各種來源提取數據，包括網頁、資料庫、其他電子表格和多種文件類型。

匯入資料後，您還可以使用強大的查詢編輯器來清理和轉換資料。 最後，您可以將轉換後的資料匯入現有的電子表格中。

匯入資料後，您可以在 Power Query 編輯器中使用的常見操作包括篩選、刪除重複項、分割列、格式化資料和合併資料。

核心好處是，使用 Power query 可以在幾分鐘內完成手動需要花費數小時才能完成的任務。 此外，當資料來源更新時，您可以透過點擊按鈕再次重複使用相同的清理或轉換操作。

Excel 中的任何進階功能都存在一定的學習曲線。 但是，雖然您可能需要幾次嘗試才能熟悉電源查詢，但一旦您知道所有內容在哪裡，它就會有一個非常用戶友好的介面。

如何在 Excel 中使用超級查詢

當學習如何使用像 power query 這樣的東西時，最有用的方法是使用範例。 以下步驟可套用於任何類型的資料集，並且突出顯示的功能可以根據需要替換為其他使用查詢的方式。

在此範例中，我將來自兩個不同地理區域的銷售數據匯總在一起進行分析。 這些數據來自美國和英國，兩個電子表格的格式和貨幣的書寫方式和格式都不同。

我的目標是將這些資料集合併在一張表中，並在一個地方清理並輕鬆解釋和分析資料。

步驟一、打開Excel，進入電量查詢。

首先，開啟您將使用的主電子表格。 在這個例子中，我打開了美國銷售電子表格，因為這是我想要清理和合併資料的地方。

在主功能區中，按一下「資料」。 您將看到「取得資料 (Power Query)」作為資料功能區中的第一項。

步驟 2. 匯入您的資料。

點擊下拉式選單以存取您的電源查詢選項。

在第一步中，我們要點擊“獲取數據”，以便我們可以使用英國銷售電子表格中的數據開始查詢。

點擊此選項將打開一個窗口，您可以在其中選擇資料來源。 正如您將看到的，可以從各種來源提取數據，包括共享的 SharePoint 文件。

在本例中，我們選擇「Excel 工作簿」來存取本機儲存在我的電腦上的檔案。 做出選擇後，您只需瀏覽文件以找到正確的電子表格，然後點擊「取得資料」。

然後，在接下來的螢幕上，點擊「下一步」。 從這裡，您可以選擇要匯入的特定工作表。 在點擊「載入」完成匯入之前，您將能夠預覽資料。

如果您在此階段選擇“載入”，power query 會將資料匯入現有電子表格的新分頁中。

從那裡，您可以選擇「資料」和「啟動 Power Query 編輯器」來執行批次清理或更新匯入的資料。

步驟 3. 清理匯入的資料。

如果您希望在匯入資料之前清理數據，則可以在匯入階段執行此操作。

您可以選擇“轉換資料”，而不是在匯入過程中選擇“載入”，這會像上一步一樣開啟 Power Query 編輯器。

例如，英國銷售表包含與美國銷售表相同的數據，但格式不正確。 貨幣格式不適用於貨幣單元格。

因此，在 Power Query 編輯器中，我可以在匯入資料之前選擇「轉換」標籤來編輯資料（如果我先匯入資料然後開啟 Power Query 編輯器，也可以執行此操作）。

正如您所看到的，這裡有大量的選項可供您選擇。 在本例中，我將選擇“貨幣”列並選擇“資料類型”。

從那裡，我可以選擇貨幣選項並將值更改為美元。

步驟 4. 載入轉換後的資料。

最後一步是將這些經過清理和轉換的資料加入我現有的電子表格中。 在 Power Query 編輯器中，導覽回「首頁」選項卡，然後按一下「關閉並載入」。

Excel 現在會將此查詢載入到電子表格的新分頁中。

這個簡單的逐步過程向您展示如何開始存取和使用 power query。

然而，由於您可以匯入和清理的資料種類繁多，以及您可以執行的轉換和資料操作類型多樣，因此強大的查詢用例幾乎是無窮無盡的。

那麼，讓我們來看看一些範例，了解可以使用 power query 執行哪些操作。

電源查詢範例

合併多個電子表格中的數據

無論您是使用自己創建的多個工作簿還是接收其他人發送的文件，將資料合併到一張工作表中的需求都是一個相當常見的問題。

強大的查詢使這成為一項非常簡單的任務。

首先，請確保要合併的所有工作簿都保存在同一資料夾中。 如果可能，請確保它們在具有相同的列標題方面全部匹配。

開啟 Excel 到空白工作簿，並使用「取得資料」選項存取進階查詢。

選擇您的資料來源作為“Excel 工作簿”並選擇您的第一個檔案。 如果您的工作表的格式都相同，則無需在此處執行任何其他操作。 只需點擊“加載”即可。

接下來，您將匯入其他電子表格。 但是，您不是以完全相同的方式執行第一步，而是將其他工作表附加到您剛剛建立的現有查詢中。

在工作表中，再次開啟 power query，然後在匯入檔案時從清單中選擇第二個工作簿。 不要點擊“載入”，而是選擇“轉換資料”選項。

現在，您應該在左側面板的 Power Query 編輯器中看到兩個可用的查詢。

在 Power Query 編輯器功能區的右側，您將看到一個「組合」按鈕，其中包含一個下拉列表，其中包含用於合併或附加查詢的選項。

從下拉清單中選擇「將查詢追加為新查詢」。 選擇您想要將一個表附加到另一個表的末尾的表，然後按一下「確定」。

Power query 會將兩個表格一起附加到工作表的一個標籤中。 您可以根據需要使用兩個電子表格或多個電子表格執行此操作。

導入前清理數據

有時，您可能會處理混亂的電子表格。 格式和樣式到處都是，您需要在匯入之前確保它盡可能乾淨整潔。

強大的查詢使匯入資料過程變得簡單，因此您無需在事後花時間手動清理資料。

一個簡單的例子是電子表格，其中一些文本是大寫的，而另一些則不是。 因此，目標是在將所有內容新增至現有工作表之前將其轉換為句首字母大寫。

假設法國的銷售團隊已向我們發送了他們的銷售報告。 它看起來很棒，但“國家/地區 ID”列中的某些文本全部大寫，而另一些則不是。 另外，國家名稱中出現的隨機空格也需要修復，否則我們將很難將資料正確地提取到資料透視表和其他報告中。

在工作表中，開啟 power query 並匯入包含混亂資料的檔案。 點擊“轉換資料”而不是立即導入。

首先，找到大小寫混合且有多餘空格的欄位。 右鍵單擊該列，然後按一下“變換列”，然後按一下“文字變換”，最後按一下“修剪”。 這將消除任何可能幹擾資料操作的額外間距。

再次右鍵單擊該列，這一次，從選項中單擊“大寫每個單字”。 您可能需要先選擇“小寫”來修復所有大寫單詞，然後使用“大寫每個單字”選項。

現在，您可以按一下「載入」將清理後的資料匯入 Excel 工作表中。

逆透視/透視數據以提高分析能力

Excel 的另一個常見挑戰是接收資料的方式難以操作和分析，特別是當您想要切換列和行或「旋轉」資料時。

在此範例中，我們有調查數據，其中的欄位由受訪者解析。 理想情況下，我們希望將其與作為調查問題的欄位一起解析。

首先，開啟一個空白的 Excel 表並使用 power query 匯入工作表。

與前面的範例一樣，我們首先需要點擊“轉換資料”，而不是點擊“載入”。

現在 Power Query 編輯器已打開，導航到功能區中的「轉換」標籤。 一起選擇所有調查問題列，然後按一下“取消透視列”，然後選擇“僅取消透視選取的列”。

Power query 取得選定的欄位並依問題的屬性和值解析資料。 導航回“主頁”選項卡，然後點擊“關閉並載入”以使用電子表格中的未透視資料。

試試使用 Power Query

在眾多工具中，Excel 在排序、清理和轉換跨多個部門和團隊的日常業務運營中使用的資料類型方面是最強大的。

雖然可能需要一些時間來熟悉電源查詢等功能的所有特性和功能，但這是值得的。

即使使用僅包含大量資料的相對簡單的電子表格，強大的查詢也可以顯著減少清理和轉換時間，因此您可以更快地進行重要的分析和決策。