Excel 2013 Tutorials Advanced Formulas and Functions Год выпуска: 2013 Производитель: Lynda Сайт производителя: www.lynda.com/Excel-tutorials/Excel-2013-Advanced-Formulas-Functions/126129-2.html Автор: Dennis Taylor Продолжительность: 5h 26m Тип раздаваемого материала: Видеоурок Язык: Английский Описание: Conquer some of the most daunting features in Microsoft Excel once and for all. In this 2013 update to his popular series, author Dennis Taylor demystifies some of the most challenging of the 300+ formulas and functions in Excel and shows how to put them to their best use. The course starts with a review of the more basic, building-block functions, and a few critical keyboard shortcuts that will speed up working with Excel data, even on multiple sheets. Dennis then covers how to perform advanced searching and data retrieval with Lookup functions, tabulate and sort data with counting and statistical functions, format data with text and math functions, and work with financial data using advanced formulas. Dennis focuses on practical examples that transition effortlessly to real-world scenarios.-Покорите некоторые из самых сложных функций в Microsoft Excel раз и навсегда. В этом 2013 обновление для его популярных серий, автор Деннис Тейлор проясняет некоторые аспекты оценки эффективности некоторых из наиболее сложных из 300 формулы и функции в Excel и показано, как поместить их оптимального использования. Курс начинается с обзора более фундаментальных функций и несколько критических комбинации клавиш на клавиатуре, что скорость работы с Excel данных даже на нескольких листах. Деннис затем охватывает как для выполнения расширенного поиска и извлечения данных с помощью функций поиска, заболоченной местности и сортировки данных с подсчетом голосов и статистические функции, формат данных с помощью текста и математических функций и работы с финансовыми данными с помощью расширенных формул. Деннис делает акцент на практических примеров перехода без особых усилий в реальном мире сценариев.
Содержание
Introduction 1m 23s Welcome 58s Using the exercise files 25s 1. Formula and Function Tips and Shortcuts 37m 48s Displaying and highlighting formulas 5m 10s Auditing tools 4m 38s Using entire row/column references 3m 43s Copying column formulas instantly 3m 11s Converting formulas to values with a simple drag 3m 32s Updating values without formulas 2m 55s Simplifying debugging formulas 3m 51s Enhancing readability with range names 5m 50s Creating 3D formulas to gather data from multiple sheets 4m 58s 2. Formula and Function Tools 24m 12s Understanding the hierarchy of operations in Excel formulas 3m 19s Using the Formulas tab on the Ribbon for locating functions 3m 14s Using the Insert Function button for guidance with unfamiliar functions 4m 36s Using and extending AutoSum button capabilities 5m 6s Using absolute and relative references in formulas 3m 30s Using mixed references in formulas 4m 27s 3. IF and Related Functions 15m 42s Exploring IF logical tests and using relational operators 3m 33s Creating and expanding the use of nested IF statements 4m 22s Using the AND and OR functions with IF to create compound logical tests 7m 47s 4. Lookup and Reference Functions 41m 58s Looking up information with VLOOKUP and HLOOKUP 3m 57s Finding approximate matches with VLOOKUP 7m 27s Finding exact matches with VLOOKUP 4m 47s Nesting lookup functions 4m 11s Using VLOOKUP with large tables 3m 23s Finding table-like information within a function with CHOOSE 3m 34s Locating data with MATCH 4m 35s Retrieving information by location with INDEX 2m 46s Using MATCH and INDEX together 7m 18s 5. Power Functions 10m 22s Tabulating information using a single criterion with COUNTIF, SUMIF, and AVERAGEIF 5m 9s Tabulating information using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS 5m 13s 6. Statistical Functions 18m 57s Finding the middle value with MEDIAN 2m 46s Ranking data without sorting with RANK 4m 24s Finding the largest and smallest values with LARGE and SMALL 2m 19s Tabulating blank cells with COUNTBLANK 5m 43s Using COUNT, COUNTA, and the status bar 3m 45s 7. Math Functions 36m 59s Working with ROUND, ROUNDUP, and ROUNDDOWN 6m 5s Working with MROUND, CEILING, and FLOOR for specialized rounding 5m 21s Using the INT and TRUNC functions to extract integer data 3m 2s Finding the remainder with MOD and using MOD with conditional formatting 5m 26s Practical uses for the random number functions RAND and RANDBETWEEN 6m 8s Converting a value between measurement systems with CONVERT 3m 17s Using the powerful AGGREGATE function to bypass errors and hidden data 4m 55s Using the ROMAN and ARABIC functions to display different numeral systems 2m 45s 8. Date and Time Functions 39m 44s Understanding Excel date/time capabilities in formulas 9m 2s Using TODAY and NOW functions for dynamic date/time entry 3m 55s Identifying the day of the week with WEEKDAY 4m 8s Counting working days with NETWORKDAYS 3m 32s Determining a completion date with WORKDAY 3m 38s Tabulating date differences with DATEDIF 5m 43s Calculating end-of-month and future/past dates with EDATE and EOMONTH 5m 0s Converting text entries into dates and times with DATEVALUE and TIMEVALUE 4m 46s 9. Array Formulas and Functions 28m 34s Extending formula capabilities with arrays 8m 27s Counting unique entries in a range with an array formula 5m 46s Determining frequency distributions with FREQUENCY 3m 54s Flipping row/column orientation with TRANSPOSE 3m 27s Building analysis via regression techniques with TREND and GROWTH 2m 31s Using array formula techniques with the MATCH function for complex lookups 4m 29s 10. Reference Functions 16m 38s Getting data from remote cells with OFFSET 6m 16s Returning references with INDIRECT 3m 50s Using INDIRECT with data validation for two-tiered pick list scenarios 6m 32s 11. Text Functions 34m 3s Locating and extracting data with FIND, SEARCH, and MID 5m 4s Extracting specific data with LEFT and RIGHT 2m 29s Removing extra spaces with TRIM and removing hidden characters with CLEAN 5m 5s Using ampersands and CONCATENATE to combine data from different cells 5m 38s Adjusting the case within cells with PROPER, UPPER, and LOWER 3m 6s Adjusting character content with REPLACE and SUBSTITUTE 5m 56s Using other utility text functions: LEN, REPT, VALUE, TEXT 6m 45s 12. Information Functions 19m 55s Extracting information with the CELL and INFO functions 5m 47s Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER 5m 20s Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA 6m 18s Using the ISFORMULA function with conditional formatting 2m 30s Conclusion 26s Goodbye 26s
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
![[Quote]](./templates/default/images/lang/en/icon_quote.gif "Reply with quote"){kind=icon}
![[Profile]](./templates/default/images/lang/en/icon_profile.gif "View user's profile"){kind=icon}
![[PM]](./templates/default/images/lang/en/icon_pm.gif "Send private message"){kind=icon}