Lynda_Excel_2016_Advanced_Formulas_and_Functions

در این آموزش تصویری متخصص اکسل Dennis Taylor برخی از مفیدترین فرمول ها و توابع Excel 2016 را توضیح می دهد و نشان می دهد چگونه از این فرمول ها و توابع در برنامه ها استفاده می شود. مدرس این دوره ابتدا از توابع ساده مانند بزرگترین و کوچکترین و میانگین شروع می کند و سپس به سراغ فرمولها و توابع بسیار پیچیده مثل آرایه ها ، کار با توابع تاریخ و زمان ، داده های متنی و … می رود.

این دوره آموزشی محصول موسسه Lynda است.

سرفصل های این دوره:

  • راهنمایی در مورد فرمول و تابع و کلید های میانبر
  • نمایش و برجسته کردن فرمول
  • استفاده از ابزار حسابرسی
  • استفاده از کل سطر و ستون مراجع
  • تبدیل فرمول به ارزش ها
  • به روز رسانی ارزش بدون فرمول
  • اشکال زدایی فرمول با کلید F9
  • افزایش خوانایی
  • جدول بندی داده ها از شیت های متعدد
  • فرمول و ابزارهای تابع
  • سلسله مراتب عملیات در فرمول
  • درج تابع برای یادگیری در مورد توابع نا آشنا
  • گسترش قابلیت های AutoSum
  • مراجع مطلق و نسبی
  • استفاده از منابع متفاوتی در فرمول
  • استفاده از autocalculate در نوار وضعیت
  • AND ، OR، و NOT توابع با IF
  • و …

عنوان دوره: Lynda Excel 2016 Advanced Formulas and Functions

مدت دوره:5 ساعت و 59 دقیقه

نویسنده: Dennis Taylor

توضیحات:

Lynda Excel 2016 Advanced Formulas and Functions

Dennis Taylor
Advanced
5h 59m

Excel expert Dennis Taylor demystifies some of the most useful of the 450+ formulas and functions in Excel and shows how to put them to their best use. Dennis starts with a review of the more basic functions (SUM, AVERAGE, and MAX), and a few critical keyboard shortcuts that will let you locate and display formula cells and accelerate working with Excel formulas'even on multiple sheets. He then covers how to find and retrieve data with the VLOOKUP and INDEX functions, calculate totals with counting and statistical functions, extract data with text functions, and work with date, time, array, math and information functions. The course focuses on practical examples that will help viewers easily transition to using Excel's most powerful formulas and functions in real-world scenarios.
Topics include:
Displaying and highlighting formulas
Converting formulas to values
Tabulating data from multiple sheets
Understanding the hierarchy of operations in formulas
Using absolute and relative references
Creating and expanding nested IF statements
Looking up information with VLOOKUP, MATCH, and INDEX
Using the powerful COUNTIF family of functions
Analyzing data with statistical functions
Calculating dates and times
Analyzing data with array formulas and functions
Extracting data with text function

