【需求】

這是一個(gè)小例子，需求很簡(jiǎn)單：
1.統(tǒng)計(jì)截止目前，所有我負(fù)責(zé)的班級(jí)數(shù)量和學(xué)員數(shù)量；
2.統(tǒng)計(jì)所有已經(jīng)結(jié)束的班級(jí)的數(shù)量，以及應(yīng)該結(jié)束但是尚未結(jié)束的班級(jí)數(shù)量；
3.統(tǒng)計(jì)本月開班的班級(jí)數(shù)量，并列出明細(xì)；
4.統(tǒng)計(jì)應(yīng)在本月結(jié)束的班級(jí)數(shù)量；
5.兩周匯報(bào)一次以上數(shù)據(jù)。

班級(jí)原始數(shù)據(jù).png

【思路】

因?yàn)槊績(jī)芍芫鸵獏R報(bào)一次統(tǒng)計(jì)結(jié)果。因此我希望實(shí)現(xiàn)自動(dòng)化——我只需要導(dǎo)出原始數(shù)據(jù)，然后打開報(bào)告，刷新，就獲得最新結(jié)果。為了實(shí)現(xiàn)這個(gè)目標(biāo)，我需要同時(shí)用到PowerQuery和PowerPivot。

PowerQuery的作用是引入數(shù)據(jù)源，并獲取本月開班明細(xì)；PowerPivot的作用是借助DAX來(lái)計(jì)算并得出其他數(shù)據(jù)。

【步驟】

1.建立單獨(dú)的項(xiàng)目文件夾
原始信息是從系統(tǒng)導(dǎo)出的數(shù)據(jù)，不作任何更改，也不重命名。如果沒(méi)有單獨(dú)的項(xiàng)目文件夾，由于系統(tǒng)導(dǎo)出的數(shù)據(jù)命名都是一樣的，所以很容易就和其他導(dǎo)出的數(shù)據(jù)文件混淆了，除非我重命名——作為懶人來(lái)講，重命名也很討嫌啊。

因此我需要單獨(dú)為此工作建立一個(gè)項(xiàng)目，我用文件夾的方式來(lái)統(tǒng)一管理原始數(shù)據(jù)和統(tǒng)計(jì)結(jié)果報(bào)表。

工程文件夾.png

為什么要單獨(dú)建立一個(gè)“原始信息”文件夾呢？這是處女座強(qiáng)迫癥——原始數(shù)據(jù)和結(jié)果數(shù)據(jù)混在一起總是讓人糾結(jié)，是不？

2.導(dǎo)出原始數(shù)據(jù)，放入項(xiàng)目文件夾的子文件夾“原始信息”文件夾中，不作任何改動(dòng)，也不重命名。系統(tǒng)中導(dǎo)出來(lái)的是什么樣，就是什么樣。

3.新建一個(gè)Excel文件，重命名為“班級(jí)統(tǒng)計(jì)結(jié)果”。

4.使用PowerQuery的參數(shù)表格建立參數(shù)表格及其函數(shù)。
如果只是我一個(gè)人用，其實(shí)參數(shù)表格沒(méi)必要。但是我有個(gè)野心——想讓其他同事拷貝我這個(gè)工程文件夾到ta電腦后，ta不需要做任何修改就可以直接使用模板。

參數(shù)表格建立方法見《PowerQuery的參數(shù)表格用法》。參數(shù)表格只有一個(gè)參數(shù)，其值是一個(gè)公式：

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&"原始信息\"

這個(gè)公式的目的就是為了讓此工程文件能在別的電腦上無(wú)障礙使用，不用修改數(shù)據(jù)源的路徑。

參數(shù)表格參數(shù)值.png

5.用PowerQuery對(duì)數(shù)據(jù)源進(jìn)行引入和整理，代碼如下：

let
源 = Folder.Files(fnGetParameter("班級(jí)匯總報(bào)表")),
刪除的其他列 = Table.SelectColumns(源,{"Content"}),
合并的二進(jìn)制 = Binary.Combine(刪除的其他列[Content]),
#"導(dǎo)入的 CSV" = Csv.Document(合并的二進(jìn)制,[Delimiter=",", Columns=14, Encoding=936, QuoteStyle=QuoteStyle.None]),
提升的標(biāo)題 = Table.PromoteHeaders(#"導(dǎo)入的 CSV"),
更改的類型 = Table.TransformColumnTypes(提升的標(biāo)題,{{"序號(hào)", Int64.Type}, {"班級(jí)名稱", type text}, {"起止日期", type text}, {"所屬域", type text}, {"承辦單位", type text}, {"班級(jí)管理員", type text}, {"應(yīng)到人數(shù)", Int64.Type}, {"實(shí)到人數(shù)", Int64.Type}, {"參加率", type text}, {"班級(jí)狀態(tài)", type text}, {"整體滿意度", Int64.Type}, {"預(yù)算費(fèi)用", Int64.Type}, {"實(shí)際費(fèi)用", Int64.Type}, {"", type text}}),
用分隔符分列 = Table.SplitColumn(更改的類型,"起止日期",Splitter.SplitTextByDelimiter("至", QuoteStyle.Csv),{"起止日期.1", "起止日期.2"}),
更改的類型1 = Table.TransformColumnTypes(用分隔符分列,{{"起止日期.1", type date}, {"起止日期.2", type date}}),
篩選的行 = Table.SelectRows(更改的類型1, each not Text.Contains([班級(jí)名稱], "提報(bào)")),
重排序的列 = Table.ReorderColumns(篩選的行,{"序號(hào)", "班級(jí)名稱", "實(shí)到人數(shù)", "起止日期.1", "起止日期.2", "所屬域", "承辦單位", "班級(jí)管理員", "應(yīng)到人數(shù)", "參加率", "班級(jí)狀態(tài)", "整體滿意度", "預(yù)算費(fèi)用", "實(shí)際費(fèi)用", ""}),
刪除的其他列1 = Table.SelectColumns(重排序的列,{"班級(jí)名稱", "班級(jí)狀態(tài)","實(shí)到人數(shù)", "起止日期.1", "起止日期.2"}),
重命名的列 = Table.RenameColumns(刪除的其他列1,{{"起止日期.1", "線上學(xué)習(xí)開始日期"}, {"起止日期.2", "線上學(xué)習(xí)結(jié)束日期"}})
in
重命名的列

忽略多余的字段和代碼格式吧。我也沒(méi)有對(duì)每一個(gè)步驟進(jìn)行有意義的命名。

注意“源=……”這一行，使用了參數(shù)表格函數(shù)引入數(shù)據(jù)源路徑。
注意“用分隔符分列”這個(gè)步驟，這是因?yàn)樵紨?shù)據(jù)的班級(jí)開班和結(jié)束日期是在同一個(gè)字段里，因此需要將其拆開為“開始日期”和“結(jié)束日期”兩個(gè)字段。

