用Excel制作萬年歷的3種方法

摘 要：萬年歷可用高級(jí)語言編程制作，在沒有現(xiàn)成平臺(tái)的情況下，編程過程較繁瑣。利用Excel提供的函數(shù)、數(shù)據(jù)公式及VBA開發(fā)功能制作萬年歷，闡述在Excel平臺(tái)上無需編程實(shí)現(xiàn)萬年歷制作方法。用Excel制作萬年歷比用其它高級(jí)語言編程方法簡單高效，體現(xiàn)了Excel強(qiáng)大的功能以及多途徑解決問題的實(shí)用性。

關(guān)鍵詞：Excel日期函數(shù);數(shù)組公式;VBA開發(fā);萬年歷制作

DOI：10. 11907/rjdk. 182689 開放科學(xué)（資源服務(wù)）標(biāo)識(shí)碼（OSID）：

中圖分類號(hào)：TP319文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1672-7800（2019）007-0154-05

Three Methods of Making Calendars With Excel

WANG Yi-min

（National Demonstration Center for Experimental Computer Education， Fudan University， Shanghai 201203，China）

Abstract：The calendar can be programmed in high-level language. Without an existing platform， the programming process is relative tedious. This paper introduces how to make a calendar without programming on the existing Excel platform by making use of functions provided by Excel， data formula and VBA development environment function to make a calendar， and compares it with the programming method. The three methods are making a calendar by using date function， making a calendar by using array formula and making a calendar by using VBA development tool. The first two methods do not need programming， only use Excel function nested combination and function to complete the task， the third method is programmed in Excel's own VBA development environment. The results show that it is simpler and more efficient to make calendars with Excel than other high-level languages. Making a calendar on Excel platform is a tribute to Excel's powerful function， practicability and multiple ways to solve problems.

Key Words： Excel date function; array formula; making calendar

作者簡介：王毅敏（1963-），男，復(fù)旦大學(xué)國家級(jí)實(shí)驗(yàn)教學(xué)示范中心高級(jí)工程師，研究方向?yàn)楣芾硇畔⑾到y(tǒng)、計(jì)算機(jī)教學(xué)。

0 引言

日常工作學(xué)習(xí)生活中經(jīng)常會(huì)處理一些數(shù)據(jù)。Microsoft Office是一套由微軟公司開發(fā)的辦公軟件套裝，Excel是其中一款電子表格處理軟件，其界面友好，功能強(qiáng)大實(shí)用，能很好解決人們?nèi)粘９ぷ鲗W(xué)習(xí)生活中遇到的數(shù)據(jù)處理問題，在各行各業(yè)廣泛應(yīng)用。

在教學(xué)領(lǐng)域，可用Excel圖表功能制作高中數(shù)學(xué)函數(shù)動(dòng)態(tài)圖形[1]，用功能或函數(shù)進(jìn)行數(shù)值計(jì)算[2]，對學(xué)習(xí)成績進(jìn)行統(tǒng)計(jì)分析[3-8]，進(jìn)行實(shí)驗(yàn)?zāi)M[9]等。

在人事管理中，可用Excel管理所有職工資料，處理公司人事調(diào)動(dòng)和績效考核等重要事務(wù)。

在生產(chǎn)領(lǐng)域，管理員需要時(shí)刻明確產(chǎn)品的生產(chǎn)總量和需要生產(chǎn)量，數(shù)量要精確。了解生產(chǎn)整體進(jìn)度，以便隨時(shí)調(diào)整計(jì)劃并做好人員配備工作[10-12]。

財(cái)務(wù)管理中，Excel被公認(rèn)為是一個(gè)通用的財(cái)務(wù)軟件，凝聚了許多開發(fā)者的智慧，以及廣大財(cái)務(wù)人員和投資分析人員的工作經(jīng)驗(yàn)，具有強(qiáng)大而靈活的財(cái)務(wù)數(shù)據(jù)管理功能[13-15]，Excel還為用戶提供VBA功能開發(fā)接口[16]。

