內容
如何根據參考列表用公式快速批量替換文本——我們已經整理好了。 現在讓我們嘗試在 Power Query 中執行此操作。
經常發生 表演 這個任務比解釋要容易得多 為什麼 它有效，但讓我們嘗試兩者都做🙂
因此，我們有兩個使用鍵盤快捷鍵從普通範圍創建的“智能”動態表 按Ctrl+T 或團隊 主頁 – 格式為表格 （首頁——表格格式）:
我打電話給第一張桌子 數據，第二張表—— 目錄使用領域 表名稱 （表名） 選項卡 構造函數 （設計）.
任務：替換錶中的地址 數據 列中的所有出現 要 手冊 到列中相應的正確對應項 替代. 單元格中的其余文本應保持不變。
步驟 1. 將目錄加載到 Power Query 中並將其轉換為列表
將活動單元格設置到參考表中的任何位置後，單擊選項卡 數據 （日期）或在選項卡上 電源查詢 （如果您有舊版本的 Excel 並且您將 Power Query 作為加載項安裝在單獨的選項卡上）在按鈕上 從表/範圍 （從表/範圍）.
引用表將加載到 Power Query 查詢編輯器中：
為了不干擾，自動添加了一個步驟 修改型 （更改類型） 在右側面板中，可以安全地刪除應用的步驟，只留下步驟 資源 （資源）:
現在，為了進行進一步的轉換和替換，我們需要把這個表變成一個列表（list）。
抒情題外話
- 枱燈 是由若干行和列組成的二維數組。
- 記錄（記錄） – 一維數組字符串，由幾個帶名稱的字段元素組成，例如 [姓名 = “瑪莎”，性別 = “f”，年齡 = 25]
- 名單 – 一維數組列，由多個元素組成，例如 {1、2、3、10、42} or {“信望愛”}
為了解決我們的問題，我們將主要對類型感興趣 名單.
這裡的技巧是 Power Query 中的列表項不僅可以是平庸的數字或文本，還可以是其他列表或記錄。 正是在這樣一個棘手的列表（list）中，由我們需要翻我們的目錄的記錄（records）組成。 在 Power Query 語法符號（方括號中的條目，大括號中的列表）中，這看起來像：
{
[查找=“聖。 聖彼得堡”，替換 = “聖。 聖彼得堡”] ,
[查找=“聖。 聖彼得堡”，替換 = “聖。 聖彼得堡”] ,
[查找=“彼得”，替換=“聖。 聖彼得堡”] ,
等。
}
這種轉換是使用 Power Query 中內置的 M 語言的特殊功能執行的 - 表.ToRecords. 要直接在公式欄中應用它，請將此函數添加到那裡的步驟代碼中 資源.
它是：
後：
添加 Table.ToRecords 函數後，我們的表的外觀會發生變化——它會變成一個記錄列表。 通過單擊任何單詞旁邊的單元格背景，可以在視圖窗格的底部查看各個記錄的內容 Record （但不是一個字！）
除了上述之外，再添加一個筆劃是有意義的——緩存（緩衝）我們創建的列表。 這將強制 Power Query 將我們的查找列表一次加載到內存中，並且當我們稍後訪問它以替換它時不再重新計算它。 為此，將我們的公式包裝在另一個函數中—— 列表緩衝區:
這種緩存將顯著提高速度（數倍！），並清除大量初始數據。
這樣就完成了手冊的準備工作。
它仍然可以點擊 主頁 – 關閉並加載 – 關閉並加載到… （首頁—關閉&加載—關閉&加載到..）， 選擇一個選項 只需創建一個連接 （僅創建連接） 並返回 Excel。
步驟 2. 加載數據表
這裡的一切都是陳詞濫調。 和以前的參考書一樣，我們起身到表格中的任何地方，點擊標籤 數據 按鍵 從表/範圍 和我們的桌子 數據 進入 Power Query。 自動添加步驟 修改型 （更改類型） 您還可以刪除：
不需要做任何特殊的準備工作，我們繼續做最重要的事情。
步驟 3. 使用 List.Accumulate 函數執行替換
讓我們使用命令將計算列添加到我們的數據表中 添加列 - 自定義列 （添加列 - 自定義列）: 並在打開的窗口中輸入添加列的名稱（例如， 更正地址) 和我們的神奇功能 列表.累積:
它仍然可以點擊 OK – 我們得到一個包含替換的列：
注意：
- 由於 Power Query 區分大小寫，因此倒數第二行沒有替換，因為在目錄中我們有“SPb”，而不是“SPb”。
- 如果源數據中有多個子字符串要同時替換（例如，在第 7 行中，您需要同時替換“S-Pb”和“Prospectus”），那麼這不會產生任何問題（與替換來自以前的方法）。
- 如果源文本中沒有可替換的內容（第 9 行），則不會發生錯誤（再次與公式替換不同）。
這樣一個請求的速度非常非常不錯。 例如，對於一個大小為 5000 行的初始數據表，此查詢在不到一秒的時間內更新（順便說一下，沒有緩衝大約 3 秒！）
List.Accumulate 函數的工作原理
原則上，這可能是本文的結尾（我寫，你讀）。 如果您不僅想能夠，而且還想了解它是如何“在引擎蓋下”工作的，那麼您將不得不更深入地研究兔子洞並處理 List.Accumulate 函數，它完成了所有的批量替換為我們工作。
這個函數的語法是：
=列表.累積（表, 種子, 累加器)
哪裡
- 表 是我們正在迭代其元素的列表。
- 種子 – 初始狀態
- 累加器 – 對列表的下一個元素執行某些操作（數學、文本等）並將處理結果累積到特殊變量中的函數。
通常，在 Power Query 中編寫函數的語法如下所示：
(argument1, argument2, ... argumentN) => 一些帶有參數的動作
例如，求和函數可以表示為：
(a, b) => a + b
對於 List.Accumulate ，這個累加器函數有兩個必需的參數（它們可以命名任何東西，但通常的名稱是 為 и 當前，如該函數的官方幫助中所示，其中：
- 為 – 結果被累加的變量（它的初始值是上面提到的那個 種子)
- 當前 – 列表中的下一個迭代值 表
比如我們看一下下面的構造邏輯的步驟：
=列表.累積（{3，2，5}, 10, （狀態，當前）=> 狀態 + 當前)
- 可變值 為 設置為等於初始參數 種子Ie 狀態 = 10
- 我們取列表的第一個元素 (當前 = 3) 並將其添加到變量中 為 （十）。 我們得到 狀態 = 13.
- 我們取列表的第二個元素 (當前 = 2) 並將其與變量中的當前累積值相加 為 （十）。 我們得到 狀態 = 15.
- 我們取列表的第三個元素 (當前 = 5) 並將其與變量中的當前累積值相加 為 （十）。 我們得到 狀態 = 20.
這是最新積累的 為 該值是我們的 List.Accumulate 函數並作為結果輸出：
如果您有一點幻想，那麼使用 List.Accumulate 函數，您可以模擬，例如 Excel 函數 CONCATENATE（在 Power Query 中，它的類似物稱為 文本合併) 使用表達式：
甚至搜索最大值（模仿 Excel 的 MAX 函數，在 Power Query 中稱為 列表.Max):
但是，List.Accumulate 的主要功能是不僅可以處理簡單的文本或數字列表作為參數，還可以處理更複雜的對象——例如，lists-from-lists 或 lists-from-records（你好，Directory！）
讓我們再看一下在我們的問題中執行替換的構造：
列表.累積（目錄, [地址], (state,current) => Text.Replace(state, current[Find], current[Replace]) )
這裡到底發生了什麼？
- 作為初始值 (種子) 我們從列中取出第一個笨拙的文本 [地址] 我們的桌子： 199034，聖彼得堡，海峽。 貝林加，D。 1
- 然後 List.Accumulate 逐一迭代列表的元素—— 手冊. 該列表的每個元素都是一個記錄，由一對字段“要查找的內容 - 替換的內容”組成，換句話說，就是目錄中的下一行。
- 累加器函數放入一個變量 為 初始值（第一個地址 199034，聖彼得堡，海峽。 貝林加，D。 1) 並對其執行累加器功能 - 使用標準 M 功能的替換操作 文本替換 （類似於 Excel 的 SUBSTITUTE 函數）。 它的語法是：
Text.Replace( 原文，我們要找的，我們要替換的)
在這裡我們有：
- 為 是我們的髒地址，位於 為 （從那裡到達 種子)
- 當前[搜索] – 字段值 要 從列表的下一個迭代條目 目錄，它位於變量中 當前
- 當前[替換] – 字段值 替代 從列表的下一個迭代條目 目錄躺在 當前
因此，對於每個地址，每次都會運行目錄中所有行的完整枚舉循環，將 [Find] 字段中的文本替換為 [Replace] 字段中的值。
希望你明白了🙂
- 使用公式批量替換列表中的文本
- Power Query 中的正則表達式 (RegExp)