通曉信息技術(shù)技能、概念與能力教參chapter

1、Learning ObjectivesState the two basic design criteria for creating effective spreadsheetsExplain how conditional formatting of spreadsheet entries applies an interpretation to spreadsheet informationExplain conditional formulas, and their components and behaviorPerform “what if” analysis with a spr

2、eadsheetUse AutoFiltering and advanced filtering to customize spreadsheet listsExplain the importance of symbolic naming of spreadsheet cellsDesigning a SpreadsheetWhen we make a spreadsheet to find an answer and then delete it, all that matters is that the computation was rightWhen a spreadsheet is

3、 used repeatedly, it es a tool of planning, analysis, and decision-makingTo be effective, a spreadsheet must be well designed, informative, and flexibleDesign GuidelinesTwo basic principles for setting up effective spreadsheets:Focus on Form: Make the form logical, the layout clean, and the entries

4、clear and easy to useExplain Everything: It should be possible to know immediately what every cell meansFocus on FormA spreadsheet is used in solving problems It must be easy to understand and easy to work withArrange the data logically:Descriptive information should be on the top and left sidesSumm

5、ary information should be on the bottom and right sidesFocus on FormFonts should be clearColors should be used in moderationBe an attraction, not a distractionUse a separate sheet for each table Individual sheets make working with multiple tables more manageableHiding information that isnt needed is

6、 a good way to make a spreadsheet clear and easy to useExplain EverythingInclude meaningful column headings and identifying information about the rowsCells and ranges are assigned symbolic names so the content es meaningfulFor summary information cells, choose modifiers like total and largestFor com

7、putations, include comments to explain assumptions made when creating the formulasThe Trip Sample SpreadsheetScenario:Two friends wonder if its possible to drive to the Arctic Circle from Chicago.The trip to Inuvik is 3,663 miles from home and will take three days and eight hours of driving time.The

8、 given driving time is continuous, which they do not plan to do.Use a spreadsheet to figure out how long it will take and how much it will cost.Applying the RulesThe trip is a five-day trek from Chicago to Dawson, Yukon TerritoryFrom Dawson, they will drive to the Arctic Circle and back to Dawson th

9、at nightApplying the RulesFriends will be included to offset costsThe friends are interested in how much the trip will costPart of the trip is through the United States where gas is priced by the gallon in US $Part of the trip is through Canada where petrol is priced by the liter in Canadian $Initia

10、l SpreadsheetApplying the RulesThe spreadsheet has a title listing the authors and stating the completion dateColumns are assigned clear headingsThe heading row is filled with a soft color that separates it from the contentA clean, sans serif font presents the data justified in the cellsApplying the

11、 RulesApplying the RulesComments or “Sticky notes”:Cells with comments have a red triangle in the cells upper right cornerHovering the cursor over the cell displays the commentTo insert a comment in Excel, select the cell and then navigate Insert New CommentTo edit it, select the cell and navigate I

12、nsert Edit CommentTo remove a comment, navigate Edit Clear CommentsConditional FormattingCell Value Is SpecificationsThis window lets users specify one or more conditionsIf the program finds that these conditions apply to the cell, it formats the entry in the manner specifiedUsers specify the condit

13、ion by picking one of a set of relationships and filling in the limitsConditional FormattingFormula Is SpecificationsIts possible to format items based on a formulaUsing Formula Isallows for acomparison with a cell other than the one being formattedDistinguish Between NamesThe complication: determin

14、ing when a price is in Canadian dollarsFuel Price Report column lists the source of the price quotewhenever the country is CA the price should be italicized=IF(LEFT(D2,2)=“CA”, TRUE, FALSE)Conditional Formulasconditional formulas: make the entire computation of a cell contingent on the e of a condit

15、ionFiguring the Amount PaidTwo choicesexpress fuel price as a gallon price instead of a liter priceexpress mileage as miles per liter (mpl)Either way, the US and Canada cases have to handled slightly differently=IF(LEFT(D2,2)=“US”, E2*C2/22, E2*C2/5.8)Figuring the Amount PaidNote: Intermediate compu

16、tation columns hiddenCost in One CurrencyEssential to know the expenditures in one currencyA Canadian dollar is worth $0.948 in U.S dollars=IF(LEFT(D2,2)=“CA”, F2*0.948. F2)Cost in One Currency Defining NamesIt can be helpful when designing a spreadsheet to give names to components of the sheetsA na

17、me is a word or phrase assigned to a cell or range of cellsOnce the name has been assigned, it can be used wherever cell references would be usedDefining NamesUsing names reduces the chance of messing up range specificationsErrors are minimized when columns and rows are added/deletedChoose Insert Na

18、me Define . . . and enter a nameThe software assigns the name to that range Defining NamesNow, choose Insert Name Apply to allow the use of the name“What If” AnalysisYou want to consider the effects of certain changesYou can do this directly by changing values, then changing them backPerhaps save th

19、e sheet, make changes, and exit without savingBut spreadsheets have better support than that for what-if analysis“What If” AnalysisScenariosA speculative or “what if ” analysis is supported in spreadsheet software by a tool called ScenariosA scenario is a named alternative to a spreadsheet based on

20、different inputsA scenario is an aid to understanding changes in plans“What If” AnalysisThe Add Scenario window is the place to name a scenarioThe software fills in the cell(s) that will changeClicking OK takes us to the Scenario Values windowClicking OK takes us back to the Scenario Manager window

21、where the newly added scenario can be seen in the list“What If” AnalysisAt the bottom of the Scenario Manager window, there is a Summary. . . ButtonWhen clicked, a dialog box appears asking what cell we consider the “bottom line” of the computationOr, if the scenario came to pass, what value are we

22、most interested inClick OK again, and you are presented with the Scenario SummaryReusing ScenariosHaving set up the scenarios earlier, it is possible to rerun them to see how the end result changesOnce run, navigate to the Scenario Manager and click SummaryScenariosYour textbook discusses the follow

23、ing scenarios. The spreadsheet can compute the changed cost of each choiceHaving the car tuned up to improve gas mileageTaking along extra passengers to share the expensesLodging v. campingTrip Log TableAnalyzing Data Using FilteringUse of the Filtering tool allows access to subsets of information h

24、eld in a spreadsheetFiltering selects only certain rows from a listIt applies only to spreadsheet tables that have column headingsFiltering lets users create a customized version of a spreadsheet list that is limited to the rows meeting some criterionAnalyzing Data Using FilteringAutoFilterSelect an

25、y cell in the listChoose Data Filter AutoFilterThe result is a redrawn spreadsheet list with triangle menu buttons by each column headingThe menu buttons give you options for filtering the list based on data in that columnAnalyzing Data Using FilteringAutoFilterClicking a button opens the menu and presents the options:include sorting rowsdisplaying rows containing a limited number of valuesdisplaying only those rows matching a specific value in the columnAdvanced Filtering TechniqueAdvanced FilteringThe filtering dialog for a column allows