"

最全数据分析常用的Excel函数合集,收藏直接套用

文 | 商业智能研究 2022-08-12
阅读次数:10,897

阅读提示:

文章中与FineReport软件使用的相关内容,基于软件的V7.0旧版本编写,不代表软件最新的使用方式。
FineReport最新版免费试用:https://www.finereport.com/product/active
FineReport最新版使用教程:https://help.fanruan.com/finereport/

Excel是我们工作中经常使用的一种工具,对于数据分析来说,这也是处理数据最基础的工具。很多传统行业的数据分析师甚至只要掌握Excel和SQL即可。

对于初学者,有的时候并不需要急于苦学R语言等专业工具(当然会也是加分项),因为Excel涵盖的功能足够多,也有很多统计、分析、可视化的插件。只不过我们平时处理数据的时候很多函数都不知道怎么用。关于Excel的进阶学习,主要分为两块:一个是数据分析常用的Excel函数,另一个就是Excel分析实战练习。

本文先为大家整理了用excel数据分析时的几种常用函数,实战后面讲:

简单函数(包含加总、计数、平均、最值、排序、乘积、除余、取整)

逻辑函数(包含if、iferror、and、or)

文本函数(包含文本提取、文本查找、文本替换、文本转换及合并)

引用与查找函数(vlookup、hlookup、lookup、indirect、index、match)

简单函数

1.加总求和函数

①sum(number1,[number2],……)

sum(数值1,数值2,……)

→ 计算单元格区域中所有数值的和

例:求下表中报名总人数?

excel函数大全,excel常用函数公式大全,excel数据分析工具

②sumif(range,criteria,[sum_range])

sumif(条件区域,条件,求和区域)

*[sum_range]参数不输入时,默认[sum_range]=range

→对满足条件的单元格求和

例:求下表中录取人数超过4000的专业共有多少人?

excel函数大全,excel常用函数公式大全,excel数据分析工具

③sumifs(sum_range,criteria_1,criteria1,criteria_2,criteria2,……)

sumifs(求和区域,条件区域1,条件1,条件区域2,条件2,……)

→多条件单元格求和

例:求下表中录取人数在2000到4000的专业,学费一共是多少?

excel函数大全,excel常用函数公式大全,excel数据分析工具

2.计数函数

①count(value1,[value2],……)

count(要计数的单元格区域)

→仅计算所选区域中包含数值的单元格个数

例:求下表中共有多少个专业?

excel函数大全,excel常用函数公式大全,excel数据分析工具

②counta(value1,[value2],……)

counta(要计数的单元格区域)

→计算所选单元格中非空格的个数

例:求下表中共有多少个专业?

excel函数大全,excel常用函数公式大全,excel数据分析工具

③countif(range,criteria)

countif(计数区域,条件)

→计算所选区域中满足条件的单元格数目

例:求下表中录取人数超过4000的专业有几个?

excel函数大全,excel常用函数公式大全,excel数据分析工具

④countifs(criteria_range1,criteria1,criteria_range2,criteria2,……)

countifs(条件区域1,条件,条件区域2,条件,……)

→用于计算所选区域中同时满足多条件的单元格数目

例:求下表中录取人数不超过4000,学费超过8000的专业有几个?

excel函数大全,excel常用函数公式大全,excel数据分析工具

⑤countblank(range)

countblank(计算区域)

→计算所选区域中空单元格的数目

例:求下表中报名人数未录入的专业个数?

excel函数大全,excel常用函数公式大全,excel数据分析工具

3.平均函数

①average(number1,[number2],……)

average(数值1,数值2,……)

→计算参数的算术平均值

例:求下表中的平均学费是多少?

excel函数大全,excel常用函数公式大全,excel数据分析工具

②averagea(value1,[value2],……)

averagea(计算的区域)

→计算所选区域非空单元格的算术平均值

*参数可以是文本,计算时文本&False值相当于0,True值相当于1

