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

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

關鍵詞：Excel日期函數;數組公式;VBA開發;萬年歷制作

DOI：10. 11907/rjdk. 182689 開放科學（資源服務）標識碼（OSID）：

中圖分類號：TP319文獻標識碼：A 文章編號：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-），男，復旦大學國家級實驗教學示范中心高級工程師，研究方向為管理信息系統、計算機教學。

0 引言

日常工作學習生活中經常會處理一些數據。Microsoft Office是一套由微軟公司開發的辦公軟件套裝，Excel是其中一款電子表格處理軟件，其界面友好，功能強大實用，能很好解決人們日常工作學習生活中遇到的數據處理問題，在各行各業廣泛應用。

在教學領域，可用Excel圖表功能制作高中數學函數動態圖形[1]，用功能或函數進行數值計算[2]，對學習成績進行統計分析[3-8]，進行實驗模擬[9]等。

在人事管理中，可用Excel管理所有職工資料，處理公司人事調動和績效考核等重要事務。

在生產領域，管理員需要時刻明確產品的生產總量和需要生產量，數量要精確。了解生產整體進度，以便隨時調整計劃并做好人員配備工作[10-12]。

財務管理中，Excel被公認為是一個通用的財務軟件，凝聚了許多開發者的智慧，以及廣大財務人員和投資分析人員的工作經驗，具有強大而靈活的財務數據管理功能[13-15]，Excel還為用戶提供VBA功能開發接口[16]。

上述應用都離不開Excel所特有的平臺及強大的函數、圖表、內置可加載的應用程序、VBA開發工具等功能。Excel應用平臺由一些單元格組成，擅長各類表格的數據處理，利用它來制作萬年歷也非常合適。因為有現成的平臺，又有多種函數及功能，不需要編寫很長的代碼，只需利用函數及功能就可完成。

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

1 利用日期函數制作萬年歷

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

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

表1 用row和column函數填充后的結果

1.1 確定本月第一天的星期數

在表1的基礎上生成某月日歷，關鍵是確定本月第一天日期對應的星期數以及本月最后一天的日期才能確保結果正確，以下是具體的實現過程及注意事項：

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

WEEKDAY函數計算本月第一天的星期數，把它看成一個修正值，通過減去單元格中的數值調整本月第一天對應一個正確的星期位置。在本例中，這個修正值是 WEEKDAY（DATE（YEAR（$A$19），MONTH（$A$19），1），1）-1，計算結果為4。對現有單元格減去這個值，正好與星期四對應。只要本月對應正確，其它月份就都是正確的。DATE（YEAR（$A$19），MONTH（$A$19），1）是構建的日期，即2018年2月1日，星期四，WEEKDAY（DATE（YEAR（$A$19），MONTH（$A$19），1），1）返回值是5。

要注意的是，A19單元格存放的是年月，必須絕對引用，防止填充時移位。其與利用數組公式制作萬年歷的方法不一樣，因為該方法不需要進行填充操作。

需要說明的是，在填充過程中，如出現“#####”錯誤信息，是因為日期數為負所以出錯，將單元格格式設為常規就可恢復正常。另外在這兒出現了負值可暫不處理，在后面將它隱去。運行結果如表2所示。

表2 用修正值調整后的結果

至此，表中顯示2018年2月1日是星期四，改變A19單元格年月進行測試，結果應該是正確的。但還有兩個問題：①表中出現了小于等于0的數值;②大于本月最后一天的數值。

1.2 計算本月最后一天的日期

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

1.3 隱藏不是本月的日期

在表2中，出現了小于等于0的數值，也出現了大于本月最后一天的數值，這都是不符合要求的，需要隱去。可利用if函數設置：如果數值小于等于0或數值大于本月最后一天，就設置為空格（隱去日期），否則正常顯示，具體公式如下：

填充后的運行結果如表3所示。

表3 隱去不是本月數值的最后結果

1.4 實現過程說明

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

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

2 利用數組公式制作萬年歷

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

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

2.1 通過數組公式生成數據矩陣

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

表4 用數組公式計算結果

2.2 數組公式與單元格中的日期數據求和，生成連續日期

用 DATE函數構建本月第一天日期，選中A31∶D36區域，輸入公式：

表5 加上本月1日后的結果

2.3 確定本月第一天星期數

確定本月第一天星期數前面已經詳細介紹，在此不再贅述。選中A31∶D36區域，在編輯欄中輸入公式：

