数据类型划分标准

数据类型和格式化 - 图1

  • 比如公式：=SUM(A1:A8,20)，A1:A8是引用，20则不是
  • 比如公式=SUM(A1:A8,20)，20是常量，A1:A8是变量
  • 比如公式=A1&”元”，”元”是常量，A1是变量，它会随A1单元格的值改变而发生改变。

image.png

常见的数字格式

数值型

需要注意：

  1. 负数：在数值前加一个 - 号或把数值放在英文小括号 () 里，都可以输入负数，
  2. 分数：要在单元格中输入分数形式的数据，应先在编辑框中输入“0”和一个空格，然后再输入分数，否则Excel会把分数当作日期处理。例如，要在单元格中输入分数“2/3”，在编辑框中输入“0”和一个空格，然后接着输入“2/3”，敲一下回车键，单元格中就会出现分数“2/3”。

image.png

字符串

  • 非数值型的文本，不能参与数学运算，否则返回错误值
  • 但数值型的文本可以参与数学运算，不过会被转换为真正的数值。

数据类型和格式化 - 图4

image.png

日期和时间

在Excel中，日期和时间是以一种特殊的数值形式储存的，这种数值形式又被称为“序列值”。

  • 最小的日期是1900年1月1日，序列值为1
  • 1900年1月3日，序列值为3
  • 最大日期是9999年12月31日，序列值是2958465。

由于日期是以数值的形式储存的，所以它可以直接参与加减乘除的数值运算

  • 例如 =”2020-9-3”+100 返回 44177 的计算结果

数据类型和格式化 - 图6
当日期以常量的形式出现在公式中时，是文本的形式，需要转换为数值类型才能参与计算

  • =IF(A1>”2019-6-12”,”生日已过”,”未到生日”) 的计算结果是错误的
  • =IF(A1>”2019-6-12”*1,”生日已过”,”未到生日”) 是正确的

公式使用 *1 的数学运算的方式，能将文本时间或日期转换为数值。

对于日期来说，1天的数值单位就是1，那么1小时就可以表述为1/24，同理，1分钟可以表述为1/24/60。

也就是说，一天中的每一个时刻都可以由小数形式的序列值来表示。比如，中午12点也就是0.5（1天的一半1/2）。

同日期一样，时间也可以直接参与加减乘除的数值运算。

  • =IF(A1>”9:00”,”迟到”,”未迟到”) 计算结果错误
  • =IF(A1>”9:00”*1,”迟到”,”未迟到”) 计算结果是正确的
  • =”9:56”-“6:28”

时间.gif

逻辑值

逻辑值只有两个。

  • 一个FALSE，假，参与数学运算时等同 0
  • 一个是TRUE，真，与数学运算时等同 1

例如：

  • 公式=TRUE+1，等同于公式=1+1，结果为2。
  • 公式=FALSE+1，等同于公式=0+1，结果为1。

错误值

错误值有多种形式，常见的有#VALUE!、#N/A等。

单元格的值

我们看到的单元格显示的值不是它真正的值，这里看到A1显示的是中文大写数字，好像是文本格式，但是看编辑栏显示的是一堆数字，这才是它的值！
单元格的值要看编辑栏的内容
image.png**
image.png

数据格式化

我们通过设置单元格格式（Ctrl+1）可以将值（渲染）转化为我们想看到的格式
数据格式化不会改变数据本身的值，只是改变了它的显示方式
image.png

如何设置数据格式化呢

  • 快捷键（不常用）
  • 【开始】-【数字】
  • Ctrl+1（推荐）

image.png

Excel内置的数字格式

image.png

  • 货币：会自动使用千位分隔符
  • 会计专用：会将0转为小横杠-
  • 文本：输入的数据会被转换为文本值。比如输入公式，显示为公式自身。
  • 特殊：邮政编码、中文小写数字和中文大写数字。不过中文数字有其局限性，无法正确处理小数值。
  • 自定义

自定义数字格式

规则：自定义格式不会改变数字的原意

数字 0

