excel教学视频百度网盘(Excel使用技巧之Excel进阶-核心函数讲解)

1.基础函数（1）自动求和自动求和时，excel会自动生成一个SUM函数，shi

函数结构如下，以等号开头，

（2）插入函数在上方的公式工具栏中，点击函数或者界面上方的Fx按钮，都可以弹出公式选择对话框，

选好函数后点击确定，开始选择计算区域，

选好区域点击确定即可获得计算结果。（3）简单函数

相同的只需要一组数据的函数还有，最大值最小值函数，

函数的使用也可以直接在界面上方的函数编辑行中按照上述格式手动输入函数名，并选择计算区间，注意左右括号将数据区域包含起来，输入完毕按下Enter，获得计算结果，

（4）逻辑函数IF

选择插入IF函数，此时生成对话框需要输入三个数据，第一栏用于输入判断条件，

之后通过点击选择需要判断的内容，以及判断条件，这里是C7>=60，意义就是判断C7单元格中的内容是否大于等于60，

第二行的内容是我们在判断条件成立时要输入框中的内容，比如大于等于60，我们输入及格，第三行是判断条件不成立是输入的内容，相应的我们输入不及格。

然后鼠标拖动进行批量计算。

如果想要让两种结果显示的更明显，我们可以在上方开始工具栏中，点击“条件格式”按钮，下拉菜单中点击“突出显示单元格规则”，选择“等于”，

在弹出对话框中，左侧输入框输入想要突出的内容，

如果要实现多个分支的判断，如分等级判断，if的两个分支就显得不足了（5）逻辑函数IFS

这里ifs函数提供了多个逻辑判断条件，以及对应的显示内容，将不同等级的判断条件输入后，相应的输入显示内容，点击确定即可。

注：这里的IFS函数只有在Excel 2016及之后的版本中才有，如果版本低于2016，可以通过IF函数嵌套的方式实现IFS的相同功能

这种格式不能通过对话框来实现输入，只能在函数栏中手动书写公式内容。

上面IFS实现的功能，可以通过如上手写公式来代替https://zhidao.baidu.com/question/1500683814066657139.html"iferror"和"IF(ISERROR"使用时的区别是什么？。

2.VLOOKUP函数如果想要实现一个条件查询功能，输入一个同学的学号，就能自动计算获得他的平均分，总分等，按照前面讲过的操作通过对话框添加函数，

第一个输入框是关键字，即判断的依据，这里就是要输入的查询学生学号的单元格，

第二个输入框是数据范围，这里我们通过鼠标选中所有的学生成绩，

注：vlookup有一个特点，就是他只能根据数据源区域的最左一列作为查询依据，因为我们查询依据是学号，所以选中区域的最左侧是学号，如果以姓名为查询依据，我们就要以姓名为数据区域的最左侧，一种方式是不选择学号区域，另一种方式就是调整学号和姓名的范围。

第三栏是查询内容所在列号，我们要查询姓名，因为姓名所在位置是第二列，所以在输入框中输入阿拉伯数字2，

第四栏输入内容用于控制是否使用精准匹配，只有两个值“TRUE”和“FALSE”，TRUE表示开启精准匹配，即只有学号确实为103才能被搜索到，

同时，为了使用批量计算，需要将分数区间锁定，不随着计算区域的变化向下移动，我们在上方的公式中选中区域后按下F4，就可以将数据区域锁定，

3.IFERROR函数（1）书写IFERROR函数iferror函数用于在计算有错误时提供错误提示信息，

将前一小节的计算公式插入到公式的第一部分，在第二部分书写错误提示内容，

而如果要保持在没有学号输入的时候，查询位置也保持空，就要再使用一个if函数，将刚才写好的函数添加到if中，

（2）检查功能点击上方数据工具栏，点击“数据验证”按钮，

在弹出的对话框中，可以规定单元格数据的输入格式和取值范围，

另外，检查功能还提供了提示功能，

以及输入内容后的错误提示信息，

另外，验证和提示信息需要删除的话，直接在对话框第一个标签页点击“全部清除”按键即可，

4.COUNTIFS&SUMIFS函数（1）COUNT函数从函数的名称就可以看出，这个函数的功能是计数，即统计数据区域中数据的数量，需要注意的是COUNT函数只能统计数字类型的资料，在统计中会自动忽略文字等其他格式数据，

（2）COUNTA函数COUNTA函数的使用方式和COUNT函数完全相同，唯一不同的就是他可以统计除空格外的所有单元格数目，

（3）COUNTIF函数COUNTIF函数的功能是计算符合条件的单元格数目，

整个函数的结构如下，

例如，如果想要统计使用手机支付的信息数目，只需要选定数据范围后，将条件设置为手机即可，另外需要注意的是，如果条件是文本格式，需要将文本用双引号括起来。

（4）COUNTIFS函数COUNTIFS函数的出现就是为了改进COUNTIF函数只能输入一个条件的限制，它可以输入多个数据范围和计算条件，

例如，如果计算娱乐类别中使用信用卡支付的数量，我们需要分别选取类别数据区和交易方式数据区，并将娱乐和信用卡作为条件，这里还有一个小技巧，可以使用单元格代替公式中直接输入的内容，这样可以通过修改单元格中的条件，避免直接编辑公式，

这里条件还可以是数学公式，例如统计选用手机消费且金额大于1000的数量，一种方式如下，直接将>1000写入到公式中，

另一种方式是保留大于号并在后面添加一个&符号，表示连接内容，之后将数据保存在一个单元格中，

另外，日期的计算和数据一样，通过大于号和小于号表示日期之后和之前，

（5）SUMIFS函数SUMIFS函数与COUNTIFS函数唯一的不同点就是前者是将符合条件的数据求和，而后者是计数，公式唯一不同点就是SUMIFS函数多一个求和范围，】

例如计算六月份使用信用卡消费的总金额，相比之前的条件统计，只需要将消费金额列添加到公式的第一部分，

5.单元格定义名称（不常用）如果我们想要给一个数据区域定义一个名称以便于记忆和操作，在上方公式工具栏中，点击定义名称按键，

此时excel会自动获取表头名称作为新名称，也可以通过手动输入定义新的名称，输入好后点击确定按键即可实现单元格定义名称功能。

而另一种简单的命名方式是选中，命名区域后，直接在界面左上角的输入框中输入想要命名的名称即可。

成功命名后，在使用这个命名区域进行计算时，直接使用这个区域的名称即可，不用再通过手动输入或者鼠标拖动选择区域的方式，

使用COUNTIF函数时也是如此，

已定义名称的管理也十分方便，在上方的公式工具栏中点击名称管理器，再弹出的对话框中可以对已定义名称进行编辑，

在编辑框中可以编辑名称和定义范围，

6.INDIRECT函数（二级菜单，百度）首先制作一个下拉菜单，在上方数据工具栏选择数据验证，在弹出对话框中验证条件选择序列，并框选数据范围，即可制作早餐类型的下拉菜单，

然后在上方公式工具栏中点击“根据所选内容创建”，根据表头所在位置，勾选最左列选项，

我们同样通过设置数据验证的方式，只不过此时的数据范围为一个函数，INDIRECT函数，并将参数固定为早餐类型单元格所在位置，

7.单元格中提取信息（非常简单&好理解）（1）LEFT函数

函数的参数有两个，一个是源数据的位置，另一个参数是要抓取的字数，这个函数的功能就是从资料位置的最左侧选取n个字的内容，

效果如图所示。

（2）RIGHT函数RIGHT函数的作用与原理和LEFT函数作用相同，只不过抓取的位置是从数据的最右边开始，

（3）MID函数MID函数比LEFT和RIGHT函数的作用相同，都是抓取n个长度内容，只不过多一个参数开始位置，

从源数据的某个位置开始向后选择n个长度的内容，

（4）FIND函数FIND函数的作用是在源数据中找到某个字符从左侧数的位置，

另外，对于资料来源中的重复字母，可以通过定义查询起点位置，来查找相应的字母位置，

（5）LEN函数计算源数据内容的长度，

8.数据查询（比较重要，可以放到函数章节）（1）HLOOKUP函数 HLOOKUP函数和我们之前讲过的VLOOKUP函数原理相同，只不过它计算的是横向表格存储的数据，

（2）INDEX函数

INDEX函数用于返回查找的数据范围内，某一位置的单元格内容，如下，对于单行或者单列，只需要两个参数，数据范围和所在位置，

如果数据范围是二维的，则需要三个参数，数据范围，所在行和所在列，

（3）MATCH函数MATCH函数的功能与INDEX相反，是返回查找对象所在的位置信息，但这里要注意，查找的范围只能是单行或者单列，不能是二维数组形式，第三个参数是对比方式，提供了1/0/-1三个参数，1代表小于查找范围的；0代表等于查找范围的；-1代表小于查找范围的。

（4）INDEX函数&MATCH函数首先按照前面讲过的方式构建一个下拉菜单，用于选择分公司、业绩、考核成绩

再构建一个下拉菜单用于选择查询员工的名字，

先通过MATCH函数获取员工所在行数，

再通过MATCH函数获得查询信息所在行数，在能够获得行坐标和列坐标后，通过INDEX函数就可以获得相应位置的内容，将两个函数写进INDEX函数，如此，一个员工信息查询就完成了

（5）Xlookup函数9.随机函数（这个主要用来编数据）（1）RANDBETWEEN函数（随机整数）RANDBETWEEN函数的作用是随机产生一个范围内的数字，

下面的内容没什么用，可以不讲现在我们已经能产生一个随即序号，现在就可以使用之前讲过的INDEX函数，将产生的随即序号对应到相应的姓名即可，

如果按下键盘的F9，随机函数就可以重新计算

另外，如果要实现随机分配的目标，也可以使用随机函数和INDEX函数的组合来实现，

（2）CHOOSE函数（随机文字）CHOOSE函数就是根据第一个参数，来返回相应的待选项，

通过CHOOSE函数可以实现26.1中的功能，而且不需要添加啊多余的辅助栏，

（3）RAND函数（随机小数）RAND函数与RANDBETWEEN函数的区别在于RAND函数是产生0-1之间不重复的数字，如果想要实现等人数分组，首先借助RAND函数对每个人产生一个0-1之间不重复的随机数，

编数据=INT(G14*(RAND()*4+1) + RANDBETWEEN(2000,3000))=(E3*RANDBETWEEN(1,3)+1234)*10下面这个例子有点复杂之后通过RANK函数，对每个人对应的随机数进行大小排序，之后将排名除以每个组的人数，再借助ROUNDUP函数，将结果进位到最接近的整数，。

之后将计算结果使用CHOOSE函数，将数字转换为组名，

10.布尔逻辑（1） 单条件布尔运算布尔逻辑有两种结果True或False，和IF函数的结果一样，而excel中简单的IF函数通常也可以直接通过布尔表达式替代，如下所示，在布尔状态栏中直接输入等号后，输入布尔表达式，这里是E3单元格大于等于1000，如果数值符合，则直接显示为True，否则是False，

相较IF函数而言， 结构更为简洁，而且不仅是数值比较，文本比较同样可以。

（2）多条件布尔运算AND函数就是只有所有条件都符合的时候，才会得到True结果，只要有一个不符合，就是False

例如，挑选出消费次数大于五次，并且消费金额大于1000，就要将两个条件都输入AND函数中，

与AND函数相对的是OR函数，OR函数的判断逻辑是，只要判断条件中有一个条件符合，就可以输出Ture结果，只有当所有的条件都是False时，OR的结果才是False

例如，挑选出消费金额大于1000或者缴纳年费的消费者，使用OR函数即可实现，

AND函数与OR函数是可以向其他函数一样进行嵌套使用的，例如筛选条件变成两个，当缴纳年费或者（消费大于1000元并且消费次数大于5次），就可以通过公式的嵌套实现，

如果想要将布尔运算的结果True/False转化成相应的文字，可以通过IF函数实现这一转换

另外，还可以使用之前讲过的数据标注功能，将符合条件的单元格填充不同的颜色加以突出，只需要将布尔公式粘贴到格式化规则中即可，

（3）SUMPRODUCT函数可以直接实现计算多个物品单价乘以数量之后求和的功能，如下图所示，

或者实现计算不同成绩不同权重成绩求和，如下所示，

（4）布尔函数与SUMPRODUCT函数混合使用布尔函数的计算结果，True的值可以用1表示，False用0表示，通过布尔函数之间的乘法，可以得到两个条件都符合的人，因为只有两个1相乘结果才是1，再通过SUMPRODUCT函数就可以将这些1进行求和，从而统计符合多个筛选条件的人的数量，

例如统计缴纳年费且居住在东区的消费者数量，

而在此基础上可以求出这些消费者的消费总和，只需要将消费金额数据栏加入到SUMPRODUCT函数的乘法中即可，

11.数组公式基础概念我们经常在一些excel函数公式两边看到添加有大括号{}，我想大部分新手同学看到大括号就晕以后兰色写的函数教程中也常会出现它到底这个大括号是什么神秘符号，今天兰色很有必要提前介绍一下。

想学好函数的同学也一定要耐心把下面的教程看完先从一个简单的计算公式说起：=A1*B1它的结果为20，只有一个数。

而如果让多个数与B1相乘，会是什么结果呢？=A1:A5*B1结果是分别返回一个相乘的结果值。即返回的是一组值：20;40;50;60;30，由于单元格无法同时显示多个结果，所以显示为错误值。

如果让一列数与另一列数相乘是什么结果呢？=A1:A5*B1:B5结果是相对应的行一对一相乘，几行数会返回几个结果：20;8;35;48;27

说了这么多，同学们只需要了解：excel中的运算后返回值的个数有两种：1个数和 1组数那么如果一个公式中含有返回一组数的表达式时，就需要用数组运算即在公式后按ctrl+shift+enter三键自动添加大括号{}。

当然也有例外，象lookup、sumproduct函数就可以直接执行数组运算，而不需要添加大括号说到这里有些同学还是有些迷惑，这个倒底有什么用，兰色下面举两个小例子【例1】如下图所示表销售统计表中，要求根据销售数量，计算所有人员提成之和（提成 10元/个）。

如果用一般的方法，公式应该为：=2*10+4*10+5*10+6*10+3*10=200用数组方法：{=SUM(B2:B6*10)}套用开始的理论，因为B2:B6*10计算后返回多个结果，所以公式要添加大括号。

【例2】计划B2：B2区域总共有多少字数。

公式：{=SUM(LEN(B2:B4))}len(B2:B4)要返回每个单元格的字符数，返回的是一组数，所以该公式也要添加大括号。

案例实战

放款明细.xlsx 真实数据.xlsx 目的：根据合同号（E列匹配A列）找到最后还款日期方法：=MAX((E:E=A2)*ROW(E:E))；MAX函数找到行号，Index函数确认内容；ROW(E:E)表示E列所有的行号；(E:E=A2)*ROW(E:E)表示的是所有匹配(E:E=A2)的行号；MAX表示最大的行号；然后注意数组公式，注意

Ctrl+Shift+Enter三连；3连之后会有个大括号找到行号后，用INDEX函数找到匹配内容：=INDEX(F:F, B2)；其中F列为要找到的内容，B2为行号难点：MAX函数找行号的时候，需要使用数组函数的3键：Ctrl+Shift+Enter。

参考帖子1（寻找最后匹配的字符，常规Match函数返回的是第一个字符）：http://club.excelhome.net/thread-942497-1-1.html参考帖子2（数组3键）：https://

zhidao.baidu.com/question/1707116519464991380.html备注：C列也可以合并成一个公式：=INDEX(F:F, MAX((E:E=A2)*ROW(E:E)))；然后注意

Ctrl+Shift+Enter三连目的：根据合同号（E列匹配A列）找到最后还款日期方法：=MAX((E:E=A2)*ROW(E:E))；MAX函数找到行号，Index函数确认内容；ROW(E:E)表示E列所有的行号；(E:E=A2)*ROW(E:E)表示的是所有匹配(E:E=A2)的行号；MAX表示最大的行号；然后注意数组公式，注意Ctrl+Shift+Enter三连

找到行号后，用INDEX函数找到匹配内容：=INDEX(F:F, B2)；其中F列为要找到的内容，B2为行号难点：MAX函数找行号的时候，需要使用数组函数的3键：Ctrl+Shift+Enter参考帖子1（寻找最后匹配的字符，常规Match函数返回的是第一个字符）：

http://club.excelhome.net/thread-942497-1-1.html参考帖子2（数组3键）：https://zhidao.baidu.com/question/1707116519464991380.html

