【函数小题】数字拆分

静听溪流

题目应该不太难，想看看大家的公式是怎么写的。具体要求和效果看图。

我走我流

1、=INDEX(A$2:A$5,MATCH(1=1,ROW(A1)<=MMULT(N(ROW($1:$4)>=COLUMN($A:$D)),CEILING($B$2:$B$5/5,1)),))
2、=IF(OR(ROW(B1)=MMULT(N(ROW($1:$4)>=COLUMN($A:$D)),CEILING($B$2:$B$5/5,1))),MOD(INDEX(B$2:B$5,MATCH(1=1,ROW(B1)<=MMULT(N(ROW($1:$4)>=COLUMN($A:$D)),CEILING($B$2:$B$5/5,1)),))-1,5)+1,5)

micch

1. =TEXT(LOOKUP(ROW()-3,MMULT(N({1;2;3;4}>{1,2,3,4}),CEILING($B2:$B5/5,1)),IFERROR(MOD(A2:A5-1,5)+1,A2:A5)),IF(F3:F13=F4:F14,5,))

复制代码

区域数组右拉。表不能用区域数组，只好转化为单元格区域了，可惜格式与结果不符

lfspecter

1.

1. =INDEX(A:A,SMALL(IF(ROUNDUP($B$2:$B$5/5,)>=COLUMN(A:AB),ROW($2:$5)),ROW(A1)))

复制代码

2.
没写出来呢

dipperrrr

先偷鸡
1.=INDEX(A:A,SMALL(IF(B$2:B$5/5>COLUMN(A:E)-1,ROW($2:$5)),ROW(A1)))
2.=MIN(MIN(--TEXT(SUBTOTAL(9,OFFSET(B$2,,,{1;2;3;4}))-SUM(E$2:E2),"0;9;9")),5)

micch

1. =INDEX(IFERROR(CEILING(MOD(A$2:A$5-1,5)+1,5^(F3=F4)),A$2:A$5),SMALL(IF($B$2:$B$5>COLUMN($A:$K)*5-5,{1;2;3;4}),ROW(A1)))

复制代码

数组右下拉

INaga2018

只做出一个，数组：

1. =INDEX(EVALUATE("{"""&CONCAT(REPT($A$2:$A$5&""";""",ROUNDUP($B$2:$B$5/5,)))&"""}"),ROW()-2)

复制代码

micch

1. =INDEX(A:A,SMALL(IF($B$2:$B$5>COLUMN($A:$K)*5-5,{2;3;4;5}),ROW(A1)))

复制代码

1. =DAY(SMALL(IF(B$2:B$5>{0,5,10,15,20},DATE({2;3;4;5},{1,2,3,4,5},TEXT(B$2:B$5-{0,5,10,15,20},"[>4]5"))),ROW(A1)))

复制代码

这坑掉的我措手不及，考虑到连续相同人名，干脆还是老实点分开写吧

micch

1. =MID(CONCAT(TRANSPOSE(IF(B2:B5>{0,5,10,15,20},A2:A5&TEXT(B2:B5-{0,5,10,15,20},"[>4]5"),""))),ROW()*3-{8,6},{2,1})

复制代码

人名两个字，数字个位数，长度固定干脆用concat试试，区域数组
不费脑子，等着看各位老师的公式吧

swyrlbq

一条公式
=IF($G$2=G$2,INDEX(A:A,SMALL(IF(INT($B$2:$B$5/5)+1>=COLUMN($A:$Z),ROW($2:$5),6),ROW(1:1))),IF(F3=F4,5,MOD(INDEX(A:A,SMALL(IF(INT($B$2:$B$5/5)+1>=COLUMN($A:$Z),ROW($2:$5),6),ROW(1:1))),5)))