Excel公式：根据日期条目显示总计

I've got a spreadsheet with two columns that represent the number of processed records, and the date the records were processed. In some cases, the records can be processed in multiple batches, so the document looks something like this:

我有一个包含两列的电子表格,表示已处理记录的数量以及处理记录的日期。在某些情况下,记录可以多批处理,因此文档看起来像这样:

33 4/1/2009
22 4/1/2009
12 4/2/2009
13 4/4/2009
36 4/4/2009

33 4/1/2009 22 4/1/2009 12 4/2/2009 13 4/4/2009 36 4/4/2009

I'm trying to add a new set of columns that contain a date, and shows the total number of records for that date, automagically:

我正在尝试添加一组包含日期的新列,并自动显示该日期的记录总数:

4/1/2009 55
4/2/2009 12
4/3/2009 0
4/4/2009 49

4/1/2009 55 4/2/2009 12 4/3/2009 0 4/4/2009 49

I know how to do this algorithmically, and I could probably manipulate the spreadsheet outside of Excel, but I'm trying to do this in the live spreadsheet, and am a bit bewildered as to how to pull it off.

我知道如何以算法方式执行此操作,并且我可能在Excel之外操作电子表格,但我正在尝试在实时电子表格中执行此操作,并且对于如何将其拉出来有点困惑。

Any ideas?

Thanks! IVR Avenger

谢谢! IVR复仇者

3 个解决方案

#1

Will the SUMIF function work for you? SUMIF([range],[criteria],[sum_range]) I think you could set range = the set of cells containing dates in your first listing, criteria would be the cell containing the date in the second listing, and sum_range would be the counts in the first column of your first listing.

SUMIF功能是否适合您? SUMIF([range],[criteria],[sum_range])我认为你可以设置range =在第一个列表中包含日期的单元格集,条件是包含第二个列表中日期的单元格,sum_range将是在第一个商家信息的第一列中计算。

#2

I would suggest using a Pivot Table. Put the dates into the row area and 'sum of' records in the data area. Nothing in the columns area.

我建议使用数据透视表。将日期放入行区域和数据区域中的“总和”记录。列区域中没有任何内容。

A pivot table will be more dynamic than a formula solution because it will only show you dates that exist.

数据透视表将比公式解决方案更具动态性,因为它只会显示存在的日期。

#3

Assuming your dates are in column B and the numbers to be accumulated are in A, you could try something like this:

假设您的日期在B列,并且要累积的数字在A中,您可以尝试这样的事情:

  | A  |   B      |    C                D
1 | 33 | 4/1/2009 | =MIN(B:B) | {=SUM(IF(B1:B5=C1,A1:A5,0))} |
2 | 22 | 4/1/2009 | =C1+1     | {=SUM(IF(B1:B5=C2,A1:A5,0))} |
3 | 12 | 4/2/2009 | =C2+1     | {=SUM(IF(B1:B5=C3,A1:A5,0))} |
4 | 13 | 4/4/2009 | =C3+1     | {=SUM(IF(B1:B5=C4,A1:A5,0))} |
5 | 36 | 4/4/2009 | =C4+1     | {=SUM(IF(B1:B5=C5,A1:A5,0))} |

Note the {} which signifies an array formula (input using Control-Shift-Enter) for any non-trivial amount of data it's heaps faster than SUMIF().

请注意{}表示数组公式(使用Control-Shift-Enter输入)表示任何非平凡数据量,它比SUMIF()更快。

I'd be inclined to define dynamic names for the A1:A5 and B1:B5 parts, something like

我倾向于为A1定义动态名称:A5和B1:B5部分,类似于

=OFFSET(A1,0,0,COUNT(A:A),1)

so that I didn't have to keep fixing up my formulae.

所以我没有必要继续修改我的公式。

There's still a manual element: adding new rows for extra dates, for example - that might be a good place for a little VBA. Alternatively, if you can get away with showing, for example, the last 90 days' totals, then you could fix the number of rows used.

还有一个手动元素:例如,为额外日期添加新行 - 这可能是一个小VBA的好地方。或者,如果您可以显示最近90天的总计,那么您可以修复使用的行数。

#1

#2