|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/advanced-excel-mastery/
课程评论：没有评论
**课程名称：高级Excel精通（含Excel宏）** **课程概述：** 本课程旨在帮助学员从基础到高级全面掌握Microsoft Excel技能，并学习如何在无编程知识的情况下实现Excel自动化（宏）。课程涵盖了所有重要的基础和高级公式与函数、数据保护、管理和分析，以及宏的使用。通过大量的跨行业实际案例，本课程将赋能学生、教师、员工和企业家。 **课程特色：** * 15小时57分钟的随选视频 * 18个部分，57个视频 * 可下载的练习文件 * 永久访问权限 * 中文授课 * 支持手机和电脑访问 **主要学习内容：** * **基础Excel：** 学习Excel单元格、行、列的基础知识，掌握提高工作效率的方法，以及大量基础公式和函数。 * **高级Excel：** 学习高级公式和功能，包括数据清洗、数据分析、图表制作、仪表盘设计，以及众多行业常用函数。 * **宏（自动化）：** 在当今竞争激烈的环境中，学习如何在不编写代码的情况下实现Excel自动化（宏）。 **课程模块概览：** 1. **引言：** MS Excel的重要性、课程详情（高级函数、问题解决、交互式仪表盘、宏自动化）。 2. **Excel界面概览：** 快速访问工具栏、功能区、名称框、编辑栏、单元格、工作表标签、状态栏。 3. **基础格式化与编辑：** 插入行/列、合并单元格、文本换行、对齐、边框、字体样式、文本方向、命名单元格范围、自定义功能区等。 4. **基础Excel公式：** 加、减、乘、除，指数运算。 5. **单元格引用类型：** 相对引用、绝对引用、混合引用。 6. **基础Excel函数：** ROUND, INT, SUM, SUMIF, COUNT, COUNTA, COUNTIF, AVERAGE, AVERAGEIF, MIN & MAX, RANK, MATCH, INDEX, RAND & RANDBETWEEN。 7. **图表类型：** 柱形图、条形图、饼图、折线图、组合图等。 8. **Excel核心功能：** 选择性粘贴、冻结窗格/拆分窗格、排序与筛选、页面设置与打印设置、条件格式、数据验证、动态下拉列表。 9. **工作表与工作簿保护：** 锁定单元格、隐藏公式、保护工作表、保护工作簿、设置特定访问权限、允许特定用户编辑范围、密码保护、创建备份。 10. **高级Excel函数：** SUMIFS, COUNTIFS, AVERAGEIFS, 日期与时间函数, IF, 嵌套IF, IF与AND/OR, VLOOKUP, HLOOKUP, IFERROR, MATCH, INDEX, INDIRECT, VLOOKUP与MATCH结合, INDEX与MATCH结合, FUZZY LOOKUP, VLOOKUP与INDIRECT结合。 11. **文本函数：** LEN, LEFT, RIGHT, MID, LOWER, UPPER, PROPER, VALUE, TRIM, CONCATENATE, FIND, SEARCH, SUBSTITUTE, REPLACE. 12. **公式审计：** 显示公式、追踪引用单元格、追踪依赖单元格、删除箭头、评估公式。 13. **假设分析：** 目标搜索 (Goal Seek) 和规划求解 (Solver)。 14. **合并多个工作簿数据：** 添加比较和合并工作簿命令、共享工作簿、将多个文件的数据合并到单个文件。 15. **创建数据透视表及图表：** 数据透视表、数据透视图、图表格式化、图表类型、切片器、时间轴，以及在演示中使用形状和SmartArt图形。 16. **创建Power Pivot数据透视表：** 从多个工作表创建数据透视表、添加到数据模型、创建关系和管理。 17. **创建交互式仪表盘（1 & 2）：** 学习制作交互式数据仪表盘。 18. **学习宏与VBA代码：** 宏基础：准备工作、录制宏、创建按钮、编辑宏、调试宏、动态选择、使用相对引用、创建宏数据录入表单、保护宏代码。
Learn Basic To Advanced Excel Including Macro!Upgrade your Microsoft Excel skills and expertise to the next level. Gain proficiency in all-important basic and advanced Excel formulas & functions, protection, management and data analysis, and Macro (automation) without knowledge of coding with lots of practical examples used across different industries. This course will empower not just students but teachers as well as employees/entrepreneurs.Course Features:15 hrs 57 min on-demand video18 sections with 57 videosDownloadable practice filesFull lifetime accessExplained in Hindi LanguageAccess on mobile and ComputerBasic ExcelYou will learn all about basic MS Excel cells, Excel rows & columns, how to speed up your work with Excel spreadsheets, and lots of basic formulas and functions.Advanced ExcelYou will also learn about advanced formulas and features, data cleaning, data analysis, charts, dashboards, and lots of functions used practically in the industry.Macro (Automation)In this competitive working environment, automation is the key to success. Here you will also learn Automation (Macro) in Excel without even knowledge of coding.Course Modules:Introduction - Importance of MS Excel and course details (About advanced level functions, special problem solver, interactive dashboards & Macro for automation).Overview Of Excel Interface - Quick access toolbar, Ribbon, Name Box, Formula Bar, Cells, Sheet Tabs, Status Bar.Basic formatting & Editing - Insert Row, Insert Column, Merge, Wrap Text, Alignment, Border, Bold, Italic, Underline, Cut, Copy, Paste, Percentage, Currency, Comma, Decimal, Font Style, Font Size, Fill Color, Undo, Redo, Text Orientation, Naming Cell Range, Ribbon customization.Basic excel formula - ADD, SUBTRACT, MULTIPLY, DIVIDE, INDICES.Type of cell references - Relative Reference, Absolute Reference & Mixed Reference.Basic Excel functions - ROUND, INT, SUM, SUMIF, COUNT, COUNTA, COUNTIF, AVERAGE, AVERAGEIF, MIN & MAX, RANK, MATCH, INDEX, RAND & RANDBETWEENTypes of Chart - Column, Bar, Pie, Line, Combo, etc.Essential features of Excel - Paste Special, Freeze & Split Panes, Sort & Filter, Page Setup & Print Settings, Conditional Formatting, Data validation, Dependable dynamic Dropdown list.Sheet & Workbook Protection - Lock Cells, Hide Formulas, Protect Sheet, Protect Workbook, Allow specific access, Allow specific users to edit ranges & Password protect the Excel file, create Excel file backup.Advanced Excel Functions - SUMIFS, COUNTIFS, AVERAGEIFS, DATE & TIME, IF, NESTED IF, IF WITH AND/OR, VLOOKUP, HLOOKUP, IFERROR, MATCH, INDEX, INDIRECT, VLOOKUP WITH MATCH, INDEX WITH MATCH, FUZZY LOOKUP, VLOOKUP WITH INDIRECT.Miscellaneous Text Function - LEN, LEFT, RIGHT, MID, LOWER, UPPER, PROPER, VALUE, TRIM, CONCATENATE, FIND, SEARCH, SUBSTITUTE, REPLACE.Formula Auditing in Excel - Show Formulas, Trace Dependents, Trace Precedents, Remove Arrows, and Evaluate Formula.What if Analysis - Goal Seek & Solver.Merge Data From Multiple Workbooks - Add Compare & Merge Workbook command, Share Workbook, Merge or Combine Data from Multiple Files to Single File.Create Pivot Table & charts - Pivot, Chart, Formatting Chart, Types of Charts, Slicer, Timeline, and Use of shapes & SmartArt Graphics in Presentation.Create Power Pivot Table - Create Pivot Table from multiple worksheets, Add to Data Model, Create Relationship & Manage.Create Interactive Dashboard -1.Create Interactive Dashboard -2.Learn Macro with VBA code - Macro Basics: Preparation, record, create button, editing, debugging, Dynamic Selection, Use relative reference, Create Macro Data Entry Form & Protect coding of Macro.
