Excel 如何實現(xiàn)函數(shù)IF的嵌套超過七層

if 超過7層如何辦 

1.     將七層之外的IF語句，放在另外的單元格內(nèi)來處理，例：C5=if(if,...,(if...),B5))),B5單元格就是存放七層之外的IF語句。依此類推，可以實現(xiàn)在數(shù)據(jù)庫語言中CASE語句的功能。 

2.     IF 函數(shù)的確有七層嵌套的限制。遇到七層嵌套還解決不了的問題，可以嘗試用其它的函數(shù)組合和數(shù)組公式來解決；有時用 VBA 方案可以有很好的效果。 

這里給出一個解決IF函數(shù)嵌套超出范圍的方法，可能比較容易使初學(xué)者看懂。其思路是：一個單元格做不了的事，分給兩個或更多的單元格來做，文字內(nèi)容是這樣，函數(shù)內(nèi)容也是這樣。 

例子：假如 A1＝1，則 B1＝A；A1＝2，則 B1＝B …… A1＝26，則 B1＝Z 

解決方法如下： 

B1 = IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",C1)))))))) 

C1 = IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF(A1=14,"N",IF(A1=15,"O",IF(A1=16,"P",D1)))))))) 

D1 = IF(A1=17,"Q",IF(A1=18,"R",IF(A1=19,"S",IF(A1=20,"T",IF(A1=21,"U",IF(A1=22,"V",IF(A1=23,"W",IF(A1=24,"X",E1)))))))) 

E1 = IF(A1=25,"Y",IF(A1=26,"Z","超出范圍")) 

根據(jù)情況，可以將 C、D、E 這些從事輔助運算的單元格放在其它任何地方 

3.        一個單元格也可以實現(xiàn) 

4.       =IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",""))))))))&IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF(A1=14,"N",IF(A1=15,"O",IF(A1=16,"P",""))))))))&IF(A1=17,"Q",IF(A1=18,"R",IF(A1=19,"S",IF(A1=20,"T",IF(A1=21,"U",IF(A1=22,"V",IF(A1=23,"W",IF(A1=24,"X",""))))))))&IF(A1=25,"Y",IF(A1=26,"Z",""))(數(shù)組形式輸入)。

5.       以一例：a1=1,2,3,4,5,6,7,8,9,10 

6.        b=if(a1=1,"一",if(a1=2,"二",if(a3=3，"三",.......if(a1=9,"九",if(a1=10,"十")))))),if超過7層不起作用，我該如何辦 

解答：1、b=if(a1>5,if(a1=6,"六"。。。。。。))，明白意思？就是截為兩段再做判斷，這樣可以不超過7重。 

2、可以用自定義數(shù)字格式。也可以用=CHOOSE(A1+1,"一二三四五六七八九十") 

問：實際上我的要求是現(xiàn)行高一成績統(tǒng)計中:b=if(a1="語文","語文",if(a1="數(shù)學(xué)","數(shù)學(xué)",if(a1="英語","英語",if(a1="物理","物理",if(a1="化學(xué)","化學(xué)",if（a1="歷史","歷史",if(a1="政治",政治",if(a1="生物","生物“，if(a1="地理","地理")))))))),這樣超過了7層。我不知如何處理。因為下面的公式要引用語、數(shù)、英、等。 

答：新建一表，取名Data，找一區(qū)域設(shè)置名稱為SubjectTable： 

語文 Chinese 

英語 English 

.. 

=vlookup(SubjectTable,a1,2,false)可以有65536個，夠了吧。其實，稍加改進，理論上，可以有達到你硬盤空間的個數(shù)。或用if和or的組合可以解決15個。 

再舉個例子： 

