(资料图片仅供参考)

1、设数据如图:将公式 =IF(B2=B1,"",B2) 复制后粘贴到"C2"单元格;将公式 =IF(C2="","",IF(SUM(C2:C8)=C2,A2&","&A3&","&A4&","&A5&","&A6&","&A7&","&A8,IF(SUM(C2:C7)=C2,A2&","&A3&","&A4&","&A5&","&A6&","&A7,IF(SUM(C2:C6)=C2,A2&","&A3&","&A4&","&A5&","&A6,IF(SUM(C2:C5)=C2,A2&","&A3&","&A4&","&A5,IF(SUM(C2:C4)=C2,A2&","&A3&","&A4,IF(SUM(C2:C3)=C2,A2&","&A3,A2))))))) 复制后粘贴到"D2"单元格,将"C2:D2"二个单元格选中,对准"D2"单元格右下角,光标成"+"状时双击(或拖下去),效果如图:注意:一、用此方法必须对"B"列排序;二、如果同一分数从数超过7人,可将公式 =IF(C2="","",IF(SUM(C2:C14)=C2,D2&","&A9&","&A10&","&A11&","&A12&","&A13&","&A14,IF(SUM(C2:C13)=C2,D2&","&A9&","&A10&","&A11&","&A12&","&A13,IF(SUM(C2:C12)=C2,D2&","&A9&","&A10&","&A11&","&A12,IF(SUM(C2:C114)=C2,D2&","&A9&","&A10&","&A11,IF(SUM(C2:C10)=C2,D2&","&A9&","&A10,IF(SUM(C2:C9)=C2,D2&","&A9,"")))))))复制后粘贴到"E2"单元格,将公式双击下去,如图:。

本文到此分享完毕，希望对大家有所帮助。

标签：