在Excel中排序公式
我有这样的数据：
bear 94
cat 25
alligator 53
impala 55
elk 56
fox 47
dog 13
gecko 18
jaguar 32
hound 59
...但我希望在同一个Excel工作表上有两个“副本”，第一个排序在第一列，如下所示：
alligator 53
bear 94
cat 25
dog 13
elk 56
fox 47
gecko 18
hound 59
impala 55
jaguar 32
...第二个表格将再次是相同的数据，但在第二列进行排序，如下所示：
bear 94
hound 59
elk 56
impala 55
alligator 53
fox 47
jaguar 32
cat 25
gecko 18
dog 13
......但问题在于我不想在Excel中使用实际的“排序”功能！ 这听起来很疯狂，但我有一个更大的应用程序，手动分类将非常繁琐。 如果可能的话，我希望有一个公式可以自动执行此操作，但我也可以使用excel-VBA宏。 有任何想法吗？
好的，这是我提出的解决方案。 也许有更优雅的方式，请让我知道！ 多谢你们 ：）
如果你有很多工作表，VBA可能会走。 以下代码是执行此操作的一种方法。 它循环遍历工作簿中的所有工作表 ，并SortBy2您在SortBy1和SortBy2定义的变量对每个表进行排序（假定工作表只包含一个以单元格A1开头的表 ）。
这将排序表SortBy2 ，复制此原始表的下方，然后再次通过排序原始表SortBy1 。 只要您想要排序的变量在整个工作簿中都被命名为相同，就应该这样工作。
Option Explicit
Sub SortAndCopy()
Dim ws As Worksheet
Dim DataRng As Range
Dim SortRng1 As Range, SortRng2 As Range
Dim nr As Integer, nc As Integer, i As Integer
Dim DataArr As Variant
Dim SortBy1 As String, SortBy2 As String
Dim nBelowTable As Integer
Dim HeaderFound As Integer
SortBy1 = "Animal" '<~~ Define the first variable to sort by
SortBy2 = "Count" '<~~ Define the second variable to sort by
nBelowTable = 5 '<~~ Defines how far below the original table you want to place a copy
Application.ScreenUpdating = False
'Loops through each individual sheets
For Each ws In ActiveWorkbook.Sheets
HeaderFound = 0
'Determines data range
nr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
nc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Set DataRng = ws.Range("A1:" & ws.Cells(nr, nc).Address)
'Determines ranges to sort by
For i = 1 To nc Step 1
If LCase(ws.Cells(1, i).Value) = LCase(SortBy1) Then
Set SortRng1 = ws.Range(ws.Cells(1, i).Address & ":" & ws.Cells(nr, i).Address)
HeaderFound = HeaderFound + 1
End If
If LCase(ws.Cells(1, i).Value) = LCase(SortBy2) Then
Set SortRng2 = ws.Range(ws.Cells(1, i).Address & ":" & ws.Cells(nr, i).Address)
HeaderFound = HeaderFound + 1
End If
Next i
'Exit if header not found
If Not HeaderFound = 2 Then
MsgBox "One of the header variables could not be found in the sheet " & ws.Name & ". No further sheets will be processed!", vbCritical
Exit Sub
End If
'Sorts table by SortBy2
With ws.Sort.SortFields
.Clear
.Add Key:=SortRng2, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With
With ws.Sort
.SetRange DataRng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Places copy of this table underneath the original
ReDim DataArr(1 To nr, 1 To nc)
DataArr = DataRng
ws.Range(ws.Cells(nr + nBelowTable, 1).Address, ws.Cells(2 * nr + nBelowTable - 1, nc).Address) = DataArr
'Sorts table by SortBy1
With ws.Sort.SortFields
.Clear
.Add Key:=SortRng1, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With
With ws.Sort
.SetRange DataRng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next ws
Application.ScreenUpdating = False
End Sub
获取MOREFUNC的Excel插件并使用VSORT（）
MOREFUNC ADDON
下一篇: Automating the sort of an excel column with formulas from access