-
1 # Excel高效技能分享
-
2 # Excel到PowerBI
在excel中，只填一個表同時讓其他資料自動分填到指定的各表中，並且，按需要出具月度的彙總分析報告——其實，這只是一個根據需要分表和彙總分析的需求，所謂“自動”，要先說明一下。
一、Excel中“自動”真是最佳解決方案嗎？應該自動到什麼程度？很多人所認為的“自動”，是總表裡一填入，馬上自動地出現在相應的分表中——其實，這種方式不是辦不到，只是沒有必要，而且也不是最好的解決方案，原因如下：
你需要分表，多數情況下只是有時候需要單獨進行提交而已，並沒有“時刻”和總表保持一致必要，只需要在需要得到分表的時候，能很快的出來就行；
即使是你要做的月度彙總表，也只是在需要的時候能夠自動更新，也不需要“時刻”和錄入資料的總表保持一致；
因此，實際工作中，所需要做的“自動”，最佳的解決方案通常是，只在需要的時候，能一鍵更新，而不需要手工重新做一遍即可！
基於以上對“自動”的認識，然後再回頭看這個問題的兩個具體需求並一一給出解法：
二、錄入資料的總表填入不同任務分表的最佳解決方案對於工作總表拆分為不同的分表問題，方法很多，比如資料透視法、VBA法等等，但是這兩種方法分別存在以下問題：
資料透視法拆分後會帶上總表的所有資料，並且不能去除篩選項；
VBA法需要編碼，起碼也要學會基本的讀和改程式碼。
實際上，這是一個相抵固定任務個數的表格問題，對於大多數的Excel使用者來說，用Excel2016的新功能Power Query（Excel2010或Excel2013可到微軟官方下載相應的外掛）來做生成分表，才是最佳的解決方案。具體過程如下：
1、載入總表資料2、篩選出第1個任務表所需資料，並可按需要進一步做其他個性化資料處理3、複製拆分的表14、修改篩選條件得到其他表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可以用來做這種資料的拼接，但是一般還是建議最好用標準化的資料透視表，那樣統計的效率會高很多。
以上從Excel中的“自動”的概念、任務總表如何拆分到不同的分表、如何對資料進行有效的組合拼接以生成特殊格式的報表等三個方面對問題進行了基本的描述，希望這些工作中的一點點經驗和體會，能幫到大家。
【60+函式彙總案例】
【資料透視基礎精選10篇】
【Power Query入門到實戰80篇】
【Power Pivot 基礎精選15篇】
回覆列表
利用資料透視表能夠簡單有效的實現你的需求。
由於圖不是很清楚，我舉例來解釋一下。
只填一個表——資料來源資料來源類似於資料庫的概念，一維表的格式，也就是指：首行是欄位，然後每列都是同種屬性的資料。如下圖，資料來源的第一行就是日期、產品、客戶、金額等欄位，日期欄位這一列，都是日期，產品列都是產品名稱諸如此類。
資料來源裡需要列出所有後期要彙總的資訊或資料，這樣在後期彙總時才能做到全面。
資料資訊的輸入可藉助下拉列表這樣做的好處是，輸入的資料是統一的，後期的統計不容易出錯。
比如產品1，你可以輸入“產品1”，也可能輸入了“產品 1”（1前面加了空格），認為判斷時會知道這是一種產品，但機器會當做2種。在給出的資料列表中選擇就可以避免手誤，也就避免了後期統計的錯誤。
用透視表對資料來源進行統計透視表的優勢在於格式的靈活性，可以根據需求自由組建報告的格式。
關注客戶，檢視每個客戶的產品銷售額：
關注產品，檢視每個產品類別都賣給了哪些客戶：
關注時間週期，檢視週期內的銷售額：
資料透視表的功能很強大，以上列出的是比較常用的基礎應用，資料來源建立好的話，自動彙總成你需要的表格不是問題。
我提出一個思路，希望可以幫到你。