数据分析师训练营/ 第二篇 Excel/ 第五章
第五章 · Excel 数据模型

Excel 是怎么存数据的

这一章不教你点按钮,只讲 Excel 的存储规则。要回答的问题只有一个: 为什么同样的数字,Excel 的处理方式不一样? 同样敲进去 1280,有的格子能求和,有的被 SUM 当成空气;同样写着 2024-06-01,有的能按月分组, 有的一排序就乱;一个横跨五列的标题,拆开之后只有一个格子有值。

这些不是 bug,是 Excel 的存储规则。本章拆开四件事:格子能装什么、日期到底是什么、 合并单元格存了什么、透视表为什么只认一种表。学完你再看表,看到的会是类型、序列号和结构。

理论 : 实操 = 9 : 1 约 4 学时 前置 第 4 章

5.1原理:格子里到底装了什么

把镜头推到最近:一个单元格。你敲一个字符,Excel 要决定三件事——存成什么类型、配什么格式、 允许它参加哪些运算。这三个决定,决定了你后面能不能求和、排序、做透视表。

5.1.1五种单元格类型

按输入形态分,单元格有五种:数值、文本、日期、逻辑、错误。但把 .xlsx 拆开,值只有四种:数字、字符串、布尔值、错误码。日期不在其中——它就是数字。

类型底层存什么默认对齐例子SUM 会怎样
数值双精度浮点数靠右1280 / 3.14正常参与计算
文本字符串靠左"1280"、QN2406001跳过,不报错
日期数值(序列号)靠右2024-06-01(=45444)能算,但通常无意义
逻辑布尔值居中TRUE / FALSE区域求和时忽略
错误错误码居中#N/A、#DIV/0!传染,整列变错

数值用双精度存,只保证 15 位有效数字,超过就抹成 0。文本会被 SUM、 AVERAGE、COUNT 跳过,但 LEN、LEFT 能用。 日期是穿了外套的数值。逻辑值参与算术时记 1 和 0,放进 SUM 区域却被忽略。 错误值以 # 开头,会传染:一列里有一个 #N/A,SUM 整列就是 #N/A。

ABCD
1QN2406001001281,280.002024-06-01
2TRUE#N/A3.1420240601
图 5.1.1 文本靠左,数值和日期靠右,逻辑和错误居中

判断类型不能看它“写了什么”,要看它“怎么存”。图里 C1 和 D2 都像日期,但 C1 是真日期, D2 只是数字 20240601。对齐、绿三角、公式,是三种由浅入深的判断办法,5.2 节逐个讲。

1

一个格子只装一个值,这个值只有一个类型

单元格是“单值”容器。换行、加粗、变色都只是格式;值本身还是一个。按存储分四类—— 数值、文本、逻辑、错误;日期是数值的显示形态。类型在值写进格子的那一刻就定了。

如果不这样会怎样:第 6 章那张 480 行的订单表,40 个金额是文本。去重后直接 SUM 得 1,115,121.36,比正确的 1,208,928.65 少 93,807.29。Excel 不报错, 因为它认为那 40 格“本来就不是数字”。

数字是怎么变成文本的:三条路径

第一条,导入。CSV、TXT、ERP 导出、网页复制,导入时那一列被设成“文本”, 或源数据带引号、前导零、千分位,Excel 就存成字符串。

第二条,粘贴。从微信、PDF、网页复制,常带首尾空格、不换行空格(CHAR(160))、 换行符;一有非数字字符,就判文本。

第三条,格式。先把格子设成“文本”再输入,或输入时加英文单引号 '1280。 单引号不显示,但强制文本。还有公式算出的文本:="1,280"、 TEXT(A1,"#,##0.00")、A1&B1。

5.1.2日期是序列号:1900 日期系统,2024-06-01 = 45444

Excel 存日期只存一个数:1900-01-01 是第 1 天,往后每天加 1。所以 2024-06-01 = 45444; 时间是它的小数部分——早上 6 点 .25,中午 .50,晚上 .75。

