EXCEL数据分析资料

EXCEL数据分析

一、实践目的

1、弥补高校在商务数据分析方面的短板，此次培训通过基本商务数据分

析的讲解，让高校学生充分感受到商务数据分析所需要的基本知识和技能

2、让学生了解到目前商业数据的发展及应用场景，对自己的职业定位有

个更加清晰的认识。

二、实践内容

1.EXCEL数据预处理

2.EXCEL数据计算

3.EXCEL数据分析

4.EXCEL图表数据展示

5.EXCEL分析实战

三、实践过程

1.EXCEL数据预处理

（一）获取数据

（1）界面组成

1、工作簿：包含多个工作表。

a.工作簿名称

b.菜单选项卡（开始,插入,数据,公式）

c.功能区

d.工作区

e.状态栏：名称框、编辑栏、工作表标签。

2、行名：1,2,3,4

3、列名：A,B,C,D

4、单元格名:列名+行名（b5c6）

（2）数据分析

工作表的格式规则由行和列组成：一行称为一条记录，一列称为一个属

性（字段），列名就是字段名；

数据表的所有记录不能重复；

不允许出现合并单元格的结构（列）。

（3）获取数据的方法

方法1：手动输入数据

数据的类型:文本，数值，日期；

文本的输入：直接输入；

数值的输入：a.较长的数值：英文单引号+数值；

b.日期的输入：年月日的顺序，年月日之间用斜杆分隔。

方法2：导入来自其他文件的数据

文本导入:数据--自文本一文件原始格式（简体中文GB2312）—根据逗号

分隔一注册FI期（F1期:号码（选择文本）；

数据库导入：数据-一自Access—-数据透视表（表容量大时候不合适）号

码放到值区域一-字段值设置（求和改成计数）一品牌放到行；

网站导入：网址：数据一自网站一选择导入数据区域（点击箭头）右键一

数据范围属性一设置刷新频率（5分钟）。

（二）数据清洗

（1）重复数据的处理

1、菜单删除法（删除重复值）

选中所有数据一数据一数据工具一删除重复值

勾选号码一发现34个重复值，已将其删除，保留23个

取消这步操作一勾选号码和开通业务一发现28个重复，保留29个

2、标识法（标识值）

选中数据（号码）一开始一条件格式一突出显示单元格规则一重复值。

告诉了哪些号码重复但是没有去掉重复值。

3、高级筛选法（把重复值取出来，放到其他位置）

选中数据（号码）一数据一排序和筛选一筛选;高级）

将筛选结果复制到其他位置--选择不重复的记录一复制到（筛选后结吴

显示）。

得到了去重复的结果，但是没有告诉你重复了几次。

4、countif统计人数（得到重复次数）

空白列单元格处一公式一插入函数一找到函数C0UNTIF-

Range（筛选的范围）快捷键：ctrl+shift-向下箭头

Criteria-（筛选条件）一选择第一个号码一点击小十字。

得到重复的次数。

5、数据透视表（统计重复次数）

选中所有数据一插入一数据透视表（默认设置）一号码放到行

号码放到值一得到号码的统计数据一选择一个右键一排序。

可以得到排序的结果。

（2）缺失值的处理

多个单元格中输入同一个值的方法:选中多个单元格--输入值一按ctr1+

回车。

选中多个不连续空单元格的方法：开始一选择一定位条件一空值。

（3）空白值的处理

方法1：替换

方法2：函数：trim（）

2.EXCEL数据抽取

数据抽取

（一）字段拆分

方法1：菜单法

步骤：选中列一数据一分列

第1步，设置固定宽度；

第2步，设置分列线；

第3步，设置各列类型，选择目标位置。

方法2：函数法

midO：从字符串中，指定位置起，返回指定长度的字符。

leftO：从字符串，第1个字符开始，返回指定长度的字符。

righl（）:从字符串，最后1个字符开始，返回指定长度的字符。

（二）记录随机抽取

1.生成随机数:rand（）

2,对随机数排序:rank。

3.把排序结果中，前200名数据提取出来:vlookupO

vlookupO搜索提取函数

第1空：要搜索的值；

第2空：搜索区域（在哪里搜索）；

第3空：要返回的值所在的列编号；

第4空：false（精确匹配）/true（大致匹配）。

rank。：排序函数

第1空：要排序的数；

第2空：区域（要绝对引用）：

第3空：0（降序）/1（升序）。

数据合并

（-）字段合并（靠左排列文本型数据，靠右排歹J为数值型数据）

方法1：函数法Concatenate

打开字段合并文件一公式一插入函数

