[已解决]函数公式求助

yorkchenshunan

请各位高手帮忙，详见附件。谢谢

最佳答案

月排行榜 / 总排行榜

26759761@qq.com

2014-8-12 15:14

H3 =   三键结束

1. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUM(COUNTIF(B3:G3,CHAR(ROW($1:$5)+64))*10^(5-ROW($1:$5))),"0\A0\B0\C0\D0\E"),"0A",),"0B",),"0C",),"0D",),"0E",)

复制代码

跳转到最佳答案楼层

tgydslr

H3=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUM(COUNTIF(B3:G3,{"A","B","C","D","E"})*10^{4,3,2,1,0}),"0A0!B0C0!D0!E"),"0A",),"0B",),"0C",),"0D",),"0E",)三键下拉

蝶·舞

五次COUNTIF+IF就行了

yorkchenshunan

蝶·舞 发表于 2014-8-12 14:47
五次COUNTIF+IF就行了

可否将公式粘贴出来。我目前是用了5次countif求出各档级的次数，然后用concatenate合并文本，然后用“”替换掉0A0B0C0D0E，但是还是感觉操作繁琐

26759761@qq.com

H3 =   三键结束

1. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(SUM(COUNTIF(B3:G3,CHAR(ROW($1:$5)+64))*10^(5-ROW($1:$5))),"0\A0\B0\C0\D0\E"),"0A",),"0B",),"0C",),"0D",),"0E",)

复制代码

蝶·舞

改用TEXT也可以

1. =TEXT(COUNTIF(B3:G3,"A"),"0A;;")&TEXT(COUNTIF(B3:G3,"B"),"0\A;;")&TEXT(COUNTIF(B3:G3,"C"),"0C;;")&TEXT(COUNTIF(B3:G3,"D"),"0\D;;")&TEXT(COUNTIF(B3:G3,"E"),"0\E;;")

复制代码

yorkchenshunan

26759761@qq.com 发表于 2014-8-12 15:14
H3 =   三键结束

谢谢。追问一下substitute嵌套显得很复杂，能否配合通配符，或者or，not等逻辑函数组合使用？

yorkchenshunan

蝶·舞 发表于 2014-8-12 15:17
改用TEXT也可以

嗯，这个很适用。TEXT(COUNTIF(B3:G3,"B"),"0\A;;")应该是笔误吧。谢谢

领风飞翔

向高手们学习！[em01]

26759761@qq.com

yorkchenshunan 发表于 2014-8-12 15:24
谢谢。追问一下substitute嵌套显得很复杂，能否配合通配符，或者or，not等逻辑函数组合使用？

substitute第二个参数用数组的话,结果也是个数组,不是一个值