1900-01-01
=
1
…
2024-01-01
=
45292
+152
2024-06-01 = 45444
06:00 = .25
12:00 = .50
18:00 = .75
图 5.1.2 日期是整数序列号,时间是它的小数部分

为什么这么存?日期要能加减、排序、按月分组,字符串做不到。两个日期相减得到天数, 一个日期加 30 得到一个月后,排序就是比大小。为什么从 1900 年开始?历史包袱: 1985 年 Excel 诞生时 Lotus 1-2-3 是霸主,Excel 照抄它的序列号,后来成了事实标准。

这套 1900 日期系统有个著名 bug:1900 年不是闰年,但 Lotus 算错了,微软为兼容原样保留。序列号 60 对应不存在的 1900-02-29,之后的日期序列号都比真实天数多 1。对现代数据没影响,面试常问。 另有一套 1904 系统,早期 Mac 版 Excel 用它,和 1900 系统差 1462 天;把 1904 工作簿的日期 复制到 1900 工作簿,会整体偏 4 年。

长得像日期,不等于日期。2024-06-01 和 2024/6/1 可以是同一个日期, 也可以是两个字符串,区别就在类型。文本日期排序按字符逐位比较,2024/10/1 会排在 2024/2/1 前面,因为月份第一位 1 小于 2。它不能按月分组—— 透视表会把 5 种写法当成 5 个标签;也不能相减,=B2-B1 返回 #VALUE!。 真日期相减得到天数,但如果结果格是日期格式,你会看到 1900-01-30 而不是 30。

2

日期是数字,不是“长得像日期”的字符串

真日期的存储是序列号,显示是格式。它能排序、相减、按月分组,因为底层就是数字。 文本日期只是一串字符,Excel 不会把它当时间看。

如果不这样会怎样:同一列 5 种日期写法,如果都是文本,透视表会把它们当成 5 个标签, 6 月的销售额被拆成 5 份;排序时 10 月会跑到 2 月前面。

5.1.3合并单元格只存左上角一个值

选中 A1:E1,合并后居中,输入标题。屏幕上是横跨五列的大格子,但把 .xlsx 改成 .zip 解压,打开 sheet1.xml,只有一行 <mergeCell ref="A1:E1"/> 和 A1 里的字符串;B1 到 E1 连记录都没有。

ABCDE
16 月订单明细
实际存储 "6 月订单明细" 无记录无记录无记录无记录
2订单号下单时间客户金额备注
图 5.1.3 合并只是画框,值仍然只在左上角

后果是一串的:COUNTBLANK(A1:E1) 数出 4 个空;筛选时只有 A1 有值; 排序会警告合并大小不一致;透视表把其余行归入“(空白)”。

假设 A2:A5 合并成“华东”,四行各 100 元。透视表得到 华东 100、空白 300, 不是华东 400——Excel 只看到 A2 有值。取消合并只保留左上角,A3:A5 变空;不补值, 结构修好了数据还是错的。只想要标题跨列居中,用“跨列居中”,它不合并。

3

合并单元格不是“一个格子”,是“一片格子只留一个值”

合并区域里只有左上角有值,其余是真空。屏幕上的“一个大格子”,只是边框和底色画在了一起。

如果不这样会怎样:A2:A5 合并成“华东”,四行各 100 元,透视表汇总出来是 “华东 100、空白 300”,不是“华东 400”。取消合并后不补值,A3:A5 仍然是空的。

5.1.4一维表与二维表:透视表的硬性前提

一维表三条:只有一个表头行;一行一条记录;一列一个字段。二维表行和列都是维度,值在交叉点。 人看着舒服,机器没法用。

二维表(交叉表)
地区1 月2 月3 月
华东120135150
华南9088102

月份是列名,不是字段。

一维表(长表)
地区月份销售额
华东1 月120
华东2 月135
华南1 月90

每个维度都是一列值。

图 5.1.4 同样是“地区 × 月份”,左边只能看,右边才能算

透视表只认一维表,因为字段列表是从表头读出来的,行、列、值、筛选拖的都是字段。二维表里“地区”是字段, “1 月”“2 月”也各是字段,但“月份”不存在——它被拆在 12 个列名里。拖不到行区,筛不了月份, 算季度只能手写 =B2+C2+D2。一维表里月份是一列值,拖到行区自动展开 12 个月; 新增 13 月只是多一行。维度必须作为一列的值存在,不能藏在列名里。

