T86-秋叶EXCEL@彩石直播 第五课【查询篇】

【导图来源】来自Excel彩色石头真人视频精讲，她虽未露脸，正如查找引用函数一直是个神秘的存在，素未谋面，却仰慕已久。 【内容包含】 查找引用语法解析、精确查找、公式错误，为何找不到、多条件查找、不完全/完全匹配查找、区间本找，查找家族、作业练习、XLOOKUP一招绝杀！ 【小钟共享】希望我的总结助你提升Excel技能，成为办公效率达人。

秋叶EXCEL@彩石直播 第五课【查询篇】

• 第一部分 查询作业分析
• 小学- 精确查询
  • 1. What
    • 根据员工编号，查姓名（查表）
  • 2. How
    • 插入函数：公式 =》查找与引用 =》VLOOKUP
    • 检验：修改值看查询结果
    • 复制公式：错误的区域
  • 3. Why
    • 精确查询概念
    • 步骤和要点
    • MATCH+INDEX（小结后，通用用法）
      • 1 定位： =MATCH(N5,$G$5:$G$18,0)
      • 2 查值： =INDEX($H$5:$H$18,MATCH(...))
      • 用途：查询条件不在第1列找
• 初中 -公式错误
  • VLOOKUP为什么错了?
    • 参数1，是一个值
    • 参数2，是至少包含两列数据的选区（查询区要在最左列）
      • 特例：根据简称查全称
    • 参数3，是相对于参数2的列数
• 高中- 找不到（答疑）
  • 案例1 数据都在，为什么查找不到？
    • 情况1 数据类型不一致
      • 1||| 数字转文本：TEXT()或&""
      • 2||| 文本转数字：VALUE()或--
    • 情况2 有不可见的空格或字符
      • 1||| 删除空格
      • 2||| 添加通配符
    • 情况3 有通配符等特殊字符
      • 1||| 通配符：* ?
      • 2||| 转义符：~
      • 3||| 查找这三种符号本身：~*、~?、~~
  • 案例2 查找列要在第1列，只能向右查找
    • MATCH()定位+INDEX()查值，分三步走
      • 第1步定位：=MATCH(L20,$H$20:$H$29,0)
      • 第2步查值：=INDEX($G$20:$G$29,J20)
      • 第3步合并公式：=INDEX($G$20:$G$29,MATCH(L20,$H$20:$H$29,0))
  • 案例3 查询区域需要锁定
    • 参数2 锁定区域
  • 真找不到处理
    • IFNA()
    • IFERROR()
• 小学-多条件查询
  • 1. What
    • 根据部门产品，查销量（双条件查表）
  • 2. How
    • 添加辅助列：多条件变成一个条件
      • 辅助列，F5公式：=G5&H5
      • 辅助列，K5公式：=L5&M5
    • 输入公式：精确查询
      • =VLOOKUP(K5,F5:I16,4,0)
    • 修改公式：合并公式，去除辅助列
      • =VLOOKUP(L5&M5,F5:I16,4,0)
  • 3. Why
    • 查询函数只能有一个条件
    • 将多条件转变为单一条件（资料）
      • 1||| 辅助列法（推荐）
      • 2||| IF({1,0}) 数组法
        • 合并区域公式：{=IF({1,0},$G$5:$G$16&$H$5:$H$16,$I$5:$I$16)}
        • 完整公式：
          • {=VLOOKUP(L5&M5,IF({1,0},$G$5:$G$16&$H$5:$H$16,$I$5:$I$16),2,0)}
        • 数组公式 ： Ctrl+Shift+Enter
      • 3||| XLOOKUP法
        • =XLOOKUP(L5&M5,G5:G16&H5:H16,I5:I16)
      • 4||| LOOKUP(1,0) 模糊查询法（高级用法）
        • =LOOKUP(1,0/((L5=G5:G16)*(M5=H5:H16)),I5:I16)
• 中学-多条件查询
  • 1. What
    • 从表中，查“产品”，在“1月”~“6月”列中的值
  • 2. How
    • 找公式的规律
      • 第1步 查一项的公式
      • 第2步 复制公式，确定锁定单元格
      • 第3步 找规律，参数3是一个有序的数字
    • 公式：=VLOOKUP($H5,$H$8:$N$12,COLUMN(B1),0)
  • 3. Why
    • 方法一 COLUMN()函数
      • 序号与标题行不能关联
      • 自动生成序号的方法
        • ROW()函数，纵向生成序号
        • COLUMN()函数，横向生成序号
    • 方法二 MATCH()函数法
      • 序号与标题行关联，由MATCH()函数根据标题返回序号
      • 公式：=VLOOKUP($H5,$H$8:$N$12,MATCH(I4,$H$8:$N$8,0),0)
    • 方法三 XLOOKUP()函数法（推荐）
      • XLOOKUP()函数，返回多列
        • 参数1：找谁
        • 参数2：条件区域（区别于VLOOKUP()函数，不包含返回区域）
        • 参数3：返回区域，选多列
      • 公式：=XLOOKUP(H5,H9:H12,I9:N12)
      • 说明：WPS不支持数组，用Ctrl+Shift+Enter
