每天清晨六点，准时与您相约

问题来源

EXCEL做数据分析的时候，经常会遇到分段统计数量的问题。今天韩老师以学生成绩分析为例，来讲述如何使用FREQUENCY函数简单方便的统计各分数段的人数。

示例数据如下图：

关键操作

函数实现

选中E2:E6单元格区域，输入公式：

“=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”，

组合键结束。

如下图：

最终结果：

公式解析

FREQUENCY函数的功能：

计算数值在某个区域内的出现频率，然后返回一个垂直数组。

语法

FREQUENCY(data_array, bins_array)

中文语法：

FREQUENCY(要统计的数组, 间隔点数组)

FREQUENCY 函数语法具有下列参数：

Data_array 必需。 要对其频率进行计数的一组数值或对这组数值的引用。 如果 data_array 中不包含任何数值，则 FREQUENCY 返回一个零数组。

Bins_array 必需。 要将 data_array 中的值插入到的间隔数组或对间隔的引用。 如果 bins_array 中不包含任何数值，则 FREQUENCY 返回 data_array 中的元素个数。

本示例中的应用

“=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”

B2:B16是要分段统计的数组；

{60,70,80,90}-0.1：是间隔点。

疑点解析：

为什么不能直接用 {60,70,80,90}？

如果直接用该数组，统计结果就0到小于等于60，大于60小于等于70，大于70小于等于80，大于80小于等于90，大于90。

为符合题目统计要求，用 {60,70,80,90}减掉一个很小的数来0.1解决。

如果成绩中有小数，可以将0.1改成更小的小数，如0.001。

素材下载

链接：http://pan.baidu/s/1slv0Zdb

密码：qldi

转载自百家号作者：Excel函数公式

Excel中的自动筛选相信大家都用过，也非常的好用，但是在实际的工作中，我们的需求往往是自动筛选无法实现的，这时候很多同学不知从何下手……其实，Excel早就为大家准备了N个超级贴心的【高级筛选】功能……今天学习之后再也不能“束手就擒”了。

一、同时满足多条件筛选。

目的：筛选出销售员“王东”对商品“手机”的销售记录。

方法：

1、选定筛选数据源。

2、【数据】-【高级】。

3、根据实际需求选择【在原有区域显示筛选结果】或【将筛选结果复制到其它位置】。

4、选择条件区域。

5、如果【将筛选结果复制到其它位置】，选择【复制到】的开始单元格并【确定】，否则直接【确定】即可。

解读：

1、需要提前准备好条件。

2、当【在原有区域显示筛选结果】时，需要查看其它数据，可以通过【清除】命令来实现。

二、满足多条件筛选。

目的：筛选出销售员“王东”和“小王”的所有记录。

方法：

1、选定筛选数据源。

2、【数据】-【高级】。

3、根据实际需求选择【在原有区域显示筛选结果】或【将筛选结果复制到其它位置】。

4、选择条件区域。

5、如果【将筛选结果复制到其它位置】，选择【复制到】的开始单元格并【确定】，否则直接【确定】即可。

解读：

1、此方法主要满足其中的一个条件即可，关键在于条件的设置，注意和上述示例中的条件进行对比，掌握要点哦！

三、按时间段筛选。

目的：筛选出2018年11月4日至11月7日的销售记录。

方法：

同上。

解读：

1、时间段不包括条件中的时间值，所以在设置时大家一定要注意哦。当然也可以设置成大于等于（>=）或小于等于（<=）的形式哦！

2、条件中除了时间值之外还有大于号（>）和小于号（<）,要一起设置哦，不然无法得到想要的结果。

四、按数值区间筛选。

目的：筛选出销售额大于等于5000，小于等于7000的数据。

方法：

同上。

解读：

1、数值不包括当前条件中的值，所以在设置时大家一定要注意哦。当然也可以设置成大于等于（>=）或小于等于（<=）的形式哦！

2、条件中除了金额之外还有大于号（>）和小于号（<）,要一起设置哦，不然无法得到想要的结果。

五、混合多条件筛选。

