课前预习

【第4课】公式篇 - 课前预习.png

课堂笔记

1. 函数公式的学习方法

1.1 小学函数

  • 等号右边是什么？
    • 数值
    • 文本
    • 计算 （数字计算、文本合并、逻辑判断）
    • 单元格引用
    • 函数

image.png

  • SUM，求和（单个或者区域）
  • COUNT，数字计数（只统计数字，不统计文本数）
  • COUNTA，文本计数

  • PRODUCT ，乘积计算

    1.2 初中函数

  • LEFT，提取左侧的字符

  • RIGHT，提取右侧的字符

  • REPT，重复文本

    1.3 高中函数

  • MID(text, start_num, num_chars)，从第几个字节开始提取几位字符

    1.4 逻辑函数举例

  • 逻辑判断

image.png

  • AND与OR函数

  • IF函数

    1.5 函数公式学历表

  • 函数学习清单

  • 函数计划表
  • 函数学历表的5个课程
    • 明确函数公式学习路线
    • 区分不同阶段学习难度

2. 函数公式初体验

2.1 案例1

  • =ROW()，1参函数

  • REPT，2参函数

    2.2 案例2

  • SUM跨表求和，1参函数，跨表引用

    2.3 案例3

  • COUNTIF函数，2参函数

  • 数据验证：避免出现输入重复值，可在数据有效性中自定义公式约束条件；

    • =COUNTIF($A$1:$D$5,$A1)<2

      2.4 案例4

  • IF函数，3参函数

3. 必修SUM函数

3.1 案例1，SUM求和

方法1：单元格区域引用
image.png
方法2：SUM忽略文本求和，逻辑计算
image.png

3.2 案例2，跨表求和

  • 跨表引用格式

image.png

  • 行列引用，跨表引用

  • 多重合并求和：也可使用ALT+D+P（依次按下），数据透视表跨表求和

    3.3 案例3

    方法1：相对引用求和

  • 加减运算

  • 计算逻辑（相对引用、逻辑计算）

image.png
方法2：拉灯模式求和，行列锁定
image.png

4. 必修IF函数

4.1 赠品1

  • 条件1：单身 >> 即送礼物“心意钻戒”

  • IF函数：=IF(K4=”单身”,”心意钻戒”,””)

    image.png

    4.2 赠品2

    方法1：函数嵌套，多条件判断，辅助列IF函数

  • =IF(AND(K4=”单身”,H4=”女”),”心意钻戒”,””)

image.png
方法2：IF函数+AND函数

  • AND函数

Excel【第4课】公式篇 - 图11

  • 合并写法：

    image.png

    4.3 赠品3

  • IF函数，3参函数

  • AND函数，2参函数
    • =AND(K4=”单身”,H4=”女”,J4>=20,J4<=30)
  • 逻辑计算
    • 20<年龄<30
      • 分成两个条件

（1）20<年龄
（2）年龄<30

  • 完整公式

=IF(AND(K4=”单身”,H4=”女”,J4>=20,J4<=30),”心意钻戒”,””)
image.png

4.4 赠品4

  • IF函数，3参函数
    • =IF(OR(J4=1,J4=6),”心意钻戒”,””)

  • OR函数，2参函数

    image.png
    image.png

    4.5 赠品5

  • 公式逻辑（可考虑分解条件步骤）

  • IF函数，3参函数

    • =IF(J4<=20,”心意钻戒”,IF(J4<40,”一鹿有你”,”本命佛”))

    image.png

    5. 必修纠错

    5.1 案例1

  • 错别字

    • 小学，考打字+眼力
    • 下划线

  • 样式障眼法

    • 高中，数据类型转换

    • 【公式】-【显示公式】，所有单元格现行，找差异

      image.png

      5.2 案例2

  • 样式障眼法

    • 高中，数据类型转换
    • 分列，快速修改数据格式
    • 复制空单元格，选择性粘贴，值和加，进行强制性运算

  • 日期格式

    • 肉身，衣服（常见错误：文本单元格格式）

      image.png

      5.3 案例3

  • 语法错误：函数拼写错误

  • 函数公式里记得使用英文的双引号（” “）

    5.4 案例4

  • 数据选取错误：选取区域错误

  • 相对引用与绝对引用：行列锁定

