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

[µo°Ý] ¤½¦¡¦p¦óºë²

[µo°Ý] ¤½¦¡¦p¦óºë²

¦U¦ì«e³Æ¡A³ÌªñÀ°ªB¤Í»s§@excelªí®æ¥H«K¤½¥q²Î­p¤Î¦C¦Lªí®æ¨Ï¥Î¡A¦ý³Ìªñµo²{¤F¤@¨Ç°ÝÃD¡A
¹³¬O¥»¨Ó¼gªº¤½¦¡¦p¤U¡G
{=IFERROR(INDEX(°õ¦æ¤¤!D:D,SMALL(IF(°õ¦æ¤¤!$A$1:$A$9999="V1",ROW($A$1:$A$9999),""),ROW(1:1))),"")}
¥»¨Ó¥H¬°ªB¤Íªº¸ê®ÆÀ³¸Ó¤£·|¶W¹L¤@¸U¡A¥u¼g¨ì1~9999¡Fµ²ªGªB¤Í³qª¾§Ú¡A¥Ø«e²Î­p¸ê®Æ¤w¸g¶W¹L¤@¸U¤G¤dµ§¡A«ùÄòÂX¤j¤¤
©ó¬O§Ú§ï¤F¤@¤Uµ{¦¡
{=IFERROR(INDEX(°õ¦æ¤¤!D:D,SMALL(IF(°õ¦æ¤¤!$A$1:$A$20000="V1",ROW($A$1:$A$20000),""),ROW(1:1))),"")}
µ²ªG³y¦¨¿é¤J¼Æ¾Ú®É­pºâ¶q¹L¤j¡A¦]¬°¥H¤Wªºµ{¦¡¾ã­Óexcel®t¤£¦h¦³5000­Ó¥ª¥k¡A
´Nºâ§â¦Û°Ê­pºâÃö±¼¡A¦sÀÉÁÙ¬O­nªá¬ù5¤ÀÄÁ­«·s­pºâ¤@¦¸¤~¦¨¦sÀÉ¡A
¤£ª¾¬O§_¦³«e½ú¥i¥H«ü¾É¤@¤U«ç»ò§â¤½¦¡Â²¤Æ¡A§Ú¬Ý¤F¤@°ï¸ê®Æ¡AÀY§Ö·w¤F:dizzy:

«Øij¥Îvba
  1. Sub ex()
  2. Dim Ar() As String, s, a, time
  3. Application.Range("A:D").SortSpecial key1:=Range("D:D")
  4. 'ÅýA:Dªº¸ê®Æ®Ú¾ÚDªº¤j¤p¡A¥Ñ¤p¦Ü¤j±Æ¦C
  5. s = WorksheetFunction.CountIf(Range("A:A"), "V1")
  6. '­pºâ¦³´X­ÓV1,V1¶W¹L65535´N·|¿ù»~
  7. ReDim Ar(0 To s) As String
  8. With Sheets(1)
  9.    s = 0
  10.    For Each a In .Range(.[D1], .[D1].End(xlDown))
  11.       If a.Offset(, -3) = "V1" Then
  12.       
  13.       Ar(s) = a.Value
  14.       s = s + 1
  15.       End If
  16.          
  17.    Next
  18.    .[F1].Resize(s, 1) = Application.Transpose(Ar)
  19.    '§â¸ê®ÆÀx¦s¦bF1
  20. End With

  21. End Sub
½Æ»s¥N½X
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦U¦ì«e³Æ¡A³ÌªñÀ°ªB¤Í»s§@excelªí®æ¥H«K¤½¥q²Î­p¤Î¦C¦Lªí®æ¨Ï¥Î¡A¦ý³Ìªñµo²{¤F¤@¨Ç°ÝÃD¡A
¹³¬O¥»¨Ó¼gªº¤½¦¡ ...
phoenix1 µoªí©ó 2014-5-19 16:09



20000®æ¤½¦¡¡A¨C®æ¤½¦¡³£­n±Æ¦C¤@¦¸(20000®æ)¡A·íµM«ÜºC
¥i¥H±Ä¥Î°Ï°ì°}¦C¤è¦¡¥[³t

1. ¿ï¾Ü­n¿é¤J¤½¦¡ªº°Ï°ì(Àx¦s®æ½d³ò)
2. ¥H°}¦C¤½¦¡¤è¦¡¿é¤J¤U¤è¤½¦¡
{=IFERROR(INDEX(D:D,SMALL(IF($A$1:$A$20000="V1",ROW($A$1:$A$20000),""),ROW(1:20000))),"")}
3. ¥H§Úªº¹q¸£¡A¬ù¥i¥H¥[§Ö5­¿
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 3# ML089


    ¤j¤j§A¦n¡A§Úµo²{§A¸ò§Úªº¤½¦¡¥u®t¦b³Ì«áªºrow(1:20000)¡A½Ð°Ý¤@¤U³o¸ò§Ú¼gªºrow(1:1)¦³¤°»ò®t§O¶Ü?

TOP

«Øij¥Îvba
softsadwind µoªí©ó 2014-5-20 14:59



    ¤j¤j§A¦n¡A¦]¬°VBA¤p§Ì§Ú¹ê¦b¤£¤Ó·|¥Î¡A³o­ÓVBA·|Åý­ì¨Ó¼Æ¾Úªº¶¶§Ç¶]±¼¶Ü?
¦]¬°§Ú¥u¬O­n¦A¥t¥~¤@­Ó¤À­¶Åã¥Ü¦³V1°O¸¹ªº¼Æ¾Ú¡A¨Ã¨S¦³­n§ó´«­ì¼Æ¾Úªº¶¶§Ç¡A
©êºp§Úªº°ÝÃD¦³ÂI¦h.XD

TOP

¦^´_ 5# phoenix1
  1. Sub ex()
  2. Dim Ar() As String, s, a, time

  3. s = WorksheetFunction.CountIf(Range("A:A"), "V1")
  4. '­pºâ¦³´X­ÓV1,V1¶W¹L65535´N·|¿ù»~
  5. .Range("F:F").Clear
  6. '²MªÅF:Fªº¸ê®Æ
  7. ReDim Ar(0 To s) As String
  8. With Sheets(1)
  9.    s = 0
  10.    For Each a In .Range(.[D1], .[D1].End(xlDown))
  11.       If a.Offset(, -3) = "V1" Then
  12.       
  13.       Ar(s) = a.Value
  14.       s = s + 1
  15.       End If
  16.          
  17.    Next
  18.    .[F1].Resize(s, 1) = Application.Transpose(Ar)
  19.    '§â¸ê®ÆÀx¦s¦bF1
  20. End With

  21. End Sub
½Æ»s¥N½X
©ñ¦b¦P¤@­¶ªºF1, ¶¶§Ç¤£ÅÜ°Ê
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_  ML089


    ¤j¤j§A¦n¡A§Úµo²{§A¸ò§Úªº¤½¦¡¥u®t¦b³Ì«áªºrow(1:20000)¡A½Ð°Ý¤@¤U³o¸ò§Ú¼gªºrow(1: ...
phoenix1 µoªí©ó 2014-5-26 14:33



    ½Ð°Ñ¦Ò3¼Ó»¡©ú¡A¸Õ¥Î«á¤~ª¾¹D¦³µL®t§O?
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤H­n¦Û·R¡A¤~¯à·R´¶¤Ñ¤Uªº¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD