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

¦p¦óÅý®Æ¸¹¯à°÷¦Û°Ê§P©w¨Ã½Æ»s¥h«ü©wªº¤u§@ªí

¦p¦óÅý®Æ¸¹¯à°÷¦Û°Ê§P©w¨Ã½Æ»s¥h«ü©wªº¤u§@ªí

½Ð±Ð¤@­Ó°ÝÃD,§Ú¨C¤Ñ³£­n¶]¤@­Ó³øªí,¦Ó³o­Ó³øªí§Ú¨C¤Ñ³£­n°µ¤@¨Ç°Ê§@
(1)¥²¶·±NEC¤ÎAC¶}ÀYªº®Æ¸¹"¾ã¦C¸ê®Æ"¦s¦Ü¥t¤@­Ó¤u§@ªí1,¨Ã±N­ì©lªº¤u§@ªí¤¤ªº®Æ¸¹¾ã¦C§R°£
(2)¥²¶·±NEA¶}ÀYªº®Æ¸¹"¾ã¦C¸ê®Æ"¦s©ñ¦Ü¤u§@ªí2,¨Ã±N­ì©lªº¤u§@ªí¤¤ªº®Æ¸¹¾ã¦C§R°£
(3)³Ñ¾lªº®Æ¸¹«h¦b­ì©l¤u§@ªí¤W,²Ä1»P2§R°£ªºªÅ¥Õ¦C¦Û°Ê§R°£

¥Ø«e¹J¨ì°ÝÃD¬O§Ú1.2¤£·|¼gÅý¥L¦Û°Ê§P©w,vb¤]¬Oªì¾ÇªÌ
¤]¬d¹L³\¦hºô­¶¤Î¬Ý¤F«Ü¦h°Q½×,³£¨S¦³¬ÛÃöªº°Q½×
©Ò¥H·Q±Ð°ª¤â­Ì,¸Ó¦p¦ó¼g³o°Ï¶ô,ÁÂÁÂ

´ú¸Õ.rar (8.53 KB)

¦^´_ 1# cclo0728

Sub test_20190702()
    Sheets("Sheet1(¦Û°Ê¼W¥[¨Ã©ñ¸mAC¤ÎEC®Æ").Select
    ROW1 = Cells(Rows.Count, "C").End(3).Row
    If ROW1 > 2 Then
        Range(Cells(1, "C"), Cells(ROW1, "E")).Clear
    End If
   
    ROW1 = Cells(Rows.Count, "A").End(3).Row
   
    arr = Range("A2:A" & ROW1)
   
    ROW2 = Sheets(1).Cells(Rows.Count, "A").End(3).Row
   
    Sheets(1).Range("A1:C" & ROW2).AdvancedFilter _
        Action:=xlFilterCopy, CriteriaRange:=Range("A1:A" & ROW1), CopyToRange:=Range( _
        "C1"), Unique:=False
   
    Columns("C:C").ColumnWidth = 14
    Columns("D:D").ColumnWidth = 16
    Columns("E:E").ColumnWidth = 8
   
    '==============================================================
    Sheets("Sheet2(¦Û°Ê¼W¥[¨Ã©ñ¸mEA¤ÎEB®Æ¸¹").Select
   
    ROW1 = Cells(Rows.Count, "C").End(3).Row
    If ROW1 > 2 Then
        Range(Cells(1, "C"), Cells(ROW1, "E")).Clear
    End If

    Range("A1").Select
   
    ROW1 = Cells(Rows.Count, "A").End(3).Row
   
    brr = Range("A2:A" & ROW1)
   
    Sheets(1).Range("A1:C" & ROW2).AdvancedFilter _
        Action:=xlFilterCopy, CriteriaRange:=Range("A1:A" & ROW1), CopyToRange:=Range( _
        "C1"), Unique:=False
    Columns("C:C").ColumnWidth = 14
    Columns("D:D").ColumnWidth = 16
    Columns("E:E").ColumnWidth = 8

    Sheets.Add After:=Sheets(Sheets.Count)
    Columns("A:A").ColumnWidth = 14
    Columns("B:B").ColumnWidth = 16
    Columns("C:C").ColumnWidth = 8
   
    Sheets(1).Range("A1:C" & ROW2).Copy Range("A1")
   
    For i = ROW2 To 2 Step -1
        
        For j = 1 To UBound(arr)
            If Cells(i, "A") Like arr(j, 1) Then
                Rows(i).Delete
                GoTo 1100
            End If
        Next
        For j = 1 To UBound(brr)
            If Cells(i, "A") Like brr(j, 1) Then
                Rows(i).Delete
                GoTo 1100
            End If
        Next
