Udemy - Microsoft Excel - Advanced Excel Formulas & Functions
| این دورهٔ آموزشی ارائه شده، دورهای جدید، کامل و آپدیتشده است |
| در پلیر موجود در این صفحه، میتوانید ویدئوی مقدمه و آشنایی با آموزشهای این دوره را با زیرنویس انگلیسی بهصورت آنلاین تماشا کنید |
توجه داشته باشید که این آموزش تصویری ارائه شده یکی از برترین، پُرامتیازترین، پرفروشترین و تأثیرگذارترین آموزشهای کمپانی Udemy است و در سایت اصلی خود یعنی یودمی، تنها با صرف هزینهٔ بسیار زیاد قابل خریداری خواهد بود.
در این دورۀ آموزشی تصویری و ویدئویی (بهصورت فیلمهای جداگانه) تمامی اصول و مهارتهای پیشرفته و مهم مربوط به فرمولها (Formulas) و توابع (Functions) در نرمافزار اکسل (Microsoft Excel) تدریس شدهاند. سطح این دوره، پیشرفته یا همان Advanced است و البته برای کاربران سطح متوسط نیز میتواند مناسب باشد.
تمامی فایلهای زیرنویس انگلیسی (و زیرنویسهای دیگر از زبانهای مهم دنیا) بهطور کامل به همراه فایلهای ویدئویی ارائه شدهاند و نیازی به دانلود جداگانهٔ آنها ندارید.
تولید کننده: Udemy
سطح: مناسب مناسب برای سطوح متوسط و پیشرفته
تاریخ آخرین آپدیت: 2020/6
مدت زمان آموزش: 08:04:41
زبان آموزش: انگلیسی
زیرنویس انگلیسی: دارد - به همراه چندین زیرنویس از زبانهای مختلف دیگر بهطور کامل
فرمت فایلهای ویدئویی: MP4
مدرس: Maven Analytics, Chris Dutton
فهرست کامل سرفصلها و عناوین آموزشی به همراه زمان دقیق آنها:
Course Content | 105 Lectures | 08:04:41
_________________________________________________
Getting Started | 07:25
Course Structure & Outline - 01:28
READ ME: Important Notes for New Students - 01:54
DOWNLOAD: Course Resources - 00:27
Setting Expectations - 03:36
Excel Formulas 101 | 01:21:38
Introduction: Excel Formulas 101 - 01:48
Excel Formula Syntax - 11:03
Writing Efficient Formulas with Fixed & Relative References - 08:42
Common Excel Error Types - 08:47
Formula Auditing: Trace Precedents & Dependents - 10:15
Formula Auditing: Evaluate Formula & Error Checking (PC Only) - 07:48
Navigating Excel Worksheets with Ctrl Shortcuts - 07:30
Saving Time with Function Shortcuts - 09:44
Accessing Tools with Alt Key Tips - 04:17
BONUS: Mac Shortcuts - 00:00
PRO TIP: Creating Drop-Down Menus with Data Validation - 06:12
Congrats, You're a Developer! - 05:07
Excel Formulas 101 - 6 questions
HOMEWORK: Excel Formulas 101 - 00:25
Conditional Statements & Logical Operators | 30:56
Introduction - 01:11
Anatomy of the IF Statement - 04:01
Nesting Multiple IF Statements - 04:53
Adding Conditional AND/OR Operators - 08:47
Using NOT & "<>" Conditionals - 03:17
Fixing Errors with IFERROR - 04:03
Common IS Statements - 04:00
Excel Logical Operators - 5 questions
HOMEWORK: Excel Logical Operators - 00:44
Common Excel Statistical Functions | 01:01:21
Introduction - 01:09
Basic Excel Statistical Functions - 05:17
Extracting Values with SMALL/LARGE & RANK/PERCENTRANK - 06:15
Randomization with RAND() & RANDBETWEEN - 02:12
Row-Level Calculation with SUMPRODUCT - 03:55
DEMO: Calculating Shipping Costs with SUMPRODUCT - 04:57
Conditional Aggregation with COUNTIFS, SUMIFS & AVERAGEIFS - 04:49
DEMO: Building a Basic Dashboard with COUNTIFS & SUMIFS - 08:09
DEMO: Counting Duplicates with COUNTIF & SUMPRODUCT - 07:57
PRO TIP: Data Profiling with COUNT & SUMPRODUCT - 16:06
Excel Stats Functions - 5 questions
HOMEWORK: Excel Stats Functions - 00:35
Lookup & Reference Functions | 01:48:35
Introduction - 01:18
Working with Named Ranges - 03:30
Counting Rows & Columns with ROW/ROWS & COLUMN/COLUMNS - 03:18
Introduction to VLOOKUP/HLOOKUP - 05:53
Joining Data with VLOOKUP - 06:23
Fixing Errors with IFERROR & VLOOKUP - 04:30
VLOOKUP Reference Array Options - 06:18
Approximate Match Lookups - 05:05
Navigating Cell Ranges with INDEX - 01:59
Matching Text & Values with MATCH - 02:32
Combining INDEX & MATCH to Dynamically Search Ranges - 06:08
Combining MATCH & VLOOKUP for More Flexible Lookups - 04:47
TROUBLESHOOTING: VLOOKUP with Duplicate Keys - 05:05
SNEAK PEEK: XLOOKUP (Office 365 Only) - 04:29
DEMO: Building Dashboards with XLOOKUP (Office 365 Only) - 18:16
Selecting List Items with CHOOSE - 13:15
Defining Ranges with OFFSET - 02:05
Combining OFFSET with COUNTA to Create a Flexible Range - 02:58
PRO TIP: Using OFFSET to Create Interactive Charts - 09:51
Excel Lookup & Reference Functions - 5 questions
HOMEWORK: Excel Lookup & Reference Functions - 00:55
Text Functions | 35:26
Introduction - 00:50
Capitalization with UPPER, LOWER, PROPER & TRIM - 03:32
Combining Text with CONCATENATE (&)- 03:26
Extracting Strings with LEFT, MID, RIGHT & LEN - 03:31
Converting Text to Values with TEXT & VALUE - 04:20
Searching Text String with SEARCH & FIND - 05:04
Categorizing Data with IF(ISNUMBER(SEARCH)) - 05:39
Combining RIGHT, LEN, and SEARCH - 05:44
Replacing Text with SUBSTITUTE - 02:21
Excel Text Functions - 5 questions
HOMEWORK: Excel Text Functions - 00:59
Date & Time Functions | 48:24
Introduction - 02:08
Understanding Excel Date Syntax with DATEVALUE - 05:06
Formatting Dates & Filling Date Series - 03:39
Creating Real-Time Functions with TODAY & NOW - 02:16
Extracting Time Periods with YEAR, MONTH, DAY, HOUR, MINUTE & SECOND - 03:04
Calculating the Month Start or End with EOMONTH - 06:17
Calculating % of Year with YEARFRAC - 02:27
Defining Time Periods with WEEKDAY, WORKDAY & NETWORKDAYS - 07:22
Calculating Differences Between Dates with DATEDIF (Legacy Function) - 04:23
DEMO: Building a Simple Budget Pacing Tool - 11:07
Excel Date & Time Functions - 5 questions
HOMEWORK: Excel Date & Time Functions - 00:35
Formula-Based Formatting | 24:34
Introduction - 01:26
Creating, Editing & Managing Formula-Based Rules - 04:39
Highlighting Rows Using the MOD Function - 03:50
Formatting Based on the Value of Another Cell - 03:29
Formatting Cells Using Stats Functions - 05:28
Formatting Cells Using Text Functions & Logical Operators - 05:08
Excel Formula-Based Formatting - 5 questions
HOMEWORK: Excel Formula-Based Formatting - 00:34
Array Formulas | 50:11
Introduction - 01:24
READ ME: Array Formulas are Changing! - 00:33
Rules of Excel Array Functions - 03:24
Pros & Cons of Array Functions - 02:15
Defining Vertical, Horizontal, and 2-Dimensional Arrays - 06:26
Using Array Constants in Formulas - 03:38
Defining & Referencing Named Array Constants - 04:31
Restructuring Data with TRANSPOSE - 04:22
Linking Data Between Sheets (Array vs. Non-Array Comparison) - 02:36
Returning the Largest Values in a Range - 03:09
Counting Characters Across Cells - 01:52
Creating a "MAX IF" Array Formula - 03:09
Creating a "MAX IF" Array Formula with Multiple Criteria - 06:54
Converting Boolean Values Using the Double Unary ("--")- 03:49
Excel Array Formulas - 5 questions
HOMEWORK: Excel Array Functions - 02:09
Extra Bonus Functions | 35:22
Introduction - 01:38
Creating Custom References with INDIRECT - 10:55
Linking to Worksheet Locations with HYPERLINK - 06:19
Real-Time Weather Conditions with WEBSERVICE & FILTERXML (Part 1) - 07:16
Real-Time Weather Conditions with WEBSERVICE & FILTERXML (Part 2) - 09:14
Bonus Excel Functions - 3 questions
Wrapping Up | 00:47
More from Maven Analytics - 00:47