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 column
2、 has one type of informationFirst row contains headings or labelsNo blank rowsBlank columns/rows all around LISTSList 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.RENT VS.OWN customer rents or owns
3、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 Steps1.Open workbook(XLMD_Customer.xls from in any cell in
4、the list3.Menu bar click on Data and then click on FilterWill see list box arrows next to each label or column heading Basic Filter StepsBasic Filter StepsBasic Filter StepsBasic Filter StepsTurning off Basic FilterPerform either of the followingFrom the menu bar,click on Data and then FilterTurn of
5、f 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 North region(select North in REGION)Own a home(select Own in RENT VS.OWN
6、)Only one household member(select 1 in NUM HOUSEHOLD)Basic FilterCUSTOM FILTERCustom Filter function hides all rows except those that meet criteria,besides“is equal to”ExampleCustomers with more than 3 household members Custom Filter Steps1.Turn on Filter2.Click on pull-down arrow in appropriate col
7、umn3.Click on Number Filters4.Complete Custom AutoFilter dialog box with criteria5.Click on OK Custom Filter StepsCustom Filter StepsCustom Filter StepsCustom Filter StepsAnother Custom Filter ExampleCustomers who spent less than$20 or more than$100 Another Custom Filter ExampleAnother Custom Filter
8、 ExampleCONDITIONAL FORMATTINGConditional formatting highlights the information in a cell that meets some criteriaDoes not hide any rowsLets you see the whole listWhile highlighting certain informationExampleCustomers show purchased more than$100 Conditional Formatting Steps1.Select entire appropria
9、te column2.From menu bar,click on Home and then Conditional Formatting within Styles3.Select Highlight Cells Rules4.Click on the appropriate boolean operator5.Complete the dialog box6.Click on OKConditional Formatting StepsConditional Formatting StepsConditional Formatting StepsConditional Formattin
10、g StepsRemoving Conditional FormattingOption#1(click anywhere in the list)1.Click on Conditional Formatting2.Select Clear Rules3.Click on Clear Rules from Entire SheetOption#2(select the entire column)1.Click on Conditional Formatting2.Select Clear Rules3.Click on Clear Rules from Selected CellsPIVO
11、T TABLESPivot table enables you to group and summarize informationShows summaries of information by dimensionCan be two-dimensionalCan be three-dimensionalSimilar to data warehouse concept from Chapter 3 Pivot Table Example2D Pivot Table Steps1.Click anywhere in list2.From menu bar,click on Insert a
12、nd then PivotTable3.Click on OK4.Drag/drop labels in row and column fields5.Provide appropriate formatting 2D Pivot TableCount of customersBy REGIONBy RENT VS.OWN 2D Pivot Table Steps2D Pivot Table Steps2D Pivot Table Steps2D Pivot Table StepsNow,drag and drop appropriate column headings(labels)from
13、 the Pivot Table Field List Box to the appropriate places in the pivot tableExample:Number of customers byREGIONRENT VS.OWN 2D Pivot Table Steps2D Pivot Table Steps2D Pivot Table Steps2D Pivot Table Steps2D Pivot Table StepsPivot TablesCan have multiple pieces of information in the body of the pivot
14、 tableExampleCount of customers(already present)Total of purchases(new information)Drag/drop TOTAL PURCHASES into pivot table Pivot Tables3D Pivot TablesDesired dimensionsREGIONRENT VS.OWNNUM HOUSEHOLDDrag/drop NUM HOUSEHOLD into“Drop Page Fields Here”location 3D Pivot Tables3D Pivot TablesBACK TO DECISION SUPPORTIts all about decision supportFilter view partial list of information based on criteriaConditional formatting see all information with some highlightedPivot table summarize information by dimension
