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

[µo°Ý] ¦p¦ó§âEXCEL SHEEP¤¤ªº¤º®e·í¸ê®Æ®w¡A·j´M«á¦A¥á¦Ü­nªº¤À­¶¤¤?

[µo°Ý] ¦p¦ó§âEXCEL SHEEP¤¤ªº¤º®e·í¸ê®Æ®w¡A·j´M«á¦A¥á¦Ü­nªº¤À­¶¤¤?

¥»©«³Ì«á¥Ñ atuan207 ©ó 2011-4-24 15:50 ½s¿è

³Ìªñ¬Ý¨ì¦P¾ÇÀ°¥L¶ý°µ¤F¤@­Ó¤ñ¹ï²Î½sªºµ{¦¡
¤£¹L¥L¼g±o«Ü½ÆÂø(À³¸Ó»¡¬Oµù¸Ñ©M©w¸q³£¼g±o«Ü¤£²M·¡)
©Ò¥H¤£¤ÓÀ´¥L¦b¼gÔ£
¥L¬Osheet1¬O¿é¤Jªº¸ê®Æ¡Asheet2¬O¸ê®Æ®w
sheet3¬O¤ñ¹ï«áªºµ²ªG
¬Ý±o¥X¨Óªº¬O
¤ñ¹ïªº³¡¤À¥ÎÃþ¦ü¤E¤E­¼ªkªº¤èªk
¨â­ÓFOR°j°é¦b¶]
EX:
    For i = 2 To ROWCNT1
        Set a = Worksheets("Sheet1").Cells(i, 2)
        Worksheets("Sheet3").Cells(i, 1) = a
        c = 2
        Dim ROWCNT2 As Integer
        ROWCNT2 = Worksheets("Sheet2").Range("A2").CurrentRegion.Rows.Count
        Worksheets("Sheet3").Range("H3") = ROWCNT2 - 1
            
        For d = 1 To ROWCNT2
            Set b = Worksheets("Sheet2").Cells(c, 1)
            If a = b Then
                Set e = Worksheets("Sheet2").Cells(c, 2)
                Worksheets("Sheet3").Cells(i, 2) = e
            End If
            c = c + 1
        Next
    Next
End Sub


