Lynda Excel 2013 Advanced Formulas and Functions
آموزش Formulas and Functions در اکسل

کاربرد : آموزش Formulas and Functions در اکسل
نوع فایل : فیلم آموزشی
زبان : انگلیسی
سیستم عامل : Windows-Mac-Linux-Android-iOS
تولید کننده : Lynda
سال تولید : 2013

توضیحات

مایکروسافت اکسل (Microsoft Office Excel) دارای خصوصیات اصلی تمام نرم‌افزارهای صفحه‌گسترده است. با استفاده از جدولی متشکل از ردیف‌ها و ستون‌ها می‌توان، داده‌ها و اطلاعات را سازماندهی و همچنین با استفاده از همین داده‌ها، به انجام محاسبات پرداخت و همچنین با استفاده از رسم توابع و نمودارها، به تجزیه و تحلیل آماری اطلاعات پرداخت. این برنامه برای محاسبات ریاضی (با قابلیت انجام محاسبات دشوار ریاضی) و ترسیم نمودار به وسیله‌ی ابزارهای گرافیکی نیز به کار می‌رود. در دوره های آموزشی Excel 2013 شما با قابلیت های اساسی و کلیدی این نرم افزار کاربردی قدرتمند آشنا می شوید.

Description

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.
Topics include:
Displaying and highlighting formulas
Converting formulas to values
Creating 3D formulas to gather data from multiple sheets
Understanding the hierarchy of operations in formulas
Using absolute and relative references
Creating and expanding the use of nested IF statements
Looking up information with VLOOKUP, MATCH, and INDEX
Using the power functions: COUNTIF, SUMIF, and AVERAGEIF
Analyzing data with the statistical functions
Performing basic math
Calculating dates and times
Editing text with functions
Checking for errors with formulas

بخش های فایل

این فایل شامل 147 بخش می باشد که بخش های آن به صورت جداگانه در زیر آمده است.

