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

¤À²Õ«e¤T¦W»yªk

¤À²Õ«e¤T¦W»yªk

¤p§Ì¦³¤@­Ósqlite¸ê®Æ®w¡A·Q­n¤À²Õ¨ú«e¤T¦W¡A¨Ò¦p¬YªÑ²¼¡B¬Y¨é°Ó¡A³Ì¤j¶q«e¤T¦W¡A¦ý¸Ó¸ê®Æ®w¨Ã¤£¤ä´©¤À²Õ¨ú¤T¦Wªº»yªk¡A¥i¯à¬O¤p§Ì¥\¤O¤Ó®t
·Q³z¹Lexcel vba ¨Ó¨ú¥X­È¡A¤£ª¾¹D¦³¤°»ò¤è¦¡·|¤ñ¸û¾A¦X¡A¦pªG¤è«K¡A½Ð«üÂI¤@¤Uµ{¦¡½X

ªí¤W«e¥|Äæ¥i¥H³z¹Lsql¬d¸ß¡A±N¸ê®Æ©ñ¶i°}¦C¤¤¡A¦p

arr = cN.Execute(sql).GetRows

ªþ¥[ÀÉ®×¥u¦³¼Æ¤Qµ§¡A¨ä¹ê³æ¤é´N­nªñ¤T¤Q¸Uµ§¡A­Y¥¿½T¬d¥X¥H1000¦hÀɪѲ¼¥[¤W«e¤T¦W¡A¬d¸ßÁ`¼Æ·|¦³¬ù5000µ§¥ª¥k

­Y¬O¼Ï¶s¤ÀªRªº¤èªk¤]¦æ

ÁÂÁÂ


¤À²Õ«e¤T¦W.zip (7.04 KB)

¦^´_ 1# joey0415
sql¤À²Õ»yªk¡A¸Õ¬Ý¬Ý
  1. SELECT a.¤é´Á,a.ªÑ²¼,a.¨é°Ó,a.¼Æ¶q, COUNT(*) AS ±Æ¦W
  2. FROM ¸ê®Æªí as a
  3. INNER JOIN ¸ê®Æªí as b
  4. ON (a.ªÑ²¼=b.ªÑ²¼) AND (a.¼Æ¶q<=b.¼Æ¶q)
  5. GROUP BY a.¤é´Á,a.ªÑ²¼,a.¨é°Ó,a.¼Æ¶q
  6. HAVING COUNT(*) <=3
  7. ORDER BY a.ªÑ²¼,COUNT(*)
½Æ»s¥N½X
¤£¹L¤T¤Q¸Uµ§.....¡A§Ú¤£ª¾³o¼Ëªº»yªk®Ä²v¬O¤£¬O¨¬°÷ = =

TOP

¦^´_ 1# joey0415

¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim AR(), E As Variant, Rng As Range
  4.     With Sheets("Sheet1")
  5.         .Range("A1").CurrentRegion.Sort Key1:=.Range("B2"), Order1:=xlAscending, Key2:=.Range( _
  6.         "A2"), Order2:=xlAscending, Key3:=.Range("D2"), Order3:=xlDescending, Header:=xlYes
  7.         '.Range("A1").CurrentRegion.Sort"¸ê®Æªº±Æ§Ç
  8.         .UsedRange.Columns(2).AdvancedFilter xlFilterCopy, , .Cells(1, .Columns.Count), True
  9.         'UsedRange.Columns(2)ªº¶i¶¥¿z¿ï->  ¤£­«½ÆªºªÑ²¼
  10.         
  11.         '**¤T¤Q¸Uµ§¸ê®Æ ³o¨â¦æ·|ºC¤@ÂI
  12.         
  13.         AR = Application.Transpose(.Cells(1, .Columns.Count).CurrentRegion.Offset(1))
  14.         '¨ú±oªÑ²¼¸m¤J¤@ºû°}¦C¤¤
  15.         ReDim Preserve AR(1 To UBound(AR) - 1)          '§R°£³Ì«á¤@µ§ªº(ªÅ¥Õ)¸ê®Æ
  16.         .Cells(1, .Columns.Count).CurrentRegion.Clear   '²M°£
  17.         .Range("F1").CurrentRegion.Offset(1).Clear      '²M°£Åã¥Ü«e¤Tµ§¸ê®Æªº°Ï°ì
  18.         For Each E In AR
  19.             Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole)  '§ä¨C¤@ªÑ²¼ªº²Ä¤@­Ó¦ì¸m
  20.             .Range(.Cells(Rng.Row, "A"), .Cells(Rng.Row + 2, "D")).Copy .Cells(.Rows.Count, "F").End(xlUp).Offset(1)
  21.         Next
  22.     End With
  23. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# stillfish00

·PÁ¤j¤j´£¨Ñªº¤èªk¡A³o¼Ëªº¦ê±µªk¡A¦b¤pµ§¼Æ®É¡AÀ³¸ÓÁÙ¦æ¡A¦pªG¬O30¸Ux30¸U³o¼Ë¦ê´N¤£¤Ó¦n¡I

¥Ø«e¬O§Q¥ÎsqlŪ¥X30¸Uµ§¡A¥Îvba¥H°j°éªº¤è¦¡¡A¦pªG¬O«e¤T¦Wªº´N«O¯d¡A±q30¸Uµ§¤¤§ä¥X¨Ó©ñ¶iexcel¤¤¡A³o¼Ë¤£¥Î´X¬í

¥u¬O­Ó¤H°j°é¤è¦¡¼gªº¤£º}«G¡K
¥Ø«e¬Ý¨Ó¦b¸ê®Æ®w¤¤¦³¤À²Õ«á¨ú±Æ¦W­È¥\¯àªº
oracle
sql server
postgresql
¥»¨Ó·QÂà¹L¥h¥Î¡A¤£¹Lpostgresql³s±µvba
§ÚÁÙ¤£¦æ
©Ò¥H¥Ø«eÁÙ¥ý³o¼Ë¥Î

TOP

¥»©«³Ì«á¥Ñ joey0415 ©ó 2014-1-16 13:24 ½s¿è

¦^´_ 3# GBKEE

¶Wª©¤j±z¦n

±zªº¤èªk¥i¥H¥Î
¥i¥H¦A½Ð±Ð¤@ÂI¡I
sql¬d¸ß¥X¨Óªº¸ê®Æ¬O¤@­Ó¤Gºû°}¦C°}¦C¤¤²Ä¤@ºû©w¸qRecordsetªºÄæ¦ì¡]¸ê®Æ¦æ¡^¡A²Ä¤Gºû©w¸qRecordsetªº¸ê®Æ¦C¡C¦p¹Ï


½Ð¥Î¦³¤èªk¥i¥H±N¨º¨Ç¸ê®Æ¥H±zªº¤èªk©Î°j°éªº¤è¦¡¡A±N¤À²Õ«e¤T¦Wªº¸ê®Æ©ñ¶i¥t¤@­Ó·sªº¤@ºû°}¦C¤¤¶Ü¡H
§Ú·Q¬d¸ß¦b©ñ¦b°}¦C¡A¿z¿ï¤]¦b°}¦C¡A³Ì«áª½±µ¦b¼g¶i¸ê®Æ®wÀ³¸Ó·|¤ñ¶K¦bcells¤W·|§Ö¤~¹ï¡H

Äæ¦ì¦p¤U
¤é´Á¡AªÑ²¼¥N½X¡A¶R¶q¡A½æ¶q¡A²b¶R½æ

¥H¤U¬O§Ú¼gªº¡A¥i¯à·|¦³¨Ç°ÝÃD¡A¶Wª©ªº¨S¦³°ÝÃD¡I
ÁÂÁÂ
  1.     Worksheets("sheet2").Activate
  2. '    r = 2 '­pºâÁ`¦C¼Æ
  3.     a = 1 '¨ú­È«áÁ`¦C¼Æ
  4.     t = 1 '¨ú«e¤T¦W
  5.     sname = arr(1, r)
  6.     For r = LBound(arr, 2) To UBound(arr, 2)
  7.         If arr(1, r) = sname Then
  8.                 If t <= 3 Then
  9.                 Cells(a, 5) = arr(0, r)
  10.                 Cells(a, 6) = arr(1, r)
  11.                 Cells(a, 7) = arr(5, r)
  12.                 t = t + 1
  13.                 a = a + 1
  14.             End If
  15.         
  16.         Else
  17.             sname = arr(1, r)
  18.             
  19.                 Cells(a, 5) = arr(0, r)
  20.                 Cells(a, 6) = arr(1, r)
  21.                 Cells(a, 7) = arr(5, r)
  22.                 a = a + 1
  23.                 t = 2 '¦]¬°¶ñ¹L¤@¦¸¡A©Ò¥H³]¬°2
  24.         
  25.         End If
  26.     r = r + 1
  27.         
  28.     Next
½Æ»s¥N½X

TOP

¦^´_ 4# joey0415
¼Ï¯Ã¤ÀªRªºµ²ªG¡A ¦Ü©ó1103¦]¬°²Ä¥|¦W©M²Ä¤T¦W¼Æ¶q³£¬Û¦P¡A©Ò¥H³£·|Åã¥Ü
¥u¬O§Úª©¥»¤£¦P(excel2010) ¡A­Y¦s¬°xlsÀÉ·|¦³¬Û®e©Ê°ÝÃD©Ò¥H¤£¤W¶ÇÀɮפF¡C

TOP

¦^´_ 6# stillfish00
·PÁ¤À¨É¡A§Ú¸Õ¸Õ¬Ý
À³¸Ó¥i¥H¿ý¦¨¥¨¶°

TOP

¦^´_ 3# GBKEE


¶Wª©½Ð±Ð¤@¤U

Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole)  '§ä¨C¤@ªÑ²¼ªº²Ä¤@­Ó¦ì¸m

¬Ý¤£¥X­þ¤@¥y¬O§ä²Ä¤@­Ó¦ì¸m

¥Î¿ý»sªº¤]¦³LookAT:=xlWhole
¥HªÑ²¼¥N½X¬°¨Ò1101¦³¦n´X­Ó¡A¬°¤°»ò·|¸õ¹L©O¡H

ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-1-16 17:26 ½s¿è

¦^´_ 8# joey0415
  1. Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole)  '§ä¨C¤@ªÑ²¼ªº²Ä¤@­Ó¦ì¸m
½Æ»s¥N½X
¤w­­¨î¦bRange("B:B")¤¤,²Ä¤@¦¸·j´M·íµM·|±q²Ä¤@­Ó(1101)§ä°_
LookAT:=xlWhole :»PÀx¦s®æªº¤º®e§¹¥þ¬Û¦P ¬O 1101 ¤£¬O A1101B
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ joey0415 ©ó 2014-1-16 20:33 ½s¿è

¦^´_ 9# GBKEE

ÁÂÁ¶Wª©¡A¤j·§À´¤F¡A¤p§Ì¬O¥HF8ªº¤è¦¡¡A¥[¤W§Y®É¹Bºâµøµ¡»P°Ï°ìÅܼƵøµ¡Æ[¹î¬Ý¥X¨Óªº

½Ð°Ýª©¤j¡Aª©¤j±`±`¨Ï¥ÎWITH¨Ó¼gµ{¦¡¡A¤§«á³£¥[¤W¤@­Ó  .    ´N¦æ¡A¥i¬O¦³¨Ç®É­Ô¡A¥[ÂI«á¡AVBA·|¶]¥X¤@¨Ç¬ÛÃöªºµ{¦¡½XÅý§A¿ï¨ú¡A¦³¨Ç®É­Ô¤£·|¡A³o¼Ë­n«ç»ò¼Ë¨Ó°£¿ù©O¡H

ª©¤jªº¤å³¹¤]±`¥Î .End(xlUp).Offset(1)¤§Ãþªº©w¦ì§ä¬Y¤@®æ©ÎÃöÁ䪺¤W¤@­Ó©Î¤U¤@®æ¡A¤£ª¾¹D­þ¦³¬ÛÃöªº»¡©ú·|²M·¡¤@¨Ç

¤p§Ì¶R¤F«Ü¦hVBAªº®Ñ¡A¦ý¦³®É­ÔÁÙ¬O«Ü§Æ±æ¯à¦³Ãþ¦ü«ö¤@­ÓÂI´N¦³¬ÛÃöªºµ{¦¡½X¥i¥H¿ï¥Î¡A¤£ª¾¹D­n«ç»ò¼g©Î³]©w¤~·|¤@ª½¥X²{©O¡H

ÁÂÁÂ

TOP

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