摘要：從輸入正確的身份證號碼推算出年齡大小，給不同年齡段的單元格設置不同的顏色，以最快最輕松的方法，輸入性別，數字的技巧化輸入，根據單元格顏色排序。

關鍵詞：Excel算法；虛歲；實歲；底紋顏色；條件格式；單元格格式；排序

中圖分類號：TP317文獻標識碼：A文章編號：1009-3044(2008)09-11726-02

Research on Several Excel Algorithms

LI Ling-ling

(The Software Department of Panyu Institute Tec， Guangzhou 511483， China)

Abstract: Calculate the age according the number of identity card， setting the different color to the different unit grid， input sex with easiest method， input the figure with skill， taxis with color of the unit grid.

Key words: Excel arithmetic; virtual age; real age; shading color; conditional format; unit grid format

因為教學與工作的關系，我接觸到Excel，通過精心鉆研，摸索出幾種算法，下面是幾個案例：

(1)從輸入正確的身份證號碼推算出年齡大小。

從身份證號碼推算年齡是我們經常遇到的一個問題，用人工計算每一個人的年齡，當然并不復雜，但如果是有一張表里面有很多人的身份證號碼呢？這就只能利用Excel的公式了。打開Excel的工作簿，先輸入必要的數據，比如姓名列（本例為A列）和身份證號碼列（本例為B列），為避免出現科學計數，比如輸入“110108197605198925”時變成“1.10108E+17”，注意將身份證號一列的單元格格式數字設為文本。C列存放中間數據即“掐頭去尾后的數據”。C3輸入公式：=IF(LEN(B3)=18，MID(B3，7，8)，IF(LEN(B3)=15，\"19\"MID(B3，7，6)，\"身份證號碼不對，請重新輸入\"))。本例中，B3文本為110108197605198925，掐頭110108去尾8925后剩下19760519。

如果B3為110108197605198925（18位數），掐頭去尾后的數據為 19760519。如果為110108760519892（15位數）掐頭去尾后的數據為760519，再加19，也是19760519。否則，打印出：身份證號碼不對，請重新輸入。然后，選中C3，當光標變成黑色十字架時，單擊右鍵拖至Cn即可復制其他行的結果。注意，現在的身份證號碼一般都是18位數，但也有15位數的；如果為18位，LEN(B3)=18，則從第7位開始，取8位數字，即MID(B3，7，8)；如果為15位，LEN(B3)=15，則從第7位開始，取6位數字，即MID(B3，7，6)。15位的身份證肯定是老身份證，所以要在數字前面加上“19”。D列存放的是“生日”，在D3輸入公式：=MID(C3，1，4) \"-\" MID(C3，5，2) \"-\" MID(C3，7，2)按此公式可以推出生日為：1976-05-19，然后，選中D3，當光標變成黑色十字架時，單擊右鍵拖至Dn即可復制其他行的結果。知道了生日，便可以算出年齡：本例中E列是虛歲，F列是實歲。在E3輸入計算虛歲的公式：=CEILING(DAYS360(D3，TODAY())/360，1)。然后，選中E3，當光標變成黑色十字架時，單擊右鍵拖至En即可復制其他行的結果。在F3輸入計算實歲的公式：=TRUNC(DAYS360(D3，TODAY())/360，0)。然后，選中F3，當光標變成黑色十字架時，單擊右鍵拖至Fn即可復制其他行的結果。我們只希望看到姓名、身份證號和年齡，其他列（如C列和D列）是中間數據，沒必要保留，可以隱藏起來，具體進行如下操作：選中“中間數據”如“C:D”列，單擊菜單“數據/組及分級顯示/組合”， 工作表上會顯示分級的1、2按鈕，只要單擊1按鈕即可隱藏“C:D”列，單擊2按鈕則恢復顯示。單擊1按鈕，將中間數據隱藏起來以后，選中需要復制的部分（比如姓名列、身份證號列和虛歲列及實歲列），可任意復制并粘貼到需要的地方。

(1)給不同年齡段的單元格設置不同的顏色。

為了讓電子表格看起來更豐富多彩，可以給單元格中不同數字的底紋設置不同的顏色。比如20-40間的數的底紋為一種顏色，40-60間的數的底紋為一種顏色，60-80間的數的底紋為另一種顏色，

選中欲設置底紋顏色的列，本例中選中F列，執行“格式→條件格式”命令，介于20到40之間：選中格式/圖案中一個合適的顏色，點擊“確定”，即可得結果。如果數據比較多，條件格式最多有三個，點擊“添加”按紐，出現第二個條件，同樣方法可以設定第三個條件。如圖示：

按圖示設計好單元格底紋顏色后，點擊“確定”，則可將20-40間單元格的底紋設為一種顏色，40-60間單元格的底紋設為另一種顏色，60-80間單元格的底紋為設又一種顏色。

(3)以最快最輕松的方法，輸入性別：輸入1得“男”，輸入0得“女”。

我們在給“性別”列輸文本時，要輸入很多“男”“女”字樣，如果數據很多，這也是很煩瑣的事情，為了簡化操作，比如輸入“1”時得“男”，輸入“0”時得“女”，這樣可以省不少事。本例中，在B列前插入一列“性別”，選中B列，單擊“格式>單元格”，打開“單元格格式”對話框，在“數字”選項卡中的“分類”列表框單擊“自定義”，然后，在“類型”文本框輸入：[=1]\"男\";[=0]\"女\"，“確定”后，在工作表“性別”列輸入“1”可輕松得“男”，輸入“0”可輕松得“女”。

(4)數字的技巧化輸入。

在輸入數字時，有時希望數字顯示得規范整齊，比如顯示成：“0001”、“0002”、“0003”……“0011”……的式樣。本例中，希望“編號”列顯示得規范整齊。具體操作如下：在“姓名”前插入“編號”列，選中A3:An，選中“單元格格式”，“數字”>“自定義”，在“類型”中輸入“0000”，在A3輸入“1”，自動顯示“0001”，在A4輸入“2”，自動顯示“0002”，后面的拖拉填充即可。在輸入數字時，希望單元格中的數字顏色能自動變化：如果是正數，自動顯示為綠色，如果是負數，自動顯示為紅色；要達到這個效果，只要將單元格中的“數字”設為“自定義”，“類型”欄敲入：[綠色]#，##0;[紅色]-#，##0。

偶爾，我們會碰到一列較長的數據，除了數字，還有一些空格，有必要刪掉這一列數字中的空白區，方法如下：Ctrl+G打開“定位”窗口，單擊“定位條件”，選擇“常量”，選中該選中的數據后，“復制”（可以用Ctrl+C鍵）到目標區域（連續區域）即可。

(5)根據單元格顏色排序。

上面提到給單元格底紋著色，那么怎樣根據單元格顏色來排序呢？其實很簡單，只要設計下面的程序，即只要如下操作：

打開“工具”/“宏”/Visual Basic編緝器，“插入”/“模塊”，敲入以下程序：

Option Explicit

Function getColorIndex(myCell As Range， Optional iArg As Integer = 1)

Application.Volatile True

Select Case iArg

Case 1

getColorIndex = myCell.Interior.ColorIndex

Case 2

getColorIndex = myCell.Font.ColorIndex

End Select

End Function

關閉后，在該工作簿中多了一個函數，點擊fx，可以找到函數：getColorIndex，該函數返回顏色索引值?？梢愿鶕etColorIndex函數，給單元格顏色排序。單擊“升序排序”按紐就可。