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;


特别提示

建议使用Windows微软office2013以上版本参与本课程，部分应用Mac版无法实现


Notice

We recommend to use windows Microsoft office2013 in this training. Some applications cannot be realized in Mac version.

Session Outline

1.    Excel技巧与操作

     1.1 数据二级验证

     1.2 限制数据重复录入

     1.3 数据动态监控

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

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


2.    Excel函数进阶

      2.1 Vlookup的多条件匹配

      2.2 Xlookup来了

      2.3 Sumproduct函数

      2.4 Offset函数

      2.5 Index函数


3.    数据新招——PowerQuery

      3.1 工作簿多工作表动态快速汇总

      3.2 多工作簿多工作表动态快速汇总

      3.3 追加查询

      3.4 合并查询

      3.5 分列与格式

      3.6 数据分组汇总


4.    数据超级透视表——Power Pivot

     4.1 日期维度表创建

     4.2 星形模型

     4.3 雪花模型

     4.4 度量值初试

     4.5 度量值规则


5.    Dashboard动态仪表板制作

      5.1 数据和图表的关联

      5.2 图表与切片器的关联

      5.3 切片器联动与布局

      5.4 切片器美化

1.  Excel skills and operation

    1.1 Secondary data verification

    1.2 Limit repeated data entry

    1.3 Data dynamic monitoring

    1.4 Data sheet error comparison and highlighting.

    1.5 Role management of data protection: cell, worksheet and workbook


2.    Advanced formulas and functions

     2.1 Multi-condition matching of vlookup

     2.2 Xlookup

     2.3 Sumproduct function

     2.4 Offset function

     2.5 Index function


3.    New data technology - PowerQuery

    3.1 Dynamic summary of multi worksheet

    3.2 Dynamic summary of multiple workbooks

    3.3 Add query

    3.4 Consolidated query

    3.5 Breakdown and format

    3.6 Data grouping summary


4.    Super PivotTable report - power pivot

    4.1 Creation of date dimension table

    4.2 Star model

    4.3 Snowflake model

    4.4 Preliminary test of measurement value

    4.5 Measure rules


5.    Dashboard dynamic dashboard

    5.1 Association of data and charts

    5.2 Correlation between chart and slicer

    5.3 Slicer linkage and layout

    5.4 Slicer 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

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

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.