第十期【每周一测】查找函数 答题秀

展翅

第十期《每周一测》已经发布，欢迎点击下载：第十期

欢迎各位跟帖分享奇招妙想。论坛会根据答题内容给予一定的金币或经验奖励。

xhrys

前面几期没做，菜鸟来试试，第一题：

1. =INDEX(B19:B26,MATCH(D19,A19:A26,))

复制代码

第二题：
（1）.奖励提点，下拉

1. =VLOOKUP(F16,B$16:C$20,2)

复制代码

（2）.销售额查询

1. =SUMIF(A25:A30,"*"&E25&"*",C25:C30)

复制代码

第三题：

1. =SUMIFS(OFFSET(A15:A26,,MATCH(J16,B$14:G$14,)),A15:A26,">="&J17,A15:A26,"<="&J18)

复制代码

或者

1. =SUMPRODUCT(OFFSET(A15:A26,,MATCH(J16,B$14:G$14,)),(A15:A26>=J17)*(A15:A26<=J18))

复制代码

第四题：

1. =SUMPRODUCT(($A$13:$A$24=$E13)*($B$13:$B$24=F$12)*$C$13:$C$24)

复制代码

或者

1. =SUMIFS($C$13:$C$24,$A$13:$A$24,$E13,$B$13:$B$24,F$12)

复制代码

下拉右拉
第五题：
三键回车，下拉

1. =IF(E$19="","",IF(COUNTIF(A$19:A$24,E$19)>=ROW(A1),INDEX(C$19:C$24,SMALL(IF(A$19:A$24=E$19,ROW($1:$6)),ROW(A1))),""))

复制代码

笨熊猫

第一题

1. =SUMPRODUCT((A19:A26=D19)*B19:B26)
   
2. =OFFSET(A18,MATCH(D19,A19:A26,),1)
   
3. =SUMIF(A19:A26,D19,B19:B26)
   
4. 数组
   
5. =SUM((A19:A26=D19)*B19:B26)
   
6. =SUM(IF(A19:A26=D19,B19:B26,))
   
7. =MAX(IF(A19:A26=D19,B19:B26,))

复制代码

第二题

1. =LOOKUP(F16,B$16:B$20,C$16:C$20)
   
2. =SUMIF(A25:B30,"*"&E25&"*",C25:C30)

复制代码

第三题 无比复杂的数组公式

1. =SUM(N(OFFSET(A14,ROW(INDIRECT("1:"&J18)),MATCH(J16,B14:G14,))))-IF(J17=1,,SUM(N(OFFSET(A14,ROW(INDIRECT("1:"&J17-1)),MATCH(J16,B14:G14,)))))

复制代码

第四题

1. =SUMPRODUCT(($A$13:$A$24&$B$13:$B$24=$E13&F$12)*$C$13:$C$24)

复制代码

第五题

1. =INDEX($C$19:$C$24,SMALL(IF($A$19:$A$24=E$19,ROW($1:$6)),ROW(A1)))

复制代码

tgydslr

huangcaiguang

依然无人挑战25中解法。。。

美斯特邦威

第一题，写几种吧，达到目的即可，要来个25种还真是折腾人

1. =SUM((A19:A26=D19)*B19:B26)
   
2. =SUMPRODUCT((A19:A26=D19)*B19:B26)
   
3. =OFFSET(B18,MATCH(D19,A19:A26,),)
   
4. =SUMIF(A19:A26,D19,B19)
   
5. =SUMIFS(B19:B26,A19:A26,D19)
   
6. =INDEX(B19:B26,MATCH(D19,A19:A26,))
   
7. =HLOOKUP(D19,TRANSPOSE(A19:B26),2,)
   
8. =LOOKUP(,0/(A19:A26=D19),B19:B26)

复制代码

第二题
1

1. =LOOKUP(F16,$B$16:$C$20)
   
2. =VLOOKUP(F16,$B$16:$C$20,2)

复制代码

2

1. =LOOKUP(,0/FIND(E25,A25:B30),C25:C30)
   
2. =SUMIF(A25:B30,"*"&E25&"*",C25)

复制代码

第三题

1. =SUM(OFFSET(A14,MATCH(J17,A15:A26,),MATCH(J16,B14:G14,),J18-J17+1))

复制代码

第四题

1. =SUMIFS($C$13:$C$24,$A$13:$A$24,$E13,$B$13:$B$24,F$12)

复制代码

第五题
一定要用VLOOKUP的话

1. =VLOOKUP($E$19,INDIRECT("A"&SMALL(IF($A$19:$A$24=$E$19,ROW($A$19:$A$24),9^9),ROW(A1))&":C25"),3,)

复制代码

huangcaiguang

5.1弄两个超长的数组公式：
=INDEX(C:C,SMALL(IF($A$19:$A$24=$E$19,ROW($A$19:$A$24),65536),ROW(1:1)))&""
=IFERROR(INDEX($A$19:$C$24,SMALL(IF($A$19:$A$24=$E$19,ROW($A$19:$A$24)-ROW($A$19)+1,ROW($A$24)+1),ROW(1:1)),3),"")
=INDIRECT("C"&SMALL(IF($A$19:$A$24=$E$19,ROW($A$19:$A$24)),ROW(1:1)))
=INDEX(C:C,SMALL(IF(ISNUMBER(FIND($E$19,$A$19:$A$24)),ROW($A$19:$A$24),4^8),ROW(A1)))&""弄成文本
=VLOOKUP(E$19&ROW(A1),IF({1,0},$A$19:$A$24&COUNTIF(INDIRECT("A19:A"&ROW($19:$24)),E$19),$C$19:$C$24),2,)

