☰

如何对包含文本的单元格的列求和？

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
如何对包含文本的单元格的列求和？

我有一张包含近 6000 种产品的 Excel 表。在“数量”列中，有些值以“60pcs”结尾，有些值以“7pkts”结尾。

我需要在最后计算总数量，但由于字母和数字混在一起，我无法做到这一点。如果值包含文本，我该如何计算总和？

答案1

这比@Andi Mohr 的解决方案更不通用；它假设您只有那些单位度量，都以“p”开头。唯一的优点是您用一个公式就能得到最终结果。

=SUM(NUMBERVALUE(MID(A1:A6000,SEARCH("p",A1:A6000)-1)))

根据需要调整列引用。

必须按Ctrl+ Shift+来确认Enter。

答案2

这个相当出色的公式会为您完成这项工作。

如果单元格 A1 中混合了数字和文本，请按Ctrl+ Shift+在下一个单元格中输入以下内容Enter：

=NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))

然后，您可以将此列相加来计算总数。

这个公式最初是由一个叫 Lori 的人发布到 Google Groups 的 - 我在Chandoo 论坛主题来自 Sajan 的一篇文章。工作原理：

NPV 的神奇之处在于 NPV 计算公式，其中每一项都乘以 (1+rate)^n 的倒数，其中 n 是序列中的第 n 项。例如 (1+rate)^1、(1+rate)^2 等。通过使用不同的 rate 值，我们可以得到不同的结果。在本例中，使用 -0.9 可得到 1+rate=1+-0.9=0.1。因此我们得到的值是 {0.1;0.01;0.001;0.0001;0.00001}。取其倒数可得到 {10;100;1000;10000;100000} 等。结合 NPV 跳过文本值的事实，我们得到了所需的结果。

编辑：提高计算速度

Máté Juhász 建议添加一个功能，以加快公式在应用于大范围时的速度。它会先检查字符串是否已经是数字，以节省 Excel 计算不需要的内容的时间。

=IF(ISNUMBER(A1),A1,NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,"")))

相关内容