excel综合应用（一）：信息查询

 引入：

        刚开始来公司的时候，听说是做项目管理，感觉非常的高大上，我猜咱们应该有专门的项目管理系统吧？但自从老大每到月底总让我将项目的工时数据和报销数据更新到一张异常复杂的excel表格之后，我就知道我是在痴人说梦。。。

        这个项目成本汇总表具体有多复杂呢？我们可以浅看一下其中一个子表的表头：

         这是仅仅一组项目信息的示意图，前A、B列是项目的索引，包括项目编号、项目名称，之后E列：每个项目，都对应把项目成本分成了三个类型：成本预算、成本支出，以及成本偏差（=成本预算-成本支出）

        E列之后的数据，为项目成本的明细，分为工时数据和报销数据：

        首先是工时数据，我们需要从第一行开始看，分别是各个项目阶段，每个项目阶段有不同的人员角色，不同角色的工资不同、所花费的工时也不同，所以不同角色产生的费用，需要分别进行统计：

        像下图展示的，便是项目售前阶段商务人员、售前组售前人员以及项目内其他小组的售前工时数据， 每个角色都有三个固定的统计数据：工时（h）、工时（人月）以及工时费用（人月*不同角色的人力基数）。

        我们要知道，一个项目的参与角色至少十几种，何况还分不同阶段。这时候肯定很混乱，如果在存储了n多个项目的这个子表里面，需要你快速查找到某个项目某个角色对应的工时费用，ctrl+F找项目编号？还不够，我想要的角色呢？这是如果那个角色藏在表列的中间位置或者末尾，那你就很难受了，鼠标不停的拉动同时目光坚定的扫描刚刚所看到的一切来定位。  

    

        有人会说，你不会用筛选器啊？这么复杂的表头，行和列都存在合并单元格，办不到。

         让你查几次倒还好，要是你在更新数据的时候得找好几百次，你不疯？

设计一个简单的查询界面，实现在原表之外找到我们想要的数据？

        熟悉excel的人都应该会想到了数据透视表吧？非常强大直观的数据汇总与展示，省去了许多公式冗余，以及一些不当的操作对原表格的数据产生的变动。

        但别忘了？数据透视表可不支持这样复杂表头的数据源，BBQ了？这可怎么办？

        没事，它不支持，我们就再创建一个支持数据透视表的“项目数据提取”表格，把原来那个复杂表格的数据，根据项目编号去查找匹配，加载出这个项目，所有的工时数据，问题自然解决了。

1、最终实现效果展示

        这个查询工作簿是完全独立与源数据之外的，利用数据透视表的切片器，还可以对费用类型、项目阶段等进行筛选，从不同角度汇总项目费用，完全不需要，操作到原表格数据。此外，还用到excel宏设置了一些交互的控件， 实现更加人性化的查询操作。

        接下来我们一起来看看如何制作这样一个简易的查询工作簿：

2、新建工作目录

        新建工作目录，这里要放源数据，在新建一个“信息查询.xlsx”的查询工作簿。

3、创建“项目信息提取”工作表

        打开新建的“信息查询”工作簿，命名工作表1为“信息查询-数据上载”。

        这个表中存放的是，根据我们在查询界面输入的项目编号，在源数据中查找匹配到不同类型的工时数据。先前所提到的仅仅是一组项目的数据，我们需要合计的，只是每个角色对应的最基本的工时（h）、工时（人月）、费用（工时费用（人月*不同角色的人力基数）。

 3.1 表头设计（数据划分基础）

         也就是说，项目提取出来的数据，需要每个角色都单独录入两行数据（成本预算、成本支出），先以“商务人员”这个角色为例，定下表头。

    首先，$A$2单元格是所有数据匹配的源头，当查询界面做好之后，直接“=用户输入的项目编号”，如下图所示：

         查询界面还没开始做，所以我们先将某个项目编号的文本填充至$A$2单元格。

        如下图所示。

3.2 表格body设计  

        接下来我们就给所有角色都录入一行数据，最后三列先放空不操作，效果如下：

        接下来就是如何根据项目编号去查找匹配的问题了，同样以“商务人员”为例，对于第一条成本预算的数据：

