[201401函中]E组第八讲作业提交贴

26759761@qq.com

1、作业统一贴公式(需要过程的直接录屏).
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、评分标准:答案正确+15/+15,个别优秀的+1-10分.不贴公式直接上附件的,评分标准上,经验BB各扣5分.即+10/+10

瑾瑄

1=VLOOKUP(C10,B2:H7,MATCH(C9,B2:H2,),)
  =HLOOKUP(C9,C2:H7,MATCH(C10,B2:B7,),)
=INDEX(B2:H7,MATCH(C10,B2:B7,),MATCH(C9,B2:H2,))
=INDIRECT(ADDRESS(MATCH(C10,B2:B7,)+1,MATCH(C9,B2:H2,)+1,))
=OFFSET(B2,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))
=SUMPRODUCT((C9=C2:H2)*(B3:B7=C10),C3:H7)
2.=LOOKUP(C29/10000,{0;1;5;10;15;20;25;30;35;40},F$17:F$26)
3.=LEFT(B37,MATCH(1,(ISNUMBER(--MID(B37,ROW($1:$99),1))+1)/1,)-1)
4.=B46/LOOKUP(99,C$46:C46)
5.=LOOKUP(1,0/(C56:C63=B67),D56:D63)

yqnh

1. 1、=VLOOKUP(C10,B2:H7,MATCH(C9,B2:H2,),)
   
2. 2、=LOOKUP(C29,{0,0;10000,0.01;50000,0.02;100000,0.05;150000,0.1;200000,0.15;250000,0.2;300000,0.25;350000,0.3;400000,0.35})
   
3. 3、=LEFT(B37,COUNT(-LEFT(B37,ROW($1:$99))))（数组）
   
4. 4、=ROUND(B46/LOOKUP(9^9,$C$46:C46),2)
   
5. 5、=LOOKUP(1,0/(C56:C63=B67),B56:B63)

复制代码

XMergeD

1

1. =OFFSET(B2,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))

复制代码

2

1. =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

1. =MID(-LOOKUP(,-LEFT(1&B37,ROW($1:$99))),2,99)

复制代码

4

1. =B46/LOOKUP(ROW(),IF(C$46:C$52,ROW(C$46:C$52)),C$46:C$52)

复制代码

5

1. =LOOKUP(9E+307,IF(C56:C63=B67,B56:B63),D56:D63)

复制代码

一杯清荼

题一:
       =VLOOKUP(C10,B2:H7,MATCH(C9,B2:H2,),)
       =HLOOKUP(C9,B2:H7,MATCH(C10,B2:B8,),)
       =INDEX(C2:H7,MATCH(C10,B2:B7,),MATCH(C9,C2:H2,))
       =SUM((B3:B7=C10)*(C2:H2=C9)*C3:H7)
       =SUM((B3:B7&C2:H2=C10&C9)*C3:H7)
题二：
      =LOOKUP(C29,--(0&MID(C$17:C$26,FIND("含",C$17:C$26&"含")+1,FIND("元",C$17:C$26)-1)),F$17:F$26)
      =LOOKUP(C29,--TEXT((ROW($1:$10)-2)*5,"0;!0;1")/1%%,F$17:F$26)
题三:
     =LEFT(B37,MATCH(1=1,MID(B37,ROW(A:A),1)>"9",)-1)
     =LEFT(B37,COUNT(-LEFT(B37,ROW(A:A))))
     =MID(LOOKUP(,-LEFT(1&B37,ROW(A:A))),3,9)
题四:
     =B46/LOOKUP(9^9,C$46:C46)

题五:
    =INDIRECT("D"&MATCH(MAX((B67=C56:C63)*B56:B63),B56:B63,)+55)
    =VLOOKUP(MAX((B67=C56:C63)*B56:B63),B56:D63,3,)
根据数据源取巧:=LOOKUP(1,0/(C56:C63=B67),D56:D63)

sinowonder

1.=VLOOKUP(C10,B3:H7,MATCH(C9,B2:H2,),)
2.=LOOKUP(C29,$E$17:$F$26)
  =IFERROR(--LEFT(C17,FIND("元",C17)-1),0)
3.=LEFT(B37,LOOKUP(9^9,FIND({"K";"M";"V"},B37))-1)
  {=LEFT(B37,SMALL(IFERROR(FIND(CHAR(ROW($65:$90)),B37),9^9),1)-1)}
4.=B46/LOOKUP(9^9,$C$46:C46)
5.=LOOKUP(1,0/(C56:C63=B67),D56:D63)

meililin

第1题

1. =OFFSET($B$2,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,),1,1)

复制代码

1. =VLOOKUP(C10,B3:H7,MATCH(C9,C2:H2,)+1,)

