Excel Training:Data statistics and analysis 数据统计与分析实战

Online Training,

Event language(s)

Price: 998 RMB for Members
1098 RMB for Non-Members
*All taxes included prices


Event ended.



数据量大时文件卡慢 很多公司的业务数据量越来越大,用常规的方法处理、计算和分析效率低下。

多来源数据的合并分析 数据会来自多个部门或集群,数据分散,需要周期性更新,前期的数据准备非常繁琐,无法保证足够的时间聚焦于业务分析。

图表 图表制作无思路,做出的图表不够直观简洁。报告中的各个图表无法动态交互和钻取。

Course Description

This course mainly solves the following problems in the use of Excel:


Improve efficiency: The amount of business data is increasing these days, so we need new methods to improve the efficiency of the data process, calculation, and analysis.


Combined analysis of multi-source: Data will come from multiple departments or clusters. The data is scattered and needs to be updated periodically. The data preparation in the early stage is important, or we can not guarantee enough time to focus on business analysis.


Chart: We will give tips to make the chart, and make the charts in the report cannot interact and drill dynamically.



  1. 所有知识讲解均结合老师多年的业务分析经验,让案例更加深入工作;
  2. 讲解与互动双管齐下,大量的案例练习,让知识点在课堂全部吸收;
  3. 所有案例内容均从真实工作中提炼,培训后学员可以立即学以致用。



1) All knowledge explanations are combined with the lecturer's business analysis experience,so the cases is more in-depth work;

2) Explanation and interaction will be highly combined, with case exercises, the knowledge absorption will be  guaranteed;

3) All cases are extracted from real work, the trainees can apply immediately after training.



课程大纲  Outline:


  1. 搞懂两大数据模型是数据分析的关键
  2. 分清4种数据类型才能避免手忙脚乱
  3. 清楚数据分析流程才能胸有成竹
  4. 有效管理数据是提高效率的基础

I. The thinking of Data management

1) Understanding the two key models in data analysis

2) Distinguish four data types

4) Clear the process of data analysis

5) Effective data management is the basis of improving efficiency



  1. 如何避免收集到错误数据
  2. 成倍提高数据录入技巧
  3. 合并一个工作簿中的多个Sheet
  4. 合并文件夹中的多个文件
  5. 数据合并后的动态更新
  6. 快速从大量数据中找到目标数据
  7. 逆透视、差异比较等数据快速整理技能
  8. 多关键字的数据匹配
  9. 解决系统导出数据不能计算问题
  10. 解决数据打开乱码问题

II. Data collection and collation

1) How to avoid collecting wrong data

2) Improve the efficiency of data entry

3) Merge multiple sheets in a workbook

4) Merge multiple files in a folder

5) Dynamic updating after data merging

6) Find target data from a large amount of data

7) Reverse perspective, difference comparison, and other data fast sorting skills

8) Multi keyword data matching

9) Solve the problem that the data exported by the system cannot be calculated

10) Solve the problem of data open garbled



  1. 数据汇总计算常见误区
  2. 用公式处理不规范的数据
  3. 日期如何正确参与计算
  4. 内容不完全相同时的查找匹配
  5. 同时查找多列数据
  6. 跨工作表或工作簿的查找
  7. 公式在绩效、奖金等计算场景中的应用

III. Formula function

1) Common mistakes in data collection and calculation

2) Dealing with nonstandard data with formulas

3) How to calculate date correctly

4) Search and match when contents are not identical

5) Find multiple columns

6) Search across worksheets or workbooks

7) Application in performance, bonus, and other calculation cases



  1. 快速了解强大的透视表
  2. 技高一筹的分组分析
  3. 透视表瞬间搞定各种分析场景
  4. 用Excel建立数据模型
  5. 如何用Excel轻松处理上亿行的数据

IV. Analysis with Pivot Table

1) Quick understanding of powerful pivot table

2) Group analysis

3) Application: Pivot table in different cases

4) Establish data models

5) Powerful super perspective in Excel

5) How to deal with large amount of data



  1. 如何选择合适的图表表达观点
  2. 商务图表制作三步走
  3. 自动突出显示异常值
  4. 双坐标图表制作
  5. 专业级图表美化
  6. 动态图表呈现数据
  7. 制作交互式仪表板
  8. Excel 与商业智能

V. Make professional charts

1) How to choose the right chart to express your views

2) Three steps of making business charts

3) Automatically highlight errors

4) Make two-axis charts

5) Professional chart beautification

6) Dynamic chart

7) Making interactive dashboards

8) Excel and business intelligence


Please send e-mail to bj-training(@)ccifc.org

Share this page Share on FacebookShare on TwitterShare on Linkedin