Introduction
1m 15s
Welcome
54s
Using the exercise files
21s
1. Formula and Function Tips and Shortcuts
43m 57s
Displaying and highlighting formulas
4m 43s
Using Auditing tools
6m 15s
Using entire row and column references
4m 4s
Copying column formulas instantly
2m 45s
Converting formulas to values with a drag
4m 59s
Updating values without formulas
3m 26s
Debugging formulas with the F9 key
4m 2s
Enhancing readability with range names
5m 23s
Tabulating data from multiple sheets
8m 20s
2. Formula and Function Tools
40m 21s
Hierarchy of operations in formulas
6m 28s
Formulas tab for locating functions
5m 34s
Insert Function for learning about unfamiliar functions
5m 20s
Extending the capabilities of AutoSum
6m 10s
Absolute and relative references
5m 4s
Using mixed references in formulas
6m 0s
Using autocalculate in the status bar
5m 45s
3. IF and Related Functions
17m 59s
IF logical tests
4m 27s
Expanding nested IF statements
4m 30s
AND, OR, and NOT functions with IF
9m 2s
4. Lookup and Reference Functions
39m 23s
Looking up information with VLOOKUP and HLOOKUP
4m 30s
Finding approximate matches with VLOOKUP and HLOOKUP
7m 16s
Finding exact matches with VLOOKUP
5m 30s
Nesting lookup functions
4m 12s
Using VLOOKUP with large tables
4m 21s
Finding table-like information within a function using the CHOOSE function
2m 44s
Locating data with the MATCH function
3m 43s
Retrieving information by location
2m 53s
Using MATCH and INDEX functions together
4m 14s
5. Power Functions
14m 40s
Tabulating data using a single criterion
4m 33s
Tabulating data using multiple criteria
5m 19s
Preventing double counting
4m 48s
6. Statistical Functions
17m 17s
Finding the middle value with MEDIAN and most common value with MODE
2m 21s
Ranking data without sorting with RANK and RANK.EQ
4m 8s
Finding the largest and smallest values with the LARGE and SMALL functions
1m 47s
Tabulating blank cells with the COUNTBLANK function
5m 29s
Using COUNT, COUNTA, and the status bar
3m 32s
7. Math Functions
36m 46s
Working with the ROUND, ROUNDUP, and ROUNDDOWN functions
6m 13s
Working with MROUND, CEILING, and FLOOR for specialized rounding
3m 20s
Using the INT and TRUNC functions to extract integer data
3m 58s
Finding the remainder with MOD and using MOD with conditional formatting
5m 26s
Exploring practical uses for the RAND and RANDBETWEEN functions
4m 57s
Converting a value between measurement systems with CONVERT
2m 55s
Using the powerful AGGREGATE function to bypass errors and hidden data
6m 32s
Using the ROMAN and ARABIC functions to display different number systems
3m 25s
8. Date and Time Functions
46m 14s
Understanding Excel date and time capabilities in formulas
9m 52s
Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND functions
4m 4s
Using the TODAY and NOW functions for dynamic date and time entry
6m 49s
Identifying the day of the week with the WEEKDAY function
4m 40s
Counting working days with the NETWORKDAYS function
5m 26s
Determining a completion date with the WORKDAY function
4m 10s
Tabulating date differences with the DATEDIF function
6m 15s
Calculating end-of-month and future and past dates with EDATE and EOMONTH
4m 58s
9. Array Formulas and Functions
28m 30s
Extending formula capabilities with array formulas
5m 7s
Counting unique entries in a range with an array formula
5m 51s
Determining frequency distributions with the FREQUENCY function
3m 53s
Flipping row and column orientation with TRANSPOSE
5m 10s
Building analysis via regression techniques with TREND and GROWTH
2m 59s
Using array formulas and the MATCH function for complex lookups
5m 30s
10. Reference Functions
16m 31s
Getting data from remote cells with the OFFSET function
5m 36s
Returning references with the INDIRECT function
3m 11s
Using INDIRECT with Data Validation for two-tiered pick list scenarios
7m 44s
11. Text Functions
35m 52s
Locating and extracting data with the FIND, SEARCH, and MID functions
6m 20s
Extracting specific data with the LEFT and RIGHT functions
3m 22s
Using the TRIM function to remove unwanted spaces in a cell
5m 43s
Using ampersands and CONCATENATE to combine data from different cells
5m 51s
Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions
3m 48s
Adjusting character content with the REPLACE and SUBSTITUTE functions
5m 41s
Using the utility text functions: TEXT, REPT, and LEN
5m 7s
12. Information Functions
20m 14s
Extracting information with the CELL and INFO functions
4m 10s
Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
5m 9s
Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions
7m 53s
Tracking and highlighting formula cells with the ISFORMULA function
3m 2s
Conclusion
33s
Next steps
33s