例:求下表中的平均成绩是多少?

excel函数大全,excel常用函数公式大全,excel数据分析工具

③averageif(range,criteria,[average_range])

averageif(条件区域,条件,求值区域)

**[average_range]参数不输入时,默认[average_range]=range

→求满足条件的单元格平均数

例:求下表中录取人数大于2000的平均学费是多少?

excel函数大全,excel常用函数公式大全,excel数据分析工具

④averageifs(average_range,criteria_1,criteria1,criteria_2,criteria2,……)

sumifs(求值区域,条件区域1,条件1,条件区域2,条件2,……)

→多条件单元格的算术平均值

例:求下表中报名人数大于10000,录取人数大于2000的平均学费?

excel函数大全,excel常用函数公式大全,excel数据分析工具

4.最值函数

max/min(number1,[number2],……)

max/min(计算区域)

→求所选单元格中数值的最大值/最小值(不含文本)

例:求下表中最高的学费?

excel函数大全,excel常用函数公式大全,excel数据分析工具

5.排序函数

rank(number,ref,[order])

rank(排序的目标数值,区域,逻辑值)

*逻辑值如果输入0或者不输入时,为降序排列(数值越大,排名越靠前);

逻辑值输入非0时,为升序排列(数值越大,排名越靠后)

→求某数值在一列数字中相较于其他数字的大小排名

例:求下表中最热门的专业?

excel函数大全,excel常用函数公式大全,excel数据分析工具

排序得排名第一的计算机专业为最热门专业

6.乘积函数

product(number1,[number2],……)

product(数值1,数值2,……)

→计算所有参数的乘积

例:求下表中每专业的总收入是多少?

excel函数大全,excel常用函数公式大全,excel数据分析工具

7.除余函数

mod(number,divisor)

mod(被除数,除数)

→得到两数相除的余数

例:求下表中获奖人数是否可以平分奖学金?

excel函数大全,excel常用函数公式大全,excel数据分析工具

得出值(余数)为0,平分,值不为0,不能平分

8.取整函数

int(number)

int(数值)

→将数值向下舍入取整

* INT函数是取整函数;不进行四舍五入,而是直接去掉小数部分取整;处理负数时小数位向上进位

例:求下表工资需要发多少张100元现金?

excel函数大全,excel常用函数公式大全,excel数据分析工具

9.判断单元格是否为空

isblank(value)

isblank(要判断的对象)

→检查是否引用了空单元格,返回true或false

例:根据成绩记录查看某生是否缺考

excel函数大全,excel常用函数公式大全,excel数据分析工具

解读:isblank返回为空值,则为缺考,否则显示考试成绩

逻辑函数

①if(logical_test,value_if_true,value_if_false)

if(计算条件的表达式或值,满足条件返回true,否则返回false)

→根据指定条件来判断其“真”(TRUE)、“假”(FALSE),根据逻辑计算的真假值,从而返回相应的内容。可以使用函数 IF 对数值和公式进行条件检测

例:根据业绩评定考核等级

excel函数大全,excel常用函数公式大全,excel数据分析工具

②iferror(value, value_if_error)

iferror(检查是否存在错误的参数,公式的计算结果为错误时要返回的值)

→如果公式的计算结果为错误,返回指定的值;否则将返回公式的结果。使用 IFERROR 函数来捕获和处理公式中的错误

例:检测下表A列数值除以B列数值的正确性

excel函数大全,excel常用函数公式大全,excel数据分析工具

③and(logical1,logical2, …)

and(条件值1,条件值2,……)

→所有参数的逻辑值为真时,返回TRUE;只要有一个参数的逻辑值为假,则返回 FALSE

例:找出以下成本低于80万,业绩大于100万的营销人员

计算得满足条件的营销人员为李四

excel函数大全,excel常用函数公式大全,excel数据分析工具

④or(logical1,logical2, …)

and(条件值1,条件值2,……)

→在其参数组中,任何一个参数逻辑值为 TRUE,返回 TRUE;所有参数的逻辑值为 FALSE,才返回 FALSE

例:找出以下成本低于50万,业绩大于100万的营销人员

excel函数大全,excel常用函数公式大全,excel数据分析工具

计算得除张三外其他营销人员均满足条件

文本函数

1.文本提取

①left/right( string, n )

left(区域,返回从左/右起始的几个字符)

→用于从一个文本字符串的第一个字符开始返回指定个数的字符

例:从以下诗句提取左边的7个字符

excel函数大全,excel常用函数公式大全,excel数据分析工具

②mid(text, start_num, num_chars)

mid(提取的范围,提取的文本开始的位置,提取几个字符)

→从一个字符串中截取出指定数量的字符

例:从下面诗句中提取清泪2个字符

excel函数大全,excel常用函数公式大全,excel数据分析工具

2.文本替换

①substitute(text,old_text,new_text,[instance_num])

substitute(要替换的单元格,老文本,新文本,从第几个开始替换)

→在某一文本字符串中替换指定的文本

例:找出下面诗句中的“奈何”,然后替换成“不要”

excel函数大全,excel常用函数公式大全,excel数据分析工具

②replace(old_text, start_num, num_chars, new_text)

replace(要替换的单元格,替换目标开始的位置,替换的字符数,新的文本)

→在某一文本字符串中替换指定位置处的任意文本

例:找出下面诗句中的“奈何”,然后替换成“不要”

excel函数大全,excel常用函数公式大全,excel数据分析工具

3.文本查找

①find(find_text,within_text,[start_num])

find(要查找的文本,搜索区域,起始搜索位置)

→对原始数据中某个字符串进行定位,以确定其位置

例:找到下列句子中”洪荒”的位置

excel函数大全,excel常用函数公式大全,excel数据分析工具

②search(find_text,within_text,[start_num])

search(要查找的文本,搜索区域,起始搜索位置)

→可在第二个文本字符串中查找第一个文本字符串,并返回第一个文本字符串的起始位置的编号,该编号从第二个文本字符串的第一个字符算起

例:找到下列句子中”King”的位置

excel函数大全,excel常用函数公式大全,excel数据分析工具

*search函数支持模糊查找,需搭配通配符“?”使用

例:找到下列句子中”King”的位置

excel函数大全,excel常用函数公式大全,excel数据分析工具

4.文本转换

upper/lower(text)

upper/lower(要转换的文本)

→将文本字符串中的所有小写字母转换成大写字母/将文本字符串中的所有大写字母转换成小写字母

例:将下列文本的小写字母转化为大写字母

excel函数大全,excel常用函数公式大全,excel数据分析工具

5.文本合并

concatenate(text1, [text2], …)

