【「EXCEL」一个公式实现表格内容根据下拉菜单变化，制表so easy】

一个公式做出媲美ERP下拉拖拽功能的信息表

职场老手总说用上ERP就像用了机械键盘，回不来了，因为ERP通过简单的拖拽就能快速定制信息表。毕竟ERP是块老奶酪，需要点学习成本。小编表示不服，今天咱们就试试用一个公式证明excel中的下拉拖拽也能想用就用。先看效果：

自动检测是否填入信息，启用标题栏样式
标题栏信息可根据基础信息表下拉选择
填入姓名关键字，自动生成对应序号及人员信息关键字，并填充表格样式
行信息可以任意排序，拖动后对应行序号按照排序后位置重新更新，人员信息跟随关键字变化
列信息可以自由删减，增加，移动，编辑，编辑后的列信息不会出现错误
关键字删除后，其他信息和表格样式，列标签删除后，自动删除颜色填充样式，重新填入信息后，以上功能依然有效

功能虽多，操作却很简单

上述功能虽然说得天花乱坠，其实的重点在于信息的自动生成，至于表格自动填充边框和样式，简单的一个条件格式就完成了。信息自动生成仅仅依赖一个index复合match函数就能实现，而且这个公式仅需要在第一个单元格内编写一次就完成了，剩下的就是放纵的双击或者拖拽了

index+match 函数

要理解这个公式的终极奥义，必须先知道这个公式讲的是啥，先来看看它的构成

index，插入函数，将某个范围内某行某列的数据插入到当前单元格内

书写规范:

index（被选插入数据所属于范围，数据所在范围内的行，数据所在范围内的列）

动画演示：

match，匹配函数，将被选关键字在关键字所属范围内的序值填写到当前位置

书写规范：

match（关键字，关键字所在范围，匹配度）

动画演示：

行匹配（计算肖赛在姓名列中的第几行）

列匹配（计算毕业院校在标题标签中的第几列）

这两个函数看起来都很简单，但是如果将这两个函复合到一起，将产生能够完全取代并超越vlookuop的强大函数，能同时在行和列间以多条件匹配的形式精确找到你要的数据信息（下图演示了利用index+match 函数根据姓名肖赛找到他的毕业院校）

书写规范

index(被选插入数据所属于范围,match(匹配行),match(匹配列）

动画演示：

函数输入完成。剩下的就是双击或者拖拽了，左右拖，上下拖，就能完成所有信息的查找。但在你放肆拖拽之前，必须要是正确的设置地址锁定，才能让生成的数据准确无误

关于地址锁定

地址指的是代表单元格位置或者范围的字符，如B1，D5:D8 这样的，A B C….Z 表示列地址，1 2 3 4….99表示行地址，在没有设置地址锁定（$表示锁定）的情况下在我们拖动句柄的时候，地址符会根据拖动的方向变化，

横向拖动变化列地址（A1→Z1），纵向拖动的时候变化行地址（A1→A100）：

在设置锁定符$后（快捷键F4），会让锁定的行列地址不随句柄的拖动而变化，$符号在谁前面表示锁定谁

$A$1,表示行列均锁定，拖动行列地址均不变化
A$1，表示列行锁定，仅在横向拖动时，列地址会按照A$1→Z$1变化，纵向拖动时，A$1不会发生变化
$A1，表示列锁定，仅在纵向拖动时，行地址会按照$A1→$A100变化，横向拖动时，$A1不会发生变化

说了那么多，关于行列地址锁定不知你看懂没，要是没看懂我也不知道咋描述了，可能度娘那有更好的答案。

接着我们看我们的公式：

index(被选插入数据所属于范围,match(匹配行),match(匹配列）

首先，在本次演示内容中，所有的范围一定不能变化，所有数据范围范围均为行列锁定；（如 $C$2:$P$24，表示C2到P24这个查找范围行列锁定，不随句柄拖动而变化）

对于行匹配，关键字姓名一直存在于B列，不能变化；向下拖动，需要纵向向下检索关键字，所以锁定列，不锁定行；

对于列匹配，标题标签一直存在于第一行，不能变化，而需要根据所选关键字生成关键字所在范围内行的序数，所以锁定行，不锁定列；完整的公式是这样的：

=INDEX(Sheet2!$C$2:$P$24,MATCH(Sheet1!$B6,Sheet2!$B$2:$B$24,0),MATCH(Sheet1!E$1,Sheet2!$C$1:$P$1,0))

演示如下：

完成上面的地址锁定设置后，放肆的自由拖动吧。最终效果请上翻第1-2图

关于无数据区显示 #N/A

这个可以使用IFNA函数解决，用IFNA函数包围上面的公式即可，当上述公式结果值为空时，公式所在的单元格不输入任何数据，看起来就像空白单元格一毛一样：=IFNA( 上述公式，””)

=IFNA(INDEX(Sheet2!$C$2:$P$24,MATCH(Sheet1!$B30,Sheet2!$B$2:$B$25,0),MATCH(Sheet1!E$1,Sheet2!$A$1:$P$1,0)),””)

关于标题栏的下拉菜单

下拉菜单依然是使用数据验证的形式完成的,在我前面的文章中已经用到过多次，再演示一遍：

关于填充边框及底色的表格样式

填充表格样式是利用条件各式完成的，excel2013版本以上的数据验证使用说明写的十分清楚易懂，一目了然，设置一个单元格的条件格式后，直接拖拽公式句柄，即可应用到其他单元格，配合公式和数据验证使用，十分简单粗暴。演示如下：

关于功能4中的行序号自动更新

其实这个很简单，利用row()函数即可实现，在序号列输入 =row()-1 后向下拖动，即为在当前单元格填写单元格所在行号减去1的数值，至于减去多少，就看你标题占了多少行就行，如果想要实现在没有行数据时不显示序号，利用if函数即可，公式书写如下(通过B列判断行信息是否为空，若空，则不填写任何数值，若不空，填写序号):