使用Excel数组函数制作日历

 

参照John Walkenbach书中的方法，制作了一个日历，效果如下：

 

这个日历可以实现从1900年开始至9999年的日期的显示，实现方法主要是基于时间日期函数和数组公式的应用。

D2单元格用于选择月份，在数据有效性中选择序列，输入January,Febury,March,April,May,June,July,August,September,October,November,December：

F2用于调整年份，插入“数值调整钮”控件

 

设置控件格式如下：

现对实现过程解释如下：

第一步、首先定义如下几个名称

1.MonthNames:

={"January","Febury","March","April","May","June","July","August","September","October","November","December"}

用于月份下拉列表中的序列。

2.TheMonth:记录所选择的月份。

=MATCH(日历!$D$2,MonthNames,0)

3.TheYear:记录所选择的年份。指向了G2

4.week ={0;1;2;3;4;5}注意这里的数组常量中由分号分隔，这是一个六维的数组，之所以是六个，是因为一个月中最多横跨六个周。

5.weekday ={0,1,2,3,4,5,6}注意此处的数组常量由逗号分隔，这是一个一维的七元数组，表示一周的七天（更确切的讲是七天的位置）。

6.StartDate =DATE(TheYear,TheMonth,1)表示在选定的年份、选定的月份条件下，该月第一天。使用这个值是为了之后区分是否是本月的数据方便。

7.StartDow =WEEKDAY(StartDate,3)表示StartDate在一个星期中的位置，即在weekday中的某一个数组。。此处WEEKDAY是EXCEL中的一个时间日期函数，需关注输入参数3，按照星期一—星期日的顺序依次显示0-6，返回参数代表日期的星期数。StartDow的取值为0-6，刚好与weekday（数组常量）中的数值相一致。

 

第二步、分析

可以确定，一个月中最大是星期跨度为6，因此每月的日期必然能够放在一个6*7的区域内，在本文中即为"C6:I11"。下面我们要解决的是如下两个问题：
问题1：如何得到该6*7区域中每一天的日期。

问题2：如何区分出该区域中哪些是在本月的，哪些不是在本月的，不在本月份的应不予显示。

 

第三步、实现：

1.问题1的解决：

我们可以利用两个数组常量week={0;1;2;3;4;5}和weekday={0,1,2,3,4,5,6}进行数组公式运算、借助StartDate，StartDow、使用其他时间日期函数来实现，具体方法为

选定单元格区域"C6:I11"，输入公式“=StartDate-StartDow+week*7+Weekday ”，并按下“Ctrl+Shift+Enter”组合键，输入此数组公式。结果如下：（以2011/03月为例）

40601	40602	40603	40604	40605	40606	40607
40608	40609	40610	40611	40612	40613	40614
40615	40616	40617	40618	40619	40620	40621
40622	40623	40624	40625	40626	40627	40628
40629	40630	40631	40632	40633	40634	40635
40636	40637	40638	40639	40640	40641	40642

 

其中week*7+weekday会得到如下二位数组：

0	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31	32	33	34
35	36	37	38	39	40	41

 

而-StartDow+week*7+weekday则表示每一天相对于本月第一天（用StartDate表示）的相对距离，结果如下

-2	-1	0	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31	32
33	34	35	36	37	38	39

 

所以StartDate-StartDow+week*7+Weekday就可以得到这个表格中的每一个单元格中代表的日期的真实日期值。如果我们把这个单元格的格式调成日期时间格式，结果如下：

 

2011-2-27	2011-2-28	2011-3-1	2011-3-2	2011-3-3	2011-3-4	2011-3-5
2011-3-6	2011-3-7	2011-3-8	2011-3-9	2011-3-10	2011-3-11	2011-3-12
2011-3-13	2011-3-14	2011-3-15	2011-3-16	2011-3-17	2011-3-18	2011-3-19
2011-3-20	2011-3-21	2011-3-22	2011-3-23	2011-3-24	2011-3-25	2011-3-26
2011-3-27	2011-3-28	2011-3-29	2011-3-30	2011-3-31	2011-4-1	2011-4-2
2011-4-3	2011-4-4	2011-4-5	2011-4-6	2011-4-7	2011-4-8	2011-4-9

 

至此，问题1已经得到了解决，我们得到了这个6*7的单元格

 

2.问题2的解决。

我们使用If函数和Month函数的混合搭配即可过滤掉非当前月份的数据，具体如下：

选定该单元格区域，输入如下公式

“=IF(MONTH(StartDate-StartDow+week*7+Weekday)<>TheMonth,"",DAY(StartDate-StartDow+week*7+Weekday))”，按下“Ctrl+Shift+Enter”组合键。

该公式通过判断每一个单元格区域代表日期所在的月份值与所选定的月份值是否相等，如不相等，则不显示任何数据，如相等，也就是确实是选定月份的日期数据，则显示出该日期的日数（有DAY函数给出）。注意，这样需将该区域的单元格格式调为“数值”或“常规”，而不能是“时间日期”。