PRR 475 Labs – Week 9 - Database Features in Excel- Database procedures in Excel Sorting data - sort cases in alphabetic or numeric order, increasing or decreasing Filters - to find cases satisfying particular criteria, this is called a "query" in database lingo Forms - to enter, edit or view data one case at a time Subtotals - to compute subtotals for groups of cases - regions, market segments Validation - checking entries as you make them Pivot Tables - frequency counts, cross-tabulations or comparing means across subgroupsIntroduction: Excel includes several database procedures, although not as many as a full-fledged relational database like Access. Excel's databases are limited to 65,000 cases and the procedures only work on a single table. Relational databases like Access may contain many interrelated tables. Think of a database as a series of cases (or records) - the rows. For each record a number of distinct variables (fields) have been measured - the columns. Excel's database procedures require that the data be set up in a contiguous block on the spreadsheet - there should be no blank rows or columns in the middle of the data. If there is other information on the spreadsheet, include blank rows or columns to separate the database from it. To execute a database procedure in Excel (1) first select a single cell inside the database - this identifies the database you want to analyze, (2) then choose one of the database procedures from the menu. WARNING: Always save spreadsheet before sorting or executing database procedures. It is easy to mess up a spreadsheet while sorting if you are not careful. Beware of blank rows or columns inside the database or of selecting more than a single cell to identify the database (e.g. sliding mouse and selecting two cells). We will demonstrate the database procedures using a couple of small databases in the Data475.xls file. There are three databases here on separate pages. Each data page has a corresponding "Information page" that precedes it. This "Inf" page defines fields in the database. Marina page has data for 124 marinasBoaters page has records for a small set of registered watercraftCounty page has information for Michigan's 83 countiesWe will first demonstrate using the Marina database and then have you do some exercises on the other two. The marina database is located in the range A1:J125 on the Marina page. The first row are the names for the fields. This database contains 124 records (marinas) and ten fields. The Marina-Inf page defines the fields and indicates if it is an alphabetic (A) or numeric(N) field. The data type for each field determines what operations can be carried out - mathematical operations like averaging or summing only apply to numeric fields. Sorting Data: to change the order of the rows in a spreadsheet without changing the contents of the cells. Data are currently sorted byregion. Let's sort the data by years in business from highest to lowest.S1. For simple single field sorts, use the speedsort buttons on the toolbar - click a single cell in the column you want to sort (in this case column E, so choose cell E1) and then select either the ascending (A to Z) or descending (Z to A) speedsort tool.S2. For more complex sorts, first select any cell in your database, then choose Data, Sort from menu. You can then sort on up to three fields simultaneously. Complete the Dialog box to specify your sort. Click OK to execute it. You can sort on up to three fields at a time. e.g. sort first by region, then size class, then number of seasonal slips. Nesting on sorts makes the first field change most slowly. Think of sorting as alphabetizing with fields substituting for letters. The first field is first letter, second is second letter, third is third letter.Filters: to find records with specified characteristics. (Called a query in database lingo). Let's find all marinas in Macomb county. 1. Select a single cell inside the database2. On menu bar select Data, then Filter, and then AutoFilter from the pull-down menu. Pull-down arrows should now be visible for each column in the first row of the database. 3. To find all marinas in Macomb County, click the filter arrow at top of the COUNTY column and then select MACOMB. All of the marinas (rows) that are not in Macomb County will be hidden from view.4. To turn off the filter and display all of the marinas again, click the arrow next to COUNTY and then select (All).5. A two variable filter - Let's find all the marinas in Macomb county with at least 1 Condominium slip. Filter the County field for Macomb, then select the filter at top of Condo Slips Field. Choose Custom and fill in boxes - in left hand box, select drop down arrow and choose "is greater than", then enter 0 in the box at the right. Click OKPage 1 of 46. Remember filtering is cumulative here so you can filter on as many fields as you like. To turn off filtering in a particular column, choose the "all" option at top of list. To remove all filtering, go back to menus, choose Data, Filter and uncheck the autofilter command. You should turn filtering off when you are done with database procedures.Forms: are a handy way to enter or edit data or perform simple queries.7. Choose a single cell in the database, Select Data, then Forms from the menu. The first record will be displayed on the form. Use scroll bar to scroll through records. Buttons at right can be used to add cases (NEW), delete them, or to find cases satisfying particular criteria. Choose Criteria button and enter Macomb in County field. Then use Find Next button to find marinas in Macomb county, one by one. 8. To add a new marina to the database, select the NEW button - this simply scrolls to the end of records. Fill in the form with a new record, say a 1000 seasonal slip marina in Monroe County - ID = 125 Southeast LP MONROE, etc. in the respective fields, and then press Close. Notice this record is added in row 126 on the spreadsheet. Delete it before proceeding. Subtotals: are useful when you want to aggregate information for subgroups of records. One of the fields in the database defines the subgroups and then you can summarize any of the other fields for these subgroups. Let's use this procedure to count the number of slips in each region. Region field forms subgroups. 9. First, the data must be sorted on the field that will define the subgroups. Select a cell in the Region column and click the speedsort tool. 10. To add