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

问题描述

我有一张按日期组织的 excel 培训表，我正在尝试制作月度统计报告.所以，我想通过一个excel公式计算统计列标题中指定的月份内完成的培训次数.标题的月份是数字格式，培训表中的日期是 01/23/2015 格式.例如，如果 stat 列的标题是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.如果不是 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屋！