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

½Ð±Ð¤U©Ô¦¡¿ï³æªº°ÝÃD

½Ð±Ð¤U©Ô¦¡¿ï³æªº°ÝÃD

½Ð±ÐEXCEL¤U©Ô¦¡¿ï³æ¡A
²Ä¤@¼h¥i¿ï¥Ò¡B¤A¡B¤þ¡B¤B¡A
²Ä¤G¼h¨Ì·Ó²Ä¤@¼h©Ò¿ïªº±ø¥ó¥i¿ï¾Ü¡G
¥Ò-A1¡BA2¡BA3¡BA4
¤A-B1¡BB2¡BB3¡BB4
¤þ-C1¡BC2¡BC3¡BC4
¤B-D1¡BD2¡BD3¡BD4
¥H¤W¤w»s§@§¹¦¨¡A¨Ã¥i°õ¦æ¡C

¦p²Ä¤@¼h¿ï¥Ò¡A²Ä¤G¼h«h¿ïA1¡C
¦p¦A¦^²Ä¤@¼h±N(¥Ò)§ï¿ï¦¨(¤A)¡A
¨S¦A°µ²Ä¤G¼h¿ï¾Üªº°Ê§@«h²Ä¤G¼hªºA1¨Ã¤£·|ÅÜ°Ê¡A¦p¥¼ª`·N¥i¯à³y¦¨¸ê®Æ¿ù»~¡A
½Ð±Ð¦p¦ó»s§@²Ä¤@¼h»P²Ä¤G¼h¸ê®Æ¿ù»~®ÉªºÅã¥Ü(´£¥Ü)¡A
©Î­×§ï²Ä¤@¼h®É²Ä¤G¼h¤]·|Åܦ¨ªÅ¥Õ¡C
Áٽаª¤â¸Ñµª¡AÁÂÁ¡C

1.rar (3.47 KB)

TOP

¦bªü¦N¤jªº³¡¸¨®æ¬ã¨s¤F¤@¤U¤ÈÁÙ¬O§ä¤£¨ìµª®×¡A
À³¸Ó¬O¥\¤O¤Ó²L¤F¡A
ÁÙ¬O·PÁªü¦N¤jªº¼ö¤ß¦^´_¤F¡A
¦A§ä§ä¨ä¥Lªº¤èªk¤F¡C

TOP

¦^´_ 1#barry2266b  ½Ð±Ð¦p¦ó»s§@²Ä¤@¼h»P²Ä¤G¼h¸ê®Æ¿ù»~®ÉªºÅã¥Ü(´£¥Ü)¡A
¿ï©w A1:B1 ½d³ò
«ü¥O->³]©w®æ¦¡¤Æ±ø¥ó    ¤½¦¡=ISERROR(MATCH($B$1,INDIRECT($A$1),0))
¦p¹Ï:

©Î­×§ï²Ä¤@¼h®É²Ä¤G¼h¤]·|Åܦ¨ªÅ¥Õ¡C     ­n°Ê¨ìVBA
½Ð¬Ý°w¹ï A1:B1 ½d³ò ªºµ{¦¡
Sheet1(¤u§@ªí1) ªºµ{¦¡½X ¦p¤U
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Application.EnableEvents = False
  3. If Application.IsError(Application.Match(Range("B1"), Sheet2.Range([A1]), 0)) Then [B1] = ""
  4. Application.EnableEvents = True
  5. End Sub
½Æ»s¥N½X

TOP

«z¡I²×©ó¦³°ª¤â¥X¤â¬Û§U¤F¡A¤Ó°ª¿³¤F¡A
¥[¤J³o­Ó½×¾Â¯u¦n¡A¦³¦n¦hªF¦è¥i¾Ç¤F¡C
¦A½Ð±Ð¡A
¥H³]©w®æ¦¡¤Æ±ø¥ó±a¤J¤½¦¡¡A¦p¾ãÄæ³£»Ý­n¦³¦¹´£¥Üªº¥\¯à­n¦p¦ó°µ?
¥Î©ì¦²ªº¦n¹³¤£¦æ¡A¦p­n¤@®æ¤@®æªº­×§ï³]©w¦n¹³«Ü²Â¤Sªá®É¶¡¡A
µæ³¾µê¤ß½Ð±Ð¤F¡A·PÁ°ª¤â¦^ÂСC

TOP

¥H³]©w®æ¦¡¤Æ±ø¥ó±a¤J¤½¦¡¡A¦p¾ãÄæ³£»Ý­n¦³¦¹´£¥Üªº¥\¯à­n¦p¦ó°µ?
...
barry2266b µoªí©ó 2010-8-4 21:41



¿ï¨úÄæ½d³ò,¦b®æ¦¡¤Æ±ø¥ó¿é¤J¤½¦¡
ª`·N $ µ´¹ï²Å¸¹ªºÀ³¥Î
¿ï¨ú½d³ò¤@¦¸§¹¦¨³]©w
¾÷¾¹¤H¤T¤j©w«ß-«OÅ@¤HÃþ¡BªA±q©R¥O¡B«OÅ@¦Û¤v
§Úªº¤T¤j©w«ß-¥Í©R¡B¥Í¦s¡B¥Í¬¡

