数据分析实战技能培训-excel使用技能提高概览

数据分析实战技能培训Office技能帮助QQ群：（已满） 讲师：陈理一、数据录入数据类型：单元格的数据类型和单元格内数据的数据类型是独立的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（），01之间的随机数【案例1】头60s算3毛钱，后面每6s算1毛钱，不足6s算6s=IF(通话时长透视表外部数据获取数据如果出现以上情况，将选项中的系统表选中。选中表格，生成透视表。透视表有四个部分：页字段、行字段（汇总依据）、列字段（汇总依据），中间（汇总）；对于汇总后的数据还可以修改和刷新： 可以对汇总数据部分点击右键，选择字段设置； 双击汇总数据可以看明细 数据透视表相当于数据库的交叉表查询 透视表位置选择右键，选择“刷新”（当数据源改变时可以直接刷新） 如果想周期更新数据透视表，做法如下图。【案例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月份销售报表中的龙虾的数据做报表）数据导入外部数据新建数据库查询选择excel3月份销售数据.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的页面，再