我们用vlookup函数对项目编号查找，是可以匹配到的，“成本预算”的匹配公式写成：

        =VLOOKUP($A$2,'[00 各项目成本汇总.xlsx]成本数据'!$A:$DH,7,FALSE)

         其中$A$2固定为用户输入的项目编号，查找范围为'[00 各项目成本汇总.xlsx]成本数据'!$A:$DH，其中商务人员对应的工时（人月）在第7列，最后！！精确匹配（False）。记得测试一下是否匹配成功。

        以此类推，可以把所有角色的成本预算数据公式都写好了。

        而第二行的“成本支出”数据，vlookup函数不再适用。以下图为例，选中项目编号单元格，显示的行号是228行，显然vlookup函数只能返回和项目编号同一行的数据，而成本支出数据所在的行为229行，也就是项目编号的下一行。

        这时我们可以用match函数返回项目编号在表格对应的行号，index函数对某一列进行（行号+1）的索引查找，“成本支出”的匹配公式写成：

       = INDEX('[00 各项目成本汇总.xlsx]汇总表'!$F:$F,MATCH($A$2,'[00 各项目成本汇总.xlsx]汇总表'!$A:$A,0)+1)

        注意match（）函数中，最后要精确匹配（0）！！       

3.3 “项目数据提取”表格效果展示：

           其中项目名称同样采用vlookup函数匹配查找。 

 4、信息查询界面设计

        在“数据提取”工作表同个工作簿中，新建一个新的工作表，将其重命名为“信息查询”，这将是用户查询项目信息的入口。

 

 4.1 项目编号输入框的设计：

         确定了填写项目编号的单元格后，我们将数据提取表格的$A$2单元格，=链接过来：

         此时在框内输入任意的项目编号，数据提取的工作表就会自动显示该项目的成本信息了。

 4.2 插入数据透视表

         文章开头讲到，数据透视表可以直观的展示、汇总数据，利用切片器做个性化类型筛选。

        首先，我们选择“项目数据提取”的表格区域，在excel上方-“插入”，点击插入“数据透视表”：

        定好数据透视表存放的位置后，确定插入：

         

         接下来是对数据透视表的字段进行设置，首列是行标签（索引）：依次是（项目编号、项目名称、）角色，后三列是工时数据的汇总，汇总依据设置为“求和”。

         数据透视表的最后一步是插入切片器，通过切片器筛选不同类型的数据，从不同角度对成本进行汇总、透视。

        点击表格区域，excel上方找到“数据透视表分析”，点击“插入切片器”。

        切片器相当于每一列的筛选控件，选定一列数据，就会生成一个切片器。这里我需要的是：项目阶段、成本类型以及费用类型。切片器筛选后的效果如下图，这里不再赘述：

 

4.3 用户交互控件设计

        这时我们在输入其他项目编号进行查询，数据透视表并不会自动更新数据，传统的做法是右键数据透视表，“刷新”。但这似乎不是很人性化，如果我们设计一个“查询”的按钮，在每次输入项目编号后，点击这个按钮，就会数据就会进行刷新，这是不是方便了许多？

        这里，我们需要用到excel的宏：

        excel上方找到“开发工具”，点击“插入”，选择按钮控件，将控件插入到输入框的旁边。

         再在开发工具这里，找到“宏”，点击“编辑”，进入代码编写界面：

        我们需要将刷新函数插入到这个工作簿的代码之中，我这里命名为Refresh，完整代码：

Sub Refresh()
    ActiveWorkbook.RefreshAll
End Sub

         代码保存后，右键刚刚插入的按钮控件，“指定宏”，指定为刚刚的Refresh（）函数，确定。

         大功告成！为了美观，最后优化下界面。

          其他个性化的需要，自行发挥。

4.4 保存为xlsm格式的工作簿

       设计完成的工作簿，需要另存为xlsm格式，

         再次打开这个工作簿时，记得启用所有宏，便能正常运行。