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

¨â²Õ¼Æ­È¥h°£­«½Æªº¸ê®Æ«á±Æ§Ç

¨â²Õ¼Æ­È¥h°£­«½Æªº¸ê®Æ«á±Æ§Ç

¥»©«³Ì«á¥Ñ henry860608 ©ó 2023-3-19 20:31 ½s¿è

¤wª¾A¡BB¨â¦C¦³¨â²Õ¼Æ­È¡A·Q­n³z¹L¤½¦¡±N¨â²Õ¼Æ­È¦³­«½Æªº³¡¤À¥h°£¡A¨Ã±N¸ê®Æ¨Ì¼Æ­È¤j¤p±Æ§Ç(¦p¤U­±C¦C)

A        B
1        1
3        4
6        7
9        9
12        10
15        14
18        16
21        18
24        22
27        37
30        46
33        57
36        80


C
1
3
4
6
7
9
10
12
14
15
16
18
21
22
24
27
30
33
36
37
46
57
80

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-20 12:04 ½s¿è

[attach]35988[/attach]¦^´_ 1# henry860608


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P±¡¹Ò
«á¾Ç½m²ß°}¦C»P¦r¨åªº¸Ñ¨M¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, Y, C%, R&
'¡ô«Å§iÅܼÆ:(Brr,Y)¬O³q¥Î«¬ÅܼÆ,C¬Oµu¾ã¼Æ,R¬Oªø¾ã¼Æ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
[C:C].ClearContents
'¡ô¥OCÄæÀx¦s®æ¤º®e²M°£
Brr = Range([B1], Cells(Rows.Count, "A").End(3))
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,
'¥H[B1]¨ìAÄæ³Ì«á¦³¤º®eÀx¦s®æ­È±a¤J

For C = 1 To 2
'¡ô³]¶¶°j°é!C±q1¨ì 2
   For R = 1 To UBound(Brr)
   '¡ô³]¶¶°j°é!R±q1¨ì Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
      Y(Brr(R, C)) = ""
      '¡ô¥OR°j°é¦CR°j°éÄæBrr°}¦C­È·íkey,item¬OªÅ¦r¤¸,¯Ç¤JY¦r¨å¸Ì
      '­Ykey­«½Æ¥u¯d¤@µ§

   Next
Next
With [C1].Resize(Y.Count, 1)
'¡ô¥H¤U¬OÃö©ó[C1]Àx¦s®æÂX®i¦V¤U(Y¦r¨åkey¼Æ¶q)¦Cªº¬ÛÃöµ{§Ç
   .Value = Application.Transpose(Y.Keys)
   '¡ô¥OÀx¦s®æ­È¥H Y¦r¨åkeyÂà¸m«á­È±a¤J
   .Sort KEY1:=.Item(1), Order1:=1, _
   Header:=0, Orientation:=1
   '¡ô¥O¥H[C1]§@¬°±Æ§Ç°ò·Ç°µ¤@¼h¦¸µL¼ÐÃD¦CªºÁa¦V¶¶±Æ§Ç
End With
Erase Brr: Set Y = Nothing
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 3# hcm19522


Sub ¥h­«±Æ§Ç()
With CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; "
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "
.Open V & "Data Source=" & ThisWorkbook.FullName

Set s = Sheets("¤u§@ªí1")
s.Columns(4).ClearContents
s.Rows("1:1").Insert Shift:=xlDown
s.Range("a1:b1") = Array("a", "b")
q = "select a as a from [¤u§@ªí1$A1:A] " & vbCrLf & " union all "
q = q & vbCrLf & " select b as a from [¤u§@ªí1$B1:B]"
q = "select distinct a from (" & q & ")  order by a "
s.Range("d2").CopyFromRecordset .Execute(q)
s.Rows("1:1").Delete: End With
End Sub

sql¥h­«±Æ§Ç.zip (18.18 KB)

TOP

¦n³á ·PÁ¤j¤j¼ö±¡¤À¨É ±ßÂI´N¨Ó¸Õ¸Õ¬Ý

TOP

¦^´_ 1# henry860608


   


Sub test()
    Dim arr   As Object, brr As Variant
    Set arr = CreateObject("System.Collections.ArrayList")
    brr = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
    For Each n In brr
        If n <> vbNullString And Not arr.contains(n) Then arr.Add (n)
    Next
    arr.Sort
    Range("c1:c" & arr.Count) = Application.Transpose(arr.toarray)
End Sub

TOP

¦^´_ 4# singo1232001
¤£¦n·N«ä¤j¤j¡A¥i¥H°Ý¤@¤U³o¥|¦æ¬O¬Æ»ò·N«ä?
¤p§Ì¹ïSQL¯AÂy²LÁ¡
  1. q = "select a as a from [¤u§@ªí1$A1:A] " & vbCrLf & " union all "
  2. q = q & vbCrLf & " select b as a from [¤u§@ªí1$B1:B]"
  3. q = "select distinct a from (" & q & ")  order by a "
  4. s.Range("d2").CopyFromRecordset .Execute(q)
½Æ»s¥N½X

TOP

¦^´_ 6# quickfixer


    ÁÂÁ«e½ú
³o¤èªk¨S¦³±N­È¼g¤JÀx¦s®æ´N¥i±Æ§Ç,­Y¥Î©ó¦Û­qªí³æ¤¤²M³æ©ú²Óªº¦¬¶°¤£­«½Æ«á±Æ§Ç,¦n¹³«Ü¤è«K
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 8# Andy2483


¨Ò¦pactivex combobox
  Sheets("¤u§@ªí1").ComboBox1.List = arr.toarray
    Sheets("¤u§@ªí1").ComboBox1.ListIndex = 0

   

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2023-3-21 23:16 ½s¿è

¦^´_ 7# henry860608

­Õ­Y¨S¾Ç¹LSQL ¥i¥Hª½±µ¸õ¹L ¨Ï¥Î¨ä¥L¤j¤jªº¸Ñªk¤ñ¸û¤è«K ¾Ç²ß¸ô½u¤]¤ñ¸û¥¿³W

¥t¥~¨Ï¥Î¦¹¤èªk³£»Ý­n¦w¸Ë .Framework 3.5
¥]¬Aq¤jªºarraylist

¥H¤U¸ÑÄÀq­È¤¤,SQL»yªk¸ÑÄÀ

select distinct a from
(select a as a from [¤u§@ªí1$A1:A]
union all
select b as a from [¤u§@ªí1$B1:B] )  
                                                                order by a



1.
Àò¨ú¤u§@ªí1¤¤ªºAÄæ¸ê®Æ
select a as a from [¤u§@ªí1$A1:A]

¦X¨Ö
union all

¤u§@ªí1¤¤ªºBÄæ¸ê®Æ
select b as a from [¤u§@ªí1$B1:B]


2.
select distinct a from      '±N1.³¡¤ÀÀò¨ú¾ã²z¦nªº¸ê®Æ distinct¥h­«
(select a as a from [¤u§@ªí1$A1:A]
union all
select b as a from [¤u§@ªí1$B1:B] )  
                                                                order by a    '¨Ã¥Border by±Æ§Ç


­Y§A¹ïSQL¦³¿³½ì ±q2019¦~«á,§K¶Oºô¸ô½Òµ{«D±`¦h ¤×¨äB¯¸¤W ±q¹s¶}©l¾Ç ¤j·§2~3­Ó¤ë´N¥i¥H
¯u¥¿·N¸q¤W¨Ã¤£¬O®³¨Óµ¹excel³æ¿W¥Îªº
¥u¤£¹L­è¦n¥i¥H®³vba°V½m»yªk  ¹ê»Ú¥Î¦bSQL server »Pvba¤W
­±¹ï²{¹êªº°ª­t²ü³õ´º,¶W¹L30000µ§¥H¤Wªº¸ê®Æ ©ÎªÌ¦hªí¾ã¦X ´N¬OSQLªº¸nªù
¤×¨ä¦b¤ººô¤¤ ¦h¥x¹q¸£ ¶i¾P¦s ©ÎªÌ±µª¦ÂΤW³£¦³«Ü±j¤jªº«K§Q©Ê  ¦Ó¥B¤£¥Î¾á¤ß ¸ê®Æ½s½X²§±` ¤ñ¦p"温" "羣" ³oÃþexcel ·|²§±`ªº¤å¦r
·íµM¥¼¨Ó¤]¯à¹B¥Î¨ìptSQL ©ÎªÌMySQL

TOP

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD