在excel中，怎么才能只填一个表同时让其他数据自动分填到指定的各表中？

非一般的感觉vl 2018-03-14

展开全文

在excel中，只填一个表同时让其他数据自动分填到指定的各表中，并且，按需要出具月度的汇总分析报告——其实，这只是一个根据需要分表和汇总分析的需求，所谓“自动”，要先说明一下。

一、Excel中“自动”真是最佳解决方案吗？应该自动到什么程度？

很多人所认为的“自动”，是总表里一填入，马上自动地出现在相应的分表中——其实，这种方式不是办不到，只是没有必要，而且也不是最好的解决方案，原因如下：

你需要分表，多数情况下只是有时候需要单独进行提交而已，并没有“时刻”和总表保持一致必要，只需要在需要得到分表的时候，能很快的出来就行；
即使是你要做的月度汇总表，也只是在需要的时候能够自动更新，也不需要“时刻”和录入数据的总表保持一致；
如果要做到每在录入数据的表中的一个变化，都驱动相应的分表和月度总表进行相应的变化，会导致工作表频繁侦测各种额外事件（如更新数据、增加行、删除行等等），不仅处理复杂，而且会导致系统运行非常缓慢，得不偿失！

因此，实际工作中，所需要做的“自动”，最佳的解决方案通常是，只在需要的时候，能一键更新，而不需要手工重新做一遍即可！

基于以上对“自动”的认识，然后再回头看这个问题的两个具体需求并一一给出解法：

二、录入数据的总表填入不同任务分表的最佳解决方案

对于工作总表拆分为不同的分表问题，方法很多，比如数据透视法、VBA法等等，但是这两种方法分别存在以下问题：

数据透视法拆分后会带上总表的所有数据，并且不能去除筛选项；
VBA法需要编码，起码也要学会基本的读和改代码。

实际上，这是一个相抵固定任务个数的表格问题，对于大多数的Excel用户来说，用Excel2016的新功能Power Query（Excel2010或Excel2013可到微软官方下载相应的插件）来做生成分表，才是最佳的解决方案。具体过程如下：

1、加载总表数据

2、筛选出第1个任务表所需数据，并可按需要进一步做其他个性化数据处理

3、复制拆分的表1

4、修改筛选条件得到其他表

5、按需要进行进一步的个性化处理

以上是用Power Query生成2个分表的例子，如果需要继续增加其他任务分表，重复步3至5即可。所有需要的表格生成后，结果返回Excel中，多个分表，如下图所示：

以后数据更新时，单击全部刷新即可“自动”得到最新的拆分结果，如下图所示：

三、按需要自动得到月度汇总表的最佳解决方案

如果所需要汇总的月度数据是相对标准的，可以用数据透视基于录入数据总表生成，也可以在需要刷新数据时一键自动刷新。但从问题所画的简图来看，这很可能不是一个标准的通过数据透视能直接得到的汇总表，因此，需要考虑不同数据的拼接问题。以下举一个用Excel2016的新功能Power Query（Excel2010或Excel2013可到微软官方下载相应的插件）来实现非标准报表的汇总拼接并可一键刷新效果的例子，以供参考。

领导就要这样的汇总表，还要一键刷新！
小勤：能用数据透视实现从数据明细到这样的数据汇总吗？
大海：对不起，不能啊。一定要这样吗？说（shui）服一下领导用标准的数据透视呗。
小勤：说不服不了啊！领导说这样最直观。那Power Pivot行吗？
大海：Power Pivot也不能直接实现，不过可以考虑Power Query和Power Pivot结合来实现。
小勤：这么复杂？
大海：先说一下这种非标准的数据汇总特殊的地方。
区域合计数与区域在同一维度：这相当于在区域这个维度上增加了一项同级别的内容，而不仅仅是数据透视表中的合计项显示问题了，这种数据追加的问题，可以考虑用Power Query的追加合并功能来实现。
跨维度合并数据：这相当于在一个细的维度上去统计另一个维度上的内容，但好在只是计算问题，所以可以通过Power Pivot的DAX函数来实现，当然，也可以通过Power Query的合并查询（横向扩展）功能来实现。
小勤：那总体看起来就可以直接用Power Query来实现了？
大海：是的。但Power Query出来的结果是不能合并单元格的。呵呵。
小勤：没有合并单元格也能接受。
大海：那就先用Power Query实现一下，后面我再跟你讲怎么Power Query跟Power Pivot结合起来做完整的。
Step-01：获取数据
Step-02：分组生成各区域及细类汇总
Step-03：引用分区汇总表，并按需要修改名字
Step-04：在引用的查询中再次分组，生成三区合并的销量数据
Step-05：添加自定义列，使三区合计的表与分区域的表结构一致
Step-06：将原分区的表和三区合并的表进行追加查询，合并成新的查询
Step-07：用新的查询再次合并“三区合并”查询，获得最右侧的三区合计列
Step-08：展开合并的数据
Step-09：按需要重命名列
Step-10：排序
结果出来了，最后上载数据：
小勤：感觉还好啊，也不算复杂。简单来说就是先分组得到各个区域的，然后另外在建个查询分组得到三区合计的，然后用各个区域的纵向追加三区合计的，再横向和三区合计的合并起来……
大海：嗯。整个过程主要就是这个思路。
小勤：知道了。以后这种报表非标准的报表要自动生成也不怕了。反正就是通过Power Query各种拼接。呵呵。
大海：对的。Power Query可以用来做这种数据的拼接，但是一般还是建议最好用标准化的数据透视表，那样统计的效率会高很多。