【面试题】利用下拉框选择机构名称、利率档、期限等条件，查询出相应的费用

对金融产品进行台账管理，基础数据表如黄色框所示（上图左边表格）。为了快速查找出不同产品的费用，需要达到上图右边表格里的效果：机构，利率档和期限可以从下拉列表中选择，选好以上三个条件后，相应的费用就会自动显示出来。

这个业务问题，使用多维度拆解分析方法，可以把这个业务问题分为两部分：第一部份创造下拉列表，第二部份根据选好的条件查询匹配出相应的费用。

1.制作下拉列表

第1步：A列中的机构名称有很多是重复的，把这一列的值复制到表格空白处，然后删除重复值后，发现这一列的机构名称只有A、B、C共3家。

在Excel表空白的地方，写上A机构、B机构、C机构，用于后面制作下拉列表里的值（下图）。这样做的目的是防止重复值出现在下拉列表里。

第2步：选中需要创作下拉列表的地方，也就是表中的I5单元格，然后在Excel中点【数据】——【数据验证】，打开数据验证对话框。

第3步：验证条件选择【序列】，来源选择【A机构，B机构，C机构】所在的单元格（第1步写A机构，B机构，C机构的单元格），最后按确定。

具体的操作如下图所示。

注意创造好下拉列表后，表中M2：M4区域中的内容A机构，B机构，C机构不能删除，否则下拉列表的内容会成空白，交给领导时为避免信息过剩可以把M列隐藏起来。

同样的道理，如果B列的利率档没有重复值在创作下拉框时可以直接作为数据验证对话框里的来源。

如果有重复值的话，和机构处理的方式一样，在表格空白处写上这一列删除重复值后的利率作为数据验证对话框里的来源。

期限的下拉框也是一样的操作，操作动态图如下：

如果后期的机构，利率档或者期限有增删改，做出相应的改动后，在数据验证对话框里的来源重新选择改动后的单元格区域范围即可。举个例子。

如下图所示，C机构改成了E机构，再添加F机构，那么需要在数据验证对话框，把来源处的单元格范围要做相应的修改，再按确定即可。

2.查询费用

机构、利率档、期限的下拉列表创造好后，接下来要根据这3个条件进行费用的查询匹配。

这时用到Excel里的两个查询函数：index，match。

INDEX函数能根据指定的行号和列号来返回一个值，用法如下：

例如在这个案例中，想要查找A机构，利率档是40%，24期的费用是976元，那么对应的公式是：

=INDEX(A2:E17,1,3)

上面公式表示在A2：E17这个区域内，第1行第3列的值是976元。但通常表格里的数据很多，有多行多列。如何让INDEX知道A机构，利率档是40%是在第1列，24期的费用是在第3列呢？

这时就要用到INDEX的王炸组合MATCH函数。

MATCH函数就是拿要查找的值，在指定的区域进行匹配。匹配上了，就会返回目标值所在的单元格位置。用法如下：

在本案例中，如想要确定A机构，利率档是40%是在第一行，那么写出来的公式是：

=MATCH(A2&B2,A2:A17&B2:B17,0)

这个公式表示，在A2:A17&B2:B17这一范围内，A机构，利率档是40%是在第一行。

由于是多条件查询，而INDEX和MATCH均不是数组公式，要同时按下【ctrl+shift+enter】才能显示正确答案。

这又涉及到另一个问题，什么是数组？

简单来说数组就是一组结构相同的数据按照一定的规律进行排列，组成数组的数据我们统称为元素。

什么情况下会用到数组呢？如果计算结果返回的是一个集合就要用到数组。在本案例中，机构和利率档是一个集合才能确定位于第几行，因此用到数组。

MATCH函数除了可以得到查找的内容在第几行，同时也可以查询出第几列，写下如下公式：

=MATCH(C1,A1:E1,0)

表示“24期费用”在A1:E1范围内，位于第3列。

了解了以上的内容后，在费用的单元格里写下公式：

【=INDEX(A:E,MATCH(I5&I6,A:A&B:B,0),MATCH(I7,A1:E1,0))】

第一个MATCH公式是要同时找到机构和利率档，属于多条件查找，涉及到数组，因此写完公式后要同时按下【ctrl+shift+enter】。

公式的最外面出现一双大括号如下图，注意大括号不能手动输入，需要同时按下【ctrl+shift+enter】就会出现，否则结果会出错。

该公式的解释如下：

最后结果动态演示图如下：

【总结】

1.用数据验证功能制作下拉列表。

2.查询函数（index、match）的嵌套应用。

3.多条件查找涉及到数组，写完公式后要同时按【ctrl+shift+enter】才能正确显示结果。

推荐：人人都需要的数据分析思维