上述應(yīng)用都離不開Excel所特有的平臺(tái)及強(qiáng)大的函數(shù)、圖表、內(nèi)置可加載的應(yīng)用程序、VBA開發(fā)工具等功能。Excel應(yīng)用平臺(tái)由一些單元格組成，擅長各類表格的數(shù)據(jù)處理，利用它來制作萬年歷也非常合適。因?yàn)橛鞋F(xiàn)成的平臺(tái)，又有多種函數(shù)及功能，不需要編寫很長的代碼，只需利用函數(shù)及功能就可完成。

下面闡述3種萬年歷制作方法。

1 利用日期函數(shù)制作萬年歷

首先，用函數(shù)column（）及row（）生成數(shù)據(jù)矩陣，作為月份日期。接著要解決3個(gè)問題：①確定本月第一天的星期數(shù);②計(jì)算本月最后一天的日期;③隱藏不是本月的日期。

任選Excel文件中的一個(gè)單元格，如在A21單元格中輸入公式：=（ROW（）-4）*7+COLUMN（），如表1所示，第3行開始的數(shù)值為本公式填充后的運(yùn)行結(jié)果。

表1 用row和column函數(shù)填充后的結(jié)果

1.1 確定本月第一天的星期數(shù)

在表1的基礎(chǔ)上生成某月日歷，關(guān)鍵是確定本月第一天日期對應(yīng)的星期數(shù)以及本月最后一天的日期才能確保結(jié)果正確，以下是具體的實(shí)現(xiàn)過程及注意事項(xiàng)：

本月第一天是某月1日但不知道是星期幾，這時(shí)可用WEEKDAY函數(shù)計(jì)算某年某月第一天的星期數(shù)，語法格式為WEEKDAY （date，type），用type 為1的選項(xiàng)返回后的結(jié)果是：星期日為1，星期一為2，依此類推，星期六為7。

WEEKDAY函數(shù)計(jì)算本月第一天的星期數(shù)，把它看成一個(gè)修正值，通過減去單元格中的數(shù)值調(diào)整本月第一天對應(yīng)一個(gè)正確的星期位置。在本例中，這個(gè)修正值是 WEEKDAY（DATE（YEAR（$A$19），MONTH（$A$19），1），1）-1，計(jì)算結(jié)果為4。對現(xiàn)有單元格減去這個(gè)值，正好與星期四對應(yīng)。只要本月對應(yīng)正確，其它月份就都是正確的。DATE（YEAR（$A$19），MONTH（$A$19），1）是構(gòu)建的日期，即2018年2月1日，星期四，WEEKDAY（DATE（YEAR（$A$19），MONTH（$A$19），1），1）返回值是5。

要注意的是，A19單元格存放的是年月，必須絕對引用，防止填充時(shí)移位。其與利用數(shù)組公式制作萬年歷的方法不一樣，因?yàn)樵摲椒ú恍枰M(jìn)行填充操作。

需要說明的是，在填充過程中，如出現(xiàn)“#####”錯(cuò)誤信息，是因?yàn)槿掌跀?shù)為負(fù)所以出錯(cuò)，將單元格格式設(shè)為常規(guī)就可恢復(fù)正常。另外在這兒出現(xiàn)了負(fù)值可暫不處理，在后面將它隱去。運(yùn)行結(jié)果如表2所示。

表2 用修正值調(diào)整后的結(jié)果

至此，表中顯示2018年2月1日是星期四，改變A19單元格年月進(jìn)行測試，結(jié)果應(yīng)該是正確的。但還有兩個(gè)問題：①表中出現(xiàn)了小于等于0的數(shù)值;②大于本月最后一天的數(shù)值。

1.2 計(jì)算本月最后一天的日期

