Excel技巧英文PPT(ppt文档).ppt

Excel Training for ConsultantsMay 16,2013ExpectationYou have a basic understanding of Excel navigation,but lack experience formatting,using functions,and conducting data analysis GoalsTo review/teach you some of the more common functions used in data analysisTo show you how analysis should be presentedTo teach you to understand how Excel formulas are designedPurpose of this TrainingIntro and 6 Factors to Excel ExcellenceExercise:Linking DataExercise:Conditional FormulasExercise:Pivot TablesOther Useful Formulas and ShortcutsAgendaSix factors that make a great model in Excel1)Formatting and LayoutHard coded cells are in blue,green are linked to another pageEach firm will have a different code so always ask!Six factors that make a great model in Excel2)FlexibilityHard coded cells as little as possibleAnticipate the scenarios you may be asked to analyze(10 yrs instead of 5 yrs)Six factors that make a great model in Excel3)Formulas,Formulas,FormulasThe more you know the better more on this latter!Six factors that make a great model in Excel4)Common Sense and TraceabilityChances are your clients are going to use your models.Make sure they are easy to use and understand for those with lesser skills(i.e.just because you can use array formulas doesnt make them better than sumif formulas)If a formula is too long,add a column or row and split up the formula.Your first tab should be either a summary or instructions and be sure to include tabs for reference or source data.Label every line clearly.Use comments if necessary to explain a line or a cell.Six factors that make a great model in Excel5)Pivot TablesThey are key to you going to bed at all!We will review the basics of Pivot Tables today,but the more your know the better off you will be and the more sleep you will getSix factors that make a great model in Excel6)VersioningSave your work every 15 minutes.Do not use Ctrl SDevelop a file naming convention(e.g.name date_time of day)nSummary TabModels should have a tab that summarizes the analysis(using linked formulas,not hard-coded numbers,of course)The data should be summarized in a way such that it can be easily copy/pasted(as a picture or PowerPoint table,not excel table)into a PowerPoint document/deliverableInclude any key assumptions to be copy/pasted into a deliverableThe summary tab may also contain the graphs that are to be copy/pasted into a PowerPoint document/deliverablenTable of Contents TabIf you are planning on turning the model over to the client or anyone else and there are multiple tabs,create a table of contents briefly describing each taFormatting Examples:Client-Ready ModelsnStaging:Combine the 3 data sources into one table based on consultant nameKeep in mind absolute vs.relative referencesnAnalytics:Use VLOOKUP,INDEX,and MATCH to identify the consultant with the most salesKeep in mind your basic Excel formulasExercise:Linking DatanReview:Use basic SUM and COUNT formulas to analyze a bidders pricingRemember to compare“apples to apples”nAdvanced:Compare pricing between multiple vendors incorporating multiple criteria into the basic SUM and COUNT formulasRemember to compare“apples to apples”Remember array formulas must be entered by pressing CTRL+SHIFT+ENTERnKick it up a notch:Expand the analysis and compare pricing between multiple vendors based on subcategories of the items up for bidRemember to compare“apples to apples”Remember array formulas must be entered by pressing CTRL+SHIFT+ENTEROftentimes,a bidder has to be able to fulfill all items in a subcategory to be considered in the analysisExercise:Conditional FormulasMake sure all columns have unique headingsAnalytics:PivotTables Prep the DatanCreate a PivotTable to analyze:Revenue,COGS and Freight by Customer Type nAdd another dimension to the PivotTable:Discover how Revenue,COGS and Freight are distributed across Products within each Customer Type by adding Products to the column headingnAdd another dimension to the PivotTable:Discover how California residents differ from Arizona residents by adding State to the Columns LabelnRefresh your PivotTable after changing the source dataInclude a 10%discount to Freight across all customersData must have different headingsExercise:Pivot TablenlennleftnrightnconcatenatenexactncountnpropernText to ColumnsntrimnASAP UtilitiesnGoal SeeknSolverOther Useful FunctionsShort CutsLearning common keystrokes and commands and save a lot of time