如何用公式取每个省份倒数前三的数据？

-建发

各位大神下午好，麻烦帮忙看一下如何用公式取每个省份倒数前三的数据？如下图

yfan17

1. =AGGREGATE(15,6,$B$2:$B$52/($A$2:$A$52=H3),COUNTIF($H$3:H3,H3))

复制代码

quqiyuan

=SMALL(IF($A$2:$A$52=H3,$B$2:$B$52),MOD(ROW(A1)-1,3)+1)

hugeinfo

=SMALL(FILTER($B$2:$B$52,$A$2:$A$52=H3),COUNTIF($H$3:H3,H3))

静听溪流

=SMALL(IF(A:A=H3,B:B),COUNTIF(H$2:H3,H3))

meiya

=SMALL(OFFSET($B$1,MATCH($H3,$A:$A,)-1,,COUNTIF($A:$A,$H3)),MOD(ROW(),3)+1)

alan57

1. =REDUCE(H2:I2,SORT(UNIQUE(A2:A52)),LAMBDA(x,y,VSTACK(x,IF({1,0},y,HSTACK(y,TAKE(SORT(FILTER(B2:B52,A2:A52=y)),3))))))

复制代码

jiaxianwa

=AGGREGATE(15,6,1/(H3=$A$2:$A$55)*($B$2:$B$55),(MOD(ROW(),3)+1))

一杯绿茶来了

H3=INDEX(A:A,MIN(IF(COUNTIF($H$2:H2,$A$2:$A$52)=MOD(ROW(A1)-1,3),ROW($2:$52),4^8)))
I3=SMALL(IF($A$2:$A$52=H3,$B$2:$B$52,4^8),MOD(ROW(A1)-1,3)+1)
365公式=REDUCE({"省份","数据"},UNIQUE(A2:A52),LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,SMALL(IF(A:A=y,B:B),ROW(1:3))),y))))

kxm1975

h3={MID(TEXTJOIN("",1,REPT(UNIQUE(A2:A52),3)),(ROW(1:15)-1)*3+1,3)}
i3={SMALL(IF($A$2:$A$52=L3,$B$2:$B$52),COUNTIF($L$2:L3,L3))}