函數篇:小小IF不簡單

  Excel辦公,讓職場更輕鬆!

IF函數是我們日常工作中最常用的Excel函數之一,看似簡單的函數,其功能卻是不容小覷的,下面從IF函數的語法解析、實例(包括基礎應用、進階應用以及數組應用)等幾方面進行說明。

函數篇:小小IF不簡單

函數語法解析

1、函數定義:判斷是否滿足某個條件,如果滿足返回一個值,如果不滿足返回另一個值。

2、使用格式:

IF(logical_test, value_if_true,[value_if_false])

IF(要判斷的條件, 滿足條件時返回的值, 不滿足條件時返回的值)

也可以表示為:如果…就…否則

比如:如果有空閒時間,就學習Excel,否則忙工作。

用函數公式表示為:IF(有空閒時間,學習Excel,忙工作)

3、參數說明

①、第一參數的結果為文本或錯誤值時,其結果為錯誤值。

②、滿足條件或不滿足條件時返回的值可以是數值、文本、單元格地址、公式等。如果返回的值是文本,要加英文雙引號,而使用單元格地址時不能加雙引號。

基礎應用

1

判斷成績是否及格

函數篇:小小IF不簡單

在C3單元格輸入公式:=IF(B3>=60,’及格’,’不及格’),向下填充。

公式解析:如果B3單元格的值大於等於60,就及格,否則不及格。

2

計算提成

函數篇:小小IF不簡單

在C13單元格輸入公式:=B13*IF(B13<5000,1%,IF(B13<10000,3%,10%)),向下填充。

公式解析:如果B13單元格的值小於5000,返回提成比例1%,如果B13單元格的值小於10000,返回提成比例3%,否則返回提成比例10%。

3

與且的條件判斷

函數篇:小小IF不簡單

在D23單元格輸入公式:=IF(AND(B23>=10000,C23>=10000),’達標’,’不達標’),向下填充。

也可以寫成:=IF((B23>=10000)*(C23>=10000),’達標’,’不達標’),向下填充。

函數篇:小小IF不簡單

公式解析:AND是且的意思,其連接的兩個條件都成立時返回結果才為真,此題中AND也可以用符號’*’代替。

4

與或的條件判斷

函數篇:小小IF不簡單

在D33單元格輸入公式:=IF(OR(B33>=10000,C33>=10000),’達標’,’不達標’),’通過’,’不通’),向下填充。

也可以寫成:=IF((B33>=10000) (C33>=10000),’達標’,’不達標’),向下填充。

函數篇:小小IF不簡單

公式解析:OR是或的意思,其連接的兩個條件只要有一個條件成立,返回結果就為真,此題中OR也可以用符號’ ‘代替。

5

計算個人所得稅

函數篇:小小IF不簡單

在C53單元格輸入公式:=IF(B53-3500>80000,(B53-3500)*0.45-13505,IF(B53-3500>55000,(B53-3500)*0.35-5505,IF(B53-3500>35000,(B53-3500)*0.3-2775,IF(B53-3500>9000,(B53-3500)*0.25-1005,IF(B53-3500>4500,(B53-3500)*0.2-555,IF(B53-3500>1500,(B53-3500)*0.1-105,(B53-3500)*0.03)))))),向下填充。

進階應用

1

判斷性別

函數篇:小小IF不簡單

在D2單元格輸入公式:=IF(ISODD(C3),’男’,’女’),向下填充。

身份證號碼的第十七位(即性別代號)是奇數為男,偶數為女。

公式解析:ISODD函數判斷數字是不是奇數,是奇數返回TRUE,不是奇數返回FALSE。

如果ISODD部分為TRUE,就返回男,否則返回女。

公式也可以寫成:=IF(ISEVEN(C3),’女’,’男’)

函數篇:小小IF不簡單

公式解析:ISEVEN判斷數字是不是偶數,是偶數返回TRUE,不是偶數返回FALSE。

如果ISEVEN部分為TRUE,就返回女,否則返回男。

2

多條件判斷

性別為女且年齡大於等於55歲,性別為男且年齡大於等於60歲,顯示退休,否則為空。

函數篇:小小IF不簡單

在D13單元格輸入公式:=IF(OR(AND(B13=’女’,C13>=55),AND(B13=’男’,C13>=60)),’退休’,”),向下填充。

公式解析:AND(B13=’女’,C13>=55)表示性別為女且年齡大於等於55歲兩個條件都要成立,該題中AND可以用*代替;

AND(B13=’男’,C13>=60)表示性別為男且年齡大於等於60歲兩個條件都要成立,該題中AND可以用*代替;

上述兩個條件任何一個成立都可以,即用OR表示,該題中OR可以用 代替。