=IF(A16="","",IF(B16="","樣辦尚未交",IF(OR(B16="內(nèi)部檢查中",B16="數(shù)據(jù)查詢中",B16="數(shù)據(jù)查詢中"),CONCATENATE(IF(B16="內(nèi)部檢查中","品質(zhì)檢測中",""),IF(B16="數(shù)據(jù)查詢中","圖紙未確認",""),IF(B16="為不合格","需要修正","")),CONCATENATE(IF(C16="客戶檢查中","待客回復(fù)",""),IF(C16="合格","待P/O生產(chǎn)",""),IF(C16="取消","客戶取消",""),IF(C16="為客戶設(shè)變中","客戶設(shè)變中",""),IF(C16="不合格","需要修正","")))))

AVERAGEIFS 函數(shù) 返回滿足多個條件的所有單元格的平均值

返回滿足多重條件的所有單元格的平均值（算術(shù)平均值）。

語法
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Average_range   是要計算平均值的一個或多個單元格，其中包括數(shù)字或包含數(shù)字的名稱、數(shù)組或引用。
Criteria_range1, criteria_range2, …   是計算關(guān)聯(lián)條件的 1 至 127 個區(qū)域。
Criteria1, criteria2, …   是數(shù)字、表達式、單元格引用或文本形式的 1 至 127 個條件，用于定義要對哪些單元格求平均值。例如，條件可以表示為 32、"32"、">32"、"蘋果" 或 B4。

注解
如果 average_range 為空值或文本值，則 AVERAGEIFS 會返回 #DIV0! 錯誤值。 
如果條件區(qū)域中的單元格為空，AVERAGEIFS 將其視為 0 值。 
區(qū)域中包含 TRUE 的單元格計算為 1；區(qū)域中包含 FALSE 的單元格計算為 0（零）。 
僅當 average_range 中的每個單元格滿足為其指定的所有相應(yīng)條件時，才對這些單元格進行平均值計算。 
與 AVERAGEIF 函數(shù)中的區(qū)域和條件參數(shù)不同，AVERAGEIFS 中每個 criteria_range 的大小和形狀必須與 sum_range 相同。 
如果 average_range 中的單元格無法轉(zhuǎn)換為數(shù)字，則 AVERAGEIFS 會返回 #DIV0! 錯誤值。

如果沒有滿足所有條件的單元格，AVERAGEIFS 會返回 #DIV/0! 錯誤值。 
您可以在條件中使用通配符，即問號 (?) 和星號 (*)。問號匹配任一單個字符；星號匹配任一字符序列。如果要查找實際的問號或星號，請在字符前鍵入波形符 (~)。 

注釋   AVERAGEIFS 函數(shù)用于計算趨中性，趨中性是統(tǒng)計分布中一組數(shù)中間的位置。三種最常見的趨中性計算方法是：
平均值   是算數(shù)平均，其計算方法是先對一組數(shù)求和再除以這組數(shù)的個數(shù)。例如，2、3、3、5、7 和 10 的平均值是 5，即用 30 除以 6。 
中值   是一組數(shù)的中間數(shù)，就是說，這組數(shù)的一半大于中值，另一半小于中值。例如，2、3、3、5、7 和 10 的中值是 4。 
眾數(shù)   是在一組數(shù)中出現(xiàn)頻率最高的數(shù)。例如，2、3、3、5、7 和 10 的眾數(shù)為 3。 
對于對稱分布的一組數(shù)來說，這三種趨中性計算方法是相同的。對于偏態(tài)分布的一組數(shù)來說，這三種趨中性計算方法可能不同。

示例：求學(xué)生的平均成績
   A B C D 
