2015年1月13日 星期二

Excel-藉助唯一值計算加總(VLOOKUP,SUMPRODUCT)

有網友問到:在 Excel 的一個資料表(如下圖左),其中含有統編、姓名和金額,如果根據人名來加總,會有不同統編卻名字相同的情形(序號3,11,17),該如何處理較好?

其實這個資料表中有一個欄位「統編」,其中的項目內容並不會重覆,即可用來做為小計的依據,不用擔心人名重覆問題。

現在來練習依上圖左的內容,製作成上圖右的結果。

1. 複製儲存格B2:B23,貼至儲存格F2。目前儲存格F2:F23為選取狀態。

2. 點選[資料/資料工具]選單中的「移除重複」按鈕。

按下[確定]按鈕:

Excel 移除了 10 個重覆值:

3. 輸入公式

(1) 利用 VLOOKUP 函數以查表方式找到每個統編對應的姓名

儲存格G2:=VLOOKUP(F2,$B$1:$C$23,2,FALSE)

(2) 利用 SUMPRODUCT 函數利用執行乘積和來求得小計

儲存格H2:=SUMPRODUCT(($B$2:$B$23=F2)*$D$2:$D$23)

$B$2:$B$23=F2:會傳回在統編儲存格B2:B23的陣列中和儲存格F2內容是否相同的 TRUE/FALSE 陣列。當執行「*」運算時,TRUE/FALSE 陣列會轉換為 1/0 陣列,再於 SUMPRODUCT 函數中根據金額陣列來執行乘積和,即可求得小計。

 

【使用樞紐分析】

如果不想使用公式,可以使用插入「樞紐分析表」工具來手動取得結果:

沒有留言:

張貼留言

檢視其他文章

好康東東