²{¦b¦b·Q
¦pªGsheet1·í¦¨³Ì«á­nªºµ²ªG

sheet2¡Bsheet3·í¦¨¸ê®Æ®w
sheet2.JPG
sheet3.JPG
µM«á§â2¡B3ªº¸ê®Æ¤ñ¹ï«á©ñ¦^¨ìsheet1

¤£ª¾¹D¸Ó«ç»ò°µ???
·Q½Ð±Ðª©¤Wªº¤j¤j

§Ú¥Ø«e¬O·Q¥ÎHsieh¤j¤j±Ðªº
Sheets(1).Range("A1").Copy Sheets(2).Range("B1")
¥Î³o­Ó¤è¦¡¨Ó½Æ»s¸ê®Æ¡A¦ý¬O·j´Mªº³¡¤ÀÁÙ¤£ª¾¹D«ç»ò°µ
¦Ó¥B¥Î³oºØ¤è¦¡½Æ»s¥u¦³³æµ§...
¦¨ÁZ¤Tµ§...¤£ª¾¹D«ç»ò°µ¤ñ¸û¦n

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-4-24 22:40 ½s¿è

Sheet2¡BSheet3Äæ¦ì¨S¦³ÃöÁp©Ê
µLªk¤ñ¹ï
¾Ç¥Í¸ò¾Ç¸¹¦p¦ó°t¹ï?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ atuan207 ©ó 2011-4-25 00:23 ½s¿è

¤£¹ï­C¡A§Úªº°ÝÃD¤£»Ý­nÃö³s
§Ú·Q§Ú¯uªº°ÝÃD°Ýªº¤£¦n¡A¤£¦n·N«ä

§Ú¬O·Q§âsheet2¡B3·í¦¨¸ê®Æ®w¡Asheet1ªºA¡BB¦æ¦U¦Û·í¦¨¤@­Ó¤ñ¹ï¸ê®Æªº­È¡A°µ¨â¦¸¤ñ¹ï¡A²Ä¤@¦¸¬O¹ïsheet1ªºA¡B±µµÛ¦A°µB
EX sheet2 ¨Ì¾Ú¬O±qsheet1 B¦æ §ä¨ì¬Û¦Pªº"©m¦W"«á §âsheet2  B¡BC¦æ¸ê®Æ°e¦^sheet1ªºC¡BD¦æ
     sheet3 ¨Ì¾Ú¬O±qsheet1 A¦æ §ä¨ì¬Û¦Pªº"¾Ç¸¹"§âsheet3 B¡BC¡BD¸ê®Æ°e¦^aheet1ªºE¡BF¡BG¦æ

³o¼Ë¸Ó«ç»ò°µ©O?
§Ú¥Ø«e¦Û¤v½®Ñ§ä¨ìªº¤è¦¡¬ORange("B2:C2").copy range("C2")
³o¼Ë¥u¯à¹ï³æµ§
¤£¹L°t¦X·j´M«á´N¤£ª¾¹D¸Ó«ç»ò°µ¤F

TOP

  1. Sub yy()
  2.     Dim c As Range, i%
  3.     With Sheet1
  4.         For i = 2 To .[a65536].End(3).Row
  5.             Set c = Sheet2.[a:a].Find(.Cells(i, 2))
  6.             If Not c Is Nothing Then
  7.                 .Cells(i, 3).Resize(, 2) = c(1, 2).Resize(, 2).Value
  8.             End If
  9.             Set c = Sheet3.[a:a].Find(.Cells(i, 1))
  10.             If Not c Is Nothing Then
  11.                 .Cells(i, 5).Resize(, 3) = c(1, 2).Resize(, 3).Value
  12.             End If
  13.         Next
  14.     End With
  15. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# atuan207
³o¬O¸ê®Æ®w«Ø¸m®É´N­n¦Ò¶q¡A
§Ú­Ìª¾¹D¾Ç¸¹·íµM¬O¤£­«½Æªº°ß¤@¯Á¤Þ­È
¦ý¬O¡A§A¦b­Ó¤H¸ê®Æ¤¤¥H©m¦W°µ¬°¯Á¤Þ­È
¦Ó¦P¦W¦P©m¬O¥i¯àµo¥Íªº°ÝÃD
¨ì®É­Ô´N·|²£¥Í¦P¦W¤£¦P¾Ç¸¹ªº±¡§Î
½Ð°Ý¨âªí¬O§_À³¸Ó«Ø¥ßÃöÁp©Ê¤~¯à¸Ñ¨M?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁÂoobird¤j¤j¡A
µ{¦¡¥i¥H°õ¦æ¡A¤£¹L±ßÂI·Q¦b½Ð±Ð¤@¤U¤¤¶¡ªº¼gªk

¤]·PÁÂHsieh¤j¤j
³o­Ó°ÝÃD°Ý±o¨S¿ù¡A§Ú½T¹ê©¿²¤¤FÀ³¸Ó­n§â©m¦W¤Î¾Ç¸¹°µÃö³s
¹J¨ì¬Û¦P©m¦W¡A¦ý¾Ç¸¹¤£¦Pªº¤H´N·|¦³°ÝÃD¡A
±ß¤@ÂI¨Ó«ä¦Ò¤@¤U¦n¤F
²{¦b¥¿¦b¦£µÛ§Ë®a¸ÌªºªF¦è¡A±ßÂI¦A¨Ó¦^

TOP

¥»©«³Ì«á¥Ñ atuan207 ©ó 2011-4-25 21:50 ½s¿è

oobird¤j¤j§Ú·Q½Ð±Ð¤@¤Uµ{¦¡
¦]¬°§Ú¥¿¦b¦Û¾ÇVBA¤¤¡A©Ò¥H¥i¯à¦³¨ÇÅܼƩM¥Îªk¤£¬O¨º»ò¼ô±x¡C
·Q¸ÕµÛ°µµù¸ÑÅý¦Û¤v§óÆ[©À¯à²M·¡
¤U­±§Ú¸ÕµÛµù¸Ñ¨Ó½Ð±Ð

Sub yy()
    Dim c As Range, i%
// ³o­Ói%¦³ÂI¤£¤ÓÀ´¡A¦ý¬O¸ÕµÛÅý¥L¿é¥X«á¡A¥i¥Hª¾¹D¬O§Ú¦³´X­Ó­È¡A¥¦«K·|¬O´X­Ó­È
(©Ò¥HÀ³¸Ó¬OºâÅܼƦ³´Xµ§¡A¨Ã©M¦ì¸m°µ³sµ²EX (i,2) ´N¬O(1,2)¡B(2,2)....¤@ª½¤U¥h)

   With Sheet1
// with¬O¦P¼ËªºªF¦è°µºë²¡A©Ò¥H«á­±¥u­n¬O"sheet1."¤°»òªº³£¥u­n¥Î".¤°»òªí¥Ü"

        For i = 2 To .[a65536].End(3).Row
//For ªì­È To ¨ì µ²§ôªº­È ¦ý¤£À´«á­±³o .End(3).Row³oÃä¬O°µ¤°»ò¥Îªº

            Set c = Sheet2.[a:a].Find(.Cells(i, 2))
//°t¦XNothingªº¥Îªk¡Aset  ½d³òA = ¬Û¥æªº½d³ò
//¤£ª¾¹D³o¼Ë¸ÑÄÀ¹ï¤£¹ï    sheet2[a:a] ¤¤ªº­È¡A¥h§ä(sheet1.cells(i,2))¤¤ªº­È
//¦ý¬Osheet2[a:a]  §Ú¤£À´[a:a]ªº¥Îªk

            If Not c Is Nothing Then
                .Cells(i, 3).Resize(, 2) = c(1, 2).Resize(, 2).Value
//¦pªG¨S¦³©Mcªº¤@¼Ëªºµ²ªG¡A
//¨º»ò±N§â­È©ñ¦^Sheet1ªº²ÄC¦æ¡A.Resize(, 2) ³o§Ú¤£À´¬O¤°»ò  ©Ò¥H«á­±´N¤£À´¤F

            End If
            Set c = Sheet3.[a:a].Find(.Cells(i, 1))
//¤ñ¹ï§¹Sheet2´«Sheet3

            If Not c Is Nothing Then
                .Cells(i, 5).Resize(, 3) = c(1, 2).Resize(, 3).Value
            End If
        Next
    End With
End Sub

¥H¤U¬O§â·Q°Ýªº·J¾ã¤@¤U
Q1:  i%ªº¥Îªk©M§Ú·Qªº¤@¼Ë¶Ü???¡A¨º"ÅܼÆ%"ªº¥Îªk¬O¤°»ò°Ú??
Q2:  .End(3).Row³oÃä¬O°µ¤°»ò¥Îªº???
Q3:  [a:a]ªº¥Îªk
Q4:  .Resizeªº¥Îªk
Q5:  °õ¦æ«á·|¥X²{³o¼ËªºÄµ§i¡A§Ú¸Ó¦p¦ó¦sÀÉ·|¤ñ¸û¦n?

¤j·§³o¤­­Ó°ÝÃD

§Ú¦Û¤v¦³µy·Lgoogle¤@¤U¡A¦ý¬O¹³Q4§ä¥X¨Óªºµ²ªG¬Ý¤FÁÙ¬O¤£¤ÓÀ´¡A
¦Ó¥B¤âÃ䪺®Ñ¥»¦n¹³¤]¨S¦³´£¨ì¡A©Ò¥H·Q½Ð±ÐªO¤Wªº¤j¤j

³Â·Ð¤F~~

TOP

¦^Hsieh¦Ñ®v¡A
¦Ñ®v»¡ªº¨S¿ù¡A¦³¨Ç¦W¤l¬Oµæ¥«³õ¦W¡A
¦b°µ¸ê®Æ®w¤ñ¹ïªº®É­Ô«Ü®e©ö·|³y¦¨»~§P¡A
©Ò¥H§Ú·Q¤]³\¤@¶}©l¸ê®Æ«Øªº®É­Ô´N¤£À³¸Ó§â©m¦W³æ¿W¤@­Ó·í¦¨ÃöÁä¦r¡A
­n§ä¤@¨Ç¨ä¥¦¥i¥H¿ë§Oªº¤@°_·íÃöÁä¨Ó°µ§PÂ_¡AEX¨­¤ÀÃÒ¦r¸¹+¦W¤l¡B©ÎªÌ¬O¾Ç¸¹+¦W¦r
¤~¤£·|Åý¸ê®Æ¦b¤ñ¹ï¤W³y¦¨§ó¦hªº°ÝÃD
ÁÂÁ¦Ѯvªº´£¿ô

TOP

¦^´_ 8# atuan207

°Ñ¦Ò¬Ý¬Ý
  1. Sub ex()
  2. With Sheet1
  3. For Each a In .Range(.[A1], .[a65536].End(xlUp))
  4.     Set b = Sheet2.Columns("A").Find(a, lookat:=xlWhole)
  5.     Set c = Sheet3.Columns("A").Find(a, lookat:=xlWhole)
  6.     a.Resize(, 7) = _
  7.     Array(a, b.Offset(, 1).Value, b.Offset(, 2).Value, b.Offset(, 3).Value, c.Offset(, 1).Value, c.Offset(, 2).Value, c.Offset(, 3).Value)
  8. Next
  9. End With
  10. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁÂHsieh¤j¤j
µ²ªG¦Ñ®v±o§óºë²
¥Î°}¦C¨Ó°µ¤]¬O­Ó¦n¤èªk
¥u¥i±¤°}¦Cªº¥Îªk§ÚÁÙ¤£¤Ó¼ô
¨ü±Ð¤F

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD