vba 如何使用宏在另一列中基于同一月份的一列中的数据求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/317447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sum data in one column based on the same month in another column using macros
提问by Sparr
I am using macros in excel 2007 for my work. I am working with many data and I need to sum data from 2 or more rows in the same coloumn according to the same month. However the month column is expressed as date.
我在 excel 2007 中使用宏来完成我的工作。我正在处理许多数据，我需要根据同一个月将同一列中 2 行或更多行的数据相加。但是，月份列表示为日期。
for example, i have series of data
例如，我有一系列数据
A B
2/10/2008 2
2/10/2008 3
4/10/2008 3
5/11/2008 4
5/11/2008 5
I want the result to be displayed in column C and D as followed
我希望结果显示在 C 列和 D 列中，如下所示
C D
Oct/08 8
Nov/08 9
I am very thankful if anyone can help me.
如果有人能帮助我，我非常感谢。
regards,
问候，
Tifu
蒂芙
回答by Sparr
A B C D E F
1 10/ 1/2008 24106 1 Oct-08 24106 8
2 10/31/2008 24106 7 Nov-08 24107 11
3 11/ 1/2008 24107 8 Dec-08 24108 6
4 11/30/2008 24107 3
5 12/ 1/2008 24108 2
6 12/ 2/2008 24108 4
B1 =MONTH(A1)+YEAR(A1)*12
E1 =MONTH(D1)+YEAR(D1)*12
F1 =SUMIF(B:B,CONCATENATE("=",E1),C:C)
I had to overcome two problems to solve this. First, SUMIF can only do direct comparison, it cannot run a function on the source location (except for range functions, which the date and time functions are not), so I had to add the B column. The E column is optional, it could be implemented as part of the formula in F, but I left it independent for illustrative purposes. Second, SUMIF takes a string parameter describing the comparison, so I built the necessary string (it is "=24106" for F1) on the fly.
我必须克服两个问题才能解决这个问题。首先，SUMIF 只能做直接比较，它不能在源位置运行函数（范围函数除外，日期和时间函数不是），所以我不得不添加 B 列。E 列是可选的，它可以作为 F 中公式的一部分来实现，但为了说明目的，我将其保留为独立的。其次，SUMIF 采用描述比较的字符串参数，因此我即时构建了必要的字符串（F1 为“=24106”）。
回答by wakingrufus
using array functions: C1:
使用数组函数：C1：
=date(2008,small(month($A:$A),1),1)
C2:
C2：
=date(2008,small(month($A:$A),2),1)
right click on these cells and format them as mmm/yy
右键单击这些单元格并将它们格式化为 mmm/yy
D1:
D1：
=sum(if(month($C1)=month($A:$A),($B:$B),0))
make sure to press ctrl-shift-enter when done writing this formula. then copy an paste it down as needed.
完成编写此公式后，请确保按 ctrl-shift-enter。然后根据需要复制粘贴。
回答by Galwegian
If you are unfamiliar with VBA, I would start off by recording a macro while doing what you want to do by using the Subtotals feature under the Data menu (i.e. through Excel's interface).
如果您不熟悉 VBA，我会先录制一个宏，同时使用“数据”菜单下的“小计”功能（即通过 Excel 的界面）做您想做的事情。
Once the macro is recorded, you can look at the VBA code produced, and alter it to suit your needs.
录制宏后，您可以查看生成的 VBA 代码，并对其进行更改以满足您的需要。
回答by Thorsten
This should be possible to do using regular excel formulas:
这应该可以使用常规的 excel 公式来完成：
- extract the month of the date (some Month-function)
then do a conditional sum (function sumwhen (?)) like this:
= SumWhen(A:B, ReferenceDate, B:B) (with ReferenceDate = C2 C3 etc.)
- 提取日期的月份（某些月份功能）
然后做一个条件总和（函数 sumwhen (?)），如下所示：
= SumWhen(A:B, ReferenceDate, B:B) （使用 ReferenceDate = C2 C3 等）
(All I have is a German excel and the function names got translated as well, so my function names may be off. Maybe someone can check against an English excel and update if necessary.)
（我只有一个德语 excel 并且函数名也被翻译了，所以我的函数名可能是关闭的。也许有人可以检查英文 excel 并在必要时更新。）