复制代码

第2题（$G$17:$G$26为辅助区域{0;10000;50000;100000;150000;200000;250000;300000;350000;400000}）

1. =LOOKUP(C29,$G$17:$G$26,$F$17:$F$26)

复制代码

1. =INDEX($F$17:$F$26,MATCH(C29,$G$17:$G$26,1))

复制代码

第3题

1. =LEFT(B37,MATCH(1,--ISERROR(--MID(B37,ROW($1:$20),1)),)-1)

复制代码

第4题

1. =B46/LOOKUP(9^9,$C$46:C46)

复制代码

第5题

1. =OFFSET(D55,MAX(IF(C56:C63=B67,ROW(1:8))),,)

复制代码

hsl215

1、

1. =VLOOKUP(C10,B3:H7,MATCH(C9,B2:H2,),)
   
2. =HLOOKUP(C9,C2:H7,MATCH(C10,B2:B7,),)
   
3. =SUMPRODUCT((C10=B3:B7)*(C2:H2=C9)*C3:H7)
   
4. =OFFSET(B2,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))
   
5. =INDEX(C3:H7,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))
   
6. =INDIRECT(CHAR(MATCH(C9,C2:H2,)+66)&MATCH(C10,B1:B7,))

复制代码

2、辅助列在I16:J26

1. =LOOKUP(C29,I$17:J$26)

复制代码

不用辅助列

1. =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%
   
2. =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、

1. =LOOKUP("座",MID(B37,1,FIND({"K","M","V"},B37)-1))

复制代码

通用公式

1. =LEFT(B37,MIN(IF(ISERR(FIND(CHAR(ROW($65:$90)),B37)),99,FIND(CHAR(ROW($65:$90)),B37)))-1)

复制代码

偷来的

1. =LEFT(B37,COUNT(-LEFT(B37,ROW($1:$99))))

复制代码

4、

1. =B46/LOOKUP(9^9,C$46:C46)

复制代码

5、

1. =LOOKUP(MAX((C56:C63=B67)*B56:B63),B56:D63)
   
2. =VLOOKUP(MAX((C56:C63=B67)*B56:B63)&B67,IF({1,0},B56:B63&C56:C63,D56:D63),2,)
   
3. =LOOKUP(,0/(MAX((C56:C64=B68)*B56:B64)&B68=B56:B64&C56:C64),D56:D64)

复制代码

ANDY0393

第一题：

1. =INDEX(C3:H8,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))
   
2. =VLOOKUP(C10,IF({1,0},B3:B7,INDEX(C3:H7,,MATCH(C9,C2:H2,))),2,)
   
3. =VLOOKUP(C10,B3:H7,MATCH(C9,B2:H2,),)
   
4. 数组公式：=MAX((C2:H2=C9)*C3:H8*(B3:B8=C10))

复制代码

第二题：

1. 数组公式：=LOOKUP(C29,{0;1;5;10;15;20;25;30;35;40}/1%%,F$17:F$26)
   
2. =LOOKUP(C29,--(0&(SUBSTITUTE(MID(C$17:C$26,IF(ISNUMBER(FIND("含",C$17:C$26)),FIND("含",C$17:C$26)+1,99),6),"元",))),F$17:F$26)

复制代码

第三题：

1. 数组公式：
   
2. =IF(-LEFT(B37)=0,0,"")&-LOOKUP(,-LEFT(B37,COLUMN(A:CV)))
   
3. =LEFT(B37,MIN(IF(ISNUMBER(FIND(CHAR(64+COLUMN(A:Z)),B37)),FIND(CHAR(64+COLUMN(A:Z)),B37)))-1)

复制代码

第四题：

1. =ROUND(B46/LOOKUP(99,C$46:C46),2)
   
2. 数组公式：=FIXED(B46:B53/LOOKUP(ROW(1:7),ROW(1:7)/(C46:C52<>""),C46:C52))

复制代码

第五题：

1. 数组公式=MOD(MAX((C56:C63=B67)*(D56:D63+ROW(1:8)*10^6)),10^6)

复制代码

天龙九部

1. INDEX(C3:H7,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))
   
2. IFERROR(VLOOKUP(C29,IF({1,0},--LEFT($$C$$17:$$D$$26,FIND("元",$$C$$17:$$D$$26)-1),$$F$$17:$$F$$26),2),)
   
3. MID(B37,1,MATCH(,N(INDIRECT(MID(B37,ROW($$1:$$99),1)&1)),)-1)
   
4. ROUND(B46/LOOKUP(9^9,$$C$$46:C46),2)
   
5. INDEX(D56:D63,MATCH(MAX((C56:C63=B67)*B56:B63),B56:B63*(C56:C63=B67),))

复制代码