眾所周知，每個(gè)月的最后一天不是一個(gè)固定值，但是，下個(gè)月的第一天是明確的，可以用下個(gè)月的第一天減去1的方法確定本月的最后一天。公式為? =DAY（DATE（YEAR（$A$19），MONTH（$A$19）+1，1）-1），結(jié)果為28。$A$19單元格是2018年2月，最后一天是28。其中DATE（YEAR（$A$19），MONTH（$A$19）+1，1）返回結(jié)果為“2018年3月1日”，為下個(gè)月第一天。2018年3月1日減去1，則為2018年2月28日，嵌套調(diào)用DAY函數(shù)返回結(jié)果為28。

1.3 隱藏不是本月的日期

在表2中，出現(xiàn)了小于等于0的數(shù)值，也出現(xiàn)了大于本月最后一天的數(shù)值，這都是不符合要求的，需要隱去?？衫胕f函數(shù)設(shè)置：如果數(shù)值小于等于0或數(shù)值大于本月最后一天，就設(shè)置為空格（隱去日期），否則正常顯示，具體公式如下：

填充后的運(yùn)行結(jié)果如表3所示。

表3 隱去不是本月數(shù)值的最后結(jié)果

1.4 實(shí)現(xiàn)過程說明

IF函數(shù)看上去很復(fù)雜繁瑣，現(xiàn)作進(jìn)一步說明。邏輯判斷部分是一個(gè)OR函數(shù)的嵌套，第一部分判斷本月是否為負(fù)數(shù)，第二部分判斷是否比本月最后一天的日期大。兩部分只要有一個(gè)為true，則執(zhí)行IF函數(shù)的true部分，對所在單元格設(shè)置為""，即單元格置空，不顯示任何信息。否則執(zhí)行IF函數(shù)的false部分，即正常計(jì)算出本月日期。

每個(gè)單元格的計(jì)算式是一樣的，但輸出結(jié)果不一樣，原因是引用了row（）及column（），它們分別返回本單元格的行和列，所以才有不同的計(jì)算結(jié)果。

2 利用數(shù)組公式制作萬年歷

在Excel中，日期用正整數(shù)表示，某年某月某日是一個(gè)正整數(shù)，加上1為下一個(gè)日期，依此類推。據(jù)此，可以用數(shù)組公式計(jì)算，順勢表示出日期。

具體過程如下：①通過數(shù)組公式生成數(shù)據(jù)矩陣;②數(shù)組公式與單元格中的日期數(shù)據(jù)求和，生成連續(xù)日期;③確定本月第一天星期數(shù);④隱去不是本月的日期（指上月日期與下月日期）;⑤設(shè)置單元格格式，僅顯示日期。

2.1 通過數(shù)組公式生成數(shù)據(jù)矩陣

在A31單元格中輸入公式={0;1;2;3;4;5}*7+{0，1，2，3，4，5，6}，選中A31∶G36區(qū)域，通過ctrl+shift+enter進(jìn)行數(shù)組公式計(jì)算。在1-6行中生成0-41連續(xù)的數(shù)據(jù)矩陣，一行7個(gè)數(shù)據(jù)，共6行，如表4所示。

表4 用數(shù)組公式計(jì)算結(jié)果

2.2 數(shù)組公式與單元格中的日期數(shù)據(jù)求和，生成連續(xù)日期

用 DATE函數(shù)構(gòu)建本月第一天日期，選中A31∶D36區(qū)域，輸入公式：

表5 加上本月1日后的結(jié)果

2.3 確定本月第一天星期數(shù)

確定本月第一天星期數(shù)前面已經(jīng)詳細(xì)介紹，在此不再贅述。選中A31∶D36區(qū)域，在編輯欄中輸入公式：

表6 修正值調(diào)整后并設(shè)置成日期格式結(jié)果

2.4 隱去不是本月的日期

可以用IF函數(shù)實(shí)現(xiàn)，只需判斷月份是否為本月即可。用MONTH函數(shù)計(jì)算出月份值，判斷是否與本月份相同。如果是本月日期則正常顯示，否則設(shè)置為空值，公式如下 ：

表7 隱去不是本月日期的最后結(jié)果

2.5 設(shè)置單元格格式，僅顯示日期

選中日期區(qū)域，設(shè)置自定義格式為：d，就可得到最后結(jié)果。