对比项一维表二维表
结构一行一条记录,一列一个字段行和列都是维度,值在交叉点
透视表任意拖拽、任意组合只有 12 个“求和项”,拖不出月份
新增月份加一行数据即可加一列,还要改公式和区域
月度趋势一条折线直接画要先把 12 列转成 12 行
筛选按任意维度筛选只能按列名一列一列看

三种伪装:月份横排、指标横排(销售额、成本、利润各一列)、多级表头。共同点是 本该在列里的值,跑到了表头里。

4

分析用的表必须是一维表:一行一条记录,一列一个字段

一维表不是排版风格,是数据结构。它保证每个维度都能被当成字段来分组、筛选、聚合。 透视表、切片器、图表、Power Query 都建立在这个前提上。

如果不这样会怎样:一张 12 个月横排的表,做透视表只能得到 12 个“求和项”, 拖不出“月份”这个维度,也算不出一条月度趋势;新增一个月,还得改公式、改区域。

5.1.5数字格式 ≠ 值

输入 1280,格式设成 #,##0.00,显示 1,280.00;选中它,编辑栏还是 1280。格式只改显示。如果这串字符是复制来的,或用公式 ="1,280.00" 生成, 它就是文本。

格式代码你看到的实际存储
常规12801280
#,##0.001,280.001280
¥#,##0.00¥1,280.001280
0%128000%1280
0.00E+001.28E+031280
#,##0.00" 元"1,280.00 元1280
图 5.1.5 六种长相,同一个值:格式是外套,值才是身体

格式代码存在 styles.xml 里,单元格只存值和一个样式编号。所以:

5

格式只影响显示,不影响值

数字格式是一张“显示说明书”。它决定 1280 写成 1,280.00 还是 1.28E+03,但不改变格子里存的是 1280。

如果不这样会怎样:把文本金额的格式改成“数值”,它仍然靠左、仍然不能求和。 你以为改好了,一按 SUM 还是少 9 万多。

5.2类型诊断的三种方法

类型判断不能靠猜。三招从粗到细:前两招快速扫一遍,第三招给确定答案。

5.2.1看对齐方式

默认对齐:文本靠左,数值和日期靠右,逻辑和错误居中。绝大多数表成立,但别人手动改过对齐就失效。 所以只能当初筛,不能当确诊。

5.2.2看绿三角

绿三角是错误检查标记,最常见含义是“看起来像数字的文本”。三个局限:可以关掉;也会标其他错误; 普通文本(QN2406001)不标。所以:有绿三角基本是文本;没有,不能反推数值。

5.2.3用 ISNUMBER 和 ISTEXT

公式最可靠。=ISNUMBER(A1) 为 TRUE 是数值(真日期也 TRUE);=ISTEXT(A1) 为 TRUE 是文本;=ISBLANK 判断真空(="" 不算);=ISERROR 判断错误。=TYPE(A1):1 数值、2 文本、4 逻辑、16 错误、 64 数组。整列用 =COUNT(A:A) 和 =COUNTA(A:A) 相减,就是非数值的 非空格子数——第 6 章 40 个文本金额就是这么数出来的。

单元格显示公式返回
A11,280.00=ISNUMBER(A1)TRUE
B11,280.00=ISNUMBER(B1)FALSE
C12024-06-01=TYPE(C1)1
D1TRUE=ISTEXT(D1)FALSE
E1#N/A=ISERROR(E1)TRUE
图 5.2.1 对齐和绿三角只能提示,公式才能确诊
方法看什么可靠度局限
看对齐左 / 右 / 居中中手动改过对齐就失效;空格子看不出来
看绿三角左上角有没有绿色标记中高可以关闭;普通文本不标;不区分日期和数字
用公式ISNUMBER / ISTEXT高要会写公式;整列检查要加辅助列
顺序:先扫对齐,把明显靠左的挑出来;再看绿三角,确认“像数字的文本”; 最后对金额、日期加一列 ISNUMBER 公式批量复核。前两招省时间,第三招保命。

