CA202 Spreadsheet Application

1. CA202Spreadsheet Application Working with Database Data Lecture # 14

2. 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.

3. 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.

4. 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.

5. Looking Up Info in a Data List =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

6. 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

7. 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

8. 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.

9. Database and List Management Function

10. DSUM example ExerciseDataList

11. 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.