Excel全自动数据更新、录入，这样操作轻松搞定

第二步：我们将由INDEX、MATCH这两个函数实现的表达式=IFNA(INDEX(学生档案!$A$1:$G$6,MATCH($A2,学生档案!$B$1:$B$6,0),MATCH(B$1,学生档案!$A$1:$G$1,0)),"")添加到B2单元格里，然后通过拖拽的方式将需要输入内容的区域填充起来就可以实现动态更新的功能。

看不懂表达式？没关系，我们只要学会按以下6步修改对应的名称就可以实现功能，我们来看下图。

①：是指原始数据的工作表名，共有3处

②：A1:G6是原始数据的范围

③：B1:B6是姓名列的数据范围

④：A1:G1是标题列的范围

⑤：B1是指输入表达式的单元格最上面的标题列的单元格。本例是在B2中输入表达式的。

⑥：A2是指下拉列表的位置。

只要修改6个地方，我们即可实现动态更新的功能！

2

下面，我们来具体解释一下这个表达式。

首先，我们可以将这个长长的表达式简化一下，变成这个样子：

=IFNA(INDEX(数据范围,MATCH(查找值,数据范围),MATCH(查找值,数据范围)),"")

从这个简化的表达式样式可以看出，表达式由四个函数组成IFNA(值,"")、INDEX（数据范围，行序号，列序号）、2个MATCH(查找值, 数据范围)组成。

MATCH(查找值, 数据范围)这个函数的作用是在数据范围里找出与“查找值”匹配的行序号。

例如表达式中这部分MATCH(A2,学生档案!B1:B6,0)，它的作用就是在学生档案工作表中B1-B6的数据范围里找到与A2的内容（下拉列表选择的内容）相同的行序号。下面这个例子得到的行序号为3。

MATCH(B1,学生档案!A1:G1,0)作用就是在学生档案工作表中A1-G1的标题范围里找到与输入公式的B2单元格对应的标题相同的列序号。下面这个例子得到的列序号为1(也就是A列)。

INDEX(学生档案!A1:G6,MATCH(A2,学生档案!B1:B6,0),MATCH(B1,学生档案!A1:G1,0))作用就是在数据范围里根据两个MATCH函数得到的行序号、列序号找出相应的数据。

当我们用B2单元格公式填充其它单元格时，会出现错误。

原因在于使用填充功能时，EXCEL自动将数据范围更改了。但我们查找的数据范围基本不变的，我们可以用$固定下来。

=INDEX(学生档案!$A$1:$G$6,MATCH(A2,学生档案!$B$1:$B$6,0),MATCH(B1,学生档案!$A$1:$G$1,0)

同时，横向填充过程中A2发生变化，第1个MATCH不再是根据A2下拉列表查找行序号了，所以我们同样用$固定A,让同一行的数据根据同一行的A列的下拉列表来决定。

=INDEX(学生档案! $A$1: $G$6,MATCH($A2,学生档案! $B$1: $B$6,0),MATCH(B1,学生档案! $A$1: $G$1,0)

上面的视频中，在纵向填充过程中B3-B4发生错误，因为第2个MATCH不再是根据最上面单元格B1查找列序号了,所以我们同样用$固定1,让同一列的数据根据同一列的第一个来判断列序号。

=INDEX(学生档案! $A$1: $G$6,MATCH($A2,学生档案! $B$1: $B$6,0),MATCH(B$1,学生档案! $A$1: $G$1,0)

当我们将空白的地方（下拉列表没有选择数据）进行填充时，发生错误。

我们需要将错误的值用IFNA函数进行空处理，如下表达式：

=IFNA(INDEX(学生档案!$A$1:$G$6,MATCH($A2,学生档案!$B$1:$B$6,0),MATCH(B$1,学生档案!$A$1:$G$1,0)),"")

这期主要结合下拉列表、INDEX、MATCH、IFNA函数实现动态更新数据，下期开始讲解EXCEL中对数据的计算分析。

咦?吴老师还有话要说！

本期分享就到这里

吴老师将定期更新Excel相关内容

有问题？直接留言，吴老师将第一时间回复你！

有问题都变没问题！

想获得本期Excel操作答案和素材？

请在本公众号回复“动态更新”，即可获得！！！

原文|吴老师

图|吴老师

编辑|莫莫、K'TA

1、手机丢失或被盗如何保证微信支付安全！

2、M·A情绪被安排|13.等我困了就睡

3、那么好看的LOGO墙怎么做出来的？看强哥PPT学的啊！

4、关于免费举办“高级图形图像处理(Photoshop)”“高级办公软件应用WINDOWS平台(MSOffice)”培训班的通知

5、如何做一个无需键盘录入数据的“多级联动下拉列表”工作簿返回搜狐，查看更多