收藏 | 高手常用的5个excel函数组合

  • A+
所属分类:数据分析

INDIRECT函数

INDIRECT(ref_text,[a1])如果 ref_text 不是合法的单元格的引用,函数 INDIRECT 返回错误值#REF!或#NAME?。

如果 ref_text 是对另一个工作簿的引用(外部引用),则工作簿必须被打开。如果源工作簿没有打开,函数 INDIRECT 返回错误值#REF!。

常用函数组合:=SUM(INDIRECT(C5&"!G:G"))

收藏 | 高手常用的5个excel函数组合

INDIRECT指南:

1,先使用&符号连接 C5&"!G:G",使其变成具有引用样式的文本字符串"一月份!G:G"。

2,再使用INDIRECT函数将文本字符串变成真正的引用。

3,最后用SUM函数对返回的引用范围求和汇总。

 

LOOKUP函数

= LOOKUP(查找值,查询范围,结果范围)

LOOKUP典型用法:逆向查询,

=LOOKUP(1,0/(条件),目标区域或数组),

其中,条件可以是多个逻辑判断相乘组成的多条件数组。

=LOOKUP(1,0/((条件1)*( 条件2)* ( 条件N)),目标区域或数组)

以0/(B2:B10=E5)构建一个0、#DIV/0!组成的数组,再用永远大于第2个参数中所有数值的1作为查找值,即可查找最后一个满足非空单元格条件的记录。

 

offset函数

求最后7行数据的平均值

Excel动态区域一般是用offset函数来完成,

=COUNTA(A:A)-7

最后7行的开始位置可以用总行数-7来推算出来,COUNTA函数用来统计非空单元格的个数。

=OFFSET(B1,COUNTA(A:A)-7,0)

表示B1向下偏移COUNTA(A:A)-7个位置,最终可以得到B7单元格。

=OFFSET(B1,COUNTA(A:A)-7,0,7,1)

可以获取以B7为顶点的7行1列的区域。

=AVERAGE(OFFSET(B1,COUNTA(A:A)-7,0,7,1))

对最后7行求平均值,即是最终要求。

 

tangtang

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: