excel怎麼設置某個單元格滿足條件（excel5種方法快速地將多個符合條件的內容歸類至一個單元格中）

2023-04-16 04:07:33 1

在前幾期的文章中，小必老師給大家講解了如何使用Power Query的功能將具有相同屬性/類型的單元格歸納到一個單元格中，並用間隔符將其隔開。對於這個問題呢，其實還有很多種方法，經小必老師不斷地積累與思考，最終給大家歸納了五種歸納的方法，這5種方法從簡單到一般，能適合每個Excel學習者及愛好者，分別是：

下面是一張各個部門的人員明細表：

根據領導的要求呢，要把每個部門的人員放到一起，還要進行要數的統計，結果如下圖所示：

方法01

VLOOKUP函數 輔助列

具體的操作方法如下：

Step-01：選中A列的任意一個單元格，單擊【數據】，選擇任意一個方式進行排序（升序與降序都行），如下圖所示：

注意：此處的排序是相當地重要的一個步驟，如果不排序那麼後面的步驟的結果就不能正常地顯示。

Step-02：在C列建立一個輔助列，在C2單元格中輸入公式：

=B2&IFERROR("，"&VLOOKUP(A2,A3:C$17,3,0),"")，按確定鍵後向下填充。

注意：上述公式中的A3:C$17一定在注意，就是在查詢的區域一定是數據區域最後一行的下一行，哪怕是多一行都行，多幾行也無所謂，但是就是不能少，同時要對行標進行鎖定，即在行標上加上"$"符號，如上述公式中的C$17。如果是第二行的公式，那麼就從第三行的區域開始選取，如A3。

解析：上述題目中使用VLOOKUP函數從當前行的下一次開始查找，如果有查找到的內容，就用」，「連接，如果沒有就為空白。

Step-03：在F12單元格中正常使用公式：=VLOOKUP(E7,A:C,3,0)，按確定鍵後向下填充即可得到。

溫馨提示：除了VLOOKUP函數，LOOKUP函數也能完成上述的問題。見方法02.

方法02

LOOKUP函數 輔助列

具體的操作方法如下：

Step-01：選中A列的任意一個單元格，單擊【數據】，選擇任意一個方式進行排序（升序與降序都行），如下圖所示：

注意：此處的排序是相當地重要的一個步驟，如果不排序那麼後面的步驟的結果就不能正常地顯示。

Step-02：在C列建立一個輔助列，在C2單元格中輸入公式：

=IF(A2=A1,C1&"，"&B2,B2)，按確定鍵後向下填充。

Step-03：在F12單元格中正常使用公式：

=LOOKUP(1,0/(E7=$A$2:$A$16),$C$2:$C$16)，按確定鍵後向下填充即可得到。

注意：關於以上公式的意義在這裡再不給大家做過多的解釋，只要大家記住套路即可。以上公式的用法屬於公式的高階用法。

方法03

TEXTJION IF函數組合

具體的操作方法如下：

在E7單元格中輸入公式：{=TEXTJOIN("，",1,IF(D7=A:A,B:B,""))}，按組合鍵完成填充。如下圖所示：

注意：對於以上公式的兩邊的大括號不是手動添加上去的，而是通過按組合鍵自動加上的，表示數組公式，但是加大括號的不一定是數組公式。另外，以上函數只在EXcel2019版本及Office365版本中才有的功能。

方法04

Excel Power Query

除了以上的三種方法以外呢，還有更好的方法。那就是Power Query的方法。

具體的操作方法如下：

Step-01：選中任意一個單元格，單擊【數據】-【從表格/區域】，在彈出的對話框中選擇【表包含行標題】-【確定】，如下圖所示：

Step-02：在彈出的界面中選擇【分組依據】，在彈出的對話框中的【分組依據】中選擇「部門」，在【操作】裡選擇「求和」，在【柱】裡選擇「姓名」，如下圖所示：

Step-03：然後將原公式：

= Table.Group(更改的類型, {"部門"}, {{"計數", each List.Sum([姓名]), type text}})

修改為：

= Table.Group(更改的類型, {"部門"}, {{"計數", each Text.Combine([姓名],","), type text}})

然後按Enter鍵確定即可。結果如上。

Step-04：單擊【關閉並上載至】，在彈出的對話框中選擇要存放的位置。此步過程略過。全部的具體的操作見下面的動態圖演示。

注意：該方法的好處就是如果有新的數據增加或者減少的時候可以自動刷新一勞永逸的方法。上面用到了PQ的專屬函數即M函數。在寫M公式的時候一定要區分大小寫。

方法05

Excel Power Pivot

還有一種方法同Power Query是一樣的神奇，那就是使用Power Pivot。具體方法如下：

準備工作：如果沒有【開發工具】選項卡的小夥伴們，右擊任意一個選項卡，單擊【自定義功能區】，在彈出的對話框中選擇 【主選項卡】，在右邊的選項中勾選【開發工具】，最後單擊【確定】即可。如果沒有有【開發工具】沒有加載Power Pivot的小夥伴們可以單擊【開發工具】下面的【COM加載項】，在彈出的對話框中選擇【Microsoft Power Pivot for Excel】，然後單擊【確定】，如下圖所示：

Step-01：選中任意一個單元格，單擊【數據】-【Powe Pivot】-【添加到數據模型】，如下圖所示：

Step-02：在公式編輯欄中輸入公式，按enter鍵完成。如下圖所示：

=CONCATENATEX('表2','表2'[姓名],"，")

Step-03：然後單擊【插入】-【數據透視表】，在彈出的對話框中選擇【使用此工作簿】

Step-04：將「部門」拖放至【行】，將「度量值1」拖放至【值】，設計表格布局中取消行列合計，如下圖所示：

注意：如果有數據的增減修改了的，在透視表的結果中右鍵刷新即可。具體的操作過程如下圖所示：

以上5種方法呢，需要說明的呢，方法1與方法2都是適用於所有版本的Excel，而方法3是只能在Excel2019版本及Office365的版本中才能做的，因為TEXTJOIN函數屬於新增函數；方法4與方法5在Excel2013版本與Excel2010版本中只能通過官方的插件來實現，大家可以在微軟的官網上下載相應的插件Power Query for Excel與Power Pivot for Excel的插件，而在Excel2016版本及以上包括Office365的版本都可以在軟體內置的功能中找到其功能。

關於方法的選擇：

1、版本的使用方面：對於使用Excel2019版本以上的小夥伴們可以選擇以上的所有的方法，而使用Excel2016版本及其以上的用戶可以選擇除方法3以外的所有的方法；而對於Excel版本在2010及以上並裝有插件Power Pivot插件的可以使用上述除方法3以外的所有的方法；而對於2013版本及其以下的用戶（未裝有插件）的小夥伴們可以使用方法1與方法2；

2、效率優先：對於追求效率方面來說，建議大家使用方法4與方法5，這兩種方法可以不用提取同類屬性，直接可以使用，而在原始數據胡增減改動的情形下可以直接右鍵刷新即可，不用拖動更改；但是以上兩種方法靈活性較差；

3、靈活性方面：對於追求靈活性方面的小夥伴可以使用前三種方法，這三種方法可以靈活地在工作表中進行處理，但是由於添加了輔助列，會造成效率低下，方法3使用了數組函數，在數據量比較大的情況下會出現Excel卡頓的現象。