在Excel中创建汇总

It's easy to create a running total in Excel, and show the accumulated amount in each row. You can use the SUMIF function in a table, or show running totals in a pivot table.

在Excel中创建运行总计并在每一行中显示累计金额很容易。 您可以在表格中使用SUMIF函数，或在数据透视表中显示运行总计 。

But what if you want to show the total for a specific number of previous months – not all the previous months?

但是，如果您想显示前几个月的特定数量的总和，而不是前几个月的全部，该怎么办？

前12个月的总计 (Total for Previous 12 Months)

Recently, someone asked how to show a running total for the previous 12 months, instead of an ongoing running total.

最近，有人问如何显示过去12个月的运行总计，而不是持续运行的总计。

Sometimes I include a Rolling Average in a client's worksheet – usually a 3 month average. So, if we're summing the amounts, I guess we can call that a Rolling Total (an accountant might have a different name for it).

有时，我会在客户的工作表中加入滚动平均值，通常是3个月的平均值。 因此，如果我们将金额相加，我想我们可以称其为滚动总和(会计师可能使用不同的名称)。

设置数据 (Set Up the Data)

To create a running total, I set up a small table with test data. There is only one entry per month, but the formula would work with multiple rows per month

要创建运行总计，我用测试数据设置了一个小表。 每月只有一个条目，但是该公式每月可以处理多行

• The dates are in column A
  日期在A列中
• The monthly amounts are in column B
  每月金额在B列中
• The list is sorted by date
  列表按日期排序

滚动总计公式 (Formula for Rolling Total)

In this example only the rows for the previous 11 months, and the current month, will be included in the total. The formula is explained in the next section.

在此示例中，仅前11个月和当前月份的行将包括在总数中。 下一节将说明该公式。

1. Select the first cell in which you want to see the rolling total -- cell C2 in this example
   选择要在其中查看滚动总计的第一个单元格-在此示例中为单元格C2

2. Enter the following formula, and press Enter:

   输入以下公式，然后按Enter：

   =SUMIF(A$2:A2,">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2)),B$2:B2)

   = SUMIF(A $ 2：A2，“> =”＆DATE(YEAR(A2)，MONTH(A2)-11，DAY(A2))，B $ 2：B2)

3. Copy the formula down to the last row with data.
   将公式向下复制到包含数据的最后一行。
4. Each row shows the Rolling Total for the latest 12 months (if available)
   每行显示最近12个月的滚动总和(如果有)

这个怎么运作 (How It Works)

Here is the formula again, that was entered in cell C2:

再次是在单元格C2中输入的公式：

     =SUMIF(A$2:A2,">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2)),B$2:B2)

= SUMIF( A $ 2：A2 ， “> =” ＆ DATE(YEAR(A2)，MONTH(A2)-11，DAY(A2)) ， B $ 2：B2 )

1. The formula checks the dates in column A, starting in row 2 (A$2), and down to the current row (A2)

1.公式检查A列中的日期，该日期从第2行(A $ 2)开始，一直到当前行(A2)

     =SUMIF(A$2:A2

     = SUMIF( A $ 2：A2

2. The DATE function calculates the date that is 11 months prior to date in current row

2. DATE函数计算当前行中日期之前11个月的日期

     DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))

     DATE(YEAR(A2)，MONTH(A2)-11，DAY(A2))

3. The >= operator checks for dates that are greater than or equal to that date,

3.> =运算符检查日期是否大于或等于该日期，

     ">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))

     “> =” ＆ DATE(YEAR(A2)，MONTH(A2)-11，DAY(A2))

4. For rows that meet the criterion, the formula sums the amounts in column B, starting in row 2 (B$2) down to the current row (B2)

4.对于符合条件的行，该公式将B列中的金额相加，从第2行(B $ 2)一直到当前行(B2)

     B$2:B2

     B $ 2：B2

更改月数 (Change the Number of Months)

Instead of hard-coding the number of months in the formula, you could put that number in a cell, so it can be changed easily.

您可以将数字放在单元格中，而不用硬编码公式中的月数，因此可以轻松更改它。

In the screen shot below, the number of months is entered in cell E1, and the formula was changed to include that reference.

在下面的屏幕快照中，在单元格E1中输入了月数，并更改了公式以包括该引用。

  =SUMIF(A$2:A2,">=" & DATE(YEAR(A2),MONTH(A2)-($E$1-1),DAY(A2)),B$2:B2)

= SUMIF(A $ 2：A2，“> =”＆DATE(YEAR(A2)，MONTH(A2)- ($ E $ 1-1) ，DAY(A2))，B $ 2：B2)

The heading in cell C1 also references the number of months:

C1单元格中的标题还引用了月数：

  =E1 & " Mth Total"

= E1和“总数Mth”

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

You can download the sample file from my website, on the Sum Functions page. That file has 3 Rolling Total examples – the 3rd one hides the total if the number of previous months is less than the number entered in cell E1.

您可以从我的网站上的“求和函数”页面下载示例文件 。 该文件有3个滚动总计示例-如果前几个月的数量少于在单元格E1中输入的数量，则第三个隐藏总计。

翻译自: https://contexturesblog.com/archives/2015/11/12/create-a-rolling-total-in-excel/