در این آموزش تصویری با تسلط بر فرمول ها و توابع پیشرفته در Excel آشنا می شوید. این دوره به معرفی ابزارهای پویا برای کار با فرمول ها و توابع پیشرفته در صفحه گسترده و تجزیه و تحلیل برنامه می پردازد. در ادامه نحوه اضافه کردن مجموعه داده ها از منابع مختلف ، دستکاری تاریخ و زمان و کار با آرایه ها را می آموزید.
این دوره آموزشی محصول موسسه Udemy است.

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

  • کار با فرمول ها در اکسل
  • کار با ابزارهای موجود
  • کار با داده های ثابت
  • کار با توابع
  • قوانین اعتبار سنجی داده ها
  • کار با عملگرهای منطقی
  • کار با دستور شرطی If
  • رفع خطاها با IF ERROR
  • کار با توابع آماری
  • کار با توابع عمومی
  • تابع SUM PRODUCT
  • کار با تابع COUNT / SUM / AVERAGE
  • ساخت یک صفحه عمومی با COUNT و SUM
  • کار با آرایه
  • کار با تابع MATCH
  • کار با تابع OFFSET
  • استفاده از OFFSET برای ایجاد یک نمودار
  • نحوه الحاق تابع
  • کار با تابع RIGHT، LEN
  • نحوه قالب بندی تاریخ
  • کار با تابع EOMONTH
  • ساخت فرمول بر اساس فرمت
  • مدیریت قوانین بر اساس فرمول
  • استفاده از تابع MOD
  • قالب بندی سلول ها بر اساس ارزش
  • استفاده از آرایه ثابت در فرمول
  • نحوه شمارش کاراکتر در سلول
  • و…

عنوان دوره: Udemy Excel for Analysts: Mastering Advanced Formulas And Functions
مدت زمان: 600 ساعت
نویسنده: Chris Dutton

توضیحات:

