信息时代的管理信息系统Module-D-Book课件.ppt

1、Extended Learning Module D(Office 2010 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 pp.388-389)CUST ID Unique ID for customerREGION North,South,etc.RENT VS.OWN customer rents or

3、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 Steps1.Open workbook(XLMD_Customer.xls from in any cel

4、l in 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 FilterTu

5、rn 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 North region(select North in REGION)Own a home(select Own in RENT V

6、S.OWN)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 appropriat

7、e column3.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 F

8、ilter 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 appr

9、opriate 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 Form

10、atting 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 Cell

11、sPIVOT 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 Ins

12、ert and 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

13、)from the Pivot Table Field List Box to the appropriate places in the pivot table summary areaExample: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

14、 body of the pivot tableExampleCount of customers(already present)Total of purchases(new information)Drag/drop TOTAL PURCHASES into Values in lower right portion of screenPivot Tables3D Pivot TablesDesired dimensionsREGIONRENT VS.OWNNUM HOUSEHOLDDrag/drop NUM HOUSEHOLD into lower right portion of th

15、e screen called“Report Filter”3D Pivot Tables3D Pivot TablesGOAL SEEKGoal Seek works backward from objective to compute an unknown valueScenario(movie posters from Chapter 1):Buy poster for$4 and sell for$9$2 shipping for each posterFixed costs of$1,500 per yearWhat is the breakeven point?GOAL SEEK

16、BREAKEVEN POINTDownload XLMD_BreakEven.xlsC14=C13*C7C15=C13*C8C16=C13*C9C18=C14 C15 C16 C17Now,for a given net profit,we want Excel to compute Units Sold(C13)GOAL SEEK BREAKEVEN POINT1.From menu bar,click on Data and What-If Analysis2.Select Goal Seek3.For Set cell,enter C184.For To value,enter 300005.For By changing cell,enter C136.Click on OKGOAL SEEK BREAKEVEN POINTGOAL SEEK BREAKEVEN POINT