数据实战技能培训excel使用技能提高资料.docx
《数据实战技能培训excel使用技能提高资料.docx》由会员分享,可在线阅读,更多相关《数据实战技能培训excel使用技能提高资料.docx(29页珍藏版)》请在冰豆网上搜索。
![数据实战技能培训excel使用技能提高资料.docx](https://file1.bdocx.com/fileroot1/2023-1/23/10359e62-6068-446f-a5a9-80b030895be8/10359e62-6068-446f-a5a9-80b030895be81.gif)
数据实战技能培训excel使用技能提高资料
数据分析实战技能培训
Office技能帮助QQ群:
24274733(已满)31366936
讲师:
陈理
一、数据录入
数据类型:
单元格的数据类型和单元格内数据的数据类型是独立的
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中嵌套了MicrosoftQuery,子表结构不一样,可以通过MicrosoftQuery添加或删除列。
可随时回到编辑界面
进入MicrosoftQuery进行编辑不同表指定的列,不用保存,直接关闭。
【案例3】动态条件来获取多个子表的数据(数据源:
销售数据D.xls)
首先按照条件输入数据的格式将数据导入表格,如选择“城市=北京”的数据导入到excel中。
完成此部操作后,右击导入的数据,选择“编辑查询”——进入MicrosoftQuery界面,将条件字段中的城市名改为一个提示框(注意:
提示框的文字要用[]括起来)
随后直接关闭MicrosoftQuery,选择“全部刷新”,按照如下格式选择
【案例4】复合条件查询
在此步骤,设置多个条件(根据查询的要求),设置完成后将数据导入到指定excel中。
然后点击“编辑查询”,进入MicrosoftQuery中,按如下格式输入
在按照单条件查询的进行设置即可
【案例5】模糊查询、选择性查询
与上述方式一致,只是在选择条件时用“包含”来实现模糊查询。
MicrosoftQuery中查询条件公式如下;
%like’%霸力建设%’:
固定格式
Like'%'&[请输入公司名]&'%':
可变格式
【案例6】关联查询(数据源:
3月销售数据.xls、客户列表.xls)
根据公司名关联
可以在MicrosoftQuery中添加表(如果出现无法访问,将已有的表删掉,把3月份表添加,再添加客户列表),连上关联数据(公司名称)
添加客户列表时注意:
表所在目录不能太长,否则无法找到该表。
关联后选择需要的列拖到下面,直接关闭就可以了
注意:
关联时,多对多是错误的,一对多可以
【案例7】包含查询
双击链接关系:
可以进行连接内容的选择
选择条件区域:
销售表公司名称,如果为空(IsNull),三月份没有发生过销售的记录
范例:
五月份卖过东西,但是四月份没有卖过东西的客户
直接点击取消,可直接进入MicrosoftQuery的页面,再进行下一步操作
添加表,编辑链接
【案例8】合计、汇总查询:
3月份每个地区的总数量。
(数据源:
3月份销售数据,客户关系数据。
地区数据在客户表中)另外,通过汇总可以去掉重复数据
十二、宏
重复劳动,用宏来做按钮。
宏录制,录制宏时选择个人宏工作簿:
表示对所有工作表有效。
设置宏在菜单上的按钮如下图,还可以编辑按钮图像。
十三、将已作好的图表保存为模版
右键点击图标—>图标类型—>自定义—>选择自定义,点击添加,取名并确定
下次选中数据,然后点击图表类型-自定义-自定义,选择做好的模版