Excel合并总和不同列总数 [英] Excel consolidate sum yields different column total

问题描述

我有两列如下：

Make/Model      Parc
Abarth*500      15
Abarth*500      19
Abarth*500      2
Abarth*500      14
Abarth*500      15
Abarth*500      25
Abarth*500      20
Abarth*500      17
Abarth*500C     12
Abarth*500C     16
Abarth*500C     23
Abarth*500C     18
Abarth*500C     1
Alfa Romeo*145  405
Alfa Romeo*145  431
Alfa Romeo*146  36
Alfa Romeo*146  80
Alfa Romeo*146  121
Alfa Romeo*146  72
Alfa Romeo*146  39

等...

Parc列中的值的总和为10,475,387。

The sum of the values in the "Parc" column is 10,475,387.

我在此表上执行合并以删除Make / Model中的重复并将每个Make / Model的Parc列中的值相加。

I perform a consolidate on this table to remove the repetitions in "Make/Model" and sum the values in the "Parc" column for each "Make/Model."

一旦我执行整合，一切l ocks如预期：

Once I perform the consolidation, everything looks as expected:

Make/Model      Parc
Abarth*500      127
Abarth*500C     70
Alfa Romeo*145  836
Alfa Romeo*146  348
Alfa Romeo*147  3848
Alfa Romeo*155  29
Alfa Romeo*156  3148
Alfa Romeo*159  573
Alfa Romeo*164  111

等...

那么问题是什么？ Parc列的新总和是10,554,076！当我执行随机现货检查时，一切似乎都是正确的。我不明白这笔钱怎么可能上涨？我在此数据集中有超过18,000行，因此单独检查所有数据将非常耗时。

So what's the problem? The new sum of the "Parc" column is 10,554,076! When I perform random "spot" checks everything seems correct. I do not understand how the sum could have gone up? I have over 18,000 rows in this dataset so individually checking all of the data would be very time consuming.

您可以在这里找到完整的数据集：
Excel文件

You can find the full dataset here: Excel File

编辑：
我使用整合工具添加了我的屏幕截图，让您了解我如何获得此结果。

I added the screenshot of me using the consolidate tool to give you an idea of what I did to get this result.

编辑2 ：
我已经尝试删除所有的格式。此外，这些数字都是整数，并且未选中自动插入小数点的选项。我也尝试创建一个新的= INT（）值列，以确保它们都是整数。我的结果在所有情况下完全一样。

Edit 2: I have tried removing all formatting. Also, these numbers are all integers and the option to "Automatically insert a decimal point" is not checked. I have also tried creating a new column of =INT() values to ensure they are all integers. My results are exactly the same in all cases.

推荐答案

asteriks是Excel中的一个特殊字符。我知道的最简单的解决方案是用空格替换星号。要在查找/替换框中执行此操作，请在查找框中键入'〜*'，然后在替换中替换所有（不带引号）。

The asteriks is a special character in Excel. Easiest solution that I know is to replace the asterisk by a space. To do so in the find/replace box, type '~*' in the find box and ' ' in the replace then do replace all (without quotes).

总和和sumif然后产出10,475,387。

Both sum and sumif then yield 10,475,387.

这篇关于Excel合并总和不同列总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！