数据分析实战技能培训.docx

数据分析实战技能培训
一、数据录入
数据类型：单元格的数据类型和单元格内数据的数据类型是独立的
1、在输入数据之前，改变单元格的类型，再输入数据，可以达到输入数据类型和单元格设置类型一致的结果。
2、在已有数据情况下，并不能通过改单元格格式来改变数据格式。
[建议]采用分列的方法（数据—>分列），在最后一步的“列数据格式”进行选择就可以将数据格式进行改变。

【案例1】Excel里面最多支持15位数字，超过15位后面数字丢失变成0，如身份证号码等，不能直接敲，可以选择的方法：
1） 将单元格格式改成文本后再敲数据
2） [建议]英文的单引号+直接输入
总结：敲之前，单引号；敲之后，用分列
【案例2】要求数据是上下行文本类型编号连续的，但输入时长文本格式数据不能通过“拖”的方式序次增加
发现“文本+数字”的形式，向下托，数字可以变，可以在需要变和不变的之间敲个空格或者“A”等，强制将后面的数据变成数值，再采用“拖”的方式完成数据录入，再用替换的方法去除加入的多余字符
二、行列转换
【案例1】数据文件中有特殊符号，如何替换特殊符号：将特殊符号ctrl+C，到替换位置ctrl+V，再进行替换
【案例2】数据行列转换
1、 一列变成多列：
ø 需要借助word，将数据拷贝到word文档中，选择“表格—>转换—>将表格转换成文本（选择段落标记）；
ø “表格—>转换—>将文本转换为表格，选择需要转换的列数；
ø 将转换完成的数据拷贝回Excel。

2、 多列变成一列
ø 借助word表格，将数据拷贝到word文档中，选择“表格—>转换—>将表格转换成文本（选择段落标记）就可以完成
[注意]如果需要保持一定顺序，可以借助先在excel中用“选择性粘贴”将数据转置，再进行上一步操作（如果数据块行数不一样时可能有问题）；
另外可以使用添加标识的方式，如给数据块中数据添加以数据所在行列的标识（通过函数column（）、row（）），来定位此列的位置，然后再word中转换，贴到excel中再排序，随后进行分列来完成。
三、利用选择性粘贴，进行数据运算
【案例1】某一列数字固定加减乘除另外一个固定数字，如图中所示的一列数据同除以100。
复制固定数字100—>选择准备运算的列—>选择“选择性粘贴” ，在“运算”块内选择相应操作即可。

【案例2】如果数据是两列或者两个表，把两列的结果进行加减乘除运算，结果放到第一列。
复制被预算数字列—>选择准备运算的列—>选择“选择性粘贴” ，在“运算”块内选择相应操作即可。

【案例3】利用“选择性粘贴”将交错的两列数据合并。
“选择性粘贴—>选择跳过空单元；这样对于前一列数据空的地方不贴，只粘贴存在的数据。

四、定位空值

【案例1】数据如下图所示， 要求第一列空格处填充向上的内容

ø 选中需要填充数据的区域，选择“编辑—>定位—>定位条件—>空值”；
ø 选中所有空格后，直接输入公式（注意不要再点击单元格，已经选中区域了），随后ctrl+enter（目的是在选黑区域内输入相同内容）；
ø 用选择性粘贴将公式变为数值，便于以后计算。
五、选择不重复记录
【案例1】选择不重复记录
ø 选中需要进行不重复记录的数据区域—>“数据—>筛选—>高级筛选”；
ø 按照下图“选择不重复的记录”；
ø 得到的结果是将重复的纪录隐藏的方式，选择“可见单元格按钮”，将数据复制到新的工作表中。完成。
ø 如果需要恢复所有数据：“数据—>筛选—>全部显示”
不同需求时，注意和排序结合使用

“可见单元格按钮”显示在菜单栏的方法：
ø 右击菜单栏，选择“自定义”，如下图所示
ø 将选定的按钮拖到菜单栏上

阶段练习一
下表转换，转换成下图中右边的格式。

做法与步骤：
ø 多列变一列：将除表头外数据拷贝粘贴到word中，参照上面的做法将其变成一列，替换掉空格后，拷回excel
ø 空值中填入上一行值：将是号码的数据在前一列标示出来（用if函数），然后再用选中空格进行相同公式输入，将首行均为号码的进行筛选删除。
注意：操作过程中，定位可能不成功，将公式运算后的结果粘贴成数值，或者用分列完成。
阶段练习二

对“号码”该列记录相同的进行汇总。做法如下：

