如何在Microsoft Office 2013 Excel中优先排序含特定关键词的行？

Hey there! Let's break down how to rearrange your Excel data so all rows containing the "SERVER" keyword show up at the top, plus cover formula/VBA solutions and general value sorting methods you asked about.

Method 1: Formula + Helper Column (No VBA Needed) #

This is a straightforward, no-code approach perfect for one-off sorting:

1. Insert a helper column (e.g., Column C) next to your data. Enter Sort Key as the header in cell C1.
2. In cell C2, paste this formula:

   =IF(ISNUMBER(SEARCH("SERVER",A2)),0,1)
   

   This formula returns 0 if the cell in Column A contains "SERVER", and 1 otherwise—so sorting by this column will push all "SERVER" rows to the front.
3. Drag the formula down to apply it to all rows of your data.
4. Select your entire data range (including the helper column), go to the Data tab, click Sort.
   • Set Main Key to Column C, Sort On to Values, Order to Ascending.
   • Make sure "My data has headers" is checked.
5. Once sorted, you can right-click Column C and select Hide to keep your sheet clean.

Method 2: VBA Macro (One-Click Automation) #

If you need to repeat this sort regularly, a VBA macro will save you time:

1. Open the VBA Editor: Press Alt + F11, or go to the Developer tab (enable it via right-clicking the ribbon if hidden) and click Visual Basic.
2. Right-click your workbook in the Project Explorer, select Insert > Module.
3. Paste this code into the module:

   Sub SortServerFirst()
       Dim ws As Worksheet
       Set ws = ActiveSheet ' Replace with Sheets("YourSheetName") if targeting a specific sheet
       
       ' Define the full data range (including headers)
       Dim dataRange As Range
       Set dataRange = ws.Range("A1").CurrentRegion
       
       ' Add helper column and populate sort key formula
       ws.Columns("C:C").Insert Shift:=xlToRight
       ws.Range("C1").Value = "SortKey"
       ws.Range("C2:C" & dataRange.Rows.Count).Formula = "=IF(ISNUMBER(SEARCH(""SERVER"",A2)),0,1)"
       
       ' Sort the data
       dataRange.Resize(, dataRange.Columns.Count + 1).Sort _
           Key1:=ws.Range("C1"), Order1:=xlAscending, Header:=xlYes
       
       ' Clean up by deleting the helper column
       ws.Columns("C:C").Delete
   End Sub
   

4. Close the VBA Editor, go back to your sheet, and run the macro via the Developer > Macros menu, or assign it to a button for even easier access.

General Value Sorting Methods #

To sort your data by values (e.g., Column B in your example):

1. Select your data range, go to Data > Sort.
2. Set Main Key to the column you want to sort by (e.g., Column B), Sort On to Values, and choose Ascending or Descending order.
3. For multi-level sorting (e.g., first sort by "SERVER" presence, then by Column B values), click Add Level in the Sort dialog and set the second key to Column B.

内容的提问来源于stack exchange，提问作者Ahmed Badreldin Amado