审计Eel培训Eelraining.ppt

UpdatedMay2014 XL1ExcelHandling Introduction Introduction ObjectivesFirststepKnowthebasicstobeoperationalbythefirstauditengagementLearnkeyformulasandmaintoolsencounteredduringauditBeingabletomaketheclientfilesquicklyexploitableSecondstepUsingfeaturestoimprovethequalityofassignmentsDevelopreusabletoolsofinterventiontogainefficiencyandspeed Summary Formatting Layoutp5CustomConditionalformattingPrinting SetprintareaHeaderfooterRepeatrowsandcolumnsatthetop ontheleftPagebreakpreviewTexttocolumns seepdfRemoveduplicatesGroup ungroupDatavalidationGotofunction CtrlgFreezepanesSetrowheight columnwidthTransposeProtectworksheet workbookBasicformulasp22MathematicalformulasSum max minAverageRoundSubtotalSumif sumifsIf iferrorVlookup HlookupABS TextualformulasLeft Right Mid FindDateUpper LowerSydANDDataManagement filtersp34 Formatting Layout CellFormattingMenu CellFormatting Copy Cut Pasteoptions Fontstyle Bordersandfills Fontsize Vertical Horizontalalignmentinthecell Mergeandcenter Withdrawalsinthecells Wraptext Numberformat Cell sformatisaccessibleviaclickright CellFormatting DifferentformatsandsizesareavailableSyntaxcustomformat00 zerospacezero or toseparatethousands 12345678Wheneveraspaceisroundedtotheright zerospacezerospace thedisplayednumberisroundedthousand 12346Youcansetthedisplayofnegativenumbers forexample 00 00 12345or 12345 or Toaddcolor 00 Red 00 12345or 12345 or Toaddasymbol Eg youmustaddthefollowing 00 Red 00 12345 or 12345 or Warning Theformatofacelldoesnotchangeitsvalue Custom Seeexcel Conditionalformattingisahandytooltoformatthecellsbasedintheirvalueortexttheycontain Itisinterestingtousethisfeaturewhenyouwanttoquicklyidentifyinconsistentorunwantedvalues Inparticular itidentifiesduplicatesinalist Conditionallayout duplicatesidentification Menu home Beforeprintingyoucanselectarangeofcellsandsettheprintareatomakesureofwhatisprinted Theheadersandfooters nameofthemissionandtheauthor closingdateanddocumentdate aredefinedinthecorrespondingtab Printing 1 2 Usetheadjustmenttorestrictprinting Afterselectingthecorrespondingcells clickhereto set theprintarea Tofreezerowsorcolumnsforprinting oftenthefirstlineofalargetable clickon PrintTitles orgotothe Sheet tabmenulayout Printing 2 2 Settheprintarea Definerows columnstokeepforprinting Afterclickingdefinetheareasconcerned Pagebreakpreview youcansetprintarea Texttocolumns SeePDF Removeduplicates Seeexcel GroupandUngroup DONOTHIDEANYTHINGINTHEEXCEL Seeexcel DataValidation Seeexcel Gotofunction ctrlg ItispossibletofreezerowsandcolumnstodisplayEspeciallyusefulfortableswithmanyrows columnswhenyoualwayswanttoseethefirst headertables Todothis placethecursoratapointonthesheet seenextslide andclick Freezepanes Displayingthetabs panes windows 1 2 VIEW Frozenlines column willbetheonesabove lefttotheselection Tofreezearow column simplyselecttherow columnlocatedtotheright below Toremovethisdisplay click UnfreezePanes inthesamemenu Displayingthetabs panes windows 2 2 Rowheight columnwidth Rowheight ctrlORE Columnwidth ctrlOCW Transpose Protection Basicformulas Ingeneral aformulaisusedbywriting followedbythedesired SUM etc formula Thechoiceoftheformulacanbedoneintwoways Double clickthenameoftheformula SelectionwitharrowkeysandTABThefieldsofaformulaarealwaysseparatedbyasemicolon fieldsinbracketareoptional Thetwopointsareusedtodefineawiderangeofcells Example sum I3 J8 Inthefollowing wepresentthemainformulasusedinourauditengagements Mathematicalformulas 1 10 SUM num1 num2 MAX num1 num2 MIN num1 num2 Calculatesthesumofseveralcomponents returnsthehighest lowestvalue num1 num2 etc areindividualcellsorcellranges Theshortcut Alt enablestocreateasumdirectlyandautomaticallyselectstheareaifitisbounded AVERAGE num1 num2 Calculatestheaverageofseveralelements num1 num2 etc aredifferentcellsorrangesofcellstobeconsideredintheaverage ROUND num num digits Roundsanumbertothenumberoffigures numisthenumbertoberounded num digitsthedesirednumberofdecimalplacesforrounding ROUNDDOWNandROUNDUParevariantsfunctionstospecifyifyouwanttoroundtowardorawayfrom0 respectively Mathematicalformulas 2 10 SUBTOTAL function num ref1 ref2 1 2 Calculatesasubtotalinalistordatabase function numisthenumberfrom1to11 includingthehiddenvalues or101and111 unknownhiddenvalues thatspecifiesthesummaryfunctionforthesubtotal AsfortheSUMfunction numbersare9and109 ThebigdifferencewiththeSUMfunctionisthatSUBTOTALfunctiondoesnotsumfiltereddata ieexamplebelow Toknowthenumberofthedesiredfunction simplytype SUBTOTAL inablankcell Keyfunctionsused 1 101 average2 102 numberofcells4 104 max5 105 min9 109 sum Mathematicalformulas 3 10 Mathematicalformulas 4 10 SUBTOTAL function num ref1 ref2 2 2 Example SUMIF range criteria sum range Addsthespecifiedcellsinacertaincriterion rangeistherangeofcellsyouwantevaluated iethoseonwhichthetestisapplied criteriaisthecriteriathatdefineswhichcellswillbeadded criteriontobemetbycellsrange Thecriteriacanbeanumber expression ortext inthiscase addquotes sum rangearetheactualcellstosum Ifomitted thecellsinrangeareused TheSUMIFfunctionallowsyoutoaddcriteriaSUM TheNBIFfunction orCOUNTIF issimilarbutinthenumberofcellswiththeeligibilitycriteria VIC cellswhosecontentisequaltoVIC64 cellswhosecontentisequalto64 55 cellswhosecontentisstrictlygreaterthan54 974 cellswhosecontentaredifferentfrom974 Mathematicalformulas 5 10 SUMIFS sum range criteria range1 criteria1 Mathematicalformulas 6 10 IF logical test value if true val if false Calculatesavaluebasedonalogicaltest ordigital logical testshouldreturnTRUEorFALSEVal if trueandval if falsearethetwovaluestobereturnedaccordingtothelogicaltest Itismandatorytohaveatleastoneofthesefieldsbutnotnecessarilyboth ItispossibletorepeattheIFfunctioninsideitself uptoseventimes Examplesoflogicaltest D972 belenus C5 D8M54 67L24 bimont F5Tocombinelogicaltests theANDandORfunctionsexistandtakeargumentsaslogicaltestsseparatedbysemicolons Mathematicalformulas 7 10 IFERROR value value if error Returnstheselectedvalueunlessitisanerror offormula referenceetc inwhichcaseitisanothervaluethatisreturned Valueisthevalue cereturnedbydefault IfExceldetectsanerrorinit thenitreturnsvalue if error Thisfunctionisespeciallyusefulwhenextendingaformulaanditispossibletohaveerrorsoncertainrows divisionby0 referencenotfound etc InsuchcasesitisusefultousesuchIFERROR formula orIFERROR formula 0 Mathematicalformulas 8 10 VLOOKUP lookup value table array col index num range lookup Searchavalueinthefirstcolumntotheleftofatable andthenreturnsavalueinthesamerowfromaspecifiedcolumn lookup valueisthevaluesoughtbyExcelinthefirstcolumntotheleftofthetable arrayarea table arrayisatableinwichdataisretrieved Itincludesthefirstcolumnandthesearchbox col index numisthecolumnnumber intable arrayandnotintheExceltab fromwhichthematchingvaluesouldbereturned Itmustbebetween1 firstcolumn andthenumberofcolumnsoftable array range lookupexiststo TRUEorFALSE 1or0 specifyifyouwantthevaluefoundinthefirstcolumntobeanexactmatch useFALSE ortheclosestmatch useRIGHT withtheonesought Ifrange lookupfieldisomitted ExcelassumesitTRUE thatistosay itlooksfortheclosestmatchwithsoughtvalue Iflookup valueorclosevalueisnotfound Excelreturnsthenextlowervalue Inthiscase itismoreprudenttosortthefirstcolumninascendingorderbecauseExcelcanreturnanerror Exceptinspecialcases inourusage setrange lookuptoFALSE or0 toavoiderror Mathematicalformulas 9 10 VLOOKUP lookup value table array col index num range lookup CAUTIONDUPLICATESinthefirstcolumn Iflookup valueisduplicate Excelreturnsthelastvaluefound Forallvalues lyingonlineslookup value itshouldcreateanintermediatecolumn Bestisstilltoavoidduplication CAUTIONFORMATScell Iflookup valueisadateoranumber itshouldensurethattheformatofthefirstcolumnisadequatetable array sameformataslookup value Otherwise Excelcouldnotfindanything Forcol index numfield itisbesttousetheMATCHfunction iefollowingslides ratherthancountingcolumnsandrisktochangecolumn spositionintable array byaddingcolumns etc TheVinVLOOKUPstandsfor Vertical asExcelislookingverticallyinacolumn HertwinHLOOKUPfunctionallowssearchinginarowandreturnsavalueinthesamecolumnasthevaluefound Itoperatesonthesameprinciple ABS Absolute Mathematicalformulas 10 10 Wehaveseenthemostusedmathematicalformulas mainlyperformingonnumbers However whenusingmathematicalformulas weshouldpayattentiontocellformatsandspecialcharacters commonwhenExcelfilecomesdirectlyfromcomputersystems Inthefollowing wepresentthemainformulasthatperformoncharactersandtext Textformulas 1 2 LEFT text num chars RIGHT text num chars MID FINDExcerpt s first s character s tothefarleft rightofatextstring textisthetextstringcontainingthecharactersyouwanttoextract num charsspecifiesthenumberofcharactersyouwantLEFT RIGHTtoexctract 1ifomitted Thesetwofunctionsareespeciallyusefulwhencombinedwithlogicaltests IF LEFT L14 4 dress Cherize Vic DATE year month day Unlikethepreviousfunctions itreconstructsadatefromthethreefieldsofday monthandyear UPPER LOWERSYD D