1 / 43

Extended Learning Module D Decision Analysis With Spreadsheet Software

Extended Learning Module D Decision Analysis With Spreadsheet Software. Overview of Module D. Lists Basic AutoFilter Custom AutoFilter Conditional Formatting Pivot Tables Back to Decision Support. Introduction.

frieda
Download Presentation

Extended Learning Module D Decision Analysis With Spreadsheet Software

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Extended Learning Module D Decision Analysis With Spreadsheet Software

  2. Overview of Module D • Lists • Basic AutoFilter • Custom AutoFilter • Conditional Formatting • Pivot Tables • Back to Decision Support

  3. Introduction • Connect to the Web site that supports this text (www.mhhe.com/haag and select XLM/D). • Download the file called XLMD_Customer.xls.

  4. Lists • List - a collection of information arranged in columns and rows in which each column displays one particular type of information.

  5. Lists • Each column has only one type of information. • The first row in the list contains the labels or column headings. • The list does not contain any blank rows. • The list is bordered on all four sides by blank rows and blank columns.

  6. Lists • List definition table - a description of a list by column. • The columns of information in Figure D.2 include: • CUST ID – a unique ID for each customer • REGION – the region in which the customer lives • RENT VS. OWN – whether the customer rents or owns a home • NUM HOUSEHOLD – number of family members • ANNUAL INCOME – total combined annual income • TOTAL PURCHASES – dollar total of all purchases made by the customer within the last six months • NUM PURCHASES – count of all purchase made by the customer within the last six months.

  7. Lists Column headings Each column has only one type of information.

  8. Basic AutoFilter • AutoFilter function - filters a list and allows you to hide all the rows in a list except those that match criteria you specify. • To use AutoFilter: • Click in any cell in the list. • Click on Data, point at Filter, and click on AutoFilter. • Click on a list arrow box and choose filtering.

  9. Basic AutoFilter Shows only customers in the North REGION.

  10. Basic AutoFilter • You can filter on multiple columns. Customers in the North REGION who own a home with only 1 household member.

  11. Custom AutoFilter • Custom AutoFilter function - allows you to hide all the rows in a list except those that match criteria, besides “is equal to,” you specify. • To perform Custom AutoFilter, click on (Custom…) in list arrow box. • To see all customers who have at least 4 people in their household, perform the steps on the following slides.

  12. Custom AutoFilter • Select (Custom…) from list arrow box for NUM HOUSEHOLD.

  13. Custom AutoFilter • You will then see a Custom AutoFilter box.

  14. Custom AutoFilter • In top-left entry box, click on the pull-down arrow and click on is greater than.

  15. Custom AutoFilter • In top-right entry box, click on the pull down arrow and click on 3.

  16. Custom AutoFilter Only customers with more than four people in their households.

  17. Custom AutoFilter • You can also use “and” and “or” with Custom AutoFilter. • What customers have spent less than $20 or more than $100 in the past six months?

  18. Custom AutoFilter

  19. Custom AutoFilter Only customers who have spent less than $20 or more than $100

  20. Conditional Formatting • Conditional formatting - highlights the information in a cell that meets some criteria you specify. • To conditionally format in red all TOTAL PURCHASES greater than $100, perform the steps on the following slides.

  21. Conditional Formatting • Select entire TOTAL PURCHASES column.

  22. Conditional Formatting • Click on Format and then Conditional Formatting.

  23. Conditional Formatting • Pull down arrow for the field second from the left and click on the greater than. • In the field on the right, enter 100. • Click on Format.

  24. Conditional Formatting • Click on Patterns tab. • Choose the color red. • Click on Ok (twice).

  25. Conditional Formatting Only customers whose number of employees exceeds 100 are highlighted in red.

  26. Pivot Tables • Pivot table - enables you to group and summarize information.

  27. Pivot Tables • To create a pivot table, perform the following steps: • Click on Data and then PivotTable and PivotChart Report. • In Step 1 of 3, click on Next. • In Step 2 of 3, click on Next. • In Step 3 of 3, click on Finish.

  28. Pivot Tables • First, from the menu bar, click on Data and then PivotTable and PivotChart Report.

  29. Pivot Tables • Second, click on Next in the Step 1 of 3 box. • Assumes pivot table will come from a list.

  30. Pivot Tables • Third, click on Next in the Step 2 of 3 box. • This defaults to the entire list.

  31. Pivot Tables • Fourth, click on Finish in the Step 3 of 3 box. • This places the pivot table in a new worksheet.

  32. Pivot Tables • You will then see a skeletal structure of a pivot table.

  33. Pivot Tables • We want to build a pivot table just the like the one in Figure D.1 on page 225. • It shows number of customers by REGION and RENT VS OWN.

  34. Pivot Tables • From Pivot Table Field List box: • Drag REGION and drop it into “Drop Row Fields Here.” • Drag RENT VS OWN and drop it into “Drop Column Fields Here.” • Drag CUST ID and drop it into “Drop Data Items Here.” • Result is shown on next slide.

  35. Pivot Tables Drag and drop RENT VS. OWN here. Drag and drop REGION here. Drag and drop CUST ID here.

  36. Pivot Tables • The default aggregation is by summing, not what we want. • To change to counting of CUST ID, perform the steps on the following slides.

  37. Pivot Tables • Click on the Field Settings button in the Pivot Table toolbar.

  38. Pivot Tables • Click on Count in Summarize by: • Click on OK.

  39. Pivot Tables Now, the pivot table counts the number of customers.

  40. Pivot Tables • You can add as many pieces of information to a pivot table as you want. • We added TOTAL PURCHASES in Figure D.13. (p. 238)

  41. Pivot Tables Drag and drop TOTAL PURCHASES here to obtain a summary of another dimension of information.

  42. BACK TO DECISION SUPPORT • AutoFilter - helps you quickly create a view of a partial list of information. • Conditional Formatting - maintains the view of the entire list of information but highlights key pieces of information that you may be looking for. • Pivot table - helps you quickly aggregate or summarize information by dimension.

  43. Summary Student Learning Outcomes • Define a list and list definition table within the context of spreadsheet software and describe the importance of each. • Compare and contrast the AutoFilter function and Custom AutoFilter function in spreadsheet software. • Describe the purpose of using conditional formatting. • Define a pivot table and describe how you can use it to view summarized information by dimension.

More Related