表6 修正值調整后并設置成日期格式結果

2.4 隱去不是本月的日期

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

表7 隱去不是本月日期的最后結果

2.5 設置單元格格式，僅顯示日期

選中日期區域，設置自定義格式為：d，就可得到最后結果。

以上詳細地介紹了制作月歷的過程及方法，用同樣方法可實現12個月的年歷。最后，添加一個年份數值控件，通過按動這個控件按鈕改變年份數值，系統將自動生成年度日歷，也可用數據有效性功能實現年份選項，結果如圖1所示。

圖1 萬年歷樣式

2.6 實現過程說明

這里用數組公式生成數據矩陣，配合函數計算整個萬年歷表。

以下公式含意：

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

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

3 利用VBA 開發工具制作萬年歷

VBA（Visual Basic for Applications）是內嵌于Office軟件中的一個開發模塊。這個模塊提供程序自主開發，語言基礎和VB（Visual Basic）相似，本文采用基于Excel的VBA開發環境。

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

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

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

啟用開發工具選項卡，在文件→選項→自定義功能區中啟用開發工具選項卡。啟用VBA中的錄制宏功能，在文件→選項→信任中心→信任中心設置→宏設置中選中“啟用所有宏”。

錄制宏代碼，進入Excel編輯界面，可以看到開發工具選項卡，然后進入VBA開發平臺，在代碼組中有“錄制宏”按鈕。按下這個按鈕，命名這個宏名稱為“My_Macro”。在sheet1的A2∶G8區域中進行日歷表格式設置。設置完成后，按下“停止錄制”按鈕，至此宏錄制成功，自動生成VBA代碼。

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

表8 宏代碼運行結果

3.2 編寫程序填寫日歷數據

下面是本程序說明：

本程序的運行結果是在A3到G8單元格區域內生成日歷表，共6行7列，程序有兩層循環。

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

3.3 設置事件觸發運行日歷生成程序

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

這是一個事件觸發子程序，放在sheet1對象中，選中這個sheet1中的D1或C1單元格，則觸發Worksheet_Change子程序運行。下面對程序進行說明：

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

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

第3行： IF為true，調用子程序Calender。運行后完成月歷表設置及日期數據更新。

表9是選中$D$1和$C$單元格中的數字“2018”和“10”后運行的結果。

表9 觸發程序運行后的結果

至此月歷制作完成，生成年歷需將月歷生成程序修改成一個子程序或對象，通過循環調用可以方便地生成整年的年歷，這里不再贅述?？傊?，用VBA編寫程序實現萬年歷，比用函數及數組公式繁瑣，但思路更簡單，原因是程序更擅長這類問題的解決，但應用人員需要對VBA的運行環境及語句有一定了解。

4 結語

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

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

參考文獻：

[1] 端木彥. 高中數學教學中Excel軟件的應用研究[D]. 南京：南京師范大學，2011.

[2] 徐清泉. Excel在實現數值計算中的應用研究[J]. 軟件導刊，2013（4）：47-48.

[3] 李欣樂. Excel宏在計算高校專業學分績排名中的應用[J]. 電腦知識與技術，2018（18）：232-236.

[4] 馬嬋娟. Excel在高中數學函數教學中的應用研究[J]. 考試周刊，2018（59）：85-86.

[5] 施得天. Excel在成績統計的運用[J]. 電腦知識與技術，2018（19）：224-225.

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

[7] 蘇巖峰. 利用Excel數組公式設計考試成績自動分析系統[J]. 中國教育技術裝備，2018（9）：31-32.

[8] 陳琳. Excel邏輯函數在實踐中的應用[J]. 軟件導刊，2013（11）： 91-93.

[9] 鄒來智，吳強. 基于Excel的擲硬幣實驗[J]. 電腦知識與技術，2010（4）：930-931.

[10] 曾強，鄧敬源，袁明明. 利用Excel Vba求解運輸問題的計算機輔助算法[J]. 計算機應用與軟件，2017（7）：40-42，54.

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

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

[13] 章艷軍. 基于Excel 函數的日記賬管理系統設計與應用[J]. 辦公自動化，2018（15）：59-60.

[14] 喬靜文. Excel財務軟件系統設計研究[J]. 電子設計工程，2015（18）：53-55.

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

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

（責任編輯：杜能鋼）