公式也可以寫成:=IF((B13=’女’)*(C13>=55) (B13=’男’)*(C13>=60),’退休’,”)

函數篇:小小IF不簡單

3

累計條件求和

函數SUMIF:對滿足條件的單元格求和。

SUMIF(條件區域,條件,求和區域)

函數篇:小小IF不簡單

日期相同的金額累計,但大家有沒有發現,日期相同的就會出現金額重復累計,那麼該怎麼辦呢?這時候用IF函數就發揮作用了,如:

函數篇:小小IF不簡單

在C23單元格輸入公式:

=IF(A23=A24,”,SUMIF(A$23:A$30,A23,B$23:B$30)),向下填充。

公式解析:先用IF函數判斷這個單元格的日期與下一個單元格的日期是否相同,如果相同返回空,不相同返回SUMIF函數公式。

4

構造記憶體數組

查詢的時候大家都喜歡用VLOOKUP函數,但當遇上反向查找時您是否會感到束手無策呢?那我們一起來看看IF函數發揮的作用!

函數篇:小小IF不簡單

公式:

=VLOOKUP(E33,IF({1,0},B33:B40,A33:A40),2,0)

公式解析:VLOOKUP(查找值,查找區域,返回結果在查找區域的第幾列,查找方式)

IF(要判斷的條件,條件成立時返回的值,條件不成立時返回的值)

IF({1,0},B33:B40,A33:A40)就是說先判斷值為1(相當於TRUE,條件成立),返回區域B33:B40單元格內容,然後判斷值為0(相當於FALSE,條件不成立),返回區域A33:A40單元格內容,整體來說就是兩列順序對換,將逆序轉換為順序。

公式也可以寫成:

=VLOOKUP(D33,IF({0,1},A33:A40,B33:B40),2,0),與上述同理。

函數篇:小小IF不簡單

數組應用

1

條件求和

函數篇:小小IF不簡單

公式:=SUM(IF(B3:B10=’男’,C3:C10)),該公式為數組公式,按<Ctrl Shift Enter>三鍵結束。

公式解析:首先用IF函數判斷區域B3:B10的性別是不是等於男,等於男就返回對應的年齡,最後用SUM函數求和。

2

多條件求平均值

求性別為男且年齡大於等於60歲的平均值

函數篇:小小IF不簡單

公式:

=AVERAGE(IF(B13:B20=’男’,IF(C13:C20>=60,C13:C20))),該公式為數組公式,按<Ctrl Shift Enter>三鍵結束。

公式解析:首先用IF函數得出滿足條件的年齡,然後用AVERAGE函數求平均值。

也可以用公式:=AVERAGE(IF((B13:B20=’男’)*(C13:C20>=60),C13:C20)),該公式為數組公式,按<Ctrl Shift Enter>三鍵結束。

函數篇:小小IF不簡單

注意:之前說過有些地方AND和*可以互換,而該題中只能用*不能用AND。

3

多條件查找

函數篇:小小IF不簡單

公式:

=VLOOKUP(E23&F23,IF({1,0},A$23:A$30&B$23:B$30,C$23:C$30),2,0),該公式為數組公式,按<Ctrl Shift Enter>三鍵結束。

4

T IF組合

函數篇:小小IF不簡單

公式:

=SUM(VLOOKUP(T(IF({1},A33:A40)),D33:E40,2,0)*B33:B40),該公式為數組公式,按<Ctrl Shift Enter>三鍵結束。

公式解析:T(IF({1},A33:A40))部分T起降維作用,將三維降為一維;

VLOOKUP函數的第一參數不能直接為數組,通過T IF轉為記憶體數組,其返回的結果也是記憶體數組;

VLOOKUP函數部分查詢出對應單價,然後與數量相乘,最後用SUM函數求和。

5

N IF組合

函數篇:小小IF不簡單

公式:

=SUM(INDEX(E46:E53,N(IF(1,MATCH(A46:A53,D46:D53,))))*B46:B53),該公式為數組公式,按<Ctrl Shift Enter>三鍵結束。

公式解析:N和T一樣是降維作用,N可以將三維引用轉換為一維數組。

注意

數組公式有個明顯的特徵,即公式是在一對花括號裡面,而花括號不是手輸的而是按<Ctrl Shift Enter>三鍵得來的。

函數篇:小小IF不簡單

【愛上Excel合夥人】能為讀者做什麼?

我們【愛上Excel合夥人】微信訂閱號平台一直秉承簡潔、優雅、高效的為讀者分享工作中遇到的每一個Excel問題,不論是Excel技巧、函數、圖表、VBA,甚至是有關於Excel的開發,只要你能提出來問題,我們總能給你一個滿意的答案!

合夥人QQ交流社群