以上詳細(xì)地介紹了制作月歷的過程及方法，用同樣方法可實(shí)現(xiàn)12個(gè)月的年歷。最后，添加一個(gè)年份數(shù)值控件，通過按動(dòng)這個(gè)控件按鈕改變年份數(shù)值，系統(tǒng)將自動(dòng)生成年度日歷，也可用數(shù)據(jù)有效性功能實(shí)現(xiàn)年份選項(xiàng)，結(jié)果如圖1所示。

圖1 萬年歷樣式

2.6 實(shí)現(xiàn)過程說明

這里用數(shù)組公式生成數(shù)據(jù)矩陣，配合函數(shù)計(jì)算整個(gè)萬年歷表。

以下公式含意：

公式計(jì)算過程：先計(jì)算第一行，如0*7+{0，1，2，3，4，5，6}，結(jié)果是0，1，2，3，4，5，6，依次存入同行單元格中;換行后再計(jì)算第二行，1*7+{0，1，2，3，4，5，6}，結(jié)果為7，8，9，10，11，12。依此類推完成整個(gè)數(shù)據(jù)矩陣的計(jì)算。

這些數(shù)據(jù)不是按回車鍵就能計(jì)算，必須啟用數(shù)組公式快捷鍵才能算出結(jié)果。計(jì)算過程：先選中需要輸出的區(qū)域，然后在編輯欄中輸入上述公式，啟用ctrl+shift+enter就可輸出結(jié)果。再加上本月1日的日期減去一個(gè)修正值，確保本月第一天的星期數(shù)對應(yīng)正確。然后利用IF函數(shù)判斷是否為本月日期，進(jìn)行隱去或顯示。最后，對顯示數(shù)據(jù)進(jìn)行日期格式設(shè)置，就可制作出一個(gè)月的日歷。

3 利用VBA 開發(fā)工具制作萬年歷

VBA（Visual Basic for Applications）是內(nèi)嵌于Office軟件中的一個(gè)開發(fā)模塊。這個(gè)模塊提供程序自主開發(fā)，語言基礎(chǔ)和VB（Visual Basic）相似，本文采用基于Excel的VBA開發(fā)環(huán)境。

用VBA制作月歷表，實(shí)現(xiàn)依據(jù)與前述類似，但需要編寫VBA程序啟用其功能，實(shí)現(xiàn)過程如下：①利用錄制宏功能生成月歷格式表;②編寫程序填寫日歷數(shù)據(jù);③設(shè)置事件觸發(fā)運(yùn)行日歷生成程序。

3.1 利用錄制宏功能生成月歷格式表

所謂宏，就是一組指令集，通過執(zhí)行類似批處理的一組命令完成某種功能。利用宏可完成很多程序原本并不支持的特殊應(yīng)用，比如完成某種特殊的數(shù)據(jù)計(jì)算，或者文檔的特殊格式設(shè)置及排版等。

啟用開發(fā)工具選項(xiàng)卡，在文件→選項(xiàng)→自定義功能區(qū)中啟用開發(fā)工具選項(xiàng)卡。啟用VBA中的錄制宏功能，在文件→選項(xiàng)→信任中心→信任中心設(shè)置→宏設(shè)置中選中“啟用所有宏”。

錄制宏代碼，進(jìn)入Excel編輯界面，可以看到開發(fā)工具選項(xiàng)卡，然后進(jìn)入VBA開發(fā)平臺(tái)，在代碼組中有“錄制宏”按鈕。按下這個(gè)按鈕，命名這個(gè)宏名稱為“My_Macro”。在sheet1的A2∶G8區(qū)域中進(jìn)行日歷表格式設(shè)置。設(shè)置完成后，按下“停止錄制”按鈕，至此宏錄制成功，自動(dòng)生成VBA代碼。

