Lecture 5: Data Analysis for Modeling

1
Lecture 5 Data Analysis for Modeling
2
Data Analysis in the Context of Modeling

• Supports the modeling process
• Improves accuracy of model
• Improves usefulness of conclusions
• Modeling is the primary goal.
• Data analysis is a means to that goal.

3
Topics for Chapter

• Finding facts in databases
• Editing,
• Searching,
• Sorting,
• Filtering, and
• Tabulating

4
Finding Facts from Databases

• Tables of information
• Each row is a record in the database.
• Each column is a field for the records.
• Excel calls such a table a list.

5
Excel Lists

• First row contains names for each field
• Each successive row contains one record.
• Lists may be
• Searched and edited
• Sorted
• Filtered
• Tabulated

6
Searching and Editing Lists

• First assign a range name to entire list.
• Include column titles.
• With list selected Form on the Quick Access
  Toolbar.
• Examine records one at a time
• Find Prev.
• Find Next.
• Enter new record with New button.
• Delete record with Delete button.

7
Database Form
8
Criteria Button

• Selected Form on the Quick Access Toolbar
• Allows for searching of records
• Enter data into a field.
• Click Find Next.

9
Alternate Excel Search Techniques

• Highlight entire database.
• Home"Editing?FindSelect?Find.
• Use Find and Replace to edit entries.
• In Find and Replace
• ? stands for any single symbol
• stands for any sequence of symbols

10
Sorting Data Sort Command
11
Filtering

• Choose Home?Editing?SortFilter?Filter.
• Will filter lists based on values
• Found under arrow at the title of each column
• Arrow on title turns blue to remind list is
  filtered
• Can remove filter by
• Choose Home?Editing?Sort Filter?Filter?Clear

12
More Filtering

• Top 10 option returns records with smallest or
  largest value of a numerical record
• Custom option allows filtering with compound
  criteria
• More complicated compound criteria can be
  achieved by clicking Custom Filter option under
  Number Filters or Text Filters.

13
Tabulating

• Select Data Pivot Table.
• Creates summary tables
• Layout button on
• third step of wizard
• creates the format
• for the table