当您想在Excel中按字母顺序排列时,无论是对列还是行进行排序,该过程通常都很简单。但是,当涉及按字母顺序排列多行或多列,或处理字母和数字时,该过程可能需要一些额外的步骤。
在本文中,您将了解有关如何在Excel电子表格中按字母顺序排列数据的所有知识。
使用排序在 Excel 中按字母顺序排列
在Excel(Excel)中用于按字母顺序排列的方法取决于有多少列或行包含您要组织的数据。
按字母顺序对单列数据进行排序是最简单的。如果您有多个列并且想要确保所有数据都与按字母顺序排列的列对齐,则需要执行一些额外的步骤。
最后,如果要对多列进行排序,以及数据是否有标题行,需要特别注意。
一般的
在Excel(Excel)中按字母顺序排列的最简单方法是如果您有一列数据。例如,如果您有一列包含名称列表。
1. 选择数据列。
2. 选择主(Home)菜单,然后在功能区的编辑(Editing)组中选择排序和筛选(Sort & Filter)下拉菜单。
3.根据您希望如何组织数据,选择从(organize the data)A 到 Z(Sort A to Z)排序或从Z 到 A 排序(Sort Z to A)。
现在,当您回顾突出显示的列时,您会看到那里的数据已使用您选择的方法按字母顺序排序。
如果你的数据有数字怎么办?例如,假设您有第二列,其中包含每个人的年龄。
您想要对该列进行排序,因此您按照与上述相同的过程并选择数据列。
这一次,当您在功能区上选择“排序(Sort)和筛选(Filter)”时,您会看到有关如何排序的选择已更改。
这些选项会将数字从最小到最大排序,或者反过来。
多列排序
在包含多列(that contains multiple columns)数据 的表中单独对列进行排序存在一个问题。
如果您只选择一列并对其进行排序,则其他列保持不变。这意味着您的电子表格将全部混淆,数据将不再有意义。答案是一次对所有列进行排序。
让我们使用姓名和年龄示例电子表格再次执行此操作。
1. 这次不是选择单个列,而是选择整个表。
2. 在主(Home)菜单中,选择排序和筛选(Sort & Filter),然后像以前一样选择您的排序首选项。
现在,您将看到不仅姓名在Excel电子表格中按字母顺序排序,而且第二列中的相关年龄也与他们一起排序。那些时代留下了正确的名字。
这不仅适用于Excel中的两列电子表格,而且无论您的电子表格有多大,只要您在排序前选择工作表中的所有数据,它都可以使用。
如果您愿意,您还可以将多列排序为一列(sort multiple columns into one),然后对单列进行排序。
自定义排序
您会注意到Sort & Filter下拉菜单中的另一个选项是Custom Sort。
为了了解它是如何工作的,让我们以一个更大的电子表格为例。此示例中的电子表格是纽约(New York)州高中的SAT分数列表。
- 选择电子表格中的整个数据表,选择主(Home)菜单,选择排序和筛选(Sort & Filter),然后选择自定义排序(Custom Sort)。
2. 您将看到一个新的弹出窗口,您可以在其中选择每列的具体排序方式。选择要排序的列,选择排序方式,然后选择排序方式(A 到 Z、Z 到 A 或自定义(Custom))。
您会注意到有一些有趣的Sort On选项。这些都是:
- 单元格值(Cell Values):按字母顺序排序,或者在数字的情况下,从低到高或从高到低
- 单元格颜色(Cell Color):按单元格颜色组织数据
- 字体颜色(Font Color):按字体颜色组织数据
- 条件格式图标(Conditional Formatting Icon):通过单元格中的图标从其条件格式逻辑组织数据
这将按您选择的第一列对整个数据表进行排序。但是您也可以进行二次排序(一旦对第一个选定的列进行了排序)。
只需选择Add Level。从出现的新字段中,根据需要再次选择Column、Sort On 和Order下拉菜单。
选择确定(OK),您会看到整个表格现在按多列排序。
它将首先按您选择的初始列排序,然后是第二个、第三个,依此类推。
注意(Note):您可能已经注意到上面下拉列表中的列排序选项包括标题名称而不是字母。这是因为选中了“我的数据在排序窗口中有标题(My data has headers)”旁边的复选框。如果您的电子表格确实有标题,请务必选择此选项。
使用过滤器在 Excel 中按字母顺序排列
在主(Home)菜单中使用排序(Sort)和筛选(Filter)下拉菜单时,您可能已经注意到还有一个筛选(Filter)选项。
如果您选择此项,您会注意到下拉箭头出现在每列的顶部。当您选择任何这些列顶部的箭头之一时,您会看到一长串选项。
以下是每个选项的工作原理:
- 从 A 到 Z(Sort A to Z)或从Z 到 A(Sort Z to A)排序:使用此列按字母顺序在任一方向对工作表进行排序。
- 按颜色排序(Sort by Color):这将显示与上一节相同的多列排序窗口。
底部的其他过滤选项不会帮助您在Excel中按字母顺序排列,但是有很多选项可以过滤掉您不关心的数据。当您选择Text Filters时,您可以选择仅在工作表中显示以下数据:
- 等于(Equals)特定值
- 不等于(Does Not Equal)特定值
- (Begins With)以单词或数字开头
- (Ends With)以单词或数字结尾
- 包含(Contains)单词或数字
- 不包含(Does Not Contain)单词或数字
- 自定义过滤器(Custom Filter)可让您组合上述任何过滤器以使用多个过滤器
如您所见,有很多方法可以在Excel中按字母顺序排列(或订单号) 。您选择的选项实际上取决于您要排序的数据量、要排序的表有多大,以及您是只想按一列或一行还是多列排序。
了解有关Excel 的不同提示和技巧的(tips and tricks for Excel)更多信息,并分享您知道的任何其他技术,以便在Excel中对行和列进行排序。
How to Alphabetize In Excel
When you wаnt to alphabetize in Excel, whether it’s to sort a column or a row, the proceѕs is usually straightforward. However, when it comes to alphabetizing multiple rows or columns, or dealіng with letters and numbers, the process may require a few extra stеps.
In this article you’ll learn everything you need to know about how to alphabetize your data in an Excel spreadsheet.
Alphabetize In Excel With Sort
The method you use to alphabetize in Excel depends on how many columns or rows contain the data that you want to organize.
Sorting a single column of data alphabetically is the easiest. If you have multiple columns and want to make sure all of the data lines up with the alphabetized column, there are a few extra steps.
Finally, there are special considerations to take in mind if you want to sort multiple columns, and whether the data has a header row or not.
General
The simplest method to alphabetize in Excel is if you have one column of data. For example, if you have a column with a list of names.
1. Select the column of data.
2. Select the Home menu, and select the Sort & Filter dropdown in the Editing group on the ribbon.
3. Select either Sort A to Z or Sort Z to A depending how you’d like to organize the data.
Now, when you look back at the column you highlighted, you’ll see that the data there has been sorted alphabetically using the method you chose.
What if your data has numbers? For example, let’s say you have a second column with the ages of each person.
You want to sort that column, so you follow the same procedure as above and select the column of data.
This time, when you select Sort & Filter on the ribbon, you’ll see that the selections for how to sort have changed.
These options will either sort the numbers from the smallest number to the largest, or the other way around.
Multi-Column Sorting
There is one problem with sorting columns individually in a table that contains multiple columns of data.
If you only select one column and sort it, the other columns stay in place. This means your spreadsheet would get all mixed up and the data would no longer make sense. The answer to this is to sort all of the columns at once.
Let’s do this again with the name and age example spreadsheet.
1. This time, instead of selecting a single column, select the entire table.
2. In the Home menu, select Sort & Filter, and just as before, select your sort preference.
Now, you’ll see that not only have the names been sorted alphabetically in your Excel spreadsheet, but the associated ages from the second column sorted along with them. Those ages stayed with the correct name.
This works not only with a two column spreadsheet in Excel, but it’ll work regardless how large your spreadsheet is, so long as you select all of the data in the sheet before sorting.
If you wanted to, you could also sort multiple columns into one, and then sort that single column instead.
Custom Sort
You’ll notice another option in the Sort & Filter dropdown menu is the Custom Sort.
To see how this works, let’s take a much larger spreadsheet as an example. The spreadsheet in this example is a list of SAT scores for high schools in the state of New York.
- Select the entire table of data in the spreadsheet, select the Home menu, select Sort & Filter, and select Custom Sort.
2. You’ll see a new pop-up appear where you can select how each column is specifically sorted. Select the column you want to sort by, select how you want to sort it, and then select how you want to sort it (A to Z, Z to A, or Custom).
You’ll notice there are some interesting Sort On choices. These are:
- Cell Values: Sort alphabetically, or in the case of numbers, from low to high or high to low
- Cell Color: Organize data grouped by cell color
- Font Color: Organize data grouped by font color
- Conditional Formatting Icon: Organize data by the icon in the cell from its conditional formatting logic
This will sort the entire table of data by the first column you’ve selected. But you can also do a secondary sort (once the first selected column is sorted).
Just select Add Level. From the new fields that appear, select the Column, Sort On, and Order dropdowns again as desired.
Select OK and you’ll see your entire table is now sorted by multiple columns.
It’ll sort first by the initial column you selected, then the second, the third, and so on.
Note: You might have noticed that the column sort options in the dropdown above included the header names rather than letters. This is because the checkbox next to My data has headers in the sort window is selected. If your spreadsheet does have headers, make sure to select this.
Alphabetize In Excel With Filter
While using the Sort & Filter dropdown in the Home menu, you might have noticed there’s also a Filter option.
If you select this, you’ll notice dropdown arrows appear at the top of each column. When you select one of the arrows at the top of any of those columns, you’ll see a long list of options.
Here is how each of these options works:
- Sort A to Z or Sort Z to A: Sort the sheet using this column alphabetically in either direction.
- Sort by Color: This will bring up the same multi-column sort window as in the last section above.
The other filtering options at the bottom won’t help you to alphabetize in Excel, but there are lots of options to filter out data you don’t care about. When you select Text Filters, you can choose to only display data in the sheet that:
- Equals a specific value
- Does Not Equal a specific value
- Begins With a word or number
- Ends With a word or number
- Contains a word or number
- Does Not Contain a word or number
- Custom Filter lets you combine any of the filters above to use multiple filters
As you can see, there are a lot of ways to alphabetize (or order numbers) in Excel. The option you choose really depends how much data you want to sort, how large the table is that you want to sort, and whether you want to sort by only one column or row, or multiple ones.
Learn more about different tips and tricks for Excel, and share any other techniques you know about to sort your rows and columns in Excel.