最近在研究自己通訊錄上的連絡人時，好奇想要知道每個姓氏的人數份佈比率，所以利用樞紐分析表工具和公式分別處理一次，卻得到不同結果，進一步了解找出了自已在公式設計的錯誤之處。（參考下圖）
在下圖中，A欄是所有連絡人的姓名，先利用公式取出其姓。
儲存格B2：=IF(LEN(A2)=4,LEFT(A2,2),LEFT(A2,1))
複製儲存格B2，往下各列貼上。
取用B欄內容執行樞紐分析表操作，得到以下的結果：
選取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相同的數量。
以上結果初看之下好像是對的公式，經過思考之後發現我犯了錯：複姓者（例如：范姜、歐陽）的第 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)
如此，就不會重覆計算了。
留言列表