按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

今天教一个比较有趣的案例,在数据表的某一列中按关键字查找,然后返回相匹配的完整数据表区域 。
案例:
下图是 1 是各邻居的团购表 。
请设置一个查找区域,按照输入的菜的关键字,模糊查找出包含关键字的所有菜及其购买人 。
效果如下图 2、3 所示 。

按关键字查找并提取出Excel的部分表格 表格提取部分内容公式


按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

【按关键字查找并提取出Excel的部分表格 表格提取部分内容公式】
按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

解决方案:
1. 选中“差异”列的第一个空单元格 –> 选择菜单栏的“布局”–>“公式”

按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

2. 在 F2 单元格中输入以下公式:
=FILTER(A2:B21,ISNUMBER(FIND(D2,B2:B21)))
公式释义:
  • FIND(D2,B2:B21):在区域 B2:B21 的每个单元格中依次查找 D2 单元格的文本串,并返回 D2 的起始位置的值;
  • ISNUMBER(…):判断序列中的值是否为数值,返回 true 或 false;能与 D2 单元格模糊匹配上的,都为 true;
  • FILTER(A2:B21,…):
    • filter 函数的作用是基于定义的条件筛选一系列数据;
    • 语法为 FILTER(要筛选的数组或区域,高度或宽度与数组相同的布尔值数组,[筛选器不返回任何内容时返回的值]);
    • 这里的公式含义是只要 isnumber 的结果为 true,则返回区域 A2:B21 中同一行的值

按关键字查找并提取出Excel的部分表格 表格提取部分内容公式


按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

如果在 D2 单元格中重新输入文字,F、G 列的查找结果也随之变动了 。

按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

现在尚有一不完美之处,查找结果的数据表没有边框 。最好能够随着查找结果的多少自动出现边框 。
3. 选中 F2:G21 区域 –> 选择菜单栏的“开始”–>“条件格式”–>“新建规则”

按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

4. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”–> 输入以下公式 –> 点击“格式”按钮:
=F2<>””
* 请注意:单元格必须相对引用 。

按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

5. 在弹出的对话框中选择“边框”选项卡 –> 将外边框设置为黑色实线 –> 点击“确定”

按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

6. 点击“确定”

按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

现在,查找结果表有值的区域会自动产生边框 。

按关键字查找并提取出Excel的部分表格 表格提取部分内容公式


按关键字查找并提取出Excel的部分表格 表格提取部分内容公式

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握 。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点 。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手 。

推荐阅读