excel - 用逗号总结格式化单元格的公式

Question

我想总结一下所有格式化的D2:D6单元格并打印此信息

---------------------------
D2  | January  |  1,000   |
---------------------------
D3  | February |  2,000   |
---------------------------
D4  | March    |  3,000   |
---------------------------
D5  | April    |    400   |
---------------------------
D6  | May      |    500   |
---------------------------
D7  | Total    |  6,900   |
---------------------------

我试图应用这个公式，但它给了我一个错误:

=SUM(VALUE(REPLACE(D2:D6;FIND(",",D2:D6;1),1;"")))

Answer 1

只需将范围相加即可。

如果范围不是数字，而是看起来像数字的文本，那么问题是:为什么会这样？为什么数字会变成文字？

插入一千个逗号？？这可以通过数字格式来完成，而不会破坏数字数据类型。

数据是从报告中下载的？使用 Power Query 加载报表并清理 Power Query 中的数据。然后，您可以使用简单的 Excel 公式。

如果以上都不是可行的选择，最后的手段可以是一个公式，比如

=SUMPRODUCT((SUBSTITUTE(D2:D6,",","")+0))

您的示例公式建议您的区域设置使用分号分隔 Excel 中的参数，因此您需要这样:

 =SUMPRODUCT((SUBSTITUTE(D2:D6;",";"")+0))

编辑:如果范围内的单元格为空白或包含无法转换为数字的文本，则不会中断的另一种变体:

=SUMProduct(IF(ISNUMBER(SUBSTITUTE(D2:D6,",","")+0),SUBSTITUTE(D2:D6,",","")+0,0))

分号版本:

=SUMProduct(IF(ISNUMBER(SUBSTITUTE(D2:D6;",";"")+0);SUBSTITUTE(D2:D6;",";"")+0;0))

使用此构造，您还可以使用 Sum() 而不是 SumProduct()，但请注意，对于非 365 版本的 Excel，Sum() 变体需要使用 Ctrl+Shift+Enter 确认公式。

使用 Power Query 清理数据可能比使用诸如求和之类的简单计算的公式容易得多。