6. 本课答疑

6.1 选修1，案例1

方法1：COUNTIF函数

  • 思路：
    • 统计姓名的次数
    • 次数>1，条件格式标记

  • 公式：=COUNTIF(J$4:J$9,G4) image.png条件格式，做样式标记

    1. ![](https://cdn.nlark.com/yuque/0/2020/jpeg/8409663/1609339628311-302a855b-049f-421b-b40d-05a8f376535a.jpeg#align=left&display=inline&height=399&margin=%5Bobject%20Object%5D&originHeight=576&originWidth=501&size=0&status=done&style=none&width=347)<br />**方法2：**使用查询函数匹配

  • 思路：

    • 查询姓名
    • 查找了，返回位置，则标记样式

  • 公式：

    • MATCH：=MATCH(G4,$J$4:$J$9,0)
    • 注意：MATCH函数的“0”是精确匹配，“1”和“-1”为近似匹配，近似匹配只对数字有效，对文本不生效。

    • VLOOKUP：=VLOOKUP(G4,$J$4:$J$9,1,0)

      image.png**

      6.2 选修1，案例2

  • 思路

    • 统计姓名的次数
    • 次数判断：>1，就不准输入；<=1，则可以输入
    • 数据验证，根据判断结果限制输入：自定义规则

  • 公式

    • COUNTIF：=COUNTIF($N$4:$N$10,N4)<=1

    • 数据验证，根据判断结果限制输入

      • 自定义规则：判断规则+公式
      • 合并公式

      • 修改出错警告

           ![image.png](https://cdn.nlark.com/yuque/0/2020/png/8409663/1609341010396-56bfa4f7-7ce4-4288-a026-a614a95247d8.png#align=left&display=inline&height=286&margin=%5Bobject%20Object%5D&name=image.png&originHeight=571&originWidth=931&size=44591&status=done&style=none&width=465.5)

        6.3 选修1，案例3

  • 思路

    • 什么是组内编号
    • 判断组名是否变更
      • 合并单元格= 第一个单元格数值+一堆空值
      • 单元格是否为空值

（1）不是空值，则组名变更

（2）是空值，则组名没变更

  • 组名变更后重新编号
    • 变更了，从1开始编号
    • 没变更，上一个编号+1
      • 公式
  • 判断组名是否变更：单元格是否为空值，=IF(单元格=””,没变更,变更了)

  • 组名变更后重新编号

    • 变更了，从1开始编号
    • 没变更，上一个编号+1

    • IF(单元格=””,上一个编号+1,1)：=IF(Q4=””,S3+1,1)

        ![image.png](https://cdn.nlark.com/yuque/0/2020/png/8409663/1609341569365-a88475b9-edc4-47a7-a9be-a2868bdca5e8.png#align=left&display=inline&height=226&margin=%5Bobject%20Object%5D&name=image.png&originHeight=412&originWidth=365&size=17642&status=done&style=none&width=200)

      6.4 选修1，案例4

  • 思路
    • 什么是组内编号
    • 判断组名是否变更
      • 上下两个单元格，是否不相等

（1）不相等，则组名变更

（2）相等，则组名没变更
（3）组名变更后重新编号：变更了，从1开始编号；没变更，上一个编号+1

  • 公式
    • 判断组名是否变更
      • 上下单元格是否相等

（1）IF(上一个单元格 = 当前单元格,没变更,变更了)

（2）IF(上一个单元格 = 当前单元格,上一个编号+1,1)

  - 组名变更后重新编号：变更了，从1开始编号；没变更，上一个编号+1<br />

IF(上一个单元格 = 当前单元格,上一个编号+1,1)：=IF(V3=V4,X3+1,1)
image.png

  • COUNTIT函数：=COUNTIF($V$4:V4,V4)

    image.png

    6.5 选修2，案例1

    image.png
    方法1：IF函数

  • 条件拆分：区间范围（0-59，60-79，80-100）

  • 公式：=IF(H4<60,”不及格”,IF(H4<80,”良”,”优”))

方法2：VLOOKUP函数