审计Excel培训Exceltraining(41页PPT).pptx

《审计Excel培训Exceltraining(41页PPT).pptx》由会员分享，可在线阅读，更多相关《审计Excel培训Exceltraining(41页PPT).pptx（41页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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、成功源于不懈的努力，人生最大的敌人是自己怯懦。16:55:0816:55:0816:5511/8/2022 4:55:08 PM3、每天只看目标，别老想障碍。11月-2216:55:0816:55Nov-2208-Nov-224、宁愿辛苦一阵子，不要辛苦一辈子。16:55:0816:55:0816:55Tuesday,November 8,20225、积极向上的心态，是成功者的最基本要素。11月-2211月-2216:55:0816:55:08November 8,20226、生活总会给你另一个机会，

43、这个机会叫明天。08十一月20224:55:08下午16:55:0811月-227、人生就像骑单车，想保持平衡就得往前走。十一月224:55下午11月-2216:55November 8,20228、业余生活要有意义，不要越轨。2022/11/816:55:0816:55:0808 November 20229、我们必须在失败中寻找胜利，在绝望中寻求希望。4:55:08下午4:55下午16:55:0811月-2210、一个人的梦想也许不值钱，但一个人的努力很值钱。11/8/2022 4:55:08 PM16:55:0808-11月-2211、在真实的生命里，每桩伟业都由信心开始，并由信心跨出第一步。11/8/2022 4:55 PM11/8/2022 4:55 PM11月-2211月-22谢谢大家谢谢大家