Excel 如何实现函数IF的嵌套超过七层

古都西安小寨 2011-11-30

展开全文

Excel 如何实现函数IF的嵌套超过七层

if 超过7层如何办

1. 将七层之外的IF语句，放在另外的单元格内来处理，例：C5=if(if,...,(if...),B5))),B5单元格就是存放七层之外的IF语句。依此类推，可以实现在数据库语言中CASE语句的功能。

2. IF 函数的确有七层嵌套的限制。遇到七层嵌套还解决不了的问题，可以尝试用其它的函数组合和数组公式来解决；有时用 VBA 方案可以有很好的效果。

这里给出一个解决IF函数嵌套超出范围的方法，可能比较容易使初学者看懂。其思路是：一个单元格做不了的事，分给两个或更多的单元格来做，文字内容是这样，函数内容也是这样。

例子：假如 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","超出范围"))

根据情况，可以将 C、D、E 这些从事辅助运算的单元格放在其它任何地方

3. 一个单元格也可以实现

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",""))(数组形式输入)。

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、可以用自定义数字格式。也可以用=CHOOSE(A1+1,"一二三四五六七八九十")

问：实际上我的要求是现行高一成绩统计中:b=if(a1="语文","语文",if(a1="数学","数学",if(a1="英语","英语",if(a1="物理","物理",if(a1="化学","化学",if（a1="历史","历史",if(a1="政治",政治",if(a1="生物","生物“，if(a1="地理","地理")))))))),这样超过了7层。我不知如何处理。因为下面的公式要引用语、数、英、等。

答：新建一表，取名Data，找一区域设置名称为SubjectTable：

语文 Chinese

英语 English

=vlookup(SubjectTable,a1,2,false)可以有65536个，够了吧。其实，稍加改进，理论上，可以有达到你硬盘空间的个数。或用if和or的组合可以解决15个。

再举个例子：

=IF(A16="","",IF(B16="","样办尚未交",IF(OR(B16="内部检查中",B16="数据查询中",B16="数据查询中"),CONCATENATE(IF(B16="内部检查中","品质检测中",""),IF(B16="数据查询中","图纸未确认",""),IF(B16="为不合格","需要修正","")),CONCATENATE(IF(C16="客户检查中","待客回复",""),IF(C16="合格","待P/O生产",""),IF(C16="取消","客户取消",""),IF(C16="为客户设变中","客户设变中",""),IF(C16="不合格","需要修正","")))))

AVERAGEIFS 函数返回满足多个条件的所有单元格的平均值

返回满足多重条件的所有单元格的平均值（算术平均值）。

语法
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Average_range   是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。
Criteria_range1, criteria_range2, …   是计算关联条件的 1 至 127 个区域。
Criteria1, criteria2, …   是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格求平均值。例如，条件可以表示为 32、"32"、">32"、"苹果" 或 B4。

注解
如果 average_range 为空值或文本值，则 AVERAGEIFS 会返回 #DIV0! 错误值。
如果条件区域中的单元格为空，AVERAGEIFS 将其视为 0 值。
区域中包含 TRUE 的单元格计算为 1；区域中包含 FALSE 的单元格计算为 0（零）。
仅当 average_range 中的每个单元格满足为其指定的所有相应条件时，才对这些单元格进行平均值计算。
与 AVERAGEIF 函数中的区域和条件参数不同，AVERAGEIFS 中每个 criteria_range 的大小和形状必须与 sum_range 相同。
如果 average_range 中的单元格无法转换为数字，则 AVERAGEIFS 会返回 #DIV0! 错误值。

如果没有满足所有条件的单元格，AVERAGEIFS 会返回 #DIV/0! 错误值。
您可以在条件中使用通配符，即问号 (?) 和星号 (*)。问号匹配任一单个字符；星号匹配任一字符序列。如果要查找实际的问号或星号，请在字符前键入波形符 (~)。

