大家好，我是在研究日歷的小爽~

今天做了個升級版本的，如下圖：?通過控件切換月份，日歷中的日期，農(nóng)歷，以及休班狀態(tài)自動更新！?

?是不是特別炫酷？??接下來，我就來揭開這份日歷表的層層面紗。

關(guān)注后回復(fù)：?虎虎?，就可以獲得這份日歷表的源文件啦。

（PS.這份日歷是 2022 年的，相信看完這篇文章的你也可以做出 2023 年日歷

制作外觀


首先我們要制作日歷表的外觀，在日歷表中，每個格子是三行兩列的，行格為 6，列格為 7。

?E3 單元格為 2022，對應(yīng)數(shù)字格式為 0 年，F(xiàn)4 單元格為 1，對應(yīng)數(shù)字格式為 0 月。?

?事先準(zhǔn)備好參數(shù)表。（關(guān)注后回復(fù)：?虎虎?，就可以獲取啦~）?

▋插入控件

在【開發(fā)工具】選項(xiàng)卡下，點(diǎn)擊【插入】-【數(shù)值調(diào)節(jié)鈕】。?

?右鍵控件，選擇【設(shè)置控件格式】。?

?設(shè)置對象窗口中，最小值為 1，最大值為 12，單元格鏈接為 F4。（月份為 1-12，所以最小值為 1，最大值為 12）

?PS. 如果沒有【開發(fā)工具】選項(xiàng)卡，在【文件】-【選項(xiàng)】-【自定義功能區(qū)】中調(diào)出。

基本的外觀制作好后，下面就是編寫公式啦。?

編寫公式

如下圖，在 E7 單元格中輸入如下公式：

• =DATE($E$3,$F$4,1)-WEEKDAY(DATE($E$3,$F$4,1),2)+INT(COLUMN(A:A)/2)+INT((ROW(1:1)-1)/3)*7?
  

?如下圖，在 F7 單元格中輸入如下公式：

• =IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,參數(shù)表!$A:$D,4,0),""),"")??

?如下圖，在 E8 單元格中輸入如下公式：

• =IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,參數(shù)表!$A:$D,3,0),""),"")? ?

?最后填充公式就可以啦~?如動圖所示，選擇 E7:F9，向右向下填充公式。?

小 tips：?E7 單元格函數(shù)公式：

• =DATE($E$3,$F$4,1)-WEEKDAY(DATE($E$3,$F$4,1),2)+INT(COLUMN(A:A)/2)+INT((ROW(1:1)-1)/3)*7? ?

其中前面一半公式是求：當(dāng)前月 1 號上一個星期日的日期。

• =DATE($E$3,$F$4,1)-WEEKDAY(DATE($E$3,$F$4,1),2)
  

后半段則是求：當(dāng)前月 1 號上一個星期日的，也就是前半段公式得出的日期。?需要加上多少間隔數(shù)，才能得出當(dāng)前單元格的日期。

• =INT(COLUMN(A:A)/2)+INT((ROW(1:1)-1)/3)*7

?比如說，2022 年 1 月 1 號，就是當(dāng)前月 1 號前一個周日的日期（2021 年 12 月 26 號）+6。?

?F7 單元格函數(shù)公式：

• =IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,參數(shù)表!$A:$D,4,0),""),"")??

如下圖，如果是左邊的日期是本月的日期，則使用 Vlookup 函數(shù)查找對應(yīng)日期的對應(yīng)休班狀態(tài)，否則顯示為空。?

?

?E8 單元格函數(shù)公式：

• =IF(MONTH(E7)=$F$4,IFERROR(VLOOKUP(E7,參數(shù)表!$A:$D,3,0),""),"")

如果是上邊的日期是本月的日期，則使用 vlookup 函數(shù)查找對應(yīng)日期的對應(yīng)日，否則顯示為空。???公式設(shè)置完后，下一步就是要「裝飾」日歷表啦~?

設(shè)置格式

▋設(shè)置休班狀態(tài)格式

??讓 0 不顯示

填充完公式后，我們發(fā)現(xiàn)狀態(tài)單元格中有很多 0，影響表格的美觀程度，所以要讓這些 0 都不顯示。

?這要怎么做呢？我們可以使用自定義數(shù)字格式。?

按住【Ctrl】鍵，選中需要設(shè)置的單元格區(qū)域，再按住快捷鍵【Ctrl+1】彈出【設(shè)置單元格格式】對話框。

然后在類型中輸入;;;@，單擊【確定】按鈕，此時除了文本，所有的 0 都顯示為空白了。?

