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

Excel函数 2021-11-25 12:55:50

能不能设计一个一对多的列表查询系统功能呢？比如选中或输入一个条件后，符合这个条件的所有数据行都显示出来，最好能带这些显示数据列的统计就好了，比如求和什么的。

小伙伴们的这个要求是非常合理的，职场中确实也会碰到这样一堆多的情况，通常都用筛选的方法来进行操作，今天教大家做一个更加“高大上”的方法，利用“按钮”实现这类设计。注意不会用到VBA哟。相信大家按照下面的操作，一定会学会的。

具体操作如下：

首先，我们来设计一个场景，下表中只要查询城市和销售人员，就自动显示出销售人员的所有的数据行，并实现“销售金额”的求和统计显示。

第一步，对数据列表，表头插入几行，（下图1处）。第二步，选中表头字段下面的第一行，然后在“视图-冻结拆分窗格”。下图3处。

这样一个查询系统的基本框架就做好了。接下来，也是非常重要的一步，将该表的数据区域转换成“动态”列表结构。光标放在表格区域内，然后点击“插入-表格”然后将表名取为Sale。（如下动图所示）

接着在空白处插入查询条件的按钮。点击“插入-切片器”插入对应的字段按钮。

本例插入了城市和销售人员的按钮。（参考动图操作）

由于按钮的布局不太美观，所以将其竖向的查询按钮改成横向设置。在“切片器”工具中将默认的1列改为多列即可。可以根据字段对应的查询个数设置。比如本例城市为4个，所以设置为4的列数。见动图操作。

设置成横向后，可以按住alt键，拖拽查询对象，进行精确定位。看动图操作。

这样，一个精致的一对多查询系统就完成了，赶紧试试效果。你也单独点击某个城市某个销售人员的按钮，也可以按住ctrl键，同时选中多个查询条件，所以我们这个系统实际上也是一个多对多的查询。每次单击，下方数据区域的数据就会发生变化。

那如何实现动态的数据统计呢，比如统计查询条件对应的订单金额的和，以及数据行的个数？

这类统计看着复杂，其实只要用一个函数即可搞定。那就是subtotal函数，赶紧来操作一下。

=SUBTOTAL(109,Sale[订单金额]) 参与109统计筛选后的订单金额的和，这里参数109和9效果一样，但牛闪闪推荐用109

=SUBTOTAL(103,Sale[订单金额]) 参与103统计筛选后的订单金额的非空单元格的各数，这里参数103和3效果一样，但牛闪闪推荐用103.

有关subtotal的详细用法，大家可以搜索subtotal 即可。

这样，一个简单的一对多查询系统就搞定，不用任何VBA代码，完全利用Excel自带的功能，并且能够实现该数据表更新后，依然支持查询，是一个非常棒的查询系统哟。

总结：列表（插入-表格）+切片器+subtotal 算是Excel的黄金三搭档，专门制作一对多的动态数据查询和统计问题，推荐大家牛牛的掌握。