Excel Chapter 8 Summary PDF

Preview

Summary

SIMNET Excel...

Description

Excel - Chapter 8 - Exploring Data Analysis and Business Intelligence

ChapterSummary 8.1Createandmanagescenariosforworksheetdata(p.E8496). Ascenarioisasavedsetofvaluesinaworkbook. Ascenarioisawhatifanalysistoolbecauseitallowsyoutodisplayandcompare multipledatapossibilitiesinaworksheet. TheScenarioManagercommandisavailablefromtheWhatifAnalysisbuttonin theForecastgroupintheDatatab. IntheAddScenariodialogbox,younameeachscenarioandselectcellstobe changed. IntheScenarioValuesdialogbox,youacceptortypenewvaluesforeachscenario. Onlyonescenariocanbedisplayedintheworksheetatatime. FromtheScenarioManagerdialogbox,youcancreateascenariosummaryreport thatlistsdetailsaboutchangingcellsandresultcellsforallscenariosinaworkbook. 8.2UseGoalSeektobacksolveacellvalueforaformula(p.E8498). Backsolvingisaproblemsolvingmethodthatstartswiththeresultandcalculates thevaluetoreachthatresult. TheGoalSeekcommandtestsvaluesforonecellinaformula. GoalSeekdetermineswhatvalueshouldbeinthecellsothattheformularesultsin aspecifiedvalue. TheGoalSeekcommandisanoptionontheWhatifAnalysisbuttonintheForecast groupintheDatatab. 8.3UseSolvertofindasolutionforaformula(p.E8500). TheSolveraddinisananalysistoolthatsolvesaprobleminreverse. Solverdeterminesthehighest,thelowest,oraspecificresultforaformulaby adjustingvaluesincellsusedintheformula. ASolverproblemhasthreecomponentsknownasparameters,identifiedinthe SolverParametersdialogbox. Theobjectivecellisacellwithaformulathatwillbesolvedforspecificresults.Itis alsocalledthetargetcell. Variablecells,alsoknownasdecisionorchangingcells,arecellsthatSolvercan adjusttoreachtheobjective. Constraintsarerestrictionsorlimitationsonvariablecells,theformula,orother worksheetcellsthatarerelatedtotheobjectivecell. TheSolverResultsdialogboxincludesoptionstokeepthesolution,toreturntothe originalvalues,andtosavetheresultsasascenario. TheSolverResultsdialogboxhasaReportssectionwithanalysisreportsthatcanbe generatedforeachsolution. SolverisactivatedfromtheAddInsdialogboxintheExcelOptionsdialogbox. TheSolverbuttondisplaysintheAnalyzegroupontheDatatab.

8.4Builddatatableswithoneandtwovariables(p.E8508). Adatatableisarangeofcellsinaworksheetthatshowsmultipleresultsforoneor moreformulas. AdatatableiscreatedfromtheWhatIfAnalysisbuttonintheForecastgrouponthe Datatab. Aonevariabledatatablesubstitutesvaluesforoneargumentinoneormore formulas. Atwovariabledatatablesubstitutesvaluesfortwoargumentsinasingle formula. Thevaluesthataresubstitutedareknownasinputvalues. Inputvaluescanbeinaroworacolumn,andtheformulamustbeenteredina specificlocationbasedonwhetherthetableusesoneortwovariables. Inthedatatablerange,theformulacanbetypedorenteredasareferencetothe formulaintheworksheet. IntheDataTabledialogbox,youspecifyaroworcolumninputcellforaone variabletableandbothforatwovariabletable. Theroworcolumninputisthecelladdressintheformulathatisreplacedwithinput values. ADataTablecommandinsertstheTABLEfunctioninanarrayformulaineachresult cell. 8.5Createaforecastsheetfortimebaseddata(p.E8511). Aforecastsheetisageneratedworksheetthatusesexistingdatatoanalyzeand predictresults. Aforecastsheetincludesatableandarelatedchart. Twodataseriesarerequiredtobuildaforecastsheet;oneseriesmustbeadateor timefield. Thedateortimefieldmustusearecognizabletimeinterval. Theforecastchartcanbealineoracolumnchart. TheForecastSheetbuttonisintheForecastgroupontheDatatab. 8.6Getandtransformdatainaquery(p.E8515). Aquerystoresinstructionsforgettingdatafromanexternalsourcefordisplayina worksheet. Aquerycangetdatafromonlineandinhousesourcesincludingdatabaseortext files,XMLdata,andothers. Aqueryestablishesadataconnectiontothesourcesothatthedatacanbe refreshed. DataiscleanedorpreparedintheQueryEditorbeforeitisloadedintoExcel. QueryresultsaredisplayedinanExceltable. Queriesarenamedandsavedwiththeworkbook. Whenacommonfieldexistsintwoormorequeries,thosequeriescanbeusedto createaPivotTable. TheNewQuerybuttonisintheGet&TransformgroupontheDatatab. 8.7ExplorePivotTabletools(p.E8522). AslicerisavisualfilterforaPivotTable,asmallwindowthatfloatsonthesheet. YoucaninsertaslicerforanyfieldinthedatasourceforthePivotTable. AtimelineisavisualfilterforadatefieldinaPivotTable.

TheInsertSlicerandInsertTimelinebuttonsareintheFiltergrouponthe PivotTableToolsAnalyzetab. Slicersandtimelinesareobjectsthatcanbepositioned,sized,andformatted. TheValueFieldSettingsforafieldinaPivotTabledeterminehowvaluesare summarized. Youcanchangethecalculationusedforvaluefields,showcustomcalculations,or insertacalculatedfield. Acustomcalculationisabuiltinpercentage,ranking,orratio. AcalculatedfieldisafieldthatisnotinthedatasourceforaPivotTableandisbuilt fromaformulawithoneofthefieldsinthesourcedata. InsertacalculatedfieldfromtheFields,Items,&Setsbutton[PivotTableTools Analyzetab,Calculationsgroup]. PivotTablefieldscanbeformattedfromtheValueFieldSettingsdialogboxorfrom theNumbergroupontheHometab. LayoutoptionsforaPivotTableincludehowandwheretotalsdisplayandthereport format. TheGETPIVOTDATAfunctiondisplaysresultsfromaparticularcellinaPivotTable. ForexpandedPivotTablefunctionsandcommands,thePowerPivotaddincanaccess millionsofrecordsforanalysisinareport. 8.8UsetheAnalysisToolPaktocalculatestatisticalmeasures(p.E8529). TheAnalysisToolPakisanExceladdinwithbuiltinstatisticalandengineering calculations. Aftertheaddinisinstalled,theDataAnalysisbuttonappearsintheAnalyzegroup ontheDatatab. TheDescriptiveStatisticscommandcalculatesandgeneratesalistofpopular measuresforadatarange. TheMovingAveragecommandcalculatesandgeneratesalistofrunningorrolling averagesforasetofvalueswitharelatedchart. OtherAnalysisToolPakcommandsincludecovariance,regressionanalysis,and randomnumbergeneration.

CheckforUnderstanding The SIMbook for this text (within your SIMnet account) provides the following resources for conceptreview: Multiplechoicequestions Matchingexercises Shortanswerquestions...