運(yùn)用EXCEL函數(shù)推算星期序號(hào)

運(yùn)用EXCEL函數(shù)推算星期序號(hào)

蔣洪力 唐山市開(kāi)灤一中

人類(lèi)的生產(chǎn)和生活與時(shí)間的關(guān)系十分密切，尤其與日期相對(duì)應(yīng)的星期紀(jì)日制度更是緊密聯(lián)系，須臾不能分開(kāi)。每一個(gè)公歷歷日、各種傳統(tǒng)節(jié)假日、紀(jì)念日、人們的出生日等等，都對(duì)應(yīng)于相應(yīng)的星期序號(hào)。筆者根據(jù)蔡勒公式，運(yùn)用EXCEL函數(shù)處理數(shù)據(jù)的強(qiáng)大功能，制作了推算星期序號(hào)的工作表，只要輸入任意日期，系統(tǒng)就能立即顯示出與之相對(duì)應(yīng)的星期序號(hào)。通過(guò)本文對(duì)函數(shù)運(yùn)用的示例和剖析，使我們能夠進(jìn)一步了解、掌握和運(yùn)用函數(shù)的思路、方法和技能，培養(yǎng)發(fā)現(xiàn)、分析、解決問(wèn)題的能力以及創(chuàng)新能力和科學(xué)探索精神，為人類(lèi)的生產(chǎn)和生活服務(wù)。

●制作星期序號(hào)推算表

蔡勒公式可表述為：“W=[C/4]-2C+y+[y/4]+[26·(M+1)/10]+D–1-7q”。式中W、C、Y、M、D分別表示星期、年的前兩位數(shù)、年的后兩位數(shù)、月(3≤M≤14，即某年的1、2月要看作上一年的13、14月)、日；[ ]代表取整數(shù)。公式取“[C/4]-2C+y+[y/4]+[26·(M+1)/10]+D–1”作為被除式，“7”為除數(shù)，q為商數(shù)，余數(shù)W就是星期序號(hào)。

那么，如何將上述公式轉(zhuǎn)換為計(jì)算機(jī)能夠識(shí)別和處理的語(yǔ)言呢？

首先新建一個(gè)工作表，制作好表頭、列標(biāo)題等，將A列設(shè)為“文本”格式，L列設(shè)為“中文小寫(xiě)數(shù)字”格式，并對(duì)整個(gè)工作表進(jìn)行修飾工作。如表①：

第二步，提取年份數(shù)的前兩位數(shù)。在B3單元格輸入如下公式（等號(hào)“＝”表示輸入公式，引號(hào)“”不要輸入）：

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

第三步，分別提取年份數(shù)的后兩位、月份和日期。在C3：E3分別輸入：“＝VALUE(MID(A3，3，2))”、“＝VALUE(MID(A3，6，2))”、“＝VALUE(MID(A3，9，2))”。

上述公式的語(yǔ)法和功能如下：

(1)提取文本子串函數(shù)“MID”：它的語(yǔ)法格式為MID(text，start_num，num_chars)，即MID(文本串，起始位置，長(zhǎng)度)。其功能是從“文本串”中指定的“起始位置”起提取指定“長(zhǎng)度”的文本子串。例如，“A3”保存有文本格式的日期“2011.10.01”，MID(A3，6，2)就是從“A3”的第“6”個(gè)字符位置起提取“2”個(gè)字符長(zhǎng)度的文本子串“10”。其中小數(shù)點(diǎn)“.”占一個(gè)字符位置。

(2)文本轉(zhuǎn)數(shù)值函數(shù)“VALUE”：它的語(yǔ)法格式為：VALUE(text)，其中“text”為代表數(shù)值的文本字符串。該函數(shù)功能是將一個(gè)代表數(shù)值的文本字符串轉(zhuǎn)換成數(shù)值。轉(zhuǎn)換成數(shù)值后，系統(tǒng)就可以判斷數(shù)值大小和進(jìn)行數(shù)據(jù)處理計(jì)算。例如，MID

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

