【已开贴】字符争霸第12期：找数字

向芃芳

=IF(INDIRECT(ADDRESS(INDEX({5,5,5,5,5,11,11,11,11,11},IF(Z3>0,Z3,10)),INDEX({3,7,11,15,19,3,7,11,15,19},IF(Z3>0,Z3,10)))):INDIRECT(ADDRESS(INDEX({5,5,5,5,5,11,11,11,11,11},IF(Z3>0,Z3,10))+4,INDEX({3,7,11,15,19,3,7,11,15,19},IF(Z3>0,Z3,10))+2))="","",INDIRECT(ADDRESS(INDEX({5,5,5,5,5,11,11,11,11,11},IF(Z3>0,Z3,10)),INDEX({3,7,11,15,19,3,7,11,15,19},IF(Z3>0,Z3,10)))):INDIRECT(ADDRESS(INDEX({5,5,5,5,5,11,11,11,11,11},IF(Z3>0,Z3,10))+4,INDEX({3,7,11,15,19,3,7,11,15,19},IF(Z3>0,Z3,10))+2)))


说明：
1，标记数据源中每个数的位置(分别标记数字左上角单元格的行号和列号)；
2，按Z3,在数据源中找到对应数字的位置(INDEX)；
3，数组公式直接复制源数据；
4，源数据中为空的，结果置空("").

lasharks

先上一个 91字符

1. =MID(" "&IF(1,OFFSET(B4,ROW()+OR(Z3>5,Z3=0)*6-9,COLUMN()+TEXT(MOD(Z3,5),"#;;5")*4-29)),2,1)

复制代码

尛ぃ謉

``

1. =OFFSET(S5:U9,OR(15:15=Z3)*6,MOD(Z3,-5)*4)&""

复制代码

向芃芳

=IF(INDIRECT(ADDRESS(INDEX({5,5,5,5,5,11,11,11,11,11},IF(Z3>0,Z3,10)),INDEX({3,7,11,15,19,3,7,11,15,19},IF(Z3>0,Z3,10)))):INDIRECT(ADDRESS(INDEX({5,5,5,5,5,11,11,11,11,11},IF(Z3>0,Z3,10))+4,INDEX({3,7,11,15,19,3,7,11,15,19},IF(Z3>0,Z3,10))+2))="","",INDIRECT(ADDRESS(INDEX({5,5,5,5,5,11,11,11,11,11},IF(Z3>0,Z3,10)),INDEX({3,7,11,15,19,3,7,11,15,19},IF(Z3>0,Z3,10)))):INDIRECT(ADDRESS(INDEX({5,5,5,5,5,11,11,11,11,11},IF(Z3>0,Z3,10))+4,INDEX({3,7,11,15,19,3,7,11,15,19},IF(Z3>0,Z3,10))+2)))


说明：
1，标记数据源中每个数的位置(分别标记数字左上角单元格的行号和列号)；
2，按Z3,在数据源中找到对应数字的位置(INDEX)；
3，数组公式直接复制源数据；
4，源数据中为空的，结果置空("").

bishen

想学一下，也看不到哦

chh0926

回答，谢谢。

满坛皆为吾师

=OFFSET(C5,((Z3<6)*Z3<1)*6,MOD(Z3+9,5)*4,5,3)&""
选定实心框后一次性输入

一杯清荼

1. =OFFSET(C5:E9,OR(Z3>5,Z3=0)*6,MOD(Z3-1,5)*4)&""

复制代码

《渐行渐远》

{=OFFSET(C5:E9,(Z3^2>=6*Z3)*6,MOD(Z3-1,5)*4)&""}

daniel32

选定z10:ab14
=OFFSET(C5:E9,IF(AND(1<=Z3,Z3<=5),,6),IF(OR(Z3=0,AND(6<=Z3,Z3<=9)),(RIGHT(Z3+5,1)-1)*4,(Z3-1)*4))&""
=OFFSET(C5:E9,IF(AND(1<=Z3,Z3<=5),,6),IF(OR(Z3=0,6<=Z3),(RIGHT(Z3+5)-1)*4,(Z3-1)*4))&""
三键结束