[µo°Ý] ½Ð±Ð¦p¦ó¨Ï¥Î vlookup¤½¦¡¤W¤UÂù¦V¬d¸ß?

peter460191

½Ð°Ý¦U¦ì¹F¤H¦p¦ó¨Ï¥Î vlookup¤½¦¡¤W¤UÂù¦V¬d¸ß¡A
¦p¤U¹Ï¤¤¤ÎªþÀɤº§Q¥ÎD3¤ÎD4¨â­ÓÀx¦s®æ¡AÂù¦VÀ˯ÁAÄæ¤ÎBÄ檺¸ê®Æ¡AÁÂÁ¤j®a!

¤U¸ü (17.18 KB)

2013-2-18 13:07

Book1.rar (1.41 KB)

¤U¸ü¦¸¼Æ: 13

2013-2-18 13:07

ML089

¦^´_ 1# peter460191

D3 ¿é¤J 1101©Î¥xªd
D4 ¤½¦¡ =IF(ISNA(MATCH(D3,A:A,)),"",INDEX(B:B,MATCH(D3,A:A,))) & IF(ISNA(MATCH(D3,B:B,)),"",INDEX(A:A,MATCH(D3,B:B,)))

hcm19522

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

yangsap

ÁÂÁ´£¨Ñ                              

peter460191

¦^´_ 7# hcm19522

ÁÂÁÂhcm19522 ´£¨Ñªº¤½¦¡,¥i§_ªñ¤@¨B±Ð¾É¦p¦ó¨Ï¥Î?¬Oª½±µ±N¤½¦¡¿é¤J¦bD5¤ÎD6ªºÀx¦s®æ¤¤¶Ü?ÁÂÁ±z!

hcm19522

=VLOOKUP(OFFSET(A$3,INT((ROW(A1)-1)/2),),A$3:B$9,MOD(ROW(A1)-1,2)+1,)
=OFFSET(A$3,INT((ROW(A1)-1)/2),MOD(ROW(A1)-1,2))

peter460191

Âù¦V¬d¸ß.zip (18 KB)

¤U¸ü¦¸¼Æ: 4

2015-12-29 15:47

[attach]23007[/attach]¦^´_ 4# Hsieh


   ¨D±Ï Hsieh ¶W¯Åª©¥D,¤§«e±zÀ°¦£¸Ñµª©Ò´£¨ÑªºVBAµ{¦¡,§Ú«áÄò¨Ï¥Î¤Wµo²{µLªk§PÂ_­^¤å¤j¤p¼g,§Ú§ï¤F«Ü¤[³£µLªk¦¨¥\,¦]¦¹·Q¦A¦V±z¨D±Ï,¤£ª¾±z¦³¨S¦³¿ìªk¸Ñ±Ï¤@¤U,«D±`ÁÂÁ±z~~
        
       *°ÝÃDÂI : 1-ªþ¥ó¤¤D5Àx¦s®æ¦V¤U¬d¸ß®É,¦p¹J§À¼Æ­^¤å¬Û¦PªÌ,µLªk§P§O­^¤å¤j¤p¼g,¬Ò·|§ì¨ú²Ä¤@­Ó¸ê®Æ
                        2-ªþ¥ó¤¤D6Àx¦s®æ¦V¤W¬d¸ß®É,«h¥i¥H¥¿±`¨Ï¥Î

[attach]23007[/attach][attach]23007[/attach][attach]23007[/attach]

peter460191

¦^´_ 4# Hsieh


    ÁÂÁ¶W¯Åª©¥Dªº«ü¾É¡A·|¥J²Ó¬ã¨s¦¹¥¨¶°ªº¼gªk¡AÁÂÁ±z!

Hsieh

¦^´_ 3# peter460191

³o¬O­n¥ÎVBA¨Ó¼g¡A¤@¯ë¤½¦¡¬O¤£¥i¯à¹F¦¨ªº
¤u§@ªí¼Ò²Õ

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Dim A As Range
   
3. If Intersect(Target, [D3:D4]) Is Nothing Then Exit Sub
   
4. Application.EnableEvents = False
   
5. Set A = Columns("A:B").Find(Target, lookat:=xlWhole)
   
6. If A Is Nothing Then MsgBox "¿é¤J¿ù»~": [D3:D4] = "": GoTo 10
   
7. [D3] = Cells(A.Row, 1).Value
   
8. [D4] = Cells(A.Row, 2)
   
9. 10
   
10. Application.EnableEvents = True
    
11. End Sub

½Æ»s¥N½X

peter460191

¦^´_ 2# Hsieh

ÁÂÁª©¥Dªº«ü¾É¡A¦]¬°§Ú¬Ý¹L½×¾Â¤W¦³¥ª¥k¨â­Ó¥i¥H¤¬¬Û¬d¸ß¡A§Ú·Q§â¥L§ï¦¨¤W¤U¡A§ï¤F¦n¤[³£§ï¤£¹L¨Ó¡A½Ð°Ýª©¥DÁÙ¦³¨ä¥L¤èªk¶ý?
   

http://forum.twbts.com/viewthread.php?tid=4102&from=favorites