Excel万年历的制作

简介:

1.   概述

Office 2007 是微软 Office 产品史上最具创新与革命性的一 个版本。全新设计的用户界面、稳定安全的文件格式、无缝高效的沟通协作。
Excel Office 套件中的一个重要组件之一。直观 的界面、出色的计算功能和图表工具,再加上成功的市场营销,使 Excel 成为最流行的微机数据处理软件。
日历分为阴历和阳历。阳历的历年为一个回归年,现时国际通用的公历(格里高历)即为太阳历的一种, 亦简称为阳历;阴历亦称月亮历,或称太阴历,其历月是一个朔望月,历年为 12 个朔望月,其大月 30 天,小月 29  天;中国的农历就是阴阳历的一种。
如何在 Excel 实现万年历是对 Office 能力的综合运用,又是分析问题并进行求解的一系列过程。

2.   分析

2.1         整体构思

在进行整体构思之前,不妨我们先看看多数日历是怎样的情形,其中:
1、    上面居中为当 前年份。
2、    然后为当年的 12 个月份,至于每行为 3 个月还是 4 个月取决于可见界面的大小和美观。
3、    总所周知,一 周则为 7 天,每个 月最大 31 天,最 小 28 天;如果 1 号为周六或周日的话,则该月可能要跨越 6 周;因此每个月最大的跨越为六周,事实上 我们找一下其它日历的模板,就会发现一般都是六周。所以我们可以把每个月视为一个 7*6 的单元格集合。
6517_201006010115491.jpg

2.2       算法问题

当我们确定了每个月事实上为 7*6 的单元格集合后,我们接下来要做以下几件事情:
1、    如何定位和填 充相应的每月的 7*6 单元格
在单纯的 Excel 表中,我们很难采用逻辑判断和循环 的方式获取在 7*6 单 元格集合中的第一个星期一应该是哪一天;但我们知道这个 7*6 单元格所在月的第一天即 1 号却是固定的,同样当日期固定后,该日期所在的星期几也是固定的,假设 2010-01-01 为星期五,那星期一是几号呢?很显然,为 2010 1 1 号向前推 4 天即 2009 12 28 号,星期二则为 1 1 号向前推 3 天,以此类推,可以推到整个 42 个单元格中,只不过超过当月第一天的日期则向后推了。
如果我们换个思路可能更好些,在第一个 7*6 单元格集合中的首个星期一为 2010 1 1 号向前推 5 天即星期五,然后加 1 天,这也是理所当然的求法,那么星期二则 为 1 1 号向前推 5 天,然后加 2 天,以此类推,推倒第 42 个单元格中则为 1 1 号向前推 5 天,然后加 42 天了,从 1 42 则为一个整数的序列;这样就变成 Excel 更擅长的计算方式了。
2、    如何屏蔽非当 月的日期
大家也许会注意到在这个 7*6 的单元格集合中,一定会混入不属于这 个月的日期,怎么把这些不属于本月的日期去除呢?如果我们用函数来实现的话,也不是不可能,只不过过于复杂。
幸好 Excel 提供了条件格式,条件格式的目的就 是为符合或不符合相应条件的单元格提供该单元格的格式化需求,如果我们将小于当月第一天,或者大于等于下个月第一天值的单元格字符设置为白色字体,同时将 这些单元格的背景色也置为白色,岂不是看不到了嘛。
3、    农历问题
农历问题是属于典型的算法问题,我还从未见到 在单纯的 Excel 表 格中使用函数和表达式就能实现农历算法的。很幸运的是, Office 提供了强大的编程语言 VBA ,再加上强大的互联网搜索引擎,我们很容易就能找到并实现农历的功能。
关于如何对不在当月的农历日期进行屏蔽,详情 见步骤。

2.3         显示和实际数 据存储的关系

6517_201006010115492.jpg
如果仅看以上最终版本,我们很可能认为“ 2010 年日历”为字符串,“ 1 月份”也为字符串,而月份中的日期为数字,实际上除了“一、二、三、四、五、六、日”为字符串外, 年份、月份均为数字类型,而日期则为日期类型;在 Excel 表中数据的展现方式与存储可以表现迥异,当然展现方式也不能完全孤立于其存储数据的类型和内容。
只有理解了以上困惑和难点,我们才知道如何下手开始工作。

3.   步骤

3.1        填充7*6日期单元格

为了简化问题,便于说明问题,让我们首先从完成第一个月开始
假设当前为 2010 年的 1 月份。
那么 2010 1 月份首日则为 2010-01-01 。我们把 B5 设置为 2010-01-01 ,同时将其设置单元格格式为 m" "" "
同时 2010-01-01 所在的星期 =weekday(B5,2) ,则为 5 即星期五
6517_201006010115493.jpg
既然 2010-01-01 是星期五,那本 7*6 单元格集合的第一个星期一应该是几号呢?
显然为 =B5-4 ,也即 =B5-WEEKDAY(B5,2)+1 ,然 后依次加 2 得到周 二的日期,加 3 得 到周三的日期,加 8 得 到下周一的日期;既然从 1 42 是 个数字序列,为了快捷的缘由,我们直接构造了一个从 1~42 的数字序列,因此用我们把 B5-J6+1 替换为 B5-J6+K6 ;然后把拷贝公式到之后的行列中,但这样问题又来了,拷贝公式是相对引用的,我们想变的是 K6 即数字序列而不是 B5 J6 ,因此我们要把 B5 J6 改为绝对引用,即 $B$5,$J$6 =$B$5-$J$6+K6
6517_201006010115494.jpg
当我们完成公式,并复制到 7*6 剩余的单元格后,就会发现 1 月份已填充完毕了,再与本机的日历比较一 下,发现是没问题的
6517_201006010115495.jpg

