从序号范围及水果种类名称个数问题

巴山渝人

工作表 SHEET2 , 黄色就是统计个数结果
12-2.rar (4.22 KB, 下载次数: 198)

2021-12-2 08:59 上传

点击文件名下载附件
12-2.rar

王二斤

B2=SUM(COUNTIFS(Sheet1!$B:$B,$A2,Sheet1!$A:$A,">="&MID(SUBSTITUTE(B$1,"~",REPT(" ",9)),{1,10},9)+{0,1})*{1,-1})
右拉下拉

柳絮榆钱

=MMULT(COUNTIFS(Sheet1!$A:$A,{">=",">"}&IMREAL(IMDIV(SUBSTITUTE(B1,"~","+")&"i",{1,"i"})),Sheet1!$B:$B,$A2),{1;-1})

右拉下拉

艾桐

数据透视表，对序号进行手动分组：

骑扬来财

多谢两位大佬,您们太牛了,问题总算解决了

韶华不负

=COUNT(TEXT(Sheet1!$A$2:$A$48,SUBSTITUTE("[<"&B$1&"];〇","~","]〇;[<="))/(Sheet1!$B$2:$B$48=$A2))

换个套路，右拉下拉

新时代的中年人

=COUNTIFS(Sheet1!$B:$B,$A2,Sheet1!$A:$A,">="&LEFT(B$1),Sheet1!$A:$A,"<="&-LOOKUP(,-RIGHT(B$1,{1,2})))

不明所以丢了个账号

SHEET2 , B2:F2
=TRANSPOSE(FREQUENCY(IF(Sheet1!$B$2:$B$48=$A2,--Sheet1!$A$2:$A$48),{2,4,6,8,11}))