返回目录:excel表格制作
Excel中有特别多的公式,一篇教程很难穷尽所有的公式,本篇文章主要有以下几个目的:
1. 介绍Excel中公式的类别
包括日期与时间函数、逻辑函数、数学函数、统计函数、财务函数、查找与引用函数、文本函数、信息函数和数据库函数等。
2. 理解函数的用途
很多时候我们并不是不想用公式,只是不知道能不能用函数、使用哪一类函数、怎么使用函数等。通过几个简单的数据处理例子,我们可以粗略掌握常用函数的用法。
日期函数本篇文章默认大家知道如何使用Excel公式,直接介绍常用的函数
1、DATE(year,month,day)
设置单元格为“日期”格式后会显示日期
2、DATEVALUE(data_text)
给出日期形式的日期编号
3、TODAY():给出当前日期
NOW():给出当前日期和时间
4、YEAR():给出日期的年份
同样的还有MONTH()、WEEKDAY()、DAY()
时间函数
1、TIME(hour, minute, second)
将输入的秒、分和时转化成时间格式,但是要注意如果参数超过范围的话会自动进位,比如如果秒输入70的话,会自动转化为1分和16秒。
2、MINUTE():给出当前值的分钟数
同样的还有SECOND()
逻辑函数
1、IF(logical_test, value_if_true, value_if_false)
应该是最常用的函数了,根据测试条件的FALSE或者TRUE执行不同的操作
2、AND(logical1, logical2, ...)
对多个逻辑值进行交集运算
3、OR(logical1, logical2, ...)
对多个逻辑值进行并集运算
4、NOT(logical)
对逻辑值求反
数学函数
1、ABS(number):求绝对值
2、CEILING(number, significance)
按照绝对值增大的方向向上舍入,如果数字已经为参数significance的倍数则不进行舍入
同样的还有FLOOR(number, significance)
3、COMBIN(number, number_chosen)
统计学上排列组合的公式,表示:在给定number个数的集合中抽取number_chosen个对象的组合数
4、EXP(number):返回e的n次幂
同样的有POWER(number, power):返回number的power次方
5、FACT(number):给出number的阶乘,超过170则无法计算
6、LN(number):求number的自然对数
LOG(number, base):以base为底求number的对数
LOG10(number):以10为底求number的对数
7、MOD(number, divisor):返回number除以divisor后的余数
8、RAND():返回0到1之间的随机数
9、ROUND(number, num_digits):返回number指定位数取整后的数字
同样的还有ROUNDDOWN(number, num_digits)、ROUNDUP(number, num_digits)
10、SUM(number1, number2, ...):加总
11、SUMIF(range, criteria, sum_range):给定条件criteria下的区域加总,相当于SUM和IF的结合
12、TRUNC(number, num_digits):按照小数位数num_digits对数字number进行截尾取整
三角函数
像正三角函数、反三角函数和弧度转换函数等,一般很少用到,用到的时候再百度即可
数组函数
1、MDETERM(array):计算行列式的值
如下图计算一个经典的对角行列式:
2、MMULT(array1, array2):计算矩阵乘积
3、MINVERSE(array):计算矩阵的逆
4、SUMPRODUCT(array1, array2, array3, ...):计算数组间对应的元素相乘结果之和
同样的有:
SUMX2MY2(array_x, array_y):计算数组间对应元素平方差之和
SUMX2PY2(array_x, array_y):计算数组间对应元素的平方和之和
SUMXMY2(array_x, array_y):计算数组间对应元素的之差的平方和
统计函数
1、AVERAGE(number1, number2, ...):计算平均值
AVERAGEA(value1, value2, ...):计算所有非空单元格的平均值(文本作为0处理)
2、COUNT(value1, value2, ...):计算包含数值的单元格个数
COUNTA(value1, value2, ...):计算非空值单元格的个数
3、COUNTBLANK(range):计算空白单元格的个数
4、COUNTIF(range, criteria):计算单元格区域内满足条件的单元格个数
5、GROWTH(known_y's, konwn_x's, new_x's, const):根据历史数据预测新的指数增长值,如:
6、LARGE(array, k):返回数组中第k个大的值
同样的,有SMALL(array, k)
7、MAX(number1, number2, ...):返回一组数值数据中的最大值
MAXA(value1, value2, ...):计算非空单元格中的最大值
8、MEDIAN(number1, number2, ...):给出一组数值集合的中位数
同样的,MODE(number1, number2, ...):给出一组数值集合的众数
MIN(number1, number2, ...):给出一组数值集合的最小值
9、RANK(number, ref, order):给出一个数字在数值列表中的排序
财务函数
如果不是金融机构的数据分析的话,很少会用到Excel中提供的财务函数,这里暂时不提了。大概包括计算折旧的函数、计算本金和利息的函数、计算投资的函数、计算报酬率的函数和计算证券的函数。
查找与引用函数
1、ADDRESS(row_num, column_num, abs_num, a1, sheet_text):对其他单元格的引用
类似的函数还有很多,但一般不会用到就不细讲了。
2、HLOOKUP(lookup_value, table_array, row_index_num, range_lookup):在表格或数值数组的首行查找指定的数值,并由此返回指定行处与该数值同一列的数值,举个例子:
3、INDEX(array, row_num, colnum_num):返回表或者区域中值或值的引用,具体有两种用法:
4、LOOKUP(lookup_value, lookup_vector, result_vector):返回向量或者数组中的数值,类似于数据库中的表连接操作
5、MATCH(lookup_value, lookup_array, match_type):返回在指定方式下与指定数值相匹配的数组中元素的相应位置。具体的用法比较复杂,可以需要的时候再查阅。
6、OFFSET(reference, rows, cols, height, width):以指定的引用为参考系,通过给定偏移量得到新的引用。
其中height指返回的行数,width指返回的列数。
7、VLOOKUP(lookup_value, table_array, col_index_num, range_lookup):在表格或数值数组的首列查找指定的数值,并由此返回指定列处与该数值同一行的数值。
需要注意的是:当比较值位于数据表首列时,可以使用VLOOKUP,当比较值位于数据表首行时,可以使用HLOOKUP。
写在最后
本篇文章旨在为大家使用Excel的函数查缺补漏,关键还是得多看多用方能熟能生巧。