在 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-計算不同姓名字數的人數(LEN,COUNTIF,REPT,SUMPRODUCT)
* Excel-在欄位中自動標示相同項目的最後一項(COUNTIF)
* Excel-計算不重複項目個數並排除空白(SUMPRODUCT,COUNTIF)
* Excel-計算各個分組的名次(SUMPORDUCT,COUNTIFS)
留言列表