原文標(biāo)題：《這么牛 X 的漂亮動態(tài)日歷，是怎么用 Excel 做出來的？（不是透視表）》
大家好，我是在研究日歷做法的小爽~
不知不覺，2023 年已經(jīng)過去幾個月啦~
之前我們介紹過，利用數(shù)據(jù)透視表制作日歷。
也介紹過利用函數(shù)制作日歷。
不過，有個小伙伴問，能不能用 PQ 制作日歷？
我突然想到 PQ 法做日歷，好像沒給大家介紹過。
PowerQuery (PQ) 里面也有很多日期類函數(shù)，也可以制作日歷。（只不過難度會比數(shù)據(jù)透視表大一點點，而且還會涉及幾個 M 函數(shù)。）
既然小伙伴們想學(xué)，那今天小爽將會帶大家一步步編寫 M 函數(shù)公式，來制作這個日歷。
1、構(gòu)造數(shù)據(jù)
在制作之前，我們先構(gòu)造一個查詢表，月的單元格，可以事先利用數(shù)據(jù)驗證設(shè)置一個下拉列表。
具體步驟：
? 將查詢表導(dǎo)入到 PQ 編輯器中。選中 A1:B2 單元格區(qū)域，在【數(shù)據(jù)】] 選項卡下，單擊【來自表格 / 區(qū)域】，進(jìn)入 PQ 編輯器中。
單擊 fx 可以新增一個公式步驟。（后續(xù)新增步驟都是點這里哦~）
鼠標(biāo)移動到需更改的步驟上，按右鍵，單擊【重命名】即可修改步驟名稱。（后續(xù)重命名步驟都是點這里哦~）
PS：命名好步驟名稱有助于提高公式的可讀性。
? 新增步驟，獲取查詢表中年和月的第一天，步驟名稱命名為「月份第一天」。
= #date(源[年]{0},源[月]{0},1)
小 Tips：
= #date (年，月，日) 主要是用來構(gòu)造一個日期。
源 [年]{0} 獲取表中的年。
源 [月]{0} 獲取表中的月。
? 新建步驟，獲取查詢表中年月的最后一天。步驟名稱命名為「月份最后一天」。
= Date.EndOfMonth(月份第一天)
Date.EndOfMonth 函數(shù)可以返回日期當(dāng)月的最后一天的日期。
? 新建步驟，將第一天和最后一天日期進(jìn)行擴(kuò)展。步驟命名為「月日期」。
= List.Transform({Number.From(月份第一天)..Number.From(月份最后一天)},Date.From)
簡單解釋一下：在 M 函數(shù)表達(dá)式中，列表的表示方式是用 {中括號}，如下圖，{1,2}，就是 1，2 形成的列表。
如果要表示 1 到 9 的列表，就是 {1,2,3,4,5,6,7,8,9}，可簡寫為 {1..9}，如下圖：
由于日期的本質(zhì)是個數(shù)值，所以我們可以先將日期利用 Number.From 先轉(zhuǎn)為數(shù)值，然后再進(jìn)行擴(kuò)展。最后利用 Date.From 轉(zhuǎn)為日期即可。
{Number.From(月份第一天)..Number.From(月份最后一天)}現(xiàn)在，一整個月的日期我們都做出來了。
觀察日歷表，可以發(fā)現(xiàn)，我們還需要得到日期中的日，星期數(shù)，以及每月周數(shù) 的相關(guān)數(shù)據(jù)。
所以我們下面三個步驟就是為了獲取這三塊的內(nèi)容。
? 新建步驟，獲取日期的天數(shù)。步驟命名為「獲取日」。
= List.Transform(月日期,Date.Day)
Date.Day 可以獲取日期中的日。
? 新建步驟，獲取星期數(shù)。步驟命名為「獲取星期數(shù)」。
= List.Transform(月日期,Date.DayOfWeekName)
Date.DayOfWeekName 可以獲得日期的星期數(shù)。
? 新建步驟，獲取日期對應(yīng)的當(dāng)前月的周數(shù)。步驟命名為「周數(shù)」。
= List.Transform((月日期),Date.WeekOfMonth)
Date.WeekOfMonth 可以獲得日期對應(yīng)的當(dāng)月的周數(shù)。
到這里，我們已經(jīng)把所需要的三個數(shù)據(jù)弄出來了。
2、轉(zhuǎn)表透視
由于日歷是個表，所以我們還需要將數(shù)據(jù)進(jìn)行整合合并在一起形成一個表。
? 新建步驟，拼接成表。步驟命名為「數(shù)據(jù)」。
= Table.FromColumns({周數(shù)獲取星期數(shù)獲取日})
Table.FromColumns 可以按列轉(zhuǎn)換為表。
? 日歷表是個二維數(shù)據(jù)，所以我們還需要將星期數(shù) (Column2 列) 進(jìn)行透視處理。
選中 [Colum2] 列，在【轉(zhuǎn)換】選項卡下，單擊【透視列】，出現(xiàn)透視列彈窗，值列選擇 [Column3] 列，單擊【確定】按鈕。
到這里，我們發(fā)現(xiàn)，星期數(shù)并不是按照我們想要的效果進(jìn)行排序的。
只需要更改第二參數(shù)，就可以改變?nèi)掌诘捻樞颉?/p>
原本的公式：
= Table.Pivot數(shù)據(jù) List.Distinct數(shù)據(jù)[Column2]), "Column2", "Column3", List.Sum)
修改后的公式：
= Table.Pivot(數(shù)據(jù), {"星期一","星期二","星期三","星期四","星期五","星期六","星期日"}, "Column2", "Column3", List.Sum)
當(dāng)然，如果你想要的日期是 從星期日開始的，也可以通過改變第二參數(shù)的順序來實現(xiàn)。
? 最后一步就是將我們不需要的 Column1 列，也就是顯示月周數(shù)的列，進(jìn)行刪除即可。
選中 Column1 列，按鼠標(biāo)右鍵-刪除。
現(xiàn)在，我們的日期就制作完成啦~
? 最后將 PQ 做好的日歷表加載到工作表中，就搞定了!
依次點擊【文件】選項卡-【關(guān)閉并上載至】，選擇「現(xiàn)有工作表」并指定單元格位置。
3、自動更新
由于 PQ 每一次更改查詢表的年月，都需要刷新一次，很麻煩。
所以，我們可以加個工作表事件，當(dāng) A2 和 A3 單元格發(fā)生值改變的時候，將表格全部進(jìn)行更新。??????
首先，將表格另存為.xlsm 格式。
然后，按住快捷鍵【Alt+F11】進(jìn)入 VBA 編輯器中。
在當(dāng)前工作表下。
輸入這段 VBA 代碼。
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect([A2:B2], Target) Is Nothing Then Exit Sub ThisWorkbook.RefreshAll End Sub
由于用到了 VBA 代碼，所以我們必須將文件保存為 xlsm 格式，否則無法使用。
這下，每次更改查詢表中的年月，日歷也會自動刷新啦。
4、總結(jié)一下
本文主要介紹了日歷的 PQ 做法，涉及以下日期 M 函數(shù)：
? 利用#date (年，月，日) 構(gòu)造一個日期；
? Date.EndOfMonth (日期)，可以返回日期當(dāng)月的最后一天的日期；
? Date.Day 可以獲取日期的日；
? Date.DayOfWeekName 可以獲取日期的星期數(shù)；
? Date.WeekOfMonth 可以獲取日期當(dāng)月對應(yīng)的周數(shù)。
還有涉及轉(zhuǎn)表（Table.FromColumns），以及表透視（Table.Pivot）的函數(shù)。
綜合來講，PQ 做法跟數(shù)據(jù)透視表制作日歷表，思維上比較相似。
數(shù)據(jù)透視表做法是通過日期函數(shù)獲取月份，天數(shù)，星期數(shù)，周數(shù)作為數(shù)據(jù)源，然后通過創(chuàng)建透視表達(dá)到制作日歷表的方式。
PQ 做法也是比較類似，但是相比于數(shù)據(jù)透視表方法要稍微復(fù)雜些。
本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：小爽
廣告聲明：文內(nèi)含有的對外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。