☰

Excel 2013，数据可以自动重新排序吗？

tag-icon • tag-icon microsoft-excel sorting microsoft-excel-2013
Excel 2013，数据可以自动重新排序吗？

有没有办法自动重新排序？我有自动更新单元格的功能，并且根据传入的值排名会发生变化。我正在寻找一种让表格自动重新排序的方法（类似于条件格式），而无需单击重新排序按钮。

这里的目标是纯粹通过内置的 Excel2013 函数来实现这一点。我不是在寻找涉及辅助排序的附加单元格的解决方案，例如 Rank()，...

编辑

我包含了一个宏的代码，该宏会按设定的时间间隔刷新工作簿，并且还在一张工作表中包含代码，该代码应该刷新 Worksheet_Calculate 上该工作表上的表格。我收到运行时错误，不知道哪里出了问题？

Public RunWhen As Double
Const frequency = 5
Const cRunWhat = "DoIt"  ' the name of the procedure to run

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, frequency)
    Application.OnTime RunWhen, cRunWhat, Schedule:=True
End Sub

Sub DoIt()
    Sheets("RAWDATA").Calculate
    ActiveSheet.Calculate
    StartTimer  ' Reschedule the procedure
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime RunWhen, cRunWhat, Schedule:=False
End Sub

以及据称刷新表格的代码

Private Sub Worksheet_Calculate()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

ActiveSheet.ListObjects("Table2").AutoFilter.ApplyFilter
    With ActiveWorkbook.Worksheets("Strategies").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

ActiveSheet.ListObjects("Table3").AutoFilter.ApplyFilter
    With ActiveWorkbook.Worksheets("Strategies").ListObjects("Table3").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With

End Sub

答案1

我不喜欢留下评论中已经回答过的问题。你可以在评论中阅读历史记录，但这里是最终的解决方案：

Private Sub Worksheet_Calculate()
    'If the active sheet is called "Strategies", then this reapplies the filter for two tables and re-sorts them

    Const wsName As String = "Strategies"

    If ActiveSheet.Name = wsName Then

        'Freeze everything and turn off events
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .DisplayAlerts = False
        End With

        'Update Table2
        With Worksheets(wsName).ListObjects("Table2")
            .AutoFilter.ApplyFilter
            With .Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With

        'Update Table3
        With Worksheets(wsName).ListObjects("Table3")
            .AutoFilter.ApplyFilter
            With .Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With

        'Unfreeze things and turn events back on
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .DisplayAlerts = True
        End With

    End If
End Sub

你甚至可以将过滤和排序缩短为

    With Worksheets(wsName).ListObjects("Table2")
        .AutoFilter.ApplyFilter
        .Sort.Apply
    End With

这是一个社区 wiki，因为我没有得出解决方案。您可以根据需要对其进行编辑，但我所做的只是抄录了评论中发现的问题并稍微清理了一下代码。

相关内容