本帖最后由 xk465592765 于 2015-11-8 17:00 编辑
开贴后参考其他同学的!!!
汇总如下- 第一题
- =VLOOKUP(A3&"*",数据源!A:C,3,)
- =IF(FIND(A3,数据源!A2)=1,数据源!C2)
- =INDEX(数据源!$$$$C$$$$2:$$$$C$$$$20,MATCH(A3&"*",数据源!$$$$A$$$$2:$$$$A$$$$20,))
- =INDIRECT("数据源!R"&MATCH(A3,LEFT(数据源!A$$$$2:A$$$$20,7),)+1&"C3",)
- =VLOOKUP(Sheet1!A3,IF({1,0},LEFT(数据源!A2:A20,7),LEFT(数据源!C2:C20,2)),2,)
- 第二题
- =VLOOKUP($$$$A15,成绩表!$$$$A:$$$$D,IF(B$$$$14="语文",2,4),)
- =VLOOKUP($$$$A15,成绩表!$$$$A$$$$2:$$$$D$$$$7,2*COLUMN(B1)-2,0)
- =SUMIF(成绩表!$$$$A$$$$2:$$$$A$$$$7,$$$$A15,IF(B$$$$14="语文",成绩表!$$$$B$$$$2,成绩表!$$$$D$$$$2))
- =OFFSET(成绩表!$$$$B$$$$1,MATCH($$$$A15,成绩表!$$$$A$$$$2:$$$$A$$$$7,),(B$$$$14="英语")*2)
- =VLOOKUP($$$$A15,成绩表!$$$$A:$$$$D,MATCH(Sheet1!B$$$$14,成绩表!$$$$A$$$$1:$$$$D$$$$1,),)
- =SUMPRODUCT((成绩表!$$$$A$$$$2:$$$$A$$$$7=$$$$A15)*(成绩表!$$$$B$$$$1:$$$$D$$$$1=B$$$$14)*(成绩表!$$$$B$$$$2:$$$$D$$$$7))
- =OFFSET(成绩表!$$$$A$$$$1,MATCH($$$$A15,成绩表!$$$$A$$$$2:$$$$A$$$$7,),MATCH(B$$$$14,成绩表!$$$$B$$$$1:$$$$D$$$$1,))
- =LOOKUP(1,0/(成绩表!$$$$A$$$$2:$$$$A$$$$7=$$$$A15),IF(B$$$$14="语文",成绩表!$$$$B$$$$2:$$$$B$$$$7,成绩表!$$$$D$$$$2:$$$$D$$$$7))
- 第三题
- =INDEX(成绩表!A:A,MATCH(A24,成绩表!C:C,))
- =OFFSET(成绩表!$$$$A$$$$1,MATCH(A24,成绩表!C:C,)-1,)
- =LOOKUP(1,0/(成绩表!C$$$$2:C$$$$7=A24),成绩表!A$$$$2:A$$$$7)
- =VLOOKUP(A24,IF({0,1},成绩表!$$$$A$$$$2:$$$$A$$$$7,成绩表!$$$$C$$$$2:$$$$C$$$$7),2,)
- 第四题
- =DMAX(A35:C47,3,E36:F37)
- =MAX(IF(A36:A47&B36:B47=E37&F37,C36:C47))
- =MAX((A36:A47=E37)*(B36:B47=F37)*(C36:C47))
- =MAX(IF((A36:A47=E37)*(B36:B47=F37),C36:C47,))
- 第五题
- =LEFT(A59,FIND(1,A59)-1)
- =MID(A59,1,FIND("1",A59)-1)
- =LEFT(A59,MIN(FIND(ROW($$$$1:$$$$9)-1,A59&1/17))-1)
- =LEFT(A59,LEN(A59)-COUNT(--MID(A59,ROW($$$$1:$$$$9),1)))
- =MID(A59,1,MIN(IFERROR(FIND(ROW($$$$1:$$$$10)-1,A59),99))-1)
- =LEFT(A59,COUNT(N(INDIRECT(MID(A59,ROW($$$$1:$$$$98),1)&1))))
- =SUBSTITUTE(A59,LOOKUP(9E+307,--RIGHT(A59,ROW($$$$1:$$$$9))),)
- =LEFT(A59,MATCH(TRUE,ISNUMBER(1*MID(A59,ROW($$$$1:$$$$9),1)),0)-1
- =IFERROR(LEFT(A59,LEN(A59)-COUNT(--RIGHT(A59,ROW($$$$1:$$$$9)))),"")
- =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A59,1,),2,),3,),4,),5,),6,),7,),8,),9,),0,)
复制代码 |