# 为什么 SUMIFS 加不到、XLOOKUP 找不到：先判断单元格里是数值还是文本

文本型数字和假日期为什么让求和漏行、查找落空、透视表分不了组，以及怎么体检并整列改回来

> 数据整理 · Excel 函数 · 表格计算 · 约 7 分钟 · 10 月 28 日

## 本篇要点

1. 单元格里装着“值加类型”和“格式”两套东西，格式只改显示，不改类型；给文本套上日期格式，它还是文本。
2. 日期不是独立类型，而是从 1900 年 1 月 1 日算起的天数（当天为 1），所以真日期能排序、能相减、能分组。
3. 数值 1003 和文本“1003”是不相等的两个值：XLOOKUP 的精确匹配不做转换，所以返回未找到。
4. SUMIFS 在求和区域上像 SUM 一样跳过文本，漏掉的金额不报错；条件区域的类型比较时松时紧，所以结果常常只错一半，最难发现。
5. 透视表按字段类型选默认聚合，看到“计数项:金额”就说明这列被读成了文本；文本日期不能分组，排序也按文本规则走。
6. 可靠的体检是 `=ISNUMBER()`，左对齐和绿三角都只是线索；修复用分列整列重新解析，日期要在第三步指定年月日顺序。
7. 工号、邮编这类编号不能转成数值（会丢前导零），应把匹配的另一边也变成文本。
8. 事后改格式改不回类型，而且要先确认整列格式为“常规”，否则转换完新输入的数据又会变回文本。

---

上一篇我们定下了“哪种图配哪种数”的判断顺序。这一篇往回退一层，处理更靠上游的问题：在算之前，先确认单元格里存的是哪一类东西。

因为最常见的三种“Excel 算错了”，其实是同一件事：

- SUMIFS 的合计比手工加的小一截，公式没报错。
- XLOOKUP 明明看得见那一行，却返回 #N/A 或你设的“未找到”。
- 透视表里金额那栏写着“计数项:金额”，日期在行标签里排成一长串具体日子，分不了组。

三种症状，一个原因：那一列里混着文本类型的数字，或者长得像日期的文本。它们和真数值、真日期看着一模一样，Excel 却当成不同的东西。

这篇教你做一次“类型体检”并改回来。做完你应该能：一眼看出某列是数值还是文本，用一条公式确认而不是靠眼看，并把整列批量改对——改完 SUMIFS 的合计和手工加的总数对得上。

## 准备的东西

Excel 2016 以上即可，用 XLOOKUP 需要 Microsoft 365 或 Excel 2021。分列在“数据”选项卡里。

先建一张 5 行的小表练手，把金额故意做成一真一假：

| 订单号 | 客户 | 日期 | 金额 |
|---|---|---|---|
| A1001 | 甲 | 2024/3/5 | 3200 |
| A1002 | 乙 | 2024/3/18 | 1800 |
| A1003 | 丙 | 2024/4/2 | 2400 |
| A1004 | 甲 | 2024/4/16 | 1500 |
| A1005 | 乙 | 2024/5/9 | 900 |

其中 1800 和 1500 两格，先选中、把格式设成“文本”，再重新输一遍数字。这样你就复现了从系统导出、从别人那里粘贴来的表里最常见的那种坏数据。

现在在空白格输入 `=SUM(D2:D6)`。答案是 6500，而手工加起来应该是 9800，差的正是 1800 + 1500 = 3300。

## 为什么“长得一样”却是两类东西

单元格里装着两样互不相干的东西：**值（连同它的类型）**和**格式**。

值是公式真正读取的对象，类型决定它属于哪一类（数值、文本、逻辑值）；格式只决定它怎么显示。给单元格套上货币格式，值还是 3200，`=D2` 读到的永远是值，格式它不看。

日期是这里最容易误解的一处：Excel 里的日期不是独立类型，它就是一个整数——1900 年 1 月 1 日记作 1，2008 年 1 月 1 日记作 39448 [3]。你看到“2024/3/5”，是因为这个数被套上了日期格式。所以日期能比大小、能相减，因为它本来就是数字。

“2024/3/5”被存成文本时，这串字符里没有任何数字可读。函数拿到的是一段话，不是一天。

由此推出一条常被忽略的规则：**事后改格式，改不回类型**。给存着文本的单元格套上日期格式，它只是显示得更像日期，里面还是文本。