• 高中-多条件查询（答疑）
  • 1. What
    • 从表中，查“员工编号”，在“年龄”、“婚配”、“姓名”列中的值
  • 2. How
    • 找公式的规律
      • 第1步 查一项的公式
      • 第2步 复制公式，确定锁定单元格
      • 第3步 找规律，参数3的数字没有规律
        • 解决：由MATCH()函数定位
        • 语法：MATCH(找谁,去哪找,怎么找)，返回位置
          • 公式：=MATCH(AB3,$T$3:$Y$3,0)
    • 公式：=VLOOKUP($O5,$H$4:$M$18,MATCH(P4,$H$4:$M$4,0))
  • 3. Why
    • VLOOKUP+MATCH组合（交叉方向条件）
      • 1||| VLOOKUP()函数的参数1、2，定位到行
      • 2||| MATCH()函数，定位到列，作为VLOOKUP()函数的参数3
    • XLOOKUP法
      • 第1步：查询值列（作为参数3）
        • 公式：=XLOOKUP(P4,$H$4:$M$4,$H$5:$M$18)
      • 第2步：定位行，参数3嵌入第1步公式
        • 公式：=XLOOKUP(O5,$H$5:$H$18,XLOOKUP(P4,$H$4:$M$4,$H$5:$M$18))
• 小学（不完全匹配查询）
  • 1. What
    • 根据品牌，查存货区域（少查多）
  • 2. How
    • 找公式的规律
      • 部分文字查询
      • 通配符
        • *代表任意数量任意字符
        • ?代表单个任意字符
      • 包含丹姿，公式：="*"&K5&"*"
    • 公式：=VLOOKUP("*"&K5&"*",$H$4:$I$18,2,0)
  • 3. Why
    • 少查多：通配符
      • XLOOKUP的参数5，通配符查询设为2
      • 公式：=XLOOKUP("*"&K5&"*",$H$5:$H$18,$I$5:$I$18,,2)
    • 多查少：LOOKUP()+FIND()函数
• 中学（匹配查询）
  • What
    • 根据成绩，查询等级
  • How
    • 检验公式
      • 输入不同的成绩，观察返回值
    • 公式：=VLOOKUP(I5,$O$5:$Q$8,3,1)
  • Why
    • 区间查询概念
      • 步骤和要点
    • 区间查询函数LOOKUP
      • LOOKUP(找谁,去哪找,把谁领回来)
      • 公式：=LOOKUP(I5,$O$5:$O$8,$Q$5:$Q$8)
  • 练习：查询年休假
    • 方法1：=VLOOKUP(K24,$O$24:$Q$27,3,1)
    • 方法2：=LOOKUP(K24,$O$24:$O$27,$Q$24:$Q$27)
    • 方法3：=XLOOKUP(K24,$O$24:$O$27,$Q$24:$Q$27,,-1)
      • XLOOKUP的参数5，区间查询设为-1
• 高中（匹配查询）
  • What
    • 根据销量，查询等级
  • How
    • 修正IF()函数公式
      • 文本添加双引号=IF(I5>=200000,"A+",IF(I5>=150000,"A",IF(I5>=120000,"B",IF(I5>=80000,"C","D"))))
    • 查询函数
      • 建立查询表
      • 公式：=VLOOKUP(I5,$U$5:$V$9,2,1)
  • WHo
    • 区间查询，判断下限（包含下限）
    • 提升：如果没有等号，用查询函数怎样实现
      • 公式：=IF(I5>200000,"A+",IF(I5>150000,"A",IF(I5>120000,"B",IF(I5>80000,"C","D"))))
      • 修改查询表（解决不包含下限的问题）
• 选修1 多条件查询（答疑）
  • What
    • 根据班级、姓名，查成绩
  • How
    • 特征：并列多条件查询
    • 问题：定位困难
    • 解决：万能公式
      • LOOKUP(1,0/((条件1)*(条件2)),值列)
    • 公式：=LOOKUP(1,0/((M4=$H$4:$H$15)*(N4=$I$4:$I$15)),$J$4:$J$15)
  • Why
    • 方法1：由数组公式定位法
      • 输入公式，按Ctrl+Shift+Enter组合键
    • 方法2：PQ法
      • 第1步 导入两张表格
      • 第2步 合并查询
        • 班级对应班级，姓名对应姓名
      • 第3步 展开表
        • 只选择成绩列
      • 第4步 上载结果表格（数据更新，点刷新就好）
      • 说明：精确查询，采用PQ的合并查询更高效
        • 多条件
        • 重复项
