excel if函数 if函数嵌套用法

excel if函数 if函数嵌套用法

excel函数中 if函数的使用非常广泛，特别是在单条件判断的时候，用好 if函数可以帮我们完成很多功能。

最简单的 excel if函数应用

例子：下图数据在d列显示如下结果：如果数据1大于60则显示合格，否则显示不合格。

那么在d2单元格输入以下公式：

=if(a2>60,"合格","不合格")

然后向下拖拽，自动生成数据，如下图D列效果。

if函数必须的条件：

每一个 if函数必须使用英文的括号括起来；

括号内为三个数据，第一个数据是条件（如上例中的a2>60），第二数据为满足第一个数据后返回的结果，通常使用英文的引号括起来，第三个数据是不满足第一个数据时需要返回的结果；（如果不输入第三个数据可以吗，当然可以，返回什么结果自己试试吧）

经常出现的错误：

其中的符号如逗号和引号皆为英文（也就是所谓的半角）；

if的右括号放在了条件的后面；（这是在多个条件使用if函数进行嵌套时非常容易犯的错误）

if函数嵌套用法

例子：下图数据，在e列显示如下结果：如果数据1小于60则显示不合格，如果大于等于60而小于80则显示合格，如果大于等于80而小于90显示良好，如果大于等于90则显示优秀。

这是经典的if嵌套应用例子，需要我们使用 if函数的嵌套。

if嵌套书写前，首先你要理解要求，并将要求数学化，也就是使用数学的模式表达出来，if函数多重嵌套一般情况下我们可以将它看做分段函数，那么问题就很容易解决了。例子可以在E2单元格使用如下代码：

=if(a2<60,"不合格",if(a2<80,"合格",if(a2<90,"良好","优秀")))

当数据1小于60时，显示不合格，这时在“不合格”逗号的右侧默认就是>=60的情况，那么根据题意，只需再满足<80即可显示合格，于是我们将最简单的 if函数的第三个数据变成了一个if函数，依次类推，每一次可以将一个if函数作为每一个基本函数的第三个数据，从而形成多种嵌套。

（图例中多余在最后一个 if前后加了一个括号，当然这种方法也正确，但不是最简单的。）

其实还有另一种写法，也就是将嵌套的if写在基本if函数的第二个数据的位置，如下图，不过这种写法不常用，也比较不好理解，并且容易写错，不推荐大家使用。

if函数的高级用法

if函数除了可以引用单元格的数据还可以引用函数值或者其他表格甚至是文件的数据。下面举个小例子：

下图数据1中，如果超过平均值的显示合格，达不到平均值的显示不合格。

函数代码如下：

=if(a2>average($a$2:$a$9),"合格","不合格")

这句话是这样理解的，当数据a2大于a2到a9所有数据的平均数时，返回合格，否则返回不合格。

其中average( )是求平均数函数，而$a$2:$a$9是绝对定位a2到a9的区域，如果不加$这个符号，我们在拖动单元格自动生成数据时a2就会变成a3，如果横向拖动时a2就会变成b2，这可不是我们想要的。关于这一点你可以自己尝试，如想了解更多关于excel绝对引用的知识可以参考这里，这篇文章还讲到了对其他表格的引用，有兴趣可以看看。

这个例子只是简单的函数举例，你可以根据自己的需要使用更加复杂的函数，将其和if函数组合使用，可以得到非常广阔的使用范围，大家可以自己试试，我只是简单阐述，你有什么感受可以留言。

excel数据引用公式的使用方法

现实中excel的功能已经能够帮助我们解决很多问题。今天一个同事问我了一个问题，说能否建立一个excel模板文件自动检测其他的excel文件，检测其中的数据，如果受检测的excel文件做任何数据的改动，这个模板文件会自动更新数据。想完成这一目的，使用excel的引用即可。

excel数据引用分为四种，我们给大家简单介绍下：

1、excel数据相对引用。这种最为简单，通常大家都知道，就是普通的公式计算。简单介绍几个公式，相信大家都很清楚。