?小 Tips：在數(shù)字格式中，正數(shù)格式;負(fù)數(shù)格式;零值格式;文本格式。

;;;@ 其實(shí)就是就是讓數(shù)值都不顯示，文本顯示本身，@ 符號代表文本本身的字符。

???設(shè)置休班顏色

將休字顯示為紅色，將班字顯示為藍(lán)色，這里我們可以使用條件格式。

在【開始】選項(xiàng)卡，單擊【條件格式】，選擇新建規(guī)則。

?注意：活動單元格是 F7，就是下圖中左邊紅框的位置（活動單元格指 Excel 表格中處于激活狀態(tài)的單元格?？梢允钦诰庉嫷?，也可以是選取的范圍中的）。?

?選擇【使用公式確定要設(shè)置格式的單元格】，輸入為=F7="休"，格式設(shè)置為紅色加粗字體，確定。

此時單元格中是「休」字的都顯示為紅色加粗字體。

??同理，輸入為=F7="班"，格式設(shè)置為藍(lán)色加粗字體，確定。

此時單元格中是「班」字的都顯示為藍(lán)色加粗字體。?

?▋不在當(dāng)前月份的日期不顯示

如下圖，我們可以看到，不在同個月份的日期依舊顯示，這顯然并不是我們想要的。?

?按住【Ctrl】鍵，選中指定區(qū)域，點(diǎn)擊【條件格式】-【新建規(guī)則】。?

?選擇【使用公式確定要設(shè)置格式的單元格】，輸入：

• =month(E7)<>$F$4
  

格式中字體設(shè)置為白色，單擊【確定】按鈕。?

?此時，我們所想要的效果就完成了，要想更美觀的話，我們可以把農(nóng)歷單元格（如 Q8:R8 等）進(jìn)行合并單元格操作。?

延伸拓展

你以為這就結(jié)束啦？當(dāng)然還沒有。?

?由于不同人的休假，還有調(diào)班時間不同，我們可以在參數(shù)表中對應(yīng)位置選擇好對應(yīng)狀態(tài)，日歷表中就可以自動更新。?

?對于特殊日子，我們也可以在參數(shù)表中輸入，比如某某日是你的生日，日歷表中也會自動更新。?

?我們也可以在日歷下面添加備注，如下圖所示，然后就可以直接進(jìn)行打印啦。?

?案例中的日歷中使用的都是主題色，所以我們也可以通過更改主題色，變身成為另外的樣子！?

寫在最后

最后，總結(jié)一下：?

本文介紹了日歷表更高級的做法，具備農(nóng)歷加休班狀態(tài)，公式自動更新的日歷。

???制作日歷外觀

確定好日歷所需要的行列數(shù)，根據(jù)自己的需求制作好外觀。

???設(shè)置公式

日期數(shù)的確定：利用當(dāng)前月的 1 號前一個星期日數(shù)來定位日期首個單元格。

休班狀態(tài)的查找：使用 vlookup 函數(shù)進(jìn)行查找。

農(nóng)歷的查找：使用 vlookup 函數(shù)進(jìn)行查找。

?? 設(shè)置格式

針對休班狀態(tài)出現(xiàn)的 0 值，我們可以使用數(shù)字格式將 0 值進(jìn)行隱藏，文中我們使用的;;;@

利用條件格式，將不屬于當(dāng)月的日期進(jìn)行隱藏。?

??簡單的進(jìn)行延伸拓展

由于本文的日歷表是用公式制作的，所以比較靈活，我們可以根據(jù)自己的需求，再 DIY 制作屬于自己的日歷表。

當(dāng)然啦，日歷的玩法不局限這些，大家可以開開自己的腦洞，做出更多好玩的日歷！！

還想掌握更多的 Excel 小技巧，搞定工作和生活中的難題？

正好，我們家的《秋葉 Excel 3 天集訓(xùn)營》專為職場人準(zhǔn)備，

全部基于職場真實(shí)表格案例設(shè)計(jì)，還有很多超實(shí)用 Excel 技巧教學(xué)。

每天學(xué)習(xí)大概?30 分鐘，從日常的功能出發(fā)，全程演示，一課一練，夯實(shí)進(jìn)階每一步。

秋葉 Excel 3 天集訓(xùn)營

原價?99 元??

現(xiàn)在?只需 1?元

?每天學(xué)習(xí)?30 分鐘

你也有可能成為 Excel 高手！

現(xiàn)在就掃碼報名吧！

??????

??

*廣告