• 选修2 区间查询（答疑）
  • What
    • 根据产品名称，查询时效
  • How
    • 第1步 创建查询表
      • 罗列所有条件
    • 第2步 查询
      • 公式：=VLOOKUP("*"&J5&"*",$O$18:$P$20,2,0)
    • 第3步 处理找不到
      • 公式：=IFNA(VLOOKUP("*"&J5&"*",$O$18:$P$20,2,0),$P$20)
  • Why
    • 一定要在公式完全正确的情况下，再处理错误
• 第二部分 小结
• 查询基础概念
  • 概念图
• 查询函数族谱
  • 常用函数
• 查询进阶
  • 1. 复杂条件查询
    • 如何定位？
  • 2. 查询多个相同项
    • 实质是筛选问题
• XLOOKUP函数用法
  • 一个函数搞定所有查询问题
    • XLOOKUP() 函数
      • 参数1：找谁
      • 参数2：在哪找，只有查找条件区域
      • 参数3：返回值区域，可为多行，多列
      • 参数4：找不到时，返回的值(处理#N/A)
      • 参数5：查询方式
        • 0：精确查询（默认）
        • 区间查询
          • 1：按上限查询
          • -1：按下限查询
        • 2 : 通配符查询
      • 参数6：查询顺序
        • 1：从上往下查找（默认）
        • -1：从下往上查找
    • 注意：软件版本要Office 365 的 Excel 和最新版本的 WPS
      • XLOOKUP()函数语法
        • 0 表示匹配模式为精确匹配； 1 表示从上往下查找； -1 表示从下往上查找。
• 讲解1：基础用法【精确查找】
  • 查找列与返回列独立
• 讲解2：条件列不是最左列【逆向查找】
  • 由条件列与返回列独立决定
• 讲解3：多条件查找
  • 并列关系的多条件（简单）
• 讲解4：返回多列
  • 支持数组，返回多个值
    • WPS要用数组公式【Ctrl+Shift+Enter】回车
    • （溢出错误，缺少足够单元格）
• 讲解5：匹配查询
  • 使用通配符，单独的查询方式
    • 参数为2
• 讲解6：区间按下限查询（通常用下限查询）
  • 按下限查询，参数为-1
• 讲解7：区间按上限查询
  • 按上限查询，参数为1
• 讲解8：找不到处理
  • 找不到直接可处理
• 讲解9：从上往下查询
  • 并列关系的多条件（简单）
• 讲解10：从下往上查询
  • 并列关系的多条件（简单）
• 第三部分 答疑课 内容梳理
• 查询语法
  • 根据条件查找值（查表）
  • VLOOKUP(找谁,在哪找,返回值序号,定位方式)
• 查询的实质（两步）
  • 1. 先定位
  • 2. 后取值
• 查询的规则（各参数的要求）
  • 1. 通用规则
    • 1||| 查1个值（参数1）
    • 2||| 查找区域（参数2）
      • 包含返回值列
      • 最左列是判断条件列
        • 问题：最左列不是判断条件列
        • 解决：
          • 构造成条件在最左列（难点）
          • INDEX()+MATCH()
    • 3||| 返回1个值（参数3）
      • 问题：多个值
      • 解决：筛选
    • 4||| 关键：1个定位条件
      • 问题：多个定位条件
      • 解决：
        • 同方向：将多个条件合并成1个条件（难点）
        • 交叉方向：V+M（MATCH返回列位置）
  • 2. 区间规则
    • 下限升序排列
• 查询函数族谱（各司其职）
  • 1. 目标
    • 了解各种函数的基本用法
    • 特别要明确关键的应用场景
  • 2. 函数组
    • 1||| VLOOKUP()垂直方向，HLOOKUP()水平方向
    • 2||| LOOKUP()，只是区间查询
      • 解决：复杂条件查询
      • 精典公式：LOOKUP(1,0)
    • 3||| INDEX()+MATCH()，将定位与取值分为两个函数
      • VLOOKUP()=INDEX()+MATCH()
      • 解决：最左列不是条件列
      • 解决：交叉方向多条件
        • VLOOKUP()+MATCH()
    • 4||| XLOOKUP()，365新版/WPS版本最新函数，功能全面
• 使用技巧
  • 1. 错误处理
    • 参数1错误
    • 参数2不是标准区域
    • 参数3错误
    • 找不到处理
  • 2. 构造查询表
    • 一维表
    • 二维交叉表
  • 3. 提升
    • 搭建查询问题框架
    • PQ中查询的实现
• 【查询篇】作业练习表
  • 附件链接