最近，我的一個朋友找我請求幫助生成由一組給定單詞組成的所有可能的短語。 當您需要在搜索查詢中檢查所有可能的單詞排列時，在為在線廣告和 SEO 推廣編制關鍵字和短語列表時，可能會出現此類問題：
在數學中，這種操作稱為 笛卡爾積. 官方定義如下：集合 A 和 B 的笛卡爾積是所有對的集合，其中第一個分量屬於集合 A，第二個分量屬於集合 B。而且集合的元素可以都是數字和文字。
翻譯成人類語言，這意味著如果在集合 A 中我們有單詞“white”和“red”，而在集合 B 中是“BMW”和“Mercedes”，那麼在這兩個集合的笛卡爾積之後，我們輸出是所有可能的短語變體的集合，由兩個列表的單詞組成：
- 白色寶馬
- 紅色寶馬
- 白色奔馳
- 紅色奔馳
… 即我們所需要的。 讓我們看一下在 Excel 中解決此任務的幾種方法。
方法 1. 公式
讓我們從公式開始。 假設作為初始數據，我們分別在 A、B 和 C 列中有三個原始單詞列表，並且每個列表中的元素數量可以變化：
首先，讓我們用索引製作三列，即每個列表中所有可能組合的單詞的序數。 第一行單位 (E2:G2) 將手動輸入，其餘的我們將使用以下公式：
這裡的邏輯很簡單：如果上一級單元格中的索引已經到了鍊錶的末尾，即等於函數計算的鍊錶中的元素個數 COUNT (計數)，然後我們重新開始編號。 否則，我們將索引增加 1。特別注意巧妙地用美元符號 ($) 固定範圍，以便您可以向下和向右複製公式。
現在我們已經從每個列表中獲得了所需單詞的序數，我們可以使用函數提取單詞本身 INDEX （指數） 分成三個單獨的列：
如果你以前在工作中沒有遇到過這個函數，那麼我強烈建議你至少對角學習它——它在很多情況下都有幫助，而且很有用（甚至更多！） VPR （VLOOKUP）.
好吧，在那之後，只需要使用連接符號 (&) 逐行粘合生成的片段：
… 或者（如果您有最新版本的 Excel）具有方便的功能 結合 （文本連接），它可以通過給定的分隔符（空格）粘合指定單元格的全部內容：
方法2.通過Power Query
Power Query 是一個強大的 Microsoft Excel 插件，它執行兩項主要任務：1. 從幾乎任何外部源將數據加載到 Excel 中，以及 2. 對加載的表進行各種轉換。 Power Query 已內置在 Excel 2016-2019 中，對於 Excel 2010-2013，它作為單獨的加載項安裝（您可以從 Microsoft 官方網站免費下載）。 如果您還沒有開始在工作中使用 Power Query，那麼是時候考慮一下了，因為上面描述的轉換可以輕鬆自然地完成，只需幾個動作。
首先，讓我們將源列表作為單獨的查詢加載到 Power Query 中。 為此，對於每個表，請執行以下步驟：
- 讓我們通過按鈕將表格變成“智能”表格 格式化為表格 選項卡 首頁
（首頁——表格格式） 或鍵盤快捷鍵 按Ctrl+T. 每個表都會自動命名 表 1,2,3、XNUMX、XNUMX…, 但是，如果需要，可以在選項卡上更改 構造函數 （設計）.
- 在表格中設置活動單元格後，按下按鈕 從表 （從表） 選項卡 數據 （日期） 或在選項卡上 電源查詢 （如果您將其作為 Excel 2010-2013 的單獨加載項安裝）。
- 在打開的查詢編輯器窗口中，選擇命令 主頁 — 關閉並加載 — 關閉並加載... （首頁—關閉&加載—關閉&加載到..） 然後選項 只需創建一個連接 （僅創建連接）. 這會將加載的表留在內存中，並允許將來訪問它。
如果你做的一切都正確，那麼右側面板中的輸出應該是模式下的三個請求 僅連接 使用我們的表名：
現在右鍵單擊第一個查詢並選擇命令 Link （參考）製作一個可更新的副本，然後通過命令向數據添加一個附加列 添加列 ž – 自定義列 （添加列-ž自定義列）. 在公式輸入窗口中，輸入新列的名稱（例如，Fragment2）和一個極其簡單的表達式作為公式：
=表2
… 即，換句話說，第二個查詢的名稱：
點擊後 OK 我們將看到一個新列，在每個單元格中都有一個嵌套表格，其中包含第二個表格中的短語（如果單擊單詞旁邊的單元格背景，您可以看到這些表格的內容 枱燈):
仍然可以使用結果列標題中帶有雙箭頭的按鈕來擴展這些嵌套表的所有內容並取消選中 使用原始列名稱作為前綴 （使用原始列名作為前綴）:
…我們從前兩組中得到所有可能的元素組合：
此外，一切都是相似的。 使用公式添加另一個計算列：
=表3
…，然後再次展開嵌套表——現在我們已經擁有了從三個集合中分別排列單詞的所有可能選項：
它仍然從左到右選擇所有三列，保持 按Ctrl, 並使用命令連接它們的內容，用空格分隔 合併列 （合併列） 從標籤 轉型 （轉換）:
可以使用熟悉的命令將生成的結果卸載回工作表上 主頁 — 關閉並加載 — 關閉並加載... （首頁—關閉&加載—關閉&加載到..）:
如果將來我們的源表中發生了帶有片段的更改，那麼只需通過右鍵單擊結果表並選擇命令來更新生成的查詢就足夠了 更新並保存 （刷新） 或按鍵盤快捷鍵 按Ctrl+其他+F5.
- 什麼是 Power Query、Power Pivot、Power Map 和 Power BI，它們為什麼需要 Excel 用戶
- 在 Power Query 中創建甘特圖
- INDEX 函數的 5 種使用方法