本帖最后由 木偶fenzi 于 2015-7-6 17:20 编辑
原来我是最后一个啊
第一题第二题- =LOOKUP(SMALL(IF(FREQUENCY(ti2,ti2),ROW(1:9)),ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(ti2,ti2))))),ROW(1:9),ti2)
复制代码- =MOD(SMALL(IF(FREQUENCY(ti2,ti2)>0,ROW(1:9)/1%+ti2),ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(ti2,ti2))))),100)
复制代码- =LOOKUP(SMALL(IF(MATCH(ti2,ti2,)=ROW(INDIRECT("1:"&COUNT(ti2))),ROW(INDIRECT("1:"&COUNT(ti2)))),ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(ti2,ti2))))),ROW(1:9),ti2)
复制代码 第三题- =LOOKUP(ROW(A16:A28),IF(A16:A28>"",ROW(A16:A28)),A16:A28)
复制代码- =INDIRECT("a"&MATCH(ROW(A16:A28),IF(A16:A28>"",ROW(A16:A28)))+15)
复制代码- =OFFSET(A15,MATCH(ROW(A16:A28),IF(A16:A28>"",ROW(A16:A28))),)
复制代码- =RIGHT(PHONETIC(OFFSET(A16,,,ROW(1:13))))
复制代码 第四题- =LOOKUP(SMALL(IFERROR(IF(data,ROW(1:20)),9999),ROW(INDIRECT("1:"&COUNT(0/data)))),ROW(1:20),data)
复制代码- =LOOKUP(SMALL(IF(data,ROW(INDIRECT("1:"&COUNT(data)))),ROW(INDIRECT("1:"&COUNT(0/data)))),ROW(1:20),data)
复制代码 |