在找工作的时候, 大家很随意的就在简历上写这么一句 "熟练使用 Excel 办公软件", 可能有些人的确是熟练使用, 但罂粟姐姐相信应该有一大部分人都是 Excel 小白(写的时候自己都木有底气), 更有一些人估计从来没有用过较为深入那么一点点的 Excel 函数. 大数据时代, 基础数据分析是每个职场人必备的技能之一, 它可以把隐藏在一大批看似杂乱无章的数据背后的信息集中和提炼出来, 从而发现其内在的规律性, 帮助我们更好的做出判断和决策.
之前给大家分享了一个关于 Excel 商务图表制作的小教程, 貌似大家不是很欢迎, 所以这次罂粟姐姐盛装归来带给大家诚意满满的 Excel 函数小教程. 熟练掌握这 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 数据分析函数就为大家介绍到这里了.
来源: http://www.jianshu.com/p/6857dd063a2d