LEN 函数返回文本字符串中的字符数。
语法：LEN(text)
Text 是要查找其长度的文本。空格将作为字符进行计数。
MOD 函数返回两数相除的余数。结果的正负号与除数相
同。
语法：MOD(number,divisor)
Number 为被除数；Divisor为除数。
MID 函数返回文本字符串中从指定位置开始的特定数目
的字符，该数目由用户指定。
语法：MID(text,start_num,num_chars)
Text 为包含要提取字符的文本字符串；Start_num为文本
中要提取的第一个字符的位置。文本中第一个字符的
start_num 为1，以此类推；Num_chars指定希望MID从文本
中返回字符的个数。
(2)实例分析
为了适应上述情况，必须设计一个能够适应两种身份编码
的性别计算公式，在D2单元格中输入
“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女
"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”。回车后即可
在单元格获得该职工的性别，而后只要把公式复制到D3、D4
等单元格，即可得到其他职工的性别。
为了便于大家了解上述公式的设计思路，下面简单介绍一
下它的工作原理：该公式由三个IF函数构成，其中
“IF(MOD(MID(C2,15,1),2)=1,"男","女")”和
“IF(MOD(MID(C2,17,1),2)=1,"男","女")”作为第一个函数
的参数。公式中“LEN(C2)=15”是一个逻辑判断语句，LEN 函
数提取C2等单元格中的字符长度，如果该字符的长度等于15，
则执行参数中的第一个IF函数，否则就执行第二个IF 函数。
在参数“IF(MOD(MID(C2,15,1),2)=1,"男","女")”中。
MID 函数从C2 的指定位置(第15位)提取1个字符，而MOD 函
数将该字符与2 相除，获取两者的余数。如果两者能够除尽，
说明提取出来的字符是0(否则就是1)。逻辑条件
43
“MOD(MID(C2,15,1),2)=1”不成立，这时就会在D2 单元格中
填入“女”，反之则会填入“男”。
如果LEN 函数提取的C2等单元格中的字符长度不等于
15，则会执行第2个IF函数。除了MID 函数从C2 的指定位置
(第17 位，即倒数第2 位)提取1个字符以外，其他运算过程
与上面的介绍相同。
2.出生日期输入
(1)函数分解
CONCATENATE 函数将几个文本字符串合并为一个文本字
符串。
语法：CONCATENATE(text1,text2,...)
Text1,text2,...为1～30个要合并成单个文本项的文本
项。文本项可以为文本字符串、数字或对单个单元格的引用。
(2)实例分析
与上面的思路相同，我们可以在E2单元格中输入公式
“=IF(LEN(C2)=15,CONCATENATE("19",MID(C2,7,2),"年
",MID(C2,9,2),"月",MID(C2,11,2),"日
"),CONCCTENCTE(MID(C2,7,4),"年",MID(C2,11,2),"月
",MID(C2,13,2),"日"))”。其中“LEN(C2)=15”仍然作为逻
辑判断语句使用，它可以判断身份证号码是15位的还是18
位的，从而调用相应的计算语句。
对15位的身份证号码来说，左起第7 至12 个字符表示出
生年、月、日，此时可以使用MID 函数从身份证号码的特定位
置，分别提取出生年、月、日。然后用CONCATENATE 函数将提
取出来的文字合并起来，就能得到对应的出生年月日。公式中
“19”是针对早期身份证号码中存在2000年问题设计的，它
可以在计算出来的出生年份前加上“19”。对“18”位的身份
证号码的计算思路相同，只是它不存在2000年问题，公式中
不用给计算出来的出生年份前加上“19”。
注意：CONCATENATE函数和MID函数的操作对象均为文本，
所以存放身份证号码的单元格必须事先设为文本格式，然后再
输入身份证号。
3.职工信息查询
Excel 提供的“记录单”功能可以查询记录，如果要查询
人事管理工作表中的某条记录，然后把它打印出来，必须采用
下面介绍的方法。
(1)函数分解
INDEX 函数返回数据清单或数组中的元素值，此元素由行
序号和列序号的索引值给定。
INDEX 函数有两种语法形式：数组和引用。数组形式通常
返回数值或数值数组，引用形式通常返回引用。当函数INDEX
的第一个参数为数组常数时，使用数组形式。
语法1(数组形式)：INDEX(array,row_num,column_num)
Array 为单元格区域或数组常量。如果数组只包含一行或
一列，则相对应的参数row_num 或column_num为可选。如果
数组有多行和多列，但只使用row_num 或column_num，函数
INDEX 返回数组中的整行或整列，且返回值也为数组；Row_num
为数组中某行的行序号，函数从该行返回数值。如果省略
row_num，则必须有column_num；Column_num 为数组中某列的
列序号，函数从该列返回数值。如果省略column_num，则必
须有row_num。
语法2(引用形式)：
INDEX(reference,row_num,column_num,area_num)
Reference 表示对一个或多个单元格区域的引用。如果为
引用输入一个不连续的区域，必须用括号括起来。如果引用中
的每个区域只包含一行或一列，则相应的参数row_num 或
column_num分别为可选项；Row_num引用中某行的行序号，函
数从该行返回一个引用；Column_num引用中某列的列序号，
函数从该列返回一个引用；Area_num 选择引用中的一个区域，
并返回该区域中row_num 和column_num的交叉区域。选中或
输入的第一个区域序号为1，第二个为2，以此类推。如果省
略area_num，函数INDEX使用区域1。
MATCH 函数返回在指定方式下与指定数值匹配的数组中
元素的相应位置。
语法：MATCH(lookup_value,lookup_array,match_type)
Lookup_value为需要在数据表中查找的数值；
Lookup_value为需要在Look_array 中查找的数值；
Match_type为数字-1、0或1。
(2)实例分析
如果上面的人事管理工作表放在Sheet1 中，为了防止因
查询操作而破坏它(必要时可以添加只读保护)，我们可以打开
另外一个空白工作表Sheet2，把上一个数据清单中的列标记
复制到第一行。假如你要以“身份证号码”作为查询关键字，
就要在C2 单元格中输入公式
“=INDEX(Sheet1!C2:C600,MATCH( SC S5,Sheet1! SC S2: SC
S600,0),1)”。其中的参数“ SC S5”引用公式所在工作表中
的C5 单元格(也可以选用其他单元格)，执行查询时要在其中
输入查询关键字，也就是待查询记录中的身份证号码。参数
“Sheet1!C2:C600”设定INDEX 函数的查询范围，引用的是数
据清单C 列的所有单元格。MATCH函数中的参数“0”指定它
查找“Sheet1! SC S2: SC S600”区域中等于 SC S5的第一
个值，并且引用的区域“Sheet1! SC S2: SC S600,0”可以按
任意顺序排列。
44
上面的公式执行数据查询操作时，首先由MATCH 函数在
“Sheet1! SC S2: SC S600”区域搜索，找到“ SC S5”单元
格中的数据在引用区域中的位置(自上而下第几个单元格)，从
而得知待查询数据在引用区域中的第几行。
接下来INDEX 函数根据MATCH 函数给出的行号，返回
“Sheet1!C2:C600”区域中对应行数单元格中的数据。假设其
中待查询的“身份证号码”是“3234567896”，它位于
“Sheet1! SC S2: SC S600”区域的第三行，MATCH函数就会
返回“3”。接着INDEX函数返回“Sheet1!C2:C600”区域中
行数是“3”的数据，也就是“3234567896”。
然后，我们将光标放到C2单元格的填充柄上，当十字光
标出现以后向右拖动，从而把C2 中的公式复制到D2、E2 等单
元格(然后再向左拖动，以便把公式复制到B2、A2单元格)，
这样就可以获得与该身份证号对应的性别、籍贯等数据。
注意：公式复制到D2、E2等单元格以后，INDEX函数引
用的区域就会发生变化，由C2:C600 变成D2:D600、E2:E600
等等。但是MATCH 函数返回的(相对)行号仍然由查询关键字给
出，此后INDEX 函数就会根据MATCH 函数返回的行号从引用区
域中找到数据。
在Sheet2工作表中进行查询时只要在查询输入单元格中
输入关键字，回车后即可在工作表的C2单元格内看到查询出
来的身份证号码。如果输入的身份证号码关键字不存在或输入
错误，则单元格内会显示“#N/A”字样。
4.职工性别统计
(1)函数分解
COUNTIF 函数计算区域中满足给定条件的单元格的个数。
语法：COUNTIF(range,criteria)
Range 为需要计算其中满足条件的单元格数目的单元格
区域；Criteria为确定哪些单元格将被计算在内的条件，其
形式可以为数字、表达式或文本。
(2)实例分析
假设上面使用的人事管理工作表中有599 条记录，统计职
工中男性和女性人数的方法是：选中单元格D601(或其他用不
上的空白单元格)，统计男性职工人数可以在其中输入公式
“="男"&COUNTIF(D2:D600,"男")&"人"”；接着选中单元格
D602，在其中输入公式“="女"&COUNTIF(D2:D227,"女")&"人
"”。回车后即可得到“男399人”、“女200人”。
上式中D2:D600 是对“性别”列数据区域的引用，实际使
用时必须根据数据个数进行修改。“男”或“女”则是条件判
断语句，用来判断区域中符合条件的数据然后进行统计。“&”
则是字符连接符，可以在统计结果的前后加上“男”、“人”
字样，使其更具有可读性。
5.年龄统计
在人事管理工作中，统计分布在各个年龄段中的职工人数
也是一项经常性工作。假设上面介绍的工作表的E2:E600 单元
格存放职工的工龄，我们要以5 年为一段分别统计年龄小于
20 岁、20至25 岁之间，一直到55 至60 岁之间的年龄段人数，
可以采用下面的操作方法。
(1)函数分解
FREQUENCY函数以一列垂直数组返回某个区域中数据的
频率分布。
语法：FREQUENCY(data_array,bins_array)
Data_array 为一数组或对一组数值的引用，用来计算频
率。如果data_array中不包含任何数值，函数FREQUENCY 返
回零数组；Bins_array为间隔的数组或对间隔的引用，该间
隔用于对data_array中的数值进行分组。如果bins_array
中不包含任何数值，函数FREQUENCY 返回data_array中元素
的个数。
(2)实例分析
首先在工作表中找到空白的I 列(或其他列)，自I2 单元
格开始依次输入20、25、30、35、40...60，分别表示统计年
龄小于20、20 至25之间、25 至30之间等的人数。然后在该
列旁边选中相同个数的单元格，例如J2：J10 准备存放各年龄
段的统计结果。然后在编辑栏输入公式
“=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”，按
下Ctrl+Shift+Enter组合键即可在选中单元格中看到计算结
果。其中位于J2单元格中的结果表示年龄小于20岁的职工人
数，J3单元格中的数值表示年龄在20 至25之间的职工人数
等。
6.名次值统计
在工资统计和成绩统计等场合，往往需要知道某一名次
(如工资总额第二、第三)的员工的工资是多少。这种统计的操
作方法如下。
(1)函数分解
LARGE 函数返回数据集中第K 个最大值。使用此函数可以
根据相对标准来选择数值。
语法：LARGE(array,k)
Array 为需要从中选择第K 个最大值的数组或数据区域；
K 为返回值在数组或数据单元格区域中的位置(从大到小排)。
SMALL 函数返回数据集中第K 个最小值。使用此函数可以
返回数据集中特定位置上的数值。
语法：SMALL(array,k)
45
Array 为需要找到第K 个最小值的数组或数字型数据区
域；K为返回的数据在数组或数据区域里的位置(从小到大)。
(2)实例分析
假设C2:C688 区域存放着员工的工资，首先在D列选取空
白单元格D3，在其中输入公式“=LARGE(C2:C688,D2)”。其
中D2作为输入名次变量的单元格，如果你在其中输入3，公
式就可以返回C2:C688 区域中第三大的数值。
如果我们把上述公式修改为“=SMALL(C2:C688,D1)”，然
后在D1单元格中输入6，就可以获得C2:C688区域倒数第六
(小)的数值。
为方便起见，你可以给C2:C688 区域定义一个名称“职工
工资”。此后可以把上述公式修改为“=LARGE(职工工
资,D2)”或“=SMALL(职工工资,D1)”。
7.位次阈值统计
与上例相似，在工资统计和成绩统计等场合，需要知道排
名达到总体的前1/3 的工资总额或分数(称为“阈值”)是多
少。这种统计的操作方法如下：
(1)函数分解
PERCENTILE 函数返回区域中数值的第K 个百分点的值。
可以使用此函数来建立接受阈值。
语法：PERCENTILE(array,k)
Array 为定义相对位置的数组或数据区域；K为0 到1 之
间的百分点值，包含0和1。
(2)实例分析
假设C2:C200 区域存放着学生的考试成绩，首先在D列选
取空白单元格D3，在其中输入公式
“=PERCENTILE(C2:C200,D2)”。其中D2 作为输入百分点变量
的单元格，如果你在其中输入0.33，公式就可以返回名次达
到前1/3 所需要的成绩。
五、函数应用案例──管理计算
企业、学校等单位均存在许多管理计算问题，例如计算一
个学期有几个授课日、企业在多少个工作日之后交货等等。下
面介绍有关问题的几种计算方法。
1.授课日数
(1)函数分解
NETWORKDAYS 函数专门用于计算两个日期值之间完整的
工作日数值。这个工作日数值将不包括双休日和专门指定的其
他各种假期。
语法：NETWORKDAYS (Start_date,End_date,Holidays)
Start_date表示开始日期；End_date为终止日期，
Holidays表示作为特定假日的一个或多个日期。这些参数值
既可以手工输入，也可以对单元格的值进行引用。
(2)实例分析
假设新学期从2003 年9 月1 日开始到2004 年1 月15 日
结束，希望知道本学期有多少个授课日，也就是排除双休日和
国家法定假日外的授课工作日。这就是计算授课日数或工作日
数的问题。
首先打开一个空白工作表，在A1、B1、C1单元格输入“开
学时间”、“结束时间”、“法定节日”，然后在其下面的单
元格内输入“2003-9-1”、“2004-1-15”、“2003-10-1”
“2003-10-2”、“2003-10-3”和“2004-1-1”(后四项必须
在C 列的“法定假日”下)。
接着可以选中D2 单元格，输入公式
“=NETWORKDAYS(A2,B2,C2:C5)”。公式中A2 引用的是学期
(或工作)的开始日期，B2引用的是学期结束的日期，C2:C5
区域引用的是作为法定假日的多个日期。输入结束回车即可获
得结果95，即2003 年9 月1 日到2004 年1 月15日，排除四
个法定假日后的实际授课日是95 天。
2.折旧值计算
无论单位还是家庭，许多固定资产和耐用消费品都存在折
旧问题，随着使用时间的延长，其残值在不断减少。假设某单
位有一批2000年购进原价8 500 元/每台的电脑，预计使用寿
命6 年，寿命期结束时的资产残值约为1 000元，要求使用第
二年内的折旧值。
(1)函数分解
DB 函数使用固定余额递减法，计算一笔资产在给定期间
内的折旧值。
语法：DB(cost,salvage,life,period,month)
Cost 为资产原值；Salvage为资产在折旧期末的价值(也
称为资产残值)；Life为折旧期限(有时也称作资产的使用寿
命)；Period为需要计算折旧值的期间。Period必须使用与
life 相同的单位；Month为第一年的月份数，如省略，则假设
为12。
(2)实例分析
为了在参数改变以后仍能进行计算，我们打开一个空白工
作表，在A1、B1、C1、D1、E1单元格输入“电脑原值”、“资
产残值”、“使用寿命”、“折旧时间”和“折旧值”，然后
46
在其下面的单元格内输入“8500”、“1000”、“6”、“2”。
然后选中E2单元格在其中输入公式“=DB(A2,B2,C2,D2)”，
回车后即可得到结果“￥1,785.00”，就是说使用期第二年的
折旧值为1 785 元。如果你要计算其他设备或财产的折旧值，
只需改变A2、B2、C2、D2单元格内的数值即可。
3.客流均衡度计算
假设某超市周一到周六的客流人数是16 359、17 254、
18 654、15 398、21 689和220 867，总经理需要知道这种情
况下的客流分布是否平坦。可以按如下方法计算：
(1)函数分解
KURT 函数返回数据集的峰值。峰值反映与正态分布相比
某一分布的尖锐度或平坦度。正峰值表示相对尖锐的分布。负
峰值表示相对平坦的分布。
语法：KURT(number1,number2,...)
Number1,number2,...是用于计算峰值的1～30个参数。
也可以不使用这种用逗号分隔参数的形式，而用单个数组或数
组引用的形式。
(2)实例分析
打开一个空白工作表，在A1单元格中输入“一周客流统
计”，然后将上述数据依次输入A2、A3等单元格。然后选中
A8 单元格，在其中输入公式“=KURT(D2:D7)”，回车即可获
得结果“-1.719218897”，这说明超市的客流分布与正态分布
相比是相对平坦的。假如星期天搞特价促销，客流增加到了
50 867 人，则计算结果就会变为“5.45379941”。说明超市
的客流分布与正态分布相比比较尖锐了，特价促销对客流的影
响还是非常大的。
需要指出的是，KURT函数在教育统计等领域也有广泛用
途，假如把函数引用的区域修改为许多分数的集合，就可以知
道考试成绩的分布是否尖锐或平坦。
4.销售额预测
假设某超市周一到周日的日销售额分别为13、17、16、
15、19、21和22(万元)，总经理需要预测今后一周内的日销
售额的最高值和最低值。可以按如下方法进行预测：
(1)函数分解
TREND 函数返回一条线性回归拟合线的值。即找到适合已
知数组known_y's 和known_x's的直线(用最小二乘法)，并返
回指定数组new_x's 在直线上对应的y值。
语法：TREND(known_y's,known_x's,new_x's,const)
Known_y's 是关系表达式y=mx+b中已知的y 值集合；
Known_x's 是关系表达式y=mx+b中已知的可选x 值集合；
New_x's为需要函数 TREND返回对应y值的新x值；Const为
一逻辑值，用于指定是否将常量b 强制设为0。
(2)实例分析
首先要打开一个空白工作表，在A1 单元格中输入“日销
售额”，然后将上述数据依次输入A2、A3 至A8 单元格。然后
选中B2 至B8 区域，在Excel 的编辑栏输入公式
“=TREND(A2:A8)”，回车即可在B2至B8区域获得7个结果，
其中最高销售额为21.64 万元，最低销售额为13.5万元。
与KURT 函数一样，TREND函数可以用于教育统计中的学
生入学数的峰值和低谷，铁路运输领域的客流高峰和低谷等的
预测。
5.客流与营业额的相关分析
(1)函数分解
CORREL 函数返回单元格区域array1 和array2之间的相
关系数。使用相关系数可以确定两种属性之间的关系。
语法：CORREL(array1,array2)
Array1 为第一组数值单元格区域；Array2为第二组数值
单元格区域。
(2)实例分析
假设一个超市要分析客流量与营业额是否相关。首先运行
Excel 打开一个空白工作表，在A1至A31单元格输入八月份
的每日客流人数，然后在B1 至B31 输入八月份每日的营业额，
再将上述两个区域的名称定义为“日客流人数”和“日营业
额”。
接下来就可以选中工作表中的某个空白单元(例如B32)，
作为存储运算结果的位置。在Excel 的编辑栏输入公式
“=CORREL(日客流人数,日营业额)”，回车后即可在公式所在
单元格看到相关系数的计算结果。
上式中CORREL函数返回“日客流人数”和“日营业额”
两个数据集合的相关系数，实际应用中必须根据要分析的数据
集合对引用区域进行修改。
与其他计算不同，CORREL函数计算出的相关系数必须进
行分析，才能得出两个数值之间是否相关的结论。统计理论根
据各种因素(如“日客流人数”和“日营业额”)相互影响的
关系，把相关分为正相关、负相关和零相关三种类型。所谓正
相关就是两个因素的变化方向相同，即同时变大或变小，例如
气温和冷饮销量就是正相关；负相关就是两个因素的变化方向
相反，即一个变大(小)另一个变小(大)，例如气温上升和羽绒
服销量就是负相关；零相关就是两个因素的变化方向无规律，
即不存在相互之间影响的情况，例如学生的考试成绩和面粉的
销量就是零相关。
47
为了帮助不太熟悉统计理论的用户掌握CORREL函数的使
用，这里使用相关程度分析的理论修改公式“=CORREL(日客流
人数,日营业额)”，使之成为下面这种形式，从而更加直观的
给出两列数据相关程度的结论。
“=IF((ABS(CORREL(日客流人数,日营业额)))<=0.3,"相
关程度低",IF((ABS(CORREL(日客流人数,日营业
额)))<=0.5,"相关程度一般",IF((ABS(CORREL(日客流人数,
日营业额)))<=0.7,"相关程度较高",IF((ABS(CORREL(日客流
人数,日营业额)))<=0.9,"相关程度高",IF((ABS(CORREL(日
客流人数,日营业额)))<=1,"相关程度极高")))))”
公式中的“CORREL(日客流人数,日营业额)”部分还是计
算日客流人数和日营业额两列数据的相关系数。由于这里只需
要了解相关程度，所以使用ABS 函数返回相关系数的绝对值。
整个公式中由左往右的下一个IF语句就是上一个IF语句的参
数。例如第一个逻辑判断表达式“(ABS(CORREL(日客流人数,
日营业额)))<=0.3”为“真”(成立)，则公式所在单元格就会
被填入“相关程度低”；如果第一个逻辑判断表达式
“(ABS(CORREL(日客流人数,日营业额)))<=0.3”为
“假”(不成立)，则计算第二个IF 语句“IF((ABS(CORREL(日
客流人数,日营业额)))<=0.5”；以此类推直至计算结束。
