运用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<3,MOD(INT(B3/4)-2*B3+C3-1+INT((C3-1)/4)+INT(26*(D3+13)/10)+E3-1,7),MOD(INT(B3/4)-2*B3+C3+INT(C3/4)+INT(26*(D3+1)/10)+E3-1,7))”.按回车键确定,然后拖动鼠标选中B3:L3,再拖动右下角复制柄,向下复制上述公式到L21.式中“*”是“乘号”,“/”是除号.

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<3,INT(26*(D3+12+1)/10),INT(26*(D3+1)/10))”.因为1月与2月之间相差2～3天,且2月的天数在平年只有28天,在闰年则为29天.因此,公式将1、2月分别作为上一年的13、14月(即“D3+12”)来计算,2月的最后一天作为上一年年末.在3～14这12个月之间向前跨越月份时,如果某月是大月,有31天,公式返回的结果将使下月比某月增加数值“3”；反之,如果某月是小月,有30天,则返回的结果将使下月比某月增加数值“2”,如表1的H3:H7.

检测设每月有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<3,C3-1+INT((C3-1)/4),C3+INT(C3/4))”.公式将1、2月分别作为上一年(即“C3-1”)的13、14月来计算.公式使年的后两位每增加1年,其计算结果就增加数值“1”,即平年“364+1”天；能被4整除的年份其计算结果就增加数值“2”,即4年1闰,闰年“364+2”天.在100年中,年的后两位有24年能被4整除(“00”年视为不能被4整除),即100年有24闰年,则400年有“96”闰年.这样就使跨年份之间的日期与星期的衔接保持连续并同步变化.

(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函数,解决生活和工作中的实际问题.