根据另一列订购一列 [英] Order a column based on another column

问题描述

我想基于列B的值对列A进行排序.

I want to order Column A based on the values of Column B.

在Google表格中，它很简单: = SORT(A1:A100，B1:B100，TRUE)

In Google Sheets that is simply: =SORT(A1:A100,B1:B100,TRUE)

如何在 Excel 中做到这一点?

推荐答案

要手动执行此操作，可以突出显示要排序的所有列，然后在排序和筛选"下单击自定义排序...".首页"标签.这会弹出一个对话框，您可以在其中告诉它要对哪一列进行排序，添加多个排序级别等.

To do it manually, you can highlight all the columns you want sorted, then click "Custom Sort..." under "Sort & Filter" in the "Home" tab. This brings up a dialog where you can tell it what column to sort by, add multiple sort levels, etc.

如果您知道如何在Excel中手动执行某项操作，并且想了解如何使用VBA以编程方式执行该操作，则只需记录自己手动执行的宏，然后查看其生成的源代码即可.我这样做是根据B列对A列和B列进行排序，然后从生成的内容中提取相关代码:

If you know how to do something manually in Excel and want to find out how to do it programmatically using VBA, you can simply record a macro of yourself doing it manually and then look at the source code it generates. I did this to sort columns A and B based on column B and pulled the relevant code from what was generated:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B6"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:B6")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply

请注意，尽管如此，自动生成的代码几乎总是会出现不必要的膨胀.但是，这是弄清楚您可能需要使用或研究哪些功能的好方法.在这种情况下，您可以将其修剪成这样:

Note that the automatically generated code almost always has unnecessary bloat though. But, it's a nice way to figure out what functions you might need to use or research more. In this case, you could trim it down to something like this:

Range("A1:B6").Sort Key1:=Range("B1:B6"), Order1:=xlAscending

如果您只想对A列的内容进行重新排序而不触及B列(即使您将其用作排序键)，则可能需要制作一个临时副本，对其进行排序，然后仅复制回A列这是因为Excel的Sort函数要求sort键位于要排序的范围内.因此，它可能看起来像这样:

If you only want to reorder the contents of column A without touching column B (even though you're using it as the sort key), you probably need to make a temporary copy, sort it, and copy back only column A. This is because Excel's Sort function requires the sort key to be in the range being sorted. So, it might look like this:

Application.ScreenUpdating = False
Range("A1:B6").Copy Destination:=Range("G1:H6")
Range("G1:H6").Sort Key1:=Range("H1:H6"), Order1:=xlAscending
Range("G1:G6").Copy Destination:=Range("A1:A6")
Range("G1:H6").Clear
Application.ScreenUpdating = True

这篇关于根据另一列订购一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！