¤ñ¹ï¨Ã§PÂ_Åã¥Ü°T®§°ÝÃD

jsc0518

§Ú¦³³]©wExcel¨ç¼Æ¡A¦ý«Ü§Ö´N¥d¨ì¤F¡A¦Ó¥B§Ú³£¤@ª½¥ÎIFªº¤è¦¡´`Àô¡A¦ýÁÙ¬O¿ù»~
=IF(MID(B5,10,4)=MID(H5,10,4),IF(MID(B5,10,5)=MID(H5,10,5),"",IF(MID(B5,14,14)<>MID(H5,14,14),"ª©¥»¤É¯Å",IF(MID(B5,10,5)<>MID(H5,10,5),"§ó§ï®Æ¸¹",IF(B5="",IF(H5<>"","·s¼W®Æ¸¹","§R°£®Æ¸¹"))))))

§Ú·Q³]©wªº±ø¥ó¬O
·íBÄæ¤ñ¹ïHÄæ®É
1.³Ì«á¤@½X¤£¤@¼Ë¡AFÄæÅã¥Ü"ª©¥»¤É¯Å"
2.«á­±¤­½X¤£¤@¼Ë¡AFÄæÅã¥Ü"§ó§ï®Æ¸¹"
3.·íBÄæ»PHÄæ¼Æ¦r§¹¥þ¤@¼Ë®É¡AFÄæÅã¥Ü""
4.·íBÄ榳¸ê®Æ¡AHÄæ¬OªÅ¥Õ¡AFÄæÅã¥Ü"§R°£®Æ¥ó"
5.·íHÄ榳¸ê®Æ¡ABÄæ¬OªÅ¥Õ¡AFÄæÅã¥Ü"·s¼W®Æ¥ó"

¤U¸ü (27.67 KB)
·Q­nªºµe­±

2017-1-21 15:41

EXCEL_¨ç¼Æ°ÝÃD.rar (34.12 KB)

EXCEL_¨ç¼Æ°ÝÃD
¤U¸ü¦¸¼Æ: 8

2017-1-21 15:42

ML089

¦^´_ 1# jsc0518
=IF(B3=H3,"",IF(AND(B3="",H3<>""),"·s¼W®Æ¥ó",IF(AND(B3<>"",H3=""),"§R°£®Æ¥ó",IF(AND(LEFT(B3,13)=LEFT(H3,13),RIGHT(B3,1)<>RIGHT(H3,1)),"ª©¥»¤É¯Å",IF(AND(LEFT(B3,9)=LEFT(H3,9),RIGHT(B3,5)<>RIGHT(H3,5)),"§ó§ï®Æ¸¹","?")))))

jsc0518

¦^´_ 2# ML089


    ML089¡A·PÁ±z´£¨Ñªº¤½¦¡¡A§Ú·|¦n¦n¾Ç²ß¡CÁÂÁÂ!

ML089

¦^´_ 1# jsc0518

¦b²¤Æ¤@¤U¤½¦¡
=IF(AND(B3="",H3<>""),"·s¼W®Æ¥ó",IF(AND(B3<>"",H3=""),"§R°£®Æ¥ó",LOOKUP(,0/(LEFT(B3,{9,13,14})=LEFT(H3,{9,13,14})),{"§ó§ï®Æ¸¹","ª©¥»¤É¯Å",""})))

ML089

¦A²¤Æ

=IF(COUNTA(B3,H3)=1,IF(B3="","·s¼W®Æ¥ó","§R°£®Æ¥ó"),LOOKUP(,0/(LEFT(B3,{9,13,14})=LEFT(H3,{9,13,14})),{"§ó§ï®Æ¸¹","ª©¥»¤É¯Å",""}))

jsc0518

¦^´_ 5# ML089

½Ð°Ý --> (LEFT(B3,{9,13,14}) ³o¬qªº·N«ä¬O?
¥t¥~ { } ³o¬O¥Nªí?

jsc0518

¦^´_ 5# ML089


±z¦n,¥i¥H±Ð§Ú¤@¤U
LOOKUP(,0/(LEFT(B3,{9,13,14})=LEFT(H3,{9,13,14})),{"§ó§ï®Æ¸¹","ª©¥»¤É¯Å",""}
¥Îªk¤Î·N«ä¶Ü

hcm19522

http://blog.xuite.net/hcm19522/twblog/482139178

ML089

¦^´_ 8# jsc0518

½Ð°Ý --> (LEFT(B3,{9,13,14}) ³o¬qªº·N«ä¬O?
¥t¥~ { } ³o¬O¥Nªí?

{ } ¬O°}¦C¹Bºâ²Å¸¹¡ALEFT(B3,{9,13,14}) ªí¥Ü¬° LEFT(B3,9) ¡BLEFT(B3,13) ¡BLEFT(B3,14) ¤T­Ó°}¦C¶µ¥Ø

jsc0518

¦^´_ 5# ML089


ML089  ±z¦n

­Y¬O­n¦b·s¼W±ø¥ó¡A§Ú­n¦p¦ó§ó§ï¤½¦¡? (·PÁ±zªº±Ð¾É)
ª©¥»¤É¯Å  --> ³Ì«á¤@½X¤£¤@¼Ë¡A¥B³Ì«á¤@½X¼Æ¦r®t¶Z¬°+1
§ó§ï®Æ¸¹  -->  ³Ì«á¤@½X¤£¤@¼Ë¡A¥B³Ì«á¤@½X¨âµ§¼Æ¦r®t¶Z¦³2¥H¤W
§ó§ï®Æ¸¹  -->  «á­±¤E½X¤£¤@¼Ë
¦pªþ¹Ï

¤U¸ü (42.12 KB)
·s¼W¨â±ø¥ó

2017-1-23 20:03