贊助廠商

網友問到:在 Excel 的工作表裡有一個資料清單(如下圖),如何分月計算各種料號的進料數量?

Excel-分月計算各種料號的進料數量(SUMPRODUCT)

 

【公式設計與解析】

選取儲存格A1:E27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、經手人、進出、料號、數量。

1. 計算每個月各種料號的出料數量

儲存格H2:

=SUMPRODUCT((MONTH(日期)=COLUMN(A:A))*(料號=$G4)*(進出="進料")*數量)

複製儲存格H2,貼至儲存格H2:I12。

SUMPRODUCT 函數裡,利用三個條件來取出數量:

(1) 條件一:MONTH(日期)=COLUMN(A:A)

利用 MONTH 函數取出日期陣列中的每個月份,

COLUMN(A:A)=1向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3

本例要找出符合日期的月份為1者。

(2) 條件二:料號=$G2

(3) 條件三:進出="進料"

最後再乘以數量陣列,即為所求。

 

2. 計算每個月各種料號的出料數量

同理,只要將公式裡的「進料」改成「出料」。

儲存格H2:

=SUMPRODUCT((MONTH(日期)=COLUMN(A:A))*(料號=$G4)*(進出="進料")*數量)

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

回答同事提問:在 Excel 裡使用「設定格式化的條件」,能根據不同數值內容設定不同的格式。在設定格式化的條件時,要注意條件的執行先後順序!

例如,以下的做法,可以做到「用紅色前景標示>20者,用藍色背景標示>25者」。

其中使用了二個不同的條件設定,先判斷背景色彩條件,再判斷前景色彩條件。

Excel-在設定格式化的條件時,要注意條件的執行先後順序

同仁遇到的問題是,為何以下的做法,無法做到「用紅色前景標示>20者,用藍色前景標示>25者」?

分析一下問題:

1. 「用紅色前景標示>20者,用藍色前景標示>25者」不合理,因為>25者,必定>20。

2. 正確的問題可能是:>20且<25者標示紅色前景,>25者標示藍色前景。

Excel-在設定格式化的條件時,要注意條件的執行先後順序

如果是這樣,則將>25的條件上移即可。因為>25的條件執行了,就不會執行>20的條件。

Excel-在設定格式化的條件時,要注意條件的執行先後順序

這樣的邏輯判斷和以下的做法,結果會一樣:

即判斷數值介於21和25之間者設定紅色,>25者設定藍色。這二個條沒有執行順序之分。

Excel-在設定格式化的條件時,要注意條件的執行先後順序

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

 

在 Excel 的工作表中,如果一個數列中包含了空格和0,如何找出排除空格和0的最小值?

求最小值常用 MIN 函數,但是無法排除0,不過空格不會被視為0。

Excel-計算不包含0及空白儲存格的最小值(SMALL,LARGE,MIN)

如果使用 SMALL 函數,也是無法排除0,空格也是不會被視為0。

Excel-計算不包含0及空白儲存格的最小值(SMALL,LARGE,MIN)

改用陣列公式,例如,

儲存格M2:{=MIN(IF(A2:L2>0,A2:L2,))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加入「{}」。

在陣列公式中,IF(A2:L2>0,A2:L2,) 表示取出儲存格A2:L2中大於0者。再以 MIN 函數取出其中的最小值。

但是此公式中 IF 的第三個參數是空白,會導致 MIN 取出的最小值為0。

Excel-計算不包含0及空白儲存格的最小值(SMALL,LARGE,MIN)

公式修改為:

儲存格M2:{=MIN(IF(A2:L2>0,A2:L2,999))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加入「{}」。

將公式中 IF 的第三個參數改為一個很大的數(本例:999),則結果是正確的。

Excel-計算不包含0及空白儲存格的最小值(SMALL,LARGE,MIN)

或是採用以下的函數

儲存格M2:=LARGE(A2:L2,COUNTIF(A2:L2,">0"))

也可以不使用陣列公式,並且也避開空白儲存格為0的問題。

Excel-計算不包含0及空白儲存格的最小值(SMALL,LARGE,MIN)

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