選單

if({1},區域)與if(1,區域),if({0,1}與if({1,0}怎麼理解?

Excel中的公式是變化多端的,但是最重要的還是對於基本知識的要足夠的紮實,才能活學活用。些公式中經常會出現一些奇怪的公式,比如說:

這些公式中的部分經常不會單獨使用,而是與其他的某一類的公式是聯合使用的。

那這些公式都是什麼意思,如何理解?

1、關於IF函式

關於if函式的基本的語法與引數的介紹不是本文介紹的重點。

這裡我們來討論一下關於真值的問題,眾所周知,if函式的可以返回2個值,條件為真時與為假時分別返回一個值。

函式中的真值的定義是這樣的:

真值是不區分大小的(除0外),即不等於0的任何數。

所以,當你輸入以下的這樣的公式的時候,還能返回正確的結果:

從上面的這個例子中,我們可以看到:

等式1為真值,所以返回第1個引數為5,等式2為非真值,所以返回3。等式中的第1個引數可以是不為0的任何的數,結果都返回5。

等式3與等式4,同上面的例子一樣,因為是真值,所以結果也是一樣的。但是不同的,等式3與等4中的和返回的是一個數組。如果我們將公式抹黑後,按F9鍵就可以發現。等式3與等4的結果不一個數組,結果為。

2、例項匯入

首先來看一個實際的案例,我們將從這個實際的例子來出發,學習if(,區域)與if(1,區域)的不同。

if({1},區域)與if(1,區域),if({0,1}與if({1,0}怎麼理解?

問題是計算指定姓名的的“合計”的最大值是多少。

首先輸入公式:

{=MAX(VLOOKUP(T(IF(1,$G$4:$G$6)),$A$1:$E$19,5,0))}

按三鍵完成後。

從返回結果16來檢查一下,很明顯這個結果是錯誤的。究其原因是什麼,因為VLOOKUP的第一個引數是不支援陣列的。所以只返回了張三3對應的值。

那下面我們將公式進行修改,強調將VLOOKUP的第一個引數寫成陣列型的:

{=MAX(VLOOKUP(T(IF(1,{"張三3","張三10","張三15"})),$A$1:$E$19,5,0))}

三鍵後結果返回20。是正確的,但是這樣很麻煩,如果有很多個的時候,是及其不方便的。

故有下面的公式:

{=MAX(VLOOKUP(T(IF(,$G$4:$G$6)),$A$1:$E$19,5,0))}

從公式1與公式3來看,一個是 ,一個是 ,那這兩個到底有什麼不同呢?

下面我們來具體地討論一下。

3、if(,區域)與if(1,區域)

,這個是指第一個引數為1時,也就是真假,告訴if返回真值的型別是單元格區域 的引用。這裡面省略了if函式的第三個值,即邏輯假時返回的值,那麼預設會返回為 。所以上述的公式1中的這部分返回了一個單元格的引用區域,VLOOKUP只能查詢第一個單元格的位置對應的值。

如果將這個公式 中的第一個引數修改為任何非0的數時都是成立的。而 這個時候就會返回FALSE,因為第1個引數為0時,是邏輯假 ,所以會返回邏輯假對應的值,這裡省略了,所以會返回FALSE。

,這個是指第一個引數為時,是告訴後面的 或如有第三個引數,是

一個數組,是可以自動擴充套件的,並且將擴充套件為與後面的兩個引數同等尺寸的陣列,然後進行真假判斷後再返回一個新的陣列。

例如, 中的將會擴充套件成與 同等的尺寸,即 ,而 會擴充套件成陣列 然後再進行判斷後重構一個數組。

前面講過 這是一個真假,所以自動擴充套件後 再進行判斷,全為真,所以得到陣列 。

if({1},區域)與if(1,區域),if({0,1}與if({1,0}怎麼理解?

所以回到實際的例子中,原公式就可以寫成:

{=MAX(VLOOKUP(T(IF(1,{“張三3”,“張三10”,“張三15”})),$A$1:$E$19,5,0))}

這個例子經常與N函式或T函式與其他函式配合使用。

4、if(,區域,區域)與if(,區域,區域)

,這個套路經常用於VLOOKUP的反向(也叫逆向)查詢過程中。其原理與上述的是一樣的,都是

陣列重構

的過程。

首先我們從一個例項,來看這個問題,查詢每個姓名對應的A型別的值。

公式可以寫成:

=VLOOKUP(F3,IF(,$A$2:$A$6,$D$2:$D$6),2,0)

,按三鍵結束後向下填充。

這裡的是一個水平陣列,而  、 分別是垂直陣列。

先來看一下,陣列重構的過程,首先 會自動擴充套件成一個與  、 相關尺寸的陣列(前提是這兩個區域的尺寸大小是一致的),而  會擴充套件成 ,  會擴充套件成 。

接下來進入到判斷重構陣列的過程:

為了方便說明,假定 中的 0為x,1為y。 的每一行分別記為x1……與y1……。

第1行:

x1=0時。為假值,取第三個引數中的對應的值為“張三1”;

y1=1時。為真值,取第二個引數中的對應的值為5。

所以組成一個數組

第2行:

x2=0時。為假值,取第三個引數中的對應的值為“張三2”;

y2=1時。為真值,取第二個引數中的對應的值為9。

所以組成一個數組

……

第5行:

x5=0時。為假值,取第三個引數中的對應的值為“張三5”;

y5=1時。為真值,取第二個引數中的對應的值為10。

所以組成一個數組

具體如下圖所示:

if({1},區域)與if(1,區域),if({0,1}與if({1,0}怎麼理解?

所以整個公式就可以寫成:

=VLOOKUP(F3,

{"張三1",5;"張三2",9;"張三3",8;"張三4",2;"張三5",10},2

,0)

同樣,公式還可以反著來寫:

=VLOOKUP(F3,IF(,

$D$2:$D$6,

$A$2:$A$6),2,0)

主要是要明確1為真值是為VLOOKUP查詢的目標值,0對應的是結果值,這樣才能重構正確的陣列。

根據這個例子可以實現VLOOKUP的反向查詢以及多條件查詢。