1100:
   
    Next

End Sub
´ú¸Õ.zip (20.84 KB)

TOP

¦^´_ 2# kim223824
·PÁ«e½ú,«e´X¤Ñ¤w¸g¬Ý¨ì¦^ÂФF,¦³¨Ç¦a¤è¤£À´
¦ý³o´X¤Ñ¦£©ó½LÂI,¤§«á¦A½Ð±Ð,ÁÂÁÂ

TOP

¦^´_ 3# cclo0728

¥Ø«eÀ°§A¦Ò¼{ªº¤º®e¦p¤U:
1. §@·~¤è¦¡¬O¥Î EXCEL¤¤ªº  "¶i¶¥¿z¿ï"¡A±N§A­nªº®Æ¸¹©ïÀY ¿ï¥X¡C
2. «ü©w®Æ¸¹ªº­¶¼Æ¥u¦³©T©w¨â­¶(­¶­±¦WºÙ¤£¥i§ó§ï)¡C
3. ¦ý¬O«ü©w®Æ¸¹ªº¤º®e¥i¯à·|ÅÜ (EA ==> EZ)¡A©Ò¥H¥u­n¦bAÄæ KEYIN§A­nªº®Æ¸¹©ïÀY´N¥i¥H¦³·sªº®Æ¸¹ ex:  EZ*

TOP

²¤Æ¤@¤Uµ{¦¡½X:
Sub test_20190702_1()
Dim i%, j%, xR As Range
Sheets(1).[A:C].Copy Sheets(4).[C:E] '½Æ»s¥þ³¡¸ê®Æ¦ÜSheet4(³Ñ¾l®Æ¸¹)
For i = 2 To 3
    With Sheets(i)
        .[C:E].Clear '²M°£­ì¦³¸ê®Æ
         Set xR = Range(.[A1], .Cells(Rows.Count, 1).End(xlUp)) '¶i¶¥¿z¿ï·Ç«h½d³ò
         Sheets(1).[A:C].AdvancedFilter Action:=xlFilterCopy, _
              CriteriaRange:=xR, CopyToRange:=.[C1], Unique:=False  '¶i¶¥¿z¿ï½Æ»s
    End With
    For j = 2 To xR.Count
        Sheets(4).[C:C].Replace xR(j), "", Lookat:=xlWhole '¨Ì¿z¿ï·Ç«h¤å¦r, ±NSheet4®Æ¸¹¨ú¥N¬°ªÅ¥Õ
    Next j
Next i
On Error Resume Next '²¤¹Lµ{¦¡¿ù»~¦Ó¤£¤¤Â_
Sheets(4).[C:C].SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'Sheet4 ©w¦ìCÄæ[½s¿è>¨ì>ªÅ¥Õ®æ]¨Ã§R°£, §Y¬°³Ñ¾l®Æ¸¹
On Error GoTo 0 '«ì´_µ{¦¡¿ù»~ÀË´ú»Pĵ§i
End Sub

Xl0000400.rar (14.78 KB)


=====================================

TOP

¦^´_ 5# ­ã´£³¡ªL


    ·PÁ¡AÅý§Ú¤S´£¤É¤F¡C

TOP

¦^´_ 5# ­ã´£³¡ªL
¨â¦ì«e½ú¥Îªº¤Ó¯«¤F~~~
§Ú³£¬Ý¤£À´§A­Ì©Ò¼gªº:( :( :(
¥Ø«e¤@ª½¤ÏÂЬݵ۩p­Ì©Ò¼gªºµ{¦¡,Ãä¼gÃäGOOGLE¬d¸ßµ{¦¡»¡©ú
¦A³Â·Ð±Ð¾Ç¤@¦¸§Ú±N¤½¥q¨C¤é·|¶]ªº³øªí(¥¼§R°£Äæ¦ìª©¸É±H¤@¦¸)¦A³Â·Ð¦U«e½ú­Ì
§Ú»¡©ú¤@¤U³oÀɮתº¤º®e
1.²Ä¤@­Ó¤u§@ªí(COPR172019070300070320190703000)³o­Ó¬OERP¨t²Î³øªí¦Û°Ê²£¥ÍªºÀɦW¨C¤Ñ¦W¤l³£¤£¤@¼Ë,©Ò¥H¶]¥X¨Óªº®É­Ô­ì©lÀÉ¥u·|¦³"¤@­Ó¤u§@ªí",«á­±¼W¥[ªº¤u§@ªí³£¬O¤â°Ê¼W¥[
1-1.Äæ¦ì¥u­n¬OÂŦ⪺°Ï¶ô,³£¬O¤£»Ý­n¨Ï¥Îªº,©Ò¥H·|§R°£¥ú¥ú<=³o¬q§Ú·|¥Î¥¨¶°¿ý»s,¥i¥H¶¶§Q¸Ñ¨M

2.DÄæ·|¤â°Ê´¡¤J¤@Äæ(¦p¶À¦â°Ï¶ô,D1¦r¬°"¶V«n¤å")<=³o¬q§Ú·|¥Î¥¨¶°¿ý»s,¥i¥H¶¶§Q¸Ñ¨M
2-1.²Ä2­Ó¤u§@ªí(¤¤¶V½Ķ)¬O§Ú³oÃä"·s¼W"ªº,¸Ì­±¬O¦]¬°­n¥ÎVLOOKUP¤ñ¹ï"«È¤á¦WºÙ"­nµ¹¥~³Ò¬Ýªº<=³o¬q§Ú·|¥Î¥¨¶°¿ý»s¤Î¨ç¼Æ,¥i¥H¶¶§Q¸Ñ¨M

3.EC&AC(³o­Ó¤u§@ªí¤]¬O¤â°Ê·s¼Wªº)
3-1.­n±N²Ä¤@­Ó¤u§@ªí¤¤ªº,EC¤ÎAC¶}ÀY®Æ¸¹²¾¦Ü¦¹¤u§@ªí

4.EA&EB(³o­Ó¤u§@ªí¤]¬O¤â°Ê·s¼Wªº)
4-1.­n±N²Ä¤@­Ó¤u§@ªí¤¤ªº,EA¤ÎEB¶}ÀY®Æ¸¹²¾¦Ü¦¹¤u§@ªí

5.¨ä¥L(³o­Ó¤u§@ªí¤]¬O¤â°Ê·s¼Wªº)
5-1.³Ñ¾lªº®Æ¸¹²¾¦Ü³o¤@Äæ

6.³]©w¦C¦L½d³ò¦Û°Ê¦C¦L<=³o¬q§Ú¤Wºô¬d¸ß¹L­n¦p¦ó¨Ï¥Îµ{¦¡¸Ñ¨M,¦ý¬O¨C­Ó¤u§@ªí³£¦U¦Û¦C¦L(°£¤F²Ä¤@­Ó¨t²Î²£¥Íªº,¨ä¾lAC&EC,EA&EB,¨ä¥L)¦A¤@°_®É§Ú¬O¨S¸ÕÅç¹L

3-5³o¬q§Ú¤ñ¸û¤£À´ªº¬O,«e½ú­Ì¬O¦p¦ó¥h§P©wªº(²³æ»¡´N¬Oªì¾ÇªÌ¬Ý¤£¤ÓÀ´¤@°ïµ{¦¡»y¨¥)
§Ú¬Ý¤Fkim(¬O¥Î¤u§@ªí¤¤ªº¦U®Æ¸¹¶}ÀY¦¬´M),¦ý¬Ý¤F­ã´£³¡ªLªº§Ú´N¤S§ó¤£À´¤F(ÁöµM®ÇÃä³£¦³¤¤¤å»¡©ú,¦ýµ{«×¤Ó®z¨S¿ìªk²z¸Ñ)
¨ÌK¤j¤§«e³]©wªº²Ä¤@­Ó¤À­¶³]­Ó¥¨¶°«ö¶s,´N¥u­n±N¨C­Ó¤u§@ªí¤º®e§R°£,«ö­Ó«ö¶s´N¥i¥H°õ¦æ
¦ý¤p§Ì§Ú·Q¤F¸Ñ³o¶ô§P©w­n¦p¦ó°õ¦æ

