type微软数据中心沉海

Day10:MICROSOFTEXCEL–CHAPTER8MICROSOFTEXCEL–CHAPTER9MICROSOFTEXCEL–CHAPTER10AkhilaKondaiakhila.
kondai@mail.
wvu.
eduSeptember23,2013AnnouncementsHomework#3isdueon09/27/2013by11.
59pmWhat–ifanalysisDataTableGoalSeekScenarioManagerWHAT-IFANALYSISWhatifanalysisistheprocessofchangingvariablestoobservehowchangeseffectscalculatedresults.
Avariableisaninputvaluethatcanchangetoothervaluestoaffecttheresultsofasituation.
DatatableOnevariableDataTableTwovariableDataTableCreatingaonevariabledatatableAdataanalysistoolthatprovidesvariousresultsbasedonchangingonevariable.
3stepsEnterSubstitutionvaluesforaone-variabledatatableEnterformulasandcompletethedatatableFormattheone-variabledatatableSubstitutionvalues…ClickcellD4,enter4%Homeribbon->Editinggroup->Fill->SeriesSelectcolumnradiobutton,andgiveStepvalueboxvalueas0.
25%andStopvalueboxvalueas6%FormattherangeD4:D12topercentageswith2decimalpoints.
Completingthedatatable…E3->B12:F3->B13:G3->B14SelectD3:G12UnderDATATABgotoWhat-IfAnalysis->DatatableGivecolumninputcellas$B$4andclickOKFormatthetable…SelectrangeE4:G12andgivethenumberformatasAccountingwith2decimalpointsEnterAPRincellD3SelectcellE3FormatcellsSelectCustomGeneraltype"Payment"andclickOK.
F3asTotalRepaidandG3asTotalInterest.
CreatingatwovariabledatatableAdataanalysistoolthatprovidesvariousresultsbasedonchangingtwovariables.
2stepsSetupthestructureforaTwo-VariabledatatableCompletetheTwo-VariableDataTableDatatablestructure…Different"costofcar"inrowJ3:L3Enter20000,25000,30000DifferentAPRincolumnI4:I20Increasingvaluesfrom4%to6%atastepvalueod0.
125%FormatI4:I20asPercentstylewith3decimalpointsSpecifywhichvariationdoyouwanttoobserve(eg.
MonthlyPaymentB12)incellI3Completingthetable.
.
.
SelectI3:L20UnderDATATABgotoWhat-IfAnalysis->DatatableGiveB2asrowinputcellboxandB4ascolumninputcellbox.
FormatcellI3todisplayAPRGoalseekCreateaGoalseekGoalseekisatoolthatidentifiesthenecessaryinputvaluetoobtainadesiredgoal.
WhatIfAnalysis->GoalseekSetcellboxB12Enter300inToValueboxBychangingcellboxB2ClickOKtoacceptthesolutionScenariomanagerSCENARIOMANAGERScenarioisasetofvaluesthatrepresentapossiblesituation.
Scenariomanagerenablesyoutodefineandmanagescenariostocomparehowtheyaffectresults.
UsingscenariomanagerCreateandeditScenariosViewScenariosGenerateScenarioSummaryReportCreateaScenarioWhat-IfAnalysis->ScenariomanagerClickAddScenarioname:Best-caseChangingscell:B2:B5Enter:25000in$B$2,5000in$B$3,0in$B$4,6in$B$5CreateAdditionalScenariosClickAddScenarioname:MostLikely-caseChangingscell:B2:B5Enter:22500in$B$2,6500in$B$3,4.
25%in$B$4,5in$B$5CreateAsummaryreportWhat-IfAnalysisScenariomanagerSummaryResultcellsbox:RangeB12:B14ClickOKsolverSolverisanadd-inapplicationthatmanipulatesvariablesbasedonconstraintstofindtheoptimalsolutiontoaproblem.
Solverisoneofthemostsophisticatedwhat-ifanalysistools,andpeopleuseSolverinavarietyofsituationsandindustries.
Loadingthesolveradd-inGotofiletabSelectoptionsAdd-insManage->ExcelAdd-insandclickGOSelectSolverAdd-inYoucanseeSolveroptioninDataribbonOptimizingresultswithsolverIdentifyObjectivecell,containstheformula-basedvaluethatyouwanttomaximize,minimize.
OrsettoavalueinSolver.
IdentifyChangingcells,containsavariablewhosevaluechangesuntilSolveroptimizesthevalueintheobjectivecellSOLVEREXAMPLEStep1:SettheObjectiveandVariablecellsStep2:DefineConstraintsStep3:GenerateareportStep1…Enter25000inB2and5000inB3Defineobjectivecellandchangingvariablecells.
Objectivecell->B12Tovalueoff:300Bychangingvariablecells:B2:B5Step2…DefineconstraintsB2=20,000B3=5,000B4=4%B5=4Step3…Generateareport.
ClickonSOLVESelectANSWERintheREPORTLISTObservetheresultinAnswerReport1worksheettab.
Multiple-sheetworkbookmanagementGroupingandungroupingworksheetsAllworksheetsAdjacentworksheetsuseSHIFTNonAdjacentworksheetsuseCONTROLWorksheetreferencePointertoacellinanotherworksheetSyntax:'Worksheet_name'!
Range_Of_CellsSelectcellB1insheet1andcalculatetheaverageforcellsJ4:J203dformulaFormulaorfunctionthatreferstothesamerangeinmultipleworksheets.
SelectcellB2insheet1Type=SUM(Clicksheet2,pressandholdshiftandthenclicksheet3andselectcellA1Theformulabecomes=SUM(Sheet2:Sheet3!
A1)InsertinghyperlinksHyperlinkisanelectronicmarkertoanotherlocationinaworksheet,workbook,file,webpageoremail.
SelectB1InsertribbonHyperlinkintheLinksgroupTypeJ4:J20intheTypethecellreferenceboxandclickOK.
Templates,themesandstylesSelectingatemplateTemplateisaspecialworkbookfileusedasamodeltocreatesimilarlystructuredworkbooksFile->NewSearchfortemplateSelectonePreviewofselectedtemplateApplyingthemesandbackgroundsAthemeisacollectionofcolors,fonts,andspecialeffects.
PageLayoutribbonThemesSelectoneAbackgroundisanimagethatappearsbehindtheworksheetdataonscreenPageLayoutribbonBackground->selectdesiredfileApplyingcellstylesAcellstyleisasetofformattingoptionsappliedtoworksheetcellsHomeribbonStyles->CellstylesQuestions