Excel表格数据分析必备函数大全

Excel表格数据分析必备函数大全。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，……）

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

例：求下表中报名总人数？

②sumif（range,criteria,[sum_range])

sumif（条件区域，条件，求和区域）

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

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

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

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

sumifs(求和区域，条件区域1，条件1，条件区域2，条件2，……）

→多条件单元格求和

例：求下表中录取人数在2000到4000的专业，学费一共是多少？

2.计数函数

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

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

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

例：求下表中共有多少个专业？

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

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

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

例：求下表中共有多少个专业？

③countif(range,criteria)

countif(计数区域，条件）

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

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

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

countifs(条件区域1，条件，条件区域2，条件，……）

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

例：求下表中录取人数不超过4000，学费超过8000的专业有几个？

⑤countblank(range)

countblank(计算区域）

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

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

3.平均函数

①average(number1，[number2]，……）

average(数值1，数值2，……）

→计算参数的算术平均值

例：求下表中的平均学费是多少？

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

averagea(计算的区域)

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

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

例：求下表中的平均成绩是多少？

③averageif(range,criteria,[average_range])

averageif（条件区域，条件，求值区域）

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

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

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

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

sumifs(求值区域，条件区域1，条件1，条件区域2，条件2，……）

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

例：求下表中报名人数大于10000，录取人数大于2000的平均学费？

4.最值函数

max/min（number1，[number2]，……）

max/min(计算区域）

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

例：求下表中最高的学费？

5.排序函数

rank(number，ref,[order])

rank(排序的目标数值，区域，逻辑值）

*逻辑值如果输入0或者不输入时，为降序排列（数值越大，排名越靠前）；

逻辑值输入非0时，为升序排列（数值越大，排名越靠后）

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

例：求下表中最热门的专业？

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

6.乘积函数

product(number1，[number2]，……）

product(数值1，数值2，……）

→计算所有参数的乘积

例：求下表中每专业的总收入是多少？

7.除余函数

mod(number，divisor)

mod(被除数，除数）

→得到两数相除的余数

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

得出值（余数）为0，平分，值不为0，不能平分

8.取整函数

int(number)

int(数值）

→将数值向下舍入取整

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

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

9.判断单元格是否为空

isblank(value)

isblank（要判断的对象）

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

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

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

逻辑函数

①if(logical_test,value_if_true,value_if_false)

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

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

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

②iferror(value, value_if_error)

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

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

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

③and(logical1,logical2, ...)

and(条件值1，条件值2，……）

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

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

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

④or(logical1,logical2, ...)

and(条件值1，条件值2，……）

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

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

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

文本函数

1.文本提取

①left/right( string, n )

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

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

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

②mid(text, start_num, num_chars)

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

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

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

2.文本替换

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

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

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

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

②replace(old_text, start_num, num_chars, new_text)

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

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

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

3.文本查找

①find（find_text,within_text,[start_num])

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

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

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

②search(find_text,within_text,[start_num])

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

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

例：找到下列句子中"King"的位置

*search函数支持模煳查找，需搭配通配符“？”使用

例：找到下列句子中"King"的位置

4.文本转换

upper/lower（text）

upper/lower（要转换的文本）

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

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

5.文本合并

concatenate(text1, [text2], ...)

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

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

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

6.文本复制

rept(text，number_times)

rept(需要重复显示的文本，重复显示的次数）

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

例：如下表，在"单元格条形图"用█表示数据

7.计算文本长度

①len(text)

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

→计算文本串的字符数

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

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

引用与查找函数

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

vlookup(查找的目标，对比的区域，参考值与对比值的列差，模煳查找/精确查找）

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

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

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

hlookup(查找的目标，对比的区域，参考值与对比值的行差，模煳查找/精确查找）

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

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

*vlookup和hlookup函数中"range_lookup"参数输入为0时，表示精确查找，输入为1时，表示模煳查找

③lookup(lookup_value,lookup_vector,result_vector)

lookup(要查找的值，查找的范围，要获得的值）

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

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

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

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

④indirect(ref_text,[a1])

indirect(引用的单元格，引用的单元格包含的引用方式）

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

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

例：如下图示：

indirect("A2") —— 加引号，文本引用——即引用A2单元格所在的文本

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

⑤match(lookup_value, lookup_array, match_type)

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

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

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

*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年数学专业的报名人数