دوره رایگان "Excel 2013 Advanced Formulas and Functions" از مجموعه آموزشی Lynda منتشر شد.
مشخصات دوره :
نام دوره : Excel 2013 Advanced Formulas and Functions
گروه اصلی : دوره های آموزشی Lynda
مدت زمان دوره : 5 ساعت و 26 دقیقه
قیمت دوره : رایگان
حداکثر زمان استفاده از دوره : نا محدود
سرفصل مطالب دوره :
00 - Introduction
01 - Welcome
02 - Using the exercise files
01 - Formula and Function Tips and Shortcuts
01 - Displaying and highlighting formulas
02 - Auditing tools
03 - Using entire row column references
04 - Copying column formulas instantly
05 - Converting formulas to values with a simple drag
06 - Updating values without formulas
07 - Simplifying debugging formulas
08 - Enhancing readability with range names
09 - Creating 3D formulas to gather data from multiple sheets
02 - Formula and Function Tools
01 - Understanding the hierarchy of operations in Excel formulas
02 - Using the Formulas tab on the Ribbon for locating functions
03 - Using the Insert Function button for guidance with unfamiliar functions
04 - Using and extending AutoSum button capabilities
05 - Using absolute and relative references in formulas
06 - Using mixed references in formulas
03 - IF and Related Functions
01 - Exploring IF logical tests and using relational operators
02 - Creating and expanding the use of nested IF statements
03 - Using the AND and OR functions with IF to create compound logical tests
04 - Lookup and Reference Functions
01 - Looking up information with VLOOKUP and HLOOKUP
02 - Finding approximate matches with VLOOKUP
03 - Finding exact matches with VLOOKUP
04 - Nesting lookup functions
05 - Using VLOOKUP with large tables
06 - Finding table-like information within a function with CHOOSE
07 - Locating data with MATCH
08 - Retrieving information by location with INDEX
09 - Using MATCH and INDEX together
05 - Power Functions
01 - Tabulating information using a single criterion with COUNTIF, SUMIF, and AVERAGEIF
02 - Tabulating information using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS
06 - Statistical Functions
01 - Finding the middle value with MEDIAN
02 - Ranking data without sorting with RANK
03 - Finding the largest and smallest values with LARGE and SMALL
04 - Tabulating blank cells with COUNTBLANK
05 - Using COUNT, COUNTA, and the status bar
07 - Math Functions
01 - Working with ROUND, ROUNDUP, and ROUNDDOWN
02 - Working with MROUND, CEILING, and FLOOR for specialized rounding
03 - Using the INT and TRUNC functions to extract integer data
04 - Finding the remainder with MOD and using MOD with conditional formatting
05 - Practical uses for the random number functions RAND and RANDBETWEEN
06 - Converting a value between measurement systems with CONVERT
07 - Using the powerful AGGREGATE function to bypass errors and hidden data
08 - Using the ROMAN and ARABIC functions to display different numeral systems
08 - Date and Time Functions
01 - Understanding Excel datetime capabilities in formulas
02 - Using TODAY and NOW functions for dynamic datetime entry
03 - Identifying the day of the week with WEEKDAY
04 - Counting working days with NETWORKDAYS
05 - Determining a completion date with WORKDAY
06 - Tabulating date differences with DATEDIF
07 - Calculating end-of-month and futurepast dates with EDATE and EOMONTH
08 - Converting text entries into dates and times with DATEVALUE and TIMEVALUE
09 - Array Formulas and Functions
01 - Extending formula capabilities with arrays
02 - Counting unique entries in a range with an array formula
03 - Determining frequency distributions with FREQUENCY
04 - Flipping row column orientation with TRANSPOSE
05 - Building analysis via regression techniques with TREND and GROWTH
06 - Using array formula techniques with the MATCH function for complex lookups
10 - Reference Functions
01 - Getting data from remote cells with OFFSET
02 - Returning references with INDIRECT
03 - Using INDIRECT with data validation for two-tiered pick list scenarios
11 - Text Functions
01 - Locating and extracting data with FIND, SEARCH, and MID
02 - Extracting specific data with LEFT and RIGHT
03 - Removing extra spaces with TRIM and removing hidden characters with CLEAN
04 - Using ampersands and CONCATENATE to combine data from different cells
05 - Adjusting the case within cells with PROPER, UPPER, and LOWER
06 - Adjusting character content with REPLACE and SUBSTITUTE
07 - Using other utility text functions LEN, REPT, VALUE, TEXT
12 - Information Functions
01 - Extracting information with the CELL and INFO functions
02 - Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER
03 - Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA
04 - Using the ISFORMULA function with conditional formatting
13 - Conclusion
01 - Goodbye
