请excel大神帮我解题

显示全部楼层 · 2012-11-20 21:57:59

1.E列用公式=IF(LEFT(A:A,3)="101","数学",IF(LEFT(A:A,3)="102","物理",IF(LEFT(A:A,3)="103","化学","")))2.公式=COUNTIF(A3:A32,"<41")3.公式=SUMIF(F3:F32,"=讲师",G3:G32)...

千问 · 2012-11-20 21:57:59

E2=INDEX({"数学系","物理系","化学系"},MATCH(LEFT(A2,3),{"101","102","103"},0))下拉J1=COUNTIF(D2:D32,"<=40")J2=SUMPRODUCT((F2:F32="讲师")*G2:G32)...

千问 · 2012-11-20 21:57:59

E2公式下拉！=CHOOSE(MID(A3,3,1),"数学系","物理系","化学系") J1公式：=COUNTIF(D3:D32,"<=40") J2公式：=SUMIF(F3:F32,"讲师",G3)...

千问 · 2012-11-20 21:57:59

E3=LOOKUP(--LEFT(A3,3),{101,"数学系";102,"物理系";103,"化学系"})J1=COUNTIF(D3:D32,"<40")J2=SUMIF(F3:F32,"讲师",G3)...