2015年12月2日 星期三

Excel-SUMPRODUCT結合其他函數達成篩選及統計的功能(LEFT,INDIRECT,ROW)

本範例是延續前四篇文章的研習講義:
這次來練習在 SUMPRODUCT 函數中搭配其他函數達成篩選並統計的功能。參考下圖,列有五種計算一二三年級各項比賽的人數。
Excel-SUMPRODUCT結合其他函數達成篩選及統計的功能(LEFT,INDIRECT,ROW)

【準備工作】
假設報名資料的儲存格範圍是A1:G49。選取儲存格A1:G49,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:年級、班級編碼、班級名稱、大隊男、大隊女、跳繩、拔河。
作法(一):未使用名稱
儲存格J3:=SUMPRODUCT(($A$2:$A$49=$I3)*(D$2:D$49))
複製儲存格J3,貼至儲存格J3:M5。
Excel-SUMPRODUCT結合其他函數達成篩選及統計的功能(LEFT,INDIRECT,ROW)

作法(二):使用名稱
儲存格J8:=SUMPRODUCT((年級=$I9)*(INDIRECT(J$8)))
INDIRECT(J$8):使用 IINDIRECT 函數將儲存格J8的內容轉換為一個『名稱』,而該名稱已定義了儲存格位址。
複製儲存格J8,貼至儲存格J8:M10。
Excel-SUMPRODUCT結合其他函數達成篩選及統計的功能(LEFT,INDIRECT,ROW)

作法(三):使用『年級』欄位
儲存格J13:=SUMPRODUCT((LEFT(年級,1)=$I15)*(INDIRECT(J$14)))
LEFT(年級,1):使用 LEFT 函數取出『年級』儲存格陣列中的每一個內容的第一個字元,再和儲存格I15來比對判斷。
複製儲存格J13,貼至儲存格J13:M15。
Excel-SUMPRODUCT結合其他函數達成篩選及統計的功能(LEFT,INDIRECT,ROW)

作法(四):使用『班級編碼』欄位
儲存格J18:=SUMPRODUCT((LEFT(班級編碼,1)=LEFT($I21,1))*(INDIRECT(J$20)))
複製儲存格J18,貼至儲存格J18:M20。
Excel-SUMPRODUCT結合其他函數達成篩選及統計的功能(LEFT,INDIRECT,ROW)

作法(五):使用『班級編碼』欄位
儲存格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。
Excel-SUMPRODUCT結合其他函數達成篩選及統計的功能(LEFT,INDIRECT,ROW)

沒有留言:

張貼留言

好康東東