[µo°Ý] ¤½¦¡¾ã¦X

shootingstar

¦p¼ÐÃD
½Ð°Ý³o¨â­Ó¤½¦¡¦p¦ó¾ã¦X¡A¦]¬°¹³¦bNÄæ§PÂ_¤ô¹q¶O¡B¥Ñ¹q¶O¤Î³f¹B¤½¥q¡A©Ò¥H¦³¥H¤U¤½¦¡¡A³o¨â­Ó¤½¥q¥i¥H¾ã¦X¶Ü¡HÁÂÁ¡I
=IF(F111="¶l¹q¶O",IF(ISNUMBER(FIND("¶l",K111)),"¶l¸ê","¹q¸Ü¶O"),IF(F111="¤ô¹q¥Ë´µ¶O",IF(ISNUMBER(FIND("¹q",K111)),"¹q¶O","¤ô¶O"),""))
{=IF(K129="","",IF(SUM(COUNTIF(K129,"*"&°Ñ·Ó!$M$2:$M$15&"*"))<1,"¨ä¥L",INDEX(°Ñ·Ó!$N$2:$N$15,MIN(IF(ISNUMBER(FIND(°Ñ·Ó!$M$2:$M$15,K129)),ROW(°Ñ·Ó!$N$2:$N$15)-1)))))}

¤U¸ü (65.48 KB)
¤½¦¡

2018-8-23 17:21

­ã´£³¡ªL

¨S¦³ÀÉ®×½Ö¤]°µ¤£¤F~~

shootingstar

¦^´_ 2# ­ã´£³¡ªL

±z¦n
ªþ¤WÀɮסA·Q¾ã¦XNÄ檺¨â­Ó¤½¦¡¡C
¦]»Ý§PÂ_¤ô¹q¶O¡B¶l¹q¶O¡A©Ò¥H¦³=IF(F2="¶l¹q¶O",IF(ISNUMBER(FIND("¶l",K2)),"¶l¸ê","¹q¸Ü¶O"),IF(F2="¤ô¹q¥Ë´µ¶O",IF(ISNUMBER(FIND("¹q",K2)),"¹q¶O","¤ô¶O"),""))
¤S¦]»Ý­n§PÂ_¹B¶OÄݦó®a³f¹B¤½¥q¡A©Ò¥H¦³{=IF(K34="","",IF(SUM(COUNTIF(K34,"*"&°Ñ·Ó!$M$2:$M$15&"*"))<1,"¨ä¥L",INDEX(°Ñ·Ó!$N$2:$N$15,MIN(IF(ISNUMBER(FIND(°Ñ·Ó!$M$2:$M$15,K34)),ROW(°Ñ·Ó!$N$2:$N$15)-1)))))}

©Î¬O¥u¯à¦A¼W¥[»²§UÄæ¡H
¥H¤W¡AÁÂÁ¡C

½d¨Ò.zip (57.6 KB)

½d¨Ò
¤U¸ü¦¸¼Æ: 3

2018-8-24 17:42

­ã´£³¡ªL

2003ª©:
=IF(F2="¶l¹q¶O",IF(ISERR(FIND("¶l",K2)),"¹q¸Ü¶O","¶l¸ê"),)&IF(F2="¤ô¹q¥Ë´µ¶O",IF(ISERR(FIND("¹q",K2)),"¤ô¶O","¹q¶O"),)&IF(F2="¹B¶O",MID(LOOKUP("z",9&IF({1,0},"¨ä¥L",LOOKUP(1,0/(FIND(°Ñ·Ó!M$2:M$20,9&K2)>1),°Ñ·Ó!N$2:N$20))),2,99),)


=IF(F2="¶l¹q¶O",IF(ISERR(FIND("¶l",K2)),"¹q¸Ü¶O","¶l¸ê"),)&IF(F2="¤ô¹q¥Ë´µ¶O",IF(ISERR(FIND("¹q",K2)),"¤ô¶O","¹q¶O"),)&IF(F2="¹B¶O",iferror(LOOKUP(1,0/(FIND(°Ñ·Ó!M$2:M$20,9&K2)>1),°Ñ·Ó!N$2:N$20),"¨ä¥L"),)