Transcript exp07_e05_ppt

Exploring Microsoft
Office Excel 2007
Chapter 5
Data to Information
Robert Grauer, Keith Mulbery, Judy Scheeren
Copyright
© 2008 Pearson
Prentice Hall.
AllNext
rights Generation
Committed
to
Shaping
the
Copyright
2008
Pearson
Prentice Hall.
rightsreserved.
reserved.
Copyright© ©
2008
Prentice-Hall.
reserved.
All All
rights
of IT Experts.
1
Objectives








Design tables based on data table theory
Import data from text files and other sources
Apply conditional formatting
Apply advanced filter and sort methods
Create and use range names
Use database functions
Create and delete PivotTables and PivotCharts
Format, sort, filter, subtotal and refresh a PivotTable
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
2
Design Tables Based on Data
Table Theory


A poorly designed table may result in flawed
analysis
Plan the elements of a data table



Who will use the data table
What types of reports will be produced
What types of searches might be done
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
3
Import Data From Text Files and
Other Sources


Importing is the process of inserting data
from another application
Data may be in an Access database, in a text
file format, or stored on a mainframe
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
4
Import Data From Text Files
and Other Sources



Text file data is often imported into Excel for use
in a spreadsheet
Text files are made up of letters, digits, and
punctuation, including spaces
Comma Separated Value (CSV) files contain
fields separated by commas and rows separated
by a newline character
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
5
Import Data From Text Files and
Other Sources



Both text and CSV formatted files are used to
exchange data between different applications
A delimiter is a character used to separate
one column from another in a text file
The most common delimiters in a text file are
commas or tabs
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
6
Import Data From Text Files and
Other Sources

This figure shows comma delimited text file before and after
being imported into an Excel worksheet
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
7
Import Data From Text Files and
Other Sources



Often necessary to import an Access database into
Excel to analyze the data in more detail
Access databases may be imported in three ways:
as a table, as a PivotTable Report, or as a
PivotChart
When importing an Access database into Excel
maintain a live connection to the data

Changes in the Excel spreadsheet automatically updates
the database
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
8
Import Data From Text Files and
Other Sources


Data can be imported from sources other
than text files and Access databases
The From Other Sources command on the
Get External Data group lists several types
of sources
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
9
Import Data From Text Files
and Other Sources
SQL Server
Create a connection to a SQL Server Table and
import data as a table or PivotTable report
Analysis Services
Create a connection to a SQL Server Analysis
Services cube. Import data as a table or
PivotTable report
XML Data Import
Open or map an XML file into Excel
Data Connection WizardImport data for an unlisted format by using the
Data Connection Wizard or OLEDB
Microsoft Query
Import data for an unlisted format by using the
Microsoft Query Wizard and ODBC
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
10
Apply Conditional Formatting


Conditional formatting is used to apply
specific formats automatically to cells that
contain particular values or content
Use to highlight interesting cells or ranges of
cells, emphasize unusual or duplicate values,
or visualize data
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
11
Apply Conditional Formatting

Conditional formatting options include:





Highlight Cell Rules
Top/Bottom Rules
Data Bars
Color Scales
Icon Sets
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
12
Apply Conditional Formatting

Data bars are gradient colored bars that help
you visualize the value of a cell relative to other
cells



Used when identifying high and low values in
large amounts of data
Most useful when working with a big range of
values
More effective with wider columns than narrow
columns
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
13
Apply Conditional Formatting
A longer bar represents a
higher value
A shorter bar
represents a lower
value
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
14
Apply Conditional Formatting



Color scales formats cells with different colors
based on the relative value of a cell compared to
other adjacent cells
Using a two-color scale, the shade of the color
represents higher or lower values
Using a three-color scale, the shade of the color
represents the high, middle, or lower values
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
15
Apply Conditional Formatting
Color Scales
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
16
Apply Conditional Formatting




Icon sets are little graphics or symbols that
display in cells
Used to classify data into three to five
categories, based on the contents of the cells
Each icon represents a range of values
The icons are effective when you want to
annotate or present data that is quickly readable
and understandable
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
17
Apply Conditional Formatting
Icon sets
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
18
Apply Conditional Formatting

To apply a conditional format:




Select the cells
Click Conditional Formatting in the Styles group
on the Home tab
Select the specific conditional formatting style you
want to apply
Clear conditional formatting from an entire
sheet or from a range or cells, a table, or a
PivotTable
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
19
Apply Conditional Formatting

Use either Quick Formatting or Advanced
Formatting


Quick Formatting uses the options in Conditional
Formatting in the Styles group on the Home tab
For advanced formatting use the Conditional
Formatting Rules Manager dialog box
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
20
Apply Conditional Formatting
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
21
Apply Advanced Filtering and Sort
Methods



Data becomes more useful and important
when it is organized or sorted
Volume of data can be reduced by selecting a
subset that meets the specific conditions
Data that is sorted and extracted using
specific conditions make it meaningful
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
22
Apply Advanced Filtering and Sort
Methods
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
23
Apply Advanced Filtering and Sort
Methods




Sort by cell attributes
Filter records based on cell attributes
Filter data by using predefined number filters
Create custom filters by using a combination
of different number filters
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
24
Apply Advanced Filtering and Sort
Methods

A criteria range is an area separate from the data
table and specifies the conditions used to filter the
table





Independent of the table
Exists as a separate area on a worksheet
Must be at least two rows deep and one column wide
The first row contains the field names as they appear in the
table
The second row contains the conditions or values you are
looking for
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
25
Apply Advanced Filtering and Sort
Methods

A relational operator is a symbol that is
used to compare cell contents to another cell
or value


