【公式简化】非空白的数值按条件分别入列

xiao911

设置了一个公式，但是需要辅助项，不知道是否简化公式，直接得出结果，不用辅助的呢？
感谢！！！

chenjx80

这种问题使用 VBA 完成不需要那么费脑

xiao911

chenjx80 发表于 2018-1-20 11:49
这种问题使用 VBA 完成不需要那么费脑

不会VBA，没有学过

hcm19522

I{=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF($C$2:$E$8>0,100+COLUMN($C2:$E8)*10001),ROW(A1)),4),"!R0C00"),),"")

J{=IFERROR(RIGHT(SMALL(IF((C$1:E$1=I2)*(C$2:E$8<>""),ROW(C$2:E$8)*10^9+C$2:E$8),COUNTIF(I$2:I2,I2)),9)*1,"")

K{=IFERROR(INDEX(B:B,SMALL(IF((C$1:E$1=I2)*(C$2:E$8<>""),ROW(C$2:E$8)),COUNTIF(I$2:I2,I2))),"")

xiao911

hcm19522 发表于 2018-1-20 16:39
I{=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF($C$2:$E$8>0,100+COLUMN($C2:$E8)*10001),ROW(A1)),4),"!R0C00") ...

第3个公式数值没显示出来，是不是哪里错了

hcm19522

xiao911 发表于 2018-1-20 16:57
第3个公式数值没显示出来，是不是哪里错了

可以 !
I2:K11{=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF($C$2:$E$8>0,ROW(C$2:E$8)^(COLUMN(A1)>1)/1%+COLUMN($C2:$E8)/1%%+COLUMN($C2:$E8)^(COLUMN(A1)<3)+(COLUMN(A1)=3)),ROW(A1)),4),"!R0C00"),),"")

hcm19522

I列{=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF($C$2:$E$8>0,100+COLUMN($C2:$E8)*10001),ROW(A1)),4),"!R0C00"),),"")

J列{=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF($C$2:$E$8>0,ROW(D$2:F$8)/1%+COLUMN($C2:$E8)*10001),ROW(B1)),4),"!R0C00"),),"")

K列{=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF($C$2:$E$8>0,ROW(E$2:G$8)/1%+COLUMN($C2:$E8)/1%%+2),ROW(C1)),4),"!R0C00"),),"")

一式三列M2:O11{=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF($C$2:$E$8>0,ROW(C$2:E$8)^(COLUMN(A1)>1)/1%+COLUMN($C2:$E8)/1%%+COLUMN($C2:$E8)^(COLUMN(A1)<3)+(COLUMN(A1)=3)),ROW(A1)),4),"!R0C00"),),"")

jivy1212

I2=LOOKUP(ROW(A1)-1,SUBTOTAL(3,OFFSET($B$2:$B$25,,,,COLUMN(A:D)))-COUNTA($B$2:$B$25),$C$1:$E$1)&""    普通

j2=INDIRECT(TEXT(RIGHT(SMALL(IF($C$2:$E$25<>"",ROW($2:$25)*100+COLUMN($C:$E)*10001,7^8),ROW(A1)),4),"R0C00"),)&""   数组


第三列公式可不变，见附件

xiao911

hcm19522 发表于 2018-1-20 17:41
I列{=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF($C$2:$E$8>0,100+COLUMN($C2:$E8)*10001),ROW(A1)),4),"!R0C00 ...

太感谢了，很受用

xiao911

jivy1212 发表于 2018-1-20 21:38
I2=LOOKUP(ROW(A1)-1,SUBTOTAL(3,OFFSET($B$2:$B$25,,,,COLUMN(A:D)))-COUNTA($B$2:$B$25),$C$1:$E$1)&""   ...

谢谢！！！~~