目的：筛选出销售员（王东、小北、小南）对商品（手机、台式机、手机）的销售记录。

方法：

同上。

解读：

1、混合多条件筛选时，需要注意同一行的关系为“And”的关系，暨“销售员”和“商品”要匹配，销售员确实销售了这个商品。同一列的关系为“Or”的关系。

六、模糊条件筛选。

目的：筛选出姓名中含有“王”字的销售员信息。

方法：

同上。

解读：

1、条件中用到的*（星号）为通配符，可以代表任意长度的字符。暨姓名中只要有“王”字就都符合条件。

七、精准完全匹配筛选。

目的：筛选出品牌为“小米”的信息。

1、常规做法（错误做法）。

方法：

同上。

解读：

从最终的筛选结果来看，信息并不是我们想要的精准信息，而是包含了以“小米”开头的所有信息。遇到这种情况时，如果处理了？

2、正确做法。

方法：

同上。

解读：

1、操作方法和表格的设置没有任何变化，但是细心的同学肯定发现了，条件发生了变化，在“小米”的前面加了=（等号）。从而达到了完全匹配的效果。

八、自定义条件筛选。

目的：筛选出金额大于等于平均金额的销售记录。

方法：

1、自定义条件，在条件单元格中输入公式：=G3>=AVERAGE(G3:G12)。

2、同上。

解读：

1、此方法非常的灵活，也非常的实用，你想按什么规则就写对应的条件即可。

2、注意事项：自定义字段中的标题行必须和数据源中的标题行不同，完全自定义，否则引起错误，得不到想要的结果。

九、筛选不重复记录。

方法：

1、选取数据源并【数据】-【高级】。

2、选择条件区域，暨和数据源相同的区域。

3、选中【选择不重复的记录】并【确定】。

解读：

1、重复记录指的是各个字段和值完全重复的值，暨不重复的记录指有一个字段或值不相同即为不重复。

2、要点在于数据源区域和条件区域相同哦！

结束语：

通过本文的学习，相信大家对“高级筛选”功能肯定有了新的认识，其实，高级筛选功能非常的强大，灵活性也很高，大家在学习的过程中要注意领会哦，如果有不懂的地方，欢迎在留言区留言讨论哦！

如何做到给大量的成绩判定是否合格，手动去判断自然是不合理的，太浪费时间了，还有一种办法就是按成绩将学生排序，然后再填充合格不合格，这个方法凑合能用，但是不符合咱大幅度提高办公效率的原则，尽量留给自己多的时间去喝喝茶看看书，岂不是更美。

如果还要给这许多的学生成绩进行评级，工作量一下子就大了很多很多很多，这是不能接受的。

看演示1 判定是否合格

合格判定：=IF(B2>=60,"合格"，"不合格")

if函数解析：=if(单元格条件，如果符合条件显示，如果不符合显示)

如果大于等于60显示合格，不符合大于等于60条件，显示不合格

看演示2 给成绩评级，我手速慢，写公式的过程太长图片就超大了。嘻嘻，所以演示的时候就直接把公式粘贴进去了，图后面有解析，不影响了。。。。。

评级：=IF(B2<60,"D",IF(B2<=70,"C",IF(B2<=80,"B",IF(B2<=100,"A"))))

1、如果分数低于60评级为D

2、不满足上条件，小于等于70判定为C

3、不满足上两个条件，小于等于80判定为B

4、不满足上三个条件，小于等于100判定为A

注意，嵌套在if函数中的if函数，都是在不符合条件的位置。

然后可以使用排序功能将各种级别各种合格不合格集中起来。。

IF函数在EXCEL中是一个基础函数，多用于条件判断，然后根据条件判断的结果返回对应的内容。今天会学网大飞老师就给大家分享一下IF函数的使用方法，我们先看一下IF函数语法组成IF(logical_test,value_if_true,value_if_false)，下边我给大家说一下对应的含义

IF(设置的条件，满足条件返回的结果，不满足条件返回的结果)

我们先看一个简单示例：合计成绩大于200的返回及格，否则不及格

IF(E3>200,"及格","不及格") ，函数参数的解释：

