精通Excel數組公式005：比較數組運算及使用一個或多個條件的聚合計算

hercules028 2020-06-22

展開全文

excelperfect

下面是Excel的比較運算符：

= 等于

<> 不等于

> 大于

>= 大于等于

< 小于

<= 小于等于

在諸如基于條件查找最小值或最大值、計算標準偏差等情形時，Excel沒有提供相應的內置函數，必須編寫數組公式，其中往往涉及到在數組中使用比較運算符。

如下圖1所示，在單元格區域A3:B8中記錄了城市名和對應的時間，想要知道每個城市對應的最小時間。我們知道，可以使用MIN函數來求一組數值的最小值，但是如何分離出每個城市并分別求出它們對應的時間最小值呢？

圖1

使用數組公式

Excel中沒有一個MINIF函數來根據條件求相應的最小值，可以使用MIN/IF函數組合來實現。在單元格E3中的數組公式如下：

=MIN(IF($A$3:$A$8=D3,$B$3:$B$8))

向下拉至單元格E5。

在公式中：

$A$3:$A$8=D3

將單元格區域A3:A8中的城市名與單元格D3中的城市名相比較，生成數組：

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

接著，IF函數根據比較的結果，從單元格區域B3:B8中獲取單元格D3城市的時間：

{FALSE;FALSE;9;FALSE;FALSE;6}

IF函數忽略了參數value_if_false，默認值為FALSE。

傳遞給MIN函數：

=MIN({FALSE;FALSE;9;FALSE;FALSE;6})

得到單元格D3城市的最小時間。注意，MIN函數忽略其參數中的邏輯值。

使用數據庫函數

在Excel中，有一組基于判斷條件執行計算的數據庫函數，共12個，也稱之為D-函數，例如DMIN、DMAX和DSUM函數。當執行單獨的計算且數據集具有字段名稱（列標簽）時，這些函數非常強大。

如下圖2所示，使用DMIN函數來計算指定城市的最小時間。

圖2

如果僅要知道某個城市的最小時間，使用DMIN函數比使用數組公式更簡單且對于大數據集來說速度更快。然而，使用DMIN函數需要在某單元格中輸入字段名并在該字段名下方輸入判斷條件，這意味著對于每次計算都需要在上下兩個單元格中輸入相應的內容。如下圖3所示，顯然，對于多個值不能像以前那樣簡單地下拉公式，這是其不利之處。

圖3

有時候，對于非常大的數據來說公式計算時間過長是個問題，下圖4展示了一個解決方案，充分利用D-函數優于數組公式計算的優勢。

圖4

下面是創建上述解決方案的步驟：

1. 在單元格E3中創建公式。

2. 在單元格區域D6:D8中輸入所有的城市名稱。

3. 在單元格E5中，創建指向單元格E3的公式。

4. 選擇單元格區域D5:E8，按Alt、D、T鍵，（或者單擊功能區“數據”選項卡“預測”組中的“模擬分析——模擬運算表”命令）打開“模擬運算表”對話框，如下圖5所示。

圖5

6. 在“輸入引用列的單元格”中輸入D3，單擊“確定”按鈕。

使用數據透視表

可以使用數據透視表來獲得上文示例中的結果，如下圖6所示。

圖6

創建數據透視表的步驟如下：

1. 在要創建數據透視表的數據集中任選一單元格，單擊“插入”選項卡“表格”組中的“數據透視表”命令。

2. 在“創建數據透視表”對話框的“選擇放置數據透視表的位置”中選取“現有工作表”，輸入：D1，單擊“確定”。

3. 將“城市”字段拖至行區域，將“時間(h)”字段拖至值區域。

4. 在數據透視表的任意值單元格中，單擊右鍵，選擇“值字段設置”命令。在“值字段設置”對話框的“值字段匯總方式”列表框中，選擇“最小值”。

5. 在數據透視表中單擊右鍵，選擇“數據透視表選項”命令。在“數據透視表選項”對話框的“匯總和篩選”選項卡中，取消“顯示行總計”和“顯示列總計”復選框。

6. 將數據透視表頂部字段修改為相應內容并調整布局。

可以看出，數據透視表對于帶有一個或多個判斷條件的聚合計算非常方便，但是與公式相比，當源數據變化時，它不能立即更新，需要刷新才能更新其內容。此外，數據透視表僅有11個函數可用，而公式有近400個可用函數。

兩個條件的求值示例

下面再看一個多條件的例子。如下圖7所示，在指定區域中分別計算每位銷售代表的最大銷售量。

圖7

想要編寫一個公式能夠直接向下復制，且當源數據更新時結果能自動更新。有了上文的基礎后，我們知道可以使用MAX函數配合兩個嵌套的IF函數來實現。正如上圖7中所示，在單元格F5中的數組公式為：

=MAX(IF($A$3:$A$12=$F$2,IF($B$3:$B$12=E5,$C$3:$C$12)))

其含義為，如果單元格區域A2:A12中的值等于單元格F2中的值，且單元格區域B3:B12中的值等于單元格E5中的值，則返回單元格區域C3:C12中相應的值，否則返回假。即生成數組：

{914;FALSE;FALSE;610;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

注意，嵌套的IF函數創建了一個AND條件判斷。此示例也可以使用上文介紹的DMAX函數或數據透視表來實現，有興趣的朋友可以試試。

再看一個示例。如下圖8所示，分別為：跑道（不同城市中的不同跑道）、BMX賽車手（賽車手名字）、時間(s)（沿跑道行駛的時間，以秒計）。現在，想要計算每個賽車手的最快時間。然而，因為PI跑道非常短而其他跑道非常長，需要從計算中排除PI跑道的時間。

圖8

我們在單元格F5中輸入數組公式：

=MIN(IF($A$3:$A$13<>$F$2,IF($B$3:$B$13=E5,$C$3:$C$13)))

其原理與前一個示例相同，只是條件判斷中使用了“<>”號，表示NOT運算。

我們看到，前面使用的數組公式必須以按Ctrl+Shift+回車鍵結束。自Excel 2010起，可以使用一個新函數：AGGREGATE函數，而無需按Ctrl+Shift+回車鍵。對于上例，使用AGGREGATE函數的解決方案如下圖9所示。（注意，如果在公式里的IF函數中有數組操作，那么該公式必須按Ctrl+Shift+回車鍵結束，即便作為AGGREGATE函數的數組參數也是如此。）