學不完．教不停．用不盡

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

【公式設計與解析】

選取儲存格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)*(進出="進料")*數量)

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

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

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

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

分析一下問題：

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

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

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

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

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

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

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

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

改用陣列公式，例如，

儲存格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。

公式修改為：

儲存格M2：｛=MIN(IF(A2:L2>0,A2:L2,999))｝

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

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

或是採用以下的函數

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

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