注释   AVERAGEIFS 函数用于计算趋中性，趋中性是统计分布中一组数中间的位置。三种最常见的趋中性计算方法是：
平均值   是算数平均，其计算方法是先对一组数求和再除以这组数的个数。例如，2、3、3、5、7 和 10 的平均值是 5，即用 30 除以 6。
中值   是一组数的中间数，就是说，这组数的一半大于中值，另一半小于中值。例如，2、3、3、5、7 和 10 的中值是 4。
众数   是在一组数中出现频率最高的数。例如，2、3、3、5、7 和 10 的众数为 3。
对于对称分布的一组数来说，这三种趋中性计算方法是相同的。对于偏态分布的一组数来说，这三种趋中性计算方法可能不同。

示例：求学生的平均成绩
   A B C D
1 学生      第一次测验成绩  第二次测验成绩   最终测验成绩
2 艾美丽  75                        85                       87
3 朱莉      94                       80                       88
4 韩维      86                       93                       不及格
5 付德庆  不及格                 75                       75
公式说明（结果）
=AVERAGEIFS(B2:B5,B2:B5,">70",B2:B5,"<90") 计算所有学生第一次测验成绩在 70 分到 90 分之间的分数的平均值 (80.5)。标记为“不及格”的分数不计算在内，因为它不是一个数字值。
=AVERAGEIFS(C2:C5,C2:C5,">95") 计算所有学生第二次测验成绩超过 95 分的分数的平均值。因为均未超过 95 分，所以返回 #DIV0!。
=AVERAGEIFS(D2:D5,D2:D5,"<>不及格",D2:D5,">80") 计算所有学生最终测验成绩超过 80 分的分数的平均值 (87.5)。标记为“不及格”的分数不计算在内，因为它不是一个数字值。

示例：求房地产的平均价格
   A                                B            C            D          E
1 类型                           价格        城镇       卧室数  是否有车库？
2 舒适 Rambler             230000   依萨跨   3           否
3 温暖舒适的平房         197000   毕尔褔   2           是
4 凉爽的酷得角             345678   毕尔褔   4           是
5 豪华分割级                321900   依萨跨   2           是
6 高级都铎式建筑         450000   毕尔褔   5           是
7 漂亮的殖民时代建筑 395000   毕尔褔   4           否
公式说明（结果）
=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函数应用实例（一）

2010-04-14 11:47:41 　　来源：IT部落窝　　浏览：54573次

　　vlookup函数应用实例一：
　　问题：如下图，已知表sheet1中的数据如下，如何在数据表二 sheet2 中如下引用：当学号随机出现的时候，如何在B列显示其对应的物理成绩？

　　根据问题的需求，这个公式应该是：=vlookup(a2,sheet1!$a$2:$f$100,6,true)
详细说明一下在此vlookup函数例子中各个参数的使用说明：
　　第一，vlookup是垂直方向的判断，如果是水平方向的判断可使用Hlookup函数
　　第二，a2 是判断的条件，也就是说如果sheet2表中a列对应的数据和sheet1表中的数据相同方能引用；
　　第三，sheet1!$a$2:$f$100 是数据跟踪的区域，因为需要引用的数据在f列，所以跟踪的区域至少在f列，$是绝对引用。
　　第四，6 这是返回什么数的列数，如上图的物理是第6列，所以应该是6，如果要求英语的数值，那么此处应该是5。
　　第五，是否绝对引用，如果是就输入 true 如果是近似即可满足条件，那么输入false （近似值主要用于带小数点的财务、运算等）。
　　结果如下图：

　　vlookup函数实例讲解二：说明函数=VLOOKUP(f1,A1:E100,2,FALSE)的意思。
　　意思是：在A1:E100区域查找f1的值，找到后，返回从a开始算的第2列值（即b列），false为精确查找的必备参数。

　　vlookup函数实例讲解三：

　　上图，A2：B5为参照数组范围，E2为欲搜寻的值，传回数组表的欲对照的栏为第2栏(姓名)，在F2输入=VLOOKUP(E2,A2:B5,2,FALSE)将会找到155003是王小华，然后显示出来。

