几种Excel算法研究

摘要：从输入正确的身份证号码推算出年龄大小，给不同年龄段的单元格设置不同的颜色，以最快最轻松的方法，输入性别，数字的技巧化输入，根据单元格颜色排序。

关键词：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，该函数返回颜色索引值。可以根据getColorIndex函数，给单元格颜色排序。单击“升序排序”按纽就可。