Lynda com Excel 2010 Advanced Formulas and Functions 英文發音 僅限電腦播放 CD Lynda.com出品的時長達17小時05分的Excel2010:高級公式和函數視頻教程。由DennisTaylor闡明了公式和Excel的一些幾乎有400個最具挑戰性的功能,還講述了怎樣最好的使用它。課程回顧了積木式函數,少數幾個關鍵快捷鍵用于加速Excel數據的使用。以及如何執行查找功能的高級搜索和數據檢索,計算制表數據,統計和數學函數,格式化文本函數數據與財務數據使用的高級公式。本教程附帶練習文件。 目錄: ViewedDuration 01:49Introduction Welcome01:25 Usingtheexercisefiles00:24 47:121.FormulaandFunctionTipsandShortcuts Usingtheentirerow/columnreferences05:43 Copyingcolumnformulasinstantly04:35 Convertingformulastovalueswithasimpledrag05:10 Creating3Dformulastogatherdatafrommultiplesheets05:17 Updatingvalueswithoutformulas04:25 Displayingandhighlightingformulas07:15 Simplifyingdebuggingformulas06:29 Enhancingreadabilitywithrangenames08:18 22:322.FormulaandFunctionTools Reviewingfunctionbasics07:12 UsingandextendingAutoSum04:55 Usingabsoluteandrelativereferences04:52 Usingmixedreferences05:33 16:393.IFandRelatedFunctions ExploringIFlogicaltestsandusingrelationaloperators04:43 CreatingandexpandingtheuseofnestedIFstatements04:47 UsingtheAND,OR,andNOTfunctionswithIFtocreatecompoundlogicaltests07:09 41:014.LookupandReferenceFunctions LookingupinformationwithVLOOKUPandHLOOKUP04:27 FindingapproximatematcheswithVLOOKUP06:22 FindingexactmatcheswithVLOOKUP04:21 NestingLOOKUPfunctions05:51 Findingtable-likeinformationwithinafunctionwithCHOOSE03:51 LocatingdatawithMATCH05:03 RetrievinginformationbylocationwithINDEX04:51 UsingMATCHandINDEXtogether06:15 12:275.PowerFunctions TabulatinginformationonasinglecriterionwithCOUNTIF,SUMIF,andAVERAGEIF04:49 TabulatinginformationonmultiplecriteriawithCOUNTIFS,SUMIFS,andAVERAGEIFS07:38 11:356.StatisticalFunctions FindingthemiddlevaluewithMEDIAN01:35 RankingdatawithoutsortingwithRANK04:50 FindingthemagnitudedatawithLARGEandSMALL01:46 TabulatingblankcellswithCOUNTBLANK03:24 23:277.DataFunctions UnderstandingExceldate/timecapabilitiesinformulas07:18 IdentifyingthedayoftheweekwithWEEKDAY06:02 CountingworkingdayswithNETWORKDAYS03:05 DeterminingacompletiondatewithWORKDAY02:24 Tabulatingdate/timedifferenceswithDATEDIF04:38 25:368.MathFunctions Workingwithroundingfunctions08:03 FindingtheremainderwithMODandusingMODwithconditionalformatting04:42 BuildingrandomnumbergeneratorswithRANDandRANDBETWEEN04:42 ConvertingavaluebetweenmeasurementsystemswithCONVERT03:07 UsingthepowerfulnewAGGREGATEfunctiontobypasserrorsandhiddendata05:02 27:169.ArrayFormulasandFunctions Extendingformulacapabilitieswitharrays07:12 Countinguniqueentriesinarangewithanarrayformula04:46 DeterminingfrequencydistributionswithFREQUENCY03:06 Flippingrow/columnorientationwithTRANSPOSE04:38 BuildinganalysisviaregressiontechniqueswithTRENDandGROWTH03:07 Combiningmultiplefunctionsinarrays04:27 36:1110.TextFunctions LocatingandextractingdatawithFINDandMID06:18 ExtractingspecificdatawithLEFTandRIGHT03:02 RemovingexcessspaceswithTRIM05:42 UsingCONCATENATEwithfunctions04:24 AdjustingcasewithincellswithPROPER,UPPER,andLOWER04:17 AdjustingcharactercontentwithREPLACEandSUBSTITUTE05:56 Reviewingadditionaltextfunctions06:32 13:3011.FinancialFunctions CalculatingpaymentswithPMT04:34 FindingfuturevalueswithFV04:03 DeterminingtotalamountoffuturepaymentswithPV04:53 11:1312.InformationFunctions WorkingwiththeISinformationfunctions04:42 Usingerror-checkingfunctions:ISERR,ISERROR,IFERROR06:31 14:2913.ReferenceFunctions GettingdatafromremotecellswithOFFSET04:46 ReturningreferenceswithINDIRECT09:43 00:41Conclusion Finalthoughts00:41