选择一Concatenate（把多个字符文本或数值连接在一起）

日期1:靠左排列--文本型数据（不能进行计算）

日期3：靠右排列--数值型数据（数值型H期才是真正的日期）

方法2：文本连接符&

&：二A2&〃-〃&B2&〃-〃&C2

方法3：日期连接函数Date（数值型数据可以计算）

公式一插入函数一时间函数一加lu

选择年（A2）月（B2：□（C2）一双击小十字

（二）字段匹配

（1）单条件匹配：vlookup

第一个参数号码表一A2元素

第二个参数：匹配区域

第三个参数：返回数据对应查找数据表

第四个参数:精确匹配0,模糊匹配l=VL00KUP（）

（2）多条件匹配：vlookup

vlookup的执行原理：从区域（第2个空）的首列，搜索第1个空的值,

vlookup。个号码,区域，列号，false）o

ppt中的多条件匹配，使用了excle数组运算。

数组运算执行方法：CTRL+SHIFT+回车。

3.EXCEL数据计算

（一）简单计算

如：+-*/

（二）日期计算

datodif（起始R期,终止n期，格式）

作用：计算起始和终止日期之间所经历的时长。

时长格式：

y：以年显示

m：以月显不

d：以日显不

year（）：提取日期中的年份

month（）：提取日期中的月

day（）：提取日期中的日

today（）：返回当前系统日期

（三）标准化计算（使一些数据的异常值，落入到正常的0T的区间）

计算方法：x标准化二（x-最小值）/（最大值-最小值）

max（）：求最大值

min（）：求最小值

（四）加权求和（通过加权，让数据的占比均化）

函数法：Sumproduct（区域1,区域2）

综合得分框输入：=SUMPRODUCT（B2:F2,权重！$B$2:$F$2）。

第一个参数：数据区域（B2到F2）；

第二个参数：权重区域（B2到F2）；

按F4固定权重区域。

权重表：权值选择要用绝对引用地址F4。

实质：各数据乘以权重，并求各乘积的和。

数据分组

方法1：1F函数一IF（条件,满足条件结果,不满足条件结果）

方法2：vlookup函数模糊匹配分组

操作：vlookup函数的第4个空填'true',打开数据分组一选中一插入

函数VLOOKUP。

第一个参数：根据什么找

第二个参数：在哪儿找

第二个参数：从条件列起相对应的第儿列

第四个参数：精确匹配或模糊匹配

数据类型转换

（一）行/列转换

操作：复制数据区域一到目标位置一右击一选择性粘贴对话框中，选择

‘转置

（二）文本到数值

方法1：分列

操作：选中数据区域一数据一分列一分列第3步选择'常规'类型。

方法2：选择性粘贴一运算

操作：先复制‘1'一选中数据一选择粘贴一运算。

方法3：智能标记

（三）数值到文本

方法1：分列

方法2：函数：text（数值，‘格式'）

常用格式:小数（0.00）

百分比（0.0%）

日期（00年00月00日）

（四）数值到日期

方法：分列

（五）二维表转换为一维表

方法：使用数据透视表制作向导

操作：1.ALT+D+P：打开向导对话框）；

2.多重合并数据区域；

3.自定义页字段；

4.选择数据区域；

5.双击数据透视表的总计值（可以把二维转一维）。

4.数据分析

（-）对比分析（日期，时间分组）

I、环比：同一年中，上个月和下个月的数据比较，如（3月-2月）/2月.

2、同比：两年中，同一时间的比较，如（20：2年-2011年）/2011年。

注意：数据透视表中，计算环比：选择'值显示方式'为差异百分比。

选择字段为“注册时间”，基本项为“上一个”；

数据透视表中，计算同比：选择'值显示方式'为差异百分比。选择字

段为“年”,基本项为“上一个”。

（二）结构占比分析（定性分组）

1、定性分组

2、占比计算：一个项目中，各决定因素所在的比例。

（三）分布分析（定量分析）

方法1：vlookup方法分组

特点：可以进行不等距分组。

操作：用vlookup分组后，用数据透视表统计分组结果。

方法2：数据透视表分组

特点：只能进行等距分组。

操作：直接用数据透视表的分组功能完成即可。

（四）交叉分析（将消费人群进行分类）

1、目的：从两个维度对我们的客户进行分类。

2、操作步骤：a.用vlookup确定各客户，两个维度的性质。

b.用数据透视表，从两个维度进行分类，来统计各分类的客户人数。

c.从一维表的角度，查看客户分类情况的操作（1.以表格形式显示；2.

