Excel 的两个主要功能是允许您从不同的角度操作和查看数据,并且该程序更简单但功能强大的工具之一是排序(Sort )功能。
无论是简单的升序/降序排序、对多个变量进行排序以分组显示数据或保持行完整性,还是简单的字母数字排序以保持表格数据有序,在Excel中对数据进行排序都是一项基本技能。
很少有程序比Excel更适合对表格数据进行排序,而且您的排序可以从简单且相对不复杂到高度复杂。虽然Excel的排序能力——你可以用正确的数据集和对程序内部工作的一点了解——确实是强大而深入的,但今天的技术提示侧重于两种基本类型,如下所示:
当然,电子表格由单元格的列和行或表格数据组成,其中每一列包含按类别划分的事实、数字或任何其他详细信息的逻辑划分,例如姓名、地址、货币、部分数字等等——取决于电子表格的类型。另一方面,行(Rows)并排或在同一实例或事件中显示人、对象或图形。
根据电子表格的类型及其包含的数据,例如姓名、地址、电话号码和其他相关数据的表格列表,行通常类似于数据库记录。
当您对数据行进行排序时,每一行都必须保持其完整性,而不会无意中将数据从一行移动到另一行,正如您将在下面看到的那样,这就是对多列数据进行排序的地方。
在单个字段上排序
您可以按行对电子表格中的记录进行排序,也可以按列对记录中的单元格进行排序。当然,您可以指定升序或降序排序。默认情况下,升序/字母数字,程序从A到Z排列文本,从最小到最大排列数字。当然,使用降序排序将旧的从Z反转为A,或者更大的数字从顶部开始。
与Excel中的许多函数一样,有几种方法可以执行简单的排序;但是,这种类型所需的一切都位于鼠标右键弹出菜单中,如下所示。
- 右键单击(Right-click)要对电子表格进行排序的列中的字段以打开弹出菜单。
- 向下滚动(Scroll)并将光标悬停在排序(Sort)
上以打开弹出菜单。
- 单击从 A 到 Z(Sort A to Z)进行升序或从Z 到 A(Sort
Z to A )降序排序(请注意(Note),Excel不包括排序中第 1 行的单元格中的数据;程序假定该行包含您的列标签或标题。)。
这种简单的排序适用于许多类型的数据,除非您的行和列具有相同或重复的数据。在这些情况下,您必须对两列或更多列进行排序,接下来是。
对多个字段的记录进行排序
根据数据的复杂性,您可能需要对多个列进行排序。也许最好的例子是按照姓氏的字母顺序对数据库进行排序。假设您的数据有几个姓氏相同的人。在这些情况下,您需要确保 Linda Johnson 出现在Lydia Johnson之前,而Cherri Anderson出现在 Charles Anderson 之后……您明白了。
您可以从“排序(Sort)”对话框设置具有多个条件的自定义搜索,如下所示。
- 单击(Click)要排序的数据的第一列中的单元格。
- 单击标题栏正下方的数据以打开数据(Data )功能(Data)区。(当然,功能区是应用程序窗口顶部的上下文选项卡行。)
- 在“排序和筛选(Sort & Filter)”部分,单击“排序(Sort )”按钮以打开“排序”对话框。
- 单击排序依据(Sort by)下拉菜单并选择要排序的第一个列名。(请注意,Excel在列的第 1 行显示内容,在这种情况下包含列标签。您可以通过取消选中“(Excel)我的数据有标题”(My data has headers)复选框来关闭此选项。)
- 单击添加级别(Add Level )以将另一列添加到排序顺序。
- 单击Then by下拉菜单并选择要排序的下一列。
- 根据需要重复步骤 6 和 7(Steps 6 and 7)以配置排序参数。
在“排序(Sort)”对话框中单击会显示几个用于修改排序的不同选项,尽管其中一些选项,除非您确切知道每个选项如何影响您的排序,否则很可能会产生不需要的结果。
- 单击确定(OK)。
- 如果出现排序警告(Sort Warning)对话框,请选择展开选择(Expand
the selection),然后单击排序。(Sort.)
此外,当您选择一列时,Excel会分析该列中单元格的内容,并对应该填充(Excel)排序依据(Sort on)和排序(Order)字段的值做出有根据的猜测。除非您有更改这些内容的特定(和合乎逻辑的)理由,否则不要。(当然,除非您正在试验。我总是鼓励这样做——Excel 的撤消(Undo)功能非常有效。)
我相信您可以想象,这只是您如何在Excel中对数据进行排序的开始。但是,重要的是要记住,除非您小心,否则使用错误的参数可能会并列您的数据并完全改变其在电子表格中的位置。排序(Sort)快速且易于设置。好消息是撤消(Undo)也很快。不要(Don)害怕撤消并重(Undo)试。然后再次。
Basic One-Column and Multi-Column Data Sorting in Excel Spreadsheets
Two of Exсel’s primary functions are to allow you to manipulate and view data from different perspeсtives, and one of the program’s more simple but pоwerful tools for doing so іs the Sort function.
Whether it’s simple ascending/descending sorts, sorting on more than one variable to display data in groups or to maintain row integrity, or simple alphanumeric sorts to keep your tabular data orderly, sorting data in Excel is an essential skill.
Few programs are better suited for sorting tabular data than Excel, and your sorts can run from the simple and relatively uncomplicated to the highly sophisticated. While Excel’s sorting prowess—what you can do with the right data set and a little knowledge of the inner workings of the program—is indeed robust and in-depth, today’s tech tip focuses on two basic types of sorts, as follows:
- Sorting data on a single column
- Sorting data on multiple columns
Spreadsheets are, of course, made up of columns and rows of
cells, or tabular data, where each column comprises a logical division of facts,
figures, or any other details by category, such as, say, names, addresses, currency,
parts numbers, and so on—depending on the type of spreadsheet. Rows, on the
other hand, display people, objects, or figures side-by-side or in the same
instance or occurrence.
Depending on the type of spreadsheet and the data it contains, such as a tabular list of names, addresses, phone numbers, and other pertinent data, rows are frequently similar to database records.
When you sort rows of data, each row must maintain its integrity, without inadvertently moving data from one row to another, which, as you’ll see further down, is where sorting data on multiple columns comes in handy.
Sorting on a Single Field
You can sort the records in your spreadsheet by rows, and
you can sort the cells within records by columns. You can, of course, specify
ascending or descending sort orders. By default, ascending / alphanumerically,
the program arranges text from A to Z and numbers from smallest
to largest. Sorting with a descending sort order, of course, reverses the older
from Z to A, or so that larger numbers start at the top.
As with many functions in Excel, there are a few ways to perform a simple sort; everything you need for this type of sort, though, resides on the right mouse button flyout-menu, as shown below.
- Right-click a field in the column upon which you
want to sort the spreadsheet to open the popup menu.
- Scroll down and hover the cursor over Sort
to open the flyout menu.
- Click Sort A to Z for ascending or Sort
Z to A descending (Note that Excel does not include the data in cells in
row 1 in the sort; the program assumes that this row holds your column labels,
or headers.).
This simple sort works for many types of data, except when
your rows and columns have identical, or duplicate, data. In these instances,
you must sort on two or more columns, coming up next.
Sorting Records on Multiple Fields
Depending on the complexity of your data, you may need to
sort on more than one column. Perhaps the best example is sorting a database
alphabetically in last-name order. Say that your data has several people with
the same last names. In these instances, you’ll want to make sure that Linda
Johnson comes before Lydia Johnson, and Cherri Anderson displays after Charles
Anderson…You get the idea.
You can set up custom searches with multiple criteria from the Sort dialog box, like this.
- Click a cell in the first column of data you want to sort.
- Click Data just beneath the title bar to open the Data ribbon. (The ribbon is, of course, the row of contextual tabs across the top of the application window.)
- In the Sort & Filter section, click the Sort button to open the Sort dialog box.
- Click the Sort by drop-down and choose
the first column name to sort on. (Note that Excel displays the content in row
1 of the column, which in this case holds the column labels. You can turn this
option off by unchecking the My data has headers checkbox.)
- Click Add Level to add another column to
the sort order.
- Click the Then by drop-down and choose
the next column to sort on.
- Repeat Steps 6 and 7 as needed to
configure the parameters of your sort.
Clicking around in the Sort dialog
box reveals several different options for modifying your sorts, though some of
them, unless you know exactly how each one affects your sort, will most likely
produce unwanted results.
- Click OK.
- If you get a Sort Warning dialog box, select Expand
the selection and then click Sort.
Besides, when you choose a column, Excel analyzes the contents of the cells in that column and makes educated guesses as to what values should populate the Sort on and Order fields. Unless you have a specific (and logical) reason for changing these, don’t. (Unless you’re experimenting, of course. I always encourage that—and Excel’s Undo feature works marvelously.)
As I’m sure you can imagine, this is just the beginning of how
you can sort data in Excel. It’s important to remember, though, that unless
you’re careful, using the wrong parameters can juxtapose your data and completely
change its position in the spreadsheet. Sort is fast and easy to setup. The
good news is that Undo is fast, too. Don’t be afraid to Undo and try again. And
again.