　　vlookup函数实例讲解四：　
　　A　　 B　　　C　　 D
1 编号　姓名　工资　科室
2 2005001 张三 2870 办公室
3 2005002 李四 2750 人事科
4 2005006 郑化 2680 供应科
5 2005010 屠刚红 2980 销售科
6 2005019 王五 2530 财务科
7 2005036 孟小庭 2200 工会

A列已排序（第四个参数缺省或用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列没有排序，要得出正确的结果，第四个参数必须用FALAE。

　　解释：关于TRUE和FALSE的应用
　　先举个例子，假如让你在数万条记录的表格中查找给定编号的某个人，假如编号已按由小到大的顺序排序，你会很轻松地找到这个人；假如编号没有排序，你只好从上到下一条一条地查找，很费事。用VLOOKUP查找数据也是这样，当第一列已排序，第四个参数用TRUE（或确省），Excel会很轻松地找到数据，效率较高。当第一列没有排序，第四个参数用FALSE，Excel会从上到下一条一条地查找，效率较低。个人觉得，若要精确查找数据，由于计算机运算速度很快，可省略排序操作，直接用第四个参数用FALSE即可。

　　vlookup函数实例讲解五：
　　本示例搜索员工表的 ID 列并查找其他列中的匹配值，计算并测试错误条件。
A　　 B　C 　　D 　　E
ID 姓氏名字职务出生日期
1 李小明销售代表 12/8/1968
2 林彩瑜销售部副总 2/19/1952
3 王志东销售代表 8/30/1963
4 潘金销售代表 9/19/1958
5 林丹销售经理 3/4/1955
6 苏术平销售代表 7/2/1963
公式说明（结果）
　　=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "未找到员工", VLOOKUP(5,A2:E7,2,FALSE)) 如果有 ID 为 5 的员工，则显示该员工的姓氏；否则，显示消息“未找到员工”。(林)
　　当 VLOOKUP 函数返回错误值 #NA 时，ISNA 函数返回值 TRUE。
　　=IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "未找到员工", VLOOKUP(15,A3:E8,2,FALSE)) 如果有 ID 为 15 的员工，则显示该员工的姓氏；否则，显示消息“未找到员工”。（未找到员工）
　　当 VLOOKUP 函数返回错误值 #NA 时，ISNA 函数返回值 TRUE。
　　=VLOOKUP(4,A2:E7,2,FALSE) & " " & VLOOKUP(4,A2:E7,3,FALSE) & "是" & VLOOKUP(4,A2:E7,4,FALSE) & "。" 对于 ID 为 4 的员工，将三个单元格的值连接为一个完整的句子。（潘金是销售代表。）

　　利用vlookup函数进行查询系统设计，请详见：vlookup函数应用实例（二），网址是：http://www./show.asp?id=379

LOOKUP函数关联阅读文章：
《Excel中LOOKUP函数的用法详解》
《Excel中向量型的LOOKUP函数实例》
《Excel中数组型的LOOKUP函数实例》

《excel函数vlookup的用法》
《vlookup函数应用实例（二）》

《Excel中HLOOKUP函数实例（一）：自动判断并获取数据》
《Excel中HLOOKUP函数实例（二）：实现查询功能》

Excel中LOOKUP 函数的用法是：可返回一行或一列区域中或者数组中的某个值。

　　LOOKUP 函数具有两种语法形式：向量型和数组型。矢量形式的 LOOKUP 函数在一行或一列区域（称为向量）中查找值，然后返回另一行或一列区域中相同位置处的值。数组形式的 LOOKUP函数在数组的第一行或列中查找指定值，然后返回该数组的最后一行或列中相同位置处的值。

　　向量型的LOOKUP 函数的语法是：LOOKUP(lookup_value,lookup_vector,result_vector)
　　向量型的LOOKUP 函数语法其中的参数如下介绍：
　　第一，Lookup_value 是 LOOKUP 在第一个矢量中搜索到的值。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。
　　第二，Lookup_vector 是一个仅包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。
　　说明：lookup_vector 中的值必须按升序顺序排列。例如，-2、-1、0、1、2 或 A-Z 或 FALSE、TRUE。否则，LOOKUP 返回的值可能不正确。大写和小写文本是等效的。
　　第三，Result_vector 是一个仅包含一行或一列的区域。它的大小必须与 lookup_vector 相同。

