Transcript CA202 Spreadsheet Application Working with Database Data

CA202
Spreadsheet Application
Working with
Database Data
Lecture # 14
1
Objectives
In this chapter you will learn to:
✔ Look up information in a data list.
✔ Retrieve data from a database.
✔ Summarize data using database
functions.
2
Lists
• Access and Excel both work with lists of data.
• In Excel, that data is often stored in complex lists
such as PivotTables, but the data used to create
PivotTables is a series of rows in a worksheet.
• Excel does have a searching (lookup) capability
you might expect to find only in a database
program.
• can If you have data in a database that you’d
like to bring into Excel, you create a query,
Similarly, you can use the built in functions into
Excel to query the data list and summarize the
data it contains.
3
Looking Up Information in a Data
List
• Whenever you create a worksheet that holds
information about a list of distinct items, such as
products offered for sale by a company you
should ensure that at least one column in the list
contains a unique value that distinguishes that
row.
• It could be primary key column, for product table
it could be product ID.
• You can use the VLOOKUP function to let your
colleagues type a Product ID in a cell and have
the corresponding product information appear in
another cell.
4
Looking Up Info in a Data List
=VLOOKUP(lookup_value, table_array,
col_index_num, range_lookup)
5
Looking Up Info in a Data List
• The VLOOKUP function works a bit differently depending
on whether the range_lookup argument is set to TRUE or
FALSE. The following list summarizes how the function
works based on the value of range_lookup.
– If the range_lookup argument is left blank or set to TRUE and
VLOOKUP doesn’t find an exact match for lookup_value, the
function returns the largest value that is less than lookup_value.
– If the range_lookup argument is left blank or set to TRUE and
lookup_value is smaller than the smallest value in the named
range, an #N/A error is returned.
– If the range_lookup argument is left blank or set to TRUE and
lookup_value is larger than all values in the named range, the
largest value in the named range is returned.
– If the range_lookup argument is set to FALSE and VLOOKUP
doesn’t find an exact match for lookup_value, the function returns
an #N/A error.
ExerciseLookup
6
Retrieving Data from a Database
• You can also save data lists, or tables,
that you’ve created in other database and
spreadsheet programs and then import
those tables into an Excel worksheet.
• In Excel, you can reach directly into an
Access, dBASE, Microsoft FoxPro, or
Microsoft Visual FoxPro database and
retrieve data from that database’s tables.
ExerciseQuery
7
Summarizing List Data
• A final, useful aspect of the relationship between
spreadsheet programs and databases is that
you can create formulas that summarize the
values in ranges of cells.
• it is possible to use many of the data summary
functions found in databases into spreadsheet
programs, and vice versa, such as SUM,
AVERAGE, COUNT, MAX, and MIN
• Similar functions are available for use with Excel
data lists: DSUM, DAVERAGE, DCOUNT,
DMAX, and DMIN.
8
Database and List
Management Function
Function
DCOUNT
DMAX
DSUM
Explanation
The number of records matching your
criteria
The maximum value of those records
matching your criteria
The sum of the values in a field for all
records matching your criteria
=DSUM(values, "field", criteria)
9
DSUM example
ExerciseDataList
10
Chapter 14 Key Points
•
•
•
•
•
•
Use the VLOOKUP function to look up a value in one
column of a data list and return a value from another column
of the same row.
You can create database queries to pull selected records
from database tables into your Excel worksheets.
Excel knows about many types of data sources, so there
are few limits on what data you can bring into your
worksheets.
If you want to limit the data your database queries bring in,
you can define filters to act on a query’s results.
By using database functions such as DSUM and
DAVERAGE, you can summarize the data in an Excel data
list.
Just as with database queries, you can set criteria to limit
the list rows considered by database functions.
11