再采用排序，筛选不重复的，得到最后结果。
六、Excel常用函数
1、文本处理
left、right、mid、len、rept、find（找的内容，在哪里找）找指定在指定单元格中出现的位置
Find函数：在字符串中查找某字符所在位置，如果出错，则是没有该字符
Left函数：

Rept函数：加几位固定数字

2、数字函数：
Round（数字，小数位数）：四舍五入
Roundup（数字，保留小数位数）：有数就入
Rounddown（数字，保留小数位数）：有数就舍
Rand（），0~1之间的随机数
【案例1】头60s算3毛钱，后面每6s算1毛钱，不足6s算6s
=IF(通话时长<60,0.3,ROUNDUP((C4-60)/6,0)*0.1+0.3)
【案例2】抽取一堆数字
生成随机数，选取最大的前多少个
3、日期函数：
Year（）返回日期年
Month（）返回日期月
Day（）返回日期日
Date（年，月，日）将非日期变成日期
DateDif(小日期，大日期，“Y”) 求日期相距（小日期表示在前的日期，大日期表示之后的日期），“Y”表示相距的年，“M”表示相距的月，“D”表示相距的日
Today（）返回当前日期
注意：1900-1-1之前的日期，excel无法计算；在excel中日期相当于数字，从1900-1-1为1，增加一天加1
【案例1】相距的分钟，两个时间直接相减，结果乘1440
4、汇总函数：
Countif（哪列找，找什么），按条件求个数；求重复个数；找两个表中的区别
特殊应用，按照指定号码编先后次序，相对地址和绝对地址之间的转换：F4
【案例1】

Sumif（在哪里找，找什么，把哪列加总）按条件求和
【案例2】

Vlookup（找的条件，在哪列找，选择区域的第几列，0）查找函数
注意：谁不动，给谁钱（F4进行切换），Vlookup函数可以和column（）函数综合使用
//----------------------------//
七、文本文件数据导入到excel
如何把数据拿到excel中来
1、如何将文本文件放入excel中来
直接在新建的excel中打开 “*.txt”文件：可以直接进入数据导入向导
注意：其中导入起始行，可以删除废数据；固定宽度分列时是左边对齐的
八、数据透视表
范例数据：销售数据D.xls
[建议]将数据文件和透视表所在文件分开
1、生成透视表
新建空的excel—>透视表—>外部数据—>获取数据

如果出现以上情况，将选项中的系统表选中。

选中表格，生成透视表。
透视表有四个部分：页字段、行字段（汇总依据）、列字段（汇总依据），中间（汇总）；
对于汇总后的数据还可以修改和刷新：
ø 可以对汇总数据部分点击右键，选择字段设置；
ø 双击汇总数据可以看明细
ø 数据透视表相当于数据库的交叉表查询
ø 透视表位置选择右键，选择“刷新”（当数据源改变时可以直接刷新）
ø 如果想周期更新数据透视表，做法如下图。

【案例1】利用透视表可以直接计算百分比，如下：

【案例2】利用透视表，可以汇总数据
右键——组及显示明细数据

每一种价位的总数量，同样上述方法，选择步长
注意：在数据的同一个文件做透视表时，选择一个单元格来建透视表就可以了。
页字段：相当于条件区，可以拆开多个表显示，如下图。

九、图示化数据方法——条件格式
图示化方法，数据更清晰
条件格式，按照某种条件，将指定区域自动变成某种特定格式。条件格式可用格式刷，做好一个单元格，其他的单元格用格式刷来刷。条件格式的主要作用：数字比较多的情况，画数据分布图；90%情况下，列不动行动；与公式结合在一起，功能更强大。
注意：通过条件格式自动产生数据分布图，一个表中最多支持3种条件格式
删除条件格式：格式—>条件格式—>删除...
条件格式范例演示：自动画格线；自动查找是否和前面的数据重复；必填项目填空后蓝底变白底，且星号去掉；销售价格和出场价格比较，进行不同颜色显示。
【案例1】标识“>50”的数据分布情况
数据源：=ROUNDUP(RAND()*100,0)函数生成一个随机数据区域块，作为分析的数据源
达到目标条件的话，改变显示形式

【案例2】大于目标的20%，小于目标值分别用不同颜色表示出来。

【案例3】录入的数据累计不能超过20，主要在公式上注意（起点固定，终点不固定）

【案例4】输入必填数据，格式变化

【案例5】录入数据后自动划线

【案例6】判断一个区域有内容，则划线（=countA（区域）>0）

十、数据分析时的复杂图表
1、达标线图表
首先做成标准柱形图，双击纵坐标，修改：刻度—交叉于：填上目标数

