第
5
章Excel函数
5.1文 本 函 数
范例要求
打开工作簿“文本函数.xlsx”，完成以下操作。
1. Left函数和Find函数
在工作表“Sheet1”中，计算“行政区”列中的数据，效果如图5.1所示。★
图5.1Left、Find函数应用效果图
2. Right函数和Len函数
在工作表“Sheet1”中，计算“街道”列中的数据，效果如图5.2所示。★
图5.2Right、Len函数应用效果图
3. Mid函数和连字符“&”
使用Mid函数和连字符&计算“出生年月(连字符&)”列，效果如图5.3所示。★
图5.3Mid函数和连字符应用效果图
4. Value函数和Text函数
使用Mid函数、Value函数和Text函数计算“出生年月(text)”列，完成后效果如图5.4所示。★
图5.4Text函数应用效果图
使用Mid函数、Value函数和Text函数计算“出生季节”列，出生月份对应的季节如表5.1所示，完成后效果如图5.5所示。★★★
表5.1月份和季度对应关系
月份季节
1月，2月，12月冬季
911月秋季
68月夏季
35月春季
图5.5text函数应用效果图
相关知识
1. Left函数和Find函数
1) Left函数
Left函数以字符串的左侧为起始位置，返回指定数量的字符，函数语法如下：
Left(text,［num_chars］)
第一参数text为要提取的字符串或单元格引用。第二参数［num_chars］为可选参数，表示从第一参数的第1个字符开始，提取的字符数量，省略时默认提取一个字符，即提取字符串最左端的一个字符。第一参数为文本字符串时，需要用一对半角双引号将其包含。
2) Find函数
从单元格中提取字符串时，提取的起始位置或结束位置往往是不固定的，需要根据条件定位某个或某些关键字符，以此作为提取的条件。使用Find函数可以解决定位字符的问题。
Find函数用于在单元格或字符串中定位指定的字符或字符串，并返回其起始位置的值，该值从单元格的第一个字符算起。函数语法如下：
Find(find_text,within_text,［start_num］
第一参数find_text为必需参数，为要查找的文本。
第二参数within_text为必需参数，包含要查找文本的单元格引用或字符串。
第三参数start_num为可选参数，指定开始查找的位置。省略此参数时，默认其值为1。
无论第三参数是否为1，函数返回位置的值都以第二参数的第一个字符开始计算，所以一般省略第三参数。
3) 范例详解
“Left函数和Find函数”范例中C2单元格的公式为“=LEFT(B2,FIND("区",B2))”。先用Find函数求字符“区”在B2单元格中字符串的位置为3，然后用Left函数读取B2单元格中字符串前3个字符的值。
2. Right函数和Len函数
1) Right函数
Right函数以字符串的右侧为起始位置，返回指定数量的字符，函数语法如下：
Right(text,［num_chars］)
第一参数text为要提取的字符串或单元格引用。第二参数［num_chars］为可选参数，表示从第一参数的最后1个字符开始，提取的字符数量，省略时默认提取一个字符，即提取字符串最右端的一个字符。第一参数为文本字符串时，需要用一对半角双引号将其包含。
2) Len函数
Len函数用于读取字符串的字符长度，函数语法如下：
Len(text)
参数text表示需要计算长度的字符串或单元格引用。
3) 范例详解
“Right函数和Len函数”范例中D2单元格中的公式为“=RIGHT(B2,LEN(B2)-FIND("区",B2))”。先用Len函数读取B2单元格中字符串的长度(值为7)，减去字符“区”在字符串B2中的位置(值为3)，得到B2单元格中字符“区”之后的所有字符的长度(值为4)。再用Right函数读取B2单元格中字符串的最后4个字符。
3. Mid函数和连字符
1) Mid函数
相较于Left函数和Right函数只能从最左端和最右端提取字符串中的字符，Mid函数在提取字符串的应用中更为灵活，可以从字符串或单元格引用中的某个字符开始提取多长的字符串。函数语法如下：
Mid(text,start_num,num_chars)
第一参数text为要提取的字符串或单元格引用； 第二参数start_num用于指定文本中要提取的第一个字符的位置； 第三参数num_chars指定从文本中返回字符的个数。
2) 连字符&
在Excel中，连字符“&”可以连接文本类型或数值类型的数据，运算结果为文本类型。
3) 范例详解
“Mid函数和连字符”范例中F2单元格中的公式为“=MID(E2,7,4)&"年"&MID(E2,11,2)&"月"&MID(E2,13,2)&"日"”。首先来看“MID(E2,7,4)&"年"”，Mid(E2,7,4)表示从E2单元格的第7个字符开始，取4个字符长度，得到E2单元格中第7~10位的字符串(值为2003)，也就是身份证号中年份的数据信息； 通过连字符“&”，和字符“年”进行连接，得到“2003年”。之后的12月和22日也是一样的函数处理方法。
4. Value函数和Text函数
1) Value函数
Value函数可以将文本类型的数据转换为数值类型，方便计算。函数用法如下：
Value(text)
参数text为需要转换为数值类型的文本。
2) Text函数
Excel的自定义数字格式功能可以将单元格中的数值显示为自定义的格式，而Text函数也有相似的功能，可以将数值转换为按指定数字格式所表示的文本。
(1) Text函数的基本用法如下：
Text(value,format_text)
第一参数value，要转换为指定格式文本的数值，也可以是文本型数字。
第二参数format_text，用于指定格式代码，与单元格数字格式中的大部分代码基本相同。比如无法显示自定义格式中表示颜色的代码，如［红色］等。
除此之外，设置单元格格式与Text函数还有以下区别： 设置单元格的格式仅仅是数字显示外观的改变，其实质仍然是数值本身，不影响进一步的汇总计算，即得到的是显示的效果。使用Text函数可以将数值转换为带格式的文本，其实质已经是文本，不再具有数值的特性，即得到的是实际的效果。
(2) Text函数的格式代码。
Text函数的格式代码分为4个条件区段，各区段之间用半角分号间隔，默认情况的用法如下：
正数对应的文本； 负数对应的文本； 单元格的值为零时对应的文本;值为文本时对应的文本
图5.6Text函数的默认区间用法
用法如图5.6所示。
和自定义格式一样，除了默认的区间以外，用户还可以自定义条件区间，用法如下：
［条件1］“条件1对应的文本”;［条件2］“不满足条件1且满足条件2对应的文本”;不满足条件1和条件2对应的文本;单元格中的值为文本时对应的值。
用法如图5.7所示。
(3) Text函数的常用格式符号。
常用的符号有“0”和“#”，用法和自定义格式基本一样。
0： 占位符。当数据源为数值类型时，数量不足的需要补齐，如“000”返回的整数不能小于3位数字。当数据源为文本时，直接返回该文本。
#： 占位符。当数据源为数值类型时，数量不足的无须补齐。
用法如图5.8所示。
图5.7Text函数的自定义条件区间用法
图5.8Text函数的常用格式符号用法
3) 范例详解
“Value函数和Text函数”范例中，G2单元格中的公式为“=TEXT(VALUE(MID(E2,7,8)),"0000年00月00日")”。首先用Mid函数从E2单元格中的第7位开始，读取8位长度的字符串，然后用Value函数进行转换，最后用格式符号"0000年00月00日"将8位数中的1~4位对应"0000年"中的4个0，5~6位对应"00月"中的2个0，7~8位对应"00天"中的2个0。
“Value函数和Text函数”范例中，H2单元格中的公式为“=TEXT(TEXT(VALUE(MID(E2,11,2)),"［>=12］冬季;［>=9］秋季;0"),"［>=6］夏季;［>=3］春季;冬季")”。这个函数有2层嵌套，先看内层的Text函数“TEXT(VALUE(MID(E2,11,2)),"［>=12］冬季;［>=9］秋季;0")”，用Mid函数读取E2单元格中的11—12位(月份值的文本数据形式)，用Value函数转为数值型数据，根据条件区间进行判断： 月份>=12为冬季，12>月份>=9为秋季。由于在内层Text函数中没有第4参数“值为文本时对应的文本”，且第3参数为0，所以余下的月份值直接返回原值。1~12月经过内层Text函数计算后得到的结果如图5.9所示。
图5.9内层Text函数计算结果
用外层的Text函数“Text(内层Text函数计算结果,"［>=6］夏季;［>=3］春季;冬季")”再次进行条件区间判断，得到的结果如图5.10所示。对于内层Text函数计算得到的月份数值18，月份>=6为夏季，>6月份>=3为春季，余下的月份数值1—2对应冬季。对于内层Text函数计算得到的文本数据“秋季，秋季，秋季，冬季”，由于在外层Text函数中没有第4参数“值为文本时对应的文本”，所以文本字符直接返回原值。
图5.10外层Text函数计算结果
操作步骤
1. Left函数和Find函数
(1) 打开工作簿“文本函数.xlsx”。
(2) 在工作表“Sheet1”的C2单元格中，输入公式“=LEFT(B2,FIND("区",B2))”。完成输入后，按下回车键。鼠标放在C2单元格右下角，双击鼠标左键，在C2:C13单元格区域中完成公式填充。如图5.11所示。
图5.11自动填充公式
2. Right函数和Len函数
在工作表“Sheet1”的D2单元格中，输入公式“=RIGHT(B3,LEN(B3)-FIND("区",B3))”。完成输入后，按下回车键。
光标指向D2单元格右下角，双击鼠标左键，在D2:D13单元格区域中完成公式填充。
3. Mid函数和连字符“&”
在工作表“Sheet1”的F2单元格中，输入公式“=MID(E2,7,4)&"年"&MID(E2,11,2)&"月"&MID(E2,13,2)&"日"”。完成输入后，按下回车键。光标指向F2单元格右下角，双击鼠标左键，在F2:F13单元格区域中完成公式填充。
4. Value函数和Text函数
(1) 在工作表“Sheet1”的G2单元格中，输入公式“=TEXT(VALUE(MID(E2,7,8)),"0000年00月00日")”。完成输入后，按下回车键。光标指向G2单元格右下角，双击鼠标左键，在G2:G13单元格区域中完成公式填充。
(2) 在工作表“Sheet1”的H2单元格中，输入公式“=TEXT(TEXT(VALUE(MID(E2,11,2)),"［>=12］冬季;［>=9］秋季;0"),"［>=6］夏季;［>=3］春季;冬季")”。完成输入后，按下回车键。光标指向G2单元格右下角，双击鼠标左键，在G2:G13单元格区域中完成公式填充。
注意问题
1. 公式中符号的注意事项
Excel中，所有的非中文字符都是半角字符，括号成对出现。
2. 字节类文本函数
上面介绍的各类文本处理函数都是以字符为单位，在Excel中还可以字节为单元处理文本数据。在上面的函数后加字母B即可。例如： LeftB，FindB，LenB等。效果如图5.12所示。
图5.12字节类文本函数应用效果
5.2数 学 函 数
范例要求
打开工作簿“数学函数.xlsx”，完成以下操作。
1. 随机函数Rand和Randbetween★
在工作表“Sheet1”的单元格区域(A1:A10)，利用随机函数rand或randbetween生成［10,100)的随机整数。
2. Mod函数、Sqrt函数和Round函数★
在工作表“Sheet1”的单元格区域(F1:F10)，利用除法运算符、Int函数、平方根函数和Round函数计算各算式的商的平方根，保留2位小数。在工作表“Sheet1”的单元格区域(I1:I10)，利用mod函数计算余数。
相关知识
1. 随机函数Rand和Randbetween
随机数是一个事先不确定的数，使用Rand和Randbetween函数均能生成随机数。
Rand函数不需要参数，可以随机生成一个大于等于0且小于1的小数，且产生的随机小数不重复。
Randbetween函数的语法为： Randbetween(bottom,top)
两个参数分别为下限和上限，用于指定产生随机数的范围。生成一个大于等于下限值且小于等于上限值的整数。
当用户在工作表中按“F9”(笔记本计算机是“Fn+F9”)键或编辑单元格等操作时，都会引发随机函数重新计算，函数会返回新的随机数。
2. Mod函数、Sqrt函数和Round函数
Mod函数用来返回两数相除后的余数，用法如下：
Mod(number,divisor)
其中，number是被除数，divisor是除数。
Sqrt函数用于计算某个数的平方根，用法如下：
Sqrt(number)
其中，number是需要计算平方根的数。
Round函数用于数字的四舍五入。用法如下：
Round(number,digits)
其中，number表示进行四舍五入的数。digits用于指定保留的位数，值为0表示精确到整数的个位，值为1表示精确到1位小数，值为-1表示精确到整数的十位数。
操作步骤
1. 随机函数Rand和Randbetween
(1) 打开工作簿“数学函数.xlsx”。
(2) 在工作表“Sheet1”的单元格A1中输入公式“=Int(Rand()*90+10)”或公式“Randbetween(10,99)”，完成后按下Enter键。鼠标放在A1单元格的右下角，双击鼠标左键，在A2:A10单元格区域中完成公式填充。
2. Mod函数、Sqrt函数和Round函数
(1) 在工作表“Sheet1”的单元格F1中输入公式“=ROUND(SQRT(INT(A1/C1)),2)”，完成后按下Enter键，并在F2:F10单元格区域进行公式填充。
(2) 在工作表“Sheet1”的I1单元格中输入公式“=MOD(A1,C1)”，完成后按下Enter键，并在I2:I10单元格区域进行公式填充。
5.3日期时间函数
范例要求
打开工作簿“日期时间.xlsx”，完成以下操作。
1. date函数、Weekday函数、datedif函数★
在“学生信息”工作表的“出生日期(日期格式)”列，使用date函数计算出生日期。在“星期”列，使用weekday函数计算学生出生日期对应的是星期几。假定这一批学生的正常毕业日期是“202571”，在“毕业年龄”列计算学生在毕业时的年龄，要求： 不满1年按0年计算。完成后效果如图5.13所示。
图5.13Date、Weekday和Datedif函数应用效果图
2. Year函数、时间计算和时间函数★
在“晚间锻炼打卡”工作表的“年度”列计算本次打卡时间对应的年份。在“晚间锻炼开始时间”列进行计算。在“结束时间”列抽取“晚间锻炼结束时间”列中的时间。假定晚间熄灯时间是23点整，按照时间格式，在“距离熄灯时间”列中进行计算。在“格式转换”列中得到“X小时X分钟X秒”的距离熄灯时间。完成后效果如图5.14所示。在“锻炼时长”列的F25单元格中，使用分类汇总函数subtotal计算平均锻炼时长。要求： 自动筛选时，锻炼时长能随着筛选班级的改变，自动计算相应班级的锻炼时长。
图5.14Year函数、时间计算和时间函数应用效果图
相关知识
1. Date函数、Weekday函数、Datedif函数
(1) Date函数可以根据指定的年份、月份和日期返回日期序列值。用法如下：
Date(num1,num2,num3)
参数num1对应年份，参数num2对应月份，参数num3对应日期。
例如： Date(2022,4,25)，表示日期： 2022425。在范例中用Mid函数分别获取年份、月份和日期值，然后作为Date函数的3个参数进行计算。当然，范例中的这一问也可以直接用快速填充完成。
类似用法的函数还有Time，用法为： Date(num1,num2,num3)
参数num1对应小时，参数num2对应分钟，参数num3对应秒。
例如： Time(14,0,0)，表示时间： 14:00:00
(2) Weekday函数可以获取指定日期对应的数字形式的星期编号。用法如下：
Weekday(date,type)
参数date表示指定的日期。该日期可以是带引号的日期文本串(“20220425”)、日期序列值、其他公式或函数通过运算得到的日期格式的值或单元格引用。参数type有3种取值： 1、2、3，分别用不同的7个数表示周一到周天。
例如： Weekday("20220425",2)或Weekday(2022425,2) 都可以表示获取2022425是星期几。
(3) Datedif函数是一个隐藏的日期函数，用于计算两个日期之间的天数、月数或年数。基本语法如下：
Datedif(start_date,end_date,type)
参数start_date代表时间段内的起始日期。该日期可以是带引号的日期文本串(“20220425”)、日期序列值、其他公式或函数通过运算得到的日期格式的值或单元格引用。
参数end_date代表时间段内的结束日期。结束日期要大于起始日期，否则将返回错误值“#NUM”。
参数type为所需信息的返回类型，不区分大小写。取值和对应的功能如表5.2所示。
表5.2Datedif函数各参数及其功能
type参数功能
Y日期段中的整年数
M日期段中的整月数
D日期段中的天数
YD日期段中的天数差，忽略日期中的年
MD日期段中的天数差，忽略日期中的年和月
YM日期段中的月数的差，忽略日期中的年和日
2. Year函数、时间函数、时间计算
(1) Year函数返回指定日期的年份值，用法如下：
Year(date)
同类型的函数还有Month和Day，函数用法分别为： Month(date)，Day(date)。
参数date表示日期。该日期可以是带半角双引号的包含日期的文本串("20220425"，"2022/4/1 19:08:01")、日期序列值、其他公式或函数通过运算得到的日期格式的值或单元格引用。
(2) 时间函数Hour、Minute和Second。用法如下：
Hour(time)，Minute(time)，Second(time)
参数time表示时间。该时间可以是带半角双引号的时间文本串("14:45:00")，其他公式或函数通过运算得到的时间格式的值或单元格引用。
(3) 时间计算。
在Excel中，两个日期时间数据相减得到的数据以天数为单位，如果需要转换为小时，需要乘以24。从日期时间数据中提取时间时，可以先用Int函数获取该日期0点的值，再用减法处理。用法如图5.15所示。
图5.15Excel中的日期时间计算
操作步骤
1. date函数、Weekday函数、datedif函数
(1) 打开工作簿“日期时间.xlsx”。
(2) 在“学生信息”工作表的C2单元格中输入公式“=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))”，完成后将公式填充到C2:C34单元格区域。
(3) 在“学生信息”工作表的D2单元格中输入公式“=WEEKDAY(C2,2)”，完成后将公式填充到D2:D34单元格区域。
(4) 在“学生信息”工作表的E2单元格中输入公式“=DATEDIF(C2,"2025/7/1","y")”，完成后将公式填充到E2:E34单元格区域。
2. Year函数、时间计算、时间函数和分类汇总函数
(1) 在“晚间锻炼打卡”工作表的C2单元格中输入公式“=YEAR(E2)”，完成后将公式填充到C2:C24单元格区域(“年度”列)。
(2) 在“晚间锻炼打卡”工作表的D2单元格中输入公式“=E2-F2/24”，完成后将公式填充到D2:D24单元格区域(“晚间锻炼开始时间”列)。
(3) 在“晚间锻炼打卡”工作表的G2单元格中输入公式“=E2-INT(E2)”，完成后将公式填充到G2:G24单元格区域(“结束时间”列)。
(4) 在“晚间锻炼打卡”工作表的H2单元格中输入公式“="23:00:00"-G2”，完成后将公式填充到H2:H24单元格区域(“距熄灯时间”列)。
(5) 在“晚间锻炼打卡”工作表的I2单元格中输入公式“=HOUR(H2)&"小时"&MINUTE(H2)&"分钟"&SECOND(H2)&"秒"”，完成后将公式填充到I2:I24单元格区域(“格式转换”列)。
注意问题
1. 日期表示
一般情况下，日期常量可以用以下2种方式表示：
(1) 带双引号的日期或日期时间文本串(“20220425”，“2022/4/1 19:08:01”)。
(2) 日期格式。
需要注意几个问题：
1900年之前的日期，Excel无法直接进行数据处理。
进行日期计算后，单元格中的值不是日期，而是一个5位正整数。其实这个5位正整数是日期对应的序列值，一般称之为日期序列值。重新设置单元格格式为日期类型就能看到日期格式的数据。
日期或时间作为函数参数时，一定要加半角双引号，否则无法进行计算。
图5.16Datedif函数应用
2. Datedif函数
Datedif函数可以计算2个日期之间完整的年份差值和月份差值。使用Year和Month函数进行计算，会出现不满1年的计算结果为1年等情况。如图5.16所示。
5.4统 计 函 数
范例要求
打开工作簿“统计函数.xlsx”，完成以下操作。
1. iserror函数★
在“公司信息”工作表的“属地”列中，利用函数计算。要求： 如果公司名称中有“武汉”，定义为本地公司； 如果没有“武汉”，定义为外地公司。完成后效果如图5.17所示。
图5.17第1问完成效果图
2. 条件统计类函数、Large函数和Subtotal函数★★
在“统计”工作表中，利用统计函数和条件统计类函数进行计算，完成后效果如图5.18所示。
图5.18条件统计类函数和Large函数应用效果图
在“公司信息”工作表的F1单元格中进行统计，当筛选公司的不同属地后，可以分别求出不同属地公司的利润和。如图5.19所示。
图5.19Subtotal函数应用效果图
相关知识
1. iserror函数
1) 函数用法
iserror函数用于判断单元格中的值是否为错误值。如果是错误值，则函数返回True。常见的错误值有： #NULL，#DIV/0，#VALUE，#REF，#NAME?，#NUM，#N/A。用法为：
iserror(range)
参数range为需要判断的值或单元格引用。
2) 范例详解
在“iserror函数”范例中，C2单元格中的公式为“=IF(ISERROR(FIND("武汉",A2)),"外地公司","本地公司")”。先用find函数判断字符串“武汉”在A2单元格中的位置，如果find函数返回了数值，说明A2单元格中有字符串“武汉”。此时iserror函数的参数是数值，iserror函数的计算结果为false。此时if函数的判断条件就为false，对应字符串“本地公司”。当A2单元格中没有字符串“武汉”时，则find函数返回错误值，iserror函数计算的结果为true，也就是if函数的判断条件为true，对应字符串“外地公司”。
2. 条件统计类函数、Large函数和Subtotal函数
1) 条件统计类函数
(1) 常见的条件统计类函数有条件求和、条件求平均值和条件计数。分为单条件统计函数和多条件统计函数。函数功能如表5.3所示：
表5.3条件统计类函数功能和用法
函数功能用法
Sumif单条件求和sumif(条件区,满足的条件,［求和区］)
Sumifs多条件求和sumifs(求和区,条件区1,条件1,……,条件区n,条件n)
Averageif单条件求平均值averageif(条件区,满足的条件,［求平均值区］)
Averageifs多条件求平均值averageifs(求平均值区,条件区1,条件1,……,条件区n,条件n)
Countif单条件计数countif(条件区,条件)
Countifs多条件计数countifs(条件区1,条件1,……,条件区n,条件n)
sumif和averageif的第3参数可以省略，省略时，条件区就是求和区或求平均值区。
在上述6个函数的条件书写时，需要注意： 如果条件中涉及关系运算(>，<，=等)表达式，关系运算符需要书写在半角双引号中，表达式中的比较对象可以是具体的数值，也可以是单元格或公式。如果比较对象是具体的值，需要和关系运算符一起写在半角双引号中，例如： countif(c2:c23,">60")。如果比较对象是单元格或公式，单元格或公式要通过连字符“&”进行连接，例如： countif(c2:c23, ">"&F1)和countif(c2:c23,">"&公式)。
(2) 范例详解。
① 统计“本地公司2021年利润大于外地公司2021年利润平均值的数量”时，多条件计数函数countifs中包含了2个条件： 1个条件是“外地公司”。另1个条件通过连字符“&”，将关系运算符“">"”和嵌入的单条件求平均值averageif公式“AVERAGEIF(公司信息!C2:C23,"外地公司",公司信息!D2:D23)”进行连接得到。
需要注意的是： 关系运算符">"需要加半角双引号，公式不需要加双引号。
② 统计“员工人数在1000以上(含1000)的本地公司2021年利润的平均值”时，多条件求平均值函数averageifs包含了两个条件。
③ 统计“员工人数在1000以上(含1000)的外地公司2021年利润的总和”时，多条件求和函数sumifs包含了两个条件。
2) Large函数
Large函数返回数据集中第k个最大值，语法： Large(array,k)
参数array为需要找到第k个最大值的数组或数字型数据区域。可以是数组，也可以是单元格区间。
参数k为返回的数据在数组或数据区域中的位置。
在范例中，通过Large函数，在“公司信息”工作表的D2:D23单元格区域，找到利润前3的数据。
相同用法的函数有Small，用于求数据集中第k个最小值。
3) Subtotal函数
Subtotal函数返回列表中的分类汇总，语法： Subtotal(function_num,ref1,［ref2］)
function_num： 用于指定要为分类汇总使用的函数。取值为111或101111。如果取值为111，计算时包括隐藏和筛选的行； 如果取值为101111，计算时不包括隐藏和筛选的行。参数取值说明如表5.4所示。
表5.4Subtotal函数的第1个参数的取值
function_num取值
(包含隐藏和筛选后的
不可见单元格) function_num取值
(不包含隐藏和筛选后的
不可见单元格) 对应的函数功能
1101Average平均值
2102Count数值个数
3103Counta非空单元格个数
4104Max最大值
5105Min最小值
6106Product数值连乘的乘积
7107Stdev样本标准偏差
8108Stdevp总体标准偏差
9109Sum求和
10110Var样本方差
11111Varp总体方差
ref1： 需要进行分类汇总计算的第一个命名区域或引用。
ref2： 可选参数。进行分类汇总计算的第2个命名区域或应用。
在范例中，公式“=SUBTOTAL(109,D2:D23)”对单元格区域D2:D23进行求和，求和项不包括隐藏单元格和筛选后的不可见单元格。
需要注意的是，Subtotal函数只适用于数据列或垂直区域，不适用于数据行或水平区域。
操作步骤
1. iserror函数
(1) 打开工作簿“统计函数.xlsx”。
(2) 在工作表“公司信息”的C2单元格中输入公式“=IF(ISERROR(FIND("武汉",A2)),"外地公司","本地公司")”，完成后将公式填充到C2:C23单元格区域。
2. 条件统计类函数、Large函数和Subtotal函数
(1) 在工作表“统计”的B1单元格中输入公式“=COUNTIFS(公司信息!C2:C23,"本地公司",公司信息!D2:D23,">"&AVERAGEIF(公司信息!C2:C23,"外地公司",公司信息!D2:D23))”，完成统计“本地公司2021年利润大于外地公司2021年利润平均值的数量”。
(2) 在工作表“统计”的B2单元格中输入公式“=AVERAGEIFS(公司信息!D2:D23,公司信息!C2:C23,"本地公司",公司信息!B2:B23,">=1000")”，完成统计“员工人数在1000以上(含1000)的本地公司2021年利润的平均值”。
(3) 在工作表“统计”的B2单元格中输入公式“=SUMIFS(公司信息!D2:D23,公司信息!B2:B23,">=1000",公司信息!C2:C23,"外地公司")”，完成统计“员工人数在1000以上(含1000)的外地公司2021年利润的总和”。
(4) 在工作表“统计”的B4单元格中输入公式“=LARGE(公司信息!$D$2:$D$23,1)”，完成统计“2021年利润第1名的值”。在B5单元格中输入公式“=LARGE(公司信息!$D$2:$D$23,2)”，完成统计“2021年利润第2名的值”。在B6单元格中输入公式“=LARGE(公司信息!$D$2:$D$23,3)”，完成统计“2021年利润第3名的值”。
(5) 在工作表“公司信息”的F1单元格中输入公式“=SUBTOTAL(109,D2:D23)”，完成统计“筛选公司属地后的利润和”。
注意问题
由于在本范例的统计计算中，没有进行公式填充，所以单元格引用采用相对引用、混合引用或绝对引用都可以。绝对引用的单元格区域一般是通过鼠标选取单元格区域后，Excel软件自动生成的一种引用方式。
5.5查找定位函数(非数组用法)
范例要求
打开工作簿“查找定位函数(非数组用法).xlsx”，完成以下操作。
1. Vlookup函数★★
在“篮球比赛记录”工作表中，根据工作表“球队区域关联”，利用Vlookup函数的精确匹配用法计算“所属区域”列中的数据。根据“日期”列的数据，利用Vlookup函数的模糊匹配用法计算“季度”列中的数据，完成后效果如图5.20所示。
图5.20Vlookup函数应用效果图
2. Lookup函数、Index函数、Match函数和Offset函数★★
在“统计”工作表中，利用lookup、index、match、offset和large等函数完成6项统计，在A7单元格中选择“2021年2月15日”。完成后效果如图5.21所示。
图5.21Lookup、Index、Match和Offset函数应用效果图
相关知识
1. Vlookup函数
Vlookup函数的基本用法如下：
Vlookup(lookup_value,table_array,col_index_num,［range_lookup］)
第一参数是在单元格区域的第一列中要查询的值。
第二参数是需要查询的单元格区域。这个区域中的首列必须要包含查询值，否则函数将返回错误值。如果查询区域中包含多个符合条件的查询值，Vlookup函数只能返回首个匹配的结果。
第三参数用于指定返回查询区域中的第几列的值。如果此参数超出待查询区域的总列数，Vlookup函数将返回错误值#N/A。
第四参数决定函数的查找方式，如果为0或false，用精确匹配方式，并且支持无序查找； 如果为非0的数、true或被省略，则使用模糊匹配方式，同时要求查询区域的首列按升序排序。
精确匹配用法如图5.22所示。
图5.22Vlookup函数精确匹配用法
模糊匹配用法如图5.23所示。
图5.23Vlookup函数模糊匹配用法
2. Lookup函数、Index函数、Match函数和Offset函数
1) Lookup函数
Lookup函数是常用的查询函数之一，和Vlookup函数的用法比较类似，这里只介绍向量用法。
Lookup(lookup_value,lookup_vector,［result_vector］)
向量语法是在由单行或单列构成的第2个参数中，查找第1个参数，并返回第3个参数中对应位置的值。第1参数为查找值。第2参数为查询范围。第3参数可选，为结果范围，必须与第2参数大小相同。如果第3参数缺省，则结果范围为第2参数。
需要注意的是：
(1) 如果需要在查找范围中查找一个明确的值，查找范围必须升序排列； 如果查找一列或一行数据的最后一个值，查找范围并不需要严格地升序排列。
(2) 如果Lookup函数找不到查询值，则该函数会与查询区域中小于或等于查询值的最大值进行匹配。
(3) 如果查询值小于查询范围中的最小值，则Lookup函数会返回#N/A错误值。
(4) 如果查询区域中有多个符合条件的记录，则Lookup函数仅返回最后一条记录。
在范例中，工作表“统计”中“指定比赛日期对应的球队名称”(B4单元格)可以用Lookup函数处理，因为“日期”列是升序排序； 但“第一场失败对应的球队名称”(B1单元格)不能用Lookup函数处理，因为“胜负关系”列不是升序排序。
2) Index函数
Index函数是重要的引用函数之一，通过指定的行列号，在一个单元格区域或数组中返回对应位置的元素值。常用的函数语法有2种，这里只介绍非数组用法。
Index(reference,row_num,［column_num］,［area_num］)
参数row_num，指定数组中的某行。参数column，可选参数，指定数组中的某列。参数reference，对一个或多个单元格区域的引用； 如果引用区域为一个不连续的区域，必须将其用括号括起来。参数area_num，可选参数，选择reference参数中的一个区域。
一般情况下，在单列连续区域中进行查找，只需要使用第1参数reference和第2参数row_num。用法如图5.24所示。
图5.24Index函数用法
在上述的单列区域定位中，查找A1:A5单元格区域中的第4行。在多个不连续区域定位中，选择(A1:A5,A7:B9,A11:A12)中的第2个区域，通过行号3和列号2定位到单元格区域A7:B9的第3行第2列。
3) Match函数
Match函数同样是Excel中重要的查找函数，通过在单元格区域中搜索指定项，返回该项在单元格区域中的相对位置。用法如下：
Match(lookup_value,lookup_array,［match_type］)
第一参数lookup_value，表示需要查找的值。第二参数lookup_array，表示查询区域，一般指单元格区域或数组，且此参数必须是一行或一列的数据范围。
第三参数match_type用来指定Match函数的查找方式，取值为： 0、1、-1。值为0时，表示精确匹配，此时的第二参数lookup_array中的值可以按任何顺序排列； 如果在查找区间有多个相同的匹配值，和第一个相同的值匹配。值为1时，表示模糊匹配，要求第二参数lookup_array中的值按升序排列，以查询区域(第二参数lookup_array)中小于查询值的最大值进行匹配。值为-1时，同样表示模糊匹配，要求第二参数lookup_array中的值按降序排列，以查询区域(第二参数lookup_array)中大于查询值的最小值进行匹配。用法如图5.25所示。
图5.25Match函数用法
在Excel中，match函数经常和index函数搭配使用，结合match函数定位行号或列号，在index函数的查找区间定位具体的单元格。用法如图5.26所示。
图5.26Index和Match函数搭配用法
该函数组合的用法除了在查找相同的最后一个值以外，可以替换Lookup函数。
在范例中，工作表“统计”中“指定比赛日期对应的球队名称”(B4单元格)和“第一场失败对应的球队名称”(B1单元格)都可以用index函数和match函数的组合处理。
“失分第1多时对阵的球队”、“失分第2多时对阵的球队”和“失分第3多时对阵的球队”，除了index和match函数，还需要项目5.4中讲到的Large函数。
以“失分第3多时对阵的球队”为例，对应的公式为“=INDEX(篮球比赛记录!A2:A15,MATCH(LARGE(篮球比赛记录!G2:G15,3),篮球比赛记录!G2:G15,0))”。先用Large函数找到G列(失分列)中第3大的数，然后用match函数求出该数在所有失分列区域(G2:G15)中的相对行号，最后用index函数在同样大小的球队名称单元格区域(A2:A15)中找到该行号对应的单元格中的值，即失分第3多时对阵的球队名称。
4) Offset函数
Offset函数以指定的引用为参照系，通过给定的偏移量返回新的引用，返回的引用可以是一个单元格或单元格区域。能够为动态数据透视表、动态图表等提供动态数据源。函数基本语法为： Offset(reference,rows,cols,［height］,［width］)。
第一参数reference，作为偏移量参照的起始引用区域。该参数必须是对单元格或连续单元格区域的引用，否则Offset函数返回错误值#VALUE。
第二参数rows，以第一参数中的单元格或单元格区域的左上角单元格为参照点，向上或向下偏移的行数。行数为正数时，向参照点的下方偏移。
第三参数cols，以第一参数中的单元格或单元格区域的左上角单元格为参照点，向左或向右偏移的列数。列数为正时，向参照点的右边偏移。列数为负时，向参照点的左边偏移。
第四参数height，可选参数。以经过第二参数和第三参数的行、列偏移后得到的偏移点为基准，返回的高度。值为正数时，返回偏移点下方的高度； 值为负数时，返回偏移点上方的高度。缺省时，值为1。
第五参数width，可选参数。以经过第二参数和第三参数的行、列偏移后得到的偏移点为基准，返回的宽度。值为正数时，返回偏移点右侧的宽度； 值为负数时，返回偏移点左侧的宽度。缺省时，值为1。函数用法如图5.27所示。
图5.27Offset函数用法
5) 范例详解
在范例“Lookup函数、Index函数、Match函数和Offset函数”中计算“截至左侧日期的积分和”时的公式为“=SUM(OFFSET(篮球比赛记录!I2,0,0,MATCH(A7,篮球比赛记录!C2:C15,0),1))”。先用match函数找到指定日期在所有日期(工作表“篮球比赛记录”的单元格区域C2:C15)中的相对行号； 再以第1场比赛获得的积分所对应的I2单元格为参照点，不做偏移(offset函数的第二参数和第三参数都是0)，将match函数计算得到的行号作为offset函数返回的单元格区域的高度(第四参数)，宽度为1列。最后计算offset函数返回的单元格区域(I1:I5)的数值和。
操作步骤
1. Vlookup函数
(1) 打开工作簿“查找定位函数(非数组用法).xlsx”。
(2) 在工作表“篮球比赛记录”的B2单元格中输入公式“=VLOOKUP(A2,球队区域关联!$A$2:$B$15,2,0)”，完成后将公式填充到B2:B15单元格区域。
(3) 在工作表“篮球比赛记录”的D2单元格中输入公式“=VLOOKUP(C2,季度和日期关联!$A$1:$B$4,2,1)”，完成后将公式填充到D2:D15单元格区域。
2. Lookup函数、Index函数、Match函数和Offset函数
(1) 在工作表“统计”的B1单元格中输入公式“=INDEX(篮球比赛记录!A2:A15,MATCH("负",篮球比赛记录!H2:H15,0))”，完成统计“第一场失败对阵的球队”。
(2) 在工作表“统计”的B4单元格中输入公式“=LOOKUP(A4,篮球比赛记录!C2:C15,篮球比赛记录!A2:A15)”，完成统计“2021年3月17日对阵的球队名”。
(3) 在工作表“统计”的A7单元格右侧的下拉列表中选择“2021年2月15日”，在B7单元格中输入公式“=SUM(OFFSET(篮球比赛记录!I2,0,0,MATCH(A7,篮球比赛记录!C2:C15,0)))”，完成统计“截至左侧日期的积分和”。
注意问题
Vlookup函数的语法为： Vlookup(lookup_value,table_array,col_index_num,［range_lookup］)，其中的第3参数col_index_num不能理解为工作表中实际的列号，而是指要返回查询区域中第几列的值，属于相对列号。
例如： 如果在B2:F6区域中需要返回D列中某行的数据。此时table_array参数值为B2:F6，col_index_num参数值就应该为3，含义为从单元格区域B2:F6的首列(B列)开始的第3列(D列)； 不能从A列开始计算偏移列号，即col_index_num参数值不能写4。
同样的，match函数返回的也是相对行号或列号，指查找区域的第几行或第几列。
5.6查找定位函数的数组用法
范例要求
打开工作簿“查找定位函数数组用法.xlsx”，完成以下操作。
match函数数组用法★★★
在“分班记录”工作表中，使用index函数和match函数的数组用法，结合“分班标准”工作表，计算“班级类型”列的类型。完成后效果如图5.28所示。
图5.28Match函数数组用法的完成效果
相关知识
1. Excel中的数组概念
在Excel函数与公式中，数组是指按一行一列或多行多列排列的一组数据元素的集合。数据元素可以是数值、文本、日期、逻辑值和错误值等。
2. Excel中的数组分类
1) 常量数组
常量数组是指直接在公式中写入数组元素，并用大括号在首尾进行标识的字符串表达式。常量数组不依赖单元格区域，可以直接参与公式的计算。数值型常量数组元素中不能包含美元符号、逗号和百分号。日常应用中，以单列单行的一维数组为主。
纵向数组(单列数组)对应多维数组，数组中各元素用半角分号间隔，输入时需要先选定所有的单元格。例如： 数组公式“={1;2;3}”有3个数组元素，要得到纵向的单列数组，就需要选中纵向的3个单元格，然后在编辑栏中输入公式，按下Ctrl+Shift+Enter键，得到纵向数组常量，如图5.29所示。
横向数组对应一维数组，数组中的各元素用半角逗号间隔，输入时同样要先选定所有的单元格。例如： 数组公式“={1，2，3}”有3个数组元素，要得到横向的单行数组，就需要选中横向的3个单元格，然后在编辑栏中输入公式，按下Ctrl+Shift+Enter键，得到横向数组常量，如图5.30所示。
图5.29纵向数组常量(多维数组)
图5.30横向数组常量(一维数组)
注意，常量数组公式不能在一个单元格中先输入公式，然后填充，否则会在填充单元格中得到同样的数值。如图5.31所示。
2) 区域数组
区域数组实际上就是公式中对单元格区域的直接引用，维度和尺寸与常量数组完全一致。例如公式“=MATCH(A1,B1:B3,0)”中的B1:B3是区域数组。如图5.32所示。
3) 范例详解
范例中用到了match函数的多条件匹配，涉及区域数组。我们将工作表“分班记录”和工作表“分班标准”中的相关数据单元格放到一个工作表中，如图5.33所示。
图5.31常量数组公式的错误填充效果
图5.32区域数组
图5.33match函数多条件匹配
要计算班级类型，就要同时满足两个条件： 某同学第1周的评定等级和F2:F11单元格区域匹配，第2周的评定等级和I2:I11单元格区域匹配。以A2单元格中姓名对应的班级类型为例，两个条件用公式描述分别是： “$F$2:$F$11=$B$2”和“$I$2:$I$11=$C$2”。公式“$F$2:$F$11=$B$2”表示单元格B2和单元格区域F2:F11中的每一个单元格匹配后的结果，在单元格区域G2:G11中呈现匹配的结果值； 公式“$I$2:$I$11=$C$2”表示单元格C2和单元格区域I2:I11中的每一个单元格匹配后的结果，在单元格区域J2:J11中呈现匹配后的结果值。这两个公式的运行结果实质就是两个区域数组。
在计算机中，用0描述逻辑假(FALSE)，用1描述逻辑真(TRUE)。所以这2个区域数组可以转换为以下2个数组常量： {0,0,0,0,0,0,0,0,1,1}和{0,0,0,0,0,0,0,1,0,1}。B2和C2单元格中的数据分别在单元格区域F2:F11和I2:I11中匹配后，只有1行数据的运行结果同时为TRUE(常量1和常量2的值都为1)，如图5.34所示。该行对应的班级类型就是A2单元格中姓名对应的班级类型。
图5.34match函数多条件匹配范例详解
在图5.34的D2单元格中书写公式“=INDEX($L$2:$L$11,MATCH(1,($F$2:$F$11=B2)*($I$2:$I$11=C2),0))”，其中match公式中的($F$2:$F$11=B2)和($I$2:$I$11=C2)的结果就是上面分析中的两个数组常量： {0,0,0,0,0,0,0,0,1,1}和{0,0,0,0,0,0,0,1,0,1}。在Excel中进行数组常量乘法，其实就是两个数组常量中的每一个数组元素相乘，得到一个新的一维数组常量{0,0,0,0,0,0,0,0,0,1}，其中只有最后一个数组元素的值同时为1，表示同时匹配成功的是两个单元格区域(F2:F11，I2:I11)的最后一行。在match函数中，为了找到这个匹配成功的数值1，所以第一参数需要写成数值1。匹配模式为精确匹配，所以match函数的第三参数为0。最后通过index函数，在L2:L11单元格区域中，结合match函数计算得到的行号，即可确定班级类型值。
D2单元格中的数组公式还可以写成“=INDEX($L$2:$L$11,MATCH(B2&C2,$F$2:$F$11&$I$2:$I$11,0))”。
注意，由于范例讲解中的数据和范例素材中的数据位置不一致，所以不能将知识点中的范例讲解的公式直接抄写至范例素材中。
操作步骤
(1) 打开工作簿“查找定位函数数组用法.xlsx”。
(2) 在工作表“分班记录”中的D2单元格中输入公式“=INDEX(分班标准!$C$2:$C$11,MATCH(1,(分班标准!$A$2:$A$11=B2)*(分班标准!$B$2:$B$11=C2),0))”，输入后按下组合键Ctrl+Shift+Enter，完成后将公式填充到D2:D101单元格区域。
注意问题
数组公式的书写和普通公式有区别，数组公式需要在公式书写完成后，同时按下
Ctrl+Shift+Enter组合键。
常量数组需要选中所有的常量数组应用范围，再按下组合键得到结果。区域数组可以按下组合键得到一个单元格中的数组公式后，再进行公式填充。
练习
一、 在“51.xlsx”工作簿中进行以下操作。完成后，保存文件。
1. 在工作表“商品明细”中，根据“产品编号”列，计算“品质”列数据。规则如表5.5所示。★
2. 在工作表“商品明细”中，根据“产品编号”列，计算“生产日期”列的数据，要求获得的数据为日期格式。规则如表5.6所示。★
表5.5编号和品质的对应关系
产品编号中jhun前1位数字品质
奇数一般
偶数高品质
表5.6编号和生产日期的对应关系
产品编号jhun后14位年份
产品编号jhun后56位月份
产品编号jhun后78位日
3. 在工作表“商品明细”中，根据“生产日期”列，计算产品是否在周末生产，如果在周末生产，在“是否周末生产”列的数据单元格中显示“是”，否则显示“否”。★
4. 在工作表“商品明细”中，根据“产品编号”列，计算“具体时间”列的数据，要求获得的数据为时间格式。规则如表5.7所示。★
5. 在工作表“商品明细”中，根据“具体时间”列，计算“生产时段”列的数据，规则如表5.8所示。★★★
表5.7编号和时间的对应关系
产品编号jhun后910位小时
产品编号jhun后1112位分钟
产品编号jhun后1314位秒
表5.8具体时间和时间区段名称的对应关系
小 时 区 间对 应 名 称
［6,12)上午
［12,14)中午
［14,18)下午
［18,24)&&［0,6)晚上
6. 在工作表“商品明细”中，计算“失效日期”列的数据，要求获得的数据格式为“XXXX/XX/XX XX:XX”效果如图5.35所示。★
图5.35“失效日期”列的完成效果图
7. 假定现在的日期时间为： 2022313 12:00:00。在工作表“商品明细”的“剩余时间”列中计算距离失效日期的天数和小时数。完成后的效果如图5.36所示。★
图5.36“剩余时间”列的完成效果图
8. 在工作表“商品明细”中，计算“剩余时间(天)”列，该列数据(J2:J20)以天为单位，保留2位小数。完成后，添加自动筛选。当筛选不同品质的商品时，在J21单元格中计算该品质商品剩余的有效时间平均值。完成后的效果如图5.37所示。★
图5.37自动筛选后的各品质“剩余时间(天)”的完成效果图
二、 在“52.xlsx”工作簿中进行以下操作。完成后，保存文件。
1. 根据“部门人员关联”工作表，在“销售记录”工作表中计算“部门”列的数据。★
2. 在“销售记录”工作表中，根据“记录”列计算“状态”列的数据。规则如表5.9。★
3. 在“统计”工作表的I2单元格中，利用countifs函数计算： 销售一部在2季度正常工作的天数。在“统计”工作表的I6单元格中，利用countifs和averageif函数计算销售二部在3季度的单天销售额大于所有部门人员在3季度的非零销售额(销售额不为0)平均值的次数。★★★
4. 在“统计”工作表的B5:B16单元格区域，利用sumif或sumifs函数计算各月的销售额总量。在“统计”工作表的C5:C16单元格区域，利用vlookup函数计算销售额对应的评级，评级规则如表5.10。★
表5.9记录和状态的对应关系
记录状态
病假或事假请假
轮休休息
白班或晚班工作
表5.10数值区间和评级的对应关系
数 值 区 间评级
［0,600)不合格
［600,700)基本合格
［700,800)合格
［800,900)良好
［900,+∞)优秀
5. 在“统计”工作表的A2单元格中，利用index和match函数计算公司中第1次员工请假的日期。★
6. 在“统计”工作表的F2单元格中，利用lookup函数计算202148的销售员姓名。★
7. 在“统计”工作表的G4单元格右侧的下拉框中选中“6月”，在G6单元格中利用sum、offset和match函数计算1到6月的所有销售额。★★
三、 在“53.xlsx”工作簿中进行以下操作。完成后，保存文件。
1. 在工作表“获奖情况”的E1单元格中计算同时在两项比赛中获奖的人数。★★★
2. 在工作表“人员情况”的D2:D14单元格区域中计算每位同学的奖学金评级。评级标准在“关联”工作表中查看。★★★