信息时代的管理信息系统Module_D_Web课件

1、Extended Learning Module D (Office 2007 Version)Decision Analysis with Spreadsheet SoftwareINTRODUCTIONIT plays an important role in aiding decision makingSpreadsheet tools can aid in decision makingFilterConditional formattingPivot tables LISTSList information arranged in columns and rowsEach colum

2、n has one type of informationFirst row contains headings or labelsNo blank rowsBlank columns/rows all around LISTSA listTotal number of customersList Definition TableList definition table description of a list by column (see Figure D.2 on p. D.4)CUST ID Unique ID for customerREGION North, South, etc

3、.RENT VS. OWN customer rents or owns a homeAnd so on BASIC FILTERFilter function filters a list and hides rows that dont match criteriaGood for seeing only certain rows of informationBasic Filter supports only “equal to” criteriaExample: customers in the North REGION Basic Filter StepsOpen workbook

4、(XLMD_Customer.xls from /haag) Click in any cell in the listMenu bar click on Data and then click on FilterWill see list box arrows next to each label or column heading Basic Filter StepsFrom the menu bar, click on Data and then click on FilterBasic Filter StepsEach column will have a pull-down arro

5、w. Click on it and select the criteriaBasic Filter StepsTo select only customers in the North REGION, you must deselect all other regionsBasic Filter StepsExcel will respond by showing only those records that meet the selection criteria (i.e., North REGION)Turning off Basic FilterPerform either of t

6、he followingFrom the menu bar, click on Data and then FilterTurn off selected column filtering by clicking on the appropriate list arrow box and clicking on Clear Filter from “columnname” where columnname is the name of the columnBasic FilterCan also filter on multiple columnsExampleCustomers in Nor

7、th region (select North in REGION)Own a home (select Own in RENT VS. OWN)Only one household member (select 1 in NUM HOUSEHOLD) Basic FilterCustomers in the North REGION who own a home with only 1 household memberCUSTOM FILTERCustom Filter function hides all rows except those that meet criteria, besi

8、des “is equal to”ExampleCustomers with more than 3 household members Custom Filter StepsTurn on FilterClick on pull-down arrow in appropriate columnClick on Number FiltersComplete Custom AutoFilter dialog box with criteriaClick on OK Custom Filter StepsTo use Custom AutoFilter, click on the appropri

9、ate pull-down arrow, select Number Filters and boolean operatorCustom Filter StepsYou will then see a Custom AutoFilter boxCustom Filter StepsEnter the appropriate value and click on OKCustom Filter StepsExcel will respond by presenting only the records that meet the selection criteriaAnother Custom

10、 Filter ExampleCustomers who spent less than $20 or more than $100 Another Custom Filter ExampleComplete both selection criteria appropriatelyAnother Custom Filter ExampleExcel will respond appropriatelyCONDITIONAL FORMATTINGConditional formatting highlights the information in a cell that meets some

11、 criteriaDoes not hide any rowsLets you see the whole listWhile highlighting certain informationExampleCustomers show purchased more than $100 Conditional Formatting StepsSelect entire appropriate columnFrom menu bar, click on Home and then Conditional Formatting within StylesSelect Highlight Cells

12、RulesClick on the appropriate boolean operatorComplete the dialog boxClick on OKConditional Formatting StepsFirst, highlight the appropriate columnConditional Formatting StepsFrom the menu bar, click on Home and then Conditional Formatting within Styles, then select Highlight Cells Rules and click o

13、n the appropriate boolean operatorConditional Formatting StepsIn the Conditional Formatting box, enter the appropriate value and click on OKConditional Formatting StepsExcel will highlight those cells meeting your selection criteriaRemoving Conditional FormattingOption #1 (click anywhere in the list

14、)Click on Conditional FormattingSelect Clear RulesClick on Clear Rules from Entire SheetOption #2 (select the entire column)Click on Conditional FormattingSelect Clear RulesClick on Clear Rules from Selected CellsPIVOT TABLESPivot table enables you to group and summarize informationShows summaries o

15、f information by dimensionCan be two-dimensionalCan be three-dimensionalSimilar to data warehouse concept from Chapter 3 Pivot Table ExampleThis is a pivot table2D Pivot Table StepsClick anywhere in listFrom menu bar, click on Insert and then PivotTableClick on OKDrag/drop labels in row and column f

16、ieldsProvide appropriate formatting 2D Pivot TableCount of customersBy REGIONBy RENT VS. OWN 2D Pivot Table StepsTo create a 2D pivot table, from the menu bar click on Insert and then PivotTable2D Pivot Table StepsClick on OK2D Pivot Table StepsPivot tablePivotTable Field ListSummary 2D Pivot Table

17、StepsNow, drag and drop appropriate column headings (labels) from the Pivot Table Field List Box to the appropriate places in the pivot tableExample: Number of customers byREGIONRENT VS. OWN 2D Pivot Table StepsDrag and drop REGION hereDrag and drop RENT VS. OWN hereDrag and drop CUST ID here2D Pivo

18、t Table StepsThe default aggregation for a pivot table is summing. To change that, click on the Field Settings button in the menu bar2D Pivot Table StepsYou will then see the Value Field Settings dialog box2D Pivot Table StepsTo change it, select another aggregation in the Summarize value field by box and click on OK2D Pivot Table StepsExcel will change the aggregation in the pivot tabl