第一个参数：我们设定的条件，合计成绩大于200（PS：这个参数需要使用比较运算符进行判断，判断后会返回true真或false假）

第二参数：如果第一个参数条件为true真时，返回的结果，这个结果可能是文字，也可能执行的公式

第三参数：如果第一个参数条件为false假，返回的结果，这个结果可能是文字，也可能执行的公式

补充：在第一个条件设置的时间。我们可以使用的比较运算符：>大于<小于 >= 大于等于 <=小于等于 <> 不等于 = 等于

如：A3="会学网"， E3>=200等等都可以返回true或者false

第二个示例：语文，数学，英语，成绩都大于60时，返回合格，那么这时间就有个问题按照示例要求我们有三个判断条件，而IF函数在使用的时间只能写一个条件。我们就需要结合And函数使用了，and函数作用就是可以设定多个条件表达式，当多个条件表达式都满足时才返回TRUE，其中有一个条件为不满足时，就会返回false,

函数语法：And(logical1,logical2, ...)，And(条件表达式1，条件表达式2, ...)

如上图所示：我们用AND函数写的AND(B3>60,C3>60,D3>60)，会学网的英语不满足条件，所以最后返回的结果就是false

那么结合IF结合AND函数我们就可以得到这样的结果了

IF(AND(B3>60,C3>60,D3>60),"及格","不及格")，IF条件判断（多条件and(语文大于60，数学大于60，英语大于60),条件为真返回及格两字，条件为假返回不及格）

第三个示例：根据销售额评定等级，如下图所示，这个示例要求我们对一个销售额执行不通情况的判断，我们需要使用if函数嵌套if函数，原理就是先进行一种情况判断，不符合再进行第二情况判断

IF(A6>10000,"甲级",IF(A6>8000,"乙级",IF(A6>5000,"丙级",IF(A6>2000,"丁级","未评级"))))函数解释：第一次先判断是否大于10000，然后返回甲级，如果不满足这个条件，就去看false，而我们的false再次执行IF函数判断，依次类推。IF函数最重要的一点就是要按照从低到高或者从高到低顺序进行排列。就像我们示例，我们就按照从高到底的顺序。千万不能>10000,之后紧接着判断>5000,这样跳跃式的

作者：大飞老师

来源：http://huixue8/a/excelhanshu/116.html

Excel表格中 符号 大全 汇总

一、公式中常用符号

： 表示一个单元格区域，如A1:B10+ - * / 加减乘除运算符> 大于号>= 大于等于号< 小于号<= 小于等于号<>不等于号，如=IF(A1<>"销售部",,)^数字 乘方运算，如2^3结果8。^(1/数字) 开方运算 8^(1/3)结果为2& 文本连续符，如 "A"& 1 结果为 A1* 通配符 表示任意字符多个字符? 通配符，表示单个任意字符{数字} 常量数组{公式} 数组公式标志，在公式后按Ctrl + shift + Enter后在公式两端自动添加的。$ 绝对引用符号，可以在复制公式时防止行号或列标发生变动，如A&1公式向下复制时，1不会变成2,3..如果不加$则会变化！ 工作表和单元格的隶属关于，如表格sheet1的单元格A1,表示为 Sheet1!A1

二、自定义格式中的符号

0 单个数字占位符，如果0的位数大于数字个数，会用0补齐，如123设置格式00000, 会显示成 00123# 单个数字占位符，和0区别是不会补位! 强制显示它后面的字符\ 作用同 ![ ] 设置条件，如 [>100][红色] 大于100的数字显示红色yyyy 4位年d 日m 月aaa 星期代码，只显示大写数字aaaa 星期代码，显示星期+大写数字ddd 星期代码，英文简写dddd 星期代码，英文全称mmm 英文短月份mmmm 英文月份完整单词h 小时[h] 显示大于24小时的数字m 分种s 秒* 用*后的字符补齐位置，让字符长度填满单元格_ 添加一个空格和_后的字符宽度相等@ 文本占位符，可以显示单元格的文本内容，如爱你 设置@@后可以显示为爱你爱你