## 三处症状各自的机制

**SUMIFS 加不到。** SUMIFS 在求和区域上就像 SUM：遇到文本直接跳过，既不加也不提醒 [6]。所以 1800 和 1500 被无声漏掉，公式看起来一切正常。麻烦在条件区域那边：类型不一致时它有时会替你凑合，有时不会——这就是“算了一半”比“算成 0”难查的原因，结果离正确答案不远，你不会怀疑公式。上篇用两个日期条件围出“某个月”的写法，一旦日期列是文本，围出来同样漏行（[SUMIFS 与 COUNTIFS](https://pangzhengboyin.com/articles/sumifs-countifs-conditional-totals-by-month-9d1c7b29)）。

**XLOOKUP 找不到。** 默认的精确匹配不做类型转换：数值 1003 和文本 `"1003"` 是不相等的两个值，所以查不到，直接落到你写的第 4 参数或 #N/A 上 [5]。这一步它做得很死板，但方向是对的——宁可说“没有”，也不猜你要哪一个。查工号那篇里“找到但为空”的处理，前提是先真的找到（[XLOOKUP](https://pangzhengboyin.com/articles/xlookup-cross-sheet-lookup-basics-5be14f3e)）。

**透视表分不成组。** 值区按字段类型决定默认聚合方式：数值默认求和，文本默认计数 [4]。所以看到“计数项:金额”，就是 Excel 在告诉你这列被读成了文字。日期字段是文本时分组菜单直接不可用，因为文本没有“月”这个概念；排序也按文本规则走，“10月”会排到“2月”前面。

```mermaid
flowchart LR
  A["可疑的一列"] --> B{"ISNUMBER 返回 TRUE？"}
  B -->|"是"| C["已经是数值或真日期"]
  B -->|"否"| D{"是编号、要保留前导零？"}
  D -->|"是"| E["把另一边也变成文本"]
  D -->|"否"| F["分列或 VALUE 转成数值"]
  F --> G["ISNUMBER 复查 + 合计对账"]
```

## 第一步：体检，不要靠眼睛

左对齐/右对齐是个线索（默认情况下文本靠左、数值靠右），但格式能改掉它，只当参考。

可靠的判断用公式，在空白格输入：

```excel
=ISNUMBER(D3)
```

真数值和真日期都返回 TRUE，文本返回 FALSE。往下一拉，整列的真实身份就列出来了。要反着看可以用 `=ISTEXT(D3)`。

## 第二步：把文本型数字改成数值

先确认整列格式是“常规”。如果还设着“文本”，转换完再往里输新数字，它又变回文本，白忙一场。

然后挑一个办法：

**警告图标。** 有绿三角的单元格，选中后点左上角的感叹号，选“转换为数字” [1]。快，但只在 Excel 恰好标了三角时才有——这个标记是错误检查给的提示，可能因设置或文件来源而根本不出现。

**分列。** 选中整列，数据 → 分列，前两步都不动，直接点“完成”。Excel 会用“常规”格式把每格重新解析一遍，纯数字的文本就变成了数字；整列一次搞定，不用辅助列。

**辅助列。** 旁边插一列写 `=VALUE(D3)` 或更短的 `=--D3`，拉到底，复制 → 选择性粘贴 → 值，覆盖原列后删掉辅助列。数据源每月重导、需要留成公式时走这条路。

做完回到那句 `=SUM(D2:D6)`：现在应该是 9800。

## 第三步：把假日期改成真日期

日期多一道坎：文本里的年月日顺序必须说清楚。

仍然用分列，只是最后一步不能直接点“完成”，要在第三步选“日期”，并在右边下拉里选这列用的是哪种顺序（YMD 还是 DMY 等）[2]。选对了，“2024/3/5”才会规规矩矩变成 2024 年 3 月 5 日。

怎么知道该选哪种？在整列里找一个第一个数大于 12 的值。比如“2024/18/3”，说明第一个数是年、第二个是月，列是 YMD 顺序；如果是“13/5/2024”，说明开头是日。

`=DATEVALUE(D3)` 也能转，但它按你电脑的日期顺序去理解文本，顺序对不上就返回 #VALUE! [3]。分列的好处是不用赌系统设置。转完再体检一次 `=ISNUMBER(D3)`，全 TRUE 才算过。

## 有一种，千万别转

工号 00450、邮编 010020、以 0 开头的商品编码，这类东西是编号，不是数量。转成数值，前面的 0 就没了，`00450` 变成 `450`——看着只是少个零，实际上两张表再也对不上。

正确做法是反方向：把要匹配的那一边也变成文本。用 `=TEXT(B2,"00000")` 把数值补回前导零，或者在查找值后面连一个空字符串 `B2&""`，让它变成文本再去查。两边类型一致就行，往哪边统一由数据决定：数量要能加，编号要保留原样。

## 改完，用三件事验收

1. 整列 `=ISNUMBER()` 全部 TRUE。
2. `=SUM(整列)` 和你信任的口径（比如系统导出的合计）一致，再看一眼 SUMIFS 有没有跟着变。
3. 透视表刷新，值区从“计数项”变成“求和项”，日期字段能右键分组了。

最后一条边界：如果每月都要重导源文件，手工转换是重复劳动——下个月同一批坏数据还会回来。更干净的做法是在导入那一步就把类型定下来，这一步留给后面讲 Power Query 的文章。

## 思维导图

```mermaid
flowchart LR
  R["一个原因：类型不对"] --> S1["SUMIFS 漏加：求和区域跳过文本"]
  R --> S2["XLOOKUP 找不到：精确匹配不转换"]
  R --> S3["透视表：文本默认计数、日期不能分组"]
  R --> K["值+类型 vs 格式：改格式改不回类型"]
  K --> D["日期 = 序列号，1900/1/1 = 1"]
  R --> T["体检：=ISNUMBER() 逐行判断"]
  T --> F1["文本型数字 → 分列 / VALUE / 警告图标"]
  T --> F2["假日期 → 分列第三步指定 YMD 顺序"]
  T --> F3["前导零编号 → 反向统一成文本"]
  F1 --> V["验收：全 TRUE + 合计对账 + 透视表变求和项"]
```

## 术语表

- 值的类型：单元格里真正被公式读取的东西属于数值、文本还是逻辑值，它和显示格式是两回事。
- 日期序列号：Excel 把每一天记成一个整数（1900 年 1 月 1 日 = 1），所谓“日期”只是给这个数套了格式。
- 文本型数字：内容是数字字符、类型却是文本的值，肉眼和真数值看不出区别，但参与不了求和与精确匹配。
- 假日期：长得像日期的一串文本，没有序列号，因此不能排序、不能分组、不能按月份围区间。
- 分列重解析：把整列交给“数据 → 分列 → 完成”重新读一遍，等于声明这列该是什么类型。
- 前导零编号：像 00450 这种以 0 开头、只用来对号的身份代码，必须保持文本，转成数值就丢了开头的零。

## 来源

1. [Microsoft Support: Convert numbers stored as text to numbers — 绿色三角与“转换为数字”、VALUE 辅助列做法](https://support.microsoft.com/en-us/excel/convert-numbers-stored-as-text-to-numbers-in-excel)
2. [Microsoft Support: Convert dates stored as text to dates — 文本日期的识别线索与分列指定日期顺序](https://support.microsoft.com/en-us/excel/convert-dates-stored-as-text-to-dates)
3. [Microsoft Support: DATEVALUE 函数 — 日期序列号定义（1900/1/1 为 1，2008/1/1 为 39448）及受系统日期设置影响](https://support.microsoft.com/en-us/excel/functions/datevalue-function)
4. [Microsoft Support: Calculate values in a PivotTable — 数值字段默认求和、文本字段默认计数](https://support.microsoft.com/en-us/excel/calculate-values-in-a-pivottable)
5. [Excel University: Avoid XLOOKUP Errors — 数值与文本类型不一致导致查不到，及 VALUE 包裹查找区域](https://www.excel-university.com/avoid-xlookup-errors/)
6. [ICAEW: Comparing SUMIF, SUMIFS and SUMPRODUCT — SUMIFS 会忽略求和区域中的文本](https://www.icaew.com/technical/technology/excel-community/excel-community-articles/2025/comparing-sumif-sumifs-and-sumproduct)

---

原文：https://pangzhengboyin.com/articles/excel-text-numbers-vs-real-dates-e2a993dc

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具：每篇 5–10 分钟，边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com