Excel高级应用教程：数据处理与数据分析

2018 年 5 月 25 日 数据分析

为一名合格的数据分析师或者说一名称职的数据挖掘领域从业者，大家肯定都耳熟能详的认为其必须具有如下基本技能：

一、熟练的掌握SQL、Hive等；

二、R、SAS、Python等至少精通其中一种。

但往往大家忽视了最基本的技能要求：使用Excle进行数据处理与数据分析的能力。大家可以仔细回想下自己公司里面，excel玩的很6的人绝对不是数据部门的人，而往往是业务部门或财务部门的同事。Excel作为数据分析工具，可以分为两大部分，一是Excel的数据处理能力，一是Excle的高级数据分析能力。在小数据量下（注：大数据量下会存在处理效率的问题），Excel完全可以像R、SAS等统计软件进行假设检验、相关分析、回归分析等数据分析与数据建模工作，这就是Excel的高级数据分析功能。本文作为Excel使用介绍的开篇，先和大家一起分享Excel的高级数据处理方法。

一

利用Vlookup函数进行数据查找

在分享这个函数前，我们先来思考如下一个案例：

【案例1.1】假设所得税的税率如下图1.1区域所示。其中的含义是：

0~500的税率为0%，

500~1000的税率为1%，

1000~1500的税率为3%

……，4000以上的税率为20%

问题：根据职工收入如何快速计算每位职工应缴的所得税？

图1.1 工资所得税计算

计算所得税的关键就在于根据收入找到其对应的所得税率。肯定有同学会说，这个简单，直接使用if函数就可以很方便的解决。诚然，if函数确实可以解决这个问题，但实际操作起来的时候，你会发现需要嵌套多个if函数方可。如果分段较多的话，if函数进行操作很不方便，因为很可能到最后你都不记得到底嵌套了多少层if函数。对于这个问题，Vlookup函数可以快速便捷的解决。

>>>>

Vlookup函数讲解

功能

Vlookup按列查找的方式从指定数据表区域的最左列查找特定数据，它能够返回查找区域中与找到单元格位于相同行不同列的单元格内容。

格式

Vlookup (x, table, n, f)

其中，x是要查找的值；table是一个单元格区域；n中table区域中要返回的数据所在列的序号。n=1时，返回 table 第1列中的数值；n=2时，返回 table 第2列中的数值；以此类推。f是一个逻辑值，表示查找的方式。当其为true（或1）时，表示模糊查找；当它为false（或0）时，表示精确查找。

说明

Vlookup函数在table区域的第1列中查找值为x的数值，如果找到，就返回与找到数据同行第n列单元格中的数据。当f为true时，table的第1列数据必须按升序排列，否则找不到正确的结果；当f为false时，table的第1列数据不需要排序。

注意

①如果Vlookup函数找不到x，且f=true，则返回小于等于x的最大值。

②如果x小于table第1列中的最小值，Vlookup函数返回错误值“#N/A”。

③如果Vlookup函数找不到x且f=FALSE，Vlookup函数返回错误值“#N/A”。

>>>>

案例讲解

（1）用Vlookup进行模糊查找

前面一开头提出的计算所得税那个问题，就可以使用Vlookup的模糊查找进行完美解决（具体方法见图1.2）。

图1.2 Vlookup进行模糊查找

（2）用Vlookup进行精确查找

精确查找就是指查找数据完全匹配的查找，Vlookup函数具有此项功能。在大表中查找特定数据，或查找不同工作表中的数据，特别是工作表数据较多， Vlookup函数显得非常有效。

【案例1.2】某校某专业期末考试的数据库成绩表如图的A:H列所示。由于人数较多，要查看某个同学的成绩非常困难。希望能按学号进行查找，即在K5输入某个学号后，就能自动显示出该学号所对应的姓名和各种成绩，如图1.3的J4:M16所示。

图1.3 个人成绩查询

案例解决方法如下：

（1）在M5中输入公式：=VLOOKUP(K5,A5:H227,2,0)

（2）在L6中输入公式：=VLOOKUP(K5,A5:H227,3,0)

（3）在L7中输入公式：=VLOOKUP(K5,A5:H227,4,0)

（4）在L9中输入公式：=VLOOKUP(K5,A5:H227,5,0)

（5）在L11中输入公式：=VLOOKUP(K5,A5:H227,6,0)

（6）在L13中输入公式： =VLOOKUP(K5,A5:H227,7,0)

