iSlide - 理清思路，五步讓報表自動化處理－鑽石舞台

Mar 19 Sat 2022 06:30
iSlide - 理清思路，五步讓報表自動化處理

在 2020~2021 的培訓中，我們更加確認 Excel 的基礎不僅僅是常規功能、函數、透視表……更是良好的數據管理思維習慣。

‍‍‍有個工作多年的小夥伴，一直泡在各類報表里，即便學會了五花八門的功能和函數，但還是逃不過加班的命運。數據表格設置的不合理，導致後續難以進行透視和分析，甚至很多數據還得手動錄入和修改。

比如下面的數據表，第一行合併單元格做標題，可能導致不便使用透視表。第二行把日期、地區單獨放出來，給匯總分析帶來了不便。

更快捷的思路是：

首先，把【公司訂單數據表】整行去掉；

其次，把【訂單日期】【城市】添加到一列數據中，雖然數據量會增加，但更便於讓表單匯總和透視；

然後，新增一個表單，把【城市】和【區域】一一對應放進去，後期可以通過 vlookup 或者其他方法進行匹配；

最後，選中整合好的數據，插入數據透視表，進行各個維度的分析。

那什麼是比較好數據管理思維呢。就是當我們有數據要處理時，要往以下方向多考慮一步：

1.【處理數據的目的】是用來統計分析、打印查看或者簽批留存。不同的目的，在格式的處理上有所不同。有個通用的方法是按照統計分析的方向留存一個數據源，然後打印和簽批的形式，都可以從數據源中引入數據進行調整。

2.【數據的來源】是需要我們做模版收集、進行系統導出、或者由其他同事給到。不同數據來源有不同的處理難度。其中系統導出的比較規整；模板收集的儘可能使用問卷或者在線填報軟件強制規範錄入者的數據輸入；同事給到的數據要通過溝通固定模板和數據格式，便於後期處理。

3.【重複周期】匯總統計的周期，是每月一次、每季度一次……也有可能遇到統計某個開始時間到結束時間的需求。解決周期性統計的方法就是講日期參數放到列中，所有日期數據匯聚在一張表單里，可以通過透視表進行每個日期段的透視。

4.【關注維度】思考統計時常用的維度、比如時間維度、空間維度、年齡維度等，然後看看已有的數據是否包含需要的維度，如果沒有包含，思考如何補全他們。

在製作表單前，想清楚上面的四個方向，就可以更有針對性地處理數據了。

下面舉一個實際的案例，小張在某電商總部工作，他每天要匯總各個城市的日報表數據，會按照每周、每月、每季度進行統計分析。由於日報表模板不夠完美，大部分時間需要手動粘貼到一起，才能使用透視表。

他想要實現把每日訂單數據放到文件夾中，點擊刷新就可以自動更新的數據，便於分析。以下我們就講講這個功能的操作方法。

（公眾號內發送關鍵詞【EXCEL練習】獲取練習文件包）

這個方法主要用到了 Excel 一項新功能PowerQuery，Office2016 以上的版本就能使用，當然最推薦的是使用 Office365 版本。

我們的目標是把分在各個表格中的數據整理為數據透視表可以使用的格式，並匯總成一張總表，最終用透視表完成統計。

第一步

前序準備

1、把需要將至少2個工作簿放到一個文件夾中。該分享就將文件夾命名為【每日訂單報表】，後續的每日報表都會放到該文件夾中進行現自動匯總。

2、在該文件夾外，新建一個 Excel 表，可以取名叫【數據匯總】。

注意事項：千萬不要把匯總表放到【每日訂單報表】文件夾內，不然會形成死循環。

第二步

使用PQ獲取數據

1、打開【數據匯總】Excel 表，【數據】功能選卡中，Office365 版本找到【獲取數據】-【來自文件】-【從文件夾】。Office2016 版本在【數據】-【新建查詢】-【從文件】-【從文件夾】。

2、點擊【從文件夾】後，通過【瀏覽按鈕】選擇文件存放的文件夾【每日訂到報表】，選擇好文件夾，雖然裡面看不到文件，但是不要慌，點擊打開就好。最終地址會被記錄下來，點擊確認。

