• 职业买手爆料
  • 中立态度甄选
  • 品质消费推荐
首页 > 神技能 > Excel技巧--5个必备函数

Excel技巧--5个必备函数

作者:来源:时间:2016-06-24 15:11

熟练掌握这5大函数,走遍职场都不怕。

一、RANK函数(排名函数)

从小到大,我们每一个人都深陷各种各样的排名,考试成绩排名、KPI绩效排名、业绩排名、市场份额排名、满意度排名……各种“被排名”关系着家长、老师、领导、同事对我们的态度。那这排名是如何实现的呢?EXCEL中有一个很神奇的函数“RANK”,它能够将数字的排名单独显示在另一列,而且可以去除重名次,就是所显示的结果是有多少人就显示多少名。


RANK函数公式解析

案例:A集团公司下属18个分公司,年底要对18个分公司年度KPI和盈利亏损情况进行考评,需要了解各个分公司KPI排名及亏损情况排名。

计算方法:

(1)在D2列输入公式“=RANK(B2,$B$2:$B$19)”,其中$B$2:$B$19代表固定B2到B19这18个数据,快捷键“F4”可以固定数据区域。

(2)将鼠标移到D2单元格右下角,直到出现填充柄,双击填充柄则D3:D19自动填充了D2的公式,或者下拉填充柄至D19。

(3)用同样的方法在E2实现年度利润升序排序,注意:E2需要升序排序,order则不能为0或者为空,所以此处用1代替。

最终计算结果:


排序结果

二、VLOOKUP函数(字段匹配函数)

强大的vlookup函数是做统计分析中最常用的函数之一,因为很多字段信息都是分布在各个不同的表里面,原数据表可能没有我们需要的字段,而需要从其他数据表中获取相关的字段信息,这时候就是发挥vlookup函数作用的时候了。


VLOOKUP函数公式解析

案例:A集团公司下属18个分公司,年底要对18个分公司年度KPI和盈利亏损情况进行考评,目前18个分公司成绩在不同的表格中。


原始数据

现在需要将盈利亏损表按照分公司字段对应到年度KPI成绩表。

方法:

(1)分别打开表1和表2。

(2)在表1中C2单元格中输入公式“=VLOOKUP(A2,表2!A2:B19,2,0)”,按Enter键。输入vlookup函数第二个参数时不需要手动输入,直接选中表2中A2:B19的区域,参数将自动录入成“表2!A2:B19”,“2”代表匹配的结果是“表2!A2:B19”区域中第2列数据,“0”代表精确匹配。

(3)将鼠标移到C2单元格右下角,直到出现填充柄,双击填充柄则C3:C19自动填充了C2的公式,或者下拉填充柄至C19。

最终计算结果:


VLOOKUP函数计算结果

VLOOKUP中的V参数表示垂直方向。还有一个横向查找函数HLOOKUP,和VLOOKUP函数属于一类函数,HLOOKUP是按行查找的,VLOOKUP是按列查找的,使用方法基本一致。

三、COUNT函数(计数函数)

在数据分析中我们通常要统计数字个数,而COUNT函数的作用就是在Excel办公软件中计算参数列表中的数字项的个数。注意:只有数字类型的数据才被计数。


COUNT函数公式解析

案例:有一列数据,只有数字格式才是符合规范的,其他格式均不符合规范。现在需要计算这列数据中有多少个规范的数据。

方法:在B2单元格输入公式“=COUNT(A2:A11)”,按Enter键。

最终计算结果:4。代表A2、A4、A5、A9这4个单元格。


COUNT函数计算结果

COUNT函数只是最基本的统计分析函数,是其他计数类函数的基础。下面给大家介绍几个CONUT函数家族的其他几个成员。

1、COUNTA函数

COUNTA函数计算区域中不为空的单元格的个数。

案例:计算表格中不为空格的单元格个数。

方案:在A8单元格输入公式“=COUNTA(B2:D6)”

最终计算结果:14。代表除了C5单元格外所有的单元格个数。


COUNTA函数计算结果

2、COUNTBLANK函数

计算指定单元格区域中空白单元格的个数。

案例:计算表格中空白单元格的个数。

计算方法:在A8单元格输入公式“=COUNTBLANK(B2:D6)”

最终计算结果:1。代表C5单元格为空格。



COUNTBLANK函数计算结果

3、COUNTIF函数


COUNTIF函数公式解析

主要用法一:利用COUNTIF函数识别重复数据

案例:现有一组客户电话号码,需要通过客户回访了解客户对公司产品的看法,为不过度影响客户感知,一个号码原则上只能拨一次,所以现在需要对相同号码进行筛选。

方法:

(1)在B2单元格中输入公式“=COUNTIF(A1:A11,A2)”

(2)在C2单元格中输入公式“=COUNTIF($A$2:A2,A2)”

(3)复制公式至B3:C11区域单元格中。

(4)筛选出C列中等于1的电话号码即可找出电话号码中所有的非重复项。

最终计算结果:


COUNTIF函数计算结果

主要用法二:按照条件格式计数

案例:现有一个学生成绩表格,需要对其中及格(>=60)的同学个数进行筛选,求全班学生中的及格人数。

方法:在B15单元格输入公式“=COUNTIF(B2:B13,">=60")”

最终计算结果:


COUNTIF函数计算结果

四、IF函数(条件函数)

IF函数是EXCLE中最常见的一个函数,它可以对值和期待值进行逻辑比较。


IF函数公式解析

1、基本应用

案例:现有某一年级同学的考试成绩,学校为不让家长和学生“唯分数论”,故不下发学生的真实成绩,只以"及格"、"不及格"表示,现需判断其结果。

方法:在C2单元格输入公式“=IF(B2>=60,"及格","不及格")”

最终计算结果:


IF函数计算结果

2、高级进阶应用(if函数嵌套)

案例:现有某一年级同学的考试成绩,学校为不让家长和学生“唯分数论”,故不下发学生的真实成绩,以等级表示,80分以上"A"、60-80分“B”、60分以下“C”表示,现需判断其结果。

方法:在C2单元格输入公式“=IF(B2>=80,"A",IF(B2>=60,"B",IF(B2<60,"C",0)))”

最终计算结果:


IF函数计算结果

五、字段分列与合并函数

在数据分析的过程中,很多时候我们都需要进行数据抽取,即保留原数据表中某些字段的部分信息,组成一个新的字段。可以是截取某一个字段的部分信息——字段分列;也可以是将某几个字段合并成为一个新的字段——字段合并。

1、LEFT、RIGHT函数(字段分列函数)


LEFT、RIGHT函数公式解析

案例:现有一组电话号码,需要将电话号码前3位与后4位提取出来。

方法:

(1)在B2单元格输入公式“=LEFT(A2,3)”

(2)在C2单元格输入公式“=RIGHT(A2,4)”

最终计算结果:


LEFT、RIGHT函数计算结果

2、CONCATENATE函数


CONCATENATE函数公式解析

案例:现在一组电话号码,需要用座机进行拨打,经判断所有号码均为异地号码,即需要在所有电话号码前加0。

方法一:在B2单元格输入公式“= CONCATENATE("0",A2)”

最终计算结果:



CONCATENATE函数计算结果

方法二:

(1)增加B列,全部输入“0”

(2)在C2单元格输入公式“= CONCATENATE(B2,A2)”

最终计算结果:



CONCATENATE函数计算结果

注:合并文本和数字有两种方式,利用CONCATENATE函数和“&”(逻辑与)均可实现。

好啦,5个常用的Excel数据分析函数就为大家介绍到这里了,解释的如此详细和生动,如果大家还不点赞、关注的话,那罂粟姐姐就真的会哭的,呃……让罂粟姐姐的眼泪再飞会儿……



本文已有382人浏览加载

最新爆料