之前跟大家分享了《Excel簡(jiǎn)易日歷表/萬(wàn)年歷，只需簡(jiǎn)單幾步，輕松搞定！》，今天就再跟大家分享一下農(nóng)歷公歷同步顯示日歷表/萬(wàn)年歷制作方法，同時(shí)模板中包含一份陽(yáng)歷、農(nóng)歷的對(duì)照表（農(nóng)歷1900年-2100年），通過(guò)這個(gè)對(duì)照表，你只需知道陽(yáng)歷日期，就可以快速準(zhǔn)確的找到對(duì)應(yīng)的農(nóng)歷日期。下面先看農(nóng)歷公歷同步顯示日歷表/萬(wàn)年歷效果：

微軟Office LTSC 2021專(zhuān)業(yè)增強(qiáng)版 簡(jiǎn)體中文批量許可版 2024年09月更新

• 類(lèi)型：辦公軟件
• 大?。?/span>2.2GB
• 語(yǔ)言：簡(jiǎn)體中文
• 時(shí)間：2024-09-12

查看詳情

制作日歷表/萬(wàn)年歷必備知識(shí)點(diǎn)：

如果我們想在Excel中制作一個(gè)日歷表/萬(wàn)年歷的話(huà)，我們必須要知道一個(gè)規(guī)律：

某個(gè)日期減去這個(gè)日期對(duì)應(yīng)的星期數(shù)再加上1，就一定會(huì)得到這個(gè)日期所在周的周一的日期

如下圖所示，我們用日期2024-3-6減去它對(duì)應(yīng)的星期數(shù)3再加上1，結(jié)果為2024-3-4就是3-6號(hào)所在周的周一。

制作步驟：

第一步：制作一個(gè)萬(wàn)年歷表頭和準(zhǔn)備對(duì)應(yīng)的陽(yáng)歷、農(nóng)歷的對(duì)照表

如下圖所示，我們制作一個(gè)萬(wàn)年歷表頭，包含周一到周天，有年份和月份選擇（年份和月份制作成下拉菜單，可以把數(shù)據(jù)隱藏）；需要有一份陽(yáng)歷、農(nóng)歷的對(duì)照表，方便通過(guò)陽(yáng)歷獲取對(duì)應(yīng)的農(nóng)歷日期。

第二步：制作日歷表陽(yáng)歷/農(nóng)歷日期

1、先制作第一行第一個(gè)單元格的陽(yáng)歷日期

在目標(biāo)單元格中輸入公式：

=DATE(B1,E1,1)-WEEKDAY(DATE(B1,E1,1),2)+1

然后點(diǎn)擊回車(chē)，把數(shù)據(jù)格式改成日期格式

解讀：

①公式中DATE(B1,E1,1)就是構(gòu)建指定月份的1號(hào)日期；WEEKDAY(DATE(B1,E1,1),2)就是獲取對(duì)應(yīng)日期的星期數(shù)。

②我們雖然不確定指定月份的1號(hào)日期是否屬于周一，但是我們可以使用指定月份的1號(hào)日期減去對(duì)應(yīng)日期的星期數(shù)再加上1，就是

=DATE(B1,E1,1)-WEEKDAY(DATE(B1,E1,1),2)+1

這樣我們就獲得了指定月份的1號(hào)日期所在周周一的日期。

2、第一行第一個(gè)單元格后面的陽(yáng)歷日期

在目標(biāo)單元格中輸入公式：

=A3+1

然后先有填充，把數(shù)據(jù)格式改成日期格式

3、第一行農(nóng)歷日期根據(jù)第一行陽(yáng)歷獲取對(duì)應(yīng)的農(nóng)歷日期

①因?yàn)檗r(nóng)歷顯示單元格的寬度有限，我們需要先把對(duì)照表中的農(nóng)歷縮寫(xiě)成顯示月和日的格式

在目標(biāo)單元格中輸入公式：

=TEXTAFTER(B2,"年")

然后點(diǎn)擊回車(chē)下拉填充

解讀：

通過(guò)TEXTAFTER函數(shù)獲取B2單元格中“年”后面的數(shù)據(jù)。

②第一行農(nóng)歷根據(jù)陽(yáng)歷獲取對(duì)應(yīng)農(nóng)歷日期

在目標(biāo)單元格中輸入公式：

=FILTER('對(duì)照表（公歷農(nóng)歷）'!$E:$E,'對(duì)照表（公歷農(nóng)歷）'!$C:$C=A3)

然后點(diǎn)擊回車(chē)，向右填充數(shù)據(jù)即可

解讀：

使用FILTER函數(shù)根據(jù)對(duì)照表數(shù)據(jù)跨表查詢(xún)陽(yáng)歷對(duì)應(yīng)的農(nóng)歷數(shù)據(jù)。

4、制作其它行的陽(yáng)歷和農(nóng)歷日期

①生成第二行陽(yáng)歷日期

在A5單元格中輸入公式：

=G3+1

然后點(diǎn)擊回車(chē)

接著，在B5單元格中輸入公式：

=A5+1

然后先后填充數(shù)據(jù)即可

②然后復(fù)制數(shù)據(jù)填充下面的位置即可

生成12行7列的日歷表，如下圖所示

第三步：把日歷中的日期改成只顯示幾號(hào)，并設(shè)置陽(yáng)歷和農(nóng)歷樣式

①修改日期顯示類(lèi)型

首先全選日期數(shù)據(jù)→然后點(diǎn)擊組合鍵【Ctrl+1】調(diào)出“單元格格式”窗口→在自定義選項(xiàng)下把【類(lèi)型】改成d→最后點(diǎn)擊確定即可

②設(shè)置陽(yáng)歷和農(nóng)歷樣式

方法：

先把陽(yáng)歷日期字體加大加粗（14號(hào)字體），農(nóng)歷日期字體縮小到10號(hào)字體→然后同時(shí)選擇第3行和第4行，點(diǎn)擊組合鍵【Ctrl+1】調(diào)出“單元格格式”窗口→在【邊框】設(shè)置外邊框和中間左右邊框，點(diǎn)擊確定→最后點(diǎn)擊格式刷為其它行設(shè)置相同的格式即可

第四步：隱藏不屬于指定月份的日期

我們可以使用條件格式，將不屬于指定月份的日期，通過(guò)格式中的自定義格式，在類(lèi)型中輸入“;;;”，從而達(dá)到隱藏?cái)?shù)據(jù)的效果

1、設(shè)置前2行

首先選擇日期數(shù)據(jù)區(qū)域→打開(kāi)【條件格式】-【新建規(guī)則】調(diào)出“新建格式規(guī)則”窗口→選擇【使用公式確定要設(shè)置格式的單元格】將公式設(shè)置為：=MONTH(A$3)<>$E$1→然后點(diǎn)擊右下角的【格式】找到【自定義】將【類(lèi)型】設(shè)置為“;;;”即可。

2、設(shè)置后兩行方法一樣，只是把公式分別改成

公式：=MONTH(A$11)<>$E$1

公式：=MONTH(A$13)<>$E$1

3、最后可以通過(guò)【視圖】-去掉【網(wǎng)格線(xiàn)】

推薦閱讀：

• Excel打造炫酷員工考勤表! 全自動(dòng)考勤表?yè)?jù)實(shí)際休息日自動(dòng)填充顏色
• Excel透視表1月到12月不能排序怎么辦? 數(shù)據(jù)透視表日期無(wú)法排序解決方法

最新評(píng)論