[已解决]提取指定数字

PJTL23

问题请看附件，请把公式贴在论坛里，谢谢!

最佳答案

月排行榜 / 总排行榜

wang5244

2023-6-13 17:36

l2=INDIRECT(TEXT(SMALL(TEXT(((($I$2:$I$20<>"")+($J$2:$J$20<>"")=1)*ROW($I$2:$J$20)*100+(($I$2:$I$20<>"")+($J$2:$J$20<>"")=1)*COLUMN($I$2:$J$20))*($I$2:$J$20<>""),"[=0]!99999")*1,ROW(A1)),"r00c00"),)
l16=INDIRECT(TEXT(SMALL(TEXT((($I$2:$I$20<>"")+($J$2:$J$20<>"")=2)*ROW($I$2:$J$20),"[=0]!99999")*1,ROW(A1)),"r00c9"),)
m2=INDIRECT(TEXT(SMALL(TEXT(((($B$16:$H$16<>"")+($B$17:$H$17<>"")=1)*ROW($B$16:$H$17)*100+(($B$16:$H$16<>"")+($B$17:$H$17<>"")=1)*COLUMN($B$16:$H$17))*($B$16:$H$17<>""),"[=0]!99999")*1,ROW(A1)),"r00c00"),)
m16=INDIRECT(TEXT(SMALL(TEXT((($B$16:$H$16<>"")+($B$17:$H$17<>"")=2)*COLUMN($B$16:$H$17),"[=0]!99999")*1,ROW(A1)),"r16c00"),)

跳转到最佳答案楼层

PJTL23

wang5244

l2=INDIRECT(TEXT(SMALL(TEXT(((($I$2:$I$20<>"")+($J$2:$J$20<>"")=1)*ROW($I$2:$J$20)*100+(($I$2:$I$20<>"")+($J$2:$J$20<>"")=1)*COLUMN($I$2:$J$20))*($I$2:$J$20<>""),"[=0]!99999")*1,ROW(A1)),"r00c00"),)
l16=INDIRECT(TEXT(SMALL(TEXT((($I$2:$I$20<>"")+($J$2:$J$20<>"")=2)*ROW($I$2:$J$20),"[=0]!99999")*1,ROW(A1)),"r00c9"),)
m2=INDIRECT(TEXT(SMALL(TEXT(((($B$16:$H$16<>"")+($B$17:$H$17<>"")=1)*ROW($B$16:$H$17)*100+(($B$16:$H$16<>"")+($B$17:$H$17<>"")=1)*COLUMN($B$16:$H$17))*($B$16:$H$17<>""),"[=0]!99999")*1,ROW(A1)),"r00c00"),)
m16=INDIRECT(TEXT(SMALL(TEXT((($B$16:$H$16<>"")+($B$17:$H$17<>"")=2)*COLUMN($B$16:$H$17),"[=0]!99999")*1,ROW(A1)),"r16c00"),)