excel请高手修改最大值对应的数公式

excel请高手修改最大值对应的数公式

不知道你为什么会在原先公式尾巴加N4的判断。
=MId(SUBSTITUTE(SUBSTITUTE(","&TEXT(SUM(IF((C4:H4>=LARGE(C4:N4,4))*(C4:H4>0),C$3:H$3*10^{10,8,6,4,2,0})),REPT("00,",6)&";")&LEFt(TEXT(SUM(IF((I4:N4>=LARGE(C4:N4,4))*(I4:N4>0),I$3:N$3*10^{10,8,6,4,2,0})),REPT("00,",6)&";"),14),",00",),",0",","),2,99)追问原先公式尾巴加N4，N列数据才不会漏掉，比如第九行应显示1,5,12,第十行应是2,4,5,12。再者如果第五行改成8,8,6,6,12,8,8,8,12,8,8,8结果显示1,2,3,4,5,6,7,8,9,10,11,12如果这样1到12全部显示，能否做到不显示，而空白出现。追答原先是5个一组，你改为6个一组了，所以应该把14改为17，就不会丢了N列。
 
8,8,6,6,12,8,8,8,12,8,8,8——返回的没有3和4。
如果你的Excel版本是2003的，超过嵌套，需定义名称。
如果是高版本，可以直接加判断：
=IF((COUNTIF(C4:N4,">="&LARGE(C4:N4,4))=12)*(LARGE(C4:N4,4)>0),"",MId(SUBSTITUTE(SUBSTITUTE(","&TEXT(SUM(IF((C4:H4>=LARGE(C4:N4,4))*(C4:H4>0),C$3:H$3*10^{10,8,6,4,2,0})),REPT("00,",6)&";")&LEFt(TEXT(SUM(IF((I4:N4>=LARGE(C4:N4,4))*(I4:N4>0),I$3:N$3*10^{10,8,6,4,2,0})),REPT("00,",6)&";"),17),",00",),",0",","),2,99))

=MId(SUBSTITUTE(SUBSTITUTE(","&TEXT(SUM(IF(C4:H4<>0,C$3:H$3*10^{10,8,6,4,2,0})),REPT("00\,",6)&";")&LEFt(TEXT(SUM(IF(I4:N4<>0,I$3:N$3*10^{10,8,6,4,2,0})),REPT("00\,",6)&";"),14),",00",),",0",","),2,99)&IF(N4<>0,",12","")

=SUBSTITUTE(SUBSTITUTE(","&TEXT(SUM(IF((C4:K4>=LARGE(C4:N4,4))*(C4:K4<>0),C$3:K$3*10^(11-COLUMN(C1:K1)))),REPT("0\,",9)&";")&IF((L4>=LARGE(C4:N4,4))*(L4<>0),10,0),",0",""),",","",1)&IF((M4>=LARGE(C4:N4,4))*(M4<>0),",11","")&IF((N4>=LARGE(C4:N4,4))*(N4<>0),",12","")