Excel解释说明：想整理Excel中常用符号的想法来由来以久，由于lan一直没动手，可左等右等几还不见网上有动静，所以必须要动手了，同学们收藏备用吧。

图文/ 小龙，兰色整理

作者简介：小龙, Excel精英讲师团函数班辅导、讲师。精通Excel函数应用。

昨天遇到一位同学问如下问题

如下图所示:

当A列数据为整数时，该数乘2当数据为小数时，小数部分小于等于0.5，则该数乘2加1，小数部分大于0.5时，该数乘2加2

最初写的答案是用IF函数

=IF(INT(A2)=A2,A2*2,IF(A2-INT(A2)<=0.5,INT(A2)*2+1,IF(A2-INT(A2)>0.5,INT(A2)*2+2)))

马上有同学提出来了，最后一个If是多余的，没有任何意义，因为当小数部分不小于等于0.5，那么必然会大于0.5，因此

=IF(INT(A2)=A2,A2*2,IF(A2-INT(A2)<=0.5,INT(A2)*2+1,INT(A2)*2+2))

好象是大功告成，其实....看了下面的公式，你会如何感想：

=CEILING(A2,0.5)*2

这么牛叉的函数，不学怎么行。下面就来学习一下

Ceiling函数

=ceiling(number,significance)

返回将参数 number 向上舍入（沿绝对值增大的方向）为最接近的指定基数的倍数。它有两个参数，第一参数为需要舍入的数值，第二参数为一个倍数关系。例如：

=CEILING(2.5,1)数值2.5对于1来说，向上最接近的1的倍数，就是3，因此函数结果为3

如果任何一个参数是非数值型，则 CEILING 返回 #VALUE! 错误值。不论参数 number 的符号如何，数值都是沿绝对值增大的方向向上舍入。 如果 number 正好是 significance 的倍数，则不进行舍入。如果number 和 significance 都为负，则对值按远离 0 的方向进行向下舍入。如果number 为负，significance 为正，则对值按朝向 0 的方向进行向上舍入。

如果你觉得该剧终，就错了，原来公式还可以再简化：

=CEILING(A2*2,1)

好吧，借用大家的留言：老太太倒地我都不扶，就扶你。

对于新手来说，公式还是以易懂为主，长一点没关系。但如果能象小龙老师写出这么简捷公式，是不是更帅呢？

CountIfs函数在 Excel 中用于组合多个条件区域/条件对，以弥补CountIf函数只能组合一个条件区域/条件对的不足。如果要统计同时满足两个条件的某种产品个数，用 CountIf 需要用数组组合条件，比较麻烦，而用 CountIfs 组合两个条件区域/条件对即可，比较简单明了。CountIfs 与 CountIf 一样，条件中可以用逻辑值与通配符（问号和星号）。以下就是 Excel CountIfs函数的使用方法及实例，包括不为空和多个条件的实例的具体操作方法，实例中操作所用版本均为 Excel 2016。

一、CountIfs函数语法

1、表达式：COUNTIFS(Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], …)

中文表达式：COUNTIFS(条件区域1, 条件1, [条件区域2, 条件2], …)

2、说明：

A、条件区域和条件组成一个条件区域/条件对，至少包含一个条件区域/条件对，最多只能有 127 个条件区域/条件对。

B、如果有多个条件区域，每个条件区域必须有相同的行数和列数，它们可以不相邻。

C、如果单元格为空，CountIfs 将它的值视为 0。

D、在条件中可以使用通配符问号（?）和星号（*），问号表示任意一个字符，星号表示一个或一串字符；如果要查找问号或星号，需要在它们前面加转义字符~，例如查找问号，表达式可以这样写 ~?。

二、CountIfs函数的使用方法及实例