6，2))又將文本串“03”轉(zhuǎn)換成數(shù)值“03”。如果不使用“VALUE”函數(shù)，系統(tǒng)就會(huì)將文本格式的“03”錯(cuò)誤判斷為“＞3”，計(jì)算時(shí)就會(huì)發(fā)生錯(cuò)誤。

第四步，建立推算星期序號(hào)的公式，并引用中間變量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))”。按回車(chē)鍵確定，然后拖動(dòng)鼠標(biāo)選中B3:L3，再拖動(dòng)右下角復(fù)制柄，向下復(fù)制上述公式到L21。式中“*”是“乘號(hào)”，“/”是除號(hào)。

L3公式中函數(shù)的語(yǔ)法和功能如下：

(1)邏輯判斷函數(shù)“IF”：IF函數(shù)用于執(zhí)行真假判斷后，根據(jù)邏輯判斷的真假值返回不同的結(jié)果，因此IF函數(shù)也稱(chēng)為條件判斷函數(shù)。IF函數(shù)的語(yǔ)法格式為：IF(logical_test，value_if_true，value_if_1)。即IF(邏輯表達(dá)式，表達(dá)式真，表達(dá)式假)。它表示：如果“邏輯表達(dá)式”所給條件成立（邏輯真），則返回“表達(dá)式真”的結(jié)果，否則將返回“表達(dá)式假”的結(jié)果。

(2)取整函數(shù)“INT”：其語(yǔ)法格式為INT(number)，即INT(數(shù)值表達(dá)式)。其功能是返回一個(gè)不大于“數(shù)值表達(dá)式”的最大整數(shù)。例如，B16單元格保存數(shù)字“23”，則INT(B16/4)返回結(jié)果“5”；同樣道理，INT(-0.25)返回“-1”。

(3)取余函數(shù)“MOD”：MOD函數(shù)返回兩數(shù)相除的余數(shù)。其格式為MOD(number，divisor)，其中“number”為被除數(shù)，“divisor”為除數(shù)，余數(shù)符號(hào)必須與除數(shù)相同。

例如：被除數(shù)是“-10”，除數(shù)是“7”，商應(yīng)該是“-2”，得余數(shù)是“4”，“7”與“4”符號(hào)相同；被除數(shù)是“15”，除數(shù)是“4”，則余數(shù)是“3”。

