Home

课程简介


数据是企业发展的命脉，然而数据需要进行汇总、分析和挖掘，从中得到见解进而获得价值。本课程从上百家企业实际应用案例出发，结合培训师十余年的培训经验进行萃取。根据数据的生命周期，循序渐进学习数据清洗、数据建模和分析到可视化呈现，让学员完整体验和学习数据分析的完整过程，为实际业务提供宝贵的经验和技能指导，赋能员工生产力。


Course Background


The business data needs to be aggregated, analyzed and mined to gain insights and value. This training is based on the practical cases of hundreds of enterprises. The participants will gradually learn data cleaning, data modeling and analysis, visual presentation.....


课程收益


  • 掌握最实用的Excel技巧，改善工作方式，提高数据处理能力；
  • 通过数据透视表和专业图表制作提供丰富的数据可视化呈现方式，为业务提供宝贵意见，优化未来工作表现。


Course Benefits


  • Master the most practical Excel skills and improve the data processing ability of trainees
  • Provide rich data visualization through pivot tables and professional chart production

Session Outline

课程大纲

 

1 数据处理


1.1 条件格式：数据条、图表集和自定义规则

1.2 高级筛选：单条件筛选和多条件筛选

1.3 自定义排序：顺序设置

1.4 数据验证：一级数据验证和二级数据验证

1.5 数据保护：单元格、工作表和工作簿保护


2 函数公式


2.1 文本函数：left, right, mid, find, replace, substitute, trim

2.2 日期函数：Emonth, Weekday, Weeknum, Datedif, Networkdays

2.3 求和函数：Sum, Sumif, Sumifs

2.4 统计函数：Count, Counta, Countis, Countifs

2.5 条件求和函数：Sumproduct

2.6 逻辑查找函数: If, Lookup, Vlookup, Xlookup, Index, Offset


3 数据建模和分析


3.1 Power Query: 数据转换、工作簿合并、文件夹合并、合并查询、追加查询

3.2 Power Pivot：模型分类、数据建模、度量值初试

3.3 数据透视表：创建数据透视表、透视表动态更新、数据分组、同比和环比分析、切片器联动

 

4 数据可视化



4.1 图表制作（上）：专业图表制作要求、图表配色、迷你图、复合饼图、瀑布图

4.2 图表制作（下）：温度计图、簇状堆积柱形图、图表美化

Outline

 

1 Data processing


1.1 Conditional Formatting: Data Bars, Chart Sets, and Custom Rules

1.2 Advanced Filtering: Single Condition Filtering and Multi-Condition Filtering

1.3 Custom Sort: Order Setting

1.4 Data Validation: Primary Data Validation and Secondary Data Validation

1.5 Data Protection: Cell, Sheet and Workbook Protection


2 Function formula


2.1 Text functions: left, right, mid, find, replace, substitute, trim

2.2 Date functions: Emonth, Weekday, Weeknum, Datedif, Networkdays

2.3 Summation functions: Sum, Sumif, Sumifs

2.4 Statistical functions: Count, Counta, Countis, Countifs

2.5 Conditional Summation Function: Sumproduct

2.6 Logical lookup functions: If, Lookup, Vlookup, Xlookup, Index, Offset


3 Data modeling and analysis


3.1 Power Query: Data Transformation, Workbook Merge, Folder Merge, Merge Query, Append Query

3.2 Power Pivot: Model Classification, Data Modeling, Measure Value Preliminary Test

3.3 Pivot table: create pivot table, dynamically update pivot table, data grouping, year-on-year and month-on-month analysis, slicer linkage


4 Data visualization



4.1 Chart Production (Part 1): Professional Chart Production Requirements, Chart Color Matching, Sparkline Chart, Compound Pie Chart, Waterfall Chart

4.2 Chart production (below): thermometer chart, clustered stacked column chart, chart beautification

Speakers

  • 晏艳 Grace Yan

    晏艳 Grace Yan

    易迪思金牌讲师
    微软 Office 中英文双语讲师
    微软 Office2013 认证大师 Microsoft Certified Office Master

     多家 500 强企业 Office 培训授课经验
     长期为客户和学员解决 Office 问题
     多个自媒体公众号微课讲师
     受众学员 10000 人以上


     Rich office training in many global 500 companies
     Solove office problem for clients and trainees
     Micro-class trainer for many Wechat Office Accounts
     More than 10,000 trainees



    多年 500 强外企工作经验，能准确挖掘企业培训需求，快速帮助学员提高 Office 技能；课程结构条例清晰，课程案例吻合工作需要；授课风格生动有趣，善于调动学员积极性，深受学员喜欢和好评。

    Having many working experiences in G500 companies, being able to meet corporate’s training requirements, can help students’ office skills quickly, the training is designed in good logic, having a humor teaching style in class and being well acclaimed by trainees



    Office 办公系统课程
     《Excel 商务数据处理与分析》
     《Excel 数据透视表与图表美颜》
     《Excel 高级商务图表制作》
     《Excel 在 HR 中的高效应用》
     《大数据时代——用 PPT 讲话》
     《Office365 该这样玩儿》

    Office Series Courses

     Business Data Processing and Analysis by Excel
     Excel PivotTable and Charts Beautify
     How to Make Business Charts by Excel
     Excel Makes HR Work More Efficient
     PPT Talks in Age of Big Data
     Office 365 Is More Than Office



    曾经服务客户
    雪佛龙中国能源公司
    中国石油西南天然气研究院沃尔沃汽车制造有限公司 西南美商会
    新电科技有限公司澳新银行
    保利地产
    当纳利印刷有限公司四川机场集团
    中国核动力研究设计院电子科大成都研究院 西安印钞有限公司
    中粮大悦城
    上海国际医学园区中国中铁西南院

    Clients

     Chevron China Energy Company
     Petrol China
     Volvo Cars Co., Ltd
     Southwest AmCham
     NCSI
     ANZ Bank
     Poly Real Estate
     RR Donnelley Publishing and Printing Company
     Sichuan Airport Group
     Nuclear Power Institute of China
     Chengdu Institute of UESTC
     Xi’an Banknote Printing Co., Ltd
     Joy City of COFCO
     Shanghai International Medical Zone
     Southwest Institute of CRECG

    view more

Who Should Attend?

课程受众


适合希望通过EXCEL快速提高工作效率，掌握数据分析方法的企业员工。


Course Audience



Suitable for employees who want to improve their work efficiency and master data analysis methods.


Excel实战训练营15天（2022.9.14-9.29）


2周在线学习，4大模块，10节录播课，2次直播互动辅导/作业讲解/答疑



2 weeks of online learning, 4 modules, 10 recorded lessons and 2 live interactive tutorials, exercise explanations and Q&A.

Tickets

Please use your own name and email when registering for a training session. Additional fees may apply if you fail to register under your own identity. 


A prepayment is required for all full-day training sessions. 

全天培训课程需要提前付款。

Member Ticket
Member Price RMB 1,080
Member Company Employee Ticket
Standard Price RMB 1,080
Non-member Ticket
Standard Price RMB 1,280

Registration

  • All registrations shall be made online before the session. Payment also shall be made before the session.
  • AmCham Shanghai members are eligible to attend at "Member Rate"; Employees who work at AmCham Shanghai member companies are eligible to attend at "Employee Rate"; Non-members are eligible to attend at "Non-Member Rate".
Cancellation Policy
  • Cancellation: If you need to cancel your registration, please notify Ray Cheng at (86 21) 6169 3015 or email ray.cheng@amcham-shanghai.org at least two weeks prior to the session for a full refund. Cancellation made within two weeks before the session will not be refundable.