　　数组型的LOOKUP 函数的语法是：LOOKUP(lookup_value,array)
　　数组型的LOOKUP 函数语法其中的参数如下介绍：
　　第一，Lookup_value 是 LOOKUP 在数组中搜索到的值。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。
　　第二，如果 LOOKUP 找不到 lookup_value，它会使用该数组中小于或等于 lookup_value 的最大值。

本文我们介绍通过利用VLOOKUP函数实现海量数据里快速获得精确查询。

　　我们以IT部落窝的流量统计表（如下图）为例加以说明整个查询步骤。

　　第一步： A列是日期，B列是流量数据。我们在C3和C5单元格中分别输入提示文字“输入日期：”和“流量IP是：”。

　　第二步：按Ctrl键不放并单击A列和B列以全部选中这两列，然后执行“插入→名称→定义”命令调出定义名称对话框，为选中的区域设置好名称（如：“流量信息”），单击“添加”后再单击“确定”返回。

　　第三步：点选D5单元格，输入公式：=IF(ISNA(VLOOKUP(D3,流量信息,2,FALSE)),"没有找到",VLOOKUP(D3,流量信息,2,FALSE))。

　　解释说明：如果在D5单元格，输入公式：=VLOOKUP(D3,流量信息,2,FALSE)。一样可以实现查询，但当D3单元格为空或者输入了不匹配的内容时，D5单元格将显示为“#N/A”，虽不影响使用，但感觉还是不太好。所有我们结合IF和ISNA函数来使用，这样更合适一些。
　　关于ISNA函数的用法请参考：excel isna函数的用法和实例，网址是：http://www./show.asp?id=380

　　到此，一个效率奇快的数据查询系统就建立完成了，我们现在只需要在D3单元格输入任意一天的日期，并回车执行,在D5单元格就会显示出这一天的流量数据了。

本文我们介绍通过利用VLOOKUP函数实现海量数据里快速获得精确查询。

　　我们以IT部落窝的流量统计表（如下图）为例加以说明整个查询步骤。

　　第一步： A列是日期，B列是流量数据。我们在C3和C5单元格中分别输入提示文字“输入日期：”和“流量IP是：”。

　　第三步：点选D5单元格，输入公式：=IF(ISNA(VLOOKUP(D3,流量信息,2,FALSE)),"没有找到",VLOOKUP(D3,流量信息,2,FALSE))。

Excel中HLOOKUP函数实例（一）：自动判断并获取数据

2010-07-10 17:33:45 　　来源：IT部落窝　　浏览：4445次

　　Excel中HLOOKUP函数的用法和VLOOKUP函数的用法相似，只是vlookup函数是垂直方向的判断，而HLOOKUP函数是水平方向的判断。具体使用方法请参考《excel函数vlookup的用法》，网址是：http://www./show.asp?id=274

　　实例：使用HLOOKUP函数自动判断并获取数据

　　本实例中列出了不同的值班类别所对应的工资标准。现在要根据统计表中的值班类别自动返回应计工资，此时可以使用HLOOKUP函数。详见下图。

　　使用HLOOKUP函数自动判断并获取数据的操作步骤如下介绍：

　　第一，根据不同的值班类别建立工资标准表，将实际值班数据输入到工作表中。

　　第二，选中F7单元格，输入公式：=HLOOKUP(E7,$A$3:$F$4,2,0)

　　即可根据日前类别返回相应的工资标准。

　　第三，再次选中F7单元格，向下复制公式，即可。

Excel中HLOOKUP函数实例（二）：实现查询功能

2010-07-10 18:28:16 　　来源：IT部落窝　　浏览：4035次

