excel每页小计累计_您还需要Excel小计吗？

excel每页小计累计

Excel has a SUBTOTAL function, which ignores hidden or filtered rows. There is a Subtotal feature too, that quickly groups your data, and adds one or more rows of subtotals. Do we still need these Excel subtotals though, now that we have pivot tables and the AGGREGATE function?

Excel具有SUBTOTAL函数，该函数将忽略隐藏或过滤的行。 还有一个小计功能，可以快速将数据分组，并添加一排或多排小计。 现在，我们有了数据透视表和AGGREGATE函数，是否仍然需要这些Excel小计？

SUBTOTAL函数 (The SUBTOTAL Function)

When you have a list of data, the SUBTOTAL function can calculate a total that ignores filtered rows. In Excel 2007 and later, it can also ignore rows that were manually hidden.

当您具有数据列表时， SUBTOTAL函数可以计算忽略过滤行的总数。 在Excel 2007和更高版本中，它也可以忽略手动隐藏的行。

That's helpful when you're filtering a list on one or more columns, and want to see the totals for the visible rows only. For example, in the screen shot below,

当您在一个或多个列上过滤列表，并且只想查看可见行的总数时，这很有用。 例如，在下面的屏幕截图中，

• SUBTOTAL shows the sum for the West region only, for a total of 300
  SUBTOTAL仅显示西部地区的总和，总计300
• SUM includes the hidden rows that have the East amounts, for a total of 700
  SUM包含具有东方金额的隐藏行，总计700

聚集函数 (The AGGREGATE Function)

If you have Excel 2010 or later, the AGGREGATE function is a more powerful version of SUBTOTAL.

如果您具有Excel 2010或更高版本， 则AGGREGATE函数是SUBTOTAL的更强大版本。

• It has 19 functions, compared to the 11 functions in SUBTOTAL
  与SUBTOTAL中的11个功能相比，它具有19个功能
• There are 8 options for what to ignore, compared to the 2 options in SUBTOTAL
  与SUBTOTAL中的2个选项相比，有8个选项可以忽略

This video shows the difference between the SUBTOTAL and AGGREGATE functions.

该视频显示了SUBTOTAL和AGGREGATE函数之间的区别。

演示地址

为什么继续使用小计？ (Why Keep Using SUBTOTAL?)

Since AGGREGATE is more powerful, why would you keep using SUBTOTAL? I can think of a few reasons – do you know of any others?

由于AGGREGATE功能更强大，为什么还要继续使用SUBTOTAL？ 我能想到几个原因-您是否认识其他原因？

• Compatibility – AGGREGATE won't work if you need to share your files with anyone using Excel 2007 or earlier

  兼容性 –如果您需要与使用Excel 2007或更早版本的任何人共享文件，则AGGREGATE无法使用

• Habit – If you're used to the SUBTOTAL function, it's easier to just keep using it, instead of learning a new function

  习惯 –如果您习惯于使用SUBTOTAL函数，那么继续使用它会比学习新功能更容易

• Ease of Use – It's easy to insert the SUBTOTAL function below a filtered list – just select a cell, and click the AutoSum button. Excel automatically inserts SUBTOTAL for you, with the Sum function (9) selected.

  易于使用 –将SUBTOTAL函数轻松插入到过滤列表下方–只需选择一个单元格，然后单击AutoSum按钮即可。 Excel会自动为您插入SUBTOTAL，并选择“求和”功能(9)。

Excel小计功能 (Excel Subtotal Feature)

Excel has a Subtotal feature too, that lets you quickly group data, and show one or more levels with subtotals. In the next screen shot, the list is sorted by Region, so I can show a Sum at the end of each Region's data.

Excel 也具有小计功能 ，可让您快速将数据分组，并显示小计的一个或多个级别。 在下一个屏幕快照中，该列表按Region排序，因此我可以在每个Region数据的末尾显示一个Sum。

Excel creates an outline for the list, and automatically inserts a SUBTOTAL function in each subtotal row. You can click the outline buttons at the top left, to see just the grand total, or all the totals, or the details and totals.

Excel为列表创建轮廓，并在每个小计行中自动插入一个SUBTOTAL函数。 您可以单击左上角的大纲按钮，以仅查看总计或所有总计，或查看详细信息和总计。

为什么继续使用小计功能？ (Why Keep Using the Subtotal Feature?)

When pivot tables were introduced, long ago, I thought that people would use those, instead of the Subtotal feature. But some people love Subtotals, and keep using them.

早在引入数据透视表时，我认为人们会使用这些数据表，而不是小计功能。 但是有些人喜欢小计，并继续使用它们。

Here are my guesses as to why – do you have any other reasons?

这是我对原因的猜测-您还有其他原因吗？

• Details – It's easy to show or hide the details, if you're analyzing the totals

  详细信息 –如果要分析总计，很容易显示或隐藏详细信息

• Changes – While troubleshooting, you're working with the live data, and can quickly change a record, to correct a total

  更改 –在进行故障排除时，您正在使用实时数据，并且可以快速更改记录以更正总计

• Habit – Like the SUBTOTAL function, it's easier to use a familiar tool, than to learn a new one

  习惯 –与SUBTOTAL函数一样，使用熟悉的工具比学习新工具更容易

每月小计 (Subtotals By Month)

If you are still using the Subtotal feature, here's a trick that you might not know.

如果您仍在使用小计功能，则可能不知道这是一个技巧。

Recently, Bill Jelen discovered that you can group by months, without adding any extra columns to the data. In the past, Bill always created a formula to format the dates, and grouped on that column. Now he simply formats the date cells, and it works just as well, without extra column.

最近，Bill Jelen发现您可以按月分组 ，而无需在数据中添加任何额外的列。 过去，Bill总是创建一个公式来格式化日期，并在该列上进行分组。 现在，他只需格式化日期单元格，它就可以正常工作，而无需额外的列。

Watch Bill's video to see both methods – the new technique starts about the 2:00 minute mark of the video.

观看Bill的视频以查看这两种方法-新技术大约在视频的2:00分钟开始。

演示地址

翻译自: https://contexturesblog.com/archives/2017/11/16/do-you-still-need-excel-subtotals/

excel每页小计累计