設(shè)置年月選項(xiàng)，進(jìn)入Excel 編輯界面，選中數(shù)據(jù)選項(xiàng)卡，在數(shù)據(jù)工具組中找到數(shù)據(jù)有效性功能，對C1及D1單元格設(shè)置選項(xiàng)。C1中設(shè)置年份2000-2030，D1中設(shè)置月份1-12。對C1及D1單元格分別設(shè)置自定義格式為：yyyy“年”及m“月”格式，完成后的結(jié)果如表8所示。

表8 宏代碼運(yùn)行結(jié)果

3.2 編寫程序填寫日歷數(shù)據(jù)

下面是本程序說明：

本程序的運(yùn)行結(jié)果是在A3到G8單元格區(qū)域內(nèi)生成日歷表，共6行7列，程序有兩層循環(huán)。

第1行：本程序名稱，可供調(diào)用;第2行：錄制的宏代碼，因?yàn)樘L此處省略;第5-7行：是一條語句，因?yàn)樘L分為3行顯示，語句最后的下劃線為連接符。thismonth變量內(nèi)存放的是經(jīng)過修正調(diào)整后的日期;第8行：if語句判斷thismonth內(nèi)的月份與單元格D1內(nèi)的月份數(shù)是否一致;第9行：if為true，即不是本月日期，則對單元格置空，其中單元格函數(shù)Cells（i + 2， j）中的行標(biāo)為i+2，是因?yàn)槿諝v表從第3行開始填寫，所以必須加上2進(jìn)行調(diào)整;第11行： 填寫本月日期到單元格，設(shè)為日期格式。

3.3 設(shè)置事件觸發(fā)運(yùn)行日歷生成程序

至此為止，主要編程工作完成，后面的工作是要觸發(fā)程序運(yùn)行。根據(jù)用戶使用習(xí)慣，每次對年份或月份選定后，自動(dòng)對月歷日期及格式進(jìn)行更新，因此可利用VBA事件觸發(fā)功能完成這項(xiàng)工作，主要代碼如下：

這是一個(gè)事件觸發(fā)子程序，放在sheet1對象中，選中這個(gè)sheet1中的D1或C1單元格，則觸發(fā)Worksheet_Change子程序運(yùn)行。下面對程序進(jìn)行說明：

第1行：子程序的入口參數(shù)為單元格地址，Target定義的類型為Range。

第2行：用IF語句對入口參數(shù)進(jìn)行判斷，如地址為$D$1 或 $C$1 則為true，否則為false。

第3行： IF為true，調(diào)用子程序Calender。運(yùn)行后完成月歷表設(shè)置及日期數(shù)據(jù)更新。

表9是選中$D$1和$C$單元格中的數(shù)字“2018”和“10”后運(yùn)行的結(jié)果。

表9 觸發(fā)程序運(yùn)行后的結(jié)果

至此月歷制作完成，生成年歷需將月歷生成程序修改成一個(gè)子程序或?qū)ο螅ㄟ^循環(huán)調(diào)用可以方便地生成整年的年歷，這里不再贅述?？傊肰BA編寫程序?qū)崿F(xiàn)萬年歷，比用函數(shù)及數(shù)組公式繁瑣，但思路更簡單，原因是程序更擅長這類問題的解決，但應(yīng)用人員需要對VBA的運(yùn)行環(huán)境及語句有一定了解。

4 結(jié)語

Excel提供了很多實(shí)用功能及函數(shù)，掌握這些功能及函數(shù)可以解決許多實(shí)際問題，對某一問題提供多種解決途徑。本文采用Excel的3種不同功能完成萬年歷制作，當(dāng)然還可利用其它功能，比如Excel自帶的日期控件完成制作。利用函數(shù)或數(shù)組公式制作萬年歷，需要對Excel日期表示方法、日期函數(shù)、數(shù)組公式、格式設(shè)置等有較深刻的理解;利用VBA工具制作萬年歷，需要對開發(fā)環(huán)境及編程技術(shù)有一定的了解及掌握。有了這些知識(shí)及技術(shù)，用多種方法制作一張萬年歷就非常容易，本文即為用多個(gè)方法解決類似問題的實(shí)例。