　　我们在上一篇文章讲解了使用Excel中使用HLOOKUP函数自动判断并获取数据。本文我们再讲一个HLOOKUP函数的使用实例。

　　实例：使用HLOOKUP函数实现查询功能

　　本实例中统计了学生的各科目成绩，现在想建立一个查询表，查询指定科目的成绩，此时也可以使用HLOOKUP函数。详见下图。

　　使用HLOOKUP函数实现查询功能的操作步骤如下介绍：

　　第一，在H1单元格制作下拉选项，制作方法为：单击菜单“数据——有效性——设置——允许——序列，在来源输入下拉列表的内容（语文,数学,英语,物理），用半角下的逗号隔开。

　　第二，选中H2单元格，输入公式：=HLOOKUP($H$1,$B$1:$E$7,ROW(A2),FALSE)，按下回车键确定，即可根据H1单元格的科目返回第一个成绩，向下复制公式，可依次得到其他学生的成绩。

　　第三，当需要查询其他科目成绩时，只需要在H1单元格中选择相应科目即可。

Excel中HLOOKUP函数实例（二）：实现查询功能

我们在上一篇文章讲解了使用Excel中使用HLOOKUP函数自动判断并获取数据。本文我们再讲一个HLOOKUP函数的使用实例。

　　实例：使用HLOOKUP函数实现查询功能

　　本实例中统计了学生的各科目成绩，现在想建立一个查询表，查询指定科目的成绩，此时也可以使用HLOOKUP函数。详见下图。

　　使用HLOOKUP函数实现查询功能的操作步骤如下介绍：

　　第三，当需要查询其他科目成绩时，只需要在H1单元格中选择相应科目即可。

Excel自动排名次和显示排名第一的姓名

在下面的excel表中，已有姓名和成绩两列数据，求名次，以及排名第一最高分的姓名。

　　第一步，单击C2单元格，输入公式：=RANK(B2,B:B)，然后将公式向下复制填充即可完成学生成绩自动排名。

　　第二步，求最高分的姓名。首先增加一辅助列，把A列的所有姓名复制到E列。然后在D2单元格输入公式：=VLOOKUP(LARGE(B:B,1),B:E,4,FALSE)，按下回车键确定，即可求出排名第一最高分对应的姓名。

　　第三步，单击D2单元格，右键选择“复制”，选择“选择性粘贴”，粘贴为“数值”。这样D2单元格就转为一个普通的数值了，我们可以发现选中D2单元格，已经没有公式了。这样就可以删除E列无用的数据了。

　　注：如果不进行“选择性粘贴”，粘贴为“数值”这一步操作，而直接删除E列数据，则D2单元格会出错，显示为“0”。

以上就是在excel中实现自动排名次和显示排名第一对应的姓名的操作。

Excel中的LARGE函数使用帮助

用途：返回某一数据集中的某个最大值。可以使用LARGE 函数查询考试分数集中第一、第二、第三等的得分。

语法：LARGE(array，k) 参数：Array 为需要从中查询第k 个最大值的数组或数据区域，K为返回值在数组或数据单元格区域里的位置(即名次)。实例：如果B1=59、B2=70、B3=80、B4=9 0、B5=89、B6=8 4、B7=92，，则公式“=LARGE(B1，B7，2)”返回90。

ISNA函数，是用来检测一个值是否为#N/A，返回TRUE或FALSE。ISNA 值为错误值 #N/A（值不存在）。

　　ISNA函数，通常是和其与函数结合使用，比如我们常见到的，使用vlookup函数时，配合if函数和isna函数进行返回值＂#N/A＂为空的更正。

　　ISNA函数常见用法介绍：

　　在使用vlookup函数时，如果查询表单中的第一列在被查询表单的第一列中没有时，则返回“#N/A”，表示返回值不可用。这同时也带来一个问题，就是“#N/A”单元格所在的列和行都不能进行加和（如sum()）或其他计算了，因为最后的计算结果肯定也是“#N/A”。因此，必须将“#N/A”通过某种方法改成空或0，这时就一般需要用if函数结合isna函数来进行。