已开贴【函中自测03】- 序列及查询

满坛皆为吾师

说明
1、题简勿喷，慢慢来，向大家学习互相交流思路。
2、欢迎各位参与，各路大神多多指点。
3、本期开贴后做公式解析。
函中自测03.rar (17.8 KB, 下载次数: 120)

2014-8-13 22:05 上传

点击文件名下载附件



本期解析
——————————————————————————————
问题1公式：=REPT(1,ROW(A1))^2
说明：本题其实就是数学题，正序平方，逆序平方根，利用REPT重复的特性达成。
小结：你们数学都很赞哈！
问题2：
一览所有取姓名的公式，有一个共同点：MATCH函数，只是在参数上的使用稍有差异，5、7、9楼类同，这里用9楼路边的记忆公式做说明。图片点击放大。

F22=OFFSET(B$13,MATCH(,COUNTIF(F$21:F21,B$14:B$24),),)
分步一：COUNTIF(F$21:F21,B$14:B$24)
　　返回一个{1;0;0;0;0;0;0;0;0;0;0}的数组，因为姓名F21在B14:B24中只存在一个。
　　这里需要注意的是公式中F$21:F21对行的绝对及相对引用。
分步二：MATCH(,COUNTIF(F$21:F21,B$14:B$24),)
　　由分步一返回MATCH(,{1;0;0;0;0;0;0;0;0;0;0},)
　　效果等同①：=MATCH(0,{1;0;0;0;0;0;0;0;0;0;0},)
　　效果等同②：=MATCH(,{1;0;0;0;0;0;0;0;0;0;0},0)
　　效果等同③：=MATCH(0,{1;0;0;0;0;0;0;0;0;0;0},0)
　　那么有同学会问：MATCH的第三参数在公式中不是省略了吗？
　　回答是NONONO~,肿么让我想起了一首byebyebye的英文歌………
　　MATCH的第三参数如果省略，默认值为1，并按照两分法的原则查询
　　省略的写法是MATCH(,{1;0;0;0;0;0;0;0;0;0;0})当中没有最后那个逗号。
　　所以分步二MATCH在常量数组中精确匹配第一个0值的位置，等于2。
分布三：OFFSET(B$13,MATCH(,COUNTIF(F$21:F21,B$14:B$24),),)
　　由分步二的返回值返回了一个最基本的公式=OFFSET(B$13,2,)
　　以B13的基址向下偏移2行0列 = B15 = 李先生
分布四：由此以上公式解析完成，当公式下拉至F23时，公式中变动的地方只有一个
　　=OFFSET(B$13,MATCH(,COUNTIF(F$21:F22,B$14:B$24),),)
　　相对引用的行F21变成了F22，因为姓名和李先生在B14:B24中存在三个
　　MATCH返回值为4，OFFSET返回的结果就变成了吴女士，然后继续下拉即可。
　　小结：你们都好犀利！！
取日期及金额公式：
　　这里公式就不细述，条件查询的方式都有约三十种了，请各位自行度娘。但是在本题当中，对于年份的判断，如何写入一条公式使之成立是要点。
　　以下取列亮点：
　　3楼E组组长ANDY0393公式中的IF(COUNTIF(G$21,"*日期")
　　5楼E06:chh0926公式中的INDIRECT(IF(RIGHT(G$21,2)="日期","c","d")
　　6楼Hsl215公式中的MOD(COLUMN(B2),2)+1，可以化简
　　7楼A05:MCQUEEN公式中的1+ISEVEN(COLUMN())
　　9楼路边的记忆公式中的$F22&LEFT(G$21,4)
　　10楼笨笨四公式中的MOD(COLUMN(),2)
　　以上请各位同学自行品味。
　　小结：你们真的好犀利！！！
总结：以上仅为个人浅见，欢迎各位不吝指正，感谢各位坛友，学员的精彩解答。

最后

最后

最后

亲们，能打赏点经验不{:221:}


楼层公式附件已整理，请自行下载。 函中自测03汇总.rar (20.8 KB, 下载次数: 12)

2014-8-16 23:04 上传

点击文件名下载附件

滴答滴

1. =REPT(1,ROW(A1))^2

复制代码

ANDY0393

题一

1. =REPT(1,ROW(A1))^2

复制代码

题二
姓名

1. =INDEX(B:B,SMALL(IF(MATCH(B$$$$15:B$$$$24,B$$$$15:B$$$$24,)=A$$$$15:A$$$$24,ROW($$$$15:$$$$24),4^8),ROW(A1)))

复制代码

日期金额

1. =SUMPRODUCT(($$$$F22=$$$$B$$$$15:$$$$B$$$$24)*(LEFT(G$$$$21,4)=LEFT($$$$C$$$$15:$$$$C$$$$24,4))*IF(COUNTIF(G$$$$21,"*日期"),$$$$C$$$$15:$$$$C$$$$24,$$$$D$$$$15:$$$$D$$$$24))&""

复制代码

瑾瑄

题1=REPT(1,ROW()-1)^2，题2：姓名＝INDEX($B$15:$B$24,SMALL(IF(MATCH($B$15:$B$24,$B$15:$B$24,0)=ROW($A$1:$A$10),ROW($A$1:$A$10),4^8),ROW(A1)))&""，日期：=IF(ISERROR(INDEX($C$15:$C$24,SMALL(IF(F22&2012=$B$15:$B$24&YEAR(TEXT($C$15:$C$24,"0000-00")),ROW($A$1:$A$10),4^8),ROW($A$1)))&""),"",INDEX($C$15:$C$24,SMALL(IF(F22&2012=$B$15:$B$24&YEAR(TEXT($C$15:$C$24,"0000-00")),ROW($A$1:$A$10),4^8),ROW($A$1)))&"")，金额＝=IF(ISERROR(VLOOKUP(F22&G22,IF({1,0},B15:B24&C15:C24,D15:D24),2,0)),"",VLOOKUP(F22&G22,IF({1,0},B15:B24&C15:C24,D15:D24),2,0))

chh0926

123

hsl215

第一题

1. =LEFT("12345678",ROW()-1)&RIGHT("7654321",ROW()-2)

复制代码

第二题（两个均是数组公式）

1. =OFFSET(B$14,SMALL(IF(MATCH(B$15:B$24,B$15:B$24,)=ROW($1:$10),ROW($1:$10),11),ROW(A1)),)
   
2. =OFFSET($B$14,IF(ISNA(MATCH($F22&LEFT(G$21,4),$B$15:$B$24&LEFT($C$15:$C$24,4),)),11,MATCH($F22&LEFT(G$21,4),$B$15:$B$24&LEFT($C$15:$C$24,4),)),MOD(COLUMN(B2),2)+1)&""

复制代码

MCQUEEN

第一题

1. =LEFT("123456789",ROW(A1))&RIGHT("87654321",ROW(A1)-1)

复制代码

第二题F22单元格公式下拉

1. =INDEX($B$15:$B$24,MATCH(,COUNTIF($F$21:F21,$B$15:$B$24),))

复制代码

第二题G22单元格公式右拉下拉J27单元格完成4列自动填充

1. =IFERROR(INDEX($C$15:$D$24,MATCH(1,($B$15:$B$24=$F22)*(LEFT($C$15:$C$24,4)=LEFT(G$21,4)),),1+ISEVEN(COLUMN())),"")

复制代码

ldxhzy

加不了附件，直接贴公式。
题一，H2公式：
          =LEFT(12345678,ROW(A1))&RIGHT(7654321,ROW(A1)-1)
    下拉至H9
题二，F22公式：
                             =LOOKUP(1,0/ISNA(MATCH($B$15:$B$24,$F$21:F21,0)),$B$15:$B$24)
          G22公式：
                             =SUMPRODUCT(--($B$15:$B$24=$F22),--(LEFT($C$15:$C$24,4)=LEFT(G$21,4)),$C$15:$C$24)
           H22公式：
                             =SUMPRODUCT(--($B$15:$B$24=$F22),--(LEFT($C$15:$C$24,4)=LEFT(H$21,4)),$D$15:$D$24)
          将以上三个公式下拉后，分别将G、H公式复制到I、J列。

路边的记忆

1,

1. =REPLACE("1234567890987654321",ROW(),22-ROW()*2,)

复制代码

2-1,F22下拉

1. =OFFSET(B$13,MATCH(,COUNTIF(F$21:F21,B$14:B$24),),)

复制代码

2-2,G22下拉右拉

1. =TEXT(SUM(N(OFFSET($C$14,$A$15:$A$24,MOD(COLUMN(B1),2)))*($B$15:$B$24&LEFT($C$15:$C$24,4)=$F22&LEFT(G$21,4))),"0;;")

复制代码

十五的月亮

1、=MID("123456789",1,ROW(A1))&RIGHT("987654321 ",ROW(A1))
2、=INDEX(B$15:B$24,SMALL(IF(MATCH(B$15:B$24,B$15:B$24,)=ROW($1:$10),ROW($1:$10),99),ROW(A1)))
=IFERROR(VLOOKUP($F22&LEFT($G$21,4),IF({1,0},$B$15:$B$24&LEFT($C$15:$C$24,4),C$15:C$24),2,),"")
=IFERROR(VLOOKUP($F22&LEFT($I$21,4),IF({1,0},$B$15:$B$24&LEFT($C$15:$C$24,4),C$15:C$24),2,),"")
2题2=IFERROR(OFFSET($B$14,MATCH($F22&LEFT(G$21,4),$B$15:$B$24&LEFT($C$15:$C$24,4),),MOD(COLUMN(B1),2)+1),"")