最近在研究自己通訊錄上的連絡人時,好奇想要知道每個姓氏的人數份佈比率,所以利用樞紐分析表工具和公式分別處理一次,卻得到不同結果,進一步了解找出了自已在公式設計的錯誤之處。(參考下圖)

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)

如此,就不會重覆計算了。

arrow
arrow
    文章標籤
    Excel SUMPRODUCT
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()