MATCH和INDEX函数
10. MATCH和INDEX函数
对VLOOKUP()函数只能根据左边查找右边的局限的完善
- match()用于找位置
- index()用于取
10.1 根据公司名称找用户ID
1. match()函数
还是之前VLOOKUP() 的那张表
查询表如下,要求找出第一个公司在原表中的索引
查询语句为=MATCH(B2,客户信息!B:B,0)
,我们找到国皓公司在原表中的id为7,接着需要用index()函数匹配
2. INDEX()函数
在1中我们找到国皓公司在原表中的id为7,即需要在原数据表中找id为7的公司id,查询语句为:=INDEX(客户信息!A:A,7)
,这样就找到了“国皓”对应的公司id为“BLONP”
3. MATCH+INDEX
如何将两种结合起来呢?我们在C2单元格输入查询语句:=INDEX(客户信息!A:A,MATCH(查询!B10,客户信息!B:B,0))
,然后选择下拉,将其他的公司名称对应的公司ID查出来,如下图所示:
我们看到C列查出来的公司id,都是灰色填充背景,而A,B列是灰白相间的,这个问题该如何解决呢?
右键下拉—>不带格式填充
10.2 用INDEX()函数做动态员工报告书
如下原始的表格为
要求按下列模板,每个员工做一张表格
如何操作呢?
- 先做滚动条
文件—>选项—>自定义功能区—>主选项卡(右侧)—>勾选“开发工具”,加载出开发工具,然后进行下述操作
开发工具—>插入表单控件—>滚动条
鼠标变成“+”,横拉为水平滚动条,竖拉为垂直滚动条
这里我们选择横拉,然后将滚动条拉到表格的右上角,如果不小心点了别处,滚动条不能拖拽,则右键选中滚动条,再进行拖拉 - 右键滚动条—>设置控件格式,单元格链接选择L1
这样L1中的数字就跟滚动条建立了联系,点击滚动条的左右箭头,L1中的数字也会跟着改变
现在我们希望L1中是什么数字,下面的表格对应的就是原数据表中哪个人的信息
做如下处理: - 在B4(姓名)中输入公式:
=INDEX(员工信息表!A:A,查询表!L1)
D4(工号)中输入公式:=INDEX(员工信息表!A:A,查询表!L1)
E4(入厂日期)中输入公式:=INDEX(员工信息表!C:C,查询表!L1)
C6(部门)中输入公式:=INDEX(员工信息表!D:D,查询表!L1)
B7(级别)中输入公式:=INDEX(员工信息表!F:F,查询表!L1)
C7(职务)中输入公式:=INDEX(员工信息表!E:E,查询表!L1)
C8(工资)中输入公式:=INDEX(员工信息表!G:G,查询表!L1)
这样相应的位置将会随着L1数字的变化而发生改变
- 需要修改的小细节,完成前3步操作后,我们发现L1内的数字是0也有信息显示,而我们期望的是L1内数字是2,抓取员工信息表中的第一位员工,只需修改滚动条的范围即可。右键滚动条—>设置控件格式
5. 把L1用白色字体填充
类似的这类问题都可采用设置控件格式处理,因为保留了原数据,统计数据和分析数据都更加方便