Excel：根据另一列排序列 [英] Excel: Order a column based on another column

问题描述

在Google文档中，只需简单地：=排序（A1：A100，B1：B100）

如何在Excel中执行？

解决方案

手动，您可以突出显示您要排序的所有列，然后在主页选项卡的排序和过滤下单击自定义排序...。这将打开一个对话框，您可以在其中指定要排序的列，添加多个排序级别等。

如果您知道如何在Excel中手动执行某些操作，要了解如何以编程方式使用VBA，您可以简单地记录自己手动执行的宏，然后查看它生成的源代码。我这样做是根据列B对列A和B进行排序，并从生成的内容中提取相关代码：

  ActiveWorkbook.Worksheets （Sheet1）Sort.SortFields.Clear 
 ActiveWorkbook.Worksheets（Sheet1）。Sort.SortFields.Add Key：= Range（B1：B6），_ 
 SortOn：= xlSortOnValues ，订单：= xlAscending，DataOption：= xlSortNormal 
使用ActiveWorkbook.Worksheets（Sheet1）排序
 .SetRange范围（A1：B6）
 .Header = xlGuess 
 .MatchCase = False 
 .Orientation = xlTopToBottom 
 .SortMethod = xlPinYin 
。应用
  

请注意，自动生成的代码几乎总是不必要的膨胀。但是，这是一个很好的方式来确定你可能需要使用或研究更多的功能。在这种情况下，您可以将其修剪成如下所示：

 范围（A1：B6）排序Key1： =范围（B1：B6），Order1：= xlAscending 
  

如果您只想重新排序列A的内容，而不触摸列B（尽管您将其用作排序键），您可能需要进行临时副本，排序和仅复制列A.这是因为Excel的排序功能需要排序关键在被排序的范围内。所以，它可能如下所示：

  Application.ScreenUpdating = False 
范围（A1：B6）。复制目的地：=范围（G1：H6）
范围（G1：H6）排序Key1：=范围（H1：H6），Order1：= xlAscending 
范围：G6）复制目的地：=范围（A1：A6）
范围（G1：H6）清除
 Application.ScreenUpdating = True 
  

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

In Google Docs that is simply: =Sort(A1:A100, B1:B100)

How to do that in 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.

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

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

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