运用EXCEL函数推算星期序号

运用EXCEL函数推算星期序号

蒋洪力 唐山市开滦一中

人类的生产和生活与时间的关系十分密切，尤其与日期相对应的星期纪日制度更是紧密联系，须臾不能分开。每一个公历历日、各种传统节假日、纪念日、人们的出生日等等，都对应于相应的星期序号。笔者根据蔡勒公式，运用EXCEL函数处理数据的强大功能，制作了推算星期序号的工作表，只要输入任意日期，系统就能立即显示出与之相对应的星期序号。通过本文对函数运用的示例和剖析，使我们能够进一步了解、掌握和运用函数的思路、方法和技能，培养发现、分析、解决问题的能力以及创新能力和科学探索精神，为人类的生产和生活服务。

制作星期序号推算表

蔡勒公式可表述为：“W=[C/4]-2C+y+[y/4]+[26・(M+1)/10]+D1-7q”。式中W、C、Y、M、D分别表示星期、年的前两位数、年的后两位数、月(3≤M≤14，即某年的1、2月要看作上一年的13、14月)、日；[ ]代表取整数。公式取“[C/4]-2C+y+[y/4]+[26・(M+1)/10]+D1”作为被除式，“7”为除数，q为商数，余数W就是星期序号。

那么，如何将上述公式转换为计算机能够识别和处理的语言呢？

首先新建一个工作表，制作好表头、列标题等，将A列设为“文本”格式，L列设为“中文小写数字”格式，并对整个工作表进行修饰工作。如表①：

第二步，提取年份数的前两位数。在B3单元格输入如下公式（等号“＝”表示输入公式，引号“”不要输入）：

“＝VALUE(MID(A3,1,2))”。

第三步，分别提取年份数的后两位、月份和日期。在C3：E3分别输入：“＝VALUE(MID(A3,3,2))”、“＝VALUE(MID(A3,6,2))”、“＝VALUE(MID(A3,9,2))”。

上述公式的语法和功能如下：

(1)提取文本子串函数“MID”：它的语法格式为MID(text,start_num,num_chars)，即MID(文本串，起始位置，长度)。其功能是从“文本串”中指定的“起始位置”起提取指定“长度”的文本子串。例如，“A3”保存有文本格式的日期“2011.10.01”，MID(A3,6,2)就是从“A3”的第“6”个字符位置起提取“2”个字符长度的文本子串“10”。其中小数点“.”占一个字符位置。

(2)文本转数值函数“VALUE”：它的语法格式为：VALUE(text)，其中“text”为代表数值的文本字符串。该函数功能是将一个代表数值的文本字符串转换成数值。转换成数值后，系统就可以判断数值大小和进行数据处理计算。例如，MID

(“2317.03.01”,6,2)等于文本串“03”，VALUE(MID(“2317.03.01”,

6,2))又将文本串“03”转换成数值“03”。如果不使用“VALUE”函数，系统就会将文本格式的“03”错误判断为“＞3”，计算时就会发生错误。

第四步，建立推算星期序号的公式，并引用中间变量B3、C3、D3、E3。在L3输入：