1）、在d2单元格中需要计算：数据一和数据二的平均数与数据三的差。就可以输入

=(A2+B2)/2-C2

或者输入

=sum(A2:B2)/2-C2

其中，括号必须是英文状态或者是半角中文状态，其中的列号ABC输入大小写没有关系，都会自动变成大写。如果求和的数据过多则不要用加号了，用sum求和函数较为方便。（求括号内英文冒号两侧的单元格之间的数据之和）sum函数不但可以求横向的行之和、竖向的列之和，还可以求对角线所辖区域的矩形区域之和。

2）、计算：数据一、数据二、数据三的最大值与最小值的差，可以输入：

=max(a2:d2)-min(a2:d2)

其中，max是求最大值函数，min是求最小值函数。和sum一样，这两个函数都支持对角线所辖区域矩形的数据运算。

3）、计算：如果数据一大于1500，则综合数据是数据一、数据二、数据三求和，若数据一介于1000至1500之间则计算数据一和数据二之和，若数据一低于1000，则只记录数据一的结果。（这个例子在公司计算工资的时候很常用，但是要非常小心边界的判断，需要的时候可以用>=表示大于等于，用<=来表示小于等于）关于这个if判断语句的写法要非常小心括号的运用，我们可以在有图早期的一篇 个人所得数计算公式 中看到多次嵌套的运用。

=IF(A2>1500,SUM(A2:C2),IF((A2<1000),A2,SUM(A2:B2)))

这三个例子主要是介绍大家常见的三种模式：直接运算，用excel的函数进行运算，用程序语句进行运算。（这种提法是我自己随便想的，不一定有真正的三中模式之说。）

2、excel数据的绝对引用。参加了办公自动化培训班的朋友和自学excel的朋友应该对他不陌生。举例说明一下，还以上面的那些数据为例：

计算数据一中每个数据与最大值的差。（呵呵，也很常用的，就是计算平级的人或者部门之间的差距）

我们一般会在d2单元格输入

=MAX(A2:A6)-A2

可是我们无法通过拖动，生成但d3、d4、d5等下面单元格的数据，当我们拖动的时候，在d3单元格变成了

=MAX(A3:A7)-A3

这显然不是我们要的，我们在d3单元格想要的是

=MAX(A2:A6)-A3

所以，我们可以使用$来固定那些需要不变的数据，如上面a2单元格的公式就可以写成

=MAX(A$2:A$6)-A2

随你怎样拖动，max计算的区域就不会变化了。如果你想固定的更牢靠一点，可以在列标号前加$。

3、同文件内excel表间引用。同文件内不同表之间的数据引用和计算通常是使用英文状态的引号表名结合而来的，例如本例中：

引用表1当中的a列数据，可以在a2单元格输入

='1'!a2

其中英文单引号中为表的名字，表和单元格名称间用英文的!分割开来。

4、不同文件间的excel引用。使用英文的中括号 [ ]来引用文件，如我们将正在操作的文件保存到d盘，默认名字为 新建一个excel文件，引用文件中第一列的数据：

=[]1!a2

此例和上面的例子中英文的单引号 ' 可要可不要，不过excel默认都会给加上，另外本例中，当我们输入完公式，打回车键之后，excel会自动加上文件地址，如本例中可能会变为：

='d:[]1'!a2

好了，上面详细介绍了excel数据引用公式的实例用法，不知道你是否已经清楚如何解决我们在文章开头提出的问题

如何使用Excel 进行 专业图表制作

首先做图表，必须得有数据，创建你获得的数据，或者导入数据是你制作表格的第一步

例如：1.首先创建这样一个表格数据2.通过 “插入 来选择你需要的是柱形图 还是 饼图 或者其他图形

3.然后 右击 出现的空白地区，出现下拉列表，“选择数据”。在出现浮窗的时候 再图表数据区域会是空的

这个时候在excel里面框出你需要的数据源就可以了 它自动会生成你的图形和数据 如果需要修改也可以通过右击 改变坐标格式 来实现横竖坐标的数据，文字等信息的自我设定。

