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

大家好 我是吴老师

为了各位能更高效、便捷的使用excel工作

吴老师给大家分享了两期“联动下拉列表”专题

到了这一期，吴老师继续和大家一起分享

更有效率 更便捷的excel进阶技能

在现有的数据基础上，

如何使用下拉列表实现其它列的数据动态更新。

版本提示：EXCEL2016

首先我们还是来看看最终功能效果。

数据动态更新的主要功能有以下几点：

1. 通过下拉列表选择姓名关键字后，自动生成对应的人员信息。

2.姓名关键字删除后，其它信息自动删除。重新选择，功能依然有效。

3.列信息也可以通过下拉列表进行选择，也可以删减、增加、移动、编辑。

下面，我们来具体讲解如何实现

1

第一步：我们根据上一期《将复杂事情统统简单化！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中对数据的计算分析。

咦?吴老师还有话要说！

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!