5.3实验:20 个单元格的类型鉴定

下面 4 行 5 列,20 个格子,全部是“看到的显示”,没有编辑栏。判断每个格子的真实类型, 不用打开 Excel,拿一张纸就能做。

ABCDE
1QN24060011,280.002024-06-01 TRUE#N/A
2001281,280.002024/6/1 FALSE3.14
3202406012024-06-01 06:00¥1,280.00 128000%二零二四年六月一日
41.28E+031,280.00 元45444 12:00:00N/A
图 5.3.1 20 个待鉴定的单元格:只看显示、对齐和绿三角,你能判断几个?
已完成 0 / 3 预计 15 分钟
01

只看显示,写下初判

5 分钟 · 对应规则 1、5
做什么
给 20 个格子各写一个类型。
为什么
先暴露直觉:很多人把 20240601 当日期、把带绿三角的 1,280.00 当数值、把 N/A 当错误。
怎么做
遮住答案,按 A1 → E4 写,拿不准的标问号。
看到什么
应该有 3–5 个问号,它们就是第二遍的重点。
02

用对齐和绿三角修正

5 分钟 · 对应规则 1
做什么
按“左文本、右数值或日期、中逻辑或错误”重判。
为什么
对齐由类型自动决定,比长相可靠;绿三角直接说明是文本型数字。
怎么做
A2、B2、C2 带绿三角,判文本;D1、D2、E1 居中,只可能是逻辑或错误。
看到什么
问号剩 1–2 个,比如 C4 的 45444,必须靠公式。
03

用公式复核拿不准的格子

5 分钟 · 对应 5.2.3
做什么
对每个问号,写一条 ISNUMBER、ISTEXT 或 TYPE。
为什么
公式读存储类型,不读显示,不会被人为的对齐或格式骗到。
怎么做
输入 =ISNUMBER(A1)、=ISTEXT(A1),把 A1 换成那个格子的地址; 再用 =TYPE(A1),返回 1 就是数值。
看到什么
TRUE / FALSE 给确定答案,再打开答案逐格对照。
参考答案(20 格全解,先自己做再打开)