4.对于你已经绘制后的图形，你不满意，想不改变数据 变成其他的图形图 可以通过“设计”-》“更改图表类型”来实现更改。

结果很简单：

5，如果还需要加入附和的变化信息：

例如：

财季

Q1

Q2

Q3

Q4

利润

￥323,300.00

￥456,600.00

￥428,790.00

￥453,910.00

折扣率

21.56%

15.88%

17.23%

13.16%

前面操作如上， 到绘制后你会发现 折扣率在下方是个小矩形 选中矩形地方右击 选择“设置数据系列格式”勾中次坐标轴下方会变为红色矩阵覆盖，也不好看，继续右击选择“更改图标类型” 选择个 折线图 就看起来很美观 也能体现变化了。

二、 实现比较矩形图

项目 上期 本期

通信费

交际费

交通费

办公费

水电费

培训费

181

156

143

151

101

181

122

121

143

185

166

127

1. 先把数据表格重新整理下 形成对比数据的表格

然后重复1-4步骤。这时只需要选中 增加/减少/本期当中 当中的1个“更改图形类型”为指定的就可以实现对比图形了，另外如果你需要这些对比的数据能在图上明显的表示出来

请直接右击-》添加数据标签 就可以实现了

[验证]电子表格中查找重复记录的方法

2010-07-10 00:23

假设要查找A列中的重复记录！

方法如下：

1.在B1（或者C1、D1...都可以）中手动输入公式（好像复制的不行）：

＝COUNTIF(A:A,A1&"")

2.然后打“√”完成公式编辑，选定B1向下推拽。

3.在B列显示数字为相同记录的数目（1为无重复，2为有一个重复，3为有2个重复，依次类推）。

4.以B列降序排列，排在前面的就是重复记录。如果要删除重复，只要删除对应的行直到B列对应位置显示为1。

Excel中countif函数用法

语法

COUNTIF(range,criteria)

Range 为需要计算其中满足条件的单元格数目的单元格区域。

Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用

或文本。例如，条件可以表示为 32、"32"、">32" 、"apples" 或 B4。

说明

Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。

若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用 SUMIF 工作表函数。

若要使公式返回两个基于条件的值之一，例如某指定销售金额的销售红利，可使用 IF 工作表函数。

若要计算空或非空单元格的个数，可使用 COUNTA 和 COUNTBLANK 函数。

示例 1：通用 COUNTIF 公式

如果您将示例复制到空白工作表中，可能会更易于理解该示例。

A B

1 数据 数据

2 apples 32

3 oranges 54

4 peaches 75

5 apples 86

公式 说明（结果）

=COUNTIF(A2:A5,"apples") 计算第一列中苹果所在单元格的个数 (2)

=COUNTIF(A2:A5,A4) 计算第一列中桃所在单元格的个数 (2)

=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) 计算第一列中柑桔和苹果所在单元格的个数

(3)

=COUNTIF(B2:B5,">55") 计算第二列中值大于 55 的单元格个数 (2)

=COUNTIF(B2:B5,"<>"&B4) 计算第二列中值不等于 75 的单元格个数 (2)

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85") 计算第二列中值大于或等于 32 且小于或等于 85 的单元格个数 (3)

计算80到89之间的人数

=countif(B1:B10,">=80")-countif(B1:B10,">90")

假设B1:B10为你的数据区域.

2009-11-08 12:20

Excel中能熟练地掌握函数的技巧，在水文日常工作中能达到事半功倍的效果。例如：要计算A1:An(定义：data)区域中非零的单元格的平均值，可在单元格中输入=sum(data)/countif(data, "<>0")。下面具体介绍Countif()函数的高级技巧如下：

一、求各种类型单元格的个数

所求类型单元格 公式 说明

真空单元格

真空+假空单元格

非真空单元格

文本型单元格

区域内所有单元格

数字型单元格

=COUNTIF(data1,"=")

=COUNTIF(data1,"")

=COUNTIF(data1,"<>")

=COUNTIF(data1,"*")

=COUNTIF(data1,"<>""")

=COUNT(data1)

相当于countblank()函数

