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

filter«¬ºA¤£²Å¦X

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-1 02:48 ½s¿è

¦^´_ 1# ssooi


¦]¬°§AªºFilter ¥Îªk¿ù°Õ~~

²Ä1­Ó°Ñ¼Æ­n©ñ¤J "°}¦C"¡A²Ä2­Ó°Ñ¼Æ­n©ñ¤J "¦r¦ê"

ªì¾ÇªÌ½Ðµ½¥Î "F1" ¨Ó¬d¥Îªk



Filter¥Îªk.png
2020-8-1 02:43



©Ò¥H¨Ì§Aªºµ{¦¡  Filter(a,all(i)) ­n§ï¦¨ Filter(all,a)

­è¦n¦³­Ó½d¨Ò¥i¥H¥ÎFilter¥Ü½dµ¹§A¬Ý¡A¦p¤UÃìµ²


http://forum.twbts.com/thread-8370-1-1.html

¤£¹L§Aªºµ{¦¡¤£¥u³o­Ó°ÝÃD......

¦³¤£¤Ö»yªk¿ù»~¡AÁÙ¦³ÅÞ¿è°ÝÃD......

§AªºIF¥i¯à¥Ã»·¶i¤£¥h......¶i¥h¤F¤]·|°õ¦æ¥¢±Ñ

¦A¥[ªo§a!
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-1 23:56 ½s¿è

¦^´_ 8# ssooi

®¥³ß®¥³ß¡A¤j·§¯à²q¥X§A­Ì¬O¥Î2­Ó°j°é°µ¤ñ¹ïªº¼gªk~

¤âÄo¤F¤@¤U¡A§Ú¤]¤½§G§Úªº¼gªk§a¡A¥u¦³"¤@­Ó°j°é"

¦pªG¥Î·Ç¤j´£¨ìªº ¤u§@ªí"¿z¿ï"¥\¯à¡AÀ³¸Ó·|¼gªº§ó²³æ

¨C­Ó¤À­¶¡A§Ú¦Û¤v¦h½Æ»s¤F"¼ÐÃD¦C"¡A³o¼Ë·Pı¤ñ¸û¥¿±`~

³o­ÓÃD¥Ø¥ÎG¤j³ßÅw¥ÎªºMatch¨ç¼Æ´N¥i¥H»´ÃP¸Ñ¨M¤F

¦pªG§A­Ì·Q­nÅýµ{¦¡§ó¥[§Ö¬½·Ç¡AMatch¨ç¼Æ­È±o§Aªá®É¶¡¬ã¨s¤@¤U¥Îªk

µ{¦¡¦p¤U


Sub Test0801()
All = Array("¤T°Ó", "»·ªF", "«H¸q")
Rn = [A1].End(4).Row
For R = 1 To Rn
  B = Application.Match(Cells(R, 2), All, 0)
  If Not IsError(B) Then '¦aÂI¬O§_¦bAll°}¦C¤º
    With Sheets(Cells(R, 2).Value)
      Ro = .Cells(Rows.Count, 1).End(3).Row '§ä¨C­¶³Ì¥½¦C
      If Ro = 1 And .[A1] = "" Then Range([A1], [A1].End(2)).Copy .[A1]     '½Æ»s¼Ð´£¦C
      Ro = Ro + 1: Range(Cells(R, 1), Cells(R, 1).End(2)).Copy .Cells(Ro, 1) '½Æ»s¸ê®Æ
    End With
  End If
Next
End Sub


Àɮצp¤U~

Test0801.rar (17.84 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-2 01:20 ½s¿è

¦^´_ 5# °a¤ªºµ

¤H®a»¡¥L¦¨¥\¤F¡A¤£¹L¨S¶Kµ{¦¡µLªkÅçÃÒ

¦ý¬O§Aªºµ{¦¡ÁÙ¨S¦¨¥\­ò¡A¦]¬°§A¨C¦¸³£§âE­«¸m¬°2¡A

¦A°õ¦æE=E+1¡A©Ò¥HE³Ì¦h=3 (²Ä3¦C)

¹J¨ì¦P¤@­Ó¦aÂI¦³3µ§¸ê®Æ¥H¤W¡A

§A·|¤@ª½Âл\¦U¤À­¶ªº²Ä3¦C(²Ä2µ§¸ê®Æ)

§A¥i¥H§â¸ê®Æ¿é¤J¦h¤@ÂI¸Õ¸Õ¡A¦p¤U¹Ï

½ÐÄ~Äò¥[ªo~  


123.png
2020-8-2 01:18
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-2 12:07 ½s¿è

¦^´_ 12# °a¤ªºµ

¶¶«K½Ð°Ý¬°¬Æ»ò¨Ï¥Î                 
D = Array(K(J, 1), K(J, 2), K(J, 3))
Sheets(K(J, 2)).Rows(E) = D
ªº®É­Ô·|¥X²{ #N/A


Sheets(K(J, 2)).Rows(E) = D

Rows¥Nªí¾ã¦C¡A¦Ó§AªºD°}¦C¥u¦³3µ§¸ê®Æ¡A©Ò¥H¨S¸ê®Æªº³¡¤À´NÅܦ¨"#N/A"

À³¸Ó¦p¦ó¼g ¤~¤£·|¥X²{ #N/A

µ¥¸¹«e­±½d³ò¥u¦³3Äæ¦Ó¤£¬O¾ã¦C©Ò¦³Äæ¡A´N¤£·|Åܦ¨#N/A

Sheets(K(J, 2)).Cells(E, 1).Resize(, 3) = D

¨Ì°}¦C¸ê®Æµ§¼Æªº¼u©Ê¼gªk (§Aªº°}¦C±q0¶}©l¡A©Ò¥H­n¥[1)

Sheets(K(J, 2)).Cells(E, 1).Resize(, UBound(D) + 1) = D
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-2 16:38 ½s¿è

¦^´_ 14# °a¤ªºµ


Äæ ¸ò ¦C  ªº°ÝÃD §Ú¬O³o¼Ë¼g

¦pªG§A¤£¦Ò¼{"°õ¦æ®Ä²v"ªº¸Ü¡A§Aªºµ{¦¡¦b¥\¯à¤W¬O¨S¦³°ÝÃDªº

½×¨ì"°õ¦æ®Ä²v"·Ç¤j¤~¬O¦æ®a¡A§Ú¤]¥u¬O¤@¯ë¤ô·Ç

·Ç¤j¼gªºµ{¦¡¦b"°õ¦æ®Ä²v"¤W¡A³£¦³«D±`°ªªº¤ô·Ç!

¤£¹L·Ç¤j¦³®É·|¬°¤F°õ¦æ®Ä²v¡A§âµ{¦¡¼gªº¤ñ¸û¶¡A¨S¨º»òª½Ä±¡A

·s¤â¯uªº¤£©ö¬ÝÀ´¡A©Ò¥H¤£«Øij·s¤â¥h¬Ý·Ç¤jªºµ{¦¡   

³o»P§Úªº¼g§@²ßºD¤£¤Ó¤@¼Ë¡A§Ú»{¬°¼gª½Ä±¡Bª½±µ¤@ÂI¤ñ¸û¦n¥Î©óºûÅ@(­Y¥H«áµ{¦¡»Ý­n§ó·s¡B­×§ï)

©Ò¥H§Ú»{¬°§Úªºµ{¦¡À³¸Ó¬O¤ñ¸û"¿Ë¥Á"¤@ÂI~ ·íµM¤£¥Nªí·s¤â´N¯à¬Ý±oÀ´


½Ð°Ý ¨º¦pªG­nÅý  A = Array("¤T°Ó", "»·ªF", "«H¸q")  Åܦ¨°ÊºA¦³¼u©Ê ¸Ó¦p¦ó¼g©O?

­n¥ý¤UÅÞ¿è¡A°£¤F¥»¨­¤u§@ªí¥H¥~ªº¤u§@ªí³£¯Ç¤JA°}¦C¡A¥i¥H³o¼Ë¼g

Dim A(): K = -1
For Each sh In Sheets
  If sh.Name <> ActiveSheet.Name Then
    K = K + 1: ReDim Preserve A(K)
    A(K) = sh.Name
  End If
Next
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-2 16:49 ½s¿è

¦^´_ 15# n7822123

¤W­±¬O"°ÊºA°}¦C" ªº¼gªk¡A¦ý³o¤£¬O§Úªº­·®æ

§Úªº²ßºD¬O¥Î "°ÊºA¦r¦ê"¡A¦AÂà°}¦C¡A¦Û¥Ñ«×§Úı±o§ó°ª(A¬J¥i¥H¬O¦r¦ê¡A¤]¥i¥H¬O°}¦C)

¦p¤U¡A¬Ý§A³ßÅw­þ¤@­Ó


Dim A
For Each sh In Sheets
  If sh.Name <> ActiveSheet.Name Then A = A & "," & sh.Name
Next
A = Split(Mid(A, 2), ",")
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

        ÀR«ä¦Û¦b : ¦³´¼¼z¤~¯à¤À¿ëµ½´c¨¸¥¿¡F¦³Á¾µê¤~¯à«Ø¥ß¬üº¡¤H¥Í¡C
ªð¦^¦Cªí ¤W¤@¥DÃD