重复所有标签；3.不显示汇总结果）。

（五）矩阵分析（将消费人群进行分类）

操作步骤：1、先进行定性分组，对数据进行平均值的计算；

平均值计算方法：在透视表的计算区域上，右击一值字段设置一平均值。

2、复制透视表数据到新的位置；

注意：粘贴数据时，用'选择性粘贴’中的值。

3、制作散点（矩阵）图；

方法：选中月平均消费和月平均流量的值一插入一散点图。

注意：不选行/列标签及总计

4、散点图x/y坐标轴，移动到平均值交叉位置；

方法：在坐标轴上右击一坐标轴格式，设置即可。

5、重新绘制x/y的坐标轴；

6、给散点添加标签。

a.在点上右击一添加标签。

b.选中标签一右击一设置标签格式一单元格中的值一选择文字性的标签

一取消Y值。

案例：根据消费、流量两个维度，分析某通信公司各大区用户质量。

用户质量矩阵

高49

毗皿

低消费高

打开用户消费明细.Xlsx文件一（匹配地区表中地区信息）C列手机品牌插

入一新列（大区）

选中大区列一第一行一插入一函数VLOOKUP；

第一个参数（找什么根据什么找）一省份第一行；

第二个参数（在哪儿找）--地区sheet（AB歹的；

第三个参数（返回位置）—2（地区在省份第二列）；

第四个参数匹配方式：0精确匹配；

双击小十字批量完成。

选中数据（sheet）中A到H歹I」：插入一数据透视表，月消费，月流量一拉

到值区域一值字段设置一都设置为一平均值，大区一-拉到行标签。

做矩阵图--选中数据（整个表格）复制-一开始--粘贴（值），选中数据区

域（不包括总计行，行列标题）--插入-一散点图一删除网格线-一先选中再删

除—先行后列；

选中纵坐标轴—右键一设置坐标轴格式一横坐标轴交叉一坐标轴值

——1000.8；

选中横坐标轴一纵坐标轴交叉--坐标值（149.2）一右侧一-下拉一标签

一标签位置（无）：

选中--图标标题一删除；

选中其中一个点---启动XYChartLabels---打开安装目

D:\AppsPro\ChartLabeler--XYChartLabeler.xla

加载项---XYChartLabe1s---AddChartlabels--选择标签范围（Select

aLabelRange）-一行标签（大区所有内容）。

（六）多表关联分析

操作步骤：1、将数据表添加至〃数据模型〃中；

2、插入数据透视表；

3、建立数据表之间的关系；

4、拖动数据字段进行分析。

案例：通过地区和通讯品牌两个维度，统计消费用户数。

操作步骤：1、分析两个表之间的连接字段（列）；

注意：连接字段，就是两个表的公共列（例如：省份）。

2、把两个表添加到模板中（方法：插入一表格。）;

3、插入一透视表一选择多个表；

4、进行多表连接（方法：透视表工具一分析一关系）；

5、数据分析即可。

案例：各手机品牌是否开通微信的用户数。

操作步骤：1）手机品牌，微信；公共列为：号码；

2）用透视表做关联；

a.把表放到模板中。

b.插入--透视表--添加多个表。

案例：各地区是否开通微信的用户数。

地区：地区C

微信：号码表。

（七）RFM分析（从三个角度对用户进行分类）

R：最近一次的消费时间（时长）

F:最近的消费次数

M:消费额度

操作步骤：1、计算R,F,M值（用数据透视表完成）；

R：日期最大值

F：订单ID计数

M:金额求平均值

用客户ID定性分组。

2、把上面透视表结果复制，到新表中（粘贴时，使用选择性粘贴的'值’）；

注意：把R用daQdif换算成天数。

3、对R,F,M评分；

4、用透视表，对评分结果做分析。

5.EXCEL数据展现

（一）Excel图表

1）饼图：占比成分

操作1:图表各对象格式设置（方法：在对象上右击一选择相应操作。）;

操作2:图表布局；

操作3:图表设计（数据选择，图表类型更改）。

制作图表方法：选中做图表的数据一插入一图表类型一进行图表设置。

2）坐标轴图表

用途：数据类别为两个（单位不同或者量差别较大）

操作步骤：1、制作图表（柱形图）；

2、把较小单位的值，用次坐标轴表示；

方法：选中图表一右击一设置格式一次坐标轴

3、修改次坐标轴，图表类型为打线图

格式设置：1、文字大小，方向；

2、坐标轴的刻度：

3、隐藏次坐标轴。

（二）excel图表工具

1）双坐标轴图