L3公式的含義是：當(dāng)月份(D3)是1、2月時(shí)，系統(tǒng)判斷“D3＜3”條件成立，則返回“表達(dá)式真”值，即返回“MOD(INT(B3/4)-2*B3+C3-1+INT((C3-1)/4)+INT(26*(D3+13)/10)+E3-1，7”的值；當(dāng)月份是3～12月時(shí)，系統(tǒng)判斷“D3＜3”條件不成立，則返回“表達(dá)式假”值，即返回“MOD(INT(B3/4)-2*B3+C3+INT(C3/4)+INT(26*(D3+1)/10)+E3-1，7”的值。

●星期與年月日的函數(shù)關(guān)系的討論

假如1年有13個(gè)月共364天、1個(gè)月有4個(gè)星期共28天、1個(gè)星期有7天，即年和月所包含的天數(shù)是“7”的整數(shù)倍，推算星期序號(hào)就十分簡(jiǎn)單。如果令1月1日是星期三，則每月1日都是星期三，2日都是星期四……，余類(lèi)推，星期與日期是固定的。用公式可表示為：所求星期=(日期差+已知星期)-7#8226;商，即W2=(△D+W1)-7q。例如，已知3月6日是星期3，則3月29日是W2=(△D+W1)-7q=(29-6+3)-7×3=5，即星期五。

可是，公歷安排年月日的規(guī)定比較復(fù)雜，它的年和月所包含的天數(shù)并不是“7”的整數(shù)倍，大小月安排很不均勻。因此，推算星期序號(hào)就要根據(jù)公歷的規(guī)定來(lái)設(shè)計(jì)公式。

現(xiàn)行陽(yáng)歷規(guī)定：平年365天，閏年366天；一年12個(gè)月，1、3、5、7、8、10、12月為31天，4、6、9、11月為30天，平年的2月為28天，閏年的2月為29天；能被4整除的年份原則上是閏年，但是逢世紀(jì)之年（即公元年數(shù)最后兩位是“00”的年份），必須同時(shí)能被“4”和“400”整除才是閏年。例如，在1700～2099年這400年中，按4年1閏計(jì)算，400年內(nèi)應(yīng)安排100個(gè)閏年。但是，其中1700、1800、1900這3個(gè)世紀(jì)年雖然能被4整除，但不能被400整除，因此這3年都不是閏年，即1700～2099這400年只安排97個(gè)閏年。“公歷”平均歷年為400－97×365＋366×97400＝365＋97400＝365.2425(天)，即每400年97閏。公歷年比回歸年365.2422天多0.0003天，大約3333年就多1天。

下面，對(duì)L3公式的年月日各項(xiàng)分別展開(kāi)進(jìn)行分析和討論。

(1)對(duì)日期項(xiàng)建立公式，在I3輸入：“=E3”。在世紀(jì)、年份、月份相同的條件下，每月的各日序號(hào)號(hào)成公差為“1”的等差數(shù)列，各星期序號(hào)也成公差為“1”的等差數(shù)列且7日為一循環(huán)周期，兩者相互對(duì)應(yīng)相互依存并同步變化。例如，2011.03.01是星期“5”，則2011.03.02是星期“6”，余類(lèi)推。任意日期的星期用公式可表示為W2=(△D+W1)-7q。因此，日期序號(hào)引用E3單元格保存的數(shù)據(jù)。

(2)對(duì)月份各項(xiàng)建立公式，在H3輸入：“=IF(D3<3，INT(26*(D3+12+1)/10)，INT(26*(D3+1)/10))”。因?yàn)?月與2月之間相差2～3天，且2月的天數(shù)在平年只有28天，在閏年則為29天。因此，公式將1、2月分別作為上一年的13、14月(即“D3+12”)來(lái)計(jì)算，2月的最后一天作為上一年年末。在3～14這12個(gè)月之間向前跨越月份時(shí)，如果某月是大月，有31天，公式返回的結(jié)果將使下月比某月增加數(shù)值“3”；反之，如果某月是小月，有30天，則返回的結(jié)果將使下月比某月增加數(shù)值“2”，如表1的H3:H7。

假設(shè)每月有28天，4月1日是星期“5”，則每月1日都是星期“5”。實(shí)際上，4月是30天，比28天多2天，則5月1日實(shí)際向前推移了2天，是星期“5+2”，即星期“日”；同理，5月是31天，比28天多3天，則6月1日實(shí)際向前推移了3天，是星期“7+3”，即星期“三”。也就是“大月的下月星期序數(shù)要加上‘3’，小月的下月星期序數(shù)要加上‘2’”。這樣就使跨月份之間的日期與星期的銜接保持連續(xù)并同步變化。

(3)對(duì)年的后兩位建立公式，在G3輸入“=IF(D3<3，C3-1+INT((C3-1)/4)，C3+INT(C3/4))”。公式將1、2月分別作為上一年(即“C3-1”)的13、14月來(lái)計(jì)算。公式使年的后兩位每增加1年，其計(jì)算結(jié)果就增加數(shù)值“1”，即平年“364+1”天；能被4整除的年份其計(jì)算結(jié)果就增加數(shù)值“2”，即4年1閏，閏年“364+2”天。在100年中，年的后兩位有24年能被4整除(“00”年視為不能被4整除)，即100年有24閏年，則400年有“96”閏年。這樣就使跨年份之間的日期與星期的銜接保持連續(xù)并同步變化。

(4)對(duì)年的前兩位建立公式，在F3輸入：“INT(B3/4)－2*B3”。公式使年的前兩位數(shù)每增加數(shù)值“1”，計(jì)算結(jié)果就減少數(shù)值“2”；如果年份的前兩位數(shù)能被“4”整除，計(jì)算結(jié)果只減少數(shù)值“1”，即每400年加1閏。它與年的后兩位相結(jié)合，每400年有“96+1”個(gè)閏年。

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

I3-1”。該公式引用了中間變量F3、G3、H3、I3，同時(shí)又間接引用了B3、C3、D3、E3變量。

(6)在K3輸入“7”。

(7)在L3輸入“=MOD(J3，K3)”。公式將J3作為被除數(shù)，“7”為除數(shù)，余數(shù)就是星期序號(hào)（其中余數(shù)0代表星期日，1代表星期一，2代表星期二，余類(lèi)推）。

因?yàn)楣?年(公元紀(jì)年沒(méi)有0年，我們約定0年為-1年)12月31日恰是星期日，則自該日至某日的天數(shù)之和除以“7”的余數(shù)就是某日的星期序號(hào)。蔡勒根據(jù)公歷對(duì)世紀(jì)、年、月、日的規(guī)定，運(yùn)用數(shù)學(xué)定律和余數(shù)定理將公元元年至某日的天數(shù)之和用含有年月日各項(xiàng)的多項(xiàng)式來(lái)表示，再進(jìn)行綜合歸納并項(xiàng)簡(jiǎn)化，消去能被“7”整除的項(xiàng)，得到最簡(jiǎn)的星期與年月日之間的函數(shù)關(guān)系式，我們稱(chēng)之為蔡勒公式。