Relational operators include <, >, <=, >=, <>, and
=
Use equal (=) and unequal (<>) symbols to select
records with empty and nonempty fields,
respectively
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
26
Apply Advanced Filtering and Sort
Methods
Relational operator
used to set criteria
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
27
Apply Advanced Filtering and Sort
Methods


Use the Top 10 AutoFilter option to see the
top or bottom 10 records in a list
You can also specify a percentage such as
the top 10% of the records in a list
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
28
Apply Advanced Filtering and Sort
Methods

The Advanced command displays the
Advanced Filter dialog box





Enables you to filter the table in place
Copy the selected records to another area in the
worksheet
Specify the list range
Specify the criteria range
Display unique records only
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
29
Apply Advanced Filtering and Sort
Methods
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
30
Create and Use Range Names


A range name is a word or string of
characters that represents cell, range of cells,
or constant value
Use the name to reference cells in formulas
and functions
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
31
Create and Use Range Names

A name can be used in any formula or
function instead of cell addresses


Names used in formulas are absolute references
Range names must be unique within a
workbook
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
32
Create and Use Range Names
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
33
Create and Use Range Names

Use the Name Manager dialog box to edit existing range
names, Delete existing range names, and create new range
names
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
34
Create and Use Range Names


Using range names in formulas is helpful
when you need to create formulas that
reference a cell or a range of cells on a
different worksheet
Because the range name creates an absolute
reference to a cell or range of cells, the
range-name reference in a formula is
absolute
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
35
Using Database Functions

The database functions analyze data for selected
records in a table





affect only records that satisfy the specified criteria
similar to statistical functions (SUM, AVERAGE, MAX, MIN,
COUNT)
return a value and save you time
use a criteria range that defines the search parameters
Using range names can simplify the construction of
database functions
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
36
Using Database Functions

Database functions have three arguments:



Database -- the entire table, including column
headings and all columns, on which the function
operates
Field -- the column in the database that contains
the values operated on by the function
Criteria -- defines the conditions to be met by the
function
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
37
Using Database Functions
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
38
Using Database Functions





DSUM -- Calculates the total of values in a field that
meets the specified condition(s)
DAVERAGE -- Determines the mathematical average of
values in a field that meets the specified condition(s)
DMAX -- Identifies the largest value in a field that meets
the specified condition(s)
DMIN -- Identifies the smallest value in a field that meets
the specified condition(s)
DCOUNT -- Counts the number of records for a field that
meets the specified condition(s)
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
39
Using Database Functions

DCOUNTA -- Counts the number of records that
contain values (nonblank) in a field that meets the
specified conditions

DPRODUCT -- Multiplies the values within a field
that meets the specified condition(s)

DSTDEV -- Calculates the standard deviation for
values in a field that meets the specified condition(s)
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
40
Using Database Functions

DVAR -- Estimates the sample population
variance for values in a field that meets the
specified condition(s)

DVARP -- Estimates the enter population
variance for values in a field that meets the
specified condition(s)
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
41
Create and Delete PivotTables and
PivotCharts

A PivotTable is a way to quickly summarize
large amounts of data by:




Querying large amounts of data in user-friendly
ways
Subtotaling numeric data, summarizing data, and
creating custom calculations
Expanding and collapsing levels of data to
facilitate focusing
Pivoting or moving rows to columns or columns to
rows to see different summaries of data
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
42
Create and Delete PivotTables and
PivotCharts

A PivotChart is a graphical representation of
data in a PivotTable


enables you to visually present the data in a
report
always has an associated PivotTable that has a
corresponding layout
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
43
Create and Delete PivotTables and
PivotCharts
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
44
Create and delete PivotTables and
PivotCharts

First, think about the design of the data table
itself



Use meaningful column headings, accurate data,
and most important do not leave any blank rows in
your data table
One column must have duplicate values to create
categories for organizing and summarizing data
Another column must have numeric values
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
45
Create and delete PivotTables and
PivotCharts


The PivotTable Field List window is used to
add, remove or rearrange fields to a
PivotTable or a PivotChart
Displays two sections:


A field section at the top shows fields from an
external data source and is used to add or remove
fields
A layout section at the bottom is used to arrange
and reposition fields
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
46
Create and Delete PivotTables and
PivotCharts

To design the PivotTable:




Drag a field to the Report Filter area in the Field List or
right-click a field name and choose Add to Report Filter
Drag a field to the Column Labels or right-click a field and
choose Add to Column Labels to organize data into
columns
Drag a field to the Row Labels area of the Field List or
right-click a field and choose Add to Row Labels to
organize data into groups on rows
Drag a field to the Values area in the Field List or right-click
a field and choose Add to Values
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
47
Format, sort, filter, subtotal and
refresh a PivotTable




Add formatting to a PivotTable to enhance its
information value
Sort and filter your table to best analyze the
data
Most PivotTables subtotal and total the
values
PivotTables and PivotCharts are NOT
dynamic; you must refresh the reports
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
48
Format, sort, filter, subtotal and
refresh a PivotTable
Formatting
PivotTables is
primarily done in
the PivotTable
Tools Design
tab.
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
49
Format, sort, filter, subtotal and
refresh a PivotTable
Sorting and filtering
the PivotTable makes
the data more
manageable and
easier to analyze.
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
50
Format, sort, filter, subtotal and
refresh a PivotTable
Displaying the
subtotals
draws
attention to the
totals
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
51
Format, sort, filter, subtotal and
refresh a PivotTable


Excel does not update PivotTables and
PivotCharts automatically
To refresh, right-click any cell in a PivotTable
and select Refresh
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
52
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
53