¨C¤é¥X³f¹w­pªí.rar (14.75 KB)

TOP

¦^´_ 4# kim223824

1. §@·~¤è¦¡¬O¥Î EXCEL¤¤ªº  "¶i¶¥¿z¿ï"¡A±N§A­nªº®Æ¸¹©ïÀY ¿ï¥X¡C
2. «ü©w®Æ¸¹ªº­¶¼Æ¥u¦³©T©w¨â­¶(­¶­±¦WºÙ¤£¥i§ó§ï)¡C====>³o­Ó¬O§Ú¶]§¹³øªí¤â°Ê¼W¥[ªº
3. ¦ý¬O«ü©w®Æ¸¹ªº¤º®e¥i¯à·|ÅÜ (EA ==> EZ)¡A©Ò¥H¥u­n¦bAÄæ KEYIN§A­nªº®Æ¸¹©ïÀY´N¥i¥H¦³·sªº®Æ¸¹ ex:  EZ*

ÁÂÁÂKIM¤j,§Ú¦b¥h¤F¸Ñ¤@¤U,§A¸ò¥t¤@¦ì¤j¤jªº»yªk

TOP

¦^´_ 4# kim223824
½Ð°Ý¥i¥H±Ð§Ú³o¬q¬O
arr = Range("A2:A" & ROW1)
¦]§Ú¬darr,google¬O¬d¥X¨Ó¬Oarray°}¦Cªº·N«ä,¦ý³o­Óªº¥Îªk¬Ý¤£¤ÓÀ´
If ROW1 > 2 ThenÁÙ¦³³o¬q
¦A³Â·Ð«ü¾É¤@¤U

TOP

¦^´_ 9# cclo0728

   
     ROW1 = Cells(Rows.Count, "C").End(3).Row           'ª¾¹DCÄæ³Ì«á¤@­Ó¦³¸ê®Æªº¦ì¸m
     If ROW1 > 2 Then                                                            '¦pªGROW1 >2 ªí¥Ü §AªºCÄ榳¸ê®Æ
          Range(Cells(1, "C"), Cells(ROW1, "E")).Clear                '±NCÄæ~EÄæ³£²M°£
     End If
     '==============================
     
     ROW1 = Cells(Rows.Count, "A").End(3).Row          'ª¾¹DAÄæ³Ì«á¤@­Ó¦³¸ê®Æªº¦ì¸m
     arr = Range("A2:A" & ROW1)      '±NAÄ檺¸ê®Æ©ñ¶iarrªº°}¦CÅܼƸ̭±

TOP

        ÀR«ä¦Û¦b : ÀR§¤±`®¦¤v¹L¡B¶¢½Í²ö½×¤H«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD