一对多查询这种千古难题,巧用 Excel 数据透视表竟然就能实现
一对多查询的方法,我真的是写了很多很多了。具体可参见:
- Excel一对多查找的结果写入同一单元格,这个函数正好对症下药
- Excel – 用vlookup一对多查找,且结果汇总在同一个单元格中
- Excel – 列出班级名,如何一对多查找出全班人的姓名和成绩?
- Excel – 一对多查找,表格还转置,你用哪种方法?
- Excel – 如何将同班同学名字汇总到同一个单元格中?
今天再教一个用数据透视表实现的方法,而且数据透视表是所有方式中最简单的一种。
唯一的缺点是:用数据透视表的方式只能查询数字,不能查询文本。
案例:
一对多查找下图 1 中每个班级的所有考分,结果排列成一张二维表。效果如下图 2 或 3 所示。
解决方案:
先试一下,如果直接创建数据透视表,就会如下图所示,对所有数据进行计算,而无法实现本例需求。
那么应该怎么做?
1. 在 C 列增加辅助列 --> 选中 C2:C19 区域 --> 输入以下公式 --> 按 Ctrl+Enter 回车:
=COUNTIF($A$2:A2,A2)
这个公式的目的是统计每个班级在区域内第几次出现。
* 请注意:
- 辅助列的标题不得为空
- 不要用错单元格的绝对和相对引用
2. 选中数据表的任意单元格 --> 选择菜单栏的“插入”-->“数据透视表”
3. 为了方便教学,我将数据透视表创建在现有工作表中 --> 点击“确定”
4. 在右侧的“数据透视表字段”区域,分别将字段拖动到以下一一对应的区域:
- 行:班级
- 列:辅助
- 值:模拟考总分
5. 选中数据透视表的任意区域 --> 选择菜单栏的“设计”-->“总计”-->“对行和列禁用”
6. 选中“二班”所在的单元格 --> 输入“一班”--> 回车,就将班级列表按顺序排序好了。
7. 隐藏工作表的第一、第二行
8. 给数据透视表的单元格加上边框,再适当调整单元格宽度,就实现了一对多查询的需求。
9. 如果需要纵向显示分数,只需要在右侧的“数据透视表字段”区域,将“班级”和“辅助”字段的位置对调一下,再按上述方式适当修饰数据透视表即可。
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。
请先 后发表评论~