数据分析实战技能培训

数据分析实战技能培训一、数据录入数据类型：单元格的数据类型和单元格内数据的数据类型是独立的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】相距的分钟，两个时间直接相减，结果乘14404、汇总函数：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月份表添加，再添加客户列表），连上关联数据（公司名称）添加客户列表时注意：表所在目录不能太长，否则无法找到该表。关联后选择需要的列拖到下