(一）只有一个条件区域/条件对

1、假如要统计“销售地区”为“广州”的个数。选中 D13 单元格，输入公式 =COUNTIFS(D2:D12,"广州")，按回车，返回统计结果 4；操作过程步骤，如图1所示：

2、公式说明：公式 =COUNTIFS(D2:D12,"广州") 只有一个条件区域/条件对，条件区域为 D2:D12，条件为“广州”，是用文字作条件，并且可以在文字前面加等号，即 =COUNTIFS(D2:D12,"=广州")；另外，仅数值也可以作条件。

（二）有两个条件区域/条件对

1、假如要统计“销售地区”为“广州”且销量大于等于 500 的服装件数。把公式 =COUNTIFS(D2:D12,"广州",F2:F12,">=500") 复制到 F13 单元格，按回车，返回统计结果为 3，操作过程步骤，如图2所示：

2、公式中第一个条件区域/条件对为 D2:D12,"广州"，第二个条件区域/条件对为 F2:F12,">=500"，即先统计 D2:D12 中为“广州”的个数，再统计 F2:F12 中销量大于等于 500 的个数；条件2 ">=500" 也可以用 ">="&500。

（三）用通配符问号与星号组合条件

1、假如要统计以“T恤”结尾、以“白”字开头、仅有三个字的服装件数；把公式 =COUNTIFS(B2:B12,"*T恤",B2:B12,"白*",B2:B12,"???") 复制到 B13 单元格，如图3所示：

2、按回车，返回结果 1，如图4所示：

3、如果要统计以一个指定字（或词）开头且以另一个指定字（或词）结果的服装件数，例如统计以“粉”字开头且以“衬衫”结尾的服装件数，公式可以这样写：=COUNTIFS(B2:B12,"粉*衬衫")。

（四）条件为空、不为空和不等于

1、假如要统计“分类”不等于“衬衫”且“销量”为空的服装件数。把公式 =COUNTIFS(C2:C12,"<>衬衫",F2:F12,"") 复制到 F13 单元格，按回车，返回统计结果 2，操作过程步骤，如图5所示：

2、如果要统计“分类”不等于“衬衫”且“销量”不为空的服装件数，则公式可以这样写：=COUNTIFS(C2:C12,"<>衬衫",F2:F12,"<>"&"")，如图6所示：

（五）CountIfs 与 Average 组合使用

1、假如要统计“分类”为“T恤”、销售地区为“杭州”且大于平均销量的服装件数。把公式 =COUNTIFS(C2:C12,"T恤",D2:D12,"杭州",F2:F12,">"&AVERAGE(F2:F12)) 复制到 F13 单元格，按回车，返回结果 2，操作过程步骤，如图7所示：

2、在条件中，只需用 & 连接 > 与求平均值的函数 Average，如果要用其它函数也是一样的连接方法。

三、CountIfs函数与CountIf函数用多条件的比较

1、CountIfs函数可轻松组合多个条件，而CountIf函数需要用数组才能组合多个条件。假如要统计销量大于等于 500 且小于等于 800 的服装件数。用 CountIfs函数公式可这样写：=COUNTIFS(F2:F12,">=500",F2:F12,"<=800")，而用 CountIf函数，公式需要这样写：=SUM(COUNTIF(F2:F12,{">=500",">800"})*{1,-1})，两个公式统计结果都为 4；操作过程步骤，如图8所示：

2、用CountIfs函数写要简单得多，且它们的计算顺序是一样的，都先统计满足第一个条件的个数，再统计满足第二个条件的件数，因此遇到多个条件的情况，可以先选用CountIfs函数。

EXCEL基础教程5-比较运算符与文本连接符

作者：陈飞老师

EXCEL公式中比较运算符主要是用来比较大小，比较是否相等，返回一个正确（true），或者返回一个错误(false)，而比较运算符也经常用来结合函数进行判断使用

比较运算符：>(大于)<（小于）>=(大于等于)<=(小于等于) <>(不等于)，我们所有的运算符号必须在英文输入法状态下输入

第一个示例大于，查看第一个单元格内容是否大于第二个。输入等号，选择G11单元格输入大于号选择H11单元格，确实没有大于，所以返回一个false，表示错误的

第二个示例：不等于，同上边操作过程是一样的。唯一需要注意的就是运算符号的不同，需要 先写小于号，再写大于号即可。

文本连接符 &（shift+7：可以实现数值与数值连接，文本与文本连接，文本与数值，文本与函数等等

输入等号，选择单元格G15，输入连接符 &，然后选择第二个单元格，依次类推就可以了。

据许多调查显示，SUM、VLOOKUP、IF是使用量最大的三个函数，前两个函数我们都已经说过了，本次我们就一起来揭开IF函数的神秘面纱。

其实只要我们留心观察，生活中到处都充满了IF函数。

如果成绩大于60分，就能及格，否则就不及格；

如果明天下雨，我就不去露营；

如果有网，我就玩电脑，否则睡觉；

如果我是女生，我一定会嫁给他。

太多这样的例子，三天三夜都举不完。

如下是某个班级的学生成绩表，根据成绩等级评判标准，<60为不及格，60-70为及格，70-80为中等，80-90为良好，90-100为优秀，如何快速对学生进行成绩等级判定呢？

这时我们就要用到IF函数啦，先判断小于60分：=IF(C3<60,"不及格")。那60-70分的怎么判断呢？可能很多人都会想到按照数学上的方法为：=IF(C3<60,"不及格",IF(60≤C3<70,"及格"))

公式报错了。其实，在计算机中处理多条件时，和我们所学的数学的表达方式是有差别的。当条件为小于等于时，我们使用<=而不是使用≤，同理大于等于使用>=。

对于多条件，我们还需要使用到另外的两个函数AND和OR，AND表示并且的意思，只有当所有的条件都为真时，结果才为真，其余都为假；OR表示或者的意思，当条件中全部为假时，结果才为假，其余均为真。二者的语法如下：

=AND(条件1，条件2，条件n)

=OR(条件1，条件2，条件n)

则上述的公式可以更改为：

=IF(C2<60,"不及格",IF(AND(C2>=60,C2<70),"及格",IF(AND(C2>=70,C2<80),"中等",IF(AND(C2>=80,C2<90),"良好",IF(AND(C2>=90,C2<100),"优秀")))))，结果如下：

针对本例中的成绩判断，其实如果我们换一个思路，公式可以简化很多，也就不用使用AND函数。我们从高等级向低等级判断，如果大于等于90为优秀，如果不大于90但是大于等于80为优秀，只有当成绩不是优秀时，才会继续判断，所示此时第二个条件中没必要再判断是否小于90，如下：

=IF(C2>=90,"优秀",IF(C2>=80,"良好",IF(C2>=70,"中等",IF(C2>=60,"及格","不及格"))))。这样是不是简洁了许多？在

Excel函数和公式的运用中，有时候我们要多角度的考虑问题，这样才能写出更高效的公式！！！

IF函数家族起到的主要是辅助的作用，其实也不用学得太深，只要知道常用的方法就行了，如果大家学了数组函数之后，IF函数还会有一些更高级的用法，此处先不说。

在工作中

老师常常会遇到这样一种情况

就是面对学生的成绩

想要给60分及以上的学生标记为1,60分以下的标记为0

可是一个一个标实在是太慢了

那么有什么快速地方法吗？

答案肯定是有的

就是使用公式啦

那究竟要怎么做呢

跟着我一起来看看吧

以下列表格为例

公式一：=N(B2>=60)

N函数将TRUE转换成1，其他值转换成0

即当B2大于等于60时为真，返回1，其他值返回0

公式二：=IF(B2>=60,1,0)

IF函数判断是否满足某个条件，如果满足返回一个值，否则返回另一个值

即如果B2大于等于60 ，则返回1，否则返回0

公式三：=(SIGN(B2-60)<>-1)*1

SIGN函数返回数字的正负号；为正时，返回1；为零时，返回0，为负时，返回-1

即SIGN(B2-60)判断为正，返回1；SIGN(B2-60)<>-1判断为真；(SIGN(B2-60)<>-1)*1最终返回1

以上

就是判断分数是否大于等于60，大于等于60显示1，小于60显示0的3个公式

这样

老师的问题就解决了

而不仅仅是老师

我们在工作中遇到类似的问题也可以快速地解决啦

你们说是不是呢