Excel如何设计一对多的列表查询统计系统

前面的技巧中讲到VLOOKUP和MATCH组合函数设计列表查询系统，但此前的设计思路只适合一对一的列表查询，能不能设计一个一对多的列表查询系统功能呢?比如选中或输入一个条件后，符合这个条件的所有数据行都显示出来，最好能带这些显示数据列的统计就好了，比如求和。

如图3-238所示，在表中只要查询地区和销售人员，就会自动显示出销售人员的所有数据行，并实现“订单金额”的求和统计显示。

图3-238

操作

步骤1：选中数据区域，依次单击“插入→表格”将其转换成表格，也可以按Ctrl+T 组合键转换成表格，如图3-239所示。

步骤2：依次单击“表格工具→设计“，将“表名称”改为“销售表”，如图3-240所示。

图3-239

图3-240

步骤3：依次单击“插入→切片器”，选择插入对应的字段按钮。本例插入了“国家/地区”和“销售人员”的按钮，如图3-241所示。当然单击图3-240中的“插入切片器”按钮也是可以的。

图3-241

步骤4：选中切片器后，可以像调整形状大小一样拖曳小圆点调整切片器高度，如图3-242所示。

步骤5：在单击切片器中的选项时，就可以控制表格数据，筛选显示符合切片器选项的记录。

那么如何实现动态的数据统计呢?比如统计查询条件对应的订单金额的和，以及数据行的个数。这类统计看着复杂，其实只要用一个函数即可搞定，那就是SUBTOTAL函数：=SUBTOTAL(109,销售表[订单金额])，注意参数109是统计筛选后的订单金额的和，这里参数109与9效果一样，但推荐用109，如图3-243所示。

图3-242

图3-243

=SUBTOTAL(103,销售表[订单金额])，注意参数103是统计筛选后的订单金额的非空单元格的个数，这里参数103与3效果一样，但推荐用103，如图3-244所示。

图3-244

这样，一个简单的一对多查询系统就搞定了，不用任何的VBA代码，完全利用Excel自带的功能，只要单击切片器上的菜单就能够实现该数据表更新后统计，依然支持查询，是一个非常棒的查询系统。

总结：列表(插入→表格)+切片器+SUBTOTAL 算是Excel的黄金三搭档，专门制作一对多的动态数据查询和统计问题，而且支持动态的数据更新，数据表新增数据，数据也会自动统计进去。强烈推荐职场人士掌握此动态数据统计模板的技巧。