Nội dung

Chapter 10 Analyzing Excel Data You can get more out of Excel by performing data analysis, which is the application of tools and techniques to organize, study, and reach conclusions about a specific collection of information. In this chapter you learn how to sort and filter a range, apply data validation rules to a range, convert a range to a table, create a data table, and summarize data using subtotals. 12_577639-ch10.indd 170 3/15/10 2:46 PM Sort a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Filter a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Set Data Validation Rules. . . . . . . . . . . . . . . . . . . . . . . 176 Convert a Range to a Table . . . . . . . . . . . . . . . . . . . . . 178 Create a Data Table . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Summarize Data with Subtotals . . . . . . . . . . . . . . . . . . . 182 12_577639-ch10.indd 171 3/15/10 2:46 PM Sort a Range You can make a range easier to read and easier to analyze by sorting the data based on the values in one or more columns. You can sort the data in either ascending or descending order. An ascending sort arranges the values alphabetically from A to Z, or numerically from 0 to 9; a descending sort arranges the values alphabetically from Z to A, or numerically from 9 to 0. Sort a Range 2 1 Click any cell in the range you 3 want to sort. 2 Click the Data tab. 3 Click Sort ( ). The Sort dialog box appears. 4 Click the Sort by and then click the field you want to use for the main sort level. 1 6 4 5 5 Click the Order and then click a sort order for the field. 6 To sort on another field, click Add Level. 172 12_577639-ch10.indd 172 3/15/10 2:46 PM 10 CHAPTER Analyzing Excel Data 9 7 8 • 7 0 Excel adds another sort level. Click the Then by and then click the field you want to use for the sort level. 8 Click the Order and then click a sort order for the field. 9 Repeat Steps 6 to 8 to add more sort levels as needed. 0 Click OK. • Is there a faster way to sort a range? Yes, as long as you only need to sort your range on a single column. First, click in any cell inside the column you want to use for the sort. Click the Data tab and then click one of the following buttons in the Sort & Filter group: Click for an ascending sort. Click for a descending sort. Excel sorts the range. How do I sort a range using the values in a row instead of a column? Excel normally sorts a range from top to bottom based on the values in one or more columns. However, you can tell Excel to sort the range from left to right based on the values in one or more rows. Follow Steps 1 to 3 to display the Sort dialog box. Click Options to display the Sort Options dialog box, select the Sort left to right option ( changes to ), and then click OK. 173 12_577639-ch10.indd 173 3/15/10 2:46 PM Filter a Range You can analyze table data much faster by filtering the data. Filtering a table means that you configure a field so that you only view the table records that you want to work with. One way to do this is to use the AutoFilter feature, which presents you with a list of check boxes for each unique value in a field. You filter the data by activating the check boxes for the records you want to see. Filter a Range 1 Click inside the table. 2 Click the Data tab. 3 Click Filter ( ). 3 2 1 4 • 4 Excel adds to each field. Click for the field you want to use as the filter. 174 12_577639-ch10.indd 174 3/15/10 2:46 PM 10 CHAPTER Analyzing Excel Data • Excel displays a list of the unique values in the field. 5 Click the check box for each value you want to see ( changes to ). 5 6 Click OK. 6 • 7 • • Excel filters the table to show only those records that have the field values you selected. Excel displays the number of records found. The field’s drop-down list displays a filter icon ( ). 7 To remove the filter, click Clear ( ). Can I create more sophisticated filters? Yes, by following these steps: 1 Follow Steps 1 to 4. 2 Click Number Filters. 4 2 Note: If the field is a date field, click Date Filters; if the field is a text field, click Text Filters. 3 5 3 Click the filter you want to use. 4 Type the value you want to use, or use the list 5 Click OK. box to select a unique value from the field. 175 12_577639-ch10.indd 175 3/15/10 2:46 PM Set Data Validation Rules You can make Excel data entry more efficient by setting up data entry cells to accept only certain values. To do this, you can set up a cell with data validation criteria that specify the allowed value or values. Excel also lets you tell the user what to enter by defining an input message that appears when the user selects the cell. Set Data Validation Rules 2 1 Click the cell you want to restrict. 2 Click the Data tab. 3 Click Data Validation ( ). The Data Validation dialog box appears. 4 Click the Settings tab. 5 In the Allow list, click the type of data you want to allow in the cell. 3 1 4 5 6 7 6 Use the Data list to click the operator you want to use to define the allowable data. 7 Specify the validation criteria, such as the Maximum and Minimum allowable values shown here. Note: The criteria boxes you see depend on the operator you chose in Step 6. 176 12_577639-ch10.indd 176 3/15/10 2:46 PM 10 CHAPTER Analyzing Excel Data 9 8 0 8 Click the Input Message tab. 9 Make sure the Show input ! @ message when cell is selected check box is clicked ( ). 0 Type a message title in the Title text box. ! Type the message you want to display in the Input message text box. @ Click OK. Excel configures the cell to accept only values that meet your criteria. • When the user selects the cell, the input message appears. Can I configure the cell to display a message if the user tries to enter an invalid value? How do I remove data validation from a cell? Yes. Follow Steps 1 to 3 to open the Data Validation dialog box, and then click the Error Alert tab. Make sure the Show error alert after invalid data is entered check box is clicked ( ), and then specify the Style, Title, and Error Message. Click OK. If you no longer need to use data validation on a cell, you should clear the settings. Follow Steps 1 to 3 to display the Data Validation dialog box and then click Clear All. Excel removes all the validation criteria, as well as the input message and the error alert. Click OK. 177 12_577639-ch10.indd 177 3/15/10 2:46 PM Convert a Range to a Table You can apply Excel’s powerful table tools to any range by first converting that range to a table. In Excel, a table is a collection of related information with an organizational structure that makes it easy to add, edit, and sort data. A table is a type of database where the data is organized into rows and columns: Each column represents a database field, and each row represents a database record. Convert a Range to a Table 1 Click a cell within the range that you want to convert to a table. 1 2 Click the Insert tab. 3 Click Table ( ). Note: You can also choose the Table command by pressing + . 2 3 178 12_577639-ch10.indd 178 3/15/10 2:46 PM 10 CHAPTER Analyzing Excel Data The Create Table dialog box appears. • • 4 5 Excel selects the range that it will convert to a table. If you want to change the range, click , drag the over the new range, mouse and then click . 4 Click OK. Excel converts the range to a table. • • • Excel applies a table format to the range. The Table Tools contextual tab appears. AutoFilter drop-down lists appear in each field heading. 5 Click the Design tab to see Excel’s table design tools. How do I add records to the table? To add a record to the end of the table, click inside the table, press + and then + to move to the last field in the last record, and then press . To add a record within the table, right-click the record above where you want to insert the new record, click Insert, and then click Table Rows Above. How do I convert a table back into a range? If you no longer require the table tools, you can convert the table back into a regular range. Select any cell within the table, click the Design tab, and then click Convert to Range ( ). When Excel asks you to confirm, click Yes. Excel removes the AutoFilter drop-down lists and hides the Table Tools contextual tab. 179 12_577639-ch10.indd 179 3/15/10 2:46 PM