ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¸ê®Æ±Æ°£°ÝÃD

[µo°Ý] ¸ê®Æ±Æ°£°ÝÃD

¤j¤j­Ì¦n,
½Ð°Ý¥H¤U°ÝÃD
CÄæ·|¦³ª«¬y°Óªº¦W¦r,¥Ø«e¤wª¾ ¥x¤¤.ªá½¬.°ª¶¯.¶³ªL,¬O§Ú¤£­nªº¸ê®Æ,¦ý­ì¸ê®Æ­n«O¯d,
§Ú¨Ì¾ÚCÄ檫¬y°Ó,¦bKÄæshow¥X§Ú­nªºª«¬y°Ó²ºÙ,§Ú­nªº§@¥Î¬O:
CÄæ¤@¥X²{§Ú­nªºª«¬y°Ó,´N¨Ì§ÇÅã¥Ü,¤@ª½¨ì¤U¤@­ÓCÄæ¥X²{·sªºª«¬y°Ó,¦pªG¬O§Ú­nªº´NÅã¥Ü·sªº,
¬O­n±Æ°£ªº,´N¥X²{ERR,¤@ª½¨ì¸ê®Æªº³Ì©³ºÝ,¦ý²{¦b¦³­Ó°ÝÃDµLªk¸Ñ¨M,¦³´X­Ó®Æ¸¹¤£¬O§Ú­nªº,
§Æ±æ¥¦¤]¥i¥H±Nª«¬y°ÓÅã¥Ü¬°ERR,§Ú±N»Ý­n±Æ°£ªº°Ó«~¶K¦bO1:P8
¥Ø«e§Ú¥Î¨ç¼ÆK2=IF(COUNTIF(C2,"¥x¤¤*")+COUNTIF(C2,"ªá½¬*")+COUNTIF(C2,"°ª¶¯*")+COUNTIF(C2,"¶³ªL*")=1,"ERR",IF(COUNTIF(C2,"ªL¤f*"),"¤ñµá¦h.ªL¤f¥þ¥x",IF(COUNTIF(C2,"¤K¼w*"),"¤ñµá¦h.¤K¼w¥þ¥x",IF(COUNTIF(C2,"¤j·Ë*"),"¤ñµá¦h.¤j·Ë¥þ¥x",K1))))
¥¦¥i¥H¥X²{§Ú­nªºª«¬y°Ó²ºÙ,¦ýµLªk±Æ°£¤£­nªº®Æ¸¹,±Nª«¬y°ÓÅܬ°ERR,½Ð°Ý¨ç¼Æ¥i¥H¦p¦ó­×§ï?
¸ê®Æ±Æ°£°ÝÃD.rar (29.9 KB)

¥»©«³Ì«á¥Ñ zheng211016 ©ó 2019-5-19 01:09 ½s¿è

§Q¥Î¨ç¼Æ1 & 2 (¥i¥H§âÄæ¦ìÁôÂð_¨Ó©Î§Q¥Î¸s²Õ ¥i¥H¬ü¤Æª©­± )²£¥Í ¨ç¼Æ3=µª®×

¨ç¼Æ1 : ¨Ì ¤u§@­¶ : ¿z¿ï±ø¥ó ²¤Æ¦WºÙ ( ¥u§ì¨úª«¬y°Óªº«e¨â­Ó¦r¨Ó°µ¤ñ¹ï )
K2 ¤½¦¡: IFERROR(IFERROR(IF(MATCH(­q³æ.¤ñµá¦h.¥þ¥x!C2,¿z¿ï±ø¥ó!A:A,0),INDEX(¿z¿ï±ø¥ó!B:B,MATCH(­q³æ.¤ñµá¦h.¥þ¥x!C2,¿z¿ï±ø¥ó!A:A,0))),INDEX(¿z¿ï±ø¥ó!B:B,MATCH(MID(­q³æ.¤ñµá¦h.¥þ¥x!C2,1,2),¿z¿ï±ø¥ó!A:A,0))),"")

¨ç¼Æ2 : ¶Ç¦^²¤Æ¼ÐÃDªº¦C¸¹
L2 ¤½¦¡: IFERROR(IF(FIND("ª«¬y",C2,1),ROW(),""),"")

¨ç¼Æ3 : ¶Ç¦^µª®×(¶Ç¦^²ºÙ­Y¨S¦³Â²ºÙ´N¶Ç¦^¤W¤@­Ó²ºÙ)
M2 ¤½¦¡ : IF(K2<>"",K2,INDIRECT("K"&MAX($L$2:L2)))


³]©w ¶µ¥Ø Âà´« ²ºÙ ( AÄ欰­n§ó§ï¦WºÙªº¶µ¥Ø , BÄ欰²ºÙ : ¨Ò¦p : ·j´M¨ì A1¸ê®Æ ´N·|¶Ç¦^ B1¸ê®Æ )

¸ê®Æ±Æ°£°ÝÃD.rar (43.4 KB)

TOP

¦^´_ 2# zheng211016

ÁÂÁ§A°Ú¡I³o¬O¥Ø«e°ß¤@ªº¤è®×¤F¡I
§Ú¦Û¤v¤@ª½¸Õ¤£¥X¥i¥H¤@­Ó¤½¦¡ºâ¥Xµª®×(¨Ò¦p¡GM2=....ªº³oºØ¤½¦¡),¦pªG¦³¨ä¥L§ó¦nªº¤èªk,½Ð¦A«ü¾É¤@¤U§Ö·Q¯}ÀYªº§Ú¡I

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD