excel根据满足两个或者两个以上的条件，从而得出结果
其中 表一中数值20,19，，，，，1下面对应的有填字母的都是该数字分别属于H,O,P,E,Z,0,1,2的属性（可以理解为H，O，P,E,Z,0,1,2都有两个属性。）。
表2为，通过填充 H，O,P,E,Z,0,1,2的属性逆向推理出20,19,，，，1，填充到表2结果里。条件为，H O P E Z 0 1 2,中两个或者两个以上（最多全部）。请教大神！
1.先设置补助单元格,在C21输入公式=IF(AND($X3="",$Y3="",$Z3="",$AA3="",$AB3="",$AC3="",$AD3="",$AE3=""),"",IF(SUMPRODUCT((IF($X3="","",C$3&C$4)=$X3)*(IF($Y3="","",C$5&C$6)=$Y3)*(IF($Z3="","",C$7&C$8)=$Z3)*(IF($AA3="","",C$9&C$10)=$AA3)*(IF($AB3="","",C$11&C$12)=$AB3)*(IF($AC3="","",C$13&C$14)=$AC3)*(IF($AD3="","",C$15&C$16)=$AD3)*(IF($AE3="","",C$17&C$18)=$AE3))=1,C$2,""))左拉至V21.再在W21输入公式=C21&IF(D21="","",","&D21)&IF(E21="","",","&E21)&IF(F21="","",","&F21)&IF(G21="","",","&G21)&IF(H21="","",","&H21)&IF(I21="","",","&I21)&IF(J21="","",","&J21)&IF(K21="","",","&K21)&IF(L21="","",","&L21)&IF(M21="","",","&M21)&IF(N21="","",","&N21)&IF(O21="","",","&O21)&IF(P21="","",","&P21)&IF(Q21="","",","&Q21)&IF(R21="","",","&R21)&IF(S21="","",","&S21)&IF(T21="","",","&T21)&IF(U21="","",","&U21)&IF(V21="","",","&V21)然后选定C21:W21下拉至28行,补助单元格设置完成. 2.在AF3输入公式=IF(LEFT(W21,1)=",",MID(W21,FIND(",",W21)+1,99),W21)下拉复制即可.
温馨提示：答案为网友推荐，仅供参考
第1个回答 2016-12-18
举例说明下 AF3=20，是怎么来的？追问
A30=20，表示，在表1中，20在H，O，P，E。Z。0。1。2,这个七个参数下的对应的值分别是S,F,S,,F,Z2,，跟0，1，没有关系。通过逆向推理得出20,。HOPEZ012分别有两个属性，其中有个特例，就是12,在E中同时拥有SF两个属性。我把表1修改了一下。可能更容易看
S,F,S,,F,Z2，是怎么根据表1，得出结果是20的？就这个没有理解你意思
追问我在表2中HOPEZ012下面分别填入S，F，S，S,F,Z2，那么对比表1中HOPEZ012这个八个参数对应的值，如果同时满足是S,F,S,S,F,Z2，从而得出20。20，19,18。。。。。。。1就是20个数字，所处的每一列就是这些数字在HOPEZ012的属性。
第2个回答 2016-12-18
要提供文件，邮箱看私信。
相似回答