提取各板块涨幅前5

zjykdada

本人不懂宏,请高手们帮忙啦!!
就是把每天各板块涨幅前5提取到第二页。




大概内容看在附件里

wdx223

1. let
   
2.     Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
   
3.     #"Filtered Rows" = Table.SelectRows(Source, each ([#"涨幅%%"] <> "--  ")),
   
4.     Custom1 = Table.Combine(Table.Group(#"Filtered Rows","细分行业",{"n",each Table.FirstN(Table.Sort(_,{"涨幅%%",1}),5)})[n])
   
5. in
   
6.     Custom1

复制代码

zjykdada

这个怎么弄，复制到宏提示语法错误

zjykdada

wdx223  大侠


这个怎么弄，复制到宏提示语法错误

zjykdada

wdx223 发表于 2018-8-2 10:42

这个怎么弄，复制到宏提示语法错误,,,求解？？？

wdx223

zjykdada 发表于 2018-8-2 15:06
这个怎么弄，复制到宏提示语法错误,,,求解？？？

PQ解法，在“数据”菜单中查找。如果找不到，去微软官网下载PowerQuery插件。

魂断蓝桥

Sub A()
Dim cnn, rs As Object, Sql As String, arr, brr(1 To 9999, 1 To 2), i%, m%, T
Dim j%, s
s = Replace(Sheet2.Name, "沪深Ａ股", "")
j = Application.Match(s + 0, Rows(1), 0)
Set cnn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.Recordset")
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName
Sql = "select distinct 细分行业 from  [沪深Ａ股20180731$a1:c] where 名称 is not null"
arr = cnn.Execute(Sql).getrows
Sql = "select 名称,细分行业,[涨幅%%] FROM  [沪深Ａ股20180731$a1:c]  ORDER BY 2,3 desc"
rs.Open Sql, cnn, 1, 1
For i = 0 To UBound(arr, 2)
    rs.Filter = "细分行业='" & arr(0, i) & "'"
        For T = 1 To 5
            If Not rs.EOF Then
                m = m + 1
                brr(m, 1) = arr(0, i)
                brr(m, 2) = rs.Fields("名称")
                rs.MoveNext
            End If
        Next
Next
[a2:z999] = ""
[a2].Resize(m, 1) = Application.Index(brr, 0, 1)
[a2].Offset(0, j - 1).Resize(m, 1) = Application.Index(brr, 0, 2)
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

魂断蓝桥

Option Explicit

Sub A()
Dim cnn, rs As Object, Sql As String, arr, brr(1 To 9999, 1 To 2), i%, m%, T
Dim j%, s
s = Replace(Sheet2.Name, "沪深Ａ股", "")
j = Application.Match(s + 0, Rows(1), 0)
Set cnn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.Recordset")
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName
Sql = "select distinct 细分行业 from  [沪深Ａ股20180731$a1:c] where 名称 is not null"
arr = cnn.Execute(Sql).getrows
Sql = "select 名称,细分行业,[涨幅%%] FROM  [沪深Ａ股20180731$a1:c]  ORDER BY 2,3 desc"
rs.Open Sql, cnn, 1, 1
For i = 0 To UBound(arr, 2)
    rs.Filter = "细分行业='" & arr(0, i) & "'"
        For T = 1 To 5
            If Not rs.EOF Then
                m = m + 1
                brr(m, 1) = arr(0, i)
                brr(m, 2) = rs.Fields("名称")
                rs.MoveNext
            End If
        Next
Next
[a2:z999] = ""
[a2].Resize(m, 1) = Application.Index(brr, 0, 1)
[a2].Offset(0, j - 1).Resize(m, 1) = Application.Index(brr, 0, 2)
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

zjykdada

mikezhan 发表于 2018-8-2 16:31
Sub test()
Dim arr, brr, crr()
Set dic = CreateObject("scripting.dictionary")

@mikezhan

还有点小问题，涨幅的点数与个股对不上帮忙该一下