register313

¦^´_ 1# luke

ª½±µ¥Î¤u§@ªí¨ç¼Æ,°Ñ¦Ò¥Î

F1=IF(A1="","",A1)  ¥k©Ô¤U©Ô

H1=IF(C1="","",IF(ISNA(INDEX(sheet2!$A$1:$V$16,MATCH($A1&$B1,sheet2!$D$1:$D$16&sheet2!$E$1:$E$16,0),MATCH(C1,sheet2!$A$1:$V$1,0))),C1,INDEX(sheet2!$A$1:$V$16,MATCH($A1&$B1,sheet2!$D$1:$D$16&sheet2!$E$1:$E$16,0),MATCH(C1,sheet2!$A$1:$V$1,0))))
°}¦C¤½¦¡   ¥k©Ô¤U©Ô

register313

¦^´_ 5# luke

°}¦C¤½¦¡
¿é¤J¦n¤½¦¡ ­n«ö²Õ¦XÁäCtrl+Shift+Enter¨Ó§@½T©w
­ì¤½¦¡ªº«e«á·|¦Û°Ê¥[¤W{ }

register313

¦^´_ 8# luke

©ó¤u§@ªí¨Ï¥Î°}¦C¤½¦¡µo²{¤@­Ó°ÝÃD "­Y¸ê®Æ¶W¹L1¸Uµ§®É,¹q¸£ÅܺC¶]¤£°Ê"
¨º¥ÎVBAªº¤è¦¡¼g°}¦C¤½¦¡©ó¤u§@ªíÁÙ¤£¬O¤@¼Ë,´«´ö¤£´«ÃÄ
=>³£¬O¥Î¤F°}¦C¤½¦¡

¨Ï¥Î2# Hsieh¶Wª©ªºVBAµ{¦¡§a(¤@¯ë¨Ï¥ÎVBA¤ñ¸û¤Ö¦b¤u§@ªí¼g¤J¤½¦¡)

register313

¦^´_ 10# luke

"¸ô¤H¥Ò"¦h¼L
¥ý´N¨Æ½×¨Æ
1.  2#  Hsieh¶Wª© ¤w¥ÎVBA¦^µª±z 1#¤§´£°Ý
      ­Y¥\¯à¦³¿ù»~©Î¤£²Å±zªº»Ý¨D
      ´NÀ³§Y®É¤ÏÀ³,¦Ó±z¦b4#¬OµªÂÐ"´ú¸ÕOK"
2.  ½×¾Â¤W¶Wª©,ª©¥D¦bEXCEL¤@¯ë°Ï,µ{¦¡°Ï¤W¤§¥\¤OµL±e¸mºÃ
     "¸ô¤H¥Ò"¤£¾á¤ß¶Wª©,ª©¥D¦^µª¤£¥X°ÝÃD,¥u­n¶Wª©,ª©¥D¦³®É¶¡,µo°ÝªÌªº´£°Ý¯àÅý¶Wª©,ª©¥D¤F¸Ñ¥\¯à»Ý¨D,³£¬Oªá¤F®É¶¡,ºÉ¤ßºÉ¤Oªº¦^µª
     "¸ô¤H¥Ò"¦b·Nªº¬Oµo°ÝªÌªº´£°Ý ÃD¥Ø¤£²M·¡ ¤Ó竉²Î ¤£ªþÀÉ®× ¤Ï¤ÏÂÐÂÐ
3. µo©««á,¨C¤@¼h¼Ó¤§´£°Ý,µªÃD³£À³¦³¤@©wªº¶¶§Ç
    ¤£µM·|Åý¬Ý©«ªº¤H»~¸Ñ,¤]·|¨S¦³®Ä²v

register313

¦^´_ 10# luke

¥Î¤FÂù¼h°j°é,°õ¦æ®Ä¯à¤£¨Î(­Y¸ê®Æ¦³¤W¸Uµ§,§ÚªºÂù®Ö¹q¸£¶]¤F¬ù20¬í)
°Ñ¦Ò¥Î

1. Sub zz()
   
2. Application.ScreenUpdating = False
   
3. With sheet1
   
4. .Columns("A:B").Copy .Columns("F:G")
   
5. .Columns("H:I") = ""
   
6. Max = sheet2.[F1].End(xlToRight).Value
   
7. For Each S In .Range(.[A1], .[A1].End(xlDown))
   
8.   For Each T In sheet2.Range(sheet2.[D2], sheet2.[D2].End(xlDown))
   
9.     If S & S.Offset(0, 1) = T & T.Offset(0, 1) Then
   
10.        If Val(S.Offset(0, 2)) <= Max And Val(S.Offset(0, 3)) <= Max Then
    
11.           S.Offset(0, 7) = sheet2.Cells(T.Row, S.Offset(0, 2) + 6)
    
12.           S.Offset(0, 8) = sheet2.Cells(T.Row, S.Offset(0, 3) + 6)
    
13.        End If
    
14.        Exit For
    
15.     End If
    
16.   Next
    
17.   If S.Offset(0, 7) = "" Then S.Offset(0, 2).Resize(1, 2).Copy S.Offset(0, 7)
    
18. Next
    
19. End With
    
20. Application.ScreenUpdating = True
    
21. MsgBox "°õ¦æ§¹²¦"
    
22. End Sub

½Æ»s¥N½X