1 學(xué)生      第一次測驗成績  第二次測驗成績   最終測驗成績 
2 艾美麗  75                        85                       87 
3 朱莉      94                       80                       88 
4 韓維      86                       93                       不及格 
5 付德慶  不及格                 75                       75 
公式 說明（結(jié)果） 
=AVERAGEIFS(B2:B5,B2:B5,">70",B2:B5,"<90") 計算所有學(xué)生第一次測驗成績在 70 分到 90 分之間的分數(shù)的平均值 (80.5)。標記為“不及格”的分數(shù)不計算在內(nèi)，因為它不是一個數(shù)字值。 
=AVERAGEIFS(C2:C5,C2:C5,">95") 計算所有學(xué)生第二次測驗成績超過 95 分的分數(shù)的平均值。因為均未超過 95 分，所以返回 #DIV0!。 
=AVERAGEIFS(D2:D5,D2:D5,"<>不及格",D2:D5,">80") 計算所有學(xué)生最終測驗成績超過 80 分的分數(shù)的平均值 (87.5)。標記為“不及格”的分數(shù)不計算在內(nèi)，因為它不是一個數(shù)字值。 
 

示例：求房地產(chǎn)的平均價格
   A                                B            C            D          E 
1 類型                           價格        城鎮(zhèn)       臥室數(shù)  是否有車庫？ 
2 舒適 Rambler             230000   依薩跨   3           否 
3 溫暖舒適的平房         197000   畢爾褔   2           是 
4 涼爽的酷得角             345678   畢爾褔   4           是 
5 豪華分割級                321900   依薩跨   2           是 
6 高級都鐸式建筑         450000   畢爾褔   5           是 
7 漂亮的殖民時代建筑 395000   畢爾褔   4           否 
公式 說明（結(jié)果） 
=AVERAGEIFS(B2:B7,C2:C7,"畢爾褔",D2:D7,">2" ,E2:E7,"是")  在畢爾褔，一個至少有 3 間臥室和一個車庫的住宅的平均價格 (397839) 
=AVERAGEIFS(B2:B7,C2:C7,"依薩跨",D2:D7,"<=3" ,E2:E7,"否")  在依薩跨，一個最多有 3 間臥室但沒有車庫的住宅的平均價格 (230000)

vlookup函數(shù)應(yīng)用實例（一）

2010-04-14 11:47:41 　　來源：IT部落窩 　　瀏覽：54573次

-

　　vlookup函數(shù)應(yīng)用實例一：
　　問題：如下圖，已知表sheet1中的數(shù)據(jù)如下，如何在數(shù)據(jù)表二 sheet2 中如下引用：當學(xué)號隨機出現(xiàn)的時候，如何在B列顯示其對應(yīng)的物理成績？

 
　　根據(jù)問題的需求，這個公式應(yīng)該是：=vlookup(a2,sheet1!$a$2:$f$100,6,true)
詳細說明一下在此vlookup函數(shù)例子中各個參數(shù)的使用說明：
　　第一，vlookup是垂直方向的判斷，如果是水平方向的判斷可使用Hlookup函數(shù)
　　第二，a2 是判斷的條件，也就是說如果sheet2表中a列對應(yīng)的數(shù)據(jù)和sheet1表中的數(shù)據(jù)相同方能引用；
　　第三，sheet1!$a$2:$f$100 是數(shù)據(jù)跟蹤的區(qū)域，因為需要引用的數(shù)據(jù)在f列，所以跟蹤的區(qū)域至少在f列，$是絕對引用。
　　第四，6 這是返回什么數(shù)的列數(shù)，如上圖的物理是第6列，所以應(yīng)該是6，如果要求英語的數(shù)值，那么此處應(yīng)該是5。
　　第五，是否絕對引用，如果是就輸入 true 如果是近似即可滿足條件，那么輸入false （近似值主要用于帶小數(shù)點的財務(wù)、運算等）。
　　結(jié)果如下圖：

　　vlookup函數(shù)實例講解二：說明函數(shù)=VLOOKUP(f1,A1:E100,2,FALSE)的意思。
　　意思是：在A1:E100區(qū)域查找f1的值，找到后，返回從a開始算的第2列值（即b列），false為精確查找的必備參數(shù)。

　　vlookup函數(shù)實例講解三：

 
　　上圖，A2：B5為參照數(shù)組范圍，E2為欲搜尋的值，傳回數(shù)組表的欲對照的欄為第2欄(姓名)，在F2輸入=VLOOKUP(E2,A2:B5,2,FALSE)將會找到155003是王小華，然后顯示出來。

