轉自:Excel之家ExcelHome
很多時候,尤其是Excel新手朋友,很怕看到一大串長長的公式,會有種看了就頭大的感覺。
但是你仔細研究就會發現,裡面的函數其實都很簡單。
你只要知道里面單個函數的應用規則,就能寫出看起來很有bigger的公式來。
今天就來講講威力無窮的嵌套函數吧。
嵌套函數,就是將一個函數作為另一個函數的參數,通過嵌套函數可以將一些簡單的函數一層層相扣,從而解決複雜的計算。(在公式中,最多可以嵌套64層函數)
對於較長的嵌套函數,看起來會比較“嚇人”,不過不要被其外表矇蔽,嵌套函數也僅是常用函數的使用,是個“紙老虎”,並沒有那麼難學。
❶ 嵌套函數的要求和原則
嵌套函數看起來複雜,通常有大批量或重複性的數據處理需求。
因此,寫嵌套函數時要保持公式、數據的簡潔,便於理解。函數之間互相嵌套的原則:內層函數須符合外層函數參數的規則。
❷ 怎樣閱讀嵌套函數
閱讀嵌套函數最常用的方法就是拆分,像剝洋蔥一樣,一層層剝開。
括號是函數的一個重要組成部分,一對括號就是一層函數,順著括號,由外向內一層層剝開,就能看出各函數的結構了。
如下圖所示,打開“IF嵌套函數.xlsx”文件,先試著分析一個簡單的IF嵌套函數。
這裡使用的公式是
=IF(E2>=80," ☆☆☆☆ ",IF(E2>=70," ☆☆☆ ",IF(E2>=60," ☆☆ ")))
將光標置於公式編輯欄時,Excel會自動根據函數的不同層次,將括號標上不同的顏色。一對括號前後的顏色是一致的,這裡由外至內依次是黑色、紅色和紫色。
通過分析可以發現,公式中只有一個IF函數 , 但是包含了3層,嵌套了2層,黑色的是最外層,紅色是第1層嵌套,紫色是第2層嵌套。
①由最外層可以知道,如果E2>=80,就會輸出“☆☆☆☆”,公式運算結束。如果E2<80,繼續往下執行。
②此時E2單元格中的值小於80,分析第一層嵌套可知,如果E2>=70,即 80>E2>=70,就會輸出“☆☆☆”,公式運算結束。如果E2<70,繼續往下執行。
③此時E2單元格中的值小於70,分析第二層嵌套可知,如果E2>=60,即70>E2>=60,就會輸出“☆☆”,公式運算結束。
至此,這個公式分析完成。看到這裡或許你會產生疑問,如果平均成績小於60,會顯示什麼(見下圖)。
❸ 怎樣寫出嵌套函數
那麼,該怎樣寫這樣的嵌套公式呢?
這樣長的公式,看似需要很強的邏輯才能辦得到。其實不然。如果公式較短,思路很容易理清楚,可以直接寫;如果公式較長,可以採用“先分步,後整合”的方法來寫。
打開“素材\判斷閏年.xlsx”文件,如下圖所示。
(1)選函數
① 通過對本案例的分析,如果要實現通過判斷輸出“閏年”或“不是閏年”,可使用IF函數。
② 判斷能被4整除而不能被100整除,或者能被400整除的年份,則需要使用OR函數。OR函數用於在其參數組中,任何一個參數邏輯值為TRUE,即返回TRUE;任何一個參數的邏輯值為FALSE,即返回FALSE。OR函數格式如下。
OR(logical1, [logical2], …)
logical1, logical2,…):logical1是必需的,後續邏輯值是可選的。這些是1~255個需要進行測試的條件,測試結果可以為TRUE或FALSE。
③ 判斷能被4整除而不能被100整除,則需要使用AND函數。AND函數用於返回邏輯值。如果所有參數值為邏輯“真(TRUE)”,那麼返回邏輯值“真 (TRUE)”,否則返回邏輯值 “假(FALSE)”。AND函數格式如下。
AND(logical1,logical2,…)
logical1:必需參數。要測試的第一個條件,其計算結果可以為TRUE或FALSE。
logical2, ...:可選參數。要測試的其他條件,其計算結果可以為TRUE或FALSE,最多可包含255個條件。
④判斷整除,則需要使用MOD函數。MOD函數用於返回數字除以除數後得到的餘數,結果的符號與除數相同,其格式如下。
MOD(number,divisor)
number:必需參數。表示要在執行除法後找到其餘數的數字。
divisor:必需參數。表示除數。
(2)分步寫出公式
年數能被4整除:MOD(A2,4) =0。
不能被100整除:MOD(A2,100)>0。
能被400整除的年份:MOD(A2,400)=0。
(3)逐步整合
能被4整除而不能被100整除的年份:
AND(MOD(A2,4)=0,MOD(A2,100)>0)。
能被4整除而不能被100整除,或者能被400整除的年份:
OR(AND(MOD(A2,4)=0,MOD(A2,100)>0), MOD(A2,400)=0)。
根據判斷輸出“ 閏年” 或“ 不是閏年”:
=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)>0), MOD(A2,400)=0)," 閏年 "," 不是閏年 ")。
最終結果如下圖所示。