第
3
章
表格数据处理
表格是指按所需的内容项目画成格子,分别填写文字或数字的书面材料,
便于统计查看。常见的表格是二维表,通常由多个属性(维度)和多个值组成,
作为最终报表方便阅读者快速掌握重要信息。
3.xel基础
1
Ec
3.1
基本术语
1.
MicrosoftExcel是一个电子表格程序,用于记录和分析数值数据。Excel
将电子表格视为列和行表的集合。字母标签通常分配给列,而数字标签通常分
配给行。列和行相交的点称为单元格。单元格的地址由表示列的字母和表示
行的数字确定。
1.工作簿
工作簿(Workbook)其实就是指Excel文件,一个Excel就是一个工作簿,
有时候也会直接用Excel代替工作簿这个称呼。
2.工作表
工作表(Workshet)是将Excel打开后看到的底部页签栏中所显示的表格,
单击页签就可以切换不同的工作表,这些表是在同一个工作簿里,在移动一些
内容时可以根据“工作簿+工作表”定位想移动的位置。
工作表是行和列的集合。工作簿是工作表的集合。默认情况下,工作簿中
有一个工作表,名字为Shet1,新建工作表的名字为Shet2和Shet3,以此
类推。
3.单元格
单元格用于记录数据。行号和列标是用来定位单元格的,在编辑公式等时
都要用到。用行号和列标可以确定一个单元格的位置,也就可以确定该位置对
应的数据,如A1单元格代表工作表中左上角的单元格。单元格是表格中行与
第3章表格数据处理29
列的交叉部分,是组成表格的最小单位,可拆分或者合并。单个数据的输入和修改都是在
单元格中进行的。可在单元格中编辑做出各种各样的表格,单元格可以合并在一起变成
更大的单元格,也可以直接调整大小,这取决于需要的表格样式。
4.单元格区域
单元格区域指的是由单个单元格或者多个单元格组成的区域,或者整行、整列等。如
V3.1工作表与单元格
果在A列的1、2、3、4行底部插入了一个求和公式“=SUM(A1:
A4)”,这个公式所包括的单元格范围就是A1:A4 四个单元格,
A1:A4 代表了单元格区域。选中一个单元格区域,然后按住
Ctrl键再选择其他的单元格,即可选中不相邻的单元格区域。单
元格区域可以不连续,如“=SUM(A1:A4,C1:C4)”,其中参数
包含了两个不相邻的单元格区域。
3.1.2基本数据类型
Excel中主要的数据类型分为数值型、文本型和逻辑型3种。
1.数值型数据
在Excel中,数值型数据是最为常见且非常重要的数据类型,是可以进行数学运算的
数据类型。
e,
数值型数据包含的符号有数字(0~9), 正负号(+
,
、
)
-), 小数点(.), 科学计数法的E或
货币符号$或$,百分号(%), 分隔符(/) 和千位符(等。以下是数字输入的基本规则。
(1)负数:带圆括号的数字被识别为负数。例如,输入(3)和-3都被识别为负数。
(2)分数:输入分数时,在整数与分数之间用空格隔开。整数为0时不可省略,否则
识别为日期。
(3)百分数:输入百分数时,只需要在数字后再输入%即可。
当数字的整数位数超过11 位时,系统将自动转化成科学计数法表示。数字的有效位
数是15 位,超过15 位的部分被舍弃用0代替。
在Excel中,日期存为整数,1900 年1月1日为基准日,对应
的序列号为1,1900 年1月2日对应序列号2,依次类推,日期数
据对应的序列号将依次递增。在Excel中,时间存储为小数,时
间数据的范围为0:00:00~23:59:59,对应[0,1)的小数,其中V3.2
日期类型数据输入
0:00:00 点对应0,23:59:59 点对应0.30 对应的
99 。时间数据9:
小数可以用数学公式“=(9+30/60)/24”计算。如1902-1-19:18:30,其对应的公式为
366*2+(9+18/60+30/60/60)/24≈732.
38784722222
。
【提示】当时间数据参加运算时,实际上是对应的小数参加运算
。
2.
文本型数据
文本型通常是指非数值型,如汉字、英文字母、符号等。在Excel中,有很多看上去是
30 数据处理实践教程(微课版)
数字,但不具备数字的全部特点,如身份证号码、学号、门牌号、电话号码、银行卡号等,它
们不需要进行数学运算。在Excel中,这些内容输入时就需要以文本形式存在。输入文
本可以先将单元格区域选中,将单元格格式设置为“文本”,再输入数字等内容,按Enter
键后这些内容就是文本格式了。也可以在输入的数字前面加上半角单引号('),如想输入
电话号码12345678,就输入1' 2345678。
按文本格式输入有一个好处,就是输入超过15位的数字也能全部保留,这个在输身
份证号码时就可以看到。如果用“数值”或“常规”格式输入数字,15位以上是无法保留精
度的,15位以上全部转化为0。
默认情况下,文本型数据在单元格中左对齐。输入数据时,在数字前面加半角单引
号,如4' 4123456,Excel会将其转化为文本型数据,这种数据称为数字文本型数据。数字
文本型数据的左上角将显示绿色的错误标记。
V3.3 将数字转化为文本
输入数字文本型数据时,除了在数字前面加半角单引号(')
的方法外,还可在输入数据之前先选定空白单元格区域,设置数
字格式为“文本”,再输入数据。在输入大批量数字文本型数据
时,往往会采用第二种方法。按文本格式输入的其他非正常的
数字,也会全部保留,不会被转化,如00.1、10.100。
3.逻辑型数据
逻辑型数据只有两个值,即TRUE和FALSE。在Excel的公式中,关系表达式结果
为逻辑值。如“=1>2”的结果为FALSE。逻辑值TRUE和FALSE在公式中作为数值
1和0参加运算,因此,公式“=1+True”结果为2。
默认情况下,逻辑型数据在单元格中居中对齐,并且自动显示为大写字母。
4.特殊值
经常用Excel的朋友可能都会遇到一些莫名其妙的错误值信息:# N/A!、
V3.4 特殊值的处理方法
#VALUE!、#DIV/0! 等。出现这些错误的原因有很多种,如
果公式不能计算正确结果,Excel将显示一个错误值。例如,在
需要数字的公式中使用文本、删除了被公式引用的单元格,或者
使用了宽度不足以显示结果的单元格。表3.1为Excel中常见
的特殊值。
表3.1 Excel中常见的特殊值
特殊值原 因错误例子解决办法
#####!
如果单元格所含的数字、日期或时间比单
元格宽,或者单元格的日期时间公式产生
了一个负值,就会产生#####! 错误
(显示错误) 调整列宽或者修改
数据
#VALUE!
当使用错误的参数或运算对象类型时,或
者当公式自动更正功能不能更正公式时,
将产生错误值#VALUE!
=1+"jnu" =1&"jnu"
第3章 表格数据处理 31
续表
特殊值原 因错误例子解决办法
#DIV/0! 当公式被零除时,将会产生错误值#
DIV/0! =1/0 =1/1
#NAME? 在公式中使用了Excel不能识别的文本时
将产生错误值#NAME? =IFF(x>y,x,y) =IF(x>y,x,y)
#N/A 当在函数或公式中没有可用数值时,将产
生错误值#N/A
= VLOOKUP(A11,
学生成绩,3,FALSE)
=IFNA(VLOOKUP
(A11,学生成绩,3,
FALSE),0)
#REF! 当单元格引用无效时将产生错误值#REF!
#NUM! 当公式或函数中某个数字有问题时将产生
错误值#NUM! =123^345 =12^34
#NULL!
当试图为两个并不相交的区域指定交叉点
时将产生错误值#NULL!,即读取数据
为空
使用了不正确的区域
运算符或不正确的单
元格引用
3.1.3 数据的输入
Excel并没有规定必须从哪个单元格开始输入数据,为方便起见,通常都从工作表的
左上角开始。输入或修改数据时应首先单击某个单元格使其成为当前工作单元格,然后
才可以向该单元格输入数据或者修改数据。输入的内容会显示在编辑栏中。一般数据的
输入方法在3.1.2节介绍数据类型时已有说明。
大量相同的数据或有规律的数据(例如,等差序列、等比序列)自动输入指定单元格的
过程也称自动填充。对这些数据可以采用填充技术,让它们自动输入一系列指定的单元
格中。当前工作单元格或选定的单元格区域的右下角黑色小方块称为填充柄,光标靠近
时会变为小十字,自动填充功能就是通过填充柄或者“序列”对话框来实现的,如图3.1、
图3.2所示。
图3.1 填充柄
图3.2 填充序列设置
32数据处理实践教程(微课版)
3.1.4数据的显示
输入数字时,Excel默认数字格式显示其内容。在多数情况下,设置为“常规”格式的
数字即以输入的方式显示。然而,如果单元格的宽度不够显示整个数字,则常规格式将对
带有小数点的数字进行四舍五入。常规格式还对较大的数字(12 位及以上)使用科学计
数(指数)法。同理,输入文本型数据、逻辑型数据系统也都使用默认格式显示。
数据输入后,为使工作表数据排列整齐、重点突出、外观更加符合要求,可以通过设置
单元格格式完成。在设置单元格格式之前必须先选定单元格或者单元格区域,然后使用
下列方法之一,根据实际需要进行设置。数据显示设置方法如下。
(1)单击“开始”菜单,使用工具栏上的按钮进行设置。
(2)单击“开始”菜单“字体”“对齐方式”“数字”3个选项组中任一项右下角的对话启
动器(本书中选择的是“数字”选项组), 打开“设置单元格格式”对话框,如图3.3所示,这
时就可以根据选定的单元格或者单元格区域中数据的类型,从“设置单元格格式”对话框
中选择不同的选项卡进行格式设置了。
图3.“设置单元格格式”对话框
3
(3)右击选定的单元格或者单元格区域,在弹出的快捷菜单中选择“设置单元格格
式”命令,打开“设置单元格格式”对话框进行单元格格式设置。
第3章 表格数据处理 33
3.2 基本计算
3.2.1 运算符
V3.5 运算符混合使用举例
运算符表明了对运算对象进行的操作,运算对象有常量(如
数字、文本等)、单元格地址或单元格区域所包含的单元格内容、
函数等。Excel的运算符有4种类型,分别是算术运算符、比较
运算符、文本连接运算符和引用运算符。如表3.2所示,设A1
单元格内容为8,A2单元格内容为1,B1单元格内容为1,B2单
元格内容为5。
表3.2 Excel的运算符
运算符类型运算符功 能举 例结 果
算术运算符
+ 加法运算=7+4或=A1+B2 11或13
- 减法运算=7-3或=A1-B2 4或3
* 乘法运算=7*4或=A1*B2 28或40
/ 除法运算=7/4或=A1/B2 1.75或1.6
% 百分比运算=7%或=A1% 0.07或0.08
^ 乘幂运算=7^3或=A1^2 343或64
比较运算符
= 等于=1=1或=A1=A2 TRUE或FALSE
> 大于=1>2或A1>A2 FALSE或TRUE
< 小于=1<2或A1<A2 TRUE或FALSE
>= 大于或等于=1>=2或A1>=A2 FALSE或TRUE
<= 小于或等于=1<=2或A1<A2 TRUE或FALSE
<> 不等于=1<>2或A1<>A2 TRUE或TRUE
文本连接
运算符& 用于连接多个文本,生成一
个新的文本="123"&"你好" 或=A1&B2
"123你好" 或"85"
引用运算符
:(冒号) 特定区域引用运算=SUM(A1:B2) 15
,(逗号) 联合多个特定区域引用运算=SUM(A1:A2,B1) 10
(空格) 交叉运算,对两个引用区域
中共有的区域进行运算=SUM (A1:A2 A1:
B1) 8
说明:在Excel中输入计算公式时,必须以等号(=)开头;公式中的文本(字符串)要
使用双引号括起来。
如果一个公式中包含了上述若干运算符,就必须按照Excel规定的优先级次序进行
计算,如表3.3所示。
34 数据处理实践教程(微课版)
表3.3 运算符优先级
优先级运 算 符说 明
1 :(冒号)、,(逗号)、(空格) 引用
2 - 负数
3 % 百分比
4 ^ 乘幂
5 *、/ 乘法、除法
6 +、- 加法、减法
7 & 文本连接
8 >、<、>=、<=、<>、= 比较
3.2.2 坐标引用
Excel单元格地址引用分为相对地址引用(简称相对引用)、绝对地址引用(简称绝对
引用)和混合地址引用(简称混合引用)三大类。
1.相对引用
系统默认使用的是相对引用,即当公式所在单元格地址发生变化时,公式中的单元格
地址也会相应地发生改变。例如,单元格C1的内容是公式“=A1+B1”,表示单元格C1
的值是单元格A1和B1中的数值之和。如果将公式复制到单元格D1,则单元格D1的公
式就自动变为“=B1+C1”。
2.绝对引用
公式中引用的单元格或单元格区域,无论公式复制到任何地方,该单元格或单元格区
V3.6 相对引用与绝对引用
域的地址都不会改变。绝对引用格式是在单元格地址的列
标和行号前面各增加一个$(美元符号)。例如,单元格C1
的内容是公式“=$A$1+$B$1”,如果将公式复制到单
元格D1,则单元格D1的公式为“=$A$1+$B$1”。
3.混合引用
公式中单元格或单元格区域地址中既有相对引用又有绝对引用。例如,单元格地址
$B2、B$2或单元格区域地址$C2:$F5、C$2:F$5。当公式复制或移动时,凡是地址
的列标或行号前面有$表示该部分地址不变,没有$表示该部分地址按照相对引用方式
自动改变。例如,单元格C1的内容是公式“=$A1+B$1”,如果将公式复制到单元格
D1,则单元格D1的公式为“=$A1+C$1”。
如果公式中引用的单元格或单元格区域不在当前工作表或也不在当前工作簿,那么
其地址格式为[工作簿名]工作表名!单元格地址。例如,单元格C2的公式功能是把当前
第3章 表格数据处理 35
工作表中的单元格A1加上“C:\Excel示例\aa.xlsx”Sheet1工作表中的单元格B2,其公
式为“=A1+'C:\Excel示例\[aa.xlsx]Sheet1'!B2”。系统进行计算时,就会直接读入磁
盘中指定位置上存在的、未被打开的Excel工作簿文件内的数据,再加上单元格A1内容
作为当前单元格的值。当然前提是该文件必须存在、对应单元格内有数据,并且该用户具
有读取该文件内这些位置的数据的权限。
3.2.3 工作表的创建与数据输入实验
1.实验目的
(1)掌握创建工作表及维护工作表操作。
(2)掌握数据的输入、编辑和单元格格式设置的基本操作。
2.实验内容
启动Excel,在工作簿1的工作表Sheet2中输入如图3.4所示的数据,完成后以你的
学号和姓名保存。
图3.4 学生表数据
3.要点提示
(1)学号、手机号码为字符型常量,出生日期为数值型常量。
(2)住校否为逻辑型常量,TRUE表示住校、FALSE表示未住校。
(3)总分为公式,其计算规则为各科分数之和且保留一位小数。
(4)表格外框为实线、内框为虚线,记录间使用不同填充颜色,除了B列左对齐外,其
余列均为居中对齐。
(5)将当前工作表名称命名为“数据输入及格式设置”,将其余工作表删除。
【操作提示】 学号、手机号码可使用引导符方式输入,或先设置单元格格式为文本,
然后再输入。
36 数据处理实践教程(微课版)
V3.7Round函数的使用
3.2.4 常用函数
函数实际上是一类特殊的、事先编写好的程序,以解决某种
特定的数据处理问题。函数通常表示为
函数名([参数1],[参数2],[参数3],…)
说明:圆括号中的参数可以有多个,中间用逗号分隔;用方括号括起来的参数表示可
选参数,使用时方括号不要输入,而没有方括号的参数表示是必需的;有的函数没有参数,
使用时外面的圆括号不能省略;参数可以是常量、单元格或单元格区域地址、已定义的名
称、公式、函数等。
Excel提供了大量的内置函数供用户使用,并按照其功能进行分类。2016版本提供
的函数有13类,下面介绍函数的输入方法和常用函数。
1.函数的输入与编辑
1)公式记忆式输入
在单元格输入=和函数的开始字母时,系统将在单元格下方显示包含该字母开头的
所有有效函数的下拉列表,此时用户可以从列表中单击函数名查看该函数的联机帮助信
息再双击所需函数即可。如果该功能被关闭,则可通过下述方法启用。
单击“文件”选项卡中的“选项”按钮,打开“Excel选项”对话框,单击“公式”选项,选
中“公式记忆式键入”复选框。
2)通过“函数库”选项组输入
图3.5 “函数参数”对话框
当知道所需函数属于哪一类别时,可采用该方法。单击“公式”选项卡,从系统提供的
“函数库”选项组中单击所需函数所在类别的下拉箭头,从打开的下拉列表中选择所需函
数,打开“函数参数”对话框,如图3.5所示。按照“函数参数”对话框中的提示输入或选择
第3章 表格数据处理 37
参数,最后单击“确定”按钮。单击左下角的“有关该函数的帮助”选项可进一步了解有关
信息。
3)通过“插入函数”按钮输入
选中需要输入公式的单元格,单击编辑工具栏上的fx 按钮,打开“插入函数”对话
框,如图3.6所示。在“或选择类别”下拉列表中选择函数类别或在“搜索函数”框中输入
与该函数名相关的字母,单击“转到”按钮,然后从“选择函数”列表中选择所需函数,最后
单击“确定”按钮。
图3.6 插入函数
2.常用函数简介
1)求和函数
语法格式:
SUM(number1,[number2],…)
函数功能:把number1,number2,…相加求和。
参数说明:至少包含一个参数number1。参数可以是常量、单元格、单元格区域、公
式等。
【例3.1】 公式“=SUM(A1,B2:C5)”,表示将单元格A1及区域B2:C5中所有单元
格的数值相加;公式“=SUM(1+2,3,D3)”的作用等价于公式“=1+2+3+D3”。如
图3.7所示,如果需要求出每个学生的总分,可以在G2 单元格中输入“=SUM(D2:
F2)”,然后利用填充柄自动填充功能把G2的公式复制到区域G3:G13中(具体操作:将
光标置于G2单元格的填充柄上双击),如图3.8所示。
38 数据处理实践教程(微课版)
图3.7 原成绩单
图3.8 计算总分后的成绩单
2)条件求和函数
语法格式:
SUMIF(range,criteria,[sum_range])
函数功能:对指定区域中符合指定条件的单元格求和。
参数说明:
range:条件所在的区域。
criteria:求和的条件。其形式可以是数值、表达式、单元格、文本或函数。
注意:除数值、函数外,文本或含有逻辑或数学符号的条件都必须使用半角双引号
("")括起来。条件中可以使用通配符,? 匹配任意单个字符,*匹配任意一串字符。
【例3.2】 如图3.9所示,公式“=SUMIF(D2:D13,">80")”,表示将区域D2:D13
中单元格大于80的数值相加;公式“=SUMIF(B2:B13,"男",G2:G13)”,表示将区域
B2:B13中单元格等于“男”与区域G2:G13中对应单元格的数值相加,即把所有男生总分
第3章 表格数据处理 39
相加。
图3.9 成绩单数据
3)多条件求和函数
语法格式:
SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)
函数功能:对指定区域中符合多个指定条件的单元格求和。
参数说明:
sum_range:求和所在的区域。
criteria_range1:指定的条件1所在区域。
criteria1:指定的条件1。
criteria_range2,criteria2:可选项。最多可以允许127个区域及对应的条件。
注意:每个criteria_range参数区域所包含的行数和列数必须与sum_range参数
相同。
【例3.3】 如图3.9所示,公式“=SUMIFS(G2:G13,B2:B13,"男",F2:F13,">80")”,
表示若区域B2:B13中单元格等于“男”且区域F2:F13中对应单元格的数值大于80,则
把区域G2:G13中对应单元格的数值相加,即把所有男生中英语成绩大于80分的总分
相加。
4)数值单元格计数函数
语法格式:
COUNT(value1,[value2],…)
函数功能:统计指定区域中包含数字的单元格的个数。
参数说明:至少包含一个参数,最多可包含255个参数。
【例3.4】 如图3.9所示,公式“=COUNT(G2:G13)”,表示统计区域G2:G13中包
含数字的单元格的个数,即学生人数,结果为12;公式“=COUNT(A2:A13)”,表示统计
区域A2:A13中包含数字的单元格的个数,结果为0。
40 数据处理实践教程(微课版)
5)非空单元格计数函数
语法格式:
COUNTA(value1,[value2],…)
函数功能:统计指定区域中非空单元格的个数。
参数说明:至少包含一个参数,最多可包含255个参数。
【例3.5】 如图3.9所示,公式“=COUNTA(A2:A13)”,表示统计区域A2:A13中
非空单元格的个数,即学生人数,结果为12;公式“=COUNTA(G2:G13)”,表示统计区
域G2:G13中非空单元格的个数,结果为12。
6)条件统计函数
语法格式:
COUNTIF(range,criteria)
函数功能:统计指定区域中满足指定条件的单元格的个数。
参数说明:
range:条件所在的区域。
criteria:指定的条件。
【例3.6】 如图3.9所示,公式“=COUNTIF(B2:B13,"女")”,表示统计区域B2:
B13中单元格等于“女”的个数,即统计女生人数;公式“=COUNTIF(D2:D13,">80")”,
表示统计区域D2:D13中单元格大于80的个数,即统计语文成绩大于80分的人数。
7)多条件计数函数
语法格式:
COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)
函数功能:统计指定区域中符合多个指定条件的单元格的个数。
参数说明:
criteria_range1:指定的条件1所在区域。
criteria1:指定的条件1。
criteria_range2,criteria2:可选项。最多可以允许127个区域及对应的条件。
【例3.7】 在图3.9中,公式“=COUNTIFS(B2:B13,"女",E2:E13,">80")”,表示
统计区域B2:B13中单元格等于“女”且区域E2:E13中对应单元格大于80的个数,即女
生中数学成绩大于80分的人数。
8)平均值函数
语法格式:
AVERAGE(number1,[number2],…)
函数功能:求number1,number2,…的算术平均值。
第3章 表格数据处理 41
参数说明:至少包含一个参数,最多可包含255个参数。如果参数包含文本、逻辑值
或空单元格,则这些值将被忽略,但包含零值的单元格会被计算在内。
【例3.8】 在图3.9中,公式“=AVERAGE(D2:D13)”,表示对区域D2:D13中的数
值求平均值,即求语文平均分。
9)条件平均值函数
语法格式:
AVERAGEIF(range,criteria,[sum_range])
函数功能:对指定区域中符合指定条件的单元格求算术平均值。
参数说明:
range:条件所在的区域。
criteria:指定求算术平均值的条件。
注意:除数值、函数外,文本或含有逻辑或数学符号的条件都必须使用半角双引号括
起来。条件中可以使用通配符,? 匹配任意单个字符,*匹配任意一串字符。
【例3.9】 在图3.9中,公式“=AVERAGEIF(B2:B13,"男",D2:D13)”,表示将区域
B2:B13中单元格等于“男”与区域D2:D13中对应单元格的数值求算术平均值,即求出所
有男生的语文平均分。
10)多条件求平均值函数
语法格式:
AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,
criteria2],…)
V3.8 常见统计函数
函数功能:对指定区域中符合多个指定条件的单元格求算术
平均值。
参数说明:
average_range:求平均值所在的区域。
criteria_range1:指定的条件1所在区域。
criteria1:指定的条件1。
criteria_range2,criteria2:可选项。最多可以允许127个区域及对应的条件。
注意:每个criteria_range参数区域所包含的行数和列数必须与average_range参数
相同。
【例3.10】 在图3.9中,公式“=AVERAGEIFS(G2:G13,B2:B13,"男",F2:F13,
">80")”,表示若区域B2:B13中如果单元格等于“男”且区域F2:F13中对应单元格的数
值大于80,则把区域G2:G13中对应单元格的数值计算出算术平均值,即把所有男生中
英语成绩大于80分的总分计算出平均分。
11)最大值函数
语法格式:
42 数据处理实践教程(微课版)
MAX(number1,[number2],…)
函数功能:求出给定的一组数值或者指定区域中的最大值。
参数说明:至少有一个参数且必须是数值,最多可以有255个参数。
【例3.11】 公式“=MAX(12,34,23,76)”的结果是76;在图3.9中,公式“=MAX(G2:
G13)”,表示从区域G2:G13中求出最大值,即求出总分最高分。
12)最小值函数
语法格式:
MIN(number1,[number2],…)
函数功能:求出给定的一组数值或者指定区域中的最小值。
参数说明:至少有一个参数且必须是数值,最多可以有255个参数。
【例3.12】 公式“=MIN(12,34,23,76)”的结果是12;在图3.9中,公式“=MIN(G2:
G13)”,表示从区域G2:G13中求出最小值,即求出总分最低分。
13)排位函数
语法格式:
RANK(number,ref,[order])
函数功能:求出一个数值在指定数值列表中的排位。如果有多个相同的数值,则求
出其实际排位。
参数说明:
number:要确定其排位的数值。
ref:指定数值列表所在位置。
order:可选项。指定数值列表的排序方式,order取值为0(默认)时表示降序,order
取值不为0时表示升序。
【例3.13】 在图3.9中,公式“=RANK(G2,G2:G13)”,表示求出G2中的数值在区
域G2:G13各单元格存储的数值中的排位,即求出夏雪的总分在12个学生总分中的排
名。如果要把所有学生的总分排名都求出,要先将G2中的区域地址修改为绝对地址,即
“=RANK(G2,$G$2:$G$13)”,然后利用填充柄自动填充就可以求出每个学生的总
分排名。
14)绝对值函数
语法格式:
ABS(number)
函数功能:求出给定数值的绝对值。
参数说明:参数是必需的。
第3章 表格数据处理 43
【例3.14】 公式“=ABS(-3)”,表示求-3的绝对值,结果为3;公式“=ABS(K2)”,
表示求单元格K2中的数值的绝对值。
15)向下取整函数
语法格式:
INT(number)
函数功能:求出给定数值向下取整为最接近的整数。
参数说明:参数是必需的。
【例3.15】 公式“=INT(2.53)”表示求2.53向下取整为最接近的整数,结果为2;公
式“=INT(-2.53)”表示求-2.53向下取整为最接近的整数,结果为-3。
16)四舍五入函数
语法格式:
ROUND(number,num_digits)
函数功能:按指定的位数对数值进行四舍五入。
参数说明:
number:要确定四舍五入的数值。
num_digits:当num_digits大于0时,表示对number的小数部分进行四舍五入;当
num_digits等于0 时,表示number只保留到整数;当num_digits小于0 时,表示对
number的整数部分进行四舍五入。
【例3.16】 公式“=ROUND(235.567,2)”的结果为235.57;公式“=ROUND
(235.567,0)”的结果为236;公式“=ROUND(235.567,-1)”的结果为240。
17)随机数函数
语法格式:
RAND()
函数功能:产生一个范围在[0,1)的随机小数。
参数说明:没有参数。
注意:虽然没有参数,但圆括号不能省略。
【例3.17】 公式“=RAND()”的结果为0.6119117。(每次运行的结果不同)
18)指定范围内的随机整数函数
语法格式:
RANDBETWEEN(bottom,top)
函数功能:产生一个指定范围的随机整数。
参数说明:
bottom:返回的最小整数。
44 数据处理实践教程(微课版)
top:返回的最大整数。
【例3.18】 公式“=RANDBETWEEN(1,100 )”的结果为46(每次运行的结果
不同)。
19)求余数函数
语法格式:
MOD(number,divisor)
函数功能:求出两个数相除的余数,结果的符号与除数相同。
参数说明:
number:被除数。
divisor:除数,应不能为零。
【例3.19】 公式“=MOD(7,3)”的结果为1。
20)截取字符串函数
语法格式:
MID(text,start_num,num_chars)
函数功能:从指定的文本中按照指定位置开始截取指定个数的字符。
参数说明:
text:指定进行截取的文本。
start_num:要截取的第一个字符在text中的位置,text中第一个字符的位置为1。
num_chars:指定截取字符的个数。
【例3.20】 公式“=MID(A5,4,5)”,表示从A5单元格的文本中第4个字符开始共
提取5个字符。
21)左侧截取字符串函数
语法格式:
LEFT(text,[num_chars])
函数功能:从指定的文本最左边开始截取指定个数的字符。
参数说明:
text:指定进行截取的文本。
num_chars:可选项,指定截取字符的个数。num_chars必须大于0,若省略,该参数
默认值为1。
【例3.21】 公式“=LEFT(A5,4)”,表示从A5单元格的文本左边第1个字符开始
共提取4个字符;公式“=LEFT("ABC",2)”的结果为AB;公式“=LEFT("ABC")”的结
果为A。
22)右侧截取字符串函数
语法格式:
第3章 表格数据处理 45
RIGHT(text,[num_chars])
函数功能:从指定的文本从最右边开始截取指定个数的字符。
参数说明:
text:指定进行截取的文本。
num_chars:可选项,指定截取字符的个数。num_chars必须大于0,若省略,该参数
默认值为1。
V3.9 字符串函数
使用举例
【例3.22】 公式“=RIGHT(A5,4)”,表示从A5单元格的文本右
边第1个字符开始共提取4个字符;公式“=RIGHT("ABC",2)”的结
果为BC;公式“=RIGHT("ABC")”的结果为C。
23)计算字符串长度函数
语法格式:
LEN(text)
函数功能:计算指定的字符串包含的字符个数。
参数说明:
text:指定进行计算的字符串。
【例3.23】 公式“=LEN(A5)”表示计算A5单元格中包含的字符个数。
24)当前系统日期和时间函数
语法格式:
NOW()
函数功能:返回当前计算机系统的日期和时间。
参数说明:
没有参数。
【例3.24】 假设当前计算机系统日期为2021年5月26日,时间为11点13分,那么
公式“=NOW()”的结果为2021-5-2611:13(显示内容与单元格格式设置有关)。
25)当前系统日期函数
语法格式:
TODAY()
函数功能:返回当前计算机系统的日期。
参数说明:没有参数。
【例3.25】 假设当前计算机系统日期为2021年5月26日,那么公式“=TODAY()”
的结果为2021-5-26。
26)获取年份函数
语法格式:
46 数据处理实践教程(微课版)
YEAR(serial_number)
函数功能:返回指定日期中的年份,返回值为1900~9999的整数。
参数说明:
serial_number:一个日期值(数值)。
【例3.26】 假设当前计算机系统日期为2021年5月26日,那么公式“=YEAR
(NOW())”的结果为2021。
27)获取月份函数
语法格式:
MONTH(serial_number)
函数功能:返回指定日期中的月份,返回值为1~12的整数。
参数说明:
serial_number:一个日期值(数值)。
【例3.27】 假设当前计算机系统日期为2021年5月26日,那么公式“=MONTH
(NOW())”的结果为5。
28)获取日期中的日的函数
语法格式:
DAY(serial_number)
函数功能:返回指定日期中的日的数值,返回的值为1~31的整数。
参数说明:
serial_number:一个日期值(数值)。
【例3.28】 假设当前计算机系统日期为2021 年5 月26 日,那么公式“=DAY
(NOW())”的结果为26。
29)指定日期函数
语法格式:
DATE(year,month,day)
函数功能:返回指定的日期。
参数说明:
year:表示年份的整数,范围是1900~9999。
month:表示月份的整数,范围是1~12,若大于12,则系统自动将年份加1。
day:表示第几天的整数,范围是1~31,若超出范围,则系统自动将月份加1。
【例3.29】 公式“=DATE(1998,6,12)”的结果为1998-6-12;公式“=DATE(1998,
13,33)”的结果为1999-2-2。
30)计算两个日期之间相隔的天数、月数或年数的函数
语法格式:
第3章 表格数据处理 47
DATEDIF(start_date,end_date,unit)
函数功能:返回两个指定日期之间相隔的天数、月数或年数。
参数说明:
start_date:是一个日期值(数值)。
end_date:是一个日期值(数值)。
unit:返回值的类型。
具体内容如表3.4所示。
表3.4 DATEDIF函数第三个参数的设置与返回值
参数函数返回值
"y" 返回两个日期值间隔的整年数
"m" 返回两个日期值间隔的整月数
"d" 返回两个日期值间隔的整天数
"md" 返回两个日期值间隔的整天数(忽略日期中的年和月)
"ym" 返回两个日期值间隔的整月数(忽略日期中的年和日)
"yd" 返回两个日期值间隔的整天数(忽略日期中的年)
【例3.30】 假设当前系统日期为2021年5月26日,A1单元格的内容为日期值
1993年8月21日,那么公式“=DATEDIF(A1,NOW(),"y")”的结果为28。也就是说,
如果A1单元格表示的是某人的出生日期,那么上述公式的结果就是其年龄。
注意:该函数没有系统自动提示功能,但不影响使用。
31)逻辑“与”函数
语法格式:
AND(logica1,[logical2],…)
函数功能:仅当所有参数的计算结果都为TRUE 时,函数返回TRUE,否则返回
FALSE。
注意:较少单独使用,当同时有多个条件需要描述时,表示条件之间的关系。
32)逻辑“或”函数
语法格式:
OR(logica1,[logical2],…)
函数功能:仅当所有参数中有一个条件的计算结果为TRUE时,函数返回TRUE,
否则返回FALSE。
注意:较少单独使用,当同时有多个条件需要描述时,表示条件之间的关系。
33)条件函数
语法格式: