Excel高级应用教程:数据处理与数据分析
为一名合格的数据分析师或者说一名称职的数据挖掘领域从业者,大家肯定都耳熟能详的认为其必须具有如下基本技能:
** 一、熟练的掌握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函数讲解 **
** 功能 ** ** **
V lookup按 列查找 的方式从指定数据表区域的 最左列 查找特定数据,它能够返回查找区域中与找到单元格位于 相同行不同列 的单元格内容
。
** 格式 ** ** **
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相结合查询数据的方法
一、Index和Match相结合查询数据的方法
一、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区域所示。
图3.1 班级成绩分析
对于对各班的考试情况进行简单的统计分析,如果是在数据库里面,就是一段简单的SQL代码,如统计上机平均成绩:
select avg(上机成绩)
from database
where 班级=’0320302’;
在Excel里面,这个就可以借助D函数实现异曲同工之效。
** 案例3.1解决方案如下: **
(1)在K13中输入计算上机平均成绩的公式:
=DAVERAGE(A4:H227,"上机成绩",J12:J13)
(2)在M13输入计算综合平均成绩的公式:
=DAVERAGE(A4:H227,"综合成绩",J12:J13)
(3)在K15输入计算缺考人数的公式:
=DCOUNTA(A4:H227,"期末考试成绩",J12:J13)-DCOUNT(A4:H227,"期末考试成绩",J12:J13)
(4)在M15输入计算最高成绩的公式:
=DMAX(A4:H227,"期末考试成绩",J12:J13)
(5)在K17输入计算最低成绩的公式:
=DMIN(A4:H227,"期末考试成绩",J12:J13)
(6)在M17输入计算考试人数的公式:
=DCOUNT(A4:H227,"淘汰率为4%下的成绩",J12:J13)
** 结束语: **
本文主要参考杜茂康老师编写的《Excel与数据处理(第3版)》,有兴趣的同学可以购买此教材进行更深入的Excel学习。
作者: 甘华来; 转自:众安数盟;
** END **
** 版权声明:本号内容部分来自互联网,转载请注明原文链接和作者,如有侵权或出处有误请和我们联系。 **
关联阅读:
** 原创系列文章: **
** [ 1:从0开始搭建自己的数据运营指标体系 ** **
** [ 2 :从0开始搭建自己的数据运营指标体系(定位篇)
** [ 3 :从0开始搭建自己的数据运营体系(业务理解篇)
**[ 4 :数据指标的构建流程与逻辑
5 : **[ 系列 :从数据指标到数据运营指标体系
**
6: **** [ 实战 :为自己的公号搭建一个数据运营指标体系
7: **** **[ 从0开始搭建自己的数据运营指标体系(运营活动分析)
**
** 数据运营 关联文章阅读: **
[ 运营入门,从0到1搭建数据分析知识体系
[ 推荐 :数据分析师与运营协作的9个好习惯
[ 干货 :手把手教你搭建数据化用户运营体系
[ 推荐 :最用心的运营数据指标解读
[ 干货 : 如何构建数据运营指标体系
[ 从零开始,构建数据化运营体系
[ 干货 :解读产品、运营和数据三个基友关系
[ 干货 :从0到1搭建数据运营体系
数据分析、数据产品 关联文章阅读:
[ 干货 :数据分析团队的搭建和思考
[ 关于用户画像那些事,看这一文章就够了
[ 数据分析师必需具备的10种分析思维。
[ 如何构建大数据层级体系,看这一文章就够了
[ 干货 : 聚焦于用户行为分析的数据产品
[ 如何构建大数据层级体系,看这一文章就够了
[ 80%的运营注定了打杂?因为你没有搭建出一套有效的用户运营体系
[ 从底层到应用,那些数据人的必备技能
[ 读懂用户运营体系:用户分层和分群
[ 做运营必须掌握的数据分析思维,你还敢说不会做数据分析