本文总共2123个字,阅读需8分钟,全文加载时间:1.749s,本站办公入门专栏收录该内容! 字体大小:

文章导读: 在Excel中,IF是查询函数,Sum是求和函数,Vlookup是查询函数……其实这些都是常规应用,但是有部分函数公式就是不按规矩“出牌”,其功能颠覆了我们的想象…… 一、Sumproduct函数。 作用:返回相应的数组区域乘积……各位看官请向下阅读:

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


一、Sumproduct函数。

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

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

(一)、基本用法。

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

方法:

在目标单元格中输入公式:=SUMPRODUCT(C3:C9,D3:D9)。

解读:

公式:=SUMPRODUCT(C3:C9,D3:D9)的计算过程就是:C3*D3 C4*D4 C5*D5 C6*D6 C7*D7 C8*D8 C9*D9。


(二)、行列交叉查询。

方法:

在目标单元格中输入公式:=SUMPRODUCT((B3:B9=J3)*(D2:G2=K3)*D3:G9)。

解读:

在B3:B9=J3和D2:G2=K3的两组条件判断中,如果条件成立,则返回1,1*1*X=X,如果有其中的一个条件不成立,则返回0。


(三)、中国式排名。

方法:

在目标单元格中输入公式:=SUMPRODUCT((H$3:H$9>H3)/COUNTIF(H$3:H$9,H$3:H$9)) 1。

解读:

条件H$3:H$9>H3如果成立,则返回1,1/1=1,暨公式:=SUMPRODUCT((H$3:H$9>H3)/COUNTIF(H$3:H$9,H$3:H$9))返回一组以1和0位数组元素的数组,和值即为大于当前值的个数,暨相对排名哦。 1位修正值。


二、Vlookup函数。

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

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

(一)、基本用法。

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

方法:

在目标单元格中输入公式:=VLOOKUP(K3,B3:H9,7,0)。

解读:

匹配模式分为0和1两种,其中0为精准匹配,1位模糊匹配。


(二)、多条件查询。

方法:

1、在目标单元格中输入公式: =IFERROR(VLOOKUP(H3&I3,IF({1,0},B3:B9&E3:E9,D3:D9),2,0),"无销量")。

2、Ctrl Shift Enter填充。

解读:

当多条件查询时,条件和条件范围之间用&符号连接。


(三)、多列查询。

方法:

在目标单元格中输入公式:=VLOOKUP($H$3,$B$3:$E$9,COLUMN(B3),0)。

解读:

多列查询的关键在于返回第几列的值,所以用Column函数替代固定的列数。


三、Subtotal函数。

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

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

常用的格式代码:

代码分为1-11和101-111,通过观察发现对应的函数名称是相同的,难倒是功能相同?

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

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

(一)、基本用法。

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

方法:

在目标单元格中输入公式:=SUBTOTAL(101,E3:E9)。

解读:

代码101-111仅对可见单元格有效,从示例中也可以看出,筛选前和筛选后的平均值截然不同。


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

方法:

在目标单元格中输入公式:=SUBTOTAL(103,B$2:B2)。

解读:

代码103对应的函数为Counta,暨统计非空单元格个数。


四、Lookup函数。

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

语法:

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

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

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

(一)、基本用法。

方法:

在目标单元格中输入公式:=LOOKUP(H3,B3:B9,D3:D9)、=LOOKUP(H3,B3:D9)。

解读:

1、Lookup函数在使用前,必须对查询值所在的范围按【升序】次序排列。

2、使用数组形式时,查询值的范围必须是第1列,返回值必须是最后1列。


(二)、单条件查询。

方法:

在目标单元格中输入公式:=LOOKUP(1,0/(B3:B9=H3),D3:D9)。

解读:

1、当Lookup函数在查找范围中找不到对应的值时,则向下匹配,原则是匹配小于查询值的最大值。

2、如果B3:B9=H3成立,则返回1,否则返回0;0/(B3:B9=H3)对应的结果为0和错误组成的数组,公式:=LOOKUP(1,0/(B3:B9=H3),D3:D9)查找值1匹配到0所对应的结果。


(三)、多条件查询。

方法:

在目标单元格中输入公式:=IFERROR(LOOKUP(1,0/((B3:B9=H3)*(E3:E9=I3)),D3:D9),"无销量")。


结束语:

Sumproduct、Vlookup、Subtotal、Lookup都是日常工作中经常要用到的函数,对于基本应用,很多亲还是基本掌握的,但是对于一些高级技巧,绝大部分的亲就犯迷糊,希望本节内容能给大家带来不一样的体验哦!

如果有不懂、不明白的地方,欢迎在留言区留言讨论哦,如果亲觉着实用,别忘了“点转评”哦,有亲的支持,小编会进一步努力的哦!


想要从零系统学习Excel,查看本站上方“视频教程”获取最新秋叶Office三合一办公应用课程!

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑,如果对您有帮助欢迎转发分享!

你可能对这些文章感兴趣:

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注