½Ð°Ý¥Î¦Û­q²M³æ¡A¨Ó¶i¶¥¿z¿ï¥X¸ê®Æ?

gaishutsusuru

¤U¸ü (29.49 KB)

2019-5-7 23:46

¦bÀx¦s®æG1¡A¬O¦Û­q²M³æ¡C

·Q­n±N¥ªÃ䪺¸ê®Æ¡A¨Ì¦Û­q²M³æ¡A¶i¶¥¿z¿ï¦¨¥kÃ䪺¸ê®Æ¡A½Ð°Ý­n¦p¦ó¤~¯à°µ¨ì©O?


ÁÂÁ¤j®aªº¨ó§U¡C·PÁ¡C

­ã´£³¡ªL

¥i¥H¥Î[¶i¶¥¿z¿ï]
Sub ±ø¥ó¿z¿ï()
Dim cRNG As Range
[I:J].Clear
Set cRNG = Range([G1], [G65536].End(xlUp))
If cRNG.Count = 1 Then Exit Sub
Range("A:B").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=cRNG, _
     CopyToRange:=Range("I1:J1"), Unique:=False
End Sub

gaishutsusuru

¦^´_ 3# rouber590324


±z¦n¡A§Ú±N±z´£¨Ñªº¥¨¶°½X½Æ»s«á¡A­n°õ¦æ®É¡A·|¥X²{¿ù»~¡G424¡A¦¹³B»Ý­nª«¥ó¡C

½Ð°Ý¦p¦ó³B²z©O¡AÁÂÁÂ

gaishutsusuru

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


    ÁÂÁ±zªº¨ó§U¡C

­ã´£³¡ªL

¦^´_ 5# gaishutsusuru


°}¦C¤½¦¡(¤TÁ䧹¦¨)
=INDEX(A:A,SMALL(IF(COUNTIF($G:$G,$A$2:$A$99),ROW($2:$99),4^8),ROW(A1)))&""
¥k©Ô/¤U©Ô
µù:¦¹¤½¦¡¦pªG¬Ò¬°[¤å¦r®æ¦¡]

©Î:
=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF($G:$G,$A$2:$A$99),ROW($2:$99)),ROW(A1))),"")

gaishutsusuru

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

±z¦n¡A¤£¦n·N«ä¡C¦]¬°¤W¦¸¨Sª`·N¬Ý­n¥ÎÀ£ÁYÀɤW¶Ç¡A¥H¬°¬O¤£¯à¤W¶Ç¡C

ªþ¤WÀɮסA¦A³Â·Ð±z»P¦U¦ì¤j¤j¨ó§U¡A·PÁ¡C

    ¬¡­¶Ã¯2.zip (7.71 KB)

¤U¸ü¦¸¼Æ: 4

2019-5-11 22:15

­ã´£³¡ªL

¦^´_ 1# gaishutsusuru


¤W¶ÇÀɮ׬O³Ì°ò¥»ªº, ¤j®a¤£·Q¬°¤F¸ÑÃDÁÙ¥tªá®É¶¡¥h«Ø¼Ë¥»ÀÉ~~

rouber590324

Sub robert()
Sheet1.[K265536].ClearContents
X = Sheet1.[A65536].End(xlUp).Row
Y = Sheet1.[K65536].End(xlUp).Row
For M = 2 To X
For K = 2 To 10
If Sheet1.Cells(M, 1) = Sheet1.Cells(K, 7) Then
  Sheet1.Cells(Y + 1, 11) = Sheet1.Cells(M, 1)
  Sheet1.Cells(Y + 1, 12) = Sheet1.Cells(M, 2)
    Y = Y + 1
  End If
Next
Next
End Sub

rouber590324

¦p¤U
°õ¦æ  robert  §Y¥i


Sub robert()
Application.Run "robert1"
Application.Run "robert2"
End Sub

Sub robert1()
Sheet1.[K265536].ClearContents
X = Sheet1.[A65536].End(xlUp).Row
Y = Sheet1.[K65536].End(xlUp).Row
For M = 2 To X
If Sheet1.Cells(M, 1) = Sheet1.Cells(2, 7) Then
  Sheet1.Cells(Y + 1, 11) = Sheet1.Cells(M, 1)
  Sheet1.Cells(Y + 1, 12) = Sheet1.Cells(M, 2)
    Y = Y + 1
  End If
  Next
End Sub

Sub robert2()
X = Sheet1.[A65536].End(xlUp).Row
Y = Sheet1.[K65536].End(xlUp).Row
For M = 2 To X
If Sheet1.Cells(M, 1) = Sheet1.Cells(3, 7) Then
  Sheet1.Cells(Y + 1, 11) = Sheet1.Cells(M, 1)
  Sheet1.Cells(Y + 1, 12) = Sheet1.Cells(M, 2)
    Y = Y + 1
  End If
  Next
End Sub