2013年10月15日 星期二

Excel-找出全部資料分為幾組(SUMPRODUCT,COUNTIF)

學校同仁在最近報名研習的時候,遇到了一個問題:因為研習單位限定只有多少組別可以參加,而且報名時有先後次序之分。如下圖,其中有部分資料省略,而且相同學校的報名人數不一樣多、報名次序也不一定連續。

同仁想要知道現有已報名的組數有多少,是否已在安全名單中,該如何處理呢?這是很多人在報名時,或是承辦單位在統計資料時,常會需要解決的問題。

【準備工作】

選取儲存格D1:D61,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學校。

 

【輸入公式】

公式=SUMPRODUCT(1/(COUNTIF(學校,學校)))

公式原理參考以下說明:

儲存格H2:=1/COUNTIF(學校,D2)

藉由 COUNTIF 函數,找出每個學校出現的次數,1/COUNTIF 的用意是將查到的次數加以倒數,例如某一學校有 3 人報名,則 1/COUNTIF 則為 0.333333,某一學校有 4 人報名,則 1/COUNTIF 則為 0.25。

參考下圖,把H欄中的所有 1/COUNTIF 加總,即可得到共有幾組了。例如:某一學校有 3 人報名,則把 3 個 0.333333 加總則為 1,即視為 1 組。

如果使用公式:SUMPRODUCT(1/(COUNTIF(學校,學校))),即不需要H欄的輔助欄位。

沒有留言:

張貼留言

好康東東