备注：C列也可以合并成一个公式：=INDEX(F:F, MAX((E:E=A2)*ROW(E:E)))；然后注意Ctrl+Shift+Enter三连速查表18个Excel最常用的公式运算技巧总结18个Excel最常用的公式运算技巧总结

一、查找重复内容公式：=IF(COUNTIF(A:AA2)>1”重复””") 二、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6”2009/8/30″FALSE))/3600) 三、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E274)”/”

MID(E2112)”/”MID(E2132)) 四、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)=15IF(MOD(MID(C2151)2)=1”男””女”)IF(MOD(MID(C2171)2)=1”男””。

女”))公式内的“C2”代表的是输入身份证号码的单元格 五、求和： =SUM(K2:K56) ——对K2到K56这一区域进行求和； 六、平均数： =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数；。

七、排名： =RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名； 八、等级： =IF(K2>=85”优”IF(K2>=74”良”IF(K2>=60”及格””不及格”))) 九、学期总评： =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平

时总评”、“期中”、“期末”三项成绩； 十、最高分： =MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分； 十一、最低分： =MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分；

十二、分数段人数统计： (1) =COUNTIF(K2:K56”100″) ——求K2到K56区域100分的人数;假设把结果存放于K57单元格； (2) =COUNTIF(K2:K56”>=95″)-K57 ——求K2到K56区域95～99.5分的人数;假设把结果存

放于K58单元格； (3)=COUNTIF(K2:K56”>=90″)-SUM(K57:K58) ——求K2到K56区域90～94.5分的人数;假设把结果存放于K59单元格； (4)=COUNTIF(K2:K56”>=85″)-SUM(K57:K59) ——求K2到K56区域85～89.5分的人数;假设

把结果存放于K60单元格； (5)=COUNTIF(K2:K56”>=70″)-SUM(K57:K60) ——求K2到K56区域70～84.5分的人数;假设把结果存放于K61单元格； (6)=COUNTIF(K2:K56”>=60″)-SUM(K57:K61) ——求K2到K56区域60～69.5分的人数;假设

把结果存放于K62单元格； (7) =COUNTIF(K2:K56”<60″) ——求K2到K56区域60分以下的人数;假设把结果存放于K63单2020/5/26 18个Excel最常用的公式运算技巧总结

https://www.xuexila.com/excel/hanshu/22215.html 2/2元格； 说明：COUNTIF函数也可计算某一区域男、女生人数 如：=COUNTIF(C2:C351”男”) ——求C2到C351区域(共350人)男性人数；。

十三、优秀率： =SUM(K57:K60)/55*100 十四、及格率： =SUM(K57:K62)/55*100 十五、标准差： =STDEV(K2:K56) ——求K2到K56区域(55人)的成绩波动情况(数值越小，说明

该班学生间的成绩差异较小，反之，说明该班存在两极分化)； 十六、条件求和： =SUMIF(B2:B56”男”，K2:K56) ——假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和；

十七、 多条件求和： {=SUM(IF(C3:C322=”男”IF(G3:G322=110)))｝ ——假设C列(C3:C322区域)存放学生的性别，G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5)，则此

函数返回的结果表示求 一班的男生人数;这是一个数组函数，输完后要按Ctrl+Shift+Enter组合键(产生“{……｝”)“{｝”不能手工输入，只能用组合键产生 十八、根据出生日期自动计算周岁：=TRUNC((DAYS360(D3NOW( )))/3600)。

———假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁参考文章：刘伟的Office系列链接：https://pan.baidu.com/s/11I9ZClGH-ukl5QmdUndQVQ。

提取码：ud42 复制这段内容后打开百度网盘手机App，操作更方便哦--来自百度网盘超级会员V7的分享12.课程相关资源笔者获取方式：微信号获取添加如下微信：huaxz001 笔者网站：华小智首页王宇韬相关课程可通过：。

京东链接：[https://search.jd.com/Search?keyword=王宇韬]，搜索“王宇韬”，在淘宝、当当也可购买加入学习交流群，可以添加如下微信：huaxz001（请注明缘由）

各类课程可在网易云、51CTO搜索王宇韬，进行查看。

亲爱的读者们，感谢您花时间阅读本文。如果您对本文有任何疑问或建议，请随时联系我。我非常乐意与您交流。