在excel中，只填一個(gè)表同時(shí)讓其他數(shù)據(jù)自動(dòng)分填到指定的各表中，并且，按需要出具月度的匯總分析報(bào)告——其實(shí)，這只是一個(gè)根據(jù)需要分表和匯總分析的需求，所謂“自動(dòng)”，要先說(shuō)明一下。

一、Excel中“自動(dòng)”真是最佳解決方案嗎？應(yīng)該自動(dòng)到什么程度？

很多人所認(rèn)為的“自動(dòng)”，是總表里一填入，馬上自動(dòng)地出現(xiàn)在相應(yīng)的分表中——其實(shí)，這種方式不是辦不到，只是沒有必要，而且也不是最好的解決方案，原因如下：

1. 你需要分表，多數(shù)情況下只是有時(shí)候需要單獨(dú)進(jìn)行提交而已，并沒有“時(shí)刻”和總表保持一致必要，只需要在需要得到分表的時(shí)候，能很快的出來(lái)就行；
   

2. 即使是你要做的月度匯總表，也只是在需要的時(shí)候能夠自動(dòng)更新，也不需要“時(shí)刻”和錄入數(shù)據(jù)的總表保持一致；
   

3. 如果要做到每在錄入數(shù)據(jù)的表中的一個(gè)變化，都驅(qū)動(dòng)相應(yīng)的分表和月度總表進(jìn)行相應(yīng)的變化，會(huì)導(dǎo)致工作表頻繁偵測(cè)各種額外事件（如更新數(shù)據(jù)、增加行、刪除行等等），不僅處理復(fù)雜，而且會(huì)導(dǎo)致系統(tǒng)運(yùn)行非常緩慢，得不償失！
   

因此，實(shí)際工作中，所需要做的“自動(dòng)”，最佳的解決方案通常是，只在需要的時(shí)候，能一鍵更新，而不需要手工重新做一遍即可！

基于以上對(duì)“自動(dòng)”的認(rèn)識(shí)，然后再回頭看這個(gè)問(wèn)題的兩個(gè)具體需求并一一給出解法：

二、錄入數(shù)據(jù)的總表填入不同任務(wù)分表的最佳解決方案

對(duì)于工作總表拆分為不同的分表問(wèn)題，方法很多，比如數(shù)據(jù)透視法、VBA法等等，但是這兩種方法分別存在以下問(wèn)題：

• 數(shù)據(jù)透視法拆分后會(huì)帶上總表的所有數(shù)據(jù)，并且不能去除篩選項(xiàng)；
  

• VBA法需要編碼，起碼也要學(xué)會(huì)基本的讀和改代碼。
  

實(shí)際上，這是一個(gè)相抵固定任務(wù)個(gè)數(shù)的表格問(wèn)題，對(duì)于大多數(shù)的Excel用戶來(lái)說(shuō)，用Excel2016的新功能Power Query（Excel2010或Excel2013可到微軟官方下載相應(yīng)的插件）來(lái)做生成分表，才是最佳的解決方案。具體過(guò)程如下：

1、加載總表數(shù)據(jù)

2、篩選出第1個(gè)任務(wù)表所需數(shù)據(jù)，并可按需要進(jìn)一步做其他個(gè)性化數(shù)據(jù)處理

3、復(fù)制拆分的表1

4、修改篩選條件得到其他表

5、按需要進(jìn)行進(jìn)一步的個(gè)性化處理

以上是用Power Query生成2個(gè)分表的例子，如果需要繼續(xù)增加其他任務(wù)分表，重復(fù)步3至5即可。所有需要的表格生成后，結(jié)果返回Excel中，多個(gè)分表，如下圖所示：

以后數(shù)據(jù)更新時(shí)，單擊全部刷新即可“自動(dòng)”得到最新的拆分結(jié)果，如下圖所示：

三、按需要自動(dòng)得到月度匯總表的最佳解決方案

如果所需要匯總的月度數(shù)據(jù)是相對(duì)標(biāo)準(zhǔn)的，可以用數(shù)據(jù)透視基于錄入數(shù)據(jù)總表生成，也可以在需要刷新數(shù)據(jù)時(shí)一鍵自動(dòng)刷新。但從問(wèn)題所畫的簡(jiǎn)圖來(lái)看，這很可能不是一個(gè)標(biāo)準(zhǔn)的通過(guò)數(shù)據(jù)透視能直接得到的匯總表，因此，需要考慮不同數(shù)據(jù)的拼接問(wèn)題。以下舉一個(gè)用Excel2016的新功能Power Query（Excel2010或Excel2013可到微軟官方下載相應(yīng)的插件）來(lái)實(shí)現(xiàn)非標(biāo)準(zhǔn)報(bào)表的匯總拼接并可一鍵刷新效果的例子，以供參考。

