帖子: 17
主題: 5
精華: 0
積分: 71
點名: 1
作業系統: window
軟體版本: google
閱讀權限: 20
性別: 男
註冊時間: 2017-6-27
最後登錄: 2024-5-17

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2023-3-19 20:30 | 只看該作者

兩組數值去除重複的資料後排序

本帖最後由 henry860608 於 2023-3-19 20:31 編輯

已知A、B兩列有兩組數值，想要透過公式將兩組數值有重複的部分去除，並將資料依數值大小排序(如下面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

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

2^#

發表於 2023-3-20 12:00 | 只看該作者

本帖最後由 Andy2483 於 2023-3-20 12:04 編輯

[attach]35988[/attach]回復 1# henry860608

謝謝前輩發表此主題與情境
後學練習陣列與字典的解決方案如下,請前輩參考

執行前:

執行結果:

Option Explicit
Sub TEST()
Dim Brr, Y, C%, R&
'↑宣告變數:(Brr,Y)是通用型變數,C是短整數,R是長整數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y這通用型變數是字典
[C:C].ClearContents
'↑令C欄儲存格內容清除
Brr = Range([B1], Cells(Rows.Count, "A").End(3))
'↑令Brr這通用型變數是二維陣列,
'以[B1]到A欄最後有內容儲存格值帶入
For C = 1 To 2
'↑設順迴圈!C從1到 2
For R = 1 To UBound(Brr)
'↑設順迴圈!R從1到 Brr陣列縱向最大索引列號
   Y(Brr(R, C)) = ""
   '↑令R迴圈列R迴圈欄Brr陣列值當key,item是空字元,納入Y字典裡
   '若key重複只留一筆
Next
Next
With [C1].Resize(Y.Count, 1)
'↑以下是關於[C1]儲存格擴展向下(Y字典key數量)列的相關程序
.Value = Application.Transpose(Y.Keys)
'↑令儲存格值以 Y字典key轉置後值帶入
.Sort KEY1:=.Item(1), Order1:=1, _
Header:=0, Orientation:=1
'↑令以[C1]作為排序基準做一層次無標題列的縱向順排序
End With
Erase Brr: Set Y = Nothing
'↑令釋放變數
End Sub

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

hcm19522

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-5

熱烈參與勳章

3^#

發表於 2023-3-20 13:43 | 只看該作者

https://blog.xuite.net/hcm19522/twblog/590762370

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

singo1232001

　暱稱: 熱狗慶

中學生 Rank: 2

帖子: 354
主題: 5
精華: 0
積分: 387
點名: 0
作業系統: windows7
軟體版本: vba,vb,excel2007
閱讀權限: 20
性別: 男
註冊時間: 2017-1-8
最後登錄: 2024-8-2

論壇榮譽勳章論壇贊助勳章

4^#

發表於 2023-3-20 15:48 | 只看該作者

回復 3# hcm19522

Sub 去重排序()
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("工作表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 [工作表1$A1:A] " & vbCrLf & " union all "
q = q & vbCrLf & " select b as a from [工作表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去重排序.zip (18.18 KB)

TOP

henry860608

中學生 Rank: 2

帖子: 17
主題: 5
精華: 0
積分: 71
點名: 1
作業系統: window
軟體版本: google
閱讀權限: 20
性別: 男
註冊時間: 2017-6-27
最後登錄: 2024-5-17

5^#

發表於 2023-3-20 23:01 | 只看該作者

好喔感謝大大熱情分享晚點就來試試看

TOP

quickfixer

中學生 Rank: 2

帖子: 154
主題: 1
精華: 0
積分: 200
點名: 28
作業系統: windwos 7
軟體版本: 64bit
閱讀權限: 20
註冊時間: 2017-5-29
最後登錄: 2024-10-22

6^#

發表於 2023-3-20 23:48 | 只看該作者

回復 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

henry860608

中學生 Rank: 2

帖子: 17
主題: 5
精華: 0
積分: 71
點名: 1
作業系統: window
軟體版本: google
閱讀權限: 20
性別: 男
註冊時間: 2017-6-27
最後登錄: 2024-5-17

7^#

發表於 2023-3-21 11:35 | 只看該作者

回復 4# singo1232001
不好意思大大，可以問一下這四行是甚麼意思?
小弟對SQL涉獵淺薄

q = "select a as a from [工作表1$A1:A] " & vbCrLf & " union all "
q = q & vbCrLf & " select b as a from [工作表1$B1:B]"
q = "select distinct a from (" & q & ") order by a "
s.Range("d2").CopyFromRecordset .Execute(q)

複製代碼

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

8^#

發表於 2023-3-21 15:01 | 只看該作者

回復 6# quickfixer

謝謝前輩
這方法沒有將值寫入儲存格就可排序,若用於自訂表單中清單明細的收集不重複後排序,好像很方便

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

quickfixer

中學生 Rank: 2

帖子: 154
主題: 1
精華: 0
積分: 200
點名: 28
作業系統: windwos 7
軟體版本: 64bit
閱讀權限: 20
註冊時間: 2017-5-29
最後登錄: 2024-10-22

9^#

發表於 2023-3-21 21:35 | 只看該作者

回復 8# Andy2483

例如activex combobox
Sheets("工作表1").ComboBox1.List = arr.toarray
Sheets("工作表1").ComboBox1.ListIndex = 0

TOP

singo1232001

　暱稱: 熱狗慶

中學生 Rank: 2

帖子: 354
主題: 5
精華: 0
積分: 387
點名: 0
作業系統: windows7
軟體版本: vba,vb,excel2007
閱讀權限: 20
性別: 男
註冊時間: 2017-1-8
最後登錄: 2024-8-2

論壇榮譽勳章論壇贊助勳章

10^#

發表於 2023-3-21 23:05 | 只看該作者

本帖最後由 singo1232001 於 2023-3-21 23:16 編輯

回復 7# henry860608

倘若沒學過SQL 可以直接跳過使用其他大大的解法比較方便學習路線也比較正規

另外使用此方法都需要安裝 .Framework 3.5
包括q大的arraylist

以下解釋q值中,SQL語法解釋

select distinct a from
(select a as a from [工作表1$A1:A]
union all
select b as a from [工作表1$B1:B] )
                                                            order by a

1.
獲取工作表1中的A欄資料
select a as a from [工作表1$A1:A]

合併
union all

工作表1中的B欄資料
select b as a from [工作表1$B1:B]

2.
select distinct a from    '將1.部分獲取整理好的資料 distinct去重
(select a as a from [工作表1$A1:A]
union all
select b as a from [工作表1$B1:B] )
                                                            order by a '並且order by排序

若你對SQL有興趣從2019年後,免費網路課程非常多尤其B站上從零開始學大概2~3個月就可以
真正意義上並不是拿來給excel單獨用的
只不過剛好可以拿vba訓練語法  實際用在SQL server 與vba上
面對現實的高負荷場景,超過30000筆以上的資料或者多表整合就是SQL的罩門
尤其在內網中多台電腦進銷存或者接爬蟲上都有很強大的便利性  而且不用擔心資料編碼異常比如"温" "羣" 這類excel 會異常的文字
當然未來也能運用到ptSQL 或者MySQL

TOP