[ Pobierz całość w formacie PDF ]
.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.This means, for exam-ple, that a date cell displaying November 16, 2000 will be sorted ahead of a date celldisplaying 12/27/2000 (because the first date has a lower numeric value), eventhough numbers occur before letters in the ANSI and Unicode character-encodingsequences.Ï% Numeric values are sorted ahead of text values.The value 98052 would therefore besorted ahead of the value, 1 Microsoft Way, because the former is a number and thelatter is text.Ï% Text (and text that includes numbers) is sorted in the following order:0 1 2 3 4 5 6 7 8 9 (space) ! $ % & ( ) * ,./ : ; ? @ [ \ ] ^ _ ` { |} ~ + a b c d e f g h i j k l m n o p q r s t u v w x y zÏ% Apostrophes and hyphens are usually ignored.If two values are identical except forthe presence of a hyphen in one, however, the value without the hyphen is sortedahead of the value with the hyphen.Ï% Logical values are sorted after text, and FALSE is sorted before TRUE.Ï% Error values (#DIV/0!, #NAME?, #VALUE, #REF!, #N/A, #NUM!, and #NULL!) aresorted after logical values.Excel regards all error values as equivalent; that is, itleaves them in the order it finds them.Ï% Blanks are placed last, in both ascending and descending sorts [ Pobierz caÅ‚ość w formacie PDF ]
zanotowane.pl doc.pisz.pl pdf.pisz.pl odbijak.htw.pl
.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.This means, for exam-ple, that a date cell displaying November 16, 2000 will be sorted ahead of a date celldisplaying 12/27/2000 (because the first date has a lower numeric value), eventhough numbers occur before letters in the ANSI and Unicode character-encodingsequences.Ï% Numeric values are sorted ahead of text values.The value 98052 would therefore besorted ahead of the value, 1 Microsoft Way, because the former is a number and thelatter is text.Ï% Text (and text that includes numbers) is sorted in the following order:0 1 2 3 4 5 6 7 8 9 (space) ! $ % & ( ) * ,./ : ; ? @ [ \ ] ^ _ ` { |} ~ + a b c d e f g h i j k l m n o p q r s t u v w x y zÏ% Apostrophes and hyphens are usually ignored.If two values are identical except forthe presence of a hyphen in one, however, the value without the hyphen is sortedahead of the value with the hyphen.Ï% Logical values are sorted after text, and FALSE is sorted before TRUE.Ï% Error values (#DIV/0!, #NAME?, #VALUE, #REF!, #N/A, #NUM!, and #NULL!) aresorted after logical values.Excel regards all error values as equivalent; that is, itleaves them in the order it finds them.Ï% Blanks are placed last, in both ascending and descending sorts [ Pobierz caÅ‚ość w formacie PDF ]