按月份计数出现次数 [英] Count number of occurrences by month

问题描述

我正在创建一个电子表格，其中所有数据都放在一张纸上，而度量标准在另一张纸上.

I am creating a spreadsheet with all my data on one sheet and metrics on the other.

在工作表1的单元格A2:A50中，我的日期格式为(4/5/13).在单元格E5中的工作表2上，我有四月，我希望它总计在F5中创建的PO的数量.

On sheet 1 in cells A2:A50 I have the dates in this format (4/5/13). On sheet 2 in cell E5 I have April and I want it to total the number of PO's created in F5.

我该怎么做?

我尝试使用

=COUNTIF('2013'!$A$2:$A$50,'2013 Metrics'!E5).

我有一种感觉，因为我的范围是13年4月5日格式，而我的标准是4月，所以不起作用.

I have a feeling that since my range is in 4/5/13 format and my criteria is April that won't work.

我能够使用此公式计算每月的总支出:

I was able to use this formula for total spend by month:

=SUM(IF(MONTH('2013'!$A$2:$A$19)=4,'2013'!$D$2:$D$19,0))

但运气不好，每个月有多少订单.

but not luck with how many PO's by month.

推荐答案

使用数据透视表.您可以通过右键单击数据透视表并单击刷新来手动刷新数据透视表的数据源.否则，您可以设置一个worksheet_change宏-或只是一个刷新按钮.数据透视表教程位于此处: http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

Use a pivot table. You can manually refresh a pivot table's data source by right-clicking on it and clicking refresh. Otherwise you can set up a worksheet_change macro - or just a refresh button. Pivot Table tutorial is here: http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

1)从日期"列中创建月份"列(例如=TEXT(B2,"MMM"))

1) Create a Month column from your Date column (e.g. =TEXT(B2,"MMM") )

2)在日期"列中创建年份"列(例如=TEXT(B2,"YYYY"))

2) Create a Year column from your Date column (e.g. =TEXT(B2,"YYYY") )

3)添加一个Count列，每个值均带有"1"

3) Add a Count column, with "1" for each value

4)使用计数"，月"和年"字段创建数据透视表 5)将年"和月"字段拖到行标签"中.确保年份"高于月份，因此数据透视表"首先按年份分组，然后按月份分组 6)将计数"字段拖到值"中以创建计数"

4) Create a Pivot table with the fields, Count, Month and Year 5) Drag the Year and Month fields into Row Labels. Ensure that Year is above month so your Pivot table first groups by year, then by month 6) Drag the Count field into Values to create a Count of Count

有更好的教程，我敢肯定只是google/bing数据透视表教程".

There are better tutorials I'm sure just google/bing "pivot table tutorial".

这篇关于按月份计数出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！