文本(6 个):A1 字母开头;A2 前导零;B2、C2 带绿三角;E3 中文数字; E4 的 N/A 是字符串,不是错误值(错误值必须带 #)。

数值(8 个):B1 千分位只是格式;E2 小数;A3 的 20240601 是八位数,不是日期; C3 货币、D3 百分比(值 1280)、A4 科学计数、B4 自定义单位;C4 是裸序列号,没套日期格式。

日期(3 个):C1 真日期;B3 日期加时间,45444.25;D4 只有时间,0.5。

逻辑(2 个):D1 的 TRUE、D2 的 FALSE。错误(1 个):E1 的 #N/A。

统计:8 数值、6 文本、3 日期、2 逻辑、1 错误。答对 16 个以上算过关。

5.4验证:8 道判断题

不看笔记,直接选。每选完一题,解析立刻出现;八题做完,得分自动累计。

Q1某单元格显示 1,280.00,编辑栏显示 1280。它的存储类型是?
解析:编辑栏是 1280,说明值就是 1280;1,280.00 只是 #,##0.00 的显示。规则 5。
Q2在默认的 1900 日期系统里,2024-06-01 存的是什么?
解析:2024-01-01 是 45292,1–5 月 152 天,45292 + 152 = 45444。规则 2。
Q3一列文本日期 2024/10/1、2024/2/1、2024/6/1 升序排序,结果是?
解析:文本逐位比较,前 5 位都是 2024/,第六位是 1、2、6,所以 10 月最前。规则 2。
Q4把 A1:E1 合并后输入“6 月订单明细”,B1 里存的是什么?
解析:合并区域只有左上角有值,B1:E1 连记录都没有。规则 3。
Q5插入透视表时提示“数据透视表字段名无效”,最可能的原因是?
解析:字段名来自表头,必须唯一、非空;表头一合并,B1:E1 就是空的。规则 3、4。
Q6一个单元格里是文本 "1280",把格式改成“数值”,结果是?
解析:格式只改显示,不改类型。要转类型,用分列、VALUE() 或选择性粘贴。规则 5。
Q7B2 里是文本日期 2024-06-01,=ISNUMBER(B2) 返回?
解析:ISNUMBER 只看类型,文本日期是字符串,所以 FALSE;真日期才 TRUE。规则 2。
Q8透视表只认一维表,根本原因是?
解析:透视表拖的是字段;二维表里“月份”不是字段,而是 12 个列名。规则 4。
得分:0 / 8

5.5常见问题排查

这一章的重点不是点按钮,但下面这些现象,全都来自本章的五条规则。

现象原因解决
求和比手算少文本型数字被 SUM 跳过(规则 1、5)用 ISNUMBER 找出来后转数值(第 6 章第 4 步)
日期排序乱了,10 月排在 2 月前日期是文本,按字符排序(规则 2)转成真日期再排序(第 6 章第 5 步)
两个日期相减,显示 1900-01-30结果格继承了日期格式(规则 5)把结果格式改成“常规”
透视表提示“字段名无效”表头有合并或空表头(规则 3、4)取消合并,补全表头
透视表出现“(空白)”分组数据区合并,只有左上角有值(规则 3)取消合并,定位空值向下填充
身份证号、订单号后几位变 0超过 15 位有效数字(规则 5)先把列设为文本,再重新输入
单元格显示 ####列太窄,不是数据错误(规则 5)双击列边界自动调宽
改了“数值”格式还是不能求和格式不改变类型(规则 5)用分列、VALUE() 或选择性粘贴
CSV 里的 00128 打开后变成 128CSV 不带格式,Excel 按数值解析(导入路径)用“数据 → 从文本/CSV”导入,把该列设为文本
从 1904 工作簿复制日期,整体差 4 年两套日期系统起点不同(5.1.2)检查“使用 1904 日期系统”

5.6本章小结与练习

  1. 五种形态,四种存储。数值、文本、日期、逻辑、错误是看到的样子;底层只有数字、字符串、布尔值、错误码。
  2. 日期是序列号。1900-01-01 = 1,2024-06-01 = 45444。文本日期不能排序、分组、相减。
  3. 合并只有一个值。值在左上角,其余是真空,透视表会归入“(空白)”。
  4. 透视表只认一维表。维度必须是一列的值,不能藏在列名里。
  5. 格式 ≠ 值。1,280.00 和 1280 是同一个数,"1,280.00" 不是。
  6. 诊断三步:看对齐 → 看绿三角 → 用 ISNUMBER / ISTEXT 确诊。

5.6.1练习

  1. 序列号 45444 是几月几日?45444.75 是几点?
  2. 20240601 为什么不能按月汇总?它和 2024-06-01 差在哪一步?
  3. 把 A2:A5 合并后填入“华东”,COUNTBLANK(A2:A5) 返回几?透视表分成几组、各几行?
  4. 一张 12 个月横排的销售表,要改成透视表能用的结构,至少做哪三步?
参考答案

1. 2024-06-01,18:00。整数部分是日期,小数部分 × 24 是小时。

2. 20240601 是一个八位数值,没有“年/月/日”结构,Excel 不知道它代表哪一天。 必须先转成序列号,才能按年月分组。差别不在长相,在类型。

3. 返回 3。透视表分两组:华东 1 行、空白 3 行,因为只有 A2 有值。

4. ① 取消合并、补全表头;② 用 Power Query 的“逆透视列”把 12 个月变成“月份 / 销售额”两列; ③ 检查月份和销售额的类型。

下一章预告:第 6 章《数据清洗》会把本章的规则落到一份真实的订单表上:取消合并(规则 3)、 删标题行(规则 4)、金额分列(规则 1 和 5)、日期归一(规则 2)。 这些规则在下一章会全部用到;哪一条还没吃透,回去再看一遍,比在实验里反复撤销要快得多。
数据分析师训练营 · 第五章
Excel / WPS 均可,菜单名可能略有差异