当前位置:首页 > 办公 > Excel教程 > 正文内容

厉害了,我的SUMIFS函数

酷网1个月前 (10-22)Excel教程28

今天讲的SUMIFS函数,相对LOOKUP而言,它有两个优势:

1、计算效率更高,当数据超过1万行,LOOKUP函数就会很卡,而SUMIFS函数依然不卡。

2、显示效果会更好,LOOKUP函数查找不到对应值显示错误值,而SUMIFS函数查找不到对应值显示0。

比如我们用SUMIFS函数来查找金额。=SUMIFS(C:C,A:A,E2,B:B,F2)

 

将项目改成【函数班】,因为没有对应值,就直接显示0。

 

SUMIFS函数语法:=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,条件区域N,条件N)

第一参数为求和区域,后面的条件区域和条件一一对应,下面通过4个案例进行说明。

一、统计每个学校的金额。

 

正常情况下,单条件我们想到的是用SUMIF函数统计,其实SUMIFS函数不仅可以多条件求和,也可以单条件求和。=SUMIFS(D:D,A:A,F2)

学会了SUMIFS函数,SUMIF函数也可以不用学。

二、统计每个学校对应分类的金额。

 

在J2输入公式,下拉和右拉。=SUMIFS($D:$D,$A:$A,$I2,$C:$C,J$1)

混合引用是所有引用方式中最难理解的,有不少读者到现在都没弄清楚该如何使用,这里再说明一下。

公式在下拉的时候,我们希望第一行的单元格不改变,将美元符号放在数字前面就锁定行号不变J$1。

公式右拉的时候,我们希望I列这一列的单元格不改变,将美元符号放在列前面就锁定列号不变$I2。

公式既要下拉,也要右拉,区域都是使用绝对引用。假设我们现在不是引用整列,区域就需要在行号跟列号前面都加美元符号,如$D$2:$D$221。

整个公式,这个引用方式是难点,自己动手多操练几次,可以通过按F4键不断切换引用方式。现在来演示一下,在编辑栏切换引用方式。

 

提示:有的电脑比较特殊,需要按Fn+F4组合键才可以。

三、计算每个员工号的每一列单位金额。

 

原来使用一个个单元格加起来的方法,如果引用的单元格比较少,这种也是不错的选择。当引用的单元格多的情况下,容易出错。这时SUMIFS函数就派上用场了。=SUMIFS(B2:Q2,$B$1:$Q$1,”单位金额”)

财务人对金额比较敏感,有的时候差1,2分钱都不行,这时在函数外面嵌套一个ROUND函数进行四舍五入,保留2位小数点。=ROUND(SUMIFS(B2:Q2,$B$1:$Q$1,”单位金额”),2)

四、统计1-6月每个销售人员对应商品的销售量。

格式相同的明细表

 

汇总表

 

求1月份的销售量可以用:=SUMIFS(1月!C:C,1月!A:A,A2,1月!B:B,B2)

常规方法是写6个SUMIFS相加。,但6个月的数据改起来也不是太费劲,不过如果是12个月,甚至有的是按天进行分表格,一年365个表格,直接写晕。

比较智能的方法应该是这样写公式:=SUMPRODUCT(SUMIFS(INDIRECT(ROW($1:$6)&”月!C:C”),INDIRECT(ROW($1:$6)&”月!A:A”),A2,INDIRECT(ROW($1:$6)&”月!B:B”),B2))

公式看起来很复杂,其实并不难。

1、ROW($1:$6)的作用就是获取1-6的数字。

2、ROW($1:$6)&”月!C:C”的作用就是获取1-6月C列的文本字符串,并不能参与计算。

3、INDIRECT(ROW($1:$6)&”月!C:C”)的作用就将1-6月C列的文本字符串转变成区域,并能参与计算。

同理,其他参数的INDIRECT都是一样的作用。

这时用SUMIFS函数统计出来的结果是6个数据:={151;0;0;0;0;0}

在最外面嵌套SUMPRODUCT函数,就可以对这些数据进行求和。

如果现在有12个月,只需将公式中的6改成12就可以。=SUMPRODUCT(SUMIFS(INDIRECT(ROW($1:$12)&”月!C:C”),INDIRECT(ROW($1:$12)&”月!A:A”),A2,INDIRECT(ROW($1:$12)&”月!B:B”),B2))

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/40280.html

分享给朋友:

相关文章

利用excel中的RANK函数进行排序

利用excel中的RANK函数进行排序

在教学中,我们考试之后都要进行分数排名。大家最先想到的应该是用排序按钮,然后下一列用自动填充功能填充,但是当遇到两个或多个数据相同的时候,自动填充显示的名次是不一样的,我们还得对这些相同分数的排名进行手动修改。数据少的时...

如何更改Excel批注形状?更改Excel批注形状方法

如何更改Excel批注形状?更改Excel批注形状方法

  我们在使用WPS进行Excel表格的编辑时,经常会使用到批注。为了让我们的Excel表格看起来更加美观,我们可能需要修改Excel批注的形状。那么如何修改Excel批注形状呢?下面...

excel表格中怎么写身份证号格式?excel表格中写身份证号格式的方法

excel表格中怎么写身份证号格式?excel表格中写身份证号格式的方法

  excel表格是我们在工作中经常使用的办公软件之一,不过你们知道在excel表格中怎么写身份证号格式?接下来我们就一起往下看看excel表格中写身份证号格式的方法吧。   ...

Excel函数的操作题第3套

Excel函数的操作题第3套

某公司拟对其产品季度销售情况进行统计,打开“Excel.xlsx”文件,按以下要求操作: 1. 分别在“一季度销售情况表”、“二季度销售情况表”工作表内,计算“一季度销售额”列和“二季度销售额”列内容,均为数值型,保留小...

RANK、COUNT/COUNTA/COUNTBLANK、COUNTIF/COUNTIFS

RANK、COUNT/COUNTA/COUNTBLANK、COUNTIF/COUNTIFS

掌握EXCEL强大函数功能,可以大大提高工作学习效率哦。本篇主要介绍3类统计函数,分别为RANK、COUNT/COUNTA/COUNTBLANK、COUNTIF/COUNTIFS函数。 01 RANK函数功能:求某个数值...

入职第一天,老板就和我说,Excel核对数据一定要会

入职第一天,老板就和我说,Excel核对数据一定要会

进行繁杂的对账工作常常是财务人员头疼的事情,不仅仅因为数据量比较大,在实际对账的过程中,可能会遇到各种各样的情况,说起来都是对账,但处理的方法可能有很大的区别,因此今天为大家整理出了一些比较常遇到的问题,都是可以运用EX...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。