Excel公式中IF({1},區域)與IF(1,區域)的區別

06-04

問題是這樣的：如下表所示，小王本月在單位吃了5天早餐、10天中餐、3天晚餐。補助標準是：早餐：每天3元；中餐：每天5元；晚餐：每天4元。求：小王本月應得多少伙食補助。

聲明：1、以下內容只是為了說明問題，並不表示只有這一種方法。其實，想要得到結果，公式=SUMPRODUCT(A3:A8,{0;3;0;5;0;4})更簡潔。 2、本文由JT_man根據Excelhome論壇中的一個貼子整理，其中加了本人對這一問題的理解，不一定正確。要查看原貼請點擊這裡。

C3單元格中公式是（數組公式，三鍵齊按：CTRL+SHIFT+ENTER）：=SUM(IF(N(IF(1,A3:A8)),A3:A8)*{0;3;0;5;0;4})公式計算結果是：0，不能計算出結果。D3單元格中公式是（數組公式）：=SUM(IF(N(IF({1},A3:A8)),A3:A8)*{0;3;0;5;0;4})公式計算結果是：77，計算結果正確。上述兩個公式的唯一區別是：公式中的1，一個沒加大括弧，一個加了大括弧。

問題來了：IF({1},區域)與IF(1,區域)到底有什麼區別？

IF(1, 這個1沒有大括弧，告訴程序我這個是真值（TRUE值），返回的結果是對A3:A8區域的引用, 不是數組。而N()函數對於區域引用只會返回第一個單元格，也就是文本「早」轉化為數值後的值，結果是0；IF(N(IF(1,A5:A10)),A5:A10)的返回值就等同於IF(0,A5:A10)的返回值，為FALSE【因為本例IF()函數中省略了第3個參數，即邏輯假時返回的值，而默認的邏輯假時返回值是FALSE】。整個公式返回值等同於數組公式=SUM(FALSE*{0;3;0;5;0;4})的返回值，即6個0的和值，最終結果為0。 IF({1}，這個{1}有大括弧，是告訴程序，{1}代表一個數組，可以自動擴展，{1}後面跟隨的是一個數組。{1}擴展為與後面A3:A8同行同列大小的數組，然後再進行真假判斷，返回一個新的數組。IF({1},A3:A8)自動擴展為IF({1;1;1;1;1;1},A3:A8)，返回數組{"早";5;"中";10;"晚";3}，外面加N()函數後返回數組{0;5;0;10;0;3}。IF(N(IF({1},A3:A8)),A3:A8)的結果等同於IF({0;5;0;10;0;3},A3:A8)的結果，返回數組{FALSE;5;FALSE;10;FALSE;3}。整個SUM()函數公式結果等同於數組公式=SUM({FALSE;5;FALSE;10;FALSE;3}*{0;3;0;5;0;4})的結果，所以能得出正確答案。

需要說明的是： 1、真值是不分大小的(除0以外)。因為函數中的真值定義是: 不等於0的任何數，所以有=IF({5}、=IF({10}、=IF({3}與=IF({1}結果相同。上述公式內{1}中的1也可改為任何非0值，例如改為{123}、{－456}。 2、IF({1}返回的是一個數組，即使{1}後面只有一個數值。例如：=IF({1},5)，雖然後面只是一個5, 我們在公式欄抹黑公式，按F9鍵運行，不難發現,這個5 ,其實就是一個數組, 結果是{5}。

同樣，利用IF({0,1}函數中數組的自動擴展功能，能夠生成新的數組，並在某些時候發揮很大的作用。

假如，A1：A3單元格中的值分別是1,2,3，B1：B3的值分別是"A","B","C"，如下圖：

數組公式=IF({0,1},A1:A3,B1:B3)中由於第一參數是水平數組，而第二、第三參數實際上是垂直數組，因此三個參數都會自動擴展成相同的維度和維數，即，擴展成IF({0,1;0,1;0,1},{1,1;2,2;3,3},{"A","A";"B","B";"C","C"}),然後按照第一參數的順序依次在二、三參數中去尋找相應的值：第一參數的第一個值0（false）得到第三參數的第一個值A,第一參數的第二個值1（true）得到第二個參數的第二個值1,第一參數的第三個值0（false）得到第三參數的第三個值B，依次類推，於是抹黑此公式按F9鍵運行得到的結果就是{"A",1;"B",2;"C",3}。示意圖如下：

數組公式=IF({1,0},B1:B3,A1:A3)與數組公式=IF({0,1},A1:A3,B1:B3)返回的數組是一樣的。示意圖如下：

基於同樣的原理，也就能理解為什麼數組公式=IF({0,1},D1:F1,B1:B3)返回的結果會是{"A",2,#N/A;"B",2,#N/A;"C",2,#N/A}了,呵呵。

IF({1,0}函數的應用

可用於VLOOKUP等函數中，一是實現從右向左進行VLOOKUP，二是實現多條件查詢。具體介紹可點擊這裡。