原文標(biāo)題：《這么牛 X 的漂亮動(dòng)態(tài)日歷，是怎么用 Excel 做出來(lái)的？（不是透視表）》
大家好，我是在研究日歷做法的小爽~
不知不覺(jué)，2023 年已經(jīng)過(guò)去幾個(gè)月啦~
之前我們介紹過(guò)，利用數(shù)據(jù)透視表制作日歷。
也介紹過(guò)利用函數(shù)制作日歷。
不過(guò)，有個(gè)小伙伴問(wèn)，能不能用 PQ 制作日歷？
我突然想到 PQ 法做日歷，好像沒(méi)給大家介紹過(guò)。
PowerQuery (PQ) 里面也有很多日期類(lèi)函數(shù)，也可以制作日歷。（只不過(guò)難度會(huì)比數(shù)據(jù)透視表大一點(diǎn)點(diǎn)，而且還會(huì)涉及幾個(gè) M 函數(shù)。）
既然小伙伴們想學(xué)，那今天小爽將會(huì)帶大家一步步編寫(xiě) M 函數(shù)公式，來(lái)制作這個(gè)日歷。
1、構(gòu)造數(shù)據(jù)
在制作之前，我們先構(gòu)造一個(gè)查詢(xún)表，月的單元格，可以事先利用數(shù)據(jù)驗(yàn)證設(shè)置一個(gè)下拉列表。
具體步驟：
? 將查詢(xún)表導(dǎo)入到 PQ 編輯器中。選中 A1:B2 單元格區(qū)域，在] 選項(xiàng)卡下，單擊，進(jìn)入 PQ 編輯器中。
單擊 fx 可以新增一個(gè)公式步驟。（后續(xù)新增步驟都是點(diǎn)這里哦~）
鼠標(biāo)移動(dòng)到需更改的步驟上，按右鍵，單擊即可修改步驟名稱(chēng)。（后續(xù)重命名步驟都是點(diǎn)這里哦~）
PS：命名好步驟名稱(chēng)有助于提高公式的可讀性。
? 新增步驟，獲取查詢(xún)表中年和月的第一天，步驟名稱(chēng)命名為「月份第一天」。
=#date(源[年]{0},源[月]{0},1)
小 Tips：
= #date (年，月，日) 主要是用來(lái)構(gòu)造一個(gè)日期。
源 [年]{0} 獲取表中的年。
源 [月]{0} 獲取表中的月。
? 新建步驟，獲取查詢(xún)表中年月的最后一天。步驟名稱(chēng)命名為「月份最后一天」。
=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)寫(xiě)為 {1..9}，如下圖：
由于日期的本質(zhì)是個(gè)數(shù)值，所以我們可以先將日期利用 Number.From 先轉(zhuǎn)為數(shù)值，然后再進(jìn)行擴(kuò)展。最后利用 Date.From 轉(zhuǎn)為日期即可。
{Number.From(月份第一天)..Number.From(月份最后一天)}
現(xiàn)在，一整個(gè)月的日期我們都做出來(lái)了。
觀(guān)察日歷表，可以發(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ù)弄出來(lái)了。
2、轉(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] 列，在選項(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)然，如果你想要的日期是 從星期日開(kāi)始的，也可以通過(guò)改變第二參數(shù)的順序來(lái)實(shí)現(xiàn)。
? 最后一步就是將我們不需要的 Column1 列，也就是顯示月周數(shù)的列，進(jìn)行刪除即可。
選中 Column1 列，按鼠標(biāo)右鍵-刪除。
現(xiàn)在，我們的日期就制作完成啦~
? 最后將 PQ 做好的日歷表加載到工作表中，就搞定了!
依次點(diǎn)擊選項(xiàng)卡-，選擇「現(xiàn)有工作表」并指定單元格位置。
3、自動(dòng)更新
由于 PQ 每一次更改查詢(xún)表的年月，都需要刷新一次，很麻煩。
所以，我們可以加個(gè)工作表事件，當(dāng) A2 和 A3 單元格發(fā)生值改變的時(shí)候，將表格全部進(jìn)行更新。
