學不完．教不停．用不盡

本範例是延續前四篇文章的研習講義：

1. Excel-SUMPRODUCT函數範例與說明(研習範例)

2. Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

3. Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)

4. Excel-多人多項的金額統計(SUMPRODUCT練習)

這次來練習在 SUMPRODUCT 函數中搭配其他函數達成篩選並統計的功能。參考下圖，列有五種計算一二三年級各項比賽的人數。

【準備工作】

假設報名資料的儲存格範圍是A1:G49。選取儲存格A1:G49，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：年級、班級編碼、班級名稱、大隊男、大隊女、跳繩、拔河。

作法(一)：未使用名稱

儲存格J3：=SUMPRODUCT(($A$2:$A$49=$I3)*(D$2:D$49))

複製儲存格J3，貼至儲存格J3:M5。

作法(二)：使用名稱

儲存格J8：=SUMPRODUCT((年級=$I9)*(INDIRECT(J$8)))

INDIRECT(J$8)：使用 IINDIRECT 函數將儲存格J8的內容轉換為一個『名稱』，而該名稱已定義了儲存格位址。

複製儲存格J8，貼至儲存格J8:M10。

作法(三)：使用『年級』欄位

儲存格J13：=SUMPRODUCT((LEFT(年級,1)=$I15)*(INDIRECT(J$14)))

LEFT(年級,1)：使用 LEFT 函數取出『年級』儲存格陣列中的每一個內容的第一個字元，再和儲存格I15來比對判斷。

複製儲存格J13，貼至儲存格J13:M15。

作法(四)：使用『班級編碼』欄位

儲存格J18：=SUMPRODUCT((LEFT(班級編碼,1)=LEFT($I21,1))*(INDIRECT(J$20)))

複製儲存格J18，貼至儲存格J18:M20。

作法(五)：使用『班級編碼』欄位

儲存格J23：

=SUMPRODUCT((VALUE(LEFT(班級編碼,1))=ROW(1:1))*(INDIRECT(J$2)))

ROW(1:1)=1，當複製後向下貼上時，ROW(1:1)→ROW(2:2)=2→ROW(3:3)=3。

複製儲存格J23，貼至儲存格J23:M25。

【延伸閱讀-FILTER函數篩選應用】

Excel-下拉式清單選取月份列出該月日期

 Excel-根據單條件和雙條件篩選資料(FILTER)

 Excel-列出指定星期幾的日期

 Excel-單一欄位篩選與跨欄位篩選

 Excel-計算分組最大值

 Excel-FILTER和OFFSET的動態陣列

 Excel-篩選資料並轉置資料

 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

 Excel-由資料清單中篩選一組

 Excel-利用FILTER函數模糊篩選

 Excel-從日期清單中區別平日和假日計算總和

 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

 Excel-FILTER函數與進階篩選

 Excel-2021版新增函數的使用