请问在已知数据里统计未出数据的问题

newname

Book1.rar (6.98 KB, 下载次数: 40)

2010-8-29 09:10 上传

点击文件名下载附件

详细要求在附件中

sqxlmy

给出了一个解法，请参考

piny

E2=IF(SUM(COUNTIF(B2:D2,{0,1,2,3})),"","小")&IF(SUM(COUNTIF(B2:D2,{4,5,6})),"","中")&IF(SUM(COUNTIF(B2:D2,{7,8,9})),"","大")

数组 下拉

newname

感谢楼上几位,应该是我忘了一个,如果三种都有的按现在的公式显示是空格,能不能加个判断大中小三种形式都有的显示为"无"

piny

=IF(IF(SUM(COUNTIF(B2:D2,{0,1,2,3})),"","小")&IF(SUM(COUNTIF(B2:D2,{4,5,6})),"","中")&IF(SUM(COUNTIF(B2:D2,{7,8,9})),"","大")="","无",IF(SUM(COUNTIF(B2:D2,{0,1,2,3})),"","小")&IF(SUM(COUNTIF(B2:D2,{4,5,6})),"","中")&IF(SUM(COUNTIF(B2:D2,{7,8,9})),"","大"))

newname

,非常感谢piny

wangg913

=MID(TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0大中小",IF(COUNTIF(B2:D2,"<4"),"小"),),IF(COUNTIF(B2:D2,">6"),"大"),),IF(SUM(COUNTIF(B2:D2,{4,5,6})),"中"),),";;0无;@"),2,4)

piny

157字元       
=IF(COUNTIF(B2:D2,"<4"),"","小")&IF(SUM(COUNTIF(B2:D2,{4,5,6})),"","中")&IF(COUNTIF(B2:D2,">6"),IF(COUNTIF(B2:D2,"<4")*SUM(COUNTIF(B2:D2,{4,5,6})),"无",""),"大")

133字元       
=SUBSTITUTE(TEXT(IF(COUNTIF(B2:D2,"<4"),0,"小")&IF(SUM(COUNTIF(B2:D2,{4,5,6})),0,"中")&IF(COUNTIF(B2:D2,">6"),0,"大"),"[=000]无;;;@"),0,)

133字元       
=CHOOSE(MMULT(--ISNUMBER(FIND({1,2,3},SUM(SUBSTITUTE(INT((B2:D2+2)/3),0,1)*10^COLUMN(A:C)))),{1;2;4}),"中大","小大","大","小中","中","小","无")

129字元       
=SUBSTITUTE(TEXT(IF(COUNTIF(B2:D2,"<4"),"","小")&IF(SUM(COUNTIF(B2:D2,{4,5,6})),"","中")&IF(COUNTIF(B2:D2,">6"),0,"大"),";;无;@"),0,)

128字元       
=TRIM(MID("中大小大大 小中中 小 无 ",2*MMULT(--ISNUMBER(FIND({1,2,3},SUM(SUBSTITUTE(INT((B2:D2+2)/3),0,1)*10^COLUMN(A:C)))),{1;2;4})-1,2))

128字元       
=TRIM(MID("中大小大大 小中中 小 无 ",2*MMULT(--ISNUMBER(FIND({1,2,3},MMULT(--SUBSTITUTE(INT((B2:D2+2)/3),0,1),{1;10;100}))),{1;2;4})-1,2))

124字元       
=TRIM(MID("中大小大大 小中中 小 无 ",2*MMULT(--ISNUMBER(FIND({1,2,3},SUM(SUBSTITUTE(INT((B2:D2+2)/3),0,1)*10^{1,2,3}))),{1;2;4})-1,2))

116字元       
=TRIM(MID("无 小 中 小中大 小大中大",2*((COUNTIF(B2:D2,"<4")=0)*1+(SUM(-(B2:D2={4;5;6}))=0)*2+(COUNTIF(B2:D2,">6")=0)*4)+1,2))

116字元       
=TRIM(MID("无 小 中 小中大 小大中大",MMULT(--(FIND({1,2,3},MMULT(INT(IF(B2:D2,B2:D2+2,3)/3),10^{2;1;0})&123)>3),{2;4;8})+1,2))

110字元       
=TRIM(MID("无 小 中 小中大 小大中大",2*(MMULT(--(COUNTIF(B2:D2,{"<4",">6"})=0),{1;4})+(SUM(-(B2:D2={4;5;6}))=0)*2)+1,2))

108字元       
=TRIM(MID("无 小 中 小中大 小大中大",2*MMULT(--(COUNTIF(B2:D2,{"<4",">6"})=0),{1;4})+4*(SUM(-(B2:D2={4;5;6}))=0)+1,2))

106字元       
=TRIM(MID("无 小 中 小中大 小大中大",MMULT(--(COUNTIF(B2:D2,{"<4",">6"})=0),{2;8})+4*(SUM(-(B2:D2={4;5;6}))=0)+1,2))

105字元
=TRIM(MID("无 小 中 小中大 小大中大",MMULT(N(COUNTIF(B2:D2,{"<4",">6"})=0),{2;8})+4*(SUM(-(B2:D2={4;5;6}))=0)+1,2))

[ 本帖最后由 piny 于 2010-8-30 19:20 编辑 ]

wangg913

=TRIM(MID("无 小 中 小中大 小大中大",2*SUM((MMULT(COUNTIF(B2:D2,IF({1,1,1,0},{1,2,3}+{0;3;6},{0;10;11})),{1;1;1;1})=0)*{1;2;4})+1,2))
=TRIM(MID("无 小 中 小中大 小大中大",SUM((MMULT(COUNTIF(B2:D2,MID("<4>3>6a",{1,7;3,5;5,7},2)),{1;-1})=0)*{2;4;8})+1,2))

[ 本帖最后由 wangg913 于 2010-8-30 13:08 编辑 ]

wangg913

=TRIM(MID("无 小 中 小中大 小大中大",SUM((MMULT(-({1;2;3}-LEFT(IF(B2:D2,B2:D2+2,3)/3)=0),{1;1;1})=0)*{2;4;8})+1,2))