2018年10月12日 星期五

Excel-計算姓名清單中每個姓氏的人數(SUMPRODUCT)

最近在研究自己通訊錄上的連絡人時,好奇想要知道每個姓氏的人數份佈比率,所以利用樞紐分析表工具和公式分別處理一次,卻得到不同結果,進一步了解找出了自已在公式設計的錯誤之處。(參考下圖)
Excel-計算姓名清單中每個姓氏的人數(SUMPRODUCT)
在下圖中,A欄是所有連絡人的姓名,先利用公式取出其姓。
儲存格B2:=IF(LEN(A2)=4,LEFT(A2,2),LEFT(A2,1))
複製儲存格B2,往下各列貼上。
取用B欄內容執行樞紐分析表操作,得到以下的結果:
Excel-計算姓名清單中每個姓氏的人數(SUMPRODUCT)
選取A欄的全部資料,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名。
可以取用樞紐分析表會產生的不重覆的姓,
設計公式,儲存格D2:
=IF(LEN(C2)=2,SUMPRODUCT((LEFT(姓名,2)=C2)*1),SUMPRODUCT((
LEFT(姓名,1)=C2)*1))
複製儲存格D2,往下各列貼上。
(1) SUMPRODUCT((LEFT(姓名,2)=C2)*1)
如果C欄中的「姓」的字數為2,則找出姓名陣列中前 2 個字和儲存格C2相同的數量。
(2) SUMPRODUCT((LEFT(姓名,1)=C2)*1)
如果C欄中的「姓」的字數為1,則找出姓名陣列中前 1 個字和儲存格C2相同的數量。
Excel-計算姓名清單中每個姓氏的人數(SUMPRODUCT)
以上結果初看之下好像是對的公式,經過思考之後發現我犯了錯:複姓者(例如:范姜、歐陽)的第 1 個恰好也有單姓者(例如:范、歐)相同的字,沒考慮到這個因素。
修改公式,儲存格E2:
=IF(LEN(C2)=2,SUMPRODUCT((LEFT(姓名,2)=C2)*1),SUMPRODUCT((
LEFT(姓名,1)=C2)*1)-SUMPRODUCT((LEFT(姓名,1)=C2)*(LEN(姓名)=4)))
將公式:
SUMPRODUCT((LEFT(姓名,1)=C2)*1)
改為:
SUMPRODUCT((LEFT(姓名,1)=C2)*(LEN(姓名)=4))
即在公式中多加了一個條件,姓名為 4 個字者:(LEN(姓名)=4)
如此,就不會重覆計算了。

沒有留言:

張貼留言

檢視其他文章

好康東東