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,區域)的不同。
問題是計算指定姓名的的“合計”的最大值是多少。
首先輸入公式:
{=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。
,這個是指第一個引數為時,是告訴後面的 或如有第三個引數,是
一個數組,是可以自動擴充套件的,並且將擴充套件為與後面的兩個引數同等尺寸的陣列,然後進行真假判斷後再返回一個新的陣列。
例如, 中的將會擴充套件成與 同等的尺寸,即 ,而 會擴充套件成陣列 然後再進行判斷後重構一個數組。
前面講過 這是一個真假,所以自動擴充套件後 再進行判斷,全為真,所以得到陣列 。
所以回到實際的例子中,原公式就可以寫成:
{=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。
所以組成一個數組
具體如下圖所示:
所以整個公式就可以寫成:
=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的反向查詢以及多條件查詢。