原标题:Excel全自动数据更新、录入这样操作轻松搞定
为了各位能更高效、便捷的使用excel工作
吴老师给大家分享了两期“联动下拉列表”专题
到了这一期,吴老师继续和大家┅起分享
更有效率 更便捷的excel进阶技能
如何使用下拉列表实现其它列的数据动态更新
首先我们还是来看看最终功能效果。
数据动态更新的主要功能有以下几点:
1. 通过下拉列表选择姓名关键字后自动生成对应的人员信息。
2.姓名关键字删除后其它信息自动删除。重新选择功能依然有效。
3.列信息也可以通过下拉列表进行选择也可以删减、增加、移动、编辑。
下面我们来具体讲解如何实现
第一步:我们根據上一期《将复杂事情统统简单化!Excel“多级联动下拉列表”应用》讲到的如何设置下拉列表的方法,将“姓名”列内容和其它标题列设置為下拉列表
看不懂表达式?没关系我们只要学会按以下6步修改对应的名称就可以实现功能,我们来看下图
①:是指原始数据的工作表名,共有3处
②:A1:G6是原始数据的范围
③:B1:B6是姓名列的数据范围
④:A1:G1是标题列的范围
⑤:B1是指输入表达式的单元格最上面的标题列的单元格本例是在B2中输入表达式的。
⑥:A2是指下拉列表的位置
只要修改6个地方,我们即可实现动态更新的功能!
下面我们来具体解释一下这個表达式。
首先我们可以将这个长长的表达式简化一下,变成这个样子:
从这个简化的表达式样式可以看出表达式由四个函数组成IFNA(值,"")、INDEX(数据范围,行序号列序号)、2个MATCH(查找值, 数据范围)组成。
MATCH(查找值, 数据范围)这个函数的作用是在数据范围里找出与“查找值”匹配的行序号
例如表达式中这部分MATCH(A2,学生档案!B1:B6,0),它的作用就是在学生档案工作表中B1-B6的数据范围里找到与A2的内容(下拉列表选择的内容)相同的行序號下面这个例子得到的行序号为3。
MATCH(B1,学生档案!A1:G1,0)作用就是在学生档案工作表中A1-G1的标题范围里找到与输入公式的B2单元格对应的标题相同的列序號下面这个例子得到的列序号为1(也就是A列)。
当我们用B2单元格公式填充其它单元格时会出现错误。
原因在于使用填充功能时EXCEL自动将数據范围更改了。但我们查找的数据范围基本不变的我们可以用$固定下来。
同时横向填充过程中A2发生变化,第1个MATCH不再是根据A2下拉列表查找行序号了所以我们同样用$固定A,让同一行的数据根据同一行的A列的下拉列表来决定。
上面的视频中在纵向填充过程中B3-B4发生错误,因为苐2个MATCH不再是根据最上面单元格B1查找列序号了,所以我们同样用$固定1,让同一列的数据根据同一列的第一个来判断列序号
当我们将空白的地方(下拉列表没有选择数据)进行填充时,发生错误
我们需要将错误的值用IFNA函数进行空处理,如下表达式:
这期主要结合下拉列表、INDEX、MATCH、IFNA函数实现动态更新数据下期开始讲解EXCEL中对数据的计算分析。
咦?吴老师还有话要说!
吴老师将定期更新Excel相关内容
有问题直接留言,吴老師将第一时间回复你!
想获得本期Excel操作答案和素材
请在本公众号回复“动态更新”,即可获得!!!
1、手机丢失或被盗如何保证微信支付安全!
2、M·A情绪被安排|13.等我困了就睡
3、那么好看的LOGO墙怎么做出来的看强哥PPT学的啊!
4、关于免费举办“高级图形图像处理(Photoshop)”“高级办公軟件应用WINDOWS平台(MSOffice)”培训班的通知
5、如何做一个无需键盘录入数据的“多级联动下拉列表”工作簿