2011年6月23日 星期四

Excel-條件式加總練習(SUMIF+COUNTIF)

在 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:找出日期在第幾列。

1 則留言:

  1. 你好, 我有一疑難:
    我要統計倉的出/入/留貨紀錄, 要同時符合幾個條件, 就把貨的數量加起來, 嘗試使用sumproduct, 但失敗。請指點。
    條件:
    1. 型號 e.g. A001
    2. 貨的出/入/留貨紀錄: 留貨
    3. 留貨單號: 有單號(一串文字)即代表肯定要貨, 沒有單號(空格)即不一定要貨

    如果是 A001 + 留貨 +有單號 = 把數量加起來
    如果是 A001 + 留貨 +沒有單號 = 把數量加起來

    由於出/入/留貨紀錄會每天更新, 所以不能設計公式在這活頁。

    回覆刪除

檢視其他文章

好康東東