本帖最后由 hsl215 于 2014-10-21 23:53 编辑
1、- =VLOOKUP(C10,B3:H7,MATCH(C9,B2:H2,),)
- =HLOOKUP(C9,C2:H7,MATCH(C10,B2:B7,),)
- =SUMPRODUCT((C10=B3:B7)*(C2:H2=C9)*C3:H7)
- =OFFSET(B2,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))
- =INDEX(C3:H7,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))
- =INDIRECT(CHAR(MATCH(C9,C2:H2,)+66)&MATCH(C10,B1:B7,))
复制代码 2、辅助列在I16:J26不用辅助列- =LOOKUP(C29%%/5,{0;0.2;1;2;3;4;5;6;7;8},{0;0.2;0.4;1;2;3;4;5;6;7})*5%
- =LOOKUP(C29%%/5,{0,0;0.2,1;1,2;2,5;3,10;4,15;5,20;6,25;7,30;8,35})%
复制代码 3、- =LOOKUP("座",MID(B37,1,FIND({"K","M","V"},B37)-1))
复制代码 通用公式- =LEFT(B37,MIN(IF(ISERR(FIND(CHAR(ROW($65:$90)),B37)),99,FIND(CHAR(ROW($65:$90)),B37)))-1)
复制代码 偷来的- =LEFT(B37,COUNT(-LEFT(B37,ROW($1:$99))))
复制代码 4、- =B46/LOOKUP(9^9,C$46:C46)
复制代码 5、- =LOOKUP(MAX((C56:C63=B67)*B56:B63),B56:D63)
- =VLOOKUP(MAX((C56:C63=B67)*B56:B63)&B67,IF({1,0},B56:B63&C56:C63,D56:D63),2,)
- =LOOKUP(,0/(MAX((C56:C64=B68)*B56:B64)&B68=B56:B64&C56:C64),D56:D64)
复制代码 |