3.=SUM(OFFSET(INDIRECT(ADDRESS(J17+14,MATCH(J16,A14:G14))),,,J18-J17))应该有超短公式。。。

xiaoni

第一题：=OFFSET(B18,MATCH(D19,A19:A26,),)
             =INDEX(B19:B26,MATCH(D19,A19:A26,))
第二题：=SUMPRODUCT(ISNUMBER(FIND(E25,A25:B30))*C25:C30)
第三题：=SUM(INDEX(A15:G26,N(IF(1,ROW(3:7))),5))
             =SUMPRODUCT(VLOOKUP(N(IF(1,ROW(INDIRECT(J17&":"&J18)))),A15:G26,MATCH(J16,A14:G14,)))
=SUM(SUBTOTAL(9,OFFSET(A14,MATCH(ROW(3:7),A15:A26,),MATCH(J16,B14:G14,),)))
第四题：=VLOOKUP($E13&F$12,IF({1,0},$A$13:$A$24&$B$13:$B$24,$C$13:$C$24),2,)
第五题：=INDIRECT("C"&SMALL(IFERROR(MATCH(($A$19:$A$24=E$19)*$C$19:$C$24,$C$19:$C$24,)+18,100),ROW(A1)))&""

一杯清荼

1. =INDIRECT("b"&MAX((D19=A19:A26)*ROW(19:26)))

复制代码

1. =INDIRECT((ADDRESS(MATCH(D19,A19:A26,)+18,2)))

复制代码

1. =DSUM(A18:B26,2,D18:D19)

复制代码

1. =AVERAGEIF(A19:B26,D19,B19)

复制代码

1. =DAVERAGE(A18:B26,2,D18:D19)

复制代码

1. =MMULT(TRANSPOSE(B19:B26),(A19:A26=D19)*1)

复制代码

1. =PRODUCT(1+((A19:A26=D19)*B19:B26))-1

复制代码

1. =LOOKUP(MATCH(D19,A19:A26,),ROW(1:8),B19:B26)

复制代码

1. =FIND(D19,PHONETIC(A19:B26))*100

复制代码

huangcaiguang

1. =LOOKUP(1,0/(D19=A19:A26),B19:B26)
   
2. =SUMPRODUCT((D19=A19:A26)*B19:B26)
   
3. {=MAXA((A19:A26=D19)*B19:B26)}
   
4. {=PRODUCT(IF(A19:A26=D19,B19:B26))}
   
5. {=MIN(IF(A19:A26=D19,B19:B26))}
   
6. {=MAX((A19:A26=D19)*B19:B26)}
   
7. =DAVERAGE(A18:B26,2,D18:D19)
   
8. =DPRODUCT(A18:B26,2,D18:D19)
   
9. =DMAX(A18:B26,2,D18:D19)
   
10. =DMIN(A18:B26,2,D18:D19)
    
11. =DGET(A18:B26,2,D18:D19)
    
12. =DSUM(A18:B26,2,D18:D19)
    
13. =SUMPRODUCT(--(A19:A26=D19),B19:B26)
    
14. {=SUM((A19:A26=D19)*B19:B26)}
    
15. =SUMIF(A19:A26,D19,B19)
    
16. =INDIRECT(ADDRESS(MATCH(D19,A1:A26,),2))
    
17. =INDIRECT("R"&(MATCH(D19,A1:A$26,))&"c2",)
    
18. =INDIRECT("B"&MATCH(D19,A1:A26,))
    
19. =OFFSET(B18,MATCH(D19,A19:A26,),,,)
    
20. =INDEX(B19:B26,MATCH(D19,A19:A26,))
    
21. =CHOOSE(MATCH(D19,A19:A26,),B19,B20,B21,B22,B23,B24,B25,B26)
    
22. {=SUM(IF(A19:A26=D19,B19:B26))}
    
23. =SUMIFS(B19:B26,A19:A26,D19)
    
24. {=HLOOKUP(D19,IF({1;0},TRANSPOSE(A19:A26),TRANSPOSE(B19:B26)),2,)}
    
25. =VLOOKUP(D19,IF({1,0},A19:A26,B19:B26),2,)

复制代码

第一题先贴25种解法，等AS补充。。。
取巧又可以得到一类=MATCH(D19,A19:A26,)*100。。。。
用FIND函数或者search获取位置也有一类=FIND(D19,"甲乙丙丁戊己庚辛")*100   =SEARCH(D19,"甲乙丙丁戊己庚辛")*100。。。 位置判断舍近求远也有 {=MATCH(1,--(A19:A26=D19),)*100}   {=SUM((LEFT(A19:A26&B19:B26)=D19)*B19:B26)}
将区域A18:b26定义首行首列名称也有 =INDIRECT(D19) 成绩
至于TEXT/FREQUENCY/MMULT等函数也是有戏的就不瞎搞了。。。