（7）在L15中输入公式： =VLOOKUP(K5,A5:H227,8,0)

二

Index和Match相结合查询数据的方法

一、Index和Match相结合查询数据的方法

同样，在了解Index和Match函数前，我们先来思考如下一个案例。

【案例2.1】某地域中各县的蔬菜销售单价表如图2.1的A4:J18区域所示，希望能够快捷地查找到某地某蔬菜的单价。最好是输入地名和蔬菜名，就能看到对应的蔬菜单价，如图2.1的B1:D3区域所示。

图2.1 蔬菜单价查询

对于这种二维查找的问题，像前面的vlookup函数或者大家熟悉的lookup函数是不能做到的，这时候就需要match函数和Index函数结合起来使用了。用一句简单话来讲，macth函数主要职责就在于定位，Index函数主要职责就在于根据match提供的位置信息去指定区域“抓人”（取数）。Index函数和Match函数这两个搭档，就很像狙击手里面第一狙击手和第二狙击手，一个负责定位和观察，一个负责精准狙击。

>>>> Match函数使用讲解

Match函数提供了比lookup（或Vlookup、Hlookup）函数更多的灵活性，它可以在工作表的一行（或一列）中进行数据查找，并返回数据在行（或列）中的位置。

如果需要找出数据在某行（或某列）的位置，就应该使用Match函数而不是Lookup函数。

在多数情况下，Match函数的结果并不是所需要的最终答案，而是作为lookup（Vlookup，Hlookup）的第3个参数或作为Index函数的参数。

Match格式

Match ( x， r，f )

其中x是要查找的数值，r可以是一个数组常量，或某列（或行）连续的单元格区域，其中可能包含有要查找的x。f用于指定match的查找方式，它可以是-1，0或1。

功能

Match(x，r，f)表示的意思是：在数组或连续的单元格区域r中查找x，并返回x在r中的位置编号。当f为0是，match进行精确查找，当f为1（或-1）时，match进行模糊查找。

说明

f=-1时，r必须按降序排列，查找大于或等于 x的最小数值

f=0时，r 不必排序，查找等于x的第一个数值

f=1时，r必须按升序排列，查找小于或等于x的最大数值

>>>>

Index函数使用讲解

格式

Index(Area，r，c，n)

其中，Area是1个或多个单元格区域；r是某行的行序号，c是某列的列序号，该函数返回指定的行与列交叉处的单元格引用。如果r等于0，则返回整行单元格引用，如果c等于0，则返回整列单元格引用。

当Area包括多个单元格区域时，n=1就表示结果来自于Area中的第1个区域，n=2表示结果来源于第2个单元格区域……。如果省略n表示结果来源于第1个单元格区域。

功能

Index（Area，r，c，n）的功能是返回Area中第n个单元格区域中的r行，c列交叉处的单元格引用。

>>>>案例讲解

前面提到的案例2.1，利用Index和Match函数结合起来可以很快速的进行解决，如下图2.2。

图2.2 蔬菜单价查询

三

D函数查询数据的方法

如果能把Excel里面某个区域里面的数据看成是数据库中一张表，在Excel里面对数据进行数据库里面SQL一样的操作该多好。

在Excel中，数据库是指每列数据都有标题的数据表。Excel提供大约12个专用数据库函数来简化这种数据表的数据统计和数据查找工作，这些函数都以D开头，所以也称为D函数。

D函数有相同的调用形式，相同参数表，格式如下：

Dname(database,field,criteria)

其中的Dname是函数名，它可以是Dsum、Daverage、Dget、Dcount、Dcounta、Dmax、Dmin等。各函数的功能如其名字所示，Dsum求总和，Daverage求平均数，Dget查找数据，Dcount统计数字个数，Dcounta统计文本和数据的个数，Dmax求最大数，Dmin求最小数。

database是一个单元格区域，要求该区域中的每列数据都必须有标题；field是database区域中某列数据的列标题（称为字段，出现在字符串中）；criteria称为条件区域，它与高级筛选条件区域的含义和构造方法完全相同。

【案例3.1】某校某专业共有224名学生，某次期末考试的“数据库系统应用”课程的成绩表如图所示。现在要查找每位学生的成绩，希望输入学号后，就能够得到该生的各种详细数据，如图3.1的J1:M8区域所示。此外，还希望对各班的考试情况进行简单的统计分析，能够随时查看各班的考试人数，最高成绩，高低成绩，及缺考人数等，如图3.1的J10:N17区域所示。