Row	Title	Size
0.Introduction
1	01 - Welcome	7.61 MB
2	01 - Welcome	1.32 KB
3	02 - Using the exercise files	1.62 MB
4	02 - Using the exercise files	626 Bytes
1.Formula and Function Tips and Shortcuts
5	01 - Displaying and highlighting formulas	18.58 MB
6	01 - Displaying and highlighting formulas	8.11 KB
7	02 - Auditing tools	22.01 MB
8	02 - Auditing tools	6.48 KB
9	03 - Using entire row-column references	17.10 MB
10	03 - Using entire row-column references	5.76 KB
11	04 - Copying column formulas instantly	14.68 MB
12	04 - Copying column formulas instantly	4.64 KB
13	05 - Converting formulas to values with a simple drag	14.54 MB
14	05 - Converting formulas to values with a simple drag	5.33 KB
15	06 - Updating values without formulas	7.66 MB
16	06 - Updating values without formulas	4.16 KB
17	07 - Simplifying debugging formulas	13.25 MB
18	07 - Simplifying debugging formulas	5.87 KB
19	08 - Enhancing readability with range names	22.75 MB
20	08 - Enhancing readability with range names	8.49 KB
21	09 - Creating 3D formulas to gather data from multiple sheets	12.46 MB
22	09 - Creating 3D formulas to gather data from multiple sheets	7.55 KB
2.Formula and Function Tools
23	01 - Understanding the hierarchy of operations in Excel formulas	7.45 MB
24	01 - Understanding the hierarchy of operations in Excel formulas	5.06 KB
25	02 - Using the Formulas tab on the Ribbon for locating functions	8.97 MB
26	02 - Using the Formulas tab on the Ribbon for locating functions	5.34 KB
27	03 - Using the Insert Function button for guidance with unfamiliar functions	10.93 MB
28	03 - Using the Insert Function button for guidance with unfamiliar functions	7.15 KB
29	04 - Using and extending AutoSum button capabilities	17.11 MB
30	04 - Using and extending AutoSum button capabilities	7.74 KB
31	05 - Using absolute and relative references in formulas	10.91 MB
32	05 - Using absolute and relative references in formulas	5.01 KB
33	06 - Using mixed references in formulas	10.56 MB
34	06 - Using mixed references in formulas	6.36 KB
3.IF and Related Functions
35	01 - Exploring IF logical tests and using relational operators	10.39 MB
36	01 - Exploring IF logical tests and using relational operators	5.50 KB
37	02 - Creating and expanding the use of nested IF statements	11.74 MB
38	02 - Creating and expanding the use of nested IF statements	6.57 KB
39	03 - Using the AND and OR functions with IF to create compound logical tests	18.92 MB
40	03 - Using the AND and OR functions with IF to create compound logical tests	12.08 KB
4.Lookup and Reference Functions
41	01 - Looking up information with VLOOKUP and HLOOKUP	15.16 MB
42	01 - Looking up information with VLOOKUP and HLOOKUP	6.14 KB
43	02 - Finding approximate matches with VLOOKUP	23.74 MB
44	02 - Finding approximate matches with VLOOKUP	11.56 KB
45	03 - Finding exact matches with VLOOKUP	14.28 MB
46	03 - Finding exact matches with VLOOKUP	7.34 KB
47	04 - Nesting lookup functions	14.55 MB
48	04 - Nesting lookup functions	6.30 KB
49	05 - Using VLOOKUP with large tables	13.93 MB
50	05 - Using VLOOKUP with large tables	4.90 KB
51	06 - Finding table-like information within a function with CHOOSE	8.30 MB
52	06 - Finding table-like information within a function with CHOOSE	5.28 KB
53	07 - Locating data with MATCH	11.76 MB
54	07 - Locating data with MATCH	7.23 KB
55	08 - Retrieving information by location with INDEX	9.05 MB
56	08 - Retrieving information by location with INDEX	4.08 KB
57	09 - Using MATCH and INDEX together	24.97 MB
58	09 - Using MATCH and INDEX together	10.20 KB
5.Power Functions
59	01 - Tabulating information using a single criterion with COUNTIF, SUMIF, and AVERAGEIF	19.85 MB
60	01 - Tabulating information using a single criterion with COUNTIF, SUMIF, and AVERAGEIF	7.80 KB
61	02 - Tabulating information using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS	17.36 MB
62	02 - Tabulating information using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS	7.38 KB
6.Statistical Functions
63	01 - Finding the middle value with MEDIAN	10.88 MB
64	01 - Finding the middle value with MEDIAN	4.39 KB
65	02 - Ranking data without sorting with RANK	15.58 MB
66	02 - Ranking data without sorting with RANK	6.85 KB
67	03 - Finding the largest and smallest values with LARGE and SMALL	6.49 MB
68	03 - Finding the largest and smallest values with LARGE and SMALL	3.55 KB
69	04 - Tabulating blank cells with COUNTBLANK	23.66 MB
70	04 - Tabulating blank cells with COUNTBLANK	8.78 KB
71	05 - Using COUNT, COUNTA, and the status bar	10.30 MB
72	05 - Using COUNT, COUNTA, and the status bar	5.68 KB
7.Math Functions
73	01 - Working with ROUND, ROUNDUP, and ROUNDDOWN	16.38 MB
74	01 - Working with ROUND, ROUNDUP, and ROUNDDOWN	8.94 KB
75	02 - Working with MROUND, CEILING, and FLOOR for specialized rounding	13.93 MB
76	02 - Working with MROUND, CEILING, and FLOOR for specialized rounding	8.15 KB
77	03 - Using the INT and TRUNC functions to extract integer data	9.88 MB
78	03 - Using the INT and TRUNC functions to extract integer data	4.55 KB
79	04 - Finding the remainder with MOD and using MOD with conditional formatting	14.84 MB
80	04 - Finding the remainder with MOD and using MOD with conditional formatting	8.52 KB
81	05 - Practical uses for the random number functions RAND and RANDBETWEEN	21.66 MB
82	05 - Practical uses for the random number functions RAND and RANDBETWEEN	9.84 KB
83	06 - Converting a value between measurement systems with CONVERT	9.51 MB
84	06 - Converting a value between measurement systems with CONVERT	5.17 KB
85	07 - Using the powerful AGGREGATE function to bypass errors and hidden data	18.86 MB
86	07 - Using the powerful AGGREGATE function to bypass errors and hidden data	7.69 KB
87	08 - Using the ROMAN and ARABIC functions to display different numeral systems	7.93 MB
88	08 - Using the ROMAN and ARABIC functions to display different numeral systems	4.22 KB
8.Date and Time Functions
89	01 - Understanding Excel date-time capabilities in formulas	24.64 MB
90	01 - Understanding Excel date-time capabilities in formulas	13.13 KB
91	02 - Using TODAY and NOW functions for dynamic date-time entry	9.36 MB
92	02 - Using TODAY and NOW functions for dynamic date-time entry	5.61 KB
93	03 - Identifying the day of the week with WEEKDAY	13.16 MB
94	03 - Identifying the day of the week with WEEKDAY	6.17 KB
95	04 - Counting working days with NETWORKDAYS	10.85 MB
96	04 - Counting working days with NETWORKDAYS	5.76 KB
97	05 - Determining a completion date with WORKDAY	9.51 MB
98	05 - Determining a completion date with WORKDAY	5.83 KB
99	06 - Tabulating date differences with DATEDIF	16.35 MB
100	06 - Tabulating date differences with DATEDIF	8.56 KB
101	07 - Calculating end-of-month and future-past dates with EDATE and EOMONTH	16.28 MB
102	07 - Calculating end-of-month and future-past dates with EDATE and EOMONTH	7.38 KB
103	08 - Converting text entries into dates and times with DATEVALUE and TIMEVALUE	11.78 MB
104	08 - Converting text entries into dates and times with DATEVALUE and TIMEVALUE	7.25 KB
9.Array Formulas and Functions
105	01 - Extending formula capabilities with arrays	36.54 MB
106	01 - Extending formula capabilities with arrays	13.12 KB
107	02 - Counting unique entries in a range with an array formula	22.57 MB
108	02 - Counting unique entries in a range with an array formula	9.07 KB
109	03 - Determining frequency distributions with FREQUENCY	12.05 MB
110	03 - Determining frequency distributions with FREQUENCY	5.82 KB
111	04 - Flipping row-column orientation with TRANSPOSE	10.48 MB
112	04 - Flipping row-column orientation with TRANSPOSE	5.25 KB
113	05 - Building analysis via regression techniques with TREND and GROWTH	12.21 MB
114	05 - Building analysis via regression techniques with TREND and GROWTH	3.46 KB
115	06 - Using array formula techniques with the MATCH function for complex lookups	14.37 MB
116	06 - Using array formula techniques with the MATCH function for complex lookups	6.69 KB
10.Reference Functions
117	01 - Getting data from remote cells with OFFSET	16.37 MB
118	01 - Getting data from remote cells with OFFSET	9.56 KB
119	02 - Returning references with INDIRECT	10.88 MB
120	02 - Returning references with INDIRECT	5.77 KB
121	03 - Using INDIRECT with data validation for two-tiered pick list scenarios	24.88 MB
122	03 - Using INDIRECT with data validation for two-tiered pick list scenarios	10.29 KB
11.Text Functions
123	01 - Locating and extracting data with FIND, SEARCH, and MID	15.44 MB
124	01 - Locating and extracting data with FIND, SEARCH, and MID	8.16 KB
125	02 - Extracting specific data with LEFT and RIGHT	8.81 MB
126	02 - Extracting specific data with LEFT and RIGHT	3.98 KB
127	03 - Removing extra spaces with TRIM and removing hidden characters with CLEAN	13.36 MB
128	03 - Removing extra spaces with TRIM and removing hidden characters with CLEAN	8.22 KB
129	04 - Using ampersands and CONCATENATE to combine data from different cells	14.68 MB
130	04 - Using ampersands and CONCATENATE to combine data from different cells	8.69 KB
131	05 - Adjusting the case within cells with PROPER, UPPER, and LOWER	10.88 MB
132	05 - Adjusting the case within cells with PROPER, UPPER, and LOWER	5.16 KB
133	06 - Adjusting character content with REPLACE and SUBSTITUTE	17.10 MB
134	06 - Adjusting character content with REPLACE and SUBSTITUTE	8.82 KB
135	07 - Using other utility text functions- LEN, REPT, VALUE, TEXT	19.48 MB
136	07 - Using other utility text functions- LEN, REPT, VALUE, TEXT	10.89 KB
12.Information Functions
137	01 - Extracting information with the CELL and INFO functions	13.88 MB
138	01 - Extracting information with the CELL and INFO functions	8.69 KB
139	02 - Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER	13.00 MB
140	02 - Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER	8.05 KB
141	03 - Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA	18.52 MB
142	03 - Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA	9.43 KB
143	04 - Using the ISFORMULA function with conditional formatting	7.13 MB
144	04 - Using the ISFORMULA function with conditional formatting	3.66 KB
13.Conclusion
145	01 - Goodbye	982.13 KB
146	01 - Goodbye	696 Bytes
14. فایل های تمرینی
147	Ex_Files_EX2013_AdvForm	1.96 MB

شما می توانید مجموعه کامل بخش های این فابل را نیز به صورت مجموعه کامل دریافت نمایید.

اگر شما نسبت به این اثر یا عنوان محق هستید، لطفا از طریق "بخش تماس با ما" با ما تماس بگیرید و برای اطلاعات بیشتر، صفحه قوانین و مقررات را مطالعه نمایید.

دیدگاه کاربران

لطفا در این قسمت فقط نظر شخصی در مورد این عنوان را وارد نمایید و در صورتیکه مشکلی با دانلود یا استفاده از این فایل دارید در صفحه کاربری تیکت ثبت کنید.

(اختیاری)

تعداد کاراکتر مجاز: 1000