“=IF(D3

L3公式中函数的语法和功能如下：

(1)逻辑判断函数“IF”：IF函数用于执行真假判断后，根据逻辑判断的真假值返回不同的结果，因此IF函数也称为条件判断函数。IF函数的语法格式为：IF(logical_test,value_if_true,value_if_false)。即IF(逻辑表达式，表达式真，表达式假)。它表示：如果“逻辑表达式”所给条件成立（逻辑真），则返回“表达式真”的结果，否则将返回“表达式假”的结果。

(2)取整函数“INT”：其语法格式为INT(number)，即INT(数值表达式)。其功能是返回一个不大于“数值表达式”的最大整数。例如，B16单元格保存数字“23”，则INT(B16/4)返回结果“5”；同样道理，INT(-0.25)返回“-1”。

(3)取余函数“MOD”：MOD函数返回两数相除的余数。其格式为MOD(number,divisor)，其中“number”为被除数，“divisor”为除数，余数符号必须与除数相同。

例如：被除数是“-10”，除数是“7”，商应该是“-2”，得余数是“4”，“7”与“4”符号相同；被除数是“15”，除数是“4”，则余数是“3”。

L3公式的含义是：当月份(D3)是1、2月时，系统判断“D3＜3”条件成立，则返回“表达式真”值，即返回“MOD(INT(B3/4)-2*B3+C3-1+INT((C3-1)/4)+INT(26*(D3+13)/10)+E3-1，7”的值；当月份是3～12月时，系统判断“D3＜3”条件不成立，则返回“表达式假”值，即返回“MOD(INT(B3/4)-2*B3+C3+INT(C3/4)+INT(26*(D3+1)/10)+E3-1,7”的值。

星期与年月日的函数关系的讨论

假如1年有13个月共364天、1个月有4个星期共28天、1个星期有7天，即年和月所包含的天数是“7”的整数倍，推算星期序号就十分简单。如果令1月1日是星期三，则每月1日都是星期三，2日都是星期四……，余类推，星期与日期是固定的。用公式可表示为：所求星期=(日期差+已知星期)-7•商，即W2=(D+W1)-7q。例如，已知3月6日是星期3，则3月29日是W2=(D+W1)-7q=(29-6+3)-7×3=5，即星期五。

可是，公历安排年月日的规定比较复杂，它的年和月所包含的天数并不是“7”的整数倍，大小月安排很不均匀。因此，推算星期序号就要根据公历的规定来设计公式。

现行阳历规定：平年365天，闰年366天；一年12个月，1、3、5、7、8、10、12月为31天，4、6、9、11月为30天，平年的2月为28天，闰年的2月为29天；能被4整除的年份原则上是闰年，但是逢世纪之年（即公元年数最后两位是“00”的年份），必须同时能被“4”和“400”整除才是闰年。例如，在1700～2099年这400年中，按4年1闰计算，400年内应安排100个闰年。但是，其中1700、1800、1900这3个世纪年虽然能被4整除，但不能被400整除，因此这3年都不是闰年，即1700～2099这400年只安排97个闰年。“公历”平均历年为400－97×365＋366×97400＝365＋97400＝365.2425(天)，即每400年97闰。公历年比回归年365.2422天多0.0003天，大约3333年就多1天。

下面，对L3公式的年月日各项分别展开进行分析和讨论。

(1)对日期项建立公式，在I3输入：“=E3”。在世纪、年份、月份相同的条件下，每月的各日序号号成公差为“1”的等差数列，各星期序号也成公差为“1”的等差数列且7日为一循环周期，两者相互对应相互依存并同步变化。例如，2011.03.01是星期“5”，则2011.03.02是星期“6”，余类推。任意日期的星期用公式可表示为W2=(D+W1)-7q。因此，日期序号引用E3单元格保存的数据。

(2)对月份各项建立公式，在H3输入：“=IF(D3

假设每月有28天，4月1日是星期“5”，则每月1日都是星期“5”。实际上，4月是30天，比28天多2天，则5月1日实际向前推移了2天，是星期“5+2”，即星期“日”；同理，5月是31天，比28天多3天，则6月1日实际向前推移了3天，是星期“7+3”，即星期“三”。也就是“大月的下月星期序数要加上‘3’，小月的下月星期序数要加上‘2’”。这样就使跨月份之间的日期与星期的衔接保持连续并同步变化。

(3)对年的后两位建立公式，在G3输入“=IF(D3

(4)对年的前两位建立公式，在F3输入：“INT(B3/4)－2*B3”。公式使年的前两位数每增加数值“1”，计算结果就减少数值“2”；如果年份的前两位数能被“4”整除，计算结果只减少数值“1”，即每400年加1闰。它与年的后两位相结合，每400年有“96+1”个闰年。

(5)在J3输入：“=F3+G3+H3+

I3-1”。该公式引用了中间变量F3、G3、H3、I3，同时又间接引用了B3、C3、D3、E3变量。

(6)在K3输入“7”。

(7)在L3输入“=MOD(J3,K3)”。公式将J3作为被除数，“7”为除数，余数就是星期序号（其中余数0代表星期日，1代表星期一，2代表星期二，余类推）。

因为公元0年(公元纪年没有0年，我们约定0年为-1年)12月31日恰是星期日，则自该日至某日的天数之和除以“7”的余数就是某日的星期序号。蔡勒根据公历对世纪、年、月、日的规定，运用数学定律和余数定理将公元元年至某日的天数之和用含有年月日各项的多项式来表示，再进行综合归纳并项简化，消去能被“7”整除的项，得到最简的星期与年月日之间的函数关系式，我们称之为蔡勒公式。

由表1可以看出，星期与年月日的函数关系还有如下规律：

假设一年有364天，能被7整除，则每年相对应日期的星期相同，以此作标准，则(3月1日为岁首，下一年的2月最后一天为本年的岁末)：

(1)若某年是平年，有“364+1”天，则下一年相对应月份日期的星期原则上要加上“1”；

(2)若某年的下一年恰逢闰年，有“364+2”天，则闰年对应月份日期的星期要加上“2”；

(3)年份每增加100年，则对应月份日期的星期原则上要加上“5”；

(4)若某年增加100年恰逢闰年，则闰年对应月份日期的星期要加上“6”；

(5)满400年对应日期的星期序号完全相同，即星期序号每400年为一循环周期。

因为公元元年12月31日是星期日，根据上述规律，可以推算出：

如果不考虑未来公历与回归年之间的误差，则公元400，800，1200，…，400n，…等能被400整除的世纪年的12月31日均是星期日；

2010.12.31比2000.12.31多8平年加2闰年，根据余数定理，2010.12.31应在2000.12.31星期“日”的基础上加上(8+2×2)-7×1=5，即2010.12.31是星期五；

2011.01.01是星期六，则2011.10.01

是星期W2=(D+W1)-7q=(31×5+28+30×3+6)-7×39=6；

2110.12.31比2010.12.31多100年，是星期(5+5)-7×1=3；

2112.12.31在2110.12.31基础上多1平年加1闰年，应是星期(3+1+2)-7×1=6；

2113.12.31是星期6+1，即星期日；2114.01.01是星期一；

2114.03.01比2114.01.01多(31+28)天，是星期W2=(D+W1)-7q=(31+28+1)-7*8=4；

2114.03.25是星期W2=(D+W1)-7q=(25-1+4)-7*4=0，即星期日。

我们也可以将L3公式中的B3、C3、D3、E3与A3进行等量代换，使之变形为：

“＝IF(VALUE(MID(A3,6,2))＜3,MOD(INT((VALUE(MID(A3,1,2)))/4)－2*(VALUE(MID(A3,1,2)))＋VALUE(MID(A3,3,2))－1＋INT((VALUE(MID(A3,3,2))－1)/4)＋INT(26*(VALUE(MID(A3,6,2))＋13)/10)＋VALUE(MID(A3,9,2))－1,7),MOD(INT((VALUE(MID(A3,1,2)))/4)－2*(VALUE(MID(A3,1,2)))＋VALUE(MID(A3,3,2))＋INT(VALUE(MID(A3,3,2))/4)＋INT(26*(VALUE(MID(A3,6,2))＋1)/10)＋VALUE(MID(A3,9,2))－1,7))”。

上述公式没有中间变量，是蔡勒公式转换成计算机能够识别和执行的完整表述形式，因此可以删除B:K列，使工作表更简洁。

从上述操作可知，EXCEL函数具有强大的数据处理功能，尤其是函数的多层嵌套，可与编程相媲美。只要我们经常学习和研究EXCEL函数，经常上机操作练习，在本文对函数运用的示例和剖析的基础上，根据具体情况和要求，举一反三，发散思维，创造性的变通和运用EXCEL函数，解决生活和工作中的实际问题。