作用：把量级差别较大或单位不同的数据，在一个图表上表示。

操作：选中数据一插入一柱形图。

设置内容：1.文字格式；

2.绘制自坐标轴；

3.更改图表类型；

4.添加标签；

5.隐藏次坐标轴。

2）目标完成率图

作用：反映业务目标的完成情况。

操作：类似双坐标轴操作。

注意：把完成值，绘制在次坐标轴上。

格式设置：1.系列图形的填充色，线条色，系列间隙宽度；

2.隐藏次坐标轴；

3.给完成值，添加完成率的数据标签。

3）雷达图

作用：系列有2组以上数据时，用该图。

操作：选中数据一插入一雷达图。

格式设置：系列宽度设置在1以下。

4）矩阵图

作用：用两组相关数据，对我们的客户进行定性分类；

注意：1.选择数据（不选行/列标题和平均值）；

2.给每个点添加标签（行标题）；

3.移动x/Y坐标轴，到平均值位置。

4.重新绘制x/y坐标。

5）迷你图

作用：当数据系列比较多时，快速查看每个系列的趋势或变化情况。

操作方法：光标放在放迷你图位置一插入一迷你图一选择类型。

设置内容：1.图表样；2.设置高点，低点。

6）漏斗图

作用：一般用来表示，一个商业行为的变化过程。

例如：购物（浏览产品一放入购物车一下单一支付一完成）。

操作：选中数据一插入一堆积条形图。

注意：1.逆序系列标签（方法：选中纵坐标轴一右击一设置格式一逆序类

别）。

2.添加占位数据（方法：在图表上右击一添加数据）。注意：把占位数据

放到系列数据的前面。

3.把占位数据的图形，填充和线条都设置为，无'；

4.形成封闭的漏斗（方法：图表工具一设计一添加元素一线条一系列线）。

7）旋风图

作用：展现不同数据在同一组指标下比较结果。

操作：选中数据一插入一堆积条形图。

注意：1.绘制其中一组数据到'次坐标轴'；

2.修改主次坐标轴的刻度（最小值：负数。最大值：负数绝对值）；

3.将次坐标轴刻度进行‘逆序'（方法：在次坐标轴上右击一逆序刻度值）。

格式设置：1.将纵坐标轴标签，移动到左侧（方法：设置标签位置

为'低）；2.隐臧次坐标轴（方法：设置次坐标轴标签为‘无'）；

3.修改主坐标轴的数字格式为'0:0:0'（方法：数字一自定义）。

8）帕累托图（28原则）

作用：分析出现问题后，原因的定位分析。

操作：选择数据一插入一柱形图。

设置：1.设置柱形图系列间距为最小；

2.设置主坐标轴刻度最大值为总问题数；

3.添加问题百分比数据到次坐标轴上,设置次坐标轴最大值为100%；

4.添加次横坐标轴（方法：图表T具一设计一添加元素一坐标轴一次横义

标轴）;

5.设置次横坐标轴的位置为：刻度线上。

6.EXCEL案例分析

案例1：

1）同比；

2）环比；

3）环比发展速度;本月/上月；

4）平均环比发展速度二所有环比发展速度的乘积，开12次方；

power（product（环比速度），1/12）。

product（）:求所有参数的乘积。

power（）:索函数。

例如：power（2,4）一16。

5）环比增长速度二环比发展速度T00；

6）移动平均值：avergeOo

案例2：通过函数计算

1）性别的计算

if（mod（mid（身份证号，17,1）,2）=0,〃女〃，〃男〃）。

说明：mid（）一提取倒数第2位;

mod（）―倒数第2位对2求余；

if（）一判断求余的结果，0时为女，否则为男。

2）出生日期的计算

text（mid（身份证号,7,8）,〃00年00月00日〃）。

说明：textO一将mid提取的8位数字，以日期形式显示；

mid。一提取中间8位。

3）年龄计算

datedif（出生日期，today（）,"y"）。

说明：datcdifO一计算两个日期的时间跨度；

today（J一返回今天的日期。

4）T龄的计算

显示形式：11年3个月。

datedif（入职日期,today。，"y"）&〃年"&；

datedif（入职日期，today。，"ym"）&〃个月〃。

案例3：

1）显示开发工具选项卡

文件一选项一自定义功能区。

2）插入分组框

开发工具一插入一分组框。

3）插入四个单选按钮，并编辑文字

插入：开发工具一插入。

编辑文字：在成钮上右击一编辑文字。

4）显示按钮编号

在按钮上右击一设置格式一控制一链接到某个单元格。