|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/excel-mis-analytics-training-series3/
课程评论：没有评论
Coursera 课程“Excel 从入门到精通（二）”（21小时） 内容摘要 本课程是 Excel 基础进阶的第二部分，适合已掌握 VLOOKUP、MATCH 等基本函数并希望深入学习 Excel 高级功能的学员。课程将带领学员进入 Excel 高级函数的全新境界，不仅讲解单个函数，更侧重于函数组合在实际 Excel 工作中的应用。通过一系列有趣的迷你练习，学员将全面掌握高级 Excel 的奥秘。 **课程亮点：** * **INDIRECT 和 ADDRESS 函数的深度探索：** * 学习 INDIRECT 函数的核心概念及其在结构化数据链接中的强大应用，被誉为最动态和最有力的函数之一。 * 掌握 ADDRESS 函数的用法。 * 深入理解 INDIRECT 与 ADDRESS 函数结合使用所带来的“mind blowing”效果。 * 学习如何运用 INDIRECT 函数解决复杂数据问题，如数据错位，并将其应用于仪表板（Dashboard）制作。 * **NAME MANAGER 的应用：** * 了解 NAME MANAGER 的概念和创建方法。 * 学习 NAME MANAGER 与 INDIRECT 函数的协同作用。 * **动态下拉菜单的实现：** * 学会创建简单的下拉菜单。 * 掌握如何利用 INDIRECT 和 NAME MANAGER 创建强大且动态的下拉菜单。 * 学习如何实现下拉菜单之间的联动，制作动态下拉列表，并将其应用于仪表板。 * **COUNT 和 SUM 系列函数精讲：** * 全面学习 COUNT、COUNTA、COUNTBLANK、COUNTIF、COUNTIFS、SUMIF、SUMIFS、MAXIFS 等函数。 * 通过解决实际 Excel 问题，展示这些函数的组合应用，例如 VLOOKUP 与 SUMIF 或 COUNTIF 的结合，提供纯粹的实战场景。 * 深入讲解通配符（* 和 ?）在 COUNT 和 SUM 函数中的应用，揭示其“unbelievable magic”。 * **高级筛选（Advance Filter）的全面解析：** * 从基础的筛选（Filters）和排序（Sort）功能讲起，包括按值、颜色、图标筛选，以及按颜色、列、行、值排序。 * 深入探讨高级筛选的概念及其在 Excel 中的重要性。 * 分析高级筛选与普通筛选在目标和功能上的区别。 * 学习如何使用高级筛选提取满足特定条件的数据，包括获取唯一记录。 * 理解“Filter in place”（原地筛选）和“copy”（复制结果）在高级筛选中的作用。 * 掌握如何创建 AND 条件（多表头筛选）和 OR 条件。 * 学习在提取唯一记录时需要遵循的规则。 * 探究如何在高级筛选中使用通配符（* 和 ?）实现逻辑判断。 * 学习使用公式提取复杂数据点。 * **条件格式（Conditional Formatting）的精通：** * 全面学习条件格式的基础和高级应用。 * 掌握如何根据单元格值设置颜色。 * 学习如何使用公式设置单元格颜色。 * 了解如何通过条件格式插入图标。 * 学会如何突出显示重复或唯一值。 * 学习如何突出显示重复出现超过指定次数（如2次或任何 n 次）的值。 * 掌握条件格式中的所有选项。 * 学习如何设置条件格式的优先级。 * **日期和时间函数详解：** * 理解 Excel 中日期的存储方式，以及了解日期的真实数据类型（文本、数字或何种类型）的重要性。 * 学习如何进行日期的加减运算，以及其背后的原因。 * 探讨日期函数的使用是否存在限制，以及 Excel 的日期起止点。 * 学习 TODAY、NOW 函数，以及插入日期和时间的相关快捷键。 * 掌握计算两个日期之间工作日（排除周末或指定天数）的方法。 * 学习计算两个日期之间排除周末和节假日的总天数。 * 深入学习 DATEDIF 函数的用法。 * 学习如何将日期和时间格式化为文本，并掌握 HOUR、SECOND、MINUTE 等函数。 * 深入研究时间格式和数值的内在联系，以及时间和时间格式相加的原理和注意事项。 * **数组（Arrays）概念入门：** * 介绍数组（Arrays）的概念以及我们学习它们的必要性。 * 讲解数组的工作原理和背后的科学原理。 * 通过多个精彩的示例，帮助学员理解数组的应用。 **学习支持：** 课程提供练习题，以帮助学员巩固学习和监控进度。讲师乐于解答学员的疑问。
This Part-2 you should watch if you are good with V-lookup ,Match and other basic formulas of excel. You may check Part1 before watching this.This tutorial will take you to new level as we are discussing so many advance excel functions.As always, not just basic functions but their combinations as well - how it works in real excel life. You will then see some mini exercises as well that are going to make you crazy about advance Excel.Introduction to INDIRECT Function, Use of indirect in real life. It is considered the most dynamic and powerful function when it comes to linking the data in a structured manner.Introduction to ADDRESS function. What happens when indirect and address functions come together. It is mind blowing.Using INDIRECT how we can solve complex data problems like data wrong alignments and even in dashboards you can use it.What is a NAME MANAGER. How to create name managers. Their use with Indirect functionLearn how to make simple drop downs and dynamic powerful drop downs using indirect and name managersLearn how to link one drop down with another drop down. Dynamic drop downs and use them in your dashboards.Discussing about Count and Sum family Functions - COUNT,COUNTA,COUNTBLANK,COUNTIF,COUNTIFS,SUMIF,SUMIFS,MAXIFSCombination of these functions with each other by solving the real excel problems like how to combine Vlookup with SUMIF or COUNTIF - Fully practical scenarios.Use of wild characters in Count Sum functions - use of * and ?. Unbelievable magic happens here.How to use basic filters, sort features and super awesome Advance Filters with different criteriaHow to use Conditional Formatting in excel with different examples - basic to advanceAssignments are added to support you and help you in monitoring the progress. Write me back if you will have questions. I will love to help you.Taking a deep dive into ADVANCE FILTER from basics to advance.First see how normal filter works with sort features. Filter by values, colors, icons. Sort by color, column wise, row wise, value wise.What is advance filter and why it is required in Excel so much.What is the difference between Advance Filter and Filters. Are they same or totally different in terms of objectives and functionality.Learn How to extract data using criteria's in advance filter - Get Fetch unique records What is Filter in place and copy in advance filter.How to make AND criteria's if you have multiple headers you like to filter. Create OR Criteria's using Advance Filter. Rules to follow while fetching unique records.How to use logic in advance filter using wild characters like * and ?Using formulas to extract complex data pointsLearning everything about conditional formatting - basic and advanceHow to color cells based on valuesHow to color cells using formulasHow to insert icons using conditional formatting.How to highlight duplicate or unique values How to highlight values if they are repeating more than 2 times or any nth instance Learn every option given in conditional formatting.How to set priorities level in conditional formatting's.How Dates are stored in Excel. Why we must know the real data type of dates - text or number or what?Dates can be added or subtracted - how and why it is possibleDate functions can be applied on every date or there is some restrictions. Is there any start or end date in excel for datesToday function, now function, shorcut keys to insert date and timeHow to calculate working days between two dates excluding weekends or any daysCalculate no of days between two dates excluding weekends or holidaysHow to use DATEDIF function in excel How to use Text function in dates and time functionsHOUR, SECOND, MINUTE FunctionsHow time formatting or values work secretely in excelCan you add time value in time format. Why you can and why you cannot. Deep studyWhat are Arrays , why we need to know. How they work - what is the science behind it. Several amazing examples
