计算excel中特定月份的所有记录 [英] Count all records from certain month in excel

问题描述

我有一份按日期组织的excel培训，我想做一份每月统计报告。所以，我想通过一个excel公式计算在统计列的标题中指定的月份中完成的培训次数。标题具有数字格式的月份，培训表中的日期为01/23/2015格式。例如，如果统计列的标题是1，我想计算在2015年1月完成的所有培训。对此问题的任何帮助将非常感谢！

I have a sheet of trainings in excel that are organized by date and I am trying to do a monthly statistics report. So, I want to count, through an excel formula, the number of trainings that were done in the month that is specified in the header of the statistics column. The header has the month in number format and the dates in the training sheet are in 01/23/2015 format. For example, if the header of the stat column was "1" I would want to count all the trainings that were done in January 2015. Any help with this issue would be greatly appreciated!

推荐答案

如果练习的日期格式为mm / dd / yyyy，我建议为该月创建一个新列（在B列中） C）年度

If the dates of the exercises are in format mm/dd/yyyy I would suggest making a new column (in column B) for the month and a new column (in column C) for the year

从列中提取月份的公式为

The formula for extracting months from a column is

=TEXT(A1,"mmmm")

提取年份的公式为

=YEAR(A1)

将这些公式添加到B列和C列，现在您可以使用excel中的过滤器仅显示所需年份和月份的训练练习。我认为过滤器是查看您想要的练习的最佳方式，很容易从这些查看过滤器表中的行数计数。

Add these formulas to columns B and C and now you can use the filters in excel to show only training exercises from the year and month you want. I think the filters are the best way to view the exercises you want and it is easy to get counts from these looking at the number of rows in the filter table.

但是，如果要显示在某个月/年中发生的练习数，可以编写一个if语句，如果条件匹配，值将为1，如果条件不匹配，则值为0。这将是这样：

However, if you want to show the number of exercises that occurred in a certain month/year you can write an if statement that will give a value of 1 if your criteria is matched and a value of 0 if the criteria is not matched. This will look something like this:

=IF(B1="January",1,0)

这将检查B列中的月份是否为一月，如果是一月，则返回值1。如果不是一月，它将返回0。

This will check if the month in column B is "January", then return the value 1 if it is "January". If it is not January, it will return 0.

要查看一年中出现的次数，请使用：

To see the number of times a year appears use:

=IF(C1=2015,1,0)

这将检查列C中的年份是否为2015，而不是返回值1.如果不是一月，返回0.

This will check if the year in column C is 2015, than return the value 1. If it is not January, return 0.

将这些公式添加到列D和E。

Add these formulas to columns D and E.

然后将此列中的值相加

=SUM(D1:D1000)

月份，

=SUM(E1:E1000)

，您将获得与您的条件匹配的报告数量。这是您可以使用Excel公式获取某个月份或年份的练习次数的一种方法。如果您有超过1000个报告，只需将数字更改为10,000或更多。

for year, and you will get the number of reports that matches your criteria. This is one way you can use Excel formulas to obtain the number of exercises in a certain month or year. If you have more than 1000 reports just change the number to 10,000 or more.

希望这有助于！

这篇关于计算excel中特定月份的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！