3.2      条件格式隐藏 不符合条件的数据

接下来我们来考虑如何隐藏非本月的日期,实际 上算法很简单:
2010 1 月份的日历中,将日期 <2010-01-01  或者  日期 >=2010-02-01 的 单元格的字体颜色和背景色设置为白色即可。
在这里我们首先选中 1 月份的 42 个单元格,然后找到条件格式,点击管理规 则。
6517_201006010115496.jpg
      在条件格式规则管理器中,点击新建规则。
6517_201006010115497.jpg
在新建格式规则中,选择规则类型为,选择规则类型为“只为包含以下内容的单元格设置格式”。
设置单元格值  小于  $B$5 ,即 2010 1 1 日所在的单元格,然后点击格式按钮
在设置单元格格式窗口中,将字体颜色设置为白 色,将填充背景色设置为白色。
确定后回到条件格式规则管理器,再次新建规则 — > 只为包 含以下内容的单元格设置格式。
6517_201006010115498.jpg
设置单元格值  大于或等于  $S$5 ,即 2010 2 1 日所在的单元格,然后点击格式按钮。
在设置单元格格式窗口中,将字体颜色设置为白 色,将填充背景色设置为白色。
最终,条件格式规则管理器结果如下
6517_201006010115499.jpg
               然后返 回 Excel 表, 发现 2010 1 月份的日期已经正常显示。
6517_2010060101154910.jpg

3.3       套用表格格式 进行美化

为了让界面好看点,我们在点击套用表格格式 ( 选中包括标题栏的 7*7 单元格集合 ) ,选择自己所喜欢的表格样式。
6517_2010060101154911.jpg
但是,套用表格格式会导致另外一个不想要的结 果,即自动对所谓的标题栏进行了筛选。
接下来,我们在这个 7*7 的单元格集合任意地方点击右键— > 表格— > 转换为区域即可。
6517_2010060101154912.jpg

3.4      进一步美工

我们还可以进一步进行美化处理,这个取决于你自己的想象力了,而非技术。
6517_2010060101154913.jpg

3.5        添加农历

首先点击开发工具— > 点击 Visual Basic
然后插入模块,将 GetYLDate() 函数拷贝到右边的工作 区域。
关闭 Visual Basic 窗口,回到 Excel 界面中,在每行日期下面增加一空 行。
6517_2010060101154914.jpg
在对应的空行下输入以下公式,例如在 C7 下,输入 =GetYLDate(C6)
6517_2010060101154915.jpg
很不幸,我们并没有看到如期的效果,这个时 侯,需要选中所有新增的农历行,然后点击条件格式— > 清除规则— > 清除所选单元格的规则。
6517_2010060101154916.jpg
很明显,这个也并非我们想要的,因为所有非本 月的农历日期也全部显示出来了,再应用条件格式的话,恐怕技术上会有问题,我们不妨换一种思路,如果其上面的公历日期在本月范围内,则输出阴历,否则则输 出空格。
=IF(AND(C6>=$B$5,C6<$S$5),GetYLDate(C6)," ")   最终结果如下:
6517_2010060101154917.jpg

3.6         持续改进

首先需要把每个月的日历完善起来。
其次需要把年份动态加入到相应的月份和日期中来。
再次需要添加数值调节钮(表单控件和 ActiveX 控件均可)

4.   总结

Excel 的万年历制作涉及到 Excel 的函数、相对引用、绝对引用、数 据的物理存储和展现、条件格式、套用表格格式、控件、 VBA
如果把万年历看做是一个小型项目的话,从需求分析、系统设计、实现、测试,直至不断优化是个反复迭 代的过程。
总之通过 Excel 万年历的制作收益匪浅。







本文转自baoqiangwang51CTO博客,原文链接: http://blog.51cto.com/baoqiangwang/324573 ,如需转载请自行联系原作者
相关文章
|
9天前
|
存储 小程序 开发工具
【Excel VBA 从入门到出门】一、Excel VBA 是个啥?
【Excel VBA 从入门到出门】一、Excel VBA 是个啥?
38 2
|
9天前
|
数据格式
使用Excel制作甘特图
使用Excel制作甘特图
23 0
|
9月前
|
Python
项目万年历
用python语言编写项目万年历。
107 0
|
10月前
|
Linux 数据安全/隐私保护 iOS开发
WPS文档导出图片要充钱?这个软件让你不花冤枉钱。
WPS文档导出图片要充钱?这个软件让你不花冤枉钱。
|
11月前
|
存储 SQL Oracle
做表格用什么软件?热门表格软件推荐
做表格用什么软件?热门表格软件推荐
|
11月前
|
数据可视化 关系型数据库 BI
表格软件有哪些?热门表格软件推荐
表格软件有哪些?热门表格软件推荐
|
数据可视化 iOS开发 MacOS
FLStudio2024中文完整版水果音乐制作编曲软件
最近,网上算是“风言风语”吧,关于FL Studio是否出21版的说法各异。首先呢,这里先肯定一点,FL Studio即将出FL Studio 21版本,但是正式版已经出来。FL Studio21绿色版本下载如下:http://t.csdn.cn/8vUrw
154 0
小技巧 - PDF 只可远观不可编辑怎么破
小技巧 - PDF 只可远观不可编辑怎么破
85 0
小技巧 - PDF 只可远观不可编辑怎么破
wps第二天表格
excel表格快捷键 快捷键ctrl+shift+上下左右全部选中 shift+鼠标左键选择区域 ctrl+回车选中区域进行同样操作 alt+回车键单元格内强制换行 ctrl+e智能填充
Excel还能这样玩?教你制作朋友圈九宫格照片
Excel还能这样玩?教你制作朋友圈九宫格照片
Excel还能这样玩?教你制作朋友圈九宫格照片