[已解决]用函数实现填充

superle!

P3:R3用函数实现，参照G3:O3区域里，固定的文字为一、二、三、四、五、六、七、八、九、十、十一、十二,前面的九个文字随便排列，剩下的三个文字在P3：R3里的补全。

最佳答案

月排行榜 / 总排行榜

心正意诚身修

2016-3-25 10:12

superle! 发表于 2016-3-25 09:49
我试了一下，=IF(ISBLANK(C3),"",
还是没有效果，你就再辛苦下，给个完整的函数代码吧。谢谢了

=IF(COUNTA($G$3:$O$3)=0,"",LOOKUP(1,0/(COUNTIF($G3:O3,{1,"一","二","三","四","五","六","七","八","九","十","甲","乙"})=0),{"","一","二","三","四","五","六","七","八","九","十","甲","乙"}))
可以的

跳转到最佳答案楼层

心正意诚身修

=SUBSTITUTE(TEXT(SMALL(IF(ISERROR(MATCH({"一";"二";"三";"四";"五";"六";"七";"八";"九";"十";"十一";"十二"},$G$3:$O$3,)),ROW($A$1:$A$12)),COLUMN(A1)),"[DBNUM1]"),"一十","十")
見附件．
數組

superle!

心正意诚身修 发表于 2016-3-24 14:53
=SUBSTITUTE(TEXT(SMALL(IF(ISERROR(MATCH({"一";"二";"三";"四";"五";"六";"七";"八";"九";"十";"十一";"十 ...

如果固定的文字不是一到十二呢？函数返回的值必须是缺少的十二个文字里的文字

心正意诚身修

=MID("一二三四五六七八九十甲乙",SMALL(IF(ISERROR(MATCH({"一","二","三","四","五","六","七","八","九","十","甲","乙"},$G3:$O3,)),COLUMN($A$1:$L$12)),COLUMN(A1)),1)

心正意诚身修

你再等等．．我記得應該有更好的解法．．這個解法．．確實有點拙劣．呵呵．

心正意诚身修

=LOOKUP(1,0/(COUNTIF($G3:O3,{1,"一","二","三","四","五","六","七","八","九","十","甲","乙"})=0),{"","一","二","三","四","五","六","七","八","九","十","甲","乙"})．．想起以前見過這個解法，也是找沒有的那個．．．借鑒一下．

superle!

心正意诚身修 发表于 2016-3-24 16:28
=LOOKUP(1,0/(COUNTIF($G3:O3,{1,"一","二","三","四","五","六","七","八","九","十","甲","乙"})=0),{"", ...

函数填充出来的为什么甲先填充？

心正意诚身修

superle! 发表于 2016-3-24 16:58
函数填充出来的为什么甲先填充？

你可以把那裡面的常量數組裡的順序倒過來一下就好．

superle!

心正意诚身修 发表于 2016-3-24 17:07
你可以把那裡面的常量數組裡的順序倒過來一下就好．

恩恩，顺序没有问题了。现在就是数据源如果没有的话，得返回函数空值才行。

李建军

1. =IF(COUNTA($G3:$O3),MID("一二三四五六七八九十甲已",SMALL(IF(COUNTIF($G3:$O3,{"一","二","三","四","五","六","七","八","九","十","甲","已"}),18,COLUMN($A:$L)),COLUMN(A1)),1),"")

复制代码