2013年7月14日 星期日

Excel-計算合於條件的不重覆個數(SUMPRODUCT,COUNTIF)

有個網友問到:在下圖中有一個業務員和客戶的對照表(下圖左),如果想要摘要出業務員的客戶數(下圖右),注意到有些客戶是重覆的,該如何處理呢?
【準備工作】
選取儲存格A1:B12,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:業務員、客戶。

【輸入公式】
儲存格E2:=SUMPRODUCT((業務員=D2)*(1/COUNTIF(客戶,客戶)))
業務員=D2:在業務員的陣列中找出是「業務員A」的儲存格。
1/COUNTIF(客戶,客戶):因為該函數在 SUMPRODUCT 函數中,所以會執行陣列運算。該式子會找出B欄中的每個客戶名稱出現次數的「倒數」。
例如儲存格B2為客戶A出現 2 次,所以結果為 1/2 。其中將 2 個 1/2 加總時,最後結果為 1。所以結果只會被計入 1,因此可以用來計算不重覆的項目個數。

複製儲存格E2,往下各列貼上,即為所求。

【注意事項】
這個公式只能用在相同客戶不能出現在不同業務員中的狀況下才可以。

【補充說明】
相關函數說明,請參閱微軟網站:
SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。
語法:SUMPRODUCT(array1, [array2], [array3], ...)
array1:要求對應元素乘積和的第一個陣列引數。
array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。
註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

好康東東