这一章不教你点按钮,只讲 Excel 的存储规则。要回答的问题只有一个:
为什么同样的数字,Excel 的处理方式不一样?
同样敲进去 1280,有的格子能求和,有的被 SUM 当成空气;同样写着 2024-06-01,有的能按月分组,
有的一排序就乱;一个横跨五列的标题,拆开之后只有一个格子有值。
这些不是 bug,是 Excel 的存储规则。本章拆开四件事:格子能装什么、日期到底是什么、
合并单元格存了什么、透视表为什么只认一种表。学完你再看表,看到的会是类型、序列号和结构。
把镜头推到最近:一个单元格。你敲一个字符,Excel 要决定三件事——存成什么类型、配什么格式、 允许它参加哪些运算。这三个决定,决定了你后面能不能求和、排序、做透视表。
按输入形态分,单元格有五种:数值、文本、日期、逻辑、错误。但把 .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。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | QN2406001 | 00128 | 1,280.00 | 2024-06-01 |
| 2 | TRUE | #N/A | 3.14 | 20240601 |
判断类型不能看它“写了什么”,要看它“怎么存”。图里 C1 和 D2 都像日期,但 C1 是真日期, D2 只是数字 20240601。对齐、绿三角、公式,是三种由浅入深的判断办法,5.2 节逐个讲。
单元格是“单值”容器。换行、加粗、变色都只是格式;值本身还是一个。按存储分四类—— 数值、文本、逻辑、错误;日期是数值的显示形态。类型在值写进格子的那一刻就定了。
如果不这样会怎样:第 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。
Excel 存日期只存一个数:1900-01-01 是第 1 天,往后每天加 1。所以 2024-06-01 = 45444; 时间是它的小数部分——早上 6 点 .25,中午 .50,晚上 .75。
为什么这么存?日期要能加减、排序、按月分组,字符串做不到。两个日期相减得到天数, 一个日期加 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。
真日期的存储是序列号,显示是格式。它能排序、相减、按月分组,因为底层就是数字。 文本日期只是一串字符,Excel 不会把它当时间看。
如果不这样会怎样:同一列 5 种日期写法,如果都是文本,透视表会把它们当成 5 个标签, 6 月的销售额被拆成 5 份;排序时 10 月会跑到 2 月前面。
选中 A1:E1,合并后居中,输入标题。屏幕上是横跨五列的大格子,但把 .xlsx 改成
.zip 解压,打开 sheet1.xml,只有一行 <mergeCell ref="A1:E1"/>
和 A1 里的字符串;B1 到 E1 连记录都没有。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 6 月订单明细 | ||||
| 实际存储 | "6 月订单明细" | 无记录 | 无记录 | 无记录 | 无记录 |
| 2 | 订单号 | 下单时间 | 客户 | 金额 | 备注 |
后果是一串的:COUNTBLANK(A1:E1) 数出 4 个空;筛选时只有 A1 有值;
排序会警告合并大小不一致;透视表把其余行归入“(空白)”。
假设 A2:A5 合并成“华东”,四行各 100 元。透视表得到 华东 100、空白 300, 不是华东 400——Excel 只看到 A2 有值。取消合并只保留左上角,A3:A5 变空;不补值, 结构修好了数据还是错的。只想要标题跨列居中,用“跨列居中”,它不合并。
合并区域里只有左上角有值,其余是真空。屏幕上的“一个大格子”,只是边框和底色画在了一起。
如果不这样会怎样:A2:A5 合并成“华东”,四行各 100 元,透视表汇总出来是 “华东 100、空白 300”,不是“华东 400”。取消合并后不补值,A3:A5 仍然是空的。
一维表三条:只有一个表头行;一行一条记录;一列一个字段。二维表行和列都是维度,值在交叉点。 人看着舒服,机器没法用。
| 地区 | 1 月 | 2 月 | 3 月 |
|---|---|---|---|
| 华东 | 120 | 135 | 150 |
| 华南 | 90 | 88 | 102 |
月份是列名,不是字段。
| 地区 | 月份 | 销售额 |
|---|---|---|
| 华东 | 1 月 | 120 |
| 华东 | 2 月 | 135 |
| 华南 | 1 月 | 90 |
每个维度都是一列值。
透视表只认一维表,因为字段列表是从表头读出来的,行、列、值、筛选拖的都是字段。二维表里“地区”是字段,
“1 月”“2 月”也各是字段,但“月份”不存在——它被拆在 12 个列名里。拖不到行区,筛不了月份,
算季度只能手写 =B2+C2+D2。一维表里月份是一列值,拖到行区自动展开 12 个月;
新增 13 月只是多一行。维度必须作为一列的值存在,不能藏在列名里。
| 对比项 | 一维表 | 二维表 |
|---|---|---|
| 结构 | 一行一条记录,一列一个字段 | 行和列都是维度,值在交叉点 |
| 透视表 | 任意拖拽、任意组合 | 只有 12 个“求和项”,拖不出月份 |
| 新增月份 | 加一行数据即可 | 加一列,还要改公式和区域 |
| 月度趋势 | 一条折线直接画 | 要先把 12 列转成 12 行 |
| 筛选 | 按任意维度筛选 | 只能按列名一列一列看 |
三种伪装:月份横排、指标横排(销售额、成本、利润各一列)、多级表头。共同点是 本该在列里的值,跑到了表头里。
一维表不是排版风格,是数据结构。它保证每个维度都能被当成字段来分组、筛选、聚合。 透视表、切片器、图表、Power Query 都建立在这个前提上。
如果不这样会怎样:一张 12 个月横排的表,做透视表只能得到 12 个“求和项”, 拖不出“月份”这个维度,也算不出一条月度趋势;新增一个月,还得改公式、改区域。
输入 1280,格式设成 #,##0.00,显示 1,280.00;选中它,编辑栏还是
1280。格式只改显示。如果这串字符是复制来的,或用公式 ="1,280.00" 生成,
它就是文本。
| 格式代码 | 你看到的 | 实际存储 |
|---|---|---|
常规 | 1280 | 1280 |
#,##0.00 | 1,280.00 | 1280 |
¥#,##0.00 | ¥1,280.00 | 1280 |
0% | 128000% | 1280 |
0.00E+00 | 1.28E+03 | 1280 |
#,##0.00" 元" | 1,280.00 元 | 1280 |
格式代码存在 styles.xml 里,单元格只存值和一个样式编号。所以:
#### 不是错误,是列太窄。拉宽就回来。数字格式是一张“显示说明书”。它决定 1280 写成 1,280.00 还是 1.28E+03,但不改变格子里存的是 1280。
如果不这样会怎样:把文本金额的格式改成“数值”,它仍然靠左、仍然不能求和。 你以为改好了,一按 SUM 还是少 9 万多。
类型判断不能靠猜。三招从粗到细:前两招快速扫一遍,第三招给确定答案。
默认对齐:文本靠左,数值和日期靠右,逻辑和错误居中。绝大多数表成立,但别人手动改过对齐就失效。 所以只能当初筛,不能当确诊。
绿三角是错误检查标记,最常见含义是“看起来像数字的文本”。三个局限:可以关掉;也会标其他错误;
普通文本(QN2406001)不标。所以:有绿三角基本是文本;没有,不能反推数值。
公式最可靠。=ISNUMBER(A1) 为 TRUE 是数值(真日期也 TRUE);=ISTEXT(A1)
为 TRUE 是文本;=ISBLANK 判断真空(="" 不算);=ISERROR
判断错误。=TYPE(A1):1 数值、2 文本、4 逻辑、16 错误、
64 数组。整列用 =COUNT(A:A) 和 =COUNTA(A:A) 相减,就是非数值的
非空格子数——第 6 章 40 个文本金额就是这么数出来的。
| 单元格 | 显示 | 公式 | 返回 |
|---|---|---|---|
| A1 | 1,280.00 | =ISNUMBER(A1) | TRUE |
| B1 | 1,280.00 | =ISNUMBER(B1) | FALSE |
| C1 | 2024-06-01 | =TYPE(C1) | 1 |
| D1 | TRUE | =ISTEXT(D1) | FALSE |
| E1 | #N/A | =ISERROR(E1) | TRUE |
| 方法 | 看什么 | 可靠度 | 局限 |
|---|---|---|---|
| 看对齐 | 左 / 右 / 居中 | 中 | 手动改过对齐就失效;空格子看不出来 |
| 看绿三角 | 左上角有没有绿色标记 | 中高 | 可以关闭;普通文本不标;不区分日期和数字 |
| 用公式 | ISNUMBER / ISTEXT | 高 | 要会写公式;整列检查要加辅助列 |
ISNUMBER 公式批量复核。前两招省时间,第三招保命。
下面 4 行 5 列,20 个格子,全部是“看到的显示”,没有编辑栏。判断每个格子的真实类型, 不用打开 Excel,拿一张纸就能做。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | QN2406001 | 1,280.00 | 2024-06-01 | TRUE | #N/A |
| 2 | 00128 | 1,280.00 | 2024/6/1 | FALSE | 3.14 |
| 3 | 20240601 | 2024-06-01 06:00 | ¥1,280.00 | 128000% | 二零二四年六月一日 |
| 4 | 1.28E+03 | 1,280.00 元 | 45444 | 12:00:00 | N/A |
ISNUMBER、ISTEXT 或 TYPE。=ISNUMBER(A1)、=ISTEXT(A1),把 A1 换成那个格子的地址;
再用 =TYPE(A1),返回 1 就是数值。文本(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 个以上算过关。
不看笔记,直接选。每选完一题,解析立刻出现;八题做完,得分自动累计。
1,280.00,编辑栏显示 1280。它的存储类型是?1,280.00 只是 #,##0.00 的显示。规则 5。2024-06-01 存的是什么?2024/10/1、2024/2/1、2024/6/1 升序排序,结果是?2024/,第六位是 1、2、6,所以 10 月最前。规则 2。"1280",把格式改成“数值”,结果是?VALUE() 或选择性粘贴。规则 5。2024-06-01,=ISNUMBER(B2) 返回?ISNUMBER 只看类型,文本日期是字符串,所以 FALSE;真日期才 TRUE。规则 2。这一章的重点不是点按钮,但下面这些现象,全都来自本章的五条规则。
| 现象 | 原因 | 解决 |
|---|---|---|
| 求和比手算少 | 文本型数字被 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 打开后变成 128 | CSV 不带格式,Excel 按数值解析(导入路径) | 用“数据 → 从文本/CSV”导入,把该列设为文本 |
| 从 1904 工作簿复制日期,整体差 4 年 | 两套日期系统起点不同(5.1.2) | 检查“使用 1904 日期系统” |
1,280.00 和 1280 是同一个数,"1,280.00" 不是。ISNUMBER / ISTEXT 确诊。20240601 为什么不能按月汇总?它和 2024-06-01 差在哪一步?COUNTBLANK(A2:A5) 返回几?透视表分成几组、各几行?1. 2024-06-01,18:00。整数部分是日期,小数部分 × 24 是小时。
2. 20240601 是一个八位数值,没有“年/月/日”结构,Excel 不知道它代表哪一天。
必须先转成序列号,才能按年月分组。差别不在长相,在类型。
3. 返回 3。透视表分两组:华东 1 行、空白 3 行,因为只有 A2 有值。
4. ① 取消合并、补全表头;② 用 Power Query 的“逆透视列”把 12 个月变成“月份 / 销售额”两列; ③ 检查月份和销售额的类型。