Cạc Sắp Xếp Dữ Liệu Trong Excel 2007

Excel for shimatest.shop 365 Excel for the website Excel 2021 Excel 2019 Excel năm 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 More...Less

Sorting data is an integral part of data analysis. You might want lớn arrange a các mục of names in alphabetical order, compile a các mục of product inventory levels from highest lớn lowest, or order rows by colors or icons. Sorting data helps you quickly visualize và understand your data better, organize & find the data that you want, và ultimately make more effective decisions.

Bạn đang xem: Cạc sắp xếp dữ liệu trong excel 2007

You can sort data by text (A khổng lồ Z or Z khổng lồ A), numbers (smallest lớn largest or largest khổng lồ smallest), & dates và times (oldest to lớn newest và newest lớn oldest) in one or more columns. You can also sort by a custom list you create (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set.


Notes: 

To find the đứng top or bottom values in a range of cells or table, such as the đứng đầu 10 grades or the bottom 5 sales amounts, use AutoFilter or conditional formatting.


*

WindowsWeb

Sort text

Select a cell in the column you want lớn sort.

On the Data tab, in the Sort và Filter group, vì chưng one of the following:

To quick sort in ascending order, click (Sort A lớn Z).

To quick sort in descending order, click (Sort Z to A).


Notes:  Potential Issues

Check that all data is stored as text If the column that you want khổng lồ sort contains numbers stored as numbers và numbers stored as text, you need to lớn format them all as either numbers or text. If you bởi vì not apply this format, the numbers stored as numbers are sorted before the numbers stored as text. To format all the selected data as text, Press Ctrl+1 lớn launch the Format Cells dialog, click the Number tab và then, under Category, click General, Number, or Text.


Sort numbers

Select a cell in the column you want to sort.

On the Data tab, in the Sort & Filter group, bởi vì one of the following:

To sort from low to high, click (Sort Smallest to Largest).

To sort from high lớn low, click (Sort Largest to lớn Smallest).


Notes: 

Potential Issue


Sort dates or times

Select a cell in the column you want lớn sort.

On the Data tab, in the Sort và Filter group, vì chưng one of the following:

To sort from an earlier lớn a later date or time, click (Sort Oldest lớn Newest).

To sort from a later to an earlier date or time, click (Sort Newest khổng lồ Oldest).


Notes:  Potential Issue


Sort by more than one column or row

You may want to sort by more than one column or row when you have data that you want lớn group by the same value in one column or row, & then sort another column or row within that group of equal values. For example, if you have a Department column và an Employee column, you can first sort by Department (to group all the employees in the same department together), & then sort by name (to put the names in alphabetical order within each department). You can sort by up to 64 columns.


Note: For best results, the range of cells that you sort should have column headings.


Select any cell in the data range.

*

On the Data tab, in the Sort và Filter group, click Sort.

In the Sort dialog box, under Column, in the Sort by box, select the first column that you want lớn sort.

*

Under Sort On, select the type of sort. Bởi vì one of the following:

To sort by text, number, or date and time, select Values.

To sort by format, select Cell Color, Font Color, or Cell Icon.

Under Order, select how you want lớn sort. Vì one of the following:

For text values, select A khổng lồ Z or Z to lớn A.

For number values, select Smallest lớn Largest or Largest khổng lồ Smallest.

For date or time values, select Oldest lớn Newest or Newest lớn Oldest.

To sort based on a custom list, select Custom List.

To địa chỉ another column lớn sort by, click Add Level, and then repeat steps three through five.

*

To copy a column to lớn sort by, select the entry and then click Copy Level.

To delete a column khổng lồ sort by, select the entry và then click Delete Level.


Note: You must keep at least one entry in the list.


To change the order in which the columns are sorted, select an entry và then click the Up or Down arrow next to lớn the Options button to change the order.Entries higher in the danh mục are sorted before entries lower in the list.


Sort by cell color, fonts color, or icon

If you have manually or conditionally formatted a range of cells or a table column by cell color or phông color, you can also sort by these colors. You can also sort by an icon set that you created with conditional formatting.

Select a cell in the column you want to sort.

On the Data tab, in the Sort & Filter group, click Sort.

In the Sort dialog box, under Column, in the Sort by box, select the column that you want to lớn sort.

*

Under Sort On, select Cell Color, Font Color, or Cell Icon.

*

Under Order, click the arrow next to the button & then, depending on the type of format, select a cell color, fonts color, or cell icon.

Next, select how you want to lớn sort. Vì one of the following:

To move the cell color, phông color, or icon khổng lồ the vị trí cao nhất or to lớn the left, select On Top for a column sort, and On Left for a row sort.

To move the cell color, font color, or icon to lớn the bottom or to lớn the right, select On Bottom for a column sort, và On Right for a row sort.


Note: There is no mặc định cell color, phông color, or icon sort order. You must define the order that you want for each sort operation.


To specify the next cell color, phông color, or icon khổng lồ sort by, click Add Level, và then repeat steps three through five.

Make sure that you select the same column in the Then by box and that you make the same selection under Order.

Keep repeating for each additional cell color, font color, or icon that you want included in the sort.

Xem thêm: 10 Bộ Phim Hay Của Pháp Chọn Lọc, 7 Bộ Phim Pháp Mà Bạn Nhất Định Phải Xem!


Sort by a custom list

You can use a custom danh sách to sort in a user-defined order. For example, a column might contain values that you want khổng lồ sort by, such as High, Medium, & Low. How can you sort so that rows containing High appear first, followed by Medium, and then Low? If you were lớn sort alphabetically, an “A to Z” sort would put High at the top, but Low would come before Medium. Và if you sorted “Z khổng lồ A,” Medium would appear first, with Low in the middle. Regardless of the order, you always want “Medium” in the middle. By creating your own custom list, you can get around this problem.

Optionally, create a custom list:

In a range of cells, enter the values that you want khổng lồ sort by, in the order that you want them, from đứng top to bottom as in this example.

*

Select the range that you just entered. Using the preceding example, select cells A1:A3.

Go to File > Options > Advanced > General > Edit Custom Lists, then in the Custom Lists dialog box, click Import, and then click OK twice.


Notes: 

You can create a custom danh mục based only on a value (text, number, & date or time). You cannot create a custom list based on a format (cell color, phông color, or icon).

The maximum length for a custom menu is 255 characters, and the first character must not begin with a number.


Select a cell in the column you want khổng lồ sort.

On the Data tab, in the Sort & Filter group, click Sort.

In the Sort dialog box, under Column, in the Sort by or Then by box, select the column that you want to lớn sort by a custom list.

Under Order, select Custom List.

In the Custom Lists dialog box, select the các mục that you want. Using the custom list that you created in the preceding example, click High, Medium, Low.

Click OK.


Case sensitive sort

On the Data tab, in the Sort và Filter group, click Sort.

In the Sort dialog box, click Options.

In the Sort Options dialog box, select Case sensitive.

*

Click OK twice.


Sort from left khổng lồ right

It"s most common to sort from đứng đầu to bottom, but you can also sort from left to lớn right.


Note: Tables don"t tư vấn left lớn right sorting. To vày so, first convert the table khổng lồ a range by selecting any cell in the table, & then clicking Table Tools > Convert lớn range.


Select any cell within the range you want to lớn sort.

On the Data tab, in the Sort & Filter group, click Sort.

In the Sort dialog box, click Options.

In the Sort Options dialog box, under Orientation, click Sort left to lớn right, and then click OK.

*

Under Row, in the Sort by box, select the row that you want khổng lồ sort. This will generally be row 1 if you want to lớn sort by your header row.

*


Tip: If your header row is text, but you want to lớn order columns by numbers, you can địa chỉ cửa hàng a new row above your data range and add numbers according to lớn the order you want them.


To sort by value, select one of the options from the Order drop-down:

For text values, select A to lớn Z or Z khổng lồ A.

For number values, select Smallest to lớn Largest or Largest khổng lồ Smallest.

For date or time values, select Oldest khổng lồ Newest or Newest khổng lồ Oldest.

To sort by cell color, fonts color, or cell icon, do this:

Under Sort On, select Cell Color, Font Color, or Cell Icon.

Under Order select a cell color, font color, or cell icon, then select On Left or On Right.


Note: When you sort rows that are part of a worksheet outline, Excel sorts the highest-level groups (level 1) so that the detail rows or columns stay together, even if the detail rows or columns are hidden.


Sort by a partial value in a column

To sort by a part of a value in a column, such as a part number code (789-WDG-34), last name (Carol Philips), or first name (Philips, Carol), you first need to lớn split the column into two or more columns so that the value you want to sort by is in its own column. To vị this, you can use text functions to lớn separate the parts of the cells or you can use the Convert Text lớn Columns Wizard. For examples và more information, see Split text into different cells and Split text among columns by using functions.


Sort a range within a larger range

Warning: It is possible to sort a range within a range, but it is not recommended, because the result disassociates the sorted range from its original data. If you were khổng lồ sort the following data as shown, the selected employees would be associated with different departments than they were before.


*

Fortunately, Excel will warn you if it senses you are about to lớn attempt this:

*

If you did not intend lớn sort like this, then press the Expand the selection option, otherwise select Continue with the current selection.

If the results are not what you want, click Undo

*
.


Note: You cannot sort this way in a table.


Learn more about general issues with sorting

If you get unexpected results when sorting your data, vày the following:

kiểm tra to see if the values returned by a formula have changed If the data that you have sorted contains one or more formulas, the return values of those formulas might change when the worksheet is recalculated. In this case, make sure that you reapply the sort lớn get up-to-date results.

Unhide rows và columns before you sort Hidden columns are not moved when you sort columns, và hidden rows are not moved when you sort rows. Before you sort data, it"s a good idea khổng lồ unhide the hidden columns và rows.

Check the locale setting Sort orders vary by locale setting. Make sure that you have the proper locale setting in Regional Settings or Regional và Language Options in Control Panel on your computer. For information about changing the locale setting, see the Windows help system.

Enter column headings in only one row If you need multiple line labels, wrap the text within the cell.

Turn on or off the heading row It"s usually best to have a heading row when you sort a column to make it easier to understand the meaning of the data. By default, the value in the heading is not included in the sort operation. Occasionally, you may need to turn the heading on or off so that the value in the heading is or is not included in the sort operation. Bởi vì one of the following:

To exclude the first row of data from the sort because it is a column heading, on the Home tab, in the Editing group, click Sort và Filter, click Custom Sort & then select My data has headers.

To include the first row of data in the sort because it is not a column heading, on the Home tab, in the Editing group, click Sort & Filter, click Custom Sort, và then clear My data has headers.