假設您有一個列表，其中寫入了不同程度的“直截了當”的初始數據——例如，地址或公司名稱：
 |  |
可以清楚地看到，同一個城市或公司在這里以各種變體形式出現，這顯然會在將來使用這些表格時產生很多問題。 如果你稍微思考一下，你可以從其他領域找到很多類似任務的例子。
現在想像一下，這些歪曲的數據會定期出現在你面前，也就是說，這不是一次性的“手動修復它，忘記它”的故事，而是一個定期和大量單元格中的問題。
該怎麼辦？ 不要通過“查找和替換”框或單擊手動將彎曲的文本替換為正確的文本 100500 次 按Ctrl+H?
在這種情況下，首先想到的是根據預編譯的參考書匹配不正確和正確的選項進行批量替換 - 像這樣：
不幸的是，隨著此類任務的普遍流行，Microsoft Excel 沒有簡單的內置方法來解決它。 首先，讓我們弄清楚如何使用公式來執行此操作，而不涉及 VBA 或 Power Query 中宏形式的“重型火砲”。
案例一、批量全換
讓我們從一個相對簡單的案例開始——您需要用新的文本替換舊的歪曲文本。 充分.
假設我們有兩個表：
在第一個 - 公司的原始雜色名稱。 在第二個 - 通信參考書。 如果我們在第一個表中的公司名稱中找到該列中的任何單詞 要，那麼你需要用正確的名字完全替換這個歪曲的名字——從列 替代 第二個查找表。
為了方便：
- 兩個表都使用鍵盤快捷鍵轉換為動態（“智能”） 按Ctrl+T 或團隊 插入 - 表格 （插入 - 表格）.
- 在出現的選項卡上 構造函數 （設計） 第一個表名為 數據，以及第二個參考表—— 替換.
為了解釋公式的邏輯，讓我們從遠處走一點。
以 A2 單元格中的第一家公司為例，暫時忘記了其餘的公司，讓我們嘗試從列中確定哪個選項 要 在那裡見面。 為此，請選擇工作表空閒部分中的任何空單元格並在此處輸入函數 尋找 （尋找）:
該函數判斷是否包含給定的子字符串（第一個參數是列中的所有值 要) 到源文本（數據表中的第一個公司）中，並且應該輸出找到文本的字符的序號，或者如果未找到子字符串則輸出錯誤。
這裡的技巧是，由於我們指定的不是一個，而是幾個值作為第一個參數，所以這個函數也將返回結果不是一個值，而是一個包含 3 個元素的數組。 如果你沒有支持動態數組的最新版 Office 365，那麼在輸入這個公式後點擊 Enter 你會在工作表上看到這個數組：
如果您有以前版本的 Excel，然後單擊 Enter 我們只會看到結果數組中的第一個值，即錯誤#VALUE！ （＃價值！）.
你不應該害怕🙂事實上，我們的公式有效，如果您在公式欄中選擇輸入的函數並按下鍵，您仍然可以看到整個結果數組 F9（只是不要忘記按 ESC回到公式）：
得到的結果數組意味著在原來的歪曲公司名稱中 (GK 莫羅茲科 OAO) 一列中所有值的 要 只找到第二個 （莫羅茲科）, 從連續的第 4 個字符開始。
現在讓我們在公式中添加一個函數 查看（抬頭）:
這個函數有三個參數：
- 期望值 – 您可以使用任何足夠大的數字（主要是它超過了源數據中任何文本的長度）
- 查看_向量 – 我們正在尋找所需值的範圍或數組。 這裡是之前介紹的功能 尋找，它返回一個數組 {#VALUE!:4:#VALUE!}
- 向量_結果 – 如果在相應的單元格中找到所需的值，我們想要返回值的範圍。 以下是列中的正確名稱 替代 我們的參考表。
這裡主要和不明顯的特徵是函數 查看 如果沒有完全匹配，總是尋找最近的最小（前一個）值. 因此，通過指定任何大數字（例如，9999）作為所需值，我們將強制 查看 在數組 {#VALUE!:4:#VALUE!} 中找到最接近的最小數字 (4) 的單元格，並從結果向量中返回相應的值，即從列中返回正確的公司名稱 替代.
第二個細微差別是，從技術上講，我們的公式是一個數組公式，因為函數 尋找 返回的結果不是一個，而是一個包含三個值的數組。 但由於函數 查看 支持開箱即用的數組，那麼我們不必將這個公式作為經典的數組公式輸入 - 使用鍵盤快捷鍵 按Ctrl+轉移+Enter. 一個簡單的就足夠了 Enter.
就這樣。 希望你能明白其中的邏輯。
剩下的是將完成的公式轉移到該列的第一個單元格 B2 固定 ——我們的任務就解決了！
當然，對於普通（非智能）表，這個公式也很有效（只是不要忘記關鍵 F4 並修復相關鏈接）：
案例二、批量部分替換
這個案子有點棘手。 同樣，我們有兩個“智能”表：
第一個寫錯地址需要更正的表（我稱之為 Data2）。 第二個表是參考書，根據它你需要對地址內的子字符串進行部分替換（我稱這個表 換人2).
這裡的根本區別是您只需要替換原始數據的一部分——例如，第一個地址有一個不正確的 “英石。 聖彼得堡” 在右側 “英石。 聖彼得堡”，保留地址的其餘部分（郵政編碼、街道、房屋）。
完成的公式會是這個樣子（為了便於理解，我把它分成了多少行使用 其他+Enter):
這裡的主要工作是由標準的 Excel 文本函數完成的 替代 （代替），它有 3 個參數：
- 源文本 – 地址列中的第一個歪曲地址
- 我們在尋找什麼——在這裡我們使用函數的技巧 查看 （抬頭）從以前的方式從列中提取值 要，它作為一個片段包含在彎曲地址中。
- 用什麼替換——同理我們從列中找到對應的正確值 替代.
輸入這個公式 按Ctrl+轉移+Enter 這裡也不需要，儘管它實際上是一個數組公式。
可以清楚地看到（參見上圖中的 #N/A 錯誤），儘管如此優雅，但這樣的公式有幾個缺點：
- 功能 SUBSTITUTE 區分大小寫，所以在替換錶中沒有找到倒數第二行的“Spb”。 要解決此問題，您可以使用該功能 扎門尼特 （代替），或初步將兩個表放在同一個寄存器中。
- 如果文本最初是正確的或在其中 沒有要替換的片段 （最後一行），那麼我們的公式會拋出錯誤。 這個時刻可以通過使用函數攔截和替換錯誤來中和 錯誤 (如果錯誤):
- 如果原文包含 一次從目錄中提取幾個片段，那麼我們的公式只替換最後一個（在第 8 行，Ligovsky «Avenue« 變成 “公關”， 但 “S-鉛” on “英石。 聖彼得堡” 不再，因為 “S-Pb”在目錄中較高）。 這個問題可以通過重新運行我們自己的公式來解決，但是已經沿著列 固定:
在某些地方並不完美和繁瑣，但比相同的手動更換要好得多，對吧？ 🙂
PS
在下一篇文章中，我們將弄清楚如何使用宏和 Power Query 實現這種批量替換。
- SUBSTITUTE 函數如何替換文本
- 使用 EXACT 函數查找精確的文本匹配
- 區分大小寫的搜索和替換（區分大小寫的 VLOOKUP）