excel数字不能累加_如何修复不累加的Excel编号

excel数字不能累加

If you download bank statement data into Excel, or copy numbers from a website, those numbers might not add up correctly. The bank data might look like numbers, but Excel might see those numbers as text -- not real numbers.

如果将银行对帐单数据下载到Excel中，或从网站复制号码，则这些号码可能无法正确累加。 银行数据可能看起来像数字，但是Excel可能会将这些数字视为文本-而不是实数。

You can fix the numbers manually, as shown in the video below, or use a macro to automate the fix.

您可以手动固定数字，如下面的视频所示，或使用宏自动进行修正。

数字不累加 (Numbers Don't Add Up)

There are a variety of reasons that numbers don't add up in Excel, and this technique fixes one of the more common problems. If it doesn't work on your data, check out some of the other fixes here.

不能在Excel中累加数字的原因有多种，这种技术解决了较常见的问题之一。 如果对您的数据不起作用，请在此处查看其他一些修复程序 。

Here is a screen shot of the sample data used in this video – it has cheque numbers and amounts from a fake bank statement. (If your bank only sends you this much information, you should switch to a different bank!)

这是此视频中使用的示例数据的屏幕快照–它具有支票号码和假银行对账单中的金额。 (如果您的银行仅向您发送了这么多信息，则应切换到其他银行！)

观看视频 (Watch the Video)

In the short video below, you will see how to check the cells, and then fix the problem. This is an update for a video that I did in May 2008 – and people are still watching that one on YouTube!

在下面的简短视频中，您将看到如何检查单元格，然后解决问题。 这是我在2008年5月拍摄的一部视频的更新-人们仍然在YouTube上观看该视频！

演示地址

用宏修正数字 (Fix the Numbers with a Macro)

Also, I've updated the macro that automates this fix, to prevent a problem. The old code used the full range that was selected, and that was okay, if you just selected the cells that you wanted to update.

另外，我还更新了自动解决此问题的宏，以防止出现问题。 如果您只是选择了要更新的单元格，那么旧的代码将使用所选的整个范围，这没关系。

However, if you selected an entire column, and then ran the macro, the Used Range was extended to that entire column.

但是，如果选择了整个列，然后运行宏，则“已用范围”将扩展到整个列。

Selection.PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlPasteSpecialOperationAdd

In the revised code, it creates a range from the cells with constants in the selected range, and updates those cells.

在修改后的代码中，它将从单元格中创建一个范围，且常数处于选定范围内，并更新这些单元格。

Set rng = Selection _
  .SpecialCells(xlCellTypeConstants, 23)

That seems to have eliminated the problem. You can get the entire code from my website, or by downloading the sample file.

这似乎消除了问题。 您可以从我的网站获得完整的代码，也可以下载示例文件。

下载样本文件 (Download the Sample File)

To test the manual or macro fix, you can download the sample file from my website. The zipped file is in xlsm format, so enable macros when you open the file.

要测试手动或宏修复程序，您可以从我的网站下载示例文件 。 压缩文件为xlsm格式，因此在打开文件时启用宏。

翻译自: https://contexturesblog.com/archives/2016/01/28/how-to-fix-excel-numbers-that-dont-add-up/

excel数字不能累加