小计合计总计表格_总计总计重复的Excel小计

小计合计总计表格

Do you use Excel's Subtotal command to automatically create subtotals in a list? I prefer pivot tables, but still use Subtotals in a few macros for clients. Last week, one of them asked why there were duplicate Grand Totals after applying two levels of subtotals.

您是否使用Excel的小计命令自动在列表中创建小计 ？ 我更喜欢数据透视表，但是仍然在一些宏中为客户使用小计。 上周，其中一个人问为什么在应用两个小计后得出总计的总和。

为什么有两个总计？ (Why Are There Two Grand Totals?)

That was a good question, and when I created subtotals on different data, there was only one Grand Total, as expected. Google was no help in solving the mystery, so I took a closer look at the two data sets, to see if I could spot a difference.

这是一个很好的问题，当我根据不同的数据创建小计时，只有一个总计(如预期)。 Google并没有帮助解决这个谜，所以我仔细研究了这两个数据集，看是否可以发现差异。

I had also been asked to check on an error in one of the columns, so I decided to tackle that problem first – it seemed easier!

我还被要求检查其中一列中的错误，因此我决定首先解决该问题-似乎更容易！

没有错误=总计没有重复 (No Error = No Duplicate Grand Totals)

The error was in one of the columns that was being subtotaled, and carried down to the grand totals. Fortunately, the formula problem was easy to solve – it was caused when a formula tried to subtract a cell that contained text.

该错误是在总计中的一列中，并向下进行总计。 幸运的是，公式问题很容易解决-它是由公式试图减去包含文本的单元格引起的。

Then a miracle happened – when I ran the subtotal command again, there were no duplicate grand totals! So, it seemed that the error was causing the duplicates.

然后发生了奇迹–当我再次运行小计命令时，没有重复的总计！ 因此，似乎该错误导致了重复。

To test my theory, I created an error in my test data. Next, I applied subtotals, and subtotaled the column with the error. Woohoo! The table had 2 grand totals, instead of one.

为了检验我的理论，我在检验数据中创建了一个错误。 接下来，我应用了小计，并用错误小计了该列。 hoo！ 该表有2个总计，而不是一个。

防止重复总计 (Prevent Duplicate Grand Totals)

After that exhausting round of testing (3 or 4 tests), I'm convinced that those mysterious duplicate grand totals are caused by errors in any column that is being subtotaled.

经过一轮筋疲力尽的测试(3或4个测试)之后，我确信这些神秘的重复总计是由任何小计的列中的错误引起的。

• To prevent duplicate grand totals, fix those errors, if possible, or use IFERROR or IF and ISERROR to override error results.
  为防止总计出现重复，请尽可能修正这些错误，或者使用IFERROR或IF和ISERROR覆盖错误结果。
• If you can't change the formulas, just hide the rows with duplicate grand totals, or ignore them. (Or tell your client, "It's not a bug, it's a feature.")
  如果您不能更改公式，则只需隐藏具有重复总计的行，或忽略它们。 (或者告诉您的客户，“这不是错误，而是功能。”)

视频：总计总计重复的小计 (Video: Subtotals With Duplicate Grand Totals)

To see how to apply two subtotals to a list, and see the effect that errors have, watch this short video tutorial. The timeline is listed below the video.

要了解如何将两个小计应用于列表，并查看错误的影响，请观看此简短视频教程。 时间线列在视频下方。

演示地址

0:00 Introduction 0:31 Sorted List 0:51 First Subtotal 1:44 Second Subtotal 2:18 Remove Subtotals 2:27 Duplicate Grand Totals 2:53 Apply Subtotals 3:24 Prevent Duplicates 4:29 Get the Sample File

0:00简介0:31排序列表0:51第一小计1:44第二小计2:18移除小计2:27重复的总计2:53应用小计3:24防止重复4:29获取示例文件

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

Visit the Excel Subtotals page on my Contextures website to download the sample file. The zipped file is in xlsx format, and does not contain macros.

访问我的Contextures网站上的Excel小计页面以下载示例文件 。 压缩文件为xlsx格式，不包含宏。

翻译自: https://contexturesblog.com/archives/2015/10/29/excel-subtotals-with-duplicate-grand-totals/

小计合计总计表格