excel统计某个区间的内某个值得个数

答案:2 悬赏:20 手机版

解决时间 2021-03-29 15:32

提问者网友：自食苦果
2021-03-28 17:12

excel统计某个区间的内某个值得个数

最佳答案

五星知识达人网友：夜风逐马
2021-03-28 17:26

统计1-50中有几个A、S、D
公式为
=SUM(COUNTIFS(A:A,">=1",A:A,"<=50",B:B,{"A","S","D"}))

51-100中有几个A、S、D
公式为
=SUM(COUNTIFS(A:A,">=51",A:A,"<=100",B:B,{"A","S","D"}))

101-150有几个A、S、D
公式为
=SUM(COUNTIFS(A:A,">=101",A:A,"<=150",B:B,{"A","S","D"}))

如果只想用一个公式来下拉,则公式为
=SUM(COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,{"A","S","D"}))

如果不是一次的合计A、S、D,而是分开统计的,则
求A个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"A")

求S个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"S")

求D个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"D")

分别下拉填充追问是分别统计。。几个A几个s几个D追答如果不是一次的合计A、S、D,而是分开统计的,则
求A个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"A")

求S个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"S")

求D个数的公式为
=COUNTIFS(A:A,">="&ROW(A1)*50-49,A:A,"<="&ROW(A1)*50,B:B,"D")

分别下拉填充

怎么输入1-50，51-100，这样的数据，快速填充填充的是1-51
单元格写入公式
=ROW(A1)*50-49&"-"&ROW(A1)*50
下拉填充公式

全部回答

1楼网友：动情书生
2021-03-28 18:56

统计1-50中有几个A、S、D,
=SUMPRODUCT(A1:A999>=1)*(A1:A999<=50)*(B1:B999="A"))
=SUMPRODUCT(A1:A999>=1)*(A1:A999<=50)*(B1:B999="S"))
=SUMPRODUCT(A1:A999>=1)*(A1:A999<=50)*(B1:B999="D"))
其他问题的公式可依此类推

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息