☰

Excel 自动对行进行排序？

tag-icon • tag-icon microsoft-excel sorting
Excel 自动对行进行排序？

我知道如何根据行的值对行进行排序，但是随着我不断添加新行，值会不断变化，我希望 Excel 能够自动对我添加的所有内容进行排序。在 Sheet 1 上，我有一张这样的表格：

    TEAM 1   TEAM 2   TEAM 3
1    3        3        1
2    1        1        0 
3    0        3        0
4    3        3        0
5    3        1        1

而在第二张表中我有以下内容：

         TOTAL
TEAM 1    10
TEAM 2    11
TEAM 3    2

Sheet2 使用如下公式：=SUM(Sheet1!B:Sheet1!B)

我的想法是，每次我在工作表 1 上添加新行时，都应该自动对这个表进行排序。我该如何实现呢？

答案1

此宏不仅可以排序，还可以进行求和：

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim wkb As Workbook
    Set wkb = ThisWorkbook
    Dim wks, wks1 As Worksheet
    Set wks = wkb.Worksheets("Sheet1") ' Sheet with the results
    Set wks1 = wkb.Worksheets("Sheet2") ' Sheet with the totals
    wks1.Rows.Clear ' Clear the contents of Sheet2
    wks1.Cells(1, 2) = "TOTAL"
    usedcolumns = True
    thecolumn = 1
    totalrow = 2
    While usedcolumns
        therow = 1
        totalpoints = 0
        usedrows = True
        While usedrows
            thedata = wks.Cells(therow, thecolumn)
            If thedata <> "" Then
                If therow = 1 Then
                    teamname = thedata
                Else
                    totalpoints = totalpoints + thedata
                End If
                therow = therow + 1
            Else
                usedrows = False
                If therow = 1 Then usedcolumns = False
            End If
        Wend
        If teamname <> "" Then
            wks1.Cells(totalrow, 1) = teamname
            wks1.Cells(totalrow, 2) = totalpoints
            teamname = ""
            totalpoints = 0
            thecolumn = thecolumn + 1
            totalrow = totalrow + 1
        End If
    Wend
    lastrow = wks1.Cells(Rows.Count, 2).End(xlUp).Row
    With wks1
        .Range("A1:B" & lastrow).Sort key1:=.Range("B2:B" & lastrow), order1:=xlDescending, Header:=xlYes
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

使用 alt+ F11 转到 VBA /Macros 并双击Sheet1，然后将此代码粘贴到右侧。

为了避免宏在某一天导致错误时可能出现的任何潜在问题，阻止工作簿上的事件检测，请双击This Workbook并粘贴以下内容：

Private Sub Workbook_Open()
   Application.EnableEvents = True
End Sub

相关内容