Excel 如何用学生花名册统计班额

发布网友发布时间：2022-06-01 06:46

共2个回答

热心网友时间：2024-12-02 10:08

1.. 总人数 =COUNTA(C2:C5000)
其中男生 =COUNTIF(D2:D5000,"男") ，女生 =COUNTIF(D2:D5000,"女")

2. 少数民族 =COUNTIF(E2:E5000,"<>汉") （如果E列里汉族是 “汉族”，请把公式中的“汉”改为“汉族”）；其中，男生 =SUMPRODUCT((E2:E5000,"<>汉")*(D2:D5000="男")) ，女生 =SUMPRODUCT((E2:E5000,"<>汉")*(D2:D5000="女"))

3. 7-12周岁 =COUNTIF(F2:F5000,">"&DATE(1997,8,DAY(TODAY())))-COUNTIF(F2:F5000,">"&DATE(2003,8,DAY(TODAY()))) ；13周岁以上 =COUNTIF(F2:F5000,"<="&DATE(1997,8,DAY(TODAY())))

4. 合计 =COUNTIF(F2:F5000,">="&DATE(2004,9,1))-COUNTIF(F2:F5000,">"&DATE(2005,8,31))
其中，女生=SUMPRODUCT((F2:F5000>=DATE(2004,9,1))*(F2:F5000<=DATE(2005,8,31))*(D2:D5000="女"))；
男生=SUMPRODUCT((F2:F5000>=DATE(2004,9,1))*(F2:F5000<=DATE(2005,8,31))*(D2:D5000="男"))；
汉族=SUMPRODUCT((F2:F5000>=DATE(2004,9,1))*(F2:F5000<=DATE(2005,8,31))*(E2:E5000="汉"))；
少数民族=SUMPRODUCT((F2:F5000>=DATE(2004,9,1))*(F2:F5000<=DATE(2005,8,31))*(E2:E5000<>"汉"))

热心网友时间：2024-12-02 10:09

问题不算，请详细点