_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，……） →计算所有参数的乘积 例：求下表中每专业的总收入是多少？

6.除余函数 mod(number，divisor) mod(被除数，除数） →得到两数相除的余数 例：求下表中获奖人数是否可以平分奖学金？ 得出值（余数）为0，平分，值不为0，不能平分

7.取整函数 int(number) int(数值） →将数值向下舍入取整

• INT函数是取整函数；不进行四舍五入，而是直接去掉小数部分取整；处理负数时小数位向上进位 例：求下表工资需要发多少张100元现金？

8.判断单元格是否为空 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(要查找的文本，搜索区域，起始搜索位置） →对原始数据中某个字符串进行定位，以确定其位置 例:找到下列句子中"King"的位置

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