函数
SUMIFS函数
用于计算其满足多个条件的全部参数的总量
语法:SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
COUNTIFS函数
计算多个区域中满足给定条件的单元格的个数
语法:countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)
VLOOKUP函数
用于在数据表中进行垂直查找的函数
语法:VLOOKUP(lookup_value,table_array, col_index_num, range_lookup)
IF函数
如果指定条件的计算结果为TRUE,IF函数将返回某个值;如果该条件的计算结果为FALSE,则返回另一个值。
语法:IF(logical_test,value_if_true,value_if_false)
RANK函数
求某一个数值在某一区域内的排名
语法:RANK(number,ref,[order])
Large函数
返回数据集中第k个最大值
语法:LARGE(array,k)
- Array必需。 需要确定第k个最大值的数组或数据区域。
如果数组为空,则 LARGE 返回 #NUM!- K必需。 返回值在数组或数据单元格区域中的位置(从大到小排)。
如果 k ≤ 0 或 k 大于数据点数,则 LARGE 返回 #NUM!
Small函数
返回数据集中的第k个最小值。
语法:SMALL(array,k)
- Array必需。 需要找到第k个最小值的数组或数值数据区域。
如果数组为空,SMALL 返回 #NUM! 。
K 必需。要返回的数据在数组或数据区域里的位置(从小到大)。
如果 k ≤ 0 或 k 超过数据点数,SMALL 将返回 #NUM! 。
如果 n 为数组中的数据点个数,则 SMALL(array,1) 等于最小值,SMALL(array,n) 等于最大值。
SUBTOTAL函数
返回列表或数据库中的分类汇总
语法:SUBTOTAL(function_num,ref1,ref2, ...)
Function_num(包含隐藏值)为1到11之间的自然数,用来指定分类汇总计算使用的函数
值 | 相当于函数 |
---|---|
1 | AVERAGE 平均值 |
2 | COUNT 非空值单元格计数 |
3 | COUNTA 非空值单元格计数(包括字母) |
4 | MAX 最大值 |
5 | MIN 最小值 |
6 | PRODUCT 乘积 |
7 | STDEV 标准偏差值(忽略逻辑值和文本) |
8 | STDEVP 标准偏差值 |
9 | SUM求和 |
10 | VAR 给定样本的方差(忽略逻辑值和文本) |
11 | VARP |
筛选后自动排序:
在 A2 单元格输入以下公式:=SUBTOTAL(3,$B$2:B2)
IF函数{1,0}结构
作用:交换两列(或两行)数据
搭配VLOOKUP函数可以进行反向查找
公式:=VLOOKUP(LARGE($B$2:$B$13,G4),IF({1,0},$B$2:$B$13,$D$2:$D$13),2,0)
OFFSET函数
语法:OFFSET(reference,rows,cols,[height],[width])
数组{ }
- excel中的运算返回值的结果有两种:1个数和1组数。
- 如果在公式中返回一组数时,就需要用数组运算。即在输入公式后按 ctrl+shift+enter三键自动添加大括号{}。当然也有例外,像sumproduct、lookup函数就可以直接执行数组运算,不需要添加大括号。
输入公式:=SUM(B2:B6*10)后,按 ctrl+shift+enter 三键自动添加大括号{}
根据姓名查找对应工资
原文链接
公式:=INDEX($G$2:$G$9,SMALL(IF($B$2:$F$9=B14,ROW($1:$8),99^9),1))
,输入公式后按ctrl+shift+enter三键自动添加大括号{}
函数解析:
①通过IF函数,如果B2:F9单元格区域中的值,等于B14单元格的值,那么返回ROW(1:8)的行号,否则返回99^9
②使用SMALL函数,引用这个数列中最小的值,就是上图中的“3”了,它的意义就在于,我们要的对应“基本工资”在数据范围内的第3行
③INDEX函数:索引区域G2:G9单元格区域中的第3个值
超级表
特点:
①标题行自动冻结
②数据行列自动扩充
③输入公式,智能填充
④汇总行自动计算
⑤可视化智能筛选
图表制作
根据数据源处理数据后,再选在数据区域“B11:G78”,插入“折线图”