這么牛X的漂亮動(dòng)態(tài)日歷，是怎么用Excel做出來的？（不是透視表）
??文末獲取配套案例文件下載方式~
大家好，我是在研究日歷做法的小爽~
不知不覺，2023 年已經(jīng)過去幾個(gè)月啦~
之前我們介紹過，利用數(shù)據(jù)透視表制作日歷。
也介紹過利用函數(shù)制作日歷。
不過，有個(gè)小伙伴問，能不能用?PQ 制作日歷？
我突然想到 PQ 法做日歷，好像沒給大家介紹過。
PowerQuery?( PQ )?里面也有很多日期類函數(shù)，也可以制作日歷。（只不過難度會(huì)比數(shù)據(jù)透視表大一點(diǎn)點(diǎn)，而且還會(huì)涉及幾個(gè) M 函數(shù)。）
既然小伙伴們想學(xué)，那今天小爽將會(huì)帶大家一步步編寫 M 函數(shù)公式，來制作這個(gè)日歷。
構(gòu)造數(shù)據(jù)
在制作之前，我們先構(gòu)造一個(gè)查詢表，月的單元格，可以事先利用數(shù)據(jù)驗(yàn)證設(shè)置一個(gè)下拉列表。
具體步驟：
? 將查詢表導(dǎo)入到 PQ 編輯器中。選中 A1:B2 單元格區(qū)域，在【數(shù)據(jù)】]選項(xiàng)卡下，單擊【來自表格/區(qū)域】，進(jìn)入 PQ 編輯器中。
單擊 fx 可以新增一個(gè)公式步驟。（后續(xù)新增步驟都是點(diǎn)這里哦~）
鼠標(biāo)移動(dòng)到需更改的步驟上，按右鍵，單擊【重命名】即可修改步驟名稱。（后續(xù)重命名步驟都是點(diǎn)這里哦~）
PS：命名好步驟名稱有助于提高公式的可讀性。
? 新增步驟，獲取查詢表中年和月的第一天，步驟名稱命名為「月份第一天」。
= #date(源[年]{0},源[月]{0},1)
小 Tips：
= #date(年,月,日) 主要是用來構(gòu)造一個(gè)日期。
源[年]{0} 獲取表中的年。
源[月]{0} 獲取表中的月。
? 新建步驟，獲取查詢表中年月的最后一天。步驟名稱命名為「月份最后一天」。
=?Date.EndOfMonth(月份第一天)
Date.EndOfMonth 函數(shù)可以返回日期當(dāng)月的最后一天的日期。
? 新建步驟，將第一天和最后一天日期進(jìn)行擴(kuò)展。步驟命名為「月日期」。
= List.Transform({Number.From(月份第一天)..Number.From(月份最后一天)},Date.From)
簡(jiǎn)單解釋一下：在 M 函數(shù)表達(dá)式中，列表的表示方式是用{中括號(hào)}，如下圖，{1,2}，就是 1，2 形成的列表。
如果要表示 1 到 9 的列表，就是{1,2,3,4,5,6,7,8,9}，可簡(jiǎn)寫為{1..9}，如下圖：
由于日期的本質(zhì)是個(gè)數(shù)值，所以我們可以先將日期利用 Number.From 先轉(zhuǎn)為數(shù)值，然后再進(jìn)行擴(kuò)展。最后利用 Date.From 轉(zhuǎn)為日期即可。
{Number.From(月份第一天)..Number.From(月份最后一天)}
現(xiàn)在，一整個(gè)月的日期我們都做出來了。
觀察日歷表，可以發(fā)現(xiàn)，我們還需要得到日期中的日，星期數(shù)，以及每月周數(shù)?的相關(guān)數(shù)據(jù)。
所以我們下面三個(gè)步驟就是為了獲取這三塊的內(nèi)容。
? 新建步驟，獲取日期的天數(shù)。步驟命名為「獲取日」。
= List.Transform((月日期),Date.Day)
Date.Day 可以獲取日期中的日。
? 新建步驟，獲取星期數(shù)。步驟命名為「獲取星期數(shù)」。
= List.Transform((月日期),Date.DayOfWeekName)
Date.DayOfWeekName 可以獲得日期的星期數(shù)。
? 新建步驟，獲取日期對(duì)應(yīng)的當(dāng)前月的周數(shù)。步驟命名為「周數(shù)」。
= List.Transform((月日期),Date.WeekOfMonth)
Date.WeekOfMonth 可以獲得日期對(duì)應(yīng)的當(dāng)月的周數(shù)。
到這里，我們已經(jīng)把所需要的三個(gè)數(shù)據(jù)弄出來了。
轉(zhuǎn)表透視
由于日歷是個(gè)表，所以我們還需要將數(shù)據(jù)進(jìn)行整合合并在一起形成一個(gè)表。
? 新建步驟，拼接成表。步驟命名為「數(shù)據(jù)」。
= Table.FromColumns({周數(shù),獲取星期數(shù),獲取日})
Table.FromColumns 可以按列轉(zhuǎn)換為表。
? 日歷表是個(gè)二維數(shù)據(jù)，所以我們還需要將星期數(shù)(Column2 列)進(jìn)行透視處理。
選中[Colum2]列，在【轉(zhuǎn)換】選項(xiàng)卡下，單擊【透視列】，出現(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ù)的順序來實(shí)現(xiàn)。
? 最后一步就是將我們不需要的 Column1 列，也就是顯示月周數(shù)的列，進(jìn)行刪除即可。
選中 Column1 列，按鼠標(biāo)右鍵-刪除。
現(xiàn)在，我們的日期就制作完成啦~
? 最后將 PQ 做好的日歷表加載到工作表中，就搞定了 !
依次點(diǎn)擊【文件】選項(xiàng)卡-【關(guān)閉并上載至】，選擇「現(xiàn)有工作表」并指定單元格位置。
自動(dòng)更新
由于 PQ 每一次更改查詢表的年月，都需要刷新一次，很麻煩。
所以，我們可以加個(gè)工作表事件，當(dāng) A2 和 A3 單元格發(fā)生值改變的時(shí)候，將表格全部進(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?SubThisWorkbook.RefreshAllEnd Sub
由于用到了 VBA 代碼，所以我們必須將文件保存為 xlsm 格式，否則無法使用。
這下，每次更改查詢表中的年月，日歷也會(huì)自動(dòng)刷新啦。
總結(jié)一下
本文主要介紹了日歷的 PQ 做法，涉及以下日期 M 函數(shù)：
? 利用#date(年,月,日) 構(gòu)造一個(gè)日期；
? Date.EndOfMonth(日期)，可以返回日期當(dāng)月的最后一天的日期；
? Date.Day 可以獲取日期的日；
? Date.DayOfWeekName 可以獲取日期的星期數(shù)；
? Date.WeekOfMonth 可以獲取日期當(dāng)月對(duì)應(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ù)雜些。
如果你想學(xué)習(xí)更高效的 Excel 技巧，不如來參加我們的秋葉 Excel 3 天集訓(xùn)營(yíng)~
秋葉 Excel 3 天集訓(xùn)營(yíng)
僅需 1 元！
3 天提升你的 Excel 能力
趕緊掃碼搶課吧
??????
如何下載案例文件？
案例文件已經(jīng)給大家準(zhǔn)備好了，微信關(guān)注：秋葉 Excel 公眾號(hào)。
回復(fù)【日歷】
即可免費(fèi)下載！
