Excel 个人练习-1 VLOOKUP
- VLOOKUP简介
- COUNTIF简介
- 任务描述
- 实验环境
- 原始文件
- 任务目标
- 任务拆分
- 分步操作
- 每科加√
- 名单去重
- 学科查阅 && 统计科目数
- 数据检查
- 绘图分析( 未完待续-后续做)
VLOOKUP简介
微软Excel VLOOKUP简介
微软VLOOKUP视频
需要在表格或区域中按行查找内容时,请使用 VLOOKUP。
- 选择一个单元格。
- 键入 =VLOOKUP(,然后选择要查找的值。
- 键入逗号 (,) 并选择要在其中查找值的区域或表。
- 键入逗号 (,) 及要查找的值所在行的行号。
- 键入 ,FALSE) 查找精确匹配项。
- 按 Enter。
视频示例中使用的公式为:
=VLOOKUP(A7, A2:B5, 2, FALSE)。
在这一最简单的形式中,VLOOKUP 函数表示:
VLOOKUP(查阅值、包含查阅值的区域、
区域中包含返回值的列号以及(可选)为近似匹配指定 TRUE 或者为精确匹配指定 FALSE)
COUNTIF简介
微软COUNTIF用法简介
微软COUNTIF介绍视频
任务描述
本次学习VLOOKUP函数,使用了一个高中报课人员名单,这张表有每一科的孩子报名名单,不同科目的名单不同。现在的任务是:
- 统计全部报名的人员的名单。
- 统计每一个孩子分别报名了哪几个科目。
- 统计每一个孩子已报的科目总数。
- 绘图,查看每科报名比例。(选做尝试)
- 绘图,分析报名科目的相关性。(选做尝试)
实验环境
电脑:WIndows7以上电脑。
软件:WPS2019中的Excel进行操作。
原始文件
大家可以选择先去下载这个原始数据文件
原始数据文件下载地址
原始数据的内容如下:
任务目标
我们所需要达成的目标任务如下图所示:
任务拆分
我们可以把上述的任务拆分为下面的几个子任务:
- 在原表的内容中都加上一行对应的学科的 √,用于后续操作。
- 新建学科名单表。
- 整理所有人的名单,并去除重复名单。
- 对单科进行VLOOKUP查阅,获取。
- 依葫芦画瓢,对每一个学科进行VLOOKUP操作。
- 对统计科目的计数进行COUNTIF,获取第一个孩子的科目数。
- 下拉,应用于所有孩子。
- 全选表格,复制,鼠标右键,粘贴为数值。
- Ctrl/Command + F 来进行替换,替换内容为
#N/A
。 - 得到最终的结果,进行手动检查匹配。
我们可以得到下面的思维导图:
分步操作
每科加√
在每一个科目中按照下面的操作进行。
对后面的每一个科目按照上述的操作进行,完成对应的列的添加。得到的最终的结果如下:
如果觉得一个个去点击插入的操作比较负责,这里介绍一个简单一点的操作:
- 按住Ctrl/Command点选所有的学科右边一列,如下图:
- 点击鼠标右键,选择插入列,并设置为自动:
- 检查对应的效果
操作起来可以跟着下面的gif图片来操作。
为了美化一下对应的最后一列,我们可以使用格式刷来完成下面的操作:
名单去重
本阶段的目标:
- 在新的表格里面建立学科姓名表。
- 把所有人的名字放进新表。
- 保证每个人名字的独有性即每个名字只能够出现一次。
我们的分解步骤大概如下:
4. 建立新表并改表名
5. 复制学科表头
6. 粘贴并添加新的头部内容
7. 添加学科统计内容
8. 去除所有重复的姓名
第一步
第二步
第三步
第四步
第五步
这里重点学习使用快捷键 Ctrl/Command + Shift + ↓ 进行快速的全选的操作。
调整一下表格格式:
- 点击左上角Ctrl/Command + A
- 按下Ctrl/Command + 1 或者 鼠标右键选择设置单元格格式
- 设置外边框和内边框
添加一个学科总数的统计,并设置冻结首行方便查阅和观看:
学科查阅 && 统计科目数
本阶段的目标是:
- 使用VLOOKUP函数在两个表之间找到每个孩子到底报了哪些学科。
- 每个学科进行VLOOKUP操作,获取每个学科的操作。
- 在每个学科完成匹配之后,用COUNTIF函数计算每个孩子购买的科目总数。
- 去掉对应的空数据内容就大功告成了。
具体的演示步骤如下:
语文
数学:
英语:
物理:
化学:
计算购买课程总数-利用COUNTIF:
复制为数值:
到此,整个表格的目标基本任务大功告成。
下面试试一个快速应用公式的方法:
我们可以在出现下拉复制公式的位置,双击,就可以直接批量对表格范围内的这一列使用同类型公式。
数据检查
找两个数据,用查找的方式进行一个数据检查,看看是不是科目成功的对应上了。
作业完成之后可以参考下面的文件:
高一名单统计-作业完成
绘图分析( 未完待续-后续做)
更多推荐
Excel 新手向个人练习-1 VLOOKUP,COUNTIF
发布评论