可以将刻度线标签修改和图案修改来进行美化图形

2、目标线图表
选中目标，拖住边框+鼠标左键

将加入的目标值右击选择图标类型—>改成散点图

双击散点—误差线X，选择显示方式和修改定值，然后对该散点和线进行美化，将散点变成白色，线变成虚线。

最后效果：

3、 目标线图表2
目标线不同的情况。
将目标变成空心柱，实际值图无框，采用双坐标的概念，然后再进行美化工作

选项—>分类间距，调整间距
双击小的坐标轴把最大刻度和右边一样。最后效果图如下：

4、 累计增长图

将累计的值次序改变

调整间距、将累计值变成白色，美化后如下：

5、动态图表
动态图表：主要通过公式（vlookup）将选择的数据先选择出来，得到需要显示的数据，做成图表；将选择出来的数据隐藏，就做出了动态图表的形式。
可以输入数据的有效性来防止输入超出范围的值。

6、动态图表
一段时间的情况（滚动条）

十一、导入外部数据
某些系统中导出数据，将其放到excel里面，如何应对导出的数据经常变化？
合并多个数据文件的方法
【案例1】无条件拿多个子文件的数据，合并成大文件
流程和步骤：新建一个excel文件（total.xls）—>数据—>导入外部数据（包括列标题）—>导入一个文件后，移到最后一行的空单元格，重复上述过程
当单个文件发生改变后，打开total.xls后，点击全部刷新，大文件的数据就可以随之更新。

如果不想要表头，点击表头数据，点鼠标右键—>数据区域属性—>包含字段名（去掉勾即可）
注意：如果目录太深了，有可能不成功；另外导入时不要将源数据文件打开。
【案例2】有条件拿数据（如，拿3月份、4月份、5月份销售报表中的龙虾的数据做报表）
数据—>导入外部数据—>新建数据库查询—>选择excel—>3月份销售数据.xls—>在条件这里选择龙虾的数据

【案例3】拿多个子表中指定条件的指定列的数据
Excel中嵌套了Microsoft Query，子表结构不一样，可以通过Microsoft Query添加或删除列。
可随时回到编辑界面

进入Microsoft Query进行编辑不同表指定的列，不用保存，直接关闭。
【案例3】动态条件来获取多个子表的数据（数据源：销售数据D.xls）
首先按照条件输入数据的格式将数据导入表格，如选择“城市=北京”的数据导入到excel中。

完成此部操作后，右击导入的数据，选择“编辑查询”——进入Microsoft Query界面，将条件字段中的城市名改为一个提示框（注意：提示框的文字要用[]括起来）

随后直接关闭Microsoft Query，选择“全部刷新”，按照如下格式选择

【案例4】复合条件查询

在此步骤，设置多个条件（根据查询的要求），设置完成后将数据导入到指定excel中。然后点击“编辑查询” ，进入Microsoft Query中，按如下格式输入

在按照单条件查询的进行设置即可
【案例5】模糊查询、选择性查询
与上述方式一致，只是在选择条件时用“包含”来实现模糊查询。Microsoft Query中查询条件公式如下；
%like’%霸力建设%’：固定格式
Like '%' & [请输入公司名] & '%'：可变格式
【案例6】关联查询（数据源：3月销售数据.xls、客户列表.xls）
根据公司名关联
可以在Microsoft Query中添加表 （如果出现无法访问，将已有的表删掉，把3月份表添加，再添加客户列表），连上关联数据（公司名称）

添加客户列表时注意：表所在目录不能太长，否则无法找到该表。

关联后选择需要的列拖到下面，直接关闭就可以了
注意：关联时，多对多是错误的，一对多可以
【案例7】包含查询
双击链接关系：可以进行连接内容的选择
选择条件区域：销售表公司名称，如果为空（Is Null），三月份没有发生过销售的记录
范例： 五月份卖过东西，但是四月份没有卖过东西的客户

直接点击取消，可直接进入Microsoft Query的页面，再进行下一步操作
添加表，编辑链接

【案例8】合计、汇总查询：3月份每个地区的总数量。（数据源：3月份销售数据，客户关系数据。地区数据在客户表中）另外，通过汇总可以去掉重复数据

十二、宏
重复劳动，用宏来做按钮。
宏录制，录制宏时选择个人宏工作簿：表示对所有工作表有效。
设置宏在菜单上的按钮如下图，还可以编辑按钮图像。

十三、将已作好的图表保存为模版
右键点击图标—>图标类型—>自定义—>选择自定义，点击添加，取名并确定
下次选中数据，然后点击图表类型-自定义-自定义，选择做好的模版