由表1可以看出，星期與年月日的函數(shù)關(guān)系還有如下規(guī)律：

假設(shè)一年有364天，能被7整除，則每年相對(duì)應(yīng)日期的星期相同，以此作標(biāo)準(zhǔn)，則(3月1日為歲首，下一年的2月最后一天為本年的歲末)：

(1)若某年是平年，有“364+1”天，則下一年相對(duì)應(yīng)月份日期的星期原則上要加上“1”；

(2)若某年的下一年恰逢閏年，有“364+2”天，則閏年對(duì)應(yīng)月份日期的星期要加上“2”；

(3)年份每增加100年，則對(duì)應(yīng)月份日期的星期原則上要加上“5”；

(4)若某年增加100年恰逢閏年，則閏年對(duì)應(yīng)月份日期的星期要加上“6”；

(5)滿400年對(duì)應(yīng)日期的星期序號(hào)完全相同，即星期序號(hào)每400年為一循環(huán)周期。

因?yàn)楣?2月31日是星期日，根據(jù)上述規(guī)律，可以推算出：

如果不考慮未來(lái)公歷與回歸年之間的誤差，則公元400，800，1200，…，400n，…等能被400整除的世紀(jì)年的12月31日均是星期日；

2010.12.31比2000.12.31多8平年加2閏年，根據(jù)余數(shù)定理，2010.12.31應(yīng)在2000.12.31星期“日”的基礎(chǔ)上加上(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基礎(chǔ)上多1平年加1閏年，應(yīng)是星期(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進(jìn)行等量代換，使之變形為：

“＝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＋I(xiàn)NT((VALUE(MID(A3，3，2))－1)/4)＋I(xiàn)NT(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))＋I(xiàn)NT(VALUE(MID(A3，3，2))/4)＋I(xiàn)NT(26*(VALUE(MID(A3，6，2))＋1)/10)＋VALUE(MID(A3，9，2))－1，7))”。

上述公式?jīng)]有中間變量，是蔡勒公式轉(zhuǎn)換成計(jì)算機(jī)能夠識(shí)別和執(zhí)行的完整表述形式，因此可以刪除B:K列，使工作表更簡(jiǎn)潔。

從上述操作可知，EXCEL函數(shù)具有強(qiáng)大的數(shù)據(jù)處理功能，尤其是函數(shù)的多層嵌套，可與編程相媲美。只要我們經(jīng)常學(xué)習(xí)和研究EXCEL函數(shù)，經(jīng)常上機(jī)操作練習(xí)，在本文對(duì)函數(shù)運(yùn)用的示例和剖析的基礎(chǔ)上，根據(jù)具體情況和要求，舉一反三，發(fā)散思維，創(chuàng)造性的變通和運(yùn)用EXCEL函數(shù)，解決生活和工作中的實(shí)際問(wèn)題。