運用EXCEL函數(shù)推算星期序號

蔣洪力 唐山市開灤一中

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

●制作星期序號推算表

蔡勒公式可表述為：“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就是星期序號。

那么，如何將上述公式轉換為計算機能夠識別和處理的語言呢？

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

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

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

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

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

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

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

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

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

第四步，建立推算星期序號的公式，并引用中間變量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公式中函數(shù)的語法和功能如下：

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

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

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

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

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

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

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

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

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

下面，對L3公式的年月日各項分別展開進行分析和討論。

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

(2)對月份各項建立公式，在H3輸入：“=IF(D3<3，INT(26*(D3+12+1)/10)，INT(26*(D3+1)/10))”。因為1月與2月之間相差2～3天，且2月的天數(shù)在平年只有28天，在閏年則為29天。因此，公式將1、2月分別作為上一年的13、14月(即“D3+12”)來計算，2月的最后一天作為上一年年末。在3～14這12個月之間向前跨越月份時，如果某月是大月，有31天，公式返回的結果將使下月比某月增加數(shù)值“3”；反之，如果某月是小月，有30天，則返回的結果將使下月比某月增加數(shù)值“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”，即星期“三”。也就是“大月的下月星期序數(shù)要加上‘3’，小月的下月星期序數(shù)要加上‘2’”。這樣就使跨月份之間的日期與星期的銜接保持連續(xù)并同步變化。

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

(4)對年的前兩位建立公式，在F3輸入：“INT(B3/4)－2*B3”。公式使年的前兩位數(shù)每增加數(shù)值“1”，計算結果就減少數(shù)值“2”；如果年份的前兩位數(shù)能被“4”整除，計算結果只減少數(shù)值“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作為被除數(shù)，“7”為除數(shù)，余數(shù)就是星期序號（其中余數(shù)0代表星期日，1代表星期一，2代表星期二，余類推）。

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

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

假設一年有364天，能被7整除，則每年相對應日期的星期相同，以此作標準，則(3月1日為歲首，下一年的2月最后一天為本年的歲末)：

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

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

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

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

(5)滿400年對應日期的星期序號完全相同，即星期序號每400年為一循環(huán)周期。

因為公元元年12月31日是星期日，根據(jù)上述規(guī)律，可以推算出：

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

2010.12.31比2000.12.31多8平年加2閏年，根據(jù)余數(shù)定理，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))”。

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

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