1.单元一 数据分析工具之Excel

Excel数据格式处理数据分析工具之ExcelExcel数据格式处理01Excel数据格式处理一、将文本型数字转换为数值格式本篇依旧将招聘数据作为引导来学习具体的操作。此时，“招聘人数”虽然看上去是数字，但其实是文本格式。Excel数据格式处理一、将文本型数字转换为数值格式那么，将文本格式用于计算会发生什么情况呢？右图中对“招聘人数”这一列求和，结果就是错误的。这是因为只有数值格式才能进行计算，而这一列是文本格式，无法进行计算。这时候，需要将这一列转换为数值格式。接下来，我们将介绍3种将文本转换为数值格式的方法。Excel数据格式处理一、将文本型数字转换为数值格式（一）使用“数据”-“分列”功能具体操作如下：①选中【招聘人数】一列，点击【数据】-【分列】Excel数据格式处理一、将文本型数字转换为数值格式②按照下图步骤进行选择最终计算出结果。Excel数据格式处理（二）使用value函数可以通过插入value函数的方式，将文本型数字进行转换。具体操作如下：①插入函数一、将文本型数字转换为数值格式Excel数据格式处理②新建一列作为辅助列，用于存放转换后的数值。在这个案例数据上，新建的列是Q列。在这一列的Q2单元格里输入下图中的value函数：=value（O2），表示让这一单元格的值等于O2单元格的值，如左图。输入公式后按回车键，可以看到数值结果，如右图。一、将文本型数字转换为数值格式Excel数据格式处理③把鼠标放置在O2单元格右下角，当出现十字标志时双击即可完成整列公式填充。最后使用求和函数就可以看到结果了。一、将文本型数字转换为数值格式Excel数据格式处理（三）让文本型的数字乘以1或者加减0同样，我们也通过操作来进行学习。如右图，借助辅助单元格F2，输入数字1，然后复制1所在的单元格。选中D列数字区域，然后点击鼠标右键：“选择性粘贴”-“乘”；单击“确定”一、将文本型数字转换为数值格式Excel数据格式处理如果数字有大小、多少的概念时，我们将数字处理成数值格式；但有的数字本身并无大小、多少含义，只是一串数字代码，那么就需要将数字保留成文本格式。  比如，工作中经常遇到输入的是18位的身份证号码，Excel却显示的是指数形式，当再次双击查看编辑单元格时，后3位数字已经显示为000。二、将数字格式转为文本格式Excel数据格式处理这是因为Excel对于数值，只能保留15位的精确度，所以，对于身份证的输入只能将单元格改为文本格式才能显示正确。同样，在这个案例里的“职位ID”列里的数字并无大小含义，无需运算处理，所以我们要将其处理为文本格式。接下来，依旧以案例操作的方式，来介绍三种处理方式。二、将数字格式转为文本格式Excel数据格式处理（一）应用分列工具①选中数据区域②选择“数据”选项卡③选择“分列”功能二、将数字格式转为文本格式Excel数据格式处理（一）应用分列工具④在“列数据格式”中选择“文本”⑤点击“完成”通过上述操作，即可将数字格式转为文本格式。二、将数字格式转为文本格式Excel数据格式处理（二）text函数转换法①插入“text”函数，如右图。②借助辅助列S列，在S2单元格输入=text(C2,"@")其中@代表文本格式。下图是把“职员ID”的数字处理为文本格式。二、将数字格式转为文本格式Excel数据格式处理（三）&连接一个空值完成转换在需要转换为文本格式的数字后加上：＆””，然后进行填充。具体操作如下图。二、将数字格式转为文本格式Excel数据格式处理什么是假日期呢？    日期的本质是数字，标准格式的“真”日期，能够和数字进行加减运算，也可以通过右键单元格来改变日期显示形式，但”假日期”做不到。且“假”日期是无法通过“单元格格式设置”来更改显示形式的，示例处一直显示#######三、将“假”日期转为“真”日期Excel数据格式处理接下来，将介绍两种更改日期格式的操作方法。（一）使用“数据”-“分列”功能①选中数据区域②点击【数据】③点击【分列】④列数据格式选择【日期】⑤点击【完成】三、将“假”日期转为“真”日期Excel数据格式处理（二）使用自定义格式在右图步骤4的地方手动输入编码“0000-00-00”。其中0在日期格式中代表占位符。三、将“假”日期转为“真”日期Excel数据格式处理（二）使用自定义格式点击“完成”之后结果如右图。三、将“假”日期转为“真”日期Excel重复数据删除数据分析工具之ExcelExcel重复数据删除01Excel重复数据删除一、删除重复值操作中，总会因为某种原因而录入了重复的数据。比如，单位发放劳保用品，关于哪些人领了哪些东西的登记是一份很长的Excel流水账，姓名一列频繁有重复值出现。为了解决这种重复值问题，接下来将继续使用招聘网站的数据表格，来演示处理重复值的妙招。Excel重复数据删除一、删除重复值具体操作如下：①鼠标位于“城市”列下的任意单元格，单击“数据”选项卡下方的“删除重复项”。Excel重复数据删除一、删除重复值②在弹出的对话框中单击“取消全选”，选择“城市”标签后单击“确定”即可。Excel重复数据删除二、高亮重复值具体操作如下：①选中“城市”列中的目标区域。②单击“开始”选项卡下的“条件格式”-“突出显示单元格规则”-“重复值”。Excel重复数据删除二、高亮重复值③在弹出的“重复值”对话框中为“重复值”设置突出显示的颜色后点击“确定”，“重复值”被高亮显示出来。Excel重复数据删除三、统计重复值出现次数统计重复值的方法有很多，在实际操作中，首选数据透视表。具体操作如下：①鼠标置于数据表格的任意单元格，单击“插入”选项卡下的“数据透视表”。Excel重复数据删除三、统计重复值出现次数②左方弹出如下视图Excel重复数据删除三、统计重复值出现次数③右方出现“数据透视表字段”框，我们的目标是计算“城市”字段中的重复值出现的次数，所以需要将“城市”拖入“行”和“值”计算两处。如右图所示。Excel重复数据删除四、显示不重复值具体操作如下：①鼠标置于数据表格的任意单元格，单击“数据”选项卡下的“筛选”-“高级”。Excel重复数据删除四、显示不重复值具体操作如下：②在“高级筛选”对话框中选择“列表区域”时，用鼠标去选择城市列中的目标区域。③勾选下方的“选择不重复的记录”，最后点击“确定”。Excel重复数据删除四、显示不重复值单击“确定”之后，如下图行标签所示，重复的项均被隐藏了起来，没有被删除，本质上保留了数据的完整性。拓展：还可以建立辅助列利用函数来标记“重复”和“不重复”值。此处则用IF函数去嵌套COUNTIFS函数去实现。Excel重复数据删除四、显示不重复值不仅如此，我们还可以建立辅助列利用函数来标记“重复”和“不重复”值。此处则用IF函数去嵌套COUNTIFS函数去实现。COUNTIFS函数的含义为：计算多个区域满足指定条件的单元格个数。而IF函数是逻辑判断函数，它有三个参数。第一个参数是条件，第二个参数为条件满足时返回的值，第三个参数为条件不满足时返回的值。这两个函数嵌套来使用，当A2单元格出现的次数大于1时，返回“重复”值，否则返回“不重复”值。Excel重复数据删除四、显示不重复值最后将公式填充至整列，则实现了对“城市”列单元格内容是否重复的判断。 效果如下：条件格式可以使用条件格式对数据进行颜色标记、图标标记、数据条等方式的展示，可以快速发现数据中的规律和异常。Excel条件格式数据分析工具之ExcelExcel条件格式01Excel条件格式在处理数据的时候，我们可以使用条件格式对数据进行颜色标记、图标标记、数据条等方式的展示，可以快速发现数据中的规律和异常。当表格里数据比较多时，可以使用Excel的条件格式来实现上述功能。“条件格式”就是如果单元格满足某种“条件”，那么它就以某种“格式”来呈现。Excel条件格式现在，我们将通过一份记录了“公司全名”、“公司大小”、“教育要求”、“平均薪水”、“工作年限要求”等信息的招聘Excel表格，利用条件格式，来快速判断哪家公司的平均薪水最高。Excel条件格式一、数据条数据条可以帮助我们直观的看到数据大小。具体操作如下：①选中需要设置条件格式的数据区域：如M列。②在【开始】选项卡下，单击【条件格式】按钮，在展开的下拉菜单中，单击【数据条】命令项。Excel条件格式一、数据条③在展开的选项菜单中，我们可以看到“数据条”从外观上主要分为两大类：“渐变填充”和“实心填充”。在这里选用了“实心填充”里的“浅蓝色数据条”。Excel条件格式一、数据条最终效果如右图，可以看到“平均薪水”这列有了数据条。通过数据条的长短，我们就能直观快速地判断数据的大小。Excel条件格式一、数据条除上述显示方式外，还可以有选择地显示某部分数据。比如，针对平均薪水列，我们只想对平均薪水大于等于9000且小于等于15000的用数据显示出来，而不在此范围的就不再显示。此时，就可以通过新建格式规则来实现这个想法。Excel条件格式一、数据条同样，首先选中需要进行条件格式设置的数据区域。 然后，在【条件格式】的下拉菜单中，选择【数据条】-【其它规则】，就会弹出【新建格式规则】窗口。Excel条件格式一、数据条在窗口中对数据条的显示区间及条形图外观进行设置。Excel条件格式一、数据条最终效果如右图：Excel条件格式二、色阶除了用数据条的长短来反映数据的大小，我们还可以通过颜色设置来反映数据的大小，这被称为“色阶”。具体操作如下：①选中需要设置条件格式的数据区域。②在【开始】选项卡下单击【条件格式】按钮，在它的下拉菜单中，单击【色阶】命令项。Excel条件格式二、色阶在展开的选项菜单中，可以看到Excel内置了12种色阶样式，有三色刻度的，也有双色刻度的。在这个案例演示中，使用了“绿-白色阶”，也就是说最大的数据显示为绿色，最小的数据显示为白色，中间的数据为过渡色。最终效果如右图：Excel条件格式二、色阶如果内置的这12种色阶样式都不能满足你的需求，你也可以进行自定义设置。具体操作如下：①【开始】-【条件格式】-【色阶】-【其它规则】Excel条件格式二、色阶②在弹出的【新建格式规则】窗口中，可对格式的样式进行自定义设置。比如，选择了“三色刻度”，对三色的设置，分别是：最大值设置为蓝色，中间值设置为黄色，最小值设置为浅绿色。所以，对于色阶图，从图中色阶的变化就可以很直观地反映出各个公司薪水的情况。Excel条件格式三、图标集在现实工作中，如果我们想对数据进行分类显示，比如对学生分数进行分类显示，又或者对公司的平均薪水，进行如下分类：小于5000的为一类，5000到10000为一类，10000以上的为一类。此时，对于不同区间的数据，就可以使用图标集来进行标识了。当我们看到图标，就能清楚地知道数据所处的范围。Excel条件格式三、图标集具体操作如下：①选中需要设置条件格式的单元格数据区域。②在【条件格式】的下拉菜单中，单击【图标集】命令项。在展开的选项菜单中，可以看到图标集主要分为四大类：方向、形状、标记、等级。Excel条件格式三、图标集在这个案例中，选择的是“三色交通灯”的样式。但是，从图表中可以看到，5348这样的数据，明明是比5000大，却同样也是用了红圈来表示。这并不符合我们的需求。原因是Excel默认的“图标集”显示规则是按百分比比率对数据进行分组的，所以，我们要根据自己的需要，对数值进行调整以显示外观。Excel条件格式三、图标集怎么对已设置好的条件格式进行修改呢？可以按以下步骤来进行。首先选择需要修改条件格式的单元格数据区域。  然后在【开始】选项卡下，单击【条件格式】按钮，在展开的下拉菜单中，单击【管理规则】，就会弹出【条件格式规则管理器】对话框。Excel条件格式三、图标集在此对话框中，选中需要编辑的规则，单击【编辑规则】，打开【编辑格式规则】对话框，就可以根据需要对已设置的条件格式进行修改。vlookup函数数据分析工具之Excelvlookup函数01vlookup函数当有多张表时，如何将一个excel表格的数据匹配到另一个表中？比如用身份证号码，到员工信息表里提取某个员工的对应信息。这时候就需要使用vlookup函数。它可以通过一个值，去另外一个数据里，提取相同值同行的其他数据。vlookup函数有4个参数，分别是vlookup(找什么，在哪找，第几列，是准确找还是近似找)。vlookup函数下面我们通过一个案例理解这4个参数是什么意思，以及学会如何使用这个函数解决多表关联查找的问题。现在有一份Excel表格，其中包含两张表格：表1为学生信息表，记录了全部学生的信息，包括姓名、出生年月、性别、班级；表2为三好学生表，记录了获得三好学生称号的学生姓名。现在我们想要通过VLOOKUP函数来查找三好学生的班级信息，以便更好地了解他们的学习背景和特点。vlookup函数在不会使用vlookup的时候，一般是通过手动查找来实现的。第1步，找什么。获取表2三好学生里姓名是林慕竹，表示我们要姓名是林慕竹这名学生在哪个班级。vlookup函数第2步，在哪找。因为班级信息在表1学生信息表里，所以我们在表1里找查找。查找到姓名是林慕竹的行。经过查找，我们发现在表1里有两个学生的姓名都叫做林慕竹，所以我们找到两行数据。vlookup函数第3步，第几列。找到姓名是林慕竹的这几行，班级所在的这一列。  有两个姓名是林慕竹的学生，在第4列里一个是在1班，一个是在3班。vlookup函数第4步，是准确找还是近似找。  精确查找是指从第一行开始往最后一行逐个查找。一找到匹配项就停止查询，所以返回的是找到的一个值。  当你要模糊查找的时候，它就会拼命地查遍所有的数据，返回的是最后一个匹配到的值。  如果查收的姓名只有1个，我们当然可以按照前面的步骤手动查找来操作，但是当这样查找的姓名有几万个的时候，那这种手动查找是效率很低的。  这时候Vlookup函数就派上用场了，这个函数就是上面手动查找过程的自动化实现。vlookup函数一、使用vlookup进行多表关联查询下面我们将刚才的查找过程用vlookup实现一遍，利用vlookup进行多表关联查询。首先插入函数：    1）选中要插入函数的单元格这里选中林慕竹这一行班级所在的单元格。2）然后点击菜单栏”插入“下面的插入函数。3）最后，在插入函数对话框中搜索函数中输入vlookup，然后点击转到，就可以找到这个函数。vlookup函数一、使用vlookup进行多表关联查询点击“确定”就会跳出参数设置的对话框。这里有4个参数，对应vookup函数的4个参数：找什么，在哪找，第几列，是准确找还是近似找。首先设置第1个参数：找什么。 按右图的操作步骤，选择第一列中需要匹配数据的单元格，只选中一个就可以了。这里选择姓名列下的林慕竹，表示要找到姓名是林慕竹的信息。vlookup函数一、使用vlookup进行多表关联查询其次设置第2个参数：在哪找。表示在哪个表里查找信息。此时，返回到第2张表里，选中全部数据。vlookup函数一、使用vlookup进行多表关联查询然后设置第3个参数：第几列。表示要查找的值在前面选定的查找范围里第几列。因为要返回的是表2中的班级信息，这在选定的查找范围里是第4列。这里的查找范围是前面参数2选定的范围，从选中查找范围的姓名那一列开始算起。vlookup函数一、使用vlookup进行多表关联查询最后设置第4个参数：准确找还是近似找。如果是近似匹配这个参数填1，如果是精确匹配填0。一般情况选0，表示精确匹配。  设置好这4个参数以后，点击确定。我们可以看到姓名林慕竹后面的班级是1班。vlookup函数一、使用vlookup进行多表关联查询如果想要把一列中的数据都匹配出来：鼠标放到单元格右下角，自动变成十字架形状。双击十字架，将函数应用到这一整列数据上。这样数据再多，也可以一次性搞定。但到这会发现，刚才查找2个姓名是林慕竹的学生，只返回查到的第一个值。这正是vlookup的弱点，即当有多个查找值满足条件时，只会返回从上往下找到的第一个值。vlookup函数二、使用vlookup查找重复值为了解决有重复值时对查找结果的影响，我们可以按下面的操作来避免这个问题。具体操作如下：①添加一个辅助列，将姓名和学号联合起来作为非重复的唯一字段。在学号前面插入一列，并命名为“辅助列”。在第2行单元格中输入公式：=C2&B2，这里&是将两个单元格的内容拼接在一起。将该公式向下填充，这样根据学号和姓名组合的辅助列就完成了。vlookup函数二、使用vlookup查找重复值②用辅助列作为vlookup的查找条件，就可以查找出来了。vlookup函数三、使用vlookup进行数据分组在数据处理的过程中，有时候为了分析数据的需要，会对一些数据进行分组处理，分析数据各组