Learn how to write powerful Excel functions with hands-on, interactive exercises designed by an award-winning instructor
It's time to show Excel who wears the pants in this relationship. Whether you're starting from square one or aspiring to become an absolute Excel badass, you've come to the right place. It's time to stop digging through help forums and shelling out absurd fees for tutors and textbooks -- you deserve better.
This course will give you a deep understanding of the advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. While most Excel courses focus on simply what each formula does, I teach through hands-on, contextual examples designed to showcase why these formulas are awesome and how they can be applied in a number of ways. I will not train you to regurgitate formula syntax; I will teach you how to THINK like Excel.
By the end of the course you'll be writing robust, elegant formulas from scratch, allowing you to:
Easily build dynamic tools & dashboards to filter, display and analyze your data
Go rogue and design your own formula-based formatting rules
Join datasets from multiple sources in seconds
Tap into API data sources like live weather, stock quotes, GPS data, etc.
Manipulate dates, times, text, and arrays
Automate tedious and time-consuming tasks (no VBA required!)
And much, much more...
What gives you the right to teach this class? Can't I just Google this stuff?
Fantastic question. First and foremost, I have a genuine passion for Excel that most people reserve for things like kittens, ice cream, and/or significant others. The only thing I love more than learning Excel is teaching it, and as the founder of Excel Maven I've been lucky enough to teach thousands of people just like you over the past 7+ years. My teaching style is conversational, authentic and to the point, and I will always communicate complex concepts in a framework that is clear and easy to comprehend.
As a full-time analytics consultant and Excel instructor, I cut my teeth using Excel to solve real-world business problems and develop award-winning analytics & data visualization tools for Fortune 500 companies. If you care about creds, I'm a card-carrying MOS Certified Excel Expert and my work has been featured by Microsoft and the New York Times. Ok so I don't actually carry the card, but you get the idea.
What are the requirements?
Microsoft Excel (2007+) installed and ready to roll
Some experience with basic formulas is recommended, but not required (we'll spend some time laying the groundwork for the more advanced stuff)
What am I going to get from this course?
Over 76 lectures and 5.5 hours of content!
60+ downloadable PDF slides and 9 Excel practice files
Hands-on practice writing powerful, complex Excel functions from scratch
A step-by-step, 100% comprehensive guide to the most versatile, powerful functions in Excel
What is the target audience?
Excel users who have basic skills but want to get really, REALLY good
Anyone hoping to expand their analytical skill set, work more efficiently with data, and take their career to a new level
Students looking for an engaging, hands-on, and highly interactive approach to training

Section 1: Before We Dive In
Lecture 1	
Course Structure & Outline
04:12
Section 2: Excel Formulas 101
Lecture 2	
Introduction
01:06
Lecture 3	
The Formula Library & Auditing Tools
04:37
Lecture 4	
Basic Formula Syntax
03:31
Lecture 5	
Fixed, Relative, & Mixed References
04:57
Lecture 6	
Common Errors & the IFERROR statement
07:25
Lecture 7	
Function, CTRL & ALT Shortcuts
06:36
Lecture 8	
Creating Custom Data Validation Rules
03:28
Lecture 9	
Fixed vs. Volatile Functions
03:21
Section 3: Logical Operators
Lecture 10	
Introduction
01:24
Lecture 11	
Anatomy of the IF Statement
04:01
Lecture 12	
Nested IF Statements
04:53
Lecture 13	
AND/OR Operators
08:47
Lecture 14	
NOT vs. "<>"
03:17
Lecture 15	
Fixing Errors with IFERROR
04:03
Lecture 16	
Common IS Statements
04:00
Section 4: Statistical Functions
Lecture 17	
Introduction
01:12
Lecture 18	
Basic Statistical Functions
05:17
Lecture 19	
SMALL/LARGE & RANK/PERCENTRANK
06:19
Lecture 20	
RAND() & RANDBETWEEN
02:12
Lecture 21	
The SUMPRODUCT Function
03:55
Lecture 22	
COUNTIFS/SUMIFS/AVERAGEIFS
04:49
Lecture 23	
PROJECT SHOWCASE: Building a Basic Dashboard with COUNTIFS & SUMIFS
08:09
Section 5: Lookup/Reference Functions
Lecture 24	
Introduction
01:23
Lecture 25	
Working with Named Arrays
03:30
Lecture 26	
ROW/ROWS & COLUMN/COLUMNS
03:18
Lecture 27	
VLOOKUP/HLOOKUP
05:54
Lecture 28	
Joining Data with VLOOKUP
06:23
Lecture 29	
Fixing Errors with IFERROR & VLOOKUP
04:30
Lecture 30	
VLOOKUP Reference Array Options
06:18
Lecture 31	
The INDEX Function
01:59
Lecture 32	
The MATCH Function
02:32
Lecture 33	
Using INDEX & MATCH together
06:08
Lecture 34	
Combining MATCH with VLOOKUP
04:47
Lecture 35	
The OFFSET Function
02:05
Lecture 36	
PROJECT SHOWCASE: Using OFFSET to create a dynamic scrolling chart
09:51
Section 6: Text Functions
Lecture 37	
Introduction
00:57
Lecture 38	
UPPER/LOWER/PROPER & TRIM
03:32
Lecture 39	
The CONCATENATE Function (&)
03:26
Lecture 40	
LEFT/MID/RIGHT & LEN
03:31
Lecture 41	
TEXT/VALUE
04:20
Lecture 42	
SEARCH/FIND
05:04
Lecture 43	
Categorizing Data with IF(ISNUMBER(SEARCH))
05:39
Lecture 44	
Combining RIGHT, LEN, and SEARCH
05:44
Section 7: Date & Time Functions
Lecture 45	
Introduction
02:14
Lecture 46	
DATEVALUE: Your New BFF
05:06
Lecture 47	
Date Formatting & Fill Series
03:39
Lecture 48	
TODAY()/NOW()
02:16
Lecture 49	
YEAR/MONTH/DAY & HOUR/MINUTE/SECOND
03:04
Lecture 50	
The EOMONTH Function
06:17
Lecture 51	
The YEARFRAC Function
02:27
Lecture 52	
WEEKDAY, WORKDAY & NETWORKDAYS
07:24
Lecture 53	
The DATEDIF Function
04:23
Lecture 54	
PROJECT SHOWCASE: Designing a Sample Budget Pacing Tool
11:07
Section 8: Formula-Based Formatting
Lecture 55	
Introduction
01:30
Lecture 56	
Creating, Editing & Managing Formula-Based Rules
04:39
Lecture 57	
Highlighting Every Other Row Using the MOD function
03:50
Lecture 58	
Formatting Cells Based on the Value of Another Cell
03:29
Lecture 59	
Formatting Cells Using Statistical Functions
05:28
Lecture 60	
Formatting Cells Using Text Functions & Logical Operators
05:08
Section 9: Array Formulas
Lecture 61	
Introduction
01:26
Lecture 62	
Rules of Array Functions
03:24
Lecture 63	
Pros & Cons of Array Functions
02:15
Lecture 64	
Vertical, Horizontal, and 2-Dimensional Array Constants
06:26
Lecture 65	
Using Array Constants in Formulas
03:38
Lecture 66	
Named Array Constants
04:31
Lecture 67	
The Transpose Function
04:22
Lecture 68	
Linking Data Between Sheets: Array vs. Non-Array Comparison
02:36
Lecture 69	
Returning the "X" Largest Values in a Range
03:09
Lecture 70	
Counting Characters Across Cells
01:52
Lecture 71	
Creating a "MAX IF" Array Formula
03:09
Section 10: Badass Bonus Functions
Lecture 72	
Introduction
01:41
Lecture 73	
The INDIRECT Function
10:55
Lecture 74	
HYPERLINK: as Awesome as it Sounds
06:19
Lecture 75	
Tapping into Real-Time Data with WEBSERVICE & FILTERXML
07:16
Section 11: Wrapping Up
Lecture 76	
Conclusion & Next Steps
00:41

حجم فایل: 811MB