【函中201401】第8讲D组作业上交贴

danny-fei

1、作业以附件方式提交，直接贴公式均可。
2、跟帖不要重复占楼,要修改直接在原楼层编辑，不要回复学委的批改;本帖已设置回帖仅作者可见；
3、非本组学员请勿跟帖，否者扣分删帖处理；
4、作业截止时间：2014年10月22日晚8:00，过期不予批改

lmze2000

第1题：

1. =VLOOKUP(C10,B2:H7,MATCH(C9,B2:H2,),)

复制代码

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

复制代码

1. =SUMPRODUCT((B3:B7=C10)*(C2:H2=C9)*C3:H7)

复制代码

1. =SUM((B3:B7=C10)*(C2:H2=C9)*C3:H7)

复制代码

第2题：

1. =LOOKUP(C29,{0,1,5,10,15,20,25,30,35,40}/1%%,{0,1,2,5,10,15,20,25,30,35}%)

复制代码

第3题：

1. =LEFT(B37,MIN(IFERROR(FIND(CHAR(ROW($65:$90)),B37),999))-1)

复制代码

1. =LEFT(B37,MATCH("王",IFERROR(--MID(B37,ROW($1:$99),1),"王"),)-1)

复制代码

1. =LEFT(B37,MATCH(,-(CODE(MID(B37,ROW($1:$99),1))<58),)-1)

复制代码

第4题：

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

复制代码

1. =B46/LOOKUP(1,0/($C$46:C46<>""),$C$46:C46)

复制代码

1. =B46/INDEX(C$46:C$52,MATCH(9^9,C$46:C46))

复制代码

第5题：

1. =LOOKUP(1,0/(B67=C56:C63),D56:D63)

复制代码

1. =INDEX(D56:D63,MAX((C56:C63=B67)*ROW(1:8)))

复制代码

Tank008

1、方法1：=INDIRECT("r"&MATCH(C10,B:B,)&"c"&MATCH(C9,2:2,),)
     方法2：=OFFSET(B2,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))

2、辅助区数据分段如下图所示：黄色区域为辅助分段数据

公式：=VLOOKUP(C29,E$16:F$26,2,1)

3、方法1：=LEFT(B37,COUNT(-LEFT(B37,ROW(INDIRECT("1:"&LEN(B37))))))
     方法2：=TEXT(-LOOKUP(1,-LEFT(B37,ROW(INDIRECT("1:"&LEN(B37))))),REPT("0",COUNT(-LEFT(B37,ROW(INDIRECT("1:"&LEN(B37)))))))

4、=TEXT(B46/LOOKUP(9^99,C$46:C46),"0.00")

5、
方法1：=LOOKUP(1,0/((C56:C63=B67)*ROW(56:63)=MAX((C56:C63=B67)*ROW(56:63))),D56:D63)
方法2：=LOOKUP(1,0/(C56:C63=B67),D56:D63)
方法3：=VLOOKUP(MAX((C56:C63=B67)*ROW(56:63)),IF({1,0},(C56:C63=B67)*ROW(56:63),D56:D63),2,)
方法4：=OFFSET(D55,MATCH(MAX((C56:C63=B67)*ROW(56:63)),(C56:C63=B67)*ROW(56:63),),)
方法5：=INDEX(D56:D63,MATCH(MAX((C56:C63=B67)*ROW(56:63)),(C56:C63=B67)*ROW(56:63),))

木偶fenzi

交作业

1. =INDEX(C3:H7,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))

复制代码

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

复制代码

2
辅助列的

0	0%
10000	1%
50000	2%
100000	5%
150000	10%
200000	15%
250000	20%
300000	25%
350000	30%
400000	35%

1. =VLOOKUP(C29,H$17:I$26,2)

复制代码

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

复制代码

不加辅助的

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

复制代码

1. 
   

复制代码

3
数组

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

复制代码

这个会去掉0

1. =-LOOKUP(,-LEFT(B37,ROW($1:$9)))

复制代码

4

1. =TEXT(B46/LOOKUP(99,C$46:C46),"0.00")

复制代码

1. =B46/VLOOKUP(99,C$46:C46,1)

复制代码

5

1. =LOOKUP(1,0/(C56:C63=B67),D56:D63)

复制代码

《渐行渐远》

1. 1.=INDEX(B2:H7,MATCH(C10,B2:B7,),MATCH(C9,B2:H2,))
   
