2012年9月5日 星期三

Excel-計算姓名中包含某些字的人數(SUMPRODUCT,陣列公式)

開學了,同事對於學生姓名有興趣,想要知道姓名中包含某些字的人數有多少,在 Excel 中該如何處理?(參考下圖)

假資料範圍為:儲存格A1:B100。

先選取儲存格B1:B100,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名。

【使用 PRODUCT 函數】

儲存格D1:=SUMPRODUCT(--(LEFT(姓名,1)="王"))

利用 LEFT 函式,取出「姓名」第一個字為「王」者的陣列(本例:Fasle, Fasle, False, True, False, …)。

利用 SUMPRODUCT 函式計算上式陣列中 True 的個數,公式中的「--」,乃將 True/False 陣列,轉換為 1/0 陣列。

儲存格D2和儲存格D3,仿上列公式。

儲存格D5:=SUMPRODUCT(--(LEN(姓名)>LEN(SUBSTITUTE(姓名,"安",""))))

利用 SUBSTITUTE 函式,將姓名中含有的「安」字,以空白取代,再計算字串長度。如果長度少於原姓名長度,則表示該姓名中含有「安」字。

儲存格D6和儲存格D7,仿上列公式。

 

【使用陣列公式】

儲存格D1:{=SUM(IF(LEFT(姓名,1)="王",1,0))}

上式為陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

儲存格D2和儲存格D3,仿上列公式。

儲存格D5:{=SUM(--(LEN(姓名)>LEN(SUBSTITUTE(姓名,"安",""))))}

上式為陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

儲存格D6和儲存格D7,仿上列公式。

 

【補充資料】

相關函式的詳細說明,請參考微軟網站: 

SUBSTITUTEhttp://office.microsoft.com/zh-tw/excel-help/HP010342927.aspx

SUBSTITUTE:將文字字串中的 old_text 部分以新字串 new_text 取代。

語法:SUBSTITUTE(text, old_text, new_text, [instance_num])

text:要取代字元的文字,或含有該文字之儲存格的參照。

old_text:這是要取代的文字。

new_text:是要用來取代 old_text 的文字。

instance_num:指定要將第幾個 old_text 取代為 new_text

 

 

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

3 則留言:

  1. =SUM(IF(LEFT(A3:A314,4)="1230",1,0))

    這是我的運算式 ? 請問哪裡錯誤了嗎?

    回覆刪除
    回覆
    1. 如果你要使用陣列公式, 則輸入完成要按 Ctrl+Shift+Enter 鍵, 或是使用SUMPRODUCT函數,可參考 http://isvincent.blogspot.tw/2013/01/excel-sumproduct_23.html

      刪除
  2. 不好意思,請問本篇是計算姓名中包含某些字的人數,我看得懂函數的部分,若是今天是要標示B1:B100所有姓名中含有這些條件的字標示成紅字(只有符合"文""淑""安"這三個字是紅字,不是整個姓名標成紅字),有想到「設定格式化條件」可是不曉得怎麼做?

    回覆刪除

好康東東