　　vlookup函數(shù)實例講解四：　
　　A　　 B　　 　C　　 D 
1 編號　 姓名 　工資 　科室 
2 2005001 張三 2870 辦公室 
3 2005002 李四 2750 人事科 
4 2005006 鄭化 2680 供應(yīng)科 
5 2005010 屠剛紅 2980 銷售科 
6 2005019 王五 2530 財務(wù)科 
7 2005036 孟小庭 2200 工 會 

A列已排序（第四個參數(shù)缺省或用TRUE） 
VLOOKUP(2005001,A1:D7,2,TRUE) 等于“張三” 
VLOOKUP(2005001,A1:D7,3,TRUE) 等于“2870” 
VLOOKUP(2005001,A1:D7,4,TRUE) 等于“辦公室” 
VLOOKUP(2005019,A1:D7,2,TRUE) 等于“王五” 
VLOOKUP(2005036,A1:D7,3,TRUE) 等于“2200” 
VLOOKUP(2005036,A1:D7,4,TRUE) 等于“工 會” 
VLOOKUP(2005036,A1:D7,4) 等于“工 會” 

若A列沒有排序，要得出正確的結(jié)果，第四個參數(shù)必須用FALAE。

　　解釋：關(guān)于TRUE和FALSE的應(yīng)用 
　　先舉個例子，假如讓你在數(shù)萬條記錄的表格中查找給定編號的某個人，假如編號已按由小到大的順序排序，你會很輕松地找到這個人；假如編號沒有排序，你只好從上到下一條一條地查找，很費事。 用VLOOKUP查找數(shù)據(jù)也是這樣，當?shù)谝涣幸雅判?，第四個參數(shù)用TRUE（或確?。?/FONT>Excel會很輕松地找到數(shù)據(jù)，效率較高。當?shù)谝涣袥]有排序，第四個參數(shù)用FALSE，Excel會從上到下一條一條地查找，效率較低。 個人覺得，若要精確查找數(shù)據(jù)，由于計算機運算速度很快，可省略排序操作，直接用第四個參數(shù)用FALSE即可。 

　　vlookup函數(shù)實例講解五：
　　本示例搜索員工表的 ID 列并查找其他列中的匹配值，計算并測試錯誤條件。 
A　　 B　C 　　D 　　E 
ID 姓氏 名字 職務(wù) 出生日期 
1 李 小明 銷售代表 12/8/1968 
2 林 彩瑜 銷售部副總 2/19/1952 
3 王 志東 銷售代表 8/30/1963 
4 潘 金 銷售代表 9/19/1958 
5 林 丹 銷售經(jīng)理 3/4/1955 
6 蘇 術(shù)平 銷售代表 7/2/1963 
公式 說明（結(jié)果） 
　　=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "未找到員工", VLOOKUP(5,A2:E7,2,FALSE)) 如果有 ID 為 5 的員工，則顯示該員工的姓氏；否則，顯示消息“未找到員工”。(林) 
　　當 VLOOKUP 函數(shù)返回錯誤值 #NA 時，ISNA 函數(shù)返回值 TRUE。 
　　=IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "未找到員工", VLOOKUP(15,A3:E8,2,FALSE)) 如果有 ID 為 15 的員工，則顯示該員工的姓氏；否則，顯示消息“未找到員工”。（未找到員工） 
　　當 VLOOKUP 函數(shù)返回錯誤值 #NA 時，ISNA 函數(shù)返回值 TRUE。 
　　=VLOOKUP(4,A2:E7,2,FALSE) & " " & VLOOKUP(4,A2:E7,3,FALSE) & "是" & VLOOKUP(4,A2:E7,4,FALSE) & "。" 對于 ID 為 4 的員工，將三個單元格的值連接為一個完整的句子。（潘金是銷售代表。）