单条件查找148思路完成版

单条件查找148思路完成版 PS公式用查找替换批量修改完成，不免有误！且不推广如此破传递思路！换汤不换药，无半分技术含量！序号长度公式137=INDIRECT(ADDRESS(MATCH(F3,A:A,),2,)237=INDIRECT(ADDRESS(MID(F3,7,3)-18,2,)337=INDIRECT(ADDRESS(RIGHT(F3,2)-18,2,)453=INDIRECT(ADDRESS(MAX(A$3:A$20=F3)*ROW($3:$20),2,)554=INDIRECT(ADDRESS(MAXA(A$3:A$20=F3)*ROW($3:$20),2,)661=INDIRECT(ADDRESS(SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20),2,)759=INDIRECT(ADDRESS(PRODUCT(IF(A$3:A$20=F3,ROW($3:$20),2,)853=INDIRECT(ADDRESS(SUM(A$3:A$20=F3)*ROW($3:$20),2,)958=INDIRECT(ADDRESS(MINA(IF(A$3:A$20=F3),ROW($3:$20),2,)1057=INDIRECT(ADDRESS(MIN(IF(A$3:A$20=F3),ROW($3:$20),2,)1161=INDIRECT(ADDRESS(AVERAGE(IF(A$3:A$20=F3),ROW($3:$20),2,)1262=INDIRECT(ADDRESS(AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20),2,)1360=INDIRECT(ADDRESS(MEDIAN(IF(A$3:A$20=F3),ROW($3:$20),2,)1470=INDIRECT(ADDRESS(MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20),2,)1564=INDIRECT(ADDRESS(SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)1664=INDIRECT(ADDRESS(MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)1765=INDIRECT(ADDRESS(MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)1850=INDIRECT(ADDRESS(SUBSTITUTE(F3,BK-830,)-18,2,)1959=INDIRECT(ADDRESS(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10,2,)2057=INDIRECT(ADDRESS(LARGE(A$3:A$20=F3)*ROW($3:$20),1),2,)2162=INDIRECT(ADDRESS(PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1),2,)2258=INDIRECT(ADDRESS(SMALL(A$3:A$20=F3)*ROW($3:$20),18),2,)2363=INDIRECT(ADDRESS(SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1),2,)2459=INDIRECT(ADDRESS(SMALL(IF(A$1:A$20=F3,ROW($1:$20),1),2,)2537=INDIRECT(ADDRESS(MATCH(F3,A:A,),2,)2637=INDIRECT(ADDRESS(MID(F3,7,3)-18,2,)2737=INDIRECT(ADDRESS(RIGHT(F3,2)-18,2,)2853=INDIRECT(ADDRESS(MAX(A$3:A$20=F3)*ROW($3:$20),2,)2954=INDIRECT(ADDRESS(MAXA(A$3:A$20=F3)*ROW($3:$20),2,)3061=INDIRECT(ADDRESS(SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20),2,)3159=INDIRECT(ADDRESS(PRODUCT(IF(A$3:A$20=F3,ROW($3:$20),2,)3253=INDIRECT(ADDRESS(SUM(A$3:A$20=F3)*ROW($3:$20),2,)3358=INDIRECT(ADDRESS(MINA(IF(A$3:A$20=F3),ROW($3:$20),2,)3457=INDIRECT(ADDRESS(MIN(IF(A$3:A$20=F3),ROW($3:$20),2,)3561=INDIRECT(ADDRESS(AVERAGE(IF(A$3:A$20=F3),ROW($3:$20),2,)3662=INDIRECT(ADDRESS(AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20),2,)3760=INDIRECT(ADDRESS(MEDIAN(IF(A$3:A$20=F3),ROW($3:$20),2,)3870=INDIRECT(ADDRESS(MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20),2,)3964=INDIRECT(ADDRESS(SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)4064=INDIRECT(ADDRESS(MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)4165=INDIRECT(ADDRESS(MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)4250=INDIRECT(ADDRESS(SUBSTITUTE(F3,BK-830,)-18,2,)4359=INDIRECT(ADDRESS(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10,2,)4457=INDIRECT(ADDRESS(LARGE(A$3:A$20=F3)*ROW($3:$20),1),2,)4562=INDIRECT(ADDRESS(PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1),2,)4658=INDIRECT(ADDRESS(SMALL(A$3:A$20=F3)*ROW($3:$20),18),2,)4763=INDIRECT(ADDRESS(SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1),2,)4859=INDIRECT(ADDRESS(SMALL(IF(A$1:A$20=F3,ROW($1:$20),1),2,)4935=INDIRECT(R&MATCH(F3,A:A,)&C2,)5035=INDIRECT(r&MID(F3,7,3)-18&C2,)5135=INDIRECT(r&RIGHT(F3,2)-18&C2,)5251=INDIRECT(r&MAX(A$3:A$20=F3)*ROW($3:$20)&C2,)5352=INDIRECT(r&MAXA(A$3:A$20=F3)*ROW($3:$20)&C2,)5459=INDIRECT(r&SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)&C2,)5557=INDIRECT(r&PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)&C2,)5651=INDIRECT(r&SUM(A$3:A$20=F3)*ROW($3:$20)&C2,)5756=INDIRECT(r&MINA(IF(A$3:A$20=F3),ROW($3:$20)&C2,)5855=INDIRECT(r&MIN(IF(A$3:A$20=F3),ROW($3:$20)&C2,)5959=INDIRECT(r&AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)&C2,)6060=INDIRECT(r&AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)&C2,)6158=INDIRECT(r&MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)&C2,)6268=INDIRECT(r&MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)&C2,)6362=INDIRECT(r&SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)&C2,)6462=INDIRECT(r&MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)&C2,)6563=INDIRECT(r&MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)&C2,)6648=INDIRECT(r&SUBSTITUTE(F3,BK-830,)-18&C2,)6762=INDIRECT(r&INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)&C2,)6855=INDIRECT(r&LARGE(A$3:A$20=F3)*ROW($3:$20),1)&C2,)6960=INDIRECT(r&PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)&C2,)7056=INDIRECT(r&SMALL(A$3:A$20=F3)*ROW($3:$20),18)&C2,)7161=INDIRECT(r&SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)&C2,)7257=INDIRECT(r&SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)&C2,)7329=OFFSET(B1,MATCH(F3,A:A,)-1,)7429=OFFSET(b1,MID(F3,7,3)-18-1,)7529=OFFSET(b1,RIGHT(F3,2)-18-1,)7645=OFFSET(b1,MAX(A$3:A$20=F3)*ROW($3:$20)-1,)7746=OFFSET(b1,MAXA(A$3:A$20=F3)*ROW($3:$20)-1,)7853=OFFSET(b1,SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)-1,)7951=OFFSET(b1,PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)-1,)8045=OFFSET(b1,SUM(A$3:A$20=F3)*ROW($3:$20)-1,)8150=OFFSET(b1,MINA(IF(A$3:A$20=F3),ROW($3:$20)-1,)8249=OFFSET(b1,MIN(IF(A$3:A$20=F3),ROW($3:$20)-1,)8353=OFFSET(b1,AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)-1,)8454=OFFSET(b1,AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)-1,)8552=OFFSET(b1,MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)-1,)8662=OFFSET(B1,MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)-1,)8756=OFFSET(b1,SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)-1,)8856=OFFSET(b1,MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)-1,)8957=OFFSET(b1,MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)-1,)9042=OFFSET(b1,SUBSTITUTE(F3,BK-830,)-18-1,)9156=OFFSET(b1,INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)-1,)9249=OFFSET(b1,LARGE(A$3:A$20=F3)*ROW($3:$20),1)-1,)9354=OFFSET(b1,PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)-1,)9450=OFFSET(b1,SMALL(A$3:A$20=F3)*ROW($3:$20),18)-1,)9555=OFFSET(b1,SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)-1,)9651=OFFSET(b1,SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)-1,)9726=INDEX(B:B,MATCH(F3,A:A,)9826=INDEX(B:B,MID(F3,7,3)-18)9926=INDEX(B:B,RIGHT(F3,2)-18)10042=INDEX(B:B,MAX(A$3:A$20=F3)*ROW($3:$20)10143=INDEX(B:B,MAXA(A$3:A$20=F3)*ROW($3:$20)10250=INDEX(B:B,SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)10348=INDEX(B:B,PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)10442=INDEX(B:B,SUM(A$3:A$20=F3)*ROW($3:$20)10547=INDEX(B:B,MINA(IF(A$3:A$20=F3),ROW($3:$20)10646=INDEX(B:B,MIN(IF(A$3:A$20=F3),ROW($3:$20)10750=INDEX(B:B,AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)10851=INDEX(B:B,AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)10949=INDEX(B:B,MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)11059=INDEX(B:B,MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)11153=INDEX(B:B,SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)11253=INDEX(B:B,MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)11354=INDEX(B:B,MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)11439=INDEX(B:B,SUBSTITUTE(F3,BK-830,)-18)11553=INDEX(B:B,INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)11646=INDEX(B:B,LARGE(A$3:A$20=F3)*ROW($3:$20),1)11751=INDEX(B:B,PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)11847=INDEX(B:B,SMALL(A$3:A$20=F3)*ROW($3:$20),18)11952=INDEX(B:B,SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)12048=INDEX(B:B,SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)12143=CELL(contents,INDEX(B:B,MATCH(F3,A:A,)12243=CELL(contents,INDEX(B:B,MID(F3,7,3)-18)12343=CELL(contents,INDEX(B:B,RIGHT(F3,2)-18)12459=CELL(contents,INDEX(B:B,MAX(A$3:A$20=F3)*ROW($3:$20)12560=CELL(contents,INDEX(B:B,MAXA(A$3:A$20=F3)*ROW($3:$20)12667=CELL(contents,INDEX(B:B,SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)12765=CELL(contents,INDEX(B:B,PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)12859=CELL(contents,INDEX(B:B,SUM(A$3:A$20=F3)*ROW($3:$20)12964=CELL(contents,INDEX(B:B,MINA(IF(A$3:A$20=F3),ROW($3:$20)13063=CELL(contents,INDEX(B:B,MIN(IF(A$3:A$20=F3),ROW($3:$20)13167=CELL(contents,INDEX(B:B,AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)13268=CELL(contents,INDEX(B:B,AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)13366=CELL(contents,INDEX(B:B,MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)13476=CELL(contents,INDEX(B:B,MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)13570=CELL(contents,INDEX(B:B,SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)13670=CELL(contents,INDEX(B:B,MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)13771=CELL(contents,INDEX(B:B,MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)13856=CELL(contents,INDEX(B:B,SUBSTITUTE(F3,BK-830,)-18)13970=CELL(contents,INDEX(B:B,INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)14063=CELL(contents,INDEX(B:B,LARGE(A$3:A$20=F3)*ROW($3:$20),1)14168=CELL(contents,INDEX(B:B,PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)14264=CELL(contents,INDEX(B:B,SMALL(A$3:A$20=F3)*ROW($3:$20),18)14369=CELL(contents,INDEX(B:B,SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)14465=CELL(contents,INDEX(B:B,SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)14525=LOOKUP(1,0/(A:A=F3),B:B)14630=LOOKUP(1,0/EXACT(A:A,F3),B:B)14719=VLOOKUP(F3,A:B,2,)14830=HLOOKUP(F3,TRANSPOSE(A:B),2,)PS公式用查找替换批量修改完成，不免有误！且不推广如此破传递思路！换汤不换药，无半分技术含量！=INDIRECT(ADDRESS(MAX(A$3:A$20=F3)*ROW($3:$20),2,)=INDIRECT(ADDRESS(MAXA(A$3:A$20=F3)*ROW($3:$20),2,)=INDIRECT(ADDRESS(SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(PRODUCT(IF(A$3:A$20=F3,ROW($3:$20),2,)=INDIRECT(ADDRESS(SUM(A$3:A$20=F3)*ROW($3:$20),2,)=INDIRECT(ADDRESS(MINA(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(MIN(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(AVERAGE(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(MEDIAN(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20),2,)=INDIRECT(ADDRESS(SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(SUBSTITUTE(F3,BK-830,)-18,2,)=INDIRECT(ADDRESS(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10,2,)=INDIRECT(ADDRESS(LARGE(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(SMALL(A$3:A$20=F3)*ROW($3:$20),18),2,)=INDIRECT(ADDRESS(SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1),2,)=INDIRECT(ADDRESS(SMALL(IF(A$1:A$20=F3,ROW($1:$20),1),2,)=INDIRECT(ADDRESS(MAX(A$3:A$20=F3)*ROW($3:$20),2,)=INDIRECT(ADDRESS(MAXA(A$3:A$20=F3)*ROW($3:$20),2,)=INDIRECT(ADDRESS(SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(PRODUCT(IF(A$3:A$20=F3,ROW($3:$20),2,)=INDIRECT(ADDRESS(SUM(A$3:A$20=F3)*ROW($3:$20),2,)=INDIRECT(ADDRESS(MINA(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(MIN(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(AVERAGE(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(MEDIAN(IF(A$3:A$20=F3),ROW($3:$20),2,)=INDIRECT(ADDRESS(MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20),2,)=INDIRECT(ADDRESS(SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(SUBSTITUTE(F3,BK-830,)-18,2,)=INDIRECT(ADDRESS(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10,2,)=INDIRECT(ADDRESS(LARGE(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1),2,)=INDIRECT(ADDRESS(SMALL(A$3:A$20=F3)*ROW($3:$20),18),2,)=INDIRECT(ADDRESS(SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1),2,)=INDIRECT(ADDRESS(SMALL(IF(A$1:A$20=F3,ROW($1:$20),1),2,)=INDIRECT(r&MAX(A$3:A$20=F3)*ROW($3:$20)&C2,)=INDIRECT(r&MAXA(A$3:A$20=F3)*ROW($3:$20)&C2,)=INDIRECT(r&SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)&C2,)=INDIRECT(r&PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)&C2,)=INDIRECT(r&SUM(A$3:A$20=F3)*ROW($3:$20)&C2,)=INDIRECT(r&MINA(IF(A$3:A$20=F3),ROW($3:$20)&C2,)=INDIRECT(r&MIN(IF(A$3:A$20=F3),ROW($3:$20)&C2,)=INDIRECT(r&AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)&C2,)=INDIRECT(r&AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)&C2,)=INDIRECT(r&MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)&C2,)=INDIRECT(r&MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)&C2,)=INDIRECT(r&SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)&C2,)=INDIRECT(r&MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)&C2,)=INDIRECT(r&MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)&C2,)=INDIRECT(r&SUBSTITUTE(F3,BK-830,)-18&C2,)=INDIRECT(r&INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)&C2,)=INDIRECT(r&LARGE(A$3:A$20=F3)*ROW($3:$20),1)&C2,)=INDIRECT(r&PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)&C2,)=INDIRECT(r&SMALL(A$3:A$20=F3)*ROW($3:$20),18)&C2,)=INDIRECT(r&SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)&C2,)=INDIRECT(r&SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)&C2,)=OFFSET(b1,MAX(A$3:A$20=F3)*ROW($3:$20)-1,)=OFFSET(b1,MAXA(A$3:A$20=F3)*ROW($3:$20)-1,)=OFFSET(b1,SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)-1,)=OFFSET(b1,PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)-1,)=OFFSET(b1,SUM(A$3:A$20=F3)*ROW($3:$20)-1,)=OFFSET(b1,MINA(IF(A$3:A$20=F3),ROW($3:$20)-1,)=OFFSET(b1,MIN(IF(A$3:A$20=F3),ROW($3:$20)-1,)=OFFSET(b1,AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)-1,)=OFFSET(b1,AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)-1,)=OFFSET(b1,MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)-1,)=OFFSET(B1,MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)-1,)=OFFSET(b1,SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)-1,)=OFFSET(b1,MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)-1,)=OFFSET(b1,MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)-1,)=OFFSET(b1,INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)-1,)=OFFSET(b1,LARGE(A$3:A$20=F3)*ROW($3:$20),1)-1,)=OFFSET(b1,PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)-1,)=OFFSET(b1,SMALL(A$3:A$20=F3)*ROW($3:$20),18)-1,)=OFFSET(b1,SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)-1,)=OFFSET(b1,SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)-1,)=INDEX(B:B,SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)=INDEX(B:B,PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)=INDEX(B:B,MINA(IF(A$3:A$20=F3),ROW($3:$20)=INDEX(B:B,MIN(IF(A$3:A$20=F3),ROW($3:$20)=INDEX(B:B,AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)=INDEX(B:B,AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)=INDEX(B:B,MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)=INDEX(B:B,MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)=INDEX(B:B,SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=INDEX(B:B,MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=INDEX(B:B,MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=INDEX(B:B,INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)=INDEX(B:B,LARGE(A$3:A$20=F3)*ROW($3:$20),1)=INDEX(B:B,PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)=INDEX(B:B,SMALL(A$3:A$20=F3)*ROW($3:$20),18)=INDEX(B:B,SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)=INDEX(B:B,SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)=CELL(contents,INDEX(B:B,MAX(A$3:A$20=F3)*ROW($3:$20)=CELL(contents,INDEX(B:B,MAXA(A$3:A$20=F3)*ROW($3:$20)=CELL(contents,INDEX(B:B,SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)=CELL(contents,INDEX(B:B,PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)=CELL(contents,INDEX(B:B,SUM(A$3:A$20=F3)*ROW($3:$20)=CELL(contents,INDEX(B:B,MINA(IF(A$3:A$20=F3),ROW($3:$20)=CELL(contents,INDEX(B:B,MIN(IF(A$3:A$20=F3),ROW($3:$20)=CELL(contents,INDEX(B:B,AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)=CELL(contents,INDEX(B:B,AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)=CELL(contents,INDEX(B:B,MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)=CELL(contents,INDEX(B:B,MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)=CELL(contents,INDEX(B:B,SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=CELL(contents,INDEX(B:B,MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=CELL(contents,INDEX(B:B,MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=CELL(contents,INDEX(B:B,SUBSTITUTE(F3,BK-830,)-18)=CELL(contents,INDEX(B:B,INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)=CELL(contents,INDEX(B:B,LARGE(A$3:A$20=F3)*ROW($3:$20),1)=CELL(contents,INDEX(B:B,PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)=CELL(contents,INDEX(B:B,SMALL(A$3:A$20=F3)*ROW($3:$20),18)=CELL(contents,INDEX(B:B,SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)=CELL(contents,INDEX(B:B,SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)号图书编号图书名称定价图书编号BK-83021计算机基础及MS Office应用#BK-83021BK-83022计算机基础及Photoshop应用#BK-83033BK-83023C语言程序设计#BK-83037BK-83024VB语言程序设计#行号获取部分思路BK-83025Java语言程序设计#公式1公式2公式3公式4公式5公式6BK-83026Aess数据库程序设计#333333BK-83027MySQL数据库程序设计#151515151515BK-83028MS Office高级应用#191919191919BK-83029网络技术#BK-83030数据库技术#序号长度公式BK-83031软件测试技术#115BK-83032信息安全技术#215BK-83033嵌入式系统开发技术#315BK-83034操作系统原理#433BK-83035计算机组成与接口#534BK-83036数据库原理#639BK-83037软件工程#739833938103711411242134014501542164217431828194420372142223823432439=PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)=SMALL(A$3:A$20=F3)*ROW($3:$20),18)=SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)=SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)=MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=SUBSTITUTE(F3,BK-830,)-18=INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)=LARGE(A$3:A$20=F3)*ROW($3:$20),1)=AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)=AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)=MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)=MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)=SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)=PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)=SUM(A$3:A$20=F3)*ROW($3:$20)=MINA(IF(A$3:A$20=F3),ROW($3:$20)=MIN(IF(A$3:A$20=F3),ROW($3:$20)=MATCH(F3,A:A,)=MID(F3,7,3)-18=RIGHT(F3,2)-18=MAX(A$3:A$20=F3)*ROW($3:$20)=MAXA(A$3:A$20=F3)*ROW($3:$20)图书编号对照表图书名称计算机基础及MS Office应用嵌入式系统开发技术软件工程计算机基础及MS Office应用嵌入式系统开发技术软件工程公式7公式8公式9公式10公式11公式12公式13公式14公式15公式16公式17公式18公式1933333333333331515151515151515151515151519191919191919191919191919=PERCENTILE(A$3:A$20=F3)*ROW($3:$20),1)=SMALL(IF(A$1:A$20=F3,ROW($1:$20),48),1)=SMALL(IF(A$1:A$20=F3,ROW($1:$20),1)=MAX(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=MAXA(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=INT(AVEDEV(A$3:A$20=F3)*ROW($3:$20)*10)=AVERAGE(IF(A$3:A$20=F3),ROW($3:$20)=AVERAGEA(IF(A$3:A$20=F3),ROW($3:$20)=MEDIAN(IF(A$3:A$20=F3),ROW($3:$20)=MODE(IF(A$3:A$20=F3),ROW($3:$20),ROW($3:$20)=SUM(MMULT(A$3:A$20=F3)*ROW($3:$20),1)=SUMPRODUCT(N(A$3:A$20=F3),ROW($3:$20)=PRODUCT(IF(A$3:A$20=F3,ROW($3:$20)公式20公式21公式22公式23公式243333315151515151919191919INDIRECT ADDRESS号图书编号图书名称定价图书编号BK-83021计算机基础及MS Office应用#BK-83021BK-83022计算机基础及P