Lynda.com出品Excel 2010: 高级公式和函数视频教程 Lynda.com Excel 2010 Advanced Formulas and Fu

游客，本帖隐藏的内容需要积分高于 1 才可浏览，您当前积分为 0

资源信息： 中文名: Lynda.com出品Excel 2010: 高级公式和函数视频教程 英文名: Lynda.com Excel 2010 Advanced Formulas and Functions 资源格式: 光盘镜像 学校: Lynda.com 主讲人: Dennis Taylor 发行日期: 2010年 地区: 美国 对白语言: 英语 文字语言: 英文 概述:

语言：英语 网址：http://www.lynda.com/home/DisplayCourse.aspx?lpk2=75923 类别：教程 （xuehui@TLF） Lynda.com 出品的时长达17小时05分的 Excel 2010: 高级公式和函数 视频教程。由Dennis Taylor阐明了公式和Excel的一些几乎有400个最具挑战性的功能，还讲述了怎样最好的使用它。课程回顾了积木式函数，少数几个关键快捷键用于加速Excel数据的使用。以及如何执行查找功能的高级搜索和数据检索，计算制表数据，统计和数学函数，格式化文本函数数据与财务数据使用的高级公式。本教程附带练习文件。 目录: Viewed Duration 01:49 Introduction Welcome 01:25 Using the exercise files 00:24 47:12 1. Formula and Function Tips and Shortcuts Using the entire row/column references 05:43 Copying column formulas instantly 04:35 Converting formulas to values with a simple drag 05:10 Creating 3D formulas to gather data from multiple sheets 05:17 Updating values without formulas 04:25 Displaying and highlighting formulas 07:15 Simplifying debugging formulas 06:29 Enhancing readability with range names 08:18 22:32 2. Formula and Function Tools Reviewing function basics 07:12 Using and extending AutoSum 04:55 Using absolute and relative references 04:52 Using mixed references 05:33 16:39 3. IF and Related Functions Exploring IF logical tests and using relational operators 04:43 Creating and expanding the use of nested IF statements 04:47 Using the AND, OR, and NOT functions with IF to create compound logical tests 07:09 41:01 4. Lookup and Reference Functions Looking up information with VLOOKUP and HLOOKUP 04:27 Finding approximate matches with VLOOKUP 06:22 Finding exact matches with VLOOKUP 04:21 Nesting LOOKUP functions 05:51 Finding table-like information within a function with CHOOSE 03:51 Locating data with MATCH 05:03 Retrieving information by location with INDEX 04:51 Using MATCH and INDEX together 06:15 12:27 5. Power Functions Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF 04:49 Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS 07:38 11:35 6. Statistical Functions Finding the middle value with MEDIAN 01:35 Ranking data without sorting with RANK 04:50 Finding the magnitude data with LARGE and SMALL 01:46 Tabulating blank cells with COUNTBLANK 03:24 23:27 7. Data Functions Understanding Excel date/time capabilities in formulas 07:18 Identifying the day of the week with WEEKDAY 06:02 Counting working days with NETWORKDAYS 03:05 Determining a completion date with WORKDAY 02:24 Tabulating date/time differences with DATEDIF 04:38 25:36 8. Math Functions Working with rounding functions 08:03 Finding the remainder with MOD and using MOD with conditional formatting 04:42 Building random number generators with RAND and RANDBETWEEN 04:42 Converting a value between measurement systems with CONVERT 03:07 Using the powerful new AGGREGATE function to bypass errors and hidden data 05:02 27:16 9. Array Formulas and Functions Extending formula capabilities with arrays 07:12 Counting unique entries in a range with an array formula 04:46 Determining frequency distributions with FREQUENCY 03:06 Flipping row/column orientation with TRANSPOSE 04:38 Building analysis via regression techniques with TREND and GROWTH 03:07 Combining multiple functions in arrays 04:27 36:11 10. Text Functions Locating and extracting data with FIND and MID 06:18 Extracting specific data with LEFT and RIGHT 03:02 Removing excess spaces with TRIM 05:42 Using CONCATENATE with functions 04:24 Adjusting case within cells with PROPER, UPPER, and LOWER 04:17 Adjusting character content with REPLACE and SUBSTITUTE 05:56 Reviewing additional text functions 06:32 13:30 11. Financial Functions Calculating payments with PMT 04:34 Finding future values with FV 04:03 Determining total amount of future payments with PV 04:53 11:13 12. Information Functions Working with the IS information functions 04:42 Using error-checking functions: ISERR, ISERROR, IFERROR 06:31 14:29 13. Reference Functions Getting data from remote cells with OFFSET 04:46 Returning references with INDIRECT 09:43 00:41 Conclusion Final thoughts 00:41