《审计Excel培训(41页PPT).pptx》由会员分享，可在线阅读，更多相关《审计Excel培训(41页PPT).pptx（41页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1UpdatedMay2014XL1ExcelHandling2Introduction3IntroductionuObjectivesFirststepKnowthebasicstobeoperationalbythefirstauditengagementLearnkeyformulasandmaintoolsencounteredduringauditBeingabletomaketheclientfilesquicklyexploitableSecondstepUsingfeaturestoimprovethequalityofassignmentsDevelop reusable t
2、ools of intervention to gain efficiency andspeed4FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsSummaryA.Formatting/Layoutp5CustomConditionalformattingPrintingSetprintareaHeaderfooterRepeatrowsandcolumnsatthetop/ontheleftPagebreakpreviewTexttocolumnsseepdfRemoveduplicatesGroup/ungroupDat
3、avalidationGotofunctionCtrlgFreezepanesSetrowheight/columnwidthTransposeProtectworksheet/workbookB.Basicformulasp22MathematicalformulasSum/max/minAverageRoundSubtotalSumif/sumifsIf/iferrorVlookup/HlookupABSTextualformulasLeft/Right/Mid/FindDateUpper/LowerSydANDC.DataManagementfiltersp345Formatting/L
4、ayout6FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuCellFormattingMenu:CellFormattingCopy/Cut/PasteoptionsFontstyleBordersandfillsFontsizeVertical/HorizontalalignmentinthecellMergeandcenterWithdrawalsinthecellsWraptextNumberformat7FormattingShortcutsBasicformulasDatamanagementAdvancedt
5、oolsuCellsformatisaccessibleviaclickrightCellFormattinguDifferentformatsandsizesareavailableuSyntaxcustomformat00(zerospacezero)or#toseparatethousands:12345678Wheneveraspaceisroundedtotheright(zerospacezerospace),thedisplayednumberisroundedthousand:12346Youcansetthedisplayofnegativenumbers,forexampl
6、e:00,(00);12345or(12345)or-Toaddcolor:00,Red(00);12345or(12345)or-Toaddasymbol(Eg.),youmustaddthefollowing:00,Red(00);12345or(12345)or-Warning:Theformatofacelldoesnotchangeitsvalue!8FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsCustomSeeexcel9FormattingShortcutsBasicformulasDatamanageme
7、ntAdvancedtoolsuConditionalformattingisahandytooltoformatthecellsbasedintheirvalueortexttheycontain.uItisinterestingtousethisfeaturewhenyouwanttoquicklyidentifyinconsistentorunwantedvalues.uInparticular,itidentifiesduplicatesinalist.Conditionallayout/duplicatesidentificationMenuhome10FormattingShort
8、cutsBasicformulasDatamanagementAdvancedtoolsuBeforeprintingyoucanselectarangeofcellsandsettheprintareatomakesureofwhatisprinted.uTheheadersandfooters(nameofthemissionandtheauthor,closingdateanddocumentdate)aredefinedinthecorrespondingtab.Printing(1/2)UsetheadjustmenttorestrictprintingAfterselectingt
9、hecorrespondingcells,clickheretosettheprintarea11FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuTofreezerowsorcolumnsforprinting(oftenthefirstlineofalargetable),clickonPrintTitlesorgototheSheettabmenulayout:Printing(2/2)SettheprintareaDefinerows/columnstokeepforprinting.Afterclickingdef
10、inetheareasconcerned12FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsPagebreakpreviewyoucansetprintarea13FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsTexttocolumnsuSeePDF14FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsRemoveduplicatesuSeeexcel15FormattingShortc
11、utsBasicformulasDatamanagementAdvancedtoolsGroupandUngroupDONOTHIDEANYTHINGINTHEEXCEL!uSeeexcel16FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsDataValidationuSeeexcel17FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsGotofunction(ctrlg)18FormattingShortcutsBasicformulasDataman
12、agementAdvancedtoolsuItispossibletofreezerowsandcolumnstodisplayEspeciallyusefulfortableswithmanyrows/columnswhenyoualwayswanttoseethefirst(headertables).uTodothis,placethecursoratapointonthesheet(seenextslide)andclickFreezepanes.Displayingthetabs/panes/windows(1/2)-VIEW19FormattingShortcutsBasicfor
13、mulasDatamanagementAdvancedtoolsuFrozenlines(column)willbetheonesabove(lefttotheselection):uTofreezearow/column,simplyselecttherow/columnlocatedtotheright/below.uToremovethisdisplay,clickUnfreezePanesinthesamemenu.Displayingthetabs/panes/windows(2/2)20FormattingShortcutsBasicformulasDatamanagementAd
14、vancedtoolsRowheight/columnwidthuRow height ctrlOREuColumn width ctrlOCW21FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsTranspose22FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsProtection23Basicformulas24FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuIngeneral,
15、aformulaisusedbywriting=followedbythedesired(SUM,etc.)formula.The choice of the formula can bedoneintwoways:Double-clickthenameoftheformula;SelectionwitharrowkeysandTABuThe fields of a formula are always separated by a semicolon(fieldsinbracketareoptional).uThetwopointsareusedtodefineawiderangeofcel
16、ls:Example:=sum(I3:J8)uInthefollowing,wepresentthemainformulasusedinourauditengagements.Mathematicalformulas(1/10)25FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuSUM(num1,num2,)/MAX(num1,num2,)/MIN(num1,num2,)Calculatesthesumofseveralcomponents/returnsthehighest/lowestvalue.num1,num2,e
17、tc.areindividualcellsorcellranges.Theshortcut(Alt+=)enablestocreateasumdirectlyandautomaticallyselectstheareaifitisbounded.uAVERAGE(num1,num2,.)Calculatestheaverageofseveralelements.num1,num2,etc.aredifferentcellsorrangesofcellstobeconsideredintheaverage.uROUND(num,num_digits)Roundsanumbertothenumbe
18、roffigures.num is the number to be rounded;num_digits the desired number of decimal places forrounding.ROUNDDOWNandROUNDUParevariantsfunctionstospecifyifyouwanttoroundtowardorawayfrom0(respectively).Mathematicalformulas(2/10)26FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuSUBTOTAL(func
19、tion_num,ref1,ref2,)(1/2)Calculatesasubtotalinalistordatabase.function_num is the number from 1 to 11(including the hidden values)or 101 and 111(unknownhiddenvalues)thatspecifiesthesummaryfunctionforthesubtotal.AsfortheSUMfunction,numbersare9and109.ThebigdifferencewiththeSUMfunctionisthatSUBTOTALfun
20、ctiondoesnotsumfiltereddata.(ieexamplebelow)Toknowthenumberofthedesiredfunction,simplytype=SUBTOTAL(inablankcell).Keyfunctionsused:1/101:average2/102:numberofcells4/104:max5/105:min9/109:sumMathematicalformulas(3/10)27FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsMathematicalformulas(4/
21、10)InitialtabRows3to5hiddenFilteruSUBTOTAL(function_num,ref1,ref2,)(2/2)Example28FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuSUMIF(range,criteria,sum_range)Addsthespecifiedcellsinacertaincriterion.rangeistherangeofcellsyouwantevaluated,iethoseonwhichthetestisapplied.criteriaisthecrit
22、eriathatdefineswhichcellswillbeadded.criteriontobemetbycellsrange.Thecriteriacanbeanumber,expression,ortext(inthiscase,addquotes).sum_rangearetheactualcellstosum.Ifomitted,thecellsinrangeareused.TheSUMIFfunctionallowsyoutoaddcriteriaSUM.TheNBIFfunction(orCOUNTIF)issimilarbutinthenumberofcellswiththe
23、eligibilitycriteria:VIC:cellswhosecontentisequaltoVIC64:cellswhosecontentisequalto6455:cellswhosecontentisstrictlygreaterthan54=972:cellswhosecontentislessorequalthan972&974:cellswhosecontentaredifferentfrom974Mathematicalformulas(5/10)29FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuSU
24、MIFS(sum_range,criteria_range1,criteria1)Mathematicalformulas(6/10)30FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuIF(logical_test,value_if_true,val_if_false)Calculatesavaluebasedonalogicaltest(ordigital)logical_testshouldreturnTRUEorFALSEVal_if_trueandval_if_falsearethetwovaluestobere
25、turnedaccordingtothelogicaltest.Itismandatorytohaveatleastoneofthesefieldsbutnotnecessarilyboth.ItispossibletorepeattheIFfunctioninsideitself(uptoseventimes).Examplesoflogicaltest:D972=belenusC5=D8M54=67L24=bimont&F5Tocombinelogicaltests,theANDandORfunctionsexistandtakeargumentsaslogicaltestsseparat
26、edbysemicolons.Mathematicalformulas(7/10)31FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuIFERROR(value,value_if_error)Returnstheselectedvalueunlessitisanerror(offormula,referenceetc.),inwhichcaseitisanothervaluethatisreturned.Value is the value/ce returned by default.If Excel detects a
27、n error in it,then it returnsvalue_if_error.Thisfunctionisespeciallyusefulwhenextendingaformulaanditispossibletohaveerrorsoncertainrows(divisionby0,referencenotfound,etc.).InsuchcasesitisusefultousesuchIFERROR(formula,)orIFERROR(formula,0).Mathematicalformulas(8/10)32FormattingShortcutsBasicformulas
28、DatamanagementAdvancedtoolsuVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Searchavalueinthefirstcolumntotheleftofatable,andthenreturnsavalueinthesamerowfromaspecifiedcolumn.lookup_valueisthevaluesoughtbyExcelinthefirstcolumntotheleftofthetable_arrayarea.table_arrayisatableinwichdataisr
29、etrieved.Itincludesthefirstcolumnandthesearchbox.col_index_numisthecolumnnumber(intable_arrayandnotintheExceltab)fromwhichthematchingvaluesouldbereturned.Itmustbebetween1(firstcolumn)andthenumberofcolumnsoftable_array.range_lookupexiststo(TRUEorFALSE/1or0)specifyifyouwantthevaluefoundinthefirstcolum
30、n to be an exact match(use FALSE)or the closest match(use RIGHT)with the onesought.Ifrange_lookupfieldisomitted,ExcelassumesitTRUE,thatistosay,itlooksfortheclosestmatchwithsoughtvalue.Iflookup_valueorclosevalueisnotfound,Excelreturnsthenextlowervalue.Inthiscase,itismoreprudenttosortthefirstcolumnina
31、scendingorderbecauseExcelcanreturnanerror.Exceptinspecialcases,inourusage,setrange_lookuptoFALSE(or0)toavoiderror.Mathematicalformulas(9/10)33FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)CAUTIONDUPLICATESinthefirstcolumn.Iflo
32、okup_valueisduplicate,Excelreturnsthelastvaluefound.Forallvalueslyingonlineslookup_value,itshouldcreateanintermediatecolumn.Bestisstilltoavoidduplication.CAUTIONFORMATScell.Iflookup_valueisadateoranumber,itshouldensurethattheformatofthefirstcolumnisadequatetable_array(sameformataslookup_value).Other
33、wise,Excelcouldnotfindanything.Forcol_index_numfield,itisbesttousetheMATCHfunction(iefollowingslides)ratherthancountingcolumnsandrisktochangecolumnspositionintable_array(byaddingcolumns,etc.).TheVinVLOOKUPstandsforVerticalasExcelislookingverticallyinacolumn.HertwinHLOOKUPfunctionallowssearchinginaro
34、wandreturnsavalueinthesamecolumnasthevaluefound.Itoperatesonthesameprinciple.uABSAbsoluteMathematicalformulas(10/10)34FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuWe have seen the most used mathematical formulas,mainly performing onnumbers.uHowever,whenusingmathematical formulas,we sh
35、ouldpayattention to cellformats and special characters(common when Excel directly from computersystems).uInthefollowing,wepresentthemainformulasthatperformoncharactersandtext.Textformulas(1/2)35FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuLEFT(text,num_chars)/RIGHT(text,num_chars)/MID
36、/FINDExcerpt(s)first(s)character(s)tothefarleft/rightofatextstring.textisthetextstringcontainingthecharactersyouwanttoextract.num_charsspecifiesthenumberofcharactersyouwantLEFT/RIGHTtoexctract;1ifomitted.Thesetwofunctionsareespeciallyusefulwhencombinedwithlogicaltests:IF(LEFT(L14,4)=dress,Cherize,Vi
37、c)uDATE(year,month,day)Unlikethepreviousfunctions,itreconstructsadatefromthethreefieldsofday,monthandyear.uUPPER/LOWERuSYDDepreciationsumofyearsuANDTextformulas(2/2)36DataManagementFormattingShortcutsBasicformulasDatamanagementAdvancedtools37FormattingShortcutsBasicformulasDatamanagementAdvancedtool
38、suTheuseofdatafiltersisverycommon,especiallywhenusedtableshavemanyrows.uTofilter,selecttherangeofdatatofilter(thefirstlineisthelineofheaderfields)andclickonfilter.Filters(1/3)FormattingShortcutsBasicformulasDatamanagementAdvancedtools38FormattingShortcutsBasicformulasDatamanagementAdvancedtoolsuThen
39、clickonthearrowstousefiltersNumberfiltersColorfilters(cellortext)uExamplesoffilters:64*:selectallcellsstartingwith64*9:selectallcellsendingin9*5*ou5:selectallcellsthatcontaina5.Alsoworksforcellfilters.Example:*balance*.Filters(2/3)FormattingShortcutsBasicformulasDatamanagementAdvancedtools39Formatti
40、ngShortcutsBasicformulasDatamanagementAdvancedtoolsuKeyelementsrelativetofilters:Becarefulwiththemergersofrowsandcolumns,thefilteronlyselectsthefirstrow:Ifthetableisclearlydefined(norowsorcolumnswithemptycells),Excelautomaticallydetectsthedataareaandcreatesthefilteralone.Ifthereisablanklineinthemidd
41、leofthetable,Excelstopsbeforethisline.Therefore,itisrecommendedtoselecttheareatowhichyouwanttoapplythefilterjusttomakesureyoudonotforget/omitanylines.Filters(3/3)FilteronGroup240THE ENDOriginalFrenchversionfromGuillaumeJOUBERT,translatedintoenglishbyDavidPraireandVictoriendeGenes.1、每一个成功者都有一个开始。勇于开始
42、，才能找到成功的路。11月-2211月-22Tuesday,November 8,20222、成功源于不懈的努力，人生最大的敌人是自己怯懦。19:51:4819:51:4819:5111/8/2022 7:51:48 PM3、每天只看目标，别老想障碍。11月-2219:51:4819:51Nov-2208-Nov-224、宁愿辛苦一阵子，不要辛苦一辈子。19:51:4819:51:4819:51Tuesday,November 8,20225、积极向上的心态，是成功者的最基本要素。11月-2211月-2219:51:4819:51:48November 8,20226、生活总会给你另一个机会，
43、这个机会叫明天。08十一月20227:51:48下午19:51:4811月-227、人生就像骑单车，想保持平衡就得往前走。十一月227:51下午11月-2219:51November 8,20228、业余生活要有意义，不要越轨。2022/11/819:51:4819:51:4808 November 20229、我们必须在失败中寻找胜利，在绝望中寻求希望。7:51:48下午7:51下午19:51:4811月-2210、一个人的梦想也许不值钱，但一个人的努力很值钱。11/8/2022 7:51:48 PM19:51:4808-11月-2211、在真实的生命里，每桩伟业都由信心开始，并由信心跨出第一步。11/8/2022 7:51 PM11/8/2022 7:51 PM11月-2211月-22谢谢大家谢谢大家