【函初201203班】-C08-一杯清荼
作业1:- =VLOOKUP($B$10,IF({1,0},附表1!$H:$H,INDEX(附表1!$A$2:$H$19,MATCH($B$10,附表1!$H$2:$H$19,),MATCH(C9,附表1!$A$1:$G$1,))),2,)
复制代码- =VLOOKUP($B$10,IF({1,0},附表1!$H:$H,INDIRECT("附表1!C"&MATCH(C9,附表1!$A$1:$G$1,),)),2,)
复制代码- =HLOOKUP(C9,附表1!$A1:$H19,MATCH($B10,附表1!$H1:$H19,),)
复制代码- =INDEX(附表1!$A$1:$H$19,MATCH($B$10,附表1!$H$1:$H$19,),MATCH(C9,附表1!$A$1:$H$1,))
复制代码 作业2:- =INDEX(附表1!$A:$H,SMALL(IF(LEFT(附表1!$H$2:$H$19)="李",ROW($2:$19),99),ROW(A1)),MATCH(C$19,附表1!$A$1:$H$1,))&""
复制代码- =INDEX(附表1!$A:$H,SMALL(IF(FIND("李",附表1!$H$2:$H$19&"李")=1,ROW($2:$18),2^9),ROW($1:$18)),MATCH(C$19,附表1!$A$1:$H$1,))&""
复制代码 作业3:- =VLOOKUP(COLUMN(A1),IF({1,0},ROW(1:8),$A$36:$A$43),2,0)
复制代码- =INDEX($A$36:$A$43,COLUMN(A1))
复制代码- =HLOOKUP($A$36:$A$43,$A$36:$A$43,COLUMN(A1),)
复制代码 |