TOP

¦^´_ 6# asimov


ÁÂÁ¦U¦ì°ª¤â¼ö¤ß¦^ÂСA
ªí®æ°µ¦n«á¤Sµo²{°ÝÃD¤F¡A
¥H³]©w®æ¦¡¤Æ±ø¥ó±a¤J¤½¦¡«á¡A
¥¼¶ñ¿ïªºÀx¦s®æ§e¬õ¦â(ªí¥Ü¸ê®Æ¿ù»~)¡A
¦p¦ó°µ¤~¯à¨Ï¥¼¶ñ¿ïªºÀx¦s®æ§e¥Õ¦â©O?
·PÁ¸ѵª¡C

Â^¨ú.JPG (20.02 KB)

Â^¨ú.JPG

TOP

¦^´_ 7# barry2266b
=OR(AND($A1<>"",ISERROR(MATCH($B1,INDIRECT($A1),0))),AND($A1="",$B1<>""))
¦h½d³ò³]©w®æ¦¡¤Æ±ø¥ó ­nª`·N    ¤W­±ªº¤½¦¡  §@¥Î¤¤ªºÀx¦s®æ¦b A1©ÎB1  ²Ä¤@¦C
¦p   §@¥Î¤¤ªºÀx¦s®æ¦b A5©ÎB5 ¬°²Ä¤­¦C   ­n§ï¦¨¤U­±ªº¤½¦¡
=OR(AND($A5<>"",ISERROR(MATCH($B5,INDIRECT($A5),0))),AND($A5="",$B5<>""))
¥H¦¹Ãþ±À

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-8-5 19:27 ½s¿è

¦^´_ 7# barry2266b
ÂI¿ï¦bB1
©w¸q¦WºÙX
=OFFSET(¤u§@ªí3!$A$1,MATCH(¤u§@ªí1!$A1,¤u§@ªí3!$A:$A,0)-1,1,,5)

¿ï¨úBÄæ
³]©w®æ¦¡¤Æ±ø¥ó
=(B1<>"")*ISERROR(MATCH(B1,X,0))
®æ¦¡±N¦r«¬ÃC¦â³]¬°¸ò©³¦â¬Û¦P   
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

½×¾Â¸Ì¯u¬O°ª¤â¦p¶³¡A
§Ú·QÀ³¸Ó§Ö­n¹F¨ì§Ú·Q­nªºªF¦è¤F¡A
·Pı¶V°µ¶V½ÆÂø¤]¶V¦³¿³½ì¤F¡C
·PÁ¦U¦ì¤£§[«ü¾É»P¸Ô²Ó¸Ñ»¡¡A
¤p§ÌÁÙ­nªáÂI®É¶¡¬ã¨s¡B®ø¤Æ¤@¤U¡A
²¦³º¥\¤OÁÙ¬O¤Ó²L¤F¡A
¦pªGÁÙ¦³°ÝÃDÁٽЦU¦ì«e½ú¤£§[«ü¾É¡C
¯u§Æ±æ§Ú¥H«á¤]¦³¯à¤O¦b½×¾Â¸ÌÀ°¦P¾Ç¦^µª°ÝÃD¡C
ÁÂÁ¤F¡C

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD