2015年6月3日 星期三

Excel-不固定資料範圍的小計(SUMPRODUCT,OFFSET)

網友想要在 Excel 中的一個資料清單(下圖左),轉換為矩陣計算小計(下圖右),該如何處理?如果資料範圍會增加時,公式如何設計?

(1) 資料範圍固定
若是資料範圍固定,若要定義名稱,選取儲存格A1:B22,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:部門主管、考績。其定義的內容如下:
部門主管:=工作表1!$A$2:$A$21
  考績:=工作表1!$B$2:$B$21
儲存格E2:=SUMPRODUCT((部門主管=$D2)*(考績=E$1))
其中「*」運算相當於使用 AND 邏輯運算。
複製儲存格E2,貼至儲存格E2:H4。

(2) 資料範圍不固定
若是資料範圍不固定,儲存格內容會不斷的增加,則必須設定為動態範圍。藉助 OFFSET 函數來定義名稱,其內容如下:
部門主管:=OFFSET(工作表1!$A$2,,,COUNTA(工作表1!$A:$A),)
  考績:=OFFSET(工作表1!$B$2,,,COUNTA(工作表1!$B:$B),)
COUNTA(工作表1!$A:$A):找出A欄中有文字的內容有幾個,也就是A欄內容增加時,函數結果也會再加 1。
再藉由 OFFSET 函數定義出含有資料的儲存格範圍。
儲存格E2:=SUMPRODUCT((部門主管=$D2)*(考績=E$1))  <==公式內容不變
當A欄和B欄增加儲存格內容時,不需更改公式。

沒有留言:

張貼留言

檢視其他文章

好康東東