excel表格带单位的怎么计算

题图来自Unsplash，基于CC0协议

导读

在日常工作中，许多人在Excel表格里输入数据时，习惯性会直接带上单位，比如“100元”、“50kg”、“3天”。然而，Excel默认将这类“数字+文本”的内容识别为文本字符串，导致你无法直接进行求和、乘积或平均值计算。当你尝试用SUM函数时，结果往往是0，这让不少人感到困惑。

要解决这个问题，核心思路在于将单位从数据中“剥离”或“屏蔽”，让数字部分恢复为可计算的数值。以下是几种实用且高效的解决方法，从手动操作到函数公式，再到傻瓜式工具一应俱全。

方法一：利用“快速填充”功能（Excel 2013及以上版本）

这是最简单、最智能的方法，尤其适用于没有规律的单位（例如有的行是“元”，有的行是“天”）：

1. 在原始数据旁边的第一列空白单元格中，手动输入纯数字（只输入数值，不输入单位）。
2. 按下键盘上的 Enter 键。
3. 接着，选中这个单元格，然后点击 Excel 菜单栏中的 “数据” 选项卡，找到并点击 “快速填充” （或直接按快捷键 Ctrl + E）。 Excel 会自动识别你提取数字的规律，并瞬间将整列数据中的纯数字全部提取出来。 得到纯数字列后，你就可以正常使用求和、乘法等公式了。

方法二：LEFT/FIND函数配合（适用于单位固定且长度统一的场景）

如果你的单位是固定的几个汉字或字符（例如统一是“公斤”或“cm”），且单位位置在数字之后，可以使用函数提取数字部分。假设数据在A2单元格，内容为“50公斤”。

• 提取数字： 使用公式 =LEFT(A2, LEN(A2)*2 - LENB(A2))
  • 这个公式利用了汉字占2个字节而数字占1个字节的原理，自动计算出数字的长度并截取。
• 或者更直接（如果知道单位是2个字）： =LEFT(A2, LEN(A2)-2)
• 得到数字后，用VALUE函数转换： =VALUE(LEFT(A2, LEN(A2)-2)) 这样就能得到一个真正的数值。

方法三：使用SUBSTITUTE替换法（最推荐，灵活且通用）

这个方法不需要额外提取，直接在原有单元格上“以假乱真”，让你能在带单位的单元格上直接计算。

核心概念：通过 SUBSTITUTE 函数把单位替换为空，再通过减负运算（--）或 VALUE 函数将其变为数字。

• 场景1：求和（假设B2:B10单元格内容为“10个”、“20个”） 公式（不要按普通回车，按Ctrl+Shift+Enter或直接回车）：=SUMPRODUCT(--SUBSTITUTE(B2:B10, "个", ""))

  • SUBSTITUTE 将“个”替换成空字符串，得到 “10”、“20”这样的文本数字。
  • --（两个负号）将文本数字转换为真正的数字。
  • SUMPRODUCT 直接进行数组求和。

• 场景2：带单位的乘法（例如单价在A列“5元”，数量在B列“3个”，求总价） 公式：=VALUE(SUBSTITUTE(A2, "元", "")) * VALUE(SUBSTITUTE(B2, "个", "")) 下拉填充即可。

方法四：自定义格式法（从根本上解决问题）

这是最优雅的处理方式，从根本上杜绝了“数字+文本”的问题，只改变显示，不改变原值。

1. 在单元格里只输入纯数字（比如输入 100，而不是“100元”）。
2. 选中该单元格，右键点击 “设置单元格格式”。
3. 在 “数字” 选项卡下，点击 “自定义”。
4. 在右侧的 “类型” 输入框中，输入 0"元" （或者 0.00"公斤"、0"天"）。
5. 点击确定。 你会发现单元格显示为“100元”，但编辑栏里的数值仍然是100。这样，你不仅看到了单位，还可以直接用 SUM 函数求和，用 * 号做乘法，完全不受干扰。

如果你需要进行单位转换（如公斤转斤、厘米转米）

当你通过上述方法提取出纯数字后，单位的转换就变成了简单的数学乘除。

• 示例： 列A是“500克”。
  1. 提取数字：=LEFT(A2, LEN(A2)-1) 得到 500 （文本）。
  2. 转换为数值并除以500（换算为公斤）：=LEFT(A2, LEN(A2)-1)/1000 （Excel会自动将文本数字参与运算）。 或者直接用：=SUBSTITUTE(A2,"克","")/1000 并设置单元格格式为“0.00公斤”。

总结

• 临时处理：用快速填充（Ctrl+E）最省心。
• 批量计算：用SUBSTITUTE配合SUMPRODUCT，无需新建列。
• 一劳永逸：养成好习惯，只输入数字，通过“自定义格式”显示单位。
• 单位转换：先提取数字，再乘以换算系数。

掌握了这些技巧，Excel中带单位的数据对你来说就不再是障碍，而是可以随意处理的普通数字。下一次当你需要计算时，不妨先看看数据是“假单位”还是“真文本”，然后选择对应的方法即可。