concatenata(要联接的第一个字符串,

→将多个文本字符串合并为一个文本字符串

例:上面表格中单元格的文字合成一个新的句子

excel函数大全,excel常用函数公式大全,excel数据分析工具

6.文本复制

rept(text,number_times)

rept(需要重复显示的文本,重复显示的次数)

→按照定义的次数重复显示文本,相当于复制文本

例:如下表,在”单元格条形图”用█表示数据

excel函数大全,excel常用函数公式大全,excel数据分析工具

7.计算文本长度

①len(text)

len(要计算字符长度的文本)

→计算文本串的字符数

例:判断如下表格中身份证号录入是否正确

excel函数大全,excel常用函数公式大全,excel数据分析工具

计算得出李四的身份证号码长度为20,录入不正确

引用与查找函数

①vlookup(lookup_value,table_array,col_index_num,[range_lookup])

vlookup(查找的目标,对比的区域,参考值与对比值的列差,模糊查找/精确查找)

→纵向查找函数,按列查找,最终返回该列所需查询列序所对应的值

例:根据下图右表标准评定左表学员的等级

excel函数大全,excel常用函数公式大全,excel数据分析工具

②hlookup(lookup_value,table_array,row_index_num,[range_lookup])

hlookup(查找的目标,对比的区域,参考值与对比值的行差,模糊查找/精确查找)

→横向查找函数,按行查找,最终返回该行所需查询行序所对应的值

例:根据下图上面表标准评定下面表格中学员的等级

excel函数大全,excel常用函数公式大全,excel数据分析工具

*vlookup和hlookup函数中”range_lookup”参数输入为0时,表示精确查找,输入为1时,表示模糊查找

③lookup(lookup_value,lookup_vector,result_vector)

lookup(要查找的值,查找的范围,要获得的值)

→在指定区域内查询指定内容所对应的匹配区域内单元格的内容

例:根据下图右表标准评定左表学员的等级

excel函数大全,excel常用函数公式大全,excel数据分析工具

*当引用单元格的值大于匹配单元格的值时,会按匹配单元格最接近引用单元格的最大值来返回结果。

如上图画圈所示,所以一般用时得小心使用,以免错误。

④indirect(ref_text,[a1])

indirect(引用的单元格,引用的单元格包含的引用方式)

→间接引用,立即对引用进行计算,并显示其内容

当需要更改公式中单元格的引用,而不更改公式本身,可使用此函数。

例:如下图示:

excel函数大全,excel常用函数公式大全,excel数据分析工具 excel函数大全,excel常用函数公式大全,excel数据分析工具

indirect(“A2”) —— 加引号,文本引用——即引用A2单元格所在的文本

indirect(E1) —— 不加引号,地址引用——即引用E1单元格对应单元格所在的文本

⑤match(lookup_value, lookup_array, match_type)

match(要查找的单元格,含要查找值的连续单元格范围,查询的指定方式)

→在指定区域内按指定方式查询与指定内容所匹配的单元格位置

例:确定下图中“2012年”在第几列,”01数学”在第几行

excel函数大全,excel常用函数公式大全,excel数据分析工具

*match函数的查询指定方式分三种:-1,0,1

为1时,查找≤目标单元格的最大数值在查找范围中的位置,查找范围必须按升序排列。

如果lookup_array的值均小于或等于lookup_value,则返回数组最后一个值的位置;如果lookup_array的值均大于lookup_value,则返回#N/A;

为0时,查找=目标单元格的第一个数值,查找范围按任意顺序排列;

为-1时,查找≥目标单元格的最小数值在查找范围中的位置,lookup_array必须按降序排列。

如果lookup_array的值均大于或等于lookup_value,则返回数组最后一个值的位置;如果lookup_array的值均小于lookup_value,则返回#N/A。

⑥index(array,row_num,column_num)

index(单元格区域,第几行,第几列)

→返回特定行和列交叉处单元格的引用

例:求下表中2012年数学专业的报名人数

excel函数大全,excel常用函数公式大全,excel数据分析工具

比如,目前主流的软件——finereport,它小到填报、查询、部署、集成,大到可视化大屏、dashboard驾驶舱,应有尽有,功能很强大。最重要的是,因为这个工具,整个公司的数据架构都可以变得规范,下一步就是构建企业的大数据平台了。而且它是java编写的,支持二次开发,类Excel的设计器,无论是IT还是业务,上手都很简单:编辑sql优化、数据集复用简直都是小case,大大降低了报表开发的门槛。在企业中被关注最多的数据安全方面,FineReport支持多人同时开发同一套报表,并通过模板加锁功能防止编辑冲突;通过数据分析权限控制,保障数据安全。

管理驾驶舱指南,业务指标,数据分析报表需要展现大量的数据和文字,提高信息传递的效率

报表产品更多介绍:www.finereport.com


免费试用FineReport

本月阅读量最高的10篇文章

售前咨询

服务热线

400-811-8890转1

售后咨询

在线QQ

800049425

服务热线

400-811-8890转2

投诉建议

总裁办24H投诉

173-127-81526