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"))
• 【公式篇】作业练习表
  • 附件链接