I have written this function that will give me a monthly sum for two columns: one has the date of each order, one has the cost of each order.
我写了这个函数，每个月给我两个栏目的金额:一个有每个订单的日期，一个有每个订单的成本。
=SUMIF($C$1:$C$1000,">="&DATE(2010,6,1),$D$1:$D$1000)-SUMIF($C$1:$C$1000,">="&DATE(2010,7,1),$D$1:$D$1000)
Using data like this:
使用数据是这样的:
8/16/10 17:00 7.99
8/16/10 14:25 7.99
8/15/10 22:42 7.99
I end up with a table like this:
最后得到这样一个表格:
May 998
June 968.28
July 1239.76
August 514.96
However, now I would like to do daily sums and using my way I have to hand edit each row.
然而，现在我想做每日的加法，用我的方法来编辑每一行。
How can I do this better in Excel?
我怎样才能在Excel中做得更好呢?
5 个解决方案
#1
11
Use a column to let each date be shown as month number; another column for day number:
使用一列，让每个日期显示为月号;第一天的另一栏:
A B C D
----- ----- ----------- --------
1 8 6 8/6/2010 12.70
2 8 7 8/7/2010 10.50
3 8 7 8/7/2010 7.10
4 8 9 8/9/2010 10.50
5 8 10 8/10/2010 15.00
The formula for A1 is =Month(C1)
A1的公式是=月(C1)
The formula for B1 is =Day(C1)
B1的公式是=Day(C1)
For Month sums, put the month number next to each month:
每月金额，将月号放在每个月的下一个:
E F G
----- ----- -------------
1 7 July $1,000,010
2 8 Aug $1,200,300
The formula for G1 is =SumIf($A$1:$A$100, E1, $D$1:$D$100). This is a portable formula; just copy it down.
G1的公式是=SumIf($ 1:$ 100, E1， $1:$ 100)这是一个可移植的公式;复制下来。
Total for the day will be be a bit more complicated, but you can probably see how to do it.
这一天的总数会有点复杂，但是你可能会看到怎么做。
#2
11
Use pivot tables, it will definitely save you time. If you are using excel 2007+ use tables (structured references) to keep your table dynamic. However if you insist on using functions, go with Smandoli's suggestion. Again, if you are on 2007+ use SUMIFS, it's faster compared to SUMIF.
使用数据透视表，绝对会节省你的时间。如果您正在使用excel 2007+使用表(结构化引用)来保持表的动态性。但是，如果您坚持使用函数，请遵循Smandoli的建议。同样，如果您使用的是2007+ SUMIFS，那么它比SUMIF要快。
#3
7
Following up on Niketya's answer, there's a good explanation of Pivot Tables here: http://peltiertech.com/WordPress/grouping-by-date-in-a-pivot-table/
根据Niketya的回答，这里有一个关于数据透视表的很好的解释:http://peltiertech.com/wordpress/groupby - data -a- pivottable/
For Excel 2007 you'd create the Pivot Table, make your Date column a Row Label, your Amount column a value. You'd then right click on one of the row labels (ie a date), right click and select Group. You'd then get the option to group by day, month, etc.
对于Excel 2007，您将创建Pivot表，使您的日期列为行标签，您的Amount列是一个值。然后右键单击其中一个行标签(即日期)，右键单击并选择Group。然后你可以按天、按月进行分组。
Personally that's the way I'd go.
我个人是这么想的。
If you prefer formulae, Smandoli's answer would get you most of the way there. To be able to use Sumif by day, you'd add a column with a formula like:
如果你更喜欢公式，Smandoli的回答会让你得到更多的答案。为了能够在白天使用Sumif，你需要添加一个列，公式如下:
=DATE(YEAR(C1), MONTH(C1), DAY(C1))
where column C contains your datetimes.
其中C列包含您的日期时间。
You can then use this in your sumif.
你可以在你的sumif中使用这个。
#4
1
Add a column to your existing data to get rid of the hour:minute:second time stamp on each row:
在现有数据中添加一列，以消除每一行上的小时:分钟:第二次时间戳:
=DATE(YEAR(A1), MONTH(A1), DAY(A1))
Extend this down the length of your data. Even easier: quit collecting the hh:mm:ss data if you don't need it. Assuming your date/time was in column A, and your value was in column B, you'd put the above formula in column C, and auto-extend it for all your data.
将此扩展到数据的长度。更简单的是:如果你不需要，不要收集hh:嗯，ss数据。假设您的日期/时间在A列，值在B列，那么您应该将上面的公式放在C列，并自动扩展所有数据。
Now, in another column (let's say E), create a series of dates corresponding to each day of the specific month you're interested in. Just type the first date, (for example, 10/7/2016 in E1), and auto-extend. Then, in the cell next to the first date, F1, enter:
现在，在另一列(假设是E)中，创建一系列与您感兴趣的月份的每一天相对应的日期。只需键入第一次日期，(例如，E1中的10/7/2016)，并自动扩展。然后，在第一个日期旁边的单元格F1中输入:
=SUMIF(C:C, E1, B:B )
autoextend the formula to cover every date in the month, and you're done. Begin at 1/1/2016, and auto-extend for the whole year if you like.
自动延长公式涵盖每个月的每个日期，你就完成了。从2016年1月1日开始，自动延长一年。
#5
0
If the second row has the same pattern as the first row, you just need edit first row manually, then you position your mouse pointer to the bottom-right corner, in the mean time, press ctrl key to drag the cell down. the pattern should be copied automatically.
如果第二行与第一行具有相同的模式，您只需手动编辑第一行，然后将鼠标指针定位到右下角，同时按ctrl键将单元格拖下。模式应该自动复制。
#1
11
Use a column to let each date be shown as month number; another column for day number:
使用一列，让每个日期显示为月号;第一天的另一栏:
A B C D
----- ----- ----------- --------
1 8 6 8/6/2010 12.70
2 8 7 8/7/2010 10.50
3 8 7 8/7/2010 7.10
4 8 9 8/9/2010 10.50
5 8 10 8/10/2010 15.00
The formula for A1 is =Month(C1)
A1的公式是=月(C1)
The formula for B1 is =Day(C1)
B1的公式是=Day(C1)
For Month sums, put the month number next to each month:
每月金额，将月号放在每个月的下一个:
E F G
----- ----- -------------
1 7 July $1,000,010
2 8 Aug $1,200,300
The formula for G1 is =SumIf($A$1:$A$100, E1, $D$1:$D$100). This is a portable formula; just copy it down.
G1的公式是=SumIf($ 1:$ 100, E1， $1:$ 100)这是一个可移植的公式;复制下来。
Total for the day will be be a bit more complicated, but you can probably see how to do it.
这一天的总数会有点复杂，但是你可能会看到怎么做。
#2
11
Use pivot tables, it will definitely save you time. If you are using excel 2007+ use tables (structured references) to keep your table dynamic. However if you insist on using functions, go with Smandoli's suggestion. Again, if you are on 2007+ use SUMIFS, it's faster compared to SUMIF.
使用数据透视表，绝对会节省你的时间。如果您正在使用excel 2007+使用表(结构化引用)来保持表的动态性。但是，如果您坚持使用函数，请遵循Smandoli的建议。同样，如果您使用的是2007+ SUMIFS，那么它比SUMIF要快。
#3
7
Following up on Niketya's answer, there's a good explanation of Pivot Tables here: http://peltiertech.com/WordPress/grouping-by-date-in-a-pivot-table/
根据Niketya的回答，这里有一个关于数据透视表的很好的解释:http://peltiertech.com/wordpress/groupby - data -a- pivottable/
For Excel 2007 you'd create the Pivot Table, make your Date column a Row Label, your Amount column a value. You'd then right click on one of the row labels (ie a date), right click and select Group. You'd then get the option to group by day, month, etc.
对于Excel 2007，您将创建Pivot表，使您的日期列为行标签，您的Amount列是一个值。然后右键单击其中一个行标签(即日期)，右键单击并选择Group。然后你可以按天、按月进行分组。
Personally that's the way I'd go.
我个人是这么想的。
If you prefer formulae, Smandoli's answer would get you most of the way there. To be able to use Sumif by day, you'd add a column with a formula like:
如果你更喜欢公式，Smandoli的回答会让你得到更多的答案。为了能够在白天使用Sumif，你需要添加一个列，公式如下:
=DATE(YEAR(C1), MONTH(C1), DAY(C1))
where column C contains your datetimes.
其中C列包含您的日期时间。
You can then use this in your sumif.
你可以在你的sumif中使用这个。
#4
1
Add a column to your existing data to get rid of the hour:minute:second time stamp on each row:
在现有数据中添加一列，以消除每一行上的小时:分钟:第二次时间戳:
=DATE(YEAR(A1), MONTH(A1), DAY(A1))
Extend this down the length of your data. Even easier: quit collecting the hh:mm:ss data if you don't need it. Assuming your date/time was in column A, and your value was in column B, you'd put the above formula in column C, and auto-extend it for all your data.
将此扩展到数据的长度。更简单的是:如果你不需要，不要收集hh:嗯，ss数据。假设您的日期/时间在A列，值在B列，那么您应该将上面的公式放在C列，并自动扩展所有数据。
Now, in another column (let's say E), create a series of dates corresponding to each day of the specific month you're interested in. Just type the first date, (for example, 10/7/2016 in E1), and auto-extend. Then, in the cell next to the first date, F1, enter:
现在，在另一列(假设是E)中，创建一系列与您感兴趣的月份的每一天相对应的日期。只需键入第一次日期，(例如，E1中的10/7/2016)，并自动扩展。然后，在第一个日期旁边的单元格F1中输入:
=SUMIF(C:C, E1, B:B )
autoextend the formula to cover every date in the month, and you're done. Begin at 1/1/2016, and auto-extend for the whole year if you like.
自动延长公式涵盖每个月的每个日期，你就完成了。从2016年1月1日开始，自动延长一年。
#5
0
If the second row has the same pattern as the first row, you just need edit first row manually, then you position your mouse pointer to the bottom-right corner, in the mean time, press ctrl key to drag the cell down. the pattern should be copied automatically.
如果第二行与第一行具有相同的模式，您只需手动编辑第一行，然后将鼠标指针定位到右下角，同时按ctrl键将单元格拖下。模式应该自动复制。