Download
1 / 35
350 likes | 481 Views
Exploring Microsoft Excel. Chapter 7 List and Data Management: Converting Data to Information By Robert T. Grauer Maryann Barber. Objectives (1 of 2). Create a list Add, edit and delete records in an existing list Use Text Import Wizard to import data from other applications
E N D
Exploring Microsoft Excel Chapter 7 List and Data Management: Converting Data to Information By Robert T. Grauer Maryann Barber Exploring Microsoft Excel 2002 Chapter 7
Objectives (1 of 2) • Create a list • Add, edit and delete records in an existing list • Use Text Import Wizard to import data from other applications • Describe the TODAY function and use date arithmetic • Use the Sort command Exploring Microsoft Excel 2002 Chapter 7
Objectives (2 of 2) • Use the database functions: DSUM, DAVERAGE, DMAX, DMIN, and DCOUNT • Use AutoFilter and Advanced Filter • Use the Subtotals command • Use a pivot table and pivot chart; save a pivot table as a web page Exploring Microsoft Excel 2002 Chapter 7
Overview • Fundamentals of list management • Display selected records • Sort the list • Use database functions, criteria range, and arithmetic • Import data from other applications • Use Excel’s data analysis tools Exploring Microsoft Excel 2002 Chapter 7
List and Data Management • Data management based on lists in Excel • a list is an area of a worksheet that contains similar rows of data • Need valid input to produce valid output • verify spelling of field names and records (use the Spell Check) • remember: Garbage In, Garbage Out • Edit the list through Insert Row and Columns command and Delete command Exploring Microsoft Excel 2002 Chapter 7
Database Concepts • Lists can be used as simple databases • Record is the individual information contained in a row • Field is unique information contained in a column for a record • Primary key is a unique field or combination like social security number Exploring Microsoft Excel 2002 Chapter 7
Lists and Data commands • Data Form Command provides easy way to add, edit and delete records • Sort command arranges lists according to value in fields • can sort on text fields or numeric fields • can sort in ascending or descending order • can sort on up to three fields • Date Arithmetic is a powerful tool for formulas • Today() function always returns the current data Exploring Microsoft Excel 2002 Chapter 7
Data Form Command Exploring Microsoft Excel 2002 Chapter 7
Sort Command Sort Descending Sort Ascending Exploring Microsoft Excel 2002 Chapter 7
Text Import Wizard • Converts an ASCII (text) file into an Excel workbook • Two file formats: fixed width and delimited • fixed width: each field uses the same number of positions in each record • delimited: fields are separated by a specific character, such as a comma or tab • Use the Open command • select All Files in the Files of Type list box to open the Text Import Wizard • you can also use the Data menu (Get External Data, Import Text File) Exploring Microsoft Excel 2002 Chapter 7
Text Import Wizard Exploring Microsoft Excel 2002 Chapter 7
Text Import Wizard Exploring Microsoft Excel 2002 Chapter 7
Hands-On Exercise 1 • Objective: To use the Text Import Wizard; to add, edit, and delete records in an employee list • Text Import Wizard • Add new records • The Spell Check • Sort the list • Delete a record • Enter the hire dates • Format the hire dates Exploring Microsoft Excel 2002 Chapter 7
Information versus Data • Data is simply facts • Information is data arranged in a useful format • Decisions in an organization are based on information • Database commands, functions and reports help turn data into information Exploring Microsoft Excel 2002 Chapter 7
Filter commands • AutoFilter is a subset of records which meet a set of criteria • Advanced Filter allows for complex criterion and storing records in a separate worksheet area • Criteria range specifies the values to search for in records Exploring Microsoft Excel 2002 Chapter 7
AutoFilter • Set criteria for fields using the drop-down list for the field • only rows meeting the criteria are displayed • other rows are hidden, not deleted • You may set criteria on multiple fields • if you set criteria on multiple fields, a row must meet all the criteria to be displayed Exploring Microsoft Excel 2002 Chapter 7
Auto Filter Exploring Microsoft Excel 2002 Chapter 7
Advanced Filter • Extends AutoFilter in two important ways: • create more complex criteria, such as the ability to set OR conditions (display rows that meet one of the criteria rather than all of them) • copy the rows to another section on the worksheet, leaving the original list intact Exploring Microsoft Excel 2002 Chapter 7
Advanced Filter Exploring Microsoft Excel 2002 Chapter 7
Understanding Criteria Ranges • Must contain at least two rows--field names and a second row of values • Same row entries imply an AND condition • Values entered in different rows meet the OR condition • Empty rows return all records • Criteria are case-insensitive • Text entries are treated as though they were followed by a wildcard (*) Exploring Microsoft Excel 2002 Chapter 7
Understanding Criteria Ranges • Relational operators can be used to find a designated range • i.e. >40000 returns rows where the value is greater than 40,000 • Upper and Lower Boundaries can be established • use the same field twice in the criteria range and relational operators to set the boundaries • Equal and unequal signs select empty and nonempty records Exploring Microsoft Excel 2002 Chapter 7
Criteria Ranges (1 of 2) Criteria ranges using same row imply AND Criteria Ranges using different rows imply OR Exploring Microsoft Excel 2002 Chapter 7
Criteria Ranges (2 of 2) Establish upper and lower boundaries Exploring Microsoft Excel 2002 Chapter 7
Database Functions • Parallel arithmetic operations of the same statistical functions • performs the operations only on rows that meet certain criteria • DSUM • DAVERAGE • DMAX • DMIN • DCOUNT Exploring Microsoft Excel 2002 Chapter 7
Using Database Functions Exploring Microsoft Excel 2002 Chapter 7
Subtotals Commands • Subtotals command in the Data menu computes subtotals based on data groups • list needs to be sorted on the field(s) you want to base subtotals on • Uses a summary function like SUM or AVERAGE • Outline format allows for several views of data Exploring Microsoft Excel 2002 Chapter 7
Subtotals Dialog Box Exploring Microsoft Excel 2002 Chapter 7
Hands-On Exercise 2 (1 of 2) • Objective: to sort a list on multiple keys; to demonstrate the AutoFilter and Advanced Filter commands; to define a named range; to use database functions • Calculate the years of service • The AutoFilter command • The Custom AutoFilter command • The Advanced Filter command • The Insert Name command • Database functions Exploring Microsoft Excel 2002 Chapter 7
Hands-On Exercise 2 (2 of 2) • The DAVERAGE function • The DMAX, DMIN, DSUM & DCOUNT functions • Change the criteria • Create the subtotals • Collapse and expand the subtotals Exploring Microsoft Excel 2002 Chapter 7
Pivot Tables • Pivot tables extends the capability of database functions by presenting the data in summary form • divides the records in a list into categories and computes summary statistics for those categories • can be refreshed when cells in the underlying list are added, deleted, or edited • Use PivotTable Wizard in the Data menu • Displays a Pivot Table toolbar • Can also display Pivot charts • Can be saved as Web pages with full interactivity Exploring Microsoft Excel 2002 Chapter 7
Pivot Tables Dialog Box Exploring Microsoft Excel 2002 Chapter 7
Pivot Chart Exploring Microsoft Excel 2002 Chapter 7
Hands-On Exercise 3 • Objective: to create a pivot table and pivot chart; to create a Web page based on the pivot table • Start the Pivot Table Wizard • Complete the pivot table • Modify the list • Modify the pivot table • Change the chart type • Complete the chart • Save the pivot table as a Web page • Pivot the Web page • Change the underlying data Exploring Microsoft Excel 2002 Chapter 7
Summary (1 of 2) • List contains records of data • Information is data arranged in a useful format • Dates can be used for calculations • Importing data from other applications • Filtered list displays only a subset of records Exploring Microsoft Excel 2002 Chapter 7
Summary (2 of 2) • Subtotals can be inserted into a list • Database functions: DSUM, DAVERAGE, DMAX, DMIN and DCOUNT • Pivot table presents data in summary form • Pivot tables can be saved as interactive web pages Exploring Microsoft Excel 2002 Chapter 7
