[ Pobierz całość w formacie PDF ]
.Excel sorts in the order you want on the column inwhich the selection resides.When you select Data, Sort, the Sort dialog box appears.If this is the first time you ve sortedthe current list, the Sort dialog box, shown in Figure 28-14, appears with default options.Ifyou ve sorted the list before, the dialog box will display the sort parameters you last used.723Chapter 28Part 9: Managing Databases and ListsMicrosoft Office Excel 2003 Inside Outf28ie14Figure 28-14.Excel recognizes the extent of your list and the presence or absence of aheader row.If your list includes a header row that should remain in place while the other rows are sorted,Excel usually recognizes that fact and selects the Header Row option at the bottom of the dia-log box.If the program, for some reason, fails to notice a header row and selects No HeaderRow instead, you can correct it before clicking OK.Excel also assumes that the column containing the active cell is the one on which you want tosort, and it fills out the Sort By box with either the label at the top of that column (if you havea header row) or the letter designation of that column.Because users tend to prefer ascendingsorts, it also selects the Ascending option button by default.To carry out the sort, make anynecessary adjustments to these settings and then click OK.Tip Save toolbar spaceThe Standard toolbar includes two sorting buttons, one for ascending sorts, the other fordescending sorts.You can make either tool do double duty.For a descending sort, holddown Shift while you click the Ascending Sort button.For an ascending sort, hold downShift while you click the Descending Sort button.If you re short of space on the toolbar, youcan eliminate the Descending Sort button and use the Shift key to reverse the default sortdirection.Sorting on More than One ColumnYou can sort on as many as three columns at once.To sort on more than one column, fill outone or both of the Then By boxes in the Sort dialog box.For example, to sort the staff listshown in Figure 28-1 first in descending order by Salary and then in ascending order by LastName, fill out the dialog box as shown in Figure 28-15.Excel then rearranges the list as shownin Figure 28-16.724Chapter 28Part 9: Managing Databases and ListsManaging Information in Listsf28ie15Figure 28-15.To sort on two columns, supply the names of the column headings in the SortBy and Then By boxes.f28ie16Figure 28-16.The rows are now arranged in descending order by Salary, with rows ofcommon salary in ascending order by Last Name.Sorting Only Part of a ListIf you select a single cell before choosing Data, Sort, Excel scans the area surrounding theselected cell, highlights the entire contiguous range of cells, and assumes that you want tosort that entire range.If you want to sort only part of a list, start by selecting only thoserows and columns you want to sort.Then choose Data, Sort.To sort rows 10 through 20in Figure 28-1, for example, start by selecting A10:G20.You can t specify a sort range in the Sort dialog box.You must select the range before youopen the dialog box.The dialog box itself doesn t indicate the range that Excel is about tosort.Check your worksheet immediately after a sort and use the Undo command if you don tlike what you get.725Chapter 28Part 9: Managing Databases and ListsMicrosoft Office Excel 2003 Inside OutSorting by ColumnsThus far, our examples have involved sorting by row leaving the columns alone.You alsocan sort by columns, leaving the order of the rows alone.To sort by columns, follow these steps:1 Choose Data, Sort.2 Click Options in the Sort dialog box, and select the Sort Left to Right option.3 Click OK to return to the main part of the Sort dialog box.4 Fill out the boxes and option buttons in the Sort dialog box, and click OK.Figures 28-17 and 28-18 show a worksheet before and after a left-to-right ascending sort.f28ie17Figure 28-17.The years are in descending order in this simple financial worksheet.f28ie18Figure 28-18.Use the Sort Left To Right option to reorder the years into an ascendingsequence.To perform this sort, follow these steps:1 Select B1:F4.2 Choose Data, Sort.726Chapter 28Part 9: Managing Databases and ListsManaging Information in Lists3 Click Options in the Sort dialog box.4 Make sure that Sort Left To Right is selected, and then click OK.5 Make your selections for the sort order and click OK.It s best to select all the data you want to sort, rather than just a single cell, when you re sort-ing laterally.If you select only one cell, Excel will propose to sort everything in the worksheet,including the labels in your first column.In other words, Excel doesn t recognize row head-ings in column-oriented sorts.Sorting Cells That Contain FormulasYou need to exercise caution when sorting cells that contain formulas with cell references.Ifyou sort by row, references to other cells in the same row will be correct after the sort, but ref-erences to cells in other rows of the list will no longer be correct.Similarly, if you sort by column, references to other cells in the same columns will be correctafter the sort, but references to cells in other columns will be broken.With either kind of sort,relative references to cells outside the list will be broken by the sort.The before-and-after illustration in Figure 28-19 demonstrates the hazards of sorting rangesthat contain formulas.Row 5 of the worksheet calculates the year-to-year change in profit,using relative-reference formulas.Cell C5, for example, uses the formula =C4-B4 to calculatethe difference between the profits for 2000 and 1999.Each of the other formulas also refer-ences the cell directly to its left.After sorting by column, each formula in row 5 of this figure still references the cell to the left,but now we have a #VALUE! error in B5, because B4 tries to subtract the text Profit from thenumber 61,000.f28ie19Figure 28-19.Sorting this worksheet laterally has broken the formulas in row 5.Interestingly, if you carry out this sort manually by picking up each column and moving itto its new location Excel updates the formulas appropriately after each move.If you do it bychoosing Data, Sort, Excel is unable to make the necessary adjustments.727Chapter 28Part 9: Managing Databases and ListsMicrosoft Office Excel 2003 Inside OutTo avoid the problems associated with sorting ranges containing formulas, observe thefollowing rules:Ï% In formulas that reference cells outside the sort range, use only absolute references.Ï% When sorting by row, avoid formulas that reference cells in other rows.If you must usesuch formulas, reference cells by name, not by address.Ï% When sorting by column, avoid formulas that reference cells in other columns.If youmust use such formulas, reference cells by name, not by address.Understanding Excel s Default Sorting SequenceTo avoid surprises, you should understand the following points about the way Excel sorts:Ï% Excel sorts cells according to their contents, not their formats [ Pobierz caÅ‚ość w formacie PDF ]