<p>我的答案,供参考</p><p>1.=LOOKUP(1,0/((C2:C5="甲")*(D2:D5="地区1")),E2:E5)</p><p> =LOOKUP(1,0/(C2:C5="甲"),E2:E5)</p><p>2.=SUM(LOOKUP("地区"&{1,3,6},B2:C8))</p><p>3.=INDEX(B2:B5,MATCH(B10,C2:C5,))</p><p>4.=LOOKUP(MOD(COLUMN()-1,3)+1,COLUMN(1:1),2:2),=INDEX(2:2,MOD(COLUMN()-1,3)+1)</p><p> =LOOKUP(COLUMN(C1)/3,COLUMN(1:1),2:2),=INDEX(2:2,COLUMN(C1)/3)</p><p>5.=LOOKUP({1,2,3,4,5,6}+{0;6},ROW(A1:A12),A1:A12)</p><p> =LOOKUP({1,2,3,4}+{0;4;8},ROW(1:12),A1:A12)</p><p> =LOOKUP({1,2,3}+{0;3;6;9},ROW(1:12),A1:A12)</p><p>6.=INDEX(A:A,SMALL(IF($B$2:$B$18&$C$2:$C$18=$G$1&$G$2,ROW($2:$18),2^16),ROW(A1)))&""</p><p>7.=LOOKUP(1,0/ISNA(MATCH(A$5:A$14,C$4:C4,)),A$5:A$14)</p><p> =LOOKUP(MATCH(,COUNTIF(D$4:D4,A$5:A$15),),ROW($1:$15),A$5:A$15&"")</p><p> =INDEX(A$5:A$15,MATCH(,COUNTIF(D$4:D4,A$5:A$15),),ROW($1:$15))&""</p><p> =INDEX(A:A,SMALL(IF(MATCH(A$5:A$14,A$5:A$14,)=ROW($1:$10),ROW($5:$14),15),ROW(A1)))&""</p><p>8.=INDEX(A:A,MOD(LARGE($B$6:$B$20*100+ROW($6:$20),ROW(A1)),100))</p><p>9.=LOOKUP("z",A$3:A3)</p><p> =INDEX(A$3:A$13,MATCH("z",A$3:A3))</p>
[此贴子已经被作者于2009-12-6 15:41:41编辑过] |