相当于counta()函数

假空单元格也是文本型单元格

不是countif()，这里顺便点一下。

备注：data1为用户定义单元格区域，如用户把a1:d50区域定义为data1，以下data2、data3、data4、data5类同data1，也为用户定义的单元格区域。

二、求大于或小于等于某个值的单元格个数

所求内容

大于50

等于50

小于50

大于或等于50

小于或等于50

大于E21单元格的值

等于E21单元格的值

小于E21单元格的值

大于或等于E21单元格的值

小于或等于E21单元格的值

公式

=COUNTIF(data2,">50")

=COUNTIF(data2,50)

=COUNTIF(data2,"<50")

=COUNTIF(data2,">=50")

=COUNTIF(data2,"<=50")

=COUNTIF(data2,">"&$E$21)

=COUNTIF(data2,$E$21)

=COUNTIF(data2,"<"&$E$21)

=COUNTIF(data2,">="&$E$21)

=COUNTIF(data2,"<="&$E$21)

三、求等于或包含某N个特定字符的单元格个数

所求内容

两个字符

两个字符并且第2个是B

公式

=COUNTIF(data3,"??")

=COUNTIF(data3,"?B")

包含B

第2个字符是B

等于“你好”

包含D38单元格的内容

第2字是D38单元格的内容

备注：countif()函数对英文字母不区分大小写

四、不连续区域求个数

所求内容

三个区域中>=60

三个区域中=70

三个区域中<60

=COUNTIF(data3,"*B*")

=COUNTIF(data3,"?B*")

=COUNTIF(data3,"你好")

=COUNTIF(data3,"*"&D38&"*")

=COUNTIF(data3,"?"&D38&"*")

公式

=SUM(COUNTIF(INDIRECT({"a1:a10","b2:b20","c3:c30"}),">=60"))

=SUM(COUNTIF(INDIRECT({"a1:a10","b2:b20","c3:c30"}),70))

=SUM(COUNTIF(INDIRECT({"a1:a10","b2:b20","c3:c30"}),"<60"))

五、两个条件求个数

所求内容

>10并且<=15

>=10并且<15

>=10并且<=15

>10并且<15

>10并且<=15

六、数据的有效性验证

首先，选择要设置有效性验证区域（如：A1:A100，定义为：data5）中的任一单元格(假设为：A1)，在excel工作表中，用鼠标依次点击菜单栏数据→有效性菜单命令，打开“数据有效性”对话框，在“允许”下拉按钮中设置有效性条件为“自定义”，在“公式”框中输入 =COUNTIF(data5,A1)<2，然后，复制A1单元格,选中除A1外的所有单元格（A2:A100），用鼠标依次点击编辑→选择性粘贴命令→粘贴→有效性验证。这样即实现了数据data5区域中不重复的验证条件。

公式

=SUM(COUNTIF(data4,">"&{10,15})*{1,-1})

=SUM(COUNTIF(data4,">="&{10,15})*{1,-1})

=SUM(COUNTIF(data4,{">=10",">15"})*{1,-1})

=SUM(COUNTIF(data4,{">10",">=15"})*{1,-1})

=COUNTIF(data4,">10")-COUNTIF(data4,">15")

关于=COUNTIF(F6:N6,"*")的相关问题:本人在用到公式=COUNTIF(F6:N6,"*")时，出现错误，我在所选的区域内（有M、＃、*、和数字1、0），在用公式求*个数时，所得个数为非数字相加之和，而分别求M、＃、*、1、0个数时得出的个数是正确的，同样的公式为什么求出不同的结果，很是郁闷，请各位帮忙！！ 本来想把表考上，但考不上来。

"*"代表除数字外的任何符号，包括两个以上的符号、字符如“#”、“#￥”、“#222”、“B1”、以及以文本方式出现的数码“123”等的通配符，但不代表数字，包括以1.00E+12这样的方式表示的数字；所以解决与*号匹配的问题时，要在*号前加~符号，即“~*”仅代表*号。公式改为 =COUNTIF(F6:N6,"~*")即可。