[µo°Ý] Ãö©óVLOOKUPªº¥Îªk.½ÐÀ°¦£

kan109

bookeasy_01.rar (164.11 KB)

bookeasy_01
¤U¸ü¦¸¼Æ: 28

2011-10-16 20:26

ªþ¥óbookeasy_01.rar ¤º
¥HSheet1 AÄæ A1-A2000 ²£«~½s¸¹
Sheet2 A1-H1589 ¬°²£«~ºØÃþ¥HA-H DPXXX/DP1XXX , DP2XXX,DP3XXX,DP4XXX,DP5XXX,DP6XXX,DP7XXXDPCXXXX ¶¶§Ç±Æ¦C
¥HB5¬°¨Ò,¤p§Ì±q¥H«eªºµo°Ý¤¤,§Q¥Î
=IF(ISNA(VLOOKUP($A5,Sheet2!$A$1:$H$1600,2,FALSE)=TRUE),"",VLOOKUP($A5,Sheet2!$A$1:$H$1600,2,FALSE))
¶¶§Q±Æ¥X
A5¬°DP1383 B5¬°DP21383 C5-H5 ¦]¨S¦³¥i¥Î²£«~½s¸¹¬G¥X²{0
°ÝÃD
¥HSheet1 A31   DP3753/2C, ©óSheet2 ¤º 507¦C ,°£¤FC507 = DP3753/2C , ÁÙ¦³B 507 D507 ¥i¥Î
§Q¥Î¨ººØ¤½¦¡¥i¥H¹ê²{
Sheet1 B31 = Sheet2 A507
Sheet1 C31 = Sheet2 B507
Sheet1 D31 = Sheet2 C507
Sheet1 E31 = Sheet2 D507
Sheet1 F31 = Sheet2 F507
¨ä¾lªº³£¸ò³oºØ±Æ¦C??

onegirl0204

¦^´_ 1# kan109


¤£ª¾¬°¦ó§AªºSHEET1ªºA31ªº²£«~¥N¸¹¦bSHEET2ªºAÄæ¸Ì§ä¤£¨ì¡H¦]¦¹§Q¥ÎVLOOKUP¤½¦¡·íµM´NµL®ÄÅo¡I
©_§×ªº¬O¬°¦óSHEET1ªºA31»P¨ä¥L¤£¦P¡A¥¦«o¬O¹ïÀ³¨ìSHEET2ªºBÄæ©O¡H©ÎªÌ§Aªº¤½¦¡À³¸Ó¬OSHEET2ªºAÄæ§ä¤£¨ì¸ê®Æ®É¡A§ï¬°´M¨DSHEET2ªºBÄæ©O¡H

kan109

¦^´_  kan109


¤£ª¾¬°¦ó§AªºSHEET1ªºA31ªº²£«~¥N¸¹¦bSHEET2ªºAÄæ¸Ì§ä¤£¨ì¡H¦]¦¹§Q¥ÎVLOOKUP¤½¦¡·íµM´N ...
onegirl0204 µoªí©ó 2011-10-16 22:10

    ¦]¬°Sheet1 A31 ªº²£«~¥N¸¹¦b Sheet2 C507. §Ú§Æ±æ¹ê²{¨ìSheet1 B31-H31 =Sheet2 B507-H507
¦p¦¹Ãþ±À!!·PÁ§AªºÀ°¦£!!

man65boy

¦^´_ 1# kan109

B5=IF(A5="","",INDEX(Sheet2!A:H,SUMPRODUCT((Sheet2!$A$1:$H$1600=Sheet1!$A5)*ROW(Sheet2!$A$1:$H$1600)),SUMPRODUCT((Sheet2!$A$1:$H$1600=Sheet1!$A5)*COLUMN($A:$H))-1))

参¦Ò¥Î:¦]Àɮ׸ê®Æ¸ûÃe¤j¡A¹Bºâ³t«×ÅܺC¡A
«Ý°ª¤âÀ°¦£¸ÑÃD­×¥¿!!!

kan109

¦^´_  kan109

B5=IF(A5="","",INDEX(Sheet2!A:H,SUMPRODUCT((Sheet2!$A$1H$1600=Sheet1!$A5)*ROW(She ...
man65boy µoªí©ó 2011-10-16 23:21

¦n~!­ø¸Ó~!ÁÂÁÂ!!½Ð°Ý¥i¥H¸ÑÄÀ¤@¤U¶Ü~!?·Q¤F¸Ñ§ó¦h~!!ÁÂ

chin15

³o¬O¥Î°}¦C¤½¦¡¨D±oSheet2!$A$1:$H$1600¤¤­È»PSheet1!$A5¬Ûµ¥ªº¦C¸¹
sumproductÄÝ°}¦C¤½¦¡¡A¦b¤j¶qÀx¦s®æ¨Ï¥Î®É·|­n«Üªø®É¶¡ªº­«ºâ¡C
³Ì¦n¦b¸ê®Æ¶q«Ü¤p©Î¼È®É©Êªº¤u§@®É¤~¥Î¤½¦¡³B²z¡C

kan109

³o¬O¥Î°}¦C¤½¦¡¨D±oSheet2!$A$1H$1600¤¤­È»PSheet1!$A5¬Ûµ¥ªº¦C¸¹
sumproductÄÝ°}¦C¤½¦¡¡A¦b¤j¶qÀx¦s®æ¨Ï ...
chin15 µoªí©ó 2011-10-17 08:29

    ½Ð°Ý¦p»Ý­n¦b¤j¶qÀx³Æ®æ¤¤¨Ï¥Î¡AÀ³¨Ï¥Î¦óºØ¤½¦¡¡H20000 ¦Cªº¸ê®Æ¡I

oobird

³o¼Ëªº¸ÜÁÙ bookeasy_01.rar (45.59 KB)

¤U¸ü¦¸¼Æ: 16

2011-10-17 14:03

¬O«Øij¥ÎVBA§a¡C

kan109

³o¼Ëªº¸ÜÁÙ¬O«Øij¥ÎVBA§a¡C
oobird µoªí©ó 2011-10-17 14:04

    Sub test()
    Set d = CreateObject("Scripting.Dictionary")
    rng = Sheet2.UsedRange
    For i = 1 To UBound(rng)
        For j = 1 To UBound(rng, 2)
            If rng(i, j) <> "" Then
                If Not d.exists(rng(i, j)) Then
                    d(rng(i, j)) = i
                End If
            End If
        Next
    Next
    With Sheet1
        For i = 1 To .[a65536].End(3).Row
            If d("" & .Cells(i, 1)) <> "" Then
                .Cells(i, 2).Resize(, 7) = Sheet2.Cells(d("" & .Cells(i, 1)), 2).Resize(, 7).Value
            End If
        Next
    End With
End Sub

½Ð°Ý¥i¥H¦V¤p§Ì¸ÑÄÀ¤@¤U¶Ü??

oobird

http://forum.twbts.com/thread-20-1-1.html°Ñ¦Ò³o¤@½g»¡©ú