分組前三名語法

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

3^#

發表於 2014-1-16 11:31 | 只看該作者

回復 1# joey0415

試試看

Option Explicit
Sub Ex()
Dim AR(), E As Variant, Rng As Range
With Sheets("Sheet1")
.Range("A1").CurrentRegion.Sort Key1:=.Range("B2"), Order1:=xlAscending, Key2:=.Range( _
"A2"), Order2:=xlAscending, Key3:=.Range("D2"), Order3:=xlDescending, Header:=xlYes
'.Range("A1").CurrentRegion.Sort"資料的排序
.UsedRange.Columns(2).AdvancedFilter xlFilterCopy, , .Cells(1, .Columns.Count), True
'UsedRange.Columns(2)的進階篩選-> 不重複的股票
'**三十萬筆資料這兩行會慢一點
AR = Application.Transpose(.Cells(1, .Columns.Count).CurrentRegion.Offset(1))
'取得股票置入一維陣列中
ReDim Preserve AR(1 To UBound(AR) - 1) '刪除最後一筆的(空白)資料
.Cells(1, .Columns.Count).CurrentRegion.Clear '清除
.Range("F1").CurrentRegion.Offset(1).Clear '清除顯示前三筆資料的區域
For Each E In AR
Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole) '找每一股票的第一個位置
.Range(.Cells(Rng.Row, "A"), .Cells(Rng.Row + 2, "D")).Copy .Cells(.Rows.Count, "F").End(xlUp).Offset(1)
Next
End With
End Sub

複製代碼

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

　暱稱: joey0415

帖子: 361
主題: 57
精華: 0
積分: 426
點名: 0
作業系統: win7
軟體版本: 2003,2010
閱讀權限: 20
性別: 男
註冊時間: 2010-5-13
最後登錄: 2022-12-8

4^#

發表於 2014-1-16 12:26 | 只看該作者

回復 2# stillfish00

感謝大大提供的方法，這樣的串接法，在小筆數時，應該還行，如果是30萬x30萬這樣串就不太好！

目前是利用sql讀出30萬筆，用vba以迴圈的方式，如果是前三名的就保留，從30萬筆中找出來放進excel中，這樣不用幾秒

只是個人迴圈方式寫的不漂亮…
目前看來在資料庫中有分組後取排名值功能的
oracle
sql server
postgresql
本來想轉過去用，不過postgresql連接vba
我還不行
所以目前還先這樣用

　暱稱: joey0415

帖子: 361
主題: 57
精華: 0
積分: 426
點名: 0
作業系統: win7
軟體版本: 2003,2010
閱讀權限: 20
性別: 男
註冊時間: 2010-5-13
最後登錄: 2022-12-8

5^#

發表於 2014-1-16 13:22 | 只看該作者

本帖最後由 joey0415 於 2014-1-16 13:24 編輯

回復 3# GBKEE

超版大您好

您的方法可以用
可以再請教一點！
sql查詢出來的資料是一個二維陣列陣列中第一維定義Recordset的欄位（資料行），第二維定義Recordset的資料列。如圖

請用有方法可以將那些資料以您的方法或迴圈的方式，將分組前三名的資料放進另一個新的一維陣列中嗎？
我想查詢在放在陣列，篩選也在陣列，最後直接在寫進資料庫應該會比貼在cells上會快才對？

欄位如下
日期，股票代碼，買量，賣量，淨買賣

以下是我寫的，可能會有些問題，超版的沒有問題！
謝謝

Worksheets("sheet2").Activate
' r = 2 '計算總列數
a = 1 '取值後總列數
t = 1 '取前三名
sname = arr(1, r)
For r = LBound(arr, 2) To UBound(arr, 2)
If arr(1, r) = sname Then
If t <= 3 Then
Cells(a, 5) = arr(0, r)
Cells(a, 6) = arr(1, r)
Cells(a, 7) = arr(5, r)
t = t + 1
a = a + 1
End If
Else
sname = arr(1, r)
Cells(a, 5) = arr(0, r)
Cells(a, 6) = arr(1, r)
Cells(a, 7) = arr(5, r)
a = a + 1
t = 2 '因為填過一次，所以設為2
End If
r = r + 1
Next

複製代碼

stillfish00

大學生 Rank: 4

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

6^#

發表於 2014-1-16 14:09 | 只看該作者

回復 4# joey0415
樞紐分析的結果，至於1103因為第四名和第三名數量都相同，所以都會顯示
只是我版本不同(excel2010) ，若存為xls檔會有相容性問題所以不上傳檔案了。

　暱稱: joey0415

帖子: 361
主題: 57
精華: 0
積分: 426
點名: 0
作業系統: win7
軟體版本: 2003,2010
閱讀權限: 20
性別: 男
註冊時間: 2010-5-13
最後登錄: 2022-12-8

7^#

發表於 2014-1-16 14:50 | 只看該作者

回復 6# stillfish00
感謝分享，我試試看
應該可以錄成巨集

　暱稱: joey0415

帖子: 361
主題: 57
精華: 0
積分: 426
點名: 0
作業系統: win7
軟體版本: 2003,2010
閱讀權限: 20
性別: 男
註冊時間: 2010-5-13
最後登錄: 2022-12-8

8^#

發表於 2014-1-16 15:01 | 只看該作者

回復 3# GBKEE

超版請教一下

Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole) '找每一股票的第一個位置

看不出哪一句是找第一個位置

用錄製的也有LookAT:=xlWhole
以股票代碼為例1101有好幾個，為什麼會跳過呢？

謝謝

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

9^#

發表於 2014-1-16 16:55 | 只看該作者

本帖最後由 GBKEE 於 2014-1-16 17:26 編輯

回復 8# joey0415

Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole) '找每一股票的第一個位置

複製代碼

已限制在Range("B:B")中,第一次搜尋當然會從第一個(1101)找起
LookAT:=xlWhole :與儲存格的內容完全相同是 1101 不是 A1101B

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

　暱稱: joey0415

帖子: 361
主題: 57
精華: 0
積分: 426
點名: 0
作業系統: win7
軟體版本: 2003,2010
閱讀權限: 20
性別: 男
註冊時間: 2010-5-13
最後登錄: 2022-12-8

10^#

發表於 2014-1-16 20:30 | 只看該作者

本帖最後由 joey0415 於 2014-1-16 20:33 編輯

回復 9# GBKEE

謝謝超版，大概懂了，小弟是以F8的方式，加上即時運算視窗與區域變數視窗觀察看出來的

請問版大，版大常常使用WITH來寫程式，之後都加上一個 . 就行，可是有些時候，加點後，VBA會跑出一些相關的程式碼讓你選取，有些時候不會，這樣要怎麼樣來除錯呢？

版大的文章也常用 .End(xlUp).Offset(1)之類的定位找某一格或關鍵的上一個或下一格，不知道哪有相關的說明會清楚一些

小弟買了很多VBA的書，但有時候還是很希望能有類似按一個點就有相關的程式碼可以選用，不知道要怎麼寫或設定才會一直出現呢？

謝謝