数字占位符：帮数字占个位置

一个0就代表一个数字

  • 实际数字位数大于0的个数，则显示实际数字
  • 实际数字位数小于0的个数，则用0补足

如：0000，123456显示为123456，123显示为0123
这里为什么只占了四个位置却能显示6个数字，记住自定义格式不能改变数字的原意，120不能变为12

如：00.000，99.14显示为99.140，1.1显示为01.100

例如：按照“3+4+4”的格式显示电话号码。000 0000 0000
数据类型和格式化 - 图13

井号

数字占位符：设置了“#”占位符的单元格，只显示有意义的零，小数点后面的数字如果多于“#”的数量，则按“#”的位数四舍五入。
如：###.##，56.7显示为56.7，而78.669显示为78.67
image.png
image.png

问号 ？

数字占位符。在小数点两边为无意义的零添加空格，使不等长的数据按小数点的位置排齐
image.png

艾特 @

文本占位符：用了引用原始文本，@可以理解为变量，而这个变量的值就是单元格的值
image.png

逗号 ,

千位分隔符：常用于显示金额列，这样设置后显示的金额直观已读，可读性非常高。
代码：#,##，15300012，显示为15,300,012

星号 *

重复下一个字符，直到填充满列宽。
点击单元格后在地址栏可以看到原始内容，双击单元格进入编辑状态也可以看到原始内容，某些时候可以用于仿真密码保护。

如：@*-，”good”则显示为”good—————-“
如：**（两个号），则将单元格的内容全部换成号显示。
如：@**，单元格值“Excel”则显示为：Excel*
数据类型和格式化 - 图18

转义符 ! 和 \

自定义格式中，””、#、？等都是有特殊意义的字符，如果想在单元格中显示这些字符，需要在符号前加 !或 \
image.png

颜色

可选颜色为：红色、黑色、黄色、绿色、白色、蓝色、青色和洋红共8种。
如：[蓝色];[红色];[黄色];[绿色]表示为

  • 正数用蓝色的字体颜色标识
  • 负数用红色的字体颜色标识
  • 0用黄色的字体颜色标识
  • 文本用绿色的字体颜色标识

数据类型和格式化 - 图20

颜色搭配条件使用：[绿色][<0]↑ G/通用格式;[红色][>=0]↓ G/通用格式
image.png

条件

对单元格内容进行判断后设置单元格格式，条件要放在中括号 [ ] 内。
如：[>=60]"及格";[<60]"不及格"，可以对成绩标注出是否及格。
数据类型和格式化 - 图22

目的：将≥60的销量填充为“蓝色”，＜60的销量填充为“红色”。[红色][<60];[蓝色][>=60]
数据类型和格式化 - 图23

日期和时间

  • Y是year的首字母
  • M是month的首字母
  • D是day的首字母

  • 一般情况下不区分大小写 | | 代码 | 效果 | 代码 | 效果 | | —- | —- | —- | —- | —- | | 年 | YYYY或e | 1999 | YY | 99 | | 月 | MM | 02 | M | 2 | | 日 | DD | 01、31 | D | 1、31 | | 星期 | aaaa | 星期日 | aaa | 日 |

  • h代表小时

  • m代表分钟
  • s代表秒
  • hh:mm:ss表示小时:分:秒
  • 此外[h]、[m]、[s]分别表示当日累加的小时、分钟、秒

image.png

条件区间

自定义格式默认划分为了4个条件区间，不同区间以分号间隔（逻辑值属于文本区间）

  • 正数;负数;零;文本或者
  • [>0];[<0];[=0];文本

数据会依次匹配区间，匹配成功返回区间的值，不成功返回原始数据
例如，正数;负数;零;文本，代码的意思如果数据是正数则返回”正数”这两个字，负数返回”负数”，零返回”零”，文本返回”文本”
image.png
也可以自定义区间，如：[>=60]"及格";[<60]"不及格"，

参考链接

https://wenku.baidu.com/view/ea786b5d77c66137ee06eff9aef8941ea76e4b8c.html