如何计算出勤表中不同条件下的学生人数 [英] How to count students number on different conditions in a attendance sheet

问题描述

我一直在处理Excel的出勤率表.我已经问过一个有关出席人数的问题.现在我有不同的情况.我在性别"部分中同时有男生(B)/女生(G)"学生.我也有一个类别部分，其中有5个不同的类别.现在我有对应于每个学生姓名的日期，在该姓名上，老师将"P"或"A"放在上面.请掠夺图片:

我需要在以下条件下计算学生总数:

1. 一个月内(至少1天)有多少男孩在场?
2. 一个月有多少女孩(至少1天)在场?
3. 每个月有多少个男孩(至少1天)出现在每个类别中?
4. 一个月中(至少1天)出现在每个类别中的女孩有多少?

请注意，学生的姓名不是唯一的.

请帮助！

我试图计算以这种方式参加至少一堂课的男学生总数(以B为例):

  = COUNTIFS(D5:D49，"B"，F5:AJ49，"P") 

但是它抛出了一个错误.

谢谢大家的答复.但是我想我无法清楚地说明我的观点.

在这里，我想计算在所有情况下有多少学生在场，而不是在每个学生在场多少次".即我需要计算即使在一天中上学的所有情况下的学生.一个学生在场，假设一个月有20天，总数将是1，而不是20.

我希望我现在把事情弄清楚了.

谢谢你们的帮助.我通过使用以下方法添加了一个额外的列来完成该任务:如果日期行中有任何"P"，则该列将返回1

  = IF((COUNTIF(G5:AK5，"P"))> 0,1,0) 

然后，我使用具有多个条件的COUNTIFS，它检查并计数(上面的)额外单元格的值是否为1，并检查性别"和类别"行并对其进行计数:

  = COUNTIFS(C5:C27，> 0"，E5:E27，"G"，F5:F27，"CAT1") 

但是，我不想使用多余的单元格，因为它不需要用于出席目的.所以我在

I have been working on an Excel sheet of school attendance. I have already asked a question about that attendance. Now I have a different situation. I have both "boy(B)/girl(G)" students in Gender section. I also have a Category section having 5 different categories. Now I have dates corresponding to each name of the students where a teacher puts either a "P" or an "A". Please loot into the image:

I need to count total number of students on below conditions:

1. How many boys were present (at least 1 day) in a month?
2. How many girl were present (at least 1 day) in a month?
3. How many boys in each category were present (at least 1 day) in a month?
4. How many girls in each category were present (at least 1 day) in a month?

Kindly note that the names of the students are not unique.

Please help !

EDIT:

I tried to count "total number of male students (B - in my case) attended at least 1 class in this way:

=COUNTIFS(D5:D49,"B",F5:AJ49,"P")

But it is throwing an error.

EDIT:

Thank you all for the responses. But I guess I did not able to make my point clearly.

Here I want to count "How many students were present in all cases and not "how many times each student was present", i.e. I need to count student in all cases who went to school even a single day. So if a student was present let’s say 20 days in a month, the total number will be 1 and not 20.

I hope I made things clear now.

EDIT:

Thank you guys for the help. I have accomplished the task by adding an extra column which returns a 1 if there is any "P" in the date row by using:

=IF((COUNTIF(G5:AK5,"P"))>0,1,0)

Then I am using a COUNTIFS with multiple conditions which checks and count if the extra cell (above) have a value of 1 and checks the Gender and Category row and count it:

=COUNTIFS(C5:C27,">0", E5:E27,"G", F5:F27,"CAT1")

Though, I don't want to use the extra cell as it is not needed for attendance purpose. So I asked a different question here

解决方案

You need to use SUM function in this case. Take a look at this question.

Hers is the table with all the formulas.

I've highlighted your questions and put the numbers (1,2,3,4).
So the formulas are:

1. =SUM(IF($D$3:$D$12="b",IF($F$3:$O$12="p",1,0),0))  

2. =SUM(IF($D$3:$D$12="g",IF($F$3:$O$12="p",1,0),0))  

3. =SUM(IF($E$3:$E$12="cat1",IF($D$3:$D$12="b",IF($F$3:$O$12="p",1,0),0)))  

4. =SUM(IF($E$3:$E$12="cat1",IF($D$3:$D$12="g",IF($F$3:$O$12="p",1,0),0)))  

In formulas 3 and 4, you just have to change cat1 to cat2 to get the result for category 2. And change cat2 to cat3 and so on.

Basically you are summing the values based on multiple conditions.

Just change the cell references.

In order to make it work you need to type CTRL + SHIFT + ENTER in the cell for it to calculate properly

这篇关于如何计算出勤表中不同条件下的学生人数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！