學不完．教不停．用不盡

網友想要在 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欄增加儲存格內容時，不需更改公式。