《用Excel制作万年历的3种方法》-工学论文，软件工程论文-论文范文参考-科学狗论文网

标题

用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.

（责任编辑：杜能钢）

随便看

科学优质学术资源、百科知识分享平台，免费提供知识科普、生活经验分享、中外学术论文、各类范文、学术文献、教学资料、学术期刊、会议、报纸、杂志、工具书等各类资源检索、在线阅读和软件app下载服务。