Get documents about "Lists
Document Sample
Lists
Things to do
A List
A List
A collection of similar data stored in a structured manner
(rows / cols)
Typically
A row of data contains a single entry / record
Should not be confused with a database
Although similar to database in the types of information stored
2
How to Design a List
Construct a data definition table
Serves to document the spreadsheet
Serves to clearly define the data itself
Should occur in a separate documentation sheet
Field Name Description
ID # Vehicle’s Identification number
Year Model year of the vehicle
Make Manufacturer (Chevrolet, Dodge, Ford, GMC)
Type Category of vehicle (Pickup, Sedan, Truck, Van)
Odometer Latest odometer reading of the vehicle
Department Assigned Department responsible for the vehicle
Purchase Price Purchase Price (in US dollars) of the vehicle
Maintenance Cost Annual maintenance cost of the vehicle
3
List from Data Definition
• Each field name in the data definition becomes a column header
– Column headers should be formatted reasonably
– List must have empty cells around the edges
• Except if at top and/or fully to the left
• This allows Excel to automatically compute data range
– Each row is a record of data
Department Purchase Maintenance
Id # Year Make Type Odometer
Assigned Price Cost-Annual
87 1991 FORD SEDAN 37780 Police 20,888 1932.76
112 1983 GMC TRUCK 105330 Housing 21,553 2311.12
151 1998 DODGE VAN 54110 Housing 28,331 2854.54
155 2003 FORD TRUCK 10005 Transportation 31,598 987.33
178 2005 GMC TRUCK 500 Transportation 34,893 854.12
4
Identify the elements of an Excel list
An Excel list is a collection of rows and columns that
contain similar data.
In a worksheet, each column represents a field of data
and each row represents a record of data.
The first row of the list always contains the name of the
fields and is called a field header row.
5
An example of an Excel list
6
Freeze rows and columns
Scrolling through large amounts of data in a
worksheet can move data off the screen.
Portions of the data can be always displayed : column
heading for example. This is called „freezing‟.
Can freeze a header:
Select „View‟ tab and then freeze panes.
Select the „Freeze top row‟
Can also freeze left column
Can split the view into „sub-windows‟
Select „View‟ tab and then „Split‟ option
The window will split into four panels at the active cell
point
7
Sorting
When sorting
Entire records are sorted based on a key (or multiple keys)
Only the range selected is included in the sort
Must always be careful to select all columns that are part of a
record
8
Consider
Sort the vehicle table by year by odometer reading.
What happens in the following case (Select column e)?
List Filtering
A range can be converted to a list
Select range
Choose „Filter‟
Drop-down menus are created to
Sort (ascending / descending)
Show top N items
Filter based on criteria
10
List Filtering
11
Filter data in a list using AutoFilters
Sometimes you will want to see a portion of the
records instead of all of them.
The processing of displaying only those records that
meet some criteria is called Filtering.
When data in the list is filtered, records that do not
meet your criteria are hidden.
These records are not removed from the list and, therefore,
can be redisplayed by removing the filter
Simple filters can be specified by clicking the list
arrow on any field name cell.
More complex filters must be created using the
Custom AutoFilters option.
12
Autofilter
Autofilter: shows
only those records
matching the
selected criteria
Each columns
criteria are ANDED
together
Example: Show all
ford trucks with
over 50000 miles on
the odometer
13
Apply conditional formatting
to a range
There are times when you will want data to have a
different appearance if it meets some criteria.
For example, you might want data to appear in red, if the
data is more than six months old
Or, you might want a value to be black if it is positive and
red if it is negative
This kind of formatting is called conditional
formatting.
You specify the condition under which you want the
formatting to take place and what the formatting
should be.
14
Lookup Tables
A lookup table is related to a list
A lookup table contains data that is accessed by
some key
Use the following table for C-S 001
John Bunyan gets a 73.5% in the course, what grade does
he receive?
What is the key?
Grade Table
What is the data to be accessed? Score Grade
0 F
60 D
70 C
80 B
90 A
15 Image from: http://www.skeletonkey.org/skeleton-key/images/skeleton_key.jpg
Lookup Tables
What does this table mean?
Any score between 0 up to 60 receives an F
Any score between 60 up to 70 receives a D
Any score between 70 up to 80 receives a C
Any score between 80 up to 90 receives a B
Any score above 90 receives an A
Even though the number 73.5 doesn‟t appear in the table, it is in the table implicitly
since each key entry represents a range of values.
Grade Table
Score Grade
0 F
60 D
70 C
80 B
90 A
16
Lookup Tables
What does this table mean?
Grade Table
Any department between MIS up to C-S has REMUS
as a chair? Dept Chair
MIS REMUS
This table doesn‟t have ranges but exact C-S PORTHOS
correspondences BIO J-LO
CHM MARTHA
MTH MADONNA
17
Lookup Example
Complete columns
D: Compute total course
average
E: Figure out the students
grade from the Grade Policy
information
What to enter into D15?
=AVERAGE(B15:C15)
What to enter into E15?
18
VLookup
The VLOOKUP function is used to find data in a table
Uses key data to find other data
My head is
spinning
VLOOKUP(lookup_value, table, col_index, range_lookup)
Lookup_value: The value which is used as the KEY into the table
May be a reference or value
An error if this number is smaller than the smallest table entry
Table: The data table itself. Must be at least two columns wide and sorted!.
Col_index: The column in the table containing the data of interest
Range_lookup: A value indicating how to interpret the table. True for range lookups and
false for exact lookups
If range_lookup is TRUE or omitted, an exact or approximate match is
returned. If an exact match is not found, the largest value that is less than
lookup_value is returned.
19
Example
Use VLOOKUP:
If range_lookup is TRUE or
omitted, an exact or
approximate match is returned. If
an exact match is not found, the
next largest value that is less
than lookup_value is returned.
What to enter into E15?
=VLOOKUP( D15, $B$7:$C$11, 2, TRUE)
=VLOOKUP( D15, $B$7:$C$11, 2)
What‟s up with this?
20
Example
The Grade Policy Table
should be given a name!
What to enter into E15?
=VLOOKUP( D15, GRADE_POLICY, 2)
21
Another Example
An employer wants to maintain information about health care costs
Various group plans available. Each plan
Has a base cost (paid by employee)
Has a percentage cost (percent of salary which is paid by employee)
Has a percentage case which is paid by the employer
Health Plan Cost Table
Base Cost Paid by Percent of Salary Percent of Salary
Code Employee Paid by Employee Paid by Employer
A $250.00 0.5% 0.5%
B $350.00 0.8% 0.2%
QA $375.00 1.0% 1.0%
QB $425.00 1.5% 1.5%
FX $500.00 2.5% 2.0%
22
VLookup Example
=VLOOKUP( D11, $A$4:$D$8, 2, FALSE)+VLOOKUP(D11, $A$4:$D$8, 3, FALSE)*C11
=VLOOKUP( D11, $A$4:$D$8, 3, FALSE)*C11
23
Create 3-D cell references and workbook
references
Think of the collections of worksheets in a workbook
as a third dimension.
You know that you can reference rows and columns
in a worksheet.
You can also reference worksheets.You can,
therefore, have a reference in a worksheet that
pertains to a cell in another worksheet.
The reference Sheet3!A5 is a reference to cell A5 on Sheet3
You can place the reference in any cell on any
worksheet within the workbook.
24
The three dimensions of a workbook
=SUM(Sheet1:Sheet4!B18)
25
Link workbooks to summarize data
You can summarize data from several workbooks by
creating links between them.
To create a workbook reference:
Click the tab for the workbook that will be the destination
workbook
Click in the cell that will receive the data, and enter an equal
sign (=), but do not press the Enter key
Switch to the target workbook, click in the cell containing
the data to be linked, and press the Enter button on the
Formula bar
The formula referencing the source workbook will appear in
the destination cell
26
Link Workbooks by specifying
source and destination files
27
What Links “Look Like”
Formulas with links to other workbooks are displayed in two ways,
depending on whether the source workbook is open or closed.
When the source is open, the link includes:
the workbook name in square brackets, followed by the worksheet name,
an exclamation point (!), and the cells of the formula.
For example, the following formula adds the cells C10:C25 from the
workbook named Budget.xls.
=SUM([Budget.xls]Annual!C10:C25)
When the source is closed, the link includes the entire path.
=SUM('C:\Reports\[Budget.xls]Annual'!C10:C25)
28
Create a workbook template
In business, you often create workbooks that have common
elements such as invoices, expense statements, etc.
Using a template makes this process easier because the
elements are already in place; all you do is fill them in.
You can use any of the templates supplied with Excel or you
can create your own.
When you design your template, you can include formatting
and calculations.
When you open a new workbook with the template, the
formatting and calculations will be built into the workbook.
29
Use pre-built templates
30
An Excel template
31
Store and access templates
To save a template, use the “Save As” option on the
File menu and then change the “File Type” to
template.
When you save a template, it must be saved in the
Templates folder.
This makes it possible for Excel to locate the template when
you are ready to use it again
You usually don't see the Templates folder in Windows
Explorer because it is a hidden folder
Once the template has been saved, it will be listed as an icon
in the Templates dialog box.
32
Worksheet with formatting and formulas
but no data
33
