學不完．教不停．用不盡

在 Excel 中取得一個物品進出料的記錄表，根據這個資料表來練習各種條件式加總的練習。

首先將A欄到E欄中有資料的儲存格，分別命名為：日期、經手人、進出、料號、數量。並將全部資料(儲存格A1:E27)命名為：資料。

(1) 計算經手人的經手次數

儲存格H2：=COUNTIF(經手人,G2)

複製儲存格H2，往下二列貼上。

(2) 計算進料/出料的小計

儲存格H7：=COUNTIF(進出,G7)

儲存格I7：=SUMIF(進出,G7,數量)

複製儲存格H7:I7，往下一列貼上。

(3) 依類別和料號計算進料/出料的小計

儲存格H11：=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(進出=H$10)*數量)

儲存格I11：=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(進出=I$10)*數量)

複製儲存格H11:I11，往下二列貼上。

MONTH(日期)=ROW(1:1)，可以找出月份為1者，往下複製時ROW(1:1)=1 → ROW(2:2)=2 → …。

同理：

儲存格H16：=SUMPRODUCT((LEFT(料號,1)=$G16)*(進出=H$15)*數量)

儲存格I16：=SUMPRODUCT((LEFT(料號,1)=$G16)*(進出=I$15)*數量)

複製儲存格H16:I16，往下二列貼上。

LEFT(料號,1)=$G16：找出料號第1個字元為「A」者。

(4) 依日期查詢當天的進出料資料

儲存格H21：=INDEX(資料,MATCH($G21,日期,0)+1,COLUMN(C:C))

複製儲存格H21，貼至儲存格H21:J21。再複製儲存格H21:J21，往下二列貼上。

COLUMN(C:C)=3，向右複製COLUMN(C:C)=3 → COLUMN(D:D)=4 → COLUMN(E:E)=5。

MATCH($G21,日期,0)+1：找出日期在第幾列。

【延伸閱讀】

* Excel-標示多欄姓名清單裡姓名重覆者(COUNTIF)

* Excel-跨工作表使用COUNTIFS函數

* Excel-比較兩組內容的重複項目和唯一項目

* Excel-計算不同姓名字數的人數(LEN,COUNTIF,REPT,SUMPRODUCT)

* Excel-動態計算最近n天且小於m筆的平均

* Excel-在欄位中自動標示相同項目的最後一項(COUNTIF)

* Excel-查詢儲存格是否出現重覆內容

* Excel-計算不重複項目個數並排除空白(SUMPRODUCT,COUNTIF)

* Excel-不使用陣列公式列出不重覆的資料清單

* Excel-計算各個分組的名次(SUMPORDUCT,COUNTIFS)

* Excel-計算多人分攤多件物品的費用(SUMPRODUCT,COUNTIF)

* Excel-在資料清單中依性別排名(COUNTIFS)