6.將PowerQuery查詢結(jié)果加載到數(shù)據(jù)模型，僅創(chuàng)建鏈接。

7.獲取本月開班明細(xì)。
這里我是直接引用了前面創(chuàng)建好的查詢，然后用公式篩選，代碼如下：

let
  源 = 原始信息,
  篩選的行 = Table.SelectRows(源, each Date.IsInCurrentMonth([線上學(xué)習(xí)開始日期]))
in
  篩選的行

這里也可以用PowerQuery的Evaluate來(lái)創(chuàng)建查詢，但是比較麻煩，還不如直接引用查詢?nèi)缓蠛Y選。

將其加載到表，并重命名為“本月明細(xì)”。

注意用Date.IsInCurrentMonth()的目的也是為了偷懶，要不然可以直接篩選具體的月份來(lái)實(shí)現(xiàn)——但那樣太麻煩，不是？

8.進(jìn)入PowerPivot，創(chuàng)建需要的度量值：

總班級(jí)數(shù)=COUNTROWS('原始信息')
總參加學(xué)員數(shù)=SUM('原始信息'[實(shí)到人數(shù)])
已結(jié)束的班級(jí)=CALCULATE(COUNTROWS('原始信息'),FILTER('原始信息','原始信息'[班級(jí)狀態(tài)]="已結(jié)束"))
本月=MONTH(NOW())
本月開始線上學(xué)習(xí)的班級(jí)數(shù)=CALCULATE(COUNTROWS('原始信息'),FILTER('原始信息',MONTH('原始信息'[線上學(xué)習(xí)開始日期])=[本月]))
本月參加學(xué)員數(shù)=CALCULATE(SUM('原始信息'[實(shí)到人數(shù)]),FILTER('原始信息',MONTH('原始信息'[線上學(xué)習(xí)開始日期])=[本月]))
本月應(yīng)結(jié)束班級(jí)數(shù)=CALCULATE(COUNTROWS('原始信息'),FILTER('原始信息',MONTH('原始信息'[線上學(xué)習(xí)結(jié)束日期])=[本月]))
上月應(yīng)結(jié)束班級(jí)數(shù)=CALCULATE(COUNTROWS('原始信息'),FILTER('原始信息',MONTH('原始信息'[線上學(xué)習(xí)結(jié)束日期])=[本月]-1))

9創(chuàng)建基于數(shù)據(jù)模型的數(shù)據(jù)透視表，將需要的字段拖入數(shù)據(jù)透視表，最后結(jié)果如下：

統(tǒng)計(jì)結(jié)果.png

很簡(jiǎn)陋的一個(gè)表，因?yàn)槭莾?nèi)部使用，我就偷懶了。
公式和統(tǒng)計(jì)結(jié)果中，我塞入了一個(gè)“上月應(yīng)結(jié)束班級(jí)數(shù)”，這個(gè)是個(gè)bonus，可以無(wú)視。

【共享方法】

將工程文件夾全部拷給需要的人，然后ta導(dǎo)出自己需要的班級(jí)數(shù)據(jù)，放到“原始信息”文件夾，打開統(tǒng)計(jì)結(jié)果文件，刷新，即可獲取最新結(jié)果。

有個(gè)前提：最好是Excel 2016。如果是Excel 2013之類的老版本，確保PowerQuery和PowerPivot插件啟用。

【反思】

其實(shí)最后建立度量值時(shí)，我可以不必那么麻煩，利用好切片器和篩選，可以獲取到更動(dòng)態(tài)的統(tǒng)計(jì)結(jié)果。但是考慮到不見得使用此報(bào)表的人員會(huì)使用這些復(fù)雜的數(shù)據(jù)透視表功能，所以我直接把對(duì)方想要的數(shù)據(jù)寫死呈現(xiàn)出來(lái)。