領(lǐng)導(dǎo)就要這樣的匯總表，還要一鍵刷新！

小勤：能用數(shù)據(jù)透視實(shí)現(xiàn)從數(shù)據(jù)明細(xì)到這樣的數(shù)據(jù)匯總嗎？

大海：對(duì)不起，不能啊。一定要這樣嗎？說(shuō)（shui）服一下領(lǐng)導(dǎo)用標(biāo)準(zhǔn)的數(shù)據(jù)透視唄。

小勤：說(shuō)不服不了啊！領(lǐng)導(dǎo)說(shuō)這樣最直觀。那Power Pivot行嗎？

大海：Power Pivot也不能直接實(shí)現(xiàn)，不過(guò)可以考慮Power Query和Power Pivot結(jié)合來(lái)實(shí)現(xiàn)。

小勤：這么復(fù)雜？

大海：先說(shuō)一下這種非標(biāo)準(zhǔn)的數(shù)據(jù)匯總特殊的地方。

• 區(qū)域合計(jì)數(shù)與區(qū)域在同一維度：這相當(dāng)于在區(qū)域這個(gè)維度上增加了一項(xiàng)同級(jí)別的內(nèi)容，而不僅僅是數(shù)據(jù)透視表中的合計(jì)項(xiàng)顯示問(wèn)題了，這種數(shù)據(jù)追加的問(wèn)題，可以考慮用Power Query的追加合并功能來(lái)實(shí)現(xiàn)。

• 跨維度合并數(shù)據(jù)：這相當(dāng)于在一個(gè)細(xì)的維度上去統(tǒng)計(jì)另一個(gè)維度上的內(nèi)容，但好在只是計(jì)算問(wèn)題，所以可以通過(guò)Power Pivot的DAX函數(shù)來(lái)實(shí)現(xiàn)，當(dāng)然，也可以通過(guò)Power Query的合并查詢（橫向擴(kuò)展）功能來(lái)實(shí)現(xiàn)。

小勤：那總體看起來(lái)就可以直接用Power Query來(lái)實(shí)現(xiàn)了？

大海：是的。但Power Query出來(lái)的結(jié)果是不能合并單元格的。呵呵。

小勤：沒有合并單元格也能接受。

大海：那就先用Power Query實(shí)現(xiàn)一下，后面我再跟你講怎么Power Query跟Power Pivot結(jié)合起來(lái)做完整的。

Step-01：獲取數(shù)據(jù)

Step-02：分組生成各區(qū)域及細(xì)類匯總

Step-03：引用分區(qū)匯總表，并按需要修改名字

Step-04：在引用的查詢中再次分組，生成三區(qū)合并的銷量數(shù)據(jù)

Step-05：添加自定義列，使三區(qū)合計(jì)的表與分區(qū)域的表結(jié)構(gòu)一致

Step-06：將原分區(qū)的表和三區(qū)合并的表進(jìn)行追加查詢，合并成新的查詢

Step-07：用新的查詢?cè)俅魏喜ⅰ叭齾^(qū)合并”查詢，獲得最右側(cè)的三區(qū)合計(jì)列

Step-08：展開合并的數(shù)據(jù)

Step-09：按需要重命名列

Step-10：排序

結(jié)果出來(lái)了，最后上載數(shù)據(jù)：

小勤：感覺還好啊，也不算復(fù)雜。簡(jiǎn)單來(lái)說(shuō)就是先分組得到各個(gè)區(qū)域的，然后另外在建個(gè)查詢分組得到三區(qū)合計(jì)的，然后用各個(gè)區(qū)域的縱向追加三區(qū)合計(jì)的，再橫向和三區(qū)合計(jì)的合并起來(lái)……

大海：嗯。整個(gè)過(guò)程主要就是這個(gè)思路。

小勤：知道了。以后這種報(bào)表非標(biāo)準(zhǔn)的報(bào)表要自動(dòng)生成也不怕了。反正就是通過(guò)Power Query各種拼接。呵呵。

大海：對(duì)的。Power Query可以用來(lái)做這種數(shù)據(jù)的拼接，但是一般還是建議最好用標(biāo)準(zhǔn)化的數(shù)據(jù)透視表，那樣統(tǒng)計(jì)的效率會(huì)高很多。

以上從Excel中的“自動(dòng)”的概念、任務(wù)總表如何拆分到不同的分表、如何對(duì)數(shù)據(jù)進(jìn)行有效的組合拼接以生成特殊格式的報(bào)表等三個(gè)方面對(duì)問(wèn)題進(jìn)行了基本的描述，希望這些工作中的一點(diǎn)點(diǎn)經(jīng)驗(yàn)和體會(huì)，能幫到大家。