Excel 中,函数与公式无疑是最具有魅力的功能之一。使用函数与公式,能帮助用户完成多种要求的数据运算、汇总、提取等工作。函数与公式同数据验证功能相结合,能限制数据的输入内容或类型,还可以制作动态更新的下拉菜单。函数与公式同条件格式功能相结合,能根据单元格中的内容,显示出用户自定义的格式。在高级图表、透视表等应用中,也少不了函数与公式的身影。
常见数据类型
Excel 中的数据类型包括数值、文本、日期时间及逻辑值和错误值。
数值
数值是指所有代表数量的数字形式,如企业的产值和利润、学生成绩、个人的身高体重等。数值可以是正数,也可以是负数,并且都可以用于计算。除了普通的数字外,还有一些带有特殊符号的数字也会被 Excel 识别为数值,如百分号(%)、货币符号(如¥)和千分间隔符(,)。如果字母 E 在数字中恰好处于特定的位置,还会被 Excel 识别为科学记数符号。例如,输入“5e3”,会显示为科学记数形式 5.00E+03,即 5.00 乘以 10 的 3 次幂。
在 Excel 中,由于软件自身的限制,对于数值的使用和存储存在一些规范和限制。
Excel 可以表示和存储的数字最大精确到 15 位有效数字。对于超过 15 位的整数数字,如 1234 567 890 123 456 789,Excel 会自动将 15 位以后的数字变为 0 来存储。对于大于 15 位有效数字的小数,则会将超出的部分截去。
因此,对于超出 15 位有效数字的数值,Excel 将无法进行精确的计算和处理。例如,无法比较相差无几的 20 位数字的大小、无法用数值形式存储 18 位的身份证号码等。
文本
文本通常是指一些非数值性的文字、符号等,如企业名称、驾校考试科目、员工姓名等。此外,很多不需要进行数值计算的数字也可以保存为文本形式,如电话号码、身份证号码、银行卡号等。在公式中直接输入文本内容时,要用一对半角双引号("")包含,如公式“=A1="ExcelHome"”。
日期和时间
在 Excel 中,日期和时间是以一种特殊的“序列值”形式存储的。
在 Windows 操作系统上所使用的 Excel 版本中,日期系统默认为“1900 日期系统”,即以1900 年 1 月 1 日作为序列值的基准日期,这一天的序列值计为 1,这之后的日期均以距离基准日期的天数作为其序列值。例如,1900 年 1 月 15 日的序列值为 15。Excel 中可表示的最大日期是9999 年 12 月 31 日,其日期序列值为 2 958 465。
输入日期后再将单元格数字格式设置为“常规”,此时就会在单元格内显示该日期的序列值。
提示
Macintosh 操作系统下的 Excel 版本默认日期系统为“1904 日期系统”,即以1904 年 1 月 1 日作为日期系统的基准日期。另外,为了保持与 Lotus 1-2-3 相兼容,在 Excel 的日期系统中还保留了并不存在的 1900 年 2 月 29 日(1900 年不是闰年)。
作为一种特殊的数值形式,日期承载着数值的所有运算功能。例如,要计算两个日期之间相距的天数,可以直接在不同单元格中分别输入两个日期,在第 3 个单元格使用单元格引用并用减法运算。
日期序列值是一个整数,时间的序列值则是一个小数。一天的数值单位是 1,1 小时可以表示为 1/24 天,1 分钟可以表示为 1/(24 * 60) 天,一天中的每一个时刻都可以由小数形式的序列值来表示。例如,中午 12:00:00 的序列值为 0.5(一天的一半),12:30:00 的序列值近似为 0.520833。
将小数部分表示的时间和整数部分表示的日期结合起来,就能以序列值表示一个完整的日期时间点。例如,2020 年 9 月 10 日中午 12:00:00 的序列值为 44 084.5。
对于不包含日期的时间值,如“12:30:00”,Excel 会自动以 1900 年 1 月 0 日这样一个实际不存在的日期作为其日期值。
如需在公式中直接输入日期或时间,需要用一对半角双引号("")包含,如公式“="2021-8-15"-15”或“="10:24:00"+"0:30:00"”。
逻辑值
逻辑值包括TRUE(真)和FALSE(假)两种。假设A3单元格为任意数值,使用公式“=A3>0”,就能够返回TRUE(真)或FALSE(假)。
逻辑值之间进行四则运算或是逻辑值与数值之间的运算时,TRUE 的作用等同于 1,FALSE 的作用等同于 0。例如,FALSE * FALSE=0,TRUE-1=0
错误值
用户在使用 Excel 的过程中可能会遇到一些错误值信息,如 #N/A、#VALUE!、#DIV/0! 等,出现这些错误值有多种原因,常见的错误值及产生的原因如表 1-1 所示。
表 1-1 常见错误值及产生的原因
- 错误值:#VALUE!
产生的原因:在需要数字或逻辑值时输入了文本,Excel 不能将其转换为正确的数据类型 - 错误值:#DIV/0!
产生的原因:使用 0 值作为除数 - 错误值:#NAME?
产生的原因:使用了不存在的名称或是函数名称拼写错误 - 错误值:#N/A
产生的原因:在查找函数中,无法找到匹配的内容 - 错误值:#REF!
产生的原因:删除了有其他公式引用的单元格或工作表,致使单元格引用无效 - 错误值:#NUM!
产生的原因:在需要数字参数的函数中,使用了不能接受的参数 - 错误值:#NULL!
产生的原因:- 在公式中未使用正确的区域运算符,产生了空的引用区域,例如将公式“=SUM(A3 : A10)” 误写为“=SUM(A3 A10)”;
- 在区域引用之间使用了交叉运算符(空格字符)来指定不相交的两个区域的交集。
例如,公式“=SUM(A1:A5 C1:C3)”,A1:A5 和 C1:C3 没有相交的区域
当单元格中所含的数字超出单元格宽度,或者在设置了日期、时间的单元格内输入了负数,将以 # 号填充。
如果将使用数据模型创建的数据透视表通过【OLAP 工具】→【转换为公式】命令转换为公式,当参数设置错误时,会瞬时显示为错误值 “#GETTING_DATA”,再转换为错误值 “#N/A”。
提示
在 Microsoft 365 版本的 Excel 中,还增加了部分新的错误值类型,如错误值“#SPILL!”表示公式的溢出区域不是空白区域。在 Microsoft 365 版本的 Excel 中,还增加了部分新的错误值类型,如错误值“#SPILL!”表示公式的溢出区域不是空白区域。
除此之外,如果数据源中本身含有错误值,大多数公式的计算结果也会返回错误值。
【END】