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

Book1.rar (6.98 KB) 详细要求在附件中

给出了一个解法，请参考

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})),"","大")

数组 下拉

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

=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})),"","大"))

,非常感谢piny

=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)

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 编辑 ]

=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 编辑 ]

=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))

看得眼花缭乱

呵 純公式精進研究 您挑前面幾樓順眼的使用即可 ^^

伤脑筋的题,我就直接用替换了:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("小中大",TEXT(B2-4,"[>2]大;小;中"),),TEXT(C2-4,"[>2]大;小;中"),),TEXT(D2-4,"[>2]大;小;中"),)

104字的,可能还不算最简短的:
=IF(COUNTIF(B2:D2,">6"),,"大")&IF(COUNTIF(B2:D2,"<4"),,"小")&IF(SUM(COUNTIF(B2:D2,">"&{3,6})*{1,-1}),,"中")

见4楼，要求变更。
目前还是我的最短，见9楼。下拉遇到空的单元格，显示为空。
哈哈。

回復版主 此題稍微需要思考的是當小中大同時存在時 題意需將其轉換為「無」

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

89字:
=IF(COUNTIF(B2:D2,">6"),,"大")&IF(COUNTIF(B2:D2,"<4"),,"小")&IF(SUM(-(B2:D2={4;5;6})),,"中")
93字:
=IF(COUNTIF(B2:D2,">6"),,"大")&IF(COUNTIF(B2:D2,"<4"),,"小")&IF(SUM(SUMIF(B2:D2,{4;5;6})),,"中")

追平大頭老師最短紀錄 105字元 ^^ (8樓揭露簡化思緒)

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

如果总有一种没出的话  可用这个
=SUBSTITUTE(SUBSTITUTE("大中小",LOOKUP(MAX(B4:D4),{0,4,7},{"小","中","大"}),""),LOOKUP(MIN(B4:D4),{0,4,7},{"小","中","大"}),"")

您的LEFT改為INT，可再少一

以我的思維，好像無法再精簡了

=TRIM(MID("无 小 中 小中大 小大中大",SUM((MMULT(COUNTIF(B2:D2,">"&{-1,3;3,6;6,9}),{1;-1})=0)*{2;4;8})+1,2))

巧思 ^^

將近一年的構思 公式可短於90字元 不知有無任何高手仍有興趣簡化 ^^