利用Excel的函數(shù)、數(shù)組公式、公式循環(huán)引用、工作表數(shù)據(jù)處理、數(shù)據(jù)透視表等功能，可對數(shù)據(jù)進(jìn)行計(jì)算、排序、查詢、篩選、分類、匯總、統(tǒng)計(jì)處理，圖表功能將抽象的數(shù)據(jù)轉(zhuǎn)換成形象的圖表，實(shí)現(xiàn)在不同類型的數(shù)據(jù)庫與Excel工作表之間數(shù)據(jù)導(dǎo)入導(dǎo)出功能的轉(zhuǎn)換，加載功能提供功能擴(kuò)展接口。這些功能解決了許多數(shù)據(jù)處理問題，減輕了工作量，提高了數(shù)據(jù)處理效率。

參考文獻(xiàn)：

[1] 端木彥. 高中數(shù)學(xué)教學(xué)中Excel軟件的應(yīng)用研究[D]. 南京：南京師范大學(xué)，2011.

[2] 徐清泉. Excel在實(shí)現(xiàn)數(shù)值計(jì)算中的應(yīng)用研究[J]. 軟件導(dǎo)刊，2013（4）：47-48.

[3] 李欣樂. Excel宏在計(jì)算高校專業(yè)學(xué)分績排名中的應(yīng)用[J]. 電腦知識(shí)與技術(shù)，2018（18）：232-236.

[4] 馬嬋娟. Excel在高中數(shù)學(xué)函數(shù)教學(xué)中的應(yīng)用研究[J]. 考試周刊，2018（59）：85-86.

[5] 施得天. Excel在成績統(tǒng)計(jì)的運(yùn)用[J]. 電腦知識(shí)與技術(shù)，2018（19）：224-225.

[6] 李浩晨. Excel圖表在成績管理中的應(yīng)用[J]. 電腦知識(shí)與技術(shù)，2017（24）：190-192.

[7] 蘇巖峰. 利用Excel數(shù)組公式設(shè)計(jì)考試成績自動(dòng)分析系統(tǒng)[J]. 中國教育技術(shù)裝備，2018（9）：31-32.

[8] 陳琳. Excel邏輯函數(shù)在實(shí)踐中的應(yīng)用[J]. 軟件導(dǎo)刊，2013（11）： 91-93.

[9] 鄒來智，吳強(qiáng). 基于Excel的擲硬幣實(shí)驗(yàn)[J]. 電腦知識(shí)與技術(shù)，2010（4）：930-931.

[10] 曾強(qiáng)，鄧敬源，袁明明. 利用Excel Vba求解運(yùn)輸問題的計(jì)算機(jī)輔助算法[J]. 計(jì)算機(jī)應(yīng)用與軟件，2017（7）：40-42，54.

[11] 趙旭娟. 運(yùn)用Excel的規(guī)劃求解工具解決最佳人員分配問題——以求解某企業(yè)特定崗位人員需求為例[J]. 電腦知識(shí)與技術(shù)，2018（16）：270-271.

[12] 常雨芳，王粟，黃文聰，等. 線性規(guī)劃問題計(jì)算機(jī)求解方法研究[J]. 軟件導(dǎo)刊，2012（9）：25-26.

[13] 章艷軍. 基于Excel 函數(shù)的日記賬管理系統(tǒng)設(shè)計(jì)與應(yīng)用[J]. 辦公自動(dòng)化，2018（15）：59-60.

[14] 喬靜文. Excel財(cái)務(wù)軟件系統(tǒng)設(shè)計(jì)研究[J]. 電子設(shè)計(jì)工程，2015（18）：53-55.

[15] 于月超. 利用Excel進(jìn)行工資薪金個(gè)人所得稅納稅籌劃——以某事業(yè)單位為例[J]. 商業(yè)會(huì)計(jì)，2018（8）：56-68.

[16] 朱儉，馬敬賢，宋玉，等. VBA在Excel中的應(yīng)用[J]. 軟件導(dǎo)刊，2015（4）：46-48.

（責(zé)任編輯：杜能鋼）