2. =VLOOKUP(C10,B3:H7,MATCH(C9,B2:H2,),)
   
3. =HLOOKUP(C9,C2:H7,MATCH(C10,B2:B7,),)
   
4. 2.=LOOKUP(C29/10000,{0,0;1,0.01;5,0.02;10,0.05;15,0.1;20,0.15;25,0.2;30,0.25;35,0.3;40,0.35})
   
5. 3.=LEFT(B37,MATCH(TRUE,CODE(MID(B37,ROW($1:$99),1))>57,)-1)
   
6. 4.=B46/LOOKUP(9^9,$C$46:C46)
   
7. 5.=INDEX(D:D,MAX(IF(B67=C56:C63,ROW(C56:C63))))
   
8. =VLOOKUP(MAX(IF(C56:C63=B67,B56:B63)),B56:D63,3,)
   

复制代码

天棋

第一题：=OFFSET(B2,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,),,)
第二题：=TEXT(LOOKUP(C29,{0;10000;50000;100000;150000;200000;250000;300000;350000;400000},{0;0.01;0.02;0.05;0.1;0.15;0.2;0.25;0.3;0.35}),"0%")
第三题：=-LOOKUP(,-LEFT(B37,ROW($1:$20)))
第四题：=B46/LOOKUP(9^9,$C$46:C46)
第五题：=OFFSET(D55,LARGE(IF(C56:C63=B67,ROW(1:8),0),1),,,)

tgy0451

请审核……

yuan1987

1、=SUMPRODUCT(((B3:B7=C10)*(C2:H2=C9))*(C3:H7))
2、不会
3、=RIGHT(-LOOKUP(1,-LEFT(B37,COLUMN(37:37))),1)
4、不会
5、=INDEX(D56:D63,MATCH(SMALL(IF(C56:C63=B67,B56:B63),COUNTIF(C56:C63,B67)),IF(C56:C63=B67,B56:B63)),1)

C.Deny

函中学员D08:C.Deny上交第八讲作业
1.=OFFSET(B2,MATCH(C10,B3:B7,0),MATCH(C9,C2:H2,0))
   =INDEX(C3:H7,MATCH(C10,B3:B7,0),MATCH(C9,C2:H2,0))
   =INDIRECT("r"&MATCH(C10,B1:B7,0)&"c"&MATCH(C9,A2:H2,0),0)  
   =LOOKUP(1,0/(B3:B7=C10),OFFSET(B3:B7,,MATCH(C9,C2:H2,0)))
   =SUMIF(B2:B7,C10,OFFSET(B2:B7,,MATCH(C9,C2:H2,0),))
  =SUMIF(B2:H2,C9,OFFSET(B2:H2,MATCH(C10,B3:B7,0),,))
    =SUMPRODUCT((C2:H2&B3:B7=C9&C10)*C3:H7)
   =SUM((C2:H2=C9)*(B3:B7=C10)*C3:H7)数组
   =VLOOKUP(C10,B3:H7,MATCH(C9,B2:H2,0),0)
  =HLOOKUP(C9,B2:H7,MATCH(C10,B3:B7,0)+1,0)
2.=LOOKUP(C29,{0,1,5,10,15,20,25,30,35,40;0,1,2,5,10,15,20,25,30,35}*{10000;0.01})下拉，单元格格式调成百分比
3.=LEFT(B37,COUNT(-LEFT(B37,ROW($1:$99))))数组
4.=ROUND(B46/LOOKUP(9^9,$C$46:C46),2)下拉
5.时间按升序：=LOOKUP(1,0/(C56:C63=B67),D56:D63)
时间乱序：=INDEX(D56:D63,MATCH(MAX(IF(C56:C63=B67,B56:B63)),IF(C56:C63=B67,B56:B63),))数组

李建军

1，=INDEX(C3:H7,MATCH(C10,B3:B7,),MATCH(C9,C2:H2,))
2，=LOOKUP(C29,{0,10000,50000,100000,150000,200000,250000,300000,350000,400000},$F$17:$F$26)
3，=MID(-LOOKUP(,-LEFT(1&B37,ROW($1:$99))),2,99)
4，=B46/LOOKUP(9E+307,$C$46:C46)
5，=INDEX(D56:D63,MATCH(MAX((C56:C63=B67)*B56:B63),B56:B63,))