3、在彈出來的數據加載框的右下角，選擇【轉換數據】對數據進行整合

第三步

使用PQ解析合併數據

1、點擊轉換數據後，進入到PowerQuery的操作界面，和 Excel 差不多。

紅色的是功能區，對整列數據進行轉換、提取、判斷等操作。

藍色的是數據區，在這裡可以看到數據情況，並且通過鼠標右鍵進行一些快捷操作。

紫色的是步驟區，可以用來看到每一步操作，可以修改或撤銷做錯的步驟。

2、現在藍色區域展現的是文件夾中3張需要匯總的表單的信息，如表名、格式等。而表單中的實際數據是存在了第一列 Content 中。其他列可以右鍵刪除。操作方法是點擊選中 Content 列，點擊鼠標右鍵，在彈出的退畫框中點擊刪除其他列。

3、通過添加列增加解析列，會用到本練習中唯一的函數Excel.Workbook([content],false)

注意事項：

a、函數名稱要注意首字母大寫，括號為英文半角的符號；

b、只有365版本才有提升，其他版本需要手動輸入。

細節分享：函數有兩個參數，一個是【content】，鼠標在右框雙擊即可；第二個參數可以輸入true或false，含義為是否要整合表頭，具體區別如下：

而我工作的表單就是後一種，T.T，所以用了false

4、解析完成數據已經在新添加的【自定義列】的 Table 里了，可以刪除 content 列，然後點擊自定義列右邊的按鈕，進行第一次展開該列數據。展開後的數據為每一張表裡面，每一個 Sheet 的信息，其中【自定義.Data】中的 Table 存放了對應表單的數據。

5、由於我們每個工作簿里，只有一張 Sheet1 表單，並且數據也存在裡面。於是這裡不用進行篩選，直接保留【自定義.Data】列，把其他的去掉，然後展開該列。

注意事項：在點擊確定展開時把【使用原始列名作為前綴】前面的勾去掉，不然每一列名字都會叫自定義.Data.xxxx。

這樣一來所有數據就都匯總在一張表里啦，來看看初步成果，上海、北京、重慶都有了。

第四步

使用PQ清理數據

1、雖然數據匯總了，但是想要用透視表來分析，還有3個需要調整的格式：

a.將每一張表的標題【公司訂單數據表】去掉；

b.要將訂單日期、區域、城市的信息形成單獨的列；

c.每張表都有表頭，要把重複的表頭去掉。

2、通過篩選功能就可以把標題去掉，點擊表頭列1右邊的箭頭，打開篩選窗口，就和Excel中一樣，將【公司訂單數據表】前面的勾去掉機構。

3、將訂單日期、區域、城市數據轉變成按列存儲的方式，主要用到條件列功能。由於日期、區域、城市都在同一行，在PQ中用判斷第一列是否包含【訂單日期】就能取得對應的數據。

首先在PQ中判斷是否包含某個文字，需要先將對應的列轉換為文本格式。點擊表頭前的【ABC123】，將第一列轉換為【文本格式ABC】。

4、通過判斷將含有日期、區域、城市信息的行區分出來，這裡用第一列是否含有【訂單日期】作為區分，使用條件列進行判斷，如果是訂單日期，就返回第二列的訂單日期數據，如果不是，就返回 null 。點擊【添加列】-【條件列】，在彈出的對話框中進行輸入：

a.【新列名】可以修改名稱 if 後面的;

b.【列名】是指用哪一列參與判斷，這裡選擇Column1（列1）;

c.【運算符】是判斷的方式，這裡選擇包含;

d.【值】是指運算符包含所要包含的內容;

e.【輸出】是指【列名】所選的列滿足判斷條件，將返回的值。這裡可以通過點擊【ABC123】來選擇【輸出一個值（返回一個固定的值）】，還是【選擇列（返回所選列在對應行上的值）】。這裡輸出選擇【選擇列】，後面選擇Column2（列2），這樣當第一列為訂單日期時，就會返回訂單日期所在這一行中，第2列的數據，也就是 2020/1/1 。如果其他日期的數據導入後，也能取得對應的日期;

f.【ELSE】是指【列名】所選的列不滿足判斷條件，將返回的值。這裡填寫null，null在PQ中代表空值，什麼都沒有。