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
計數
對於這一部分計算姓名的個數。使用的函式組合是”LEN函式+SUBSTITUTE函式“。
在G7單元格中輸入公式:=LEN(F7)-LEN(SUBSTITUTE(F7,",",""))+1,按確定鍵後向下填充。如下圖所示:
注意:上述題目中的逗號是在中文狀態下的逗號。
解析:LEN函式是計算一個單元格中或者一個字串的長度,LEN(F7)是先計算出F7單元格中字元的長度;SUBSTITUTE(F7,",","")是將F7單元格中的逗號替換成空白;然後使用LEN函式測算出替換掉逗號的長度是多少,其公式為LEN(SUBSTITUTE(F7,",","")),最後使用其原有的長度減去替換後的長度。需要說明的是如果有四個人那麼就有三個逗號,所以在計算人數的時候還在加上1.本公式的實質就是計算單元格中的逗號有多少個。
03
附錄-其他參考方法
上述問題中呢,會使得到VLOOKUP函式以及輔助列的使用,接下來呢,小必老師再給大家演示兩種方法,一種是使用PQ的方法,一種是使用PP的方法,但是都是作為參考,大家只要把上面的VLOOKUP方法學會就行。
方法1:PQ法
注意:該方法的好處就是如果有新的資料增加或者減少的時候可以自動重新整理一勞永逸的方法。上面用到了PQ的專屬函式即M函式。
方法2:PP法
除了上面給大家介紹的Power Query的方法以外呢,也可以使用Power Pivot來解決這個問題。
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
計數
對於這一部分計算姓名的個數。使用的函式組合是”LEN函式+SUBSTITUTE函式“。
在G7單元格中輸入公式:=LEN(F7)-LEN(SUBSTITUTE(F7,",",""))+1,按確定鍵後向下填充。如下圖所示:
注意:上述題目中的逗號是在中文狀態下的逗號。
解析:LEN函式是計算一個單元格中或者一個字串的長度,LEN(F7)是先計算出F7單元格中字元的長度;SUBSTITUTE(F7,",","")是將F7單元格中的逗號替換成空白;然後使用LEN函式測算出替換掉逗號的長度是多少,其公式為LEN(SUBSTITUTE(F7,",","")),最後使用其原有的長度減去替換後的長度。需要說明的是如果有四個人那麼就有三個逗號,所以在計算人數的時候還在加上1.本公式的實質就是計算單元格中的逗號有多少個。
03
附錄-其他參考方法
上述問題中呢,會使得到VLOOKUP函式以及輔助列的使用,接下來呢,小必老師再給大家演示兩種方法,一種是使用PQ的方法,一種是使用PP的方法,但是都是作為參考,大家只要把上面的VLOOKUP方法學會就行。
方法1:PQ法
注意:該方法的好處就是如果有新的資料增加或者減少的時候可以自動重新整理一勞永逸的方法。上面用到了PQ的專屬函式即M函式。
方法2:PP法
除了上面給大家介紹的Power Query的方法以外呢,也可以使用Power Pivot來解決這個問題。