4个超级“变态”EXCEL函数公式,功能不是一般的强大!

2019年6月28日09:29:11 发表评论 361 views
4个超级“变态”EXCEL函数公式,功能不是一般的强大!

Excel中,IF是查询函数,Sum是求和函数,Vlookup是查询函数……这些是常规应用,但是有部分函数公式就是不按规矩“出牌”,其功能颠覆了我们的想象……

一、Sumproduct函数

作用:返回相应的数组区域乘积的和。

语法:=Sumproduct(值或数组区域)。

一)基本用法

目的:快速计算商品的总销售额。

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:

=SUMPRODUCT(C3:C9,D3:D9)。

二)行列交叉查询

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:

=SUMPRODUCT((B3:B9=J3)*(D2:G2=K3)*D3:G9)。

三)中国式排名

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:

=SUMPRODUCT((H$3:H$9>H3)/COUNTIF(H$3:H$9,H$3:H$9))+1。

二、Vlookup函数

作用:查询指定区域中符合条件的值。

语法:=Vlookup(查找值,数据范围,返回值的列数,匹配模式)。

一)基本用法

目的:查询销售员的销量和。

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:

=VLOOKUP(K3,B3:H9,7,0)。

二)多条件查询

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

1、输入公式:

=IFERROR(VLOOKUP(H3&I3,IF({1,0},B3:B9&E3:E9,D3:D9),2,0),"无销量")。

2、Ctrl+Shift+Enter填充。

三)多列查询

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:

=VLOOKUP($H$3,$B$3:$E$9,COLUMN(B3),0)。

三、Subtotal函数

作用:返回一个数据库或数据表的分类汇总。

语法:=Subtotal(格式代码,数组或引用)。

常用的格式代码:

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

1-11:包含手动隐藏的值。

101-111:不包含隐藏的值,暨仅对可见单元格有效。

一)基本用法

目的:对筛选的值求平均值。

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:=SUBTOTAL(101,E3:E9)。

二)保持序号(No)的连续性

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:=SUBTOTAL(103,B$2:B2)。

四、Lookup函数

作用:在指定的区域中查找对应的值。

语法:

1、向量形式:=Lookup(查找值,查找值所在范围,返回值所在范围)。

2、数组形式:=Lookup(查找值,查找值和返回值所在范围)。

前提条件:必须对查找值所在范围中值【升序】排序。

一)基本用法

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:

=LOOKUP(H3,B3:B9,D3:D9)、=LOOKUP(H3,B3:D9)。

二)单条件查询

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:=LOOKUP(1,0/(B3:B9=H3),D3:D9)。

三)多条件查询

4个超级“变态”EXCEL函数公式,功能不是一般的强大!

输入公式:

=IFERROR(LOOKUP(1,0/((B3:B9=H3)*(E3:E9=I3)),D3:D9),"无销量")。

发表评论

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