T85-秋叶EXCEL@King直播 第四课【公式篇】

【导图来源】来自Excel King老师真人视频精讲，戴个黑框眼镜的帅哥，公式纯熟，举一反三。 【内容包含】 SUM函数、多条件判断、计算后判断、逻辑判断、公式不计算错误案例、不连续求和、跨行求和、模糊匹配求和、IF函数排错、WPS函数手册。 【小钟共享】希望我的总结助你提升Excel技能，成为办公效率达人。

秋叶EXCEL@King直播 第四课【公式篇】

小学：SUMIF基础

案例1，快速求和

ALT+=快速求和

函数的结构

函数名

参数

返回值

案例2，SUMIF函数基础用法

公式如下：=SUMIF($H$17:$H$27,L17,$I$17:$I$27)

行列锁定

$用来锁定行列

按下F4键，快速切换锁定状态

1||| 相对引用

2||| 绝对引用

3||| 混合引用

SUMIF函数结构如下

初中：逻辑判断求和

案例1，统计非行政部门的奖金总和

公式如下：=SUMIF(H5:H9,"<>行政",I5:I9)

函数公式中常用的判断符号

<> 不等于

>= 大于等于

<=小于等于

> 大于

< 小于

= 等于

判断条件要写在双引号" "当中

案例2：统计前3名总销量

公式如下：=SUMIF(J15:J25,"<=3",I15:I25)

案例3：根据账龄统计库存

公式如下：

<3年：=SUMIF(J30:J40,"<3",I30:I40)

≥3年：=SUMIF(J30:J40,">=3",I30:I40)

高中：日期判断求和

案例1：根据产品、账龄统计库存总和

公式如下：=SUMIFS($I$6:$I$16,$H$6:$H$16,M6,$J$6:$J$16,N6)

SUMIFS函数结构

案例2：统计不同账龄的总库存

公式如下：

1年：=SUMIF(J23:J33,1,I23:I33)

如果判断标准是数字，可以不写双引号

2~5年：=SUMIFS(I23:I33,J23:J33,">=2",J23:J33,"<=5")

如果是某个数字区间的判断，需要拆分成2个条件

然后使用SUMIFS函数来完成，多条件求和

不要使用AND函数，这个一个错误的思路

6~10年：=SUMIFS(I23:I33,J23:J33,">=6",J23:J33,"<=10")

案例3：统计每个月的总销量

添加辅助列，用MONTH函数计算出月份，再用SUMIF求和

公式如下：=MONTH(G40)

SUMIF公式如下：=SUMIF($K$40:$K$47,M40,$J$40:$J$47)

小学：IF函数基础

案例1：在单身人员的K列备注"相亲"

公式如下：=IF(J5="单身","相亲","")

知识点

1||| 所有文本，必须用英文双引号包裹起来

如"单身"，而不是单身，或者中文双引号“单身”

2||| 如果要返回空白文本，需要用两个英文双引号作为参数，

不能不填，否则会返回false或true

3||| 何时需要添加双引号

只要不是数字、不是函数名、不是单元格引用，都需要添加双引号

IF函数的结构如下：

案例2：备注胜出队伍名称

公式如下：=IF(I18>H18,J18,G18)

案例3：对比本月销量趋势，并添加备注

公式如下：=IF(I28>H28,"↑","▼")

特殊字符的输入

"↑"，搜狗输入法，输入"上"，第5个备选项

特殊字符的输入

"▼"，搜狗输入法，按下Ctrl+Shift+Z，打开特殊字符对话框，选择即可。

初中：多条件判断

案例1：判断两门成绩都>60分

公式如下：=IF(AND(H5>60,I5>60),"是","不是的")

案例2：判断销量在60~110之间

公式如下：=IF(AND(H16>=60,H16<=110),"是","")

知识点

1||| 对于数字区间的判断，要拆分成两个条件来计算

2||| 并用AND函数来判断，是否同时满足这两个条件

案例3：判断销量>100或销售额>1000

公式如下：=IF(OR(H30>100,I30>1000),"是","")

高中：计算后判断

案例1：根据性别识别码，判断男女性别

公式如下：=IF(MOD(I4,2)=1,"男","女")

MOD函数结构

案例2：判断检测日期是否超90天或3个月

>=90天公式如下：=IF(I16-H16>=90,"是","")

>3个月公式如下：=IF(DATEDIF(H16,I16,"M")>=3,"是","")

小学，常见函数错误

案例1：公式不计算

单元格被保存成了文本格式，所以公式未计算

解决方法

1||| 先设置单元格格式为【常规】

2||| 然后分列一下，直接点击完成，就可以了。

案例2：数字求和为0

数字被保存成为了文本格式，导致无法正常求和。

解决方法1

选择单元格，在悬浮的功能选项中，选择【转换为数字】就可以了。

解决方法2

1||| 复制一个空白单元格

2||| 使用选择性粘贴，以加的形式粘贴到目标单元格即可。

初中，函数容错

案例1：如何纠正错误

公式如下：=IFERROR(H5/I5,"")

案例2：如何纠正错误

公式如下：=IFERROR(I19*H19,"")

知识点

1||| 数字中包含文本单位，会导致计算错误

2||| 用查找替换，删除所有的文本单位即可。

高中，函数学习方法

官方手册

输入公式后，点击fx按钮，即可打开参数窗口

WPS函数手册

1||| 在线网址

2||| 374例函数公式学表格

3||| 函数学习计划表

Excel小问小答，学习周刊

联系班主任获取

进阶选修课知识点

案例1，不连续单元求和

1||| Ctrl+G定位空白单元格

ALT+=快速求和

2||| SUMIF函数，公式如下：

=SUMIF($G$5:$G$18,J5,$H$5:$H$18)

案例2：跨行求和

公式如下:=SUMIF($H$24:$H$38,K24,$I$24:$I$38)

案例3：模糊匹配求和

通配符

*，代表任意长度，任意字符

?，代表任意一个字符

公式如下：

=SUMIF($G$44:$G$54,J44&"*",$H$44:$H$54)

COUNTIF函数，公式如下

=COUNTIF($G$44:$G$54,J44&"*")

案例4: IF函数排错

两个条件，要拆分成两个判断

60<H5<80应该用AND函数来表达

AND(60<H5,H5>80)

完整公式如下：

=IF(AND(60<H5,H5<80),"良","")

案例5: 阶梯型判断

所有的字符，都要用英文双引号" "包裹起来

正确的公式如下：

=IF(H17<=60,"C",IF(H17<=80,"B","A"))

【公式篇】作业练习表

附件链接