Home

课程受益


  • 掌握职场高阶函数，处理更为复杂的数据统计与分析；
  • 掌握数据分析的相关工具，有效挖掘数据价值；
  • 掌握数据看板制作方法，数据分析结果更全面、系统及高效；
  • 掌握高阶商务图表类型的绘制，让数据传达形式更为新颖及多样化；

Course Benefits


  • Master the higher-level functions to process more complex data statistics and analysis;
  • Master the relevant tools of data analysis and find the value of data effectively;
  • Master data table production methods, make data analysis results more comprehensive, systematic and efficient;
  • Master the drawing methods of high-level business chart types to make the forms of data transmission more novel and diversified;

Session Outline

课程大纲


一、Excel数据验证


1. 数据二级验证

2. 限制数据重复录入

3. 数据动态监控

4. 数据表误差比对与突出显示

5. 数据保护之单元格、工作表、工作簿和角色管理


二、表格灵魂：函数公式魅力 


1. 公式与基础函数

2. 统计求和家族: Sum, sumif, sumifs, count, countif, countifs

3. 逻辑查找帮你解难题: Vlookup和match vs Xlookup

4. 日期函数： datedif, networkdays

5. 其他高阶实用函数：Sumproduct、Index、Offset函数


三、表格利器：透视规划分析

 

1. 初始数据透视表 

2. 同一字段不同汇总方式 

3. 透视表自动和手动组合 

4. 环比和同比

5. 动态数据透视表设置


四、表格印象：图表演示之道 


1. 迷你图、双Y轴图、复合饼图

2. 图表美化：柱形图、折线图、饼图

3. 计划与实际对比图制作：温度计柱形图、柱形+堆积图


五、Dashboard动态仪表板制作


1. 数据和图表的关联

2. 图表与切片器的关联

3. 切片器联动与布局

4. 切片器美化

 

六、总结提高：学以致用，整体答疑  

Session outline


I Data verification


1.  Secondary data verification

2.  Limit repeated data entry

3.  Data dynamic monitoring

4.  Data sheet error comparison and highlighting

5.  Role management of data protection: cell, worksheet and workbook


II Soul of Table: The Charming of Formulas


1. Formula and basic function

2. Sum, sumif, sumifs, count, countif, countifs

3. Vlookup和match vs Xlookup

4. Date function: datedif, networkdays

5. Sumproduct、Index、Offset


III. Data analysis with PivotTable


1. 1. Initial PivotTable report

2. 2. Different summary methods

3. 3. Automatic and manual combination in Pivot table

4. 5. Month/ Year comparison

5. 6. Settings in dynamic PivotTable report


IV The Impression of Table: Business charts


1. Mini chart, double Y-axis chart, Pie chart

2. Chart beautification: column chart, line chart, pie chart

3. Making contrast: thermometer column chart, column stack chart


V Dashboard dynamic dashboard


1. Association of data and charts

2. Correlation between chart and slicer

3. Slicer linkage and layout

4. Slicer beautification

Speakers

  • Grace Yan

    Grace Yan

    曾任全球前 10 强外企内部培训讲师，知名世界 500 强外企从
    业经历（戴尔、雪佛龙和保乐力加），多家 500强企业微软
    Office 和微软 Power BI 培训授课经验，长期为客户和学员解
    决 Office 和 Power BI 问题，线下受众学员 100,000 人以上。
    10 余年 Office 软件深度用户，多年 500 强外企工作经验，能
    准确挖掘企业培训需求，快速帮助学员提高 Office技能；课
    程结构条例清晰，课程案例吻合工作需要；授课风格生动有
    趣，善于调动学员积极性，深受学员喜欢和好评。长期为多
    家客户设计制作工作、发布会等 PPT。

    Grace，Microsoft Certified Trainer, is also the trainer of Microsoft’s golden partner. She is certified as Microsoft Certified Office Master, Microsoft Certified Excel / PPT/ Word(Full mark in Word part) and Microsoft Certified Data Analyst Associate. As a bilingual trainer, she was the internal trainer of G10 multinational enterprise. Grace has been worked in Dell, Chevron and Pernod-Ricard. Having rich experience in office training in global 500 companies, she has helped solving office problems for clients and trainees. Until now, more than 10,000 trainees are benefited from Grace’s training. Grace, with more than 10 years’ experience of Microsoft Office Word/Excel/PPT and Power BI, can meet corporate’s training requirements, and helps students’ office skills quickly. Her professional training is designed in good logic and is delivered in a humor style and being well acclaimed by trainees. She has permanent cooperation with companies for PPT designing and designed PPT for many companies for regular report or public press conference.

    view more

Who Should Attend?

课程受众


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

 

Course Audience



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

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,098
Member Company Employee Ticket
Standard Price RMB 1,098
Non-member Ticket
Standard Price RMB 1,298

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.