betway必威-betway必威官方网站
做最好的网站

Excel中SUMIF函数的13个高端用法,Excel的援引有两种

问题:Excel的引用有几种?

SUMIF函数可以对范围中符合指定条件的值求和,该函数拥有十分强大的条件求和功能,在工作中有极其广泛的应用,在之前的文章中,罂粟姐姐也给大家介绍了该函数的一些经典应用场景。

今天进入函数与公式学习的第四天学习“求和函数”。简单记忆:SUM就是求和;IF是条件所以SUMIF是条件求和;而多个条件求和就是SUMIFS,理解为条件求和的复数形式。

回答:

但是,今天,罂粟姐姐将带大家深入了解SUMIF函数,因为它不仅有漂亮的外表,还有丰富的内涵。(当然,最主要原因是之前有小伙伴表示没看懂,这一次再看不懂就……就……就给姐姐发红包,手把手教!)

图片 1求和函数

想要真正学会Excel,需要花费一番功夫。例如一个小小的引用就有好几种不同的类型。赶紧过来看看你都会用哪一种?

1、单字段单条件求和

一,SUM基本用法

图片 2

计算“杨过”总销售额,公式:SUMIF(D2:D16,"杨过",E2:E16)

图片 3


单字段单条件求和

方法1:SUM快捷键Alt =

1、相对引用

说白一点,其实就是当你把公式复制到其他单元格时,行或列值的引用或自动变更。

例如:在B2单元格中输入“=B1”,但下拉填充时就会自动把公式类推为“=B2”或“=B3”等等。

图片 4


2、单字段多条件求和

方法2:SUM(不连续区域用英文状态下的逗号,最多255个区域求和)

2、绝对引用

而绝对引用就是当你复制公式时,行或列值不会变更。

例如:在B2单元格中输入“= $A$1”,那不管你向下或向右填充,公式都不会改变。


计算“杨过”和“杨逍”两个人的总销售额,共有两种方法。

方法3:名称框中设置好“求和区域”,=SUM或者用快捷键F3,选择“求和区域”

3、混合引用

当然了有些公式,会同时出现绝对引用和相对引用这两种类型。

例如:在B2单元格中输入“=B$1”,那么向右填充时,公式就会变成“=C$1”,依次类推。

图片 5

其实如果你有安装Mcrosoft Excel 2013的话,一些比较简单的数据填充,可以直接用“Ctrl E”来搞定!完全不用记住公式。

图片 6

像拆分、合并单元格信息,手机号码分段显示和加密。统统都可以在手动输入第一个单元格内容后,按下“Ctrl E”来完成自动填充操作。

如果你好不容易制作完成的Excel表格被不小心误删了,不用慌,可以在电脑上安装[强力数据恢复软件],点击扫描后,你还可以预览文件内容哦。

图片 7

回答:

说个简单的引用记忆法:

Excel中的引用有三种:绝对引用,相对引用和混合引用。这是常规的说法,很多人在刚接触时会比较晕,其实很很简单。

方法1:借助SUMIF进行数学运算,杨过总销售额 杨逍总销售额。

图片 8Alt =

公式中单元格的引用方式:

excel中的每一个单元格都是用行数字和列字母来表示的(默认设置情况下),类似于行列交叉定位,如下图的369在B3单元格,名称框中也会显示B3。

图片 9

在公式中,我们想要引用369,就可以用B3来表示,如下图:

图片 10

公式:SUMIF(D2:D16,"杨过",E2:E16) SUMIF(D2:D16,"杨逍",E2:E16)

Tips:1)对带颜色的区域求和

拖拉公式时,单元格的引用方式的变化:

输入公式时,为了方便,我们通常会向下或向右拖拉,以达到同类公式的快捷填充。

拖拉公式时,行列数字和字母是随着单元格的变动而变化的,如下图红色区域

图片 11

如果我们想要固定某个单元格,就要将其行列加“$"符号进行锁定,例如上图绿色区域的B12单元格,锁定单元格后的公式在向下拖动时,就不会再变动。

同一行的向右填充,也是同样的效果:

图片 12

方法1

Ctrl F5➡️查找和替换➡️格式➡️填充颜色➡️选定颜色➡️确定➡️查找全部➡️Ctrl A➡️定义名称“颜色区域”➡️=SUM➡️F3➡️颜色区域➡️确定

锁定行列的不同,分为三种引用方式:

之前有说,每个单元格有行数字和列字母两元素进行定位,那么同一个单元格的锁定也就有两种方式,进行行数字锁定或者进行列字母锁定,锁定哪个就在哪个前面加$符号就可。

行列同时锁定的,如$B$13,就是绝对引用,拖动公式时,行列都不会变化。

行锁定或者列锁定的,如B$13和$B13,就是混合引用,拖动公式时,锁定的行或列不会变化。

行列都不锁定的,如B13,就是相对引用,拖动公式时,行列都会变化。

方法2:借助SUM和SUMIF实现单字段多条件求和,SUM(杨过销售额,杨逍销售额)。

2)累计求和

引用方式的转换:

1、手动输入:锁定哪个在哪个前面输入$符号(英文符号)

2、F4键转换:输入公式后,选中单元格名称,比如B13,按F4键可进行三种引用的转换,此时,只要记着哪个方向锁定,$符号就在哪个的前面就好了。


专注分享高效工作技能,助你“职场UP”,欢迎关注。

回答:

公式里面的引用有直接引用和间接引用。一般我们常用A1引用,如果编写宏代码就会大量使用R1C1引用。

直接引用有相对引用,绝对引用和混合引用,楼上已经说明,这里不再赘述。

间接引用,一般由函数OFFSET,INDIRECT完成。例如求A1:A10的和:

直接引用求和公式为=SUM(A1:A10),INDIRECT的A1引用求和公式为

=SUM(INDIRECT("A1:A10")),或者=SUM(INDIRECT("A1:A"&10))

INDIRECT的R1C1引用求和公式为=SUM(INDIRECT("R1C1:R10C1",))

OFFSET的引用求和公式为

=SUM(SUMIF(OFFSET(A1,{0,1,2,3,4,5,6,7,8,9},),"<>"))

或者=SUM(SUBTOTAL(9,OFFSET(A1,{0,1,2,3,4,5,6,7,8,9},)))

显然用常量数组列举比较麻烦,不过不用按ctrl shift enter

当然我们可以修改公式为

=SUM(SUMIF(OFFSET(A1,row(1:10)-1,),"<>"))

这个公式需要按ctrl shift enter。

或者修改公式为

=SUM(SUBTOTAL(9,OFFSET(A1,ROW(1:10)-1,)))

这个公式也需要按ctrl shift enter。

这里OFFSET(A1,ROW(1:10)-1,),向下依次偏移了0,1,2,3,4,5,6,7,8,9,所以是一个多维数组,多维数组不能直接用 SUM求和。

OFFSET还可以这么写公式=SUMIF(OFFSET(A1:A10,,),"<>")

或者这么写公式=SUBTOTAL(9,OFFSET(A1:A10,,))

或者干脆写成=SUM(OFFSET(A1:A10,,))

这里OFFSET(A1:A10,,),由于偏移量都为0,所以是一个一维数组,一维数组可以直接用SUM求和。

回答:

指公式中的引用吧。n一是相对引用,最常用。在公式填充时,会随公式所在单元格的变化而变化。例如:在c1单元格中输入=a1 b1,即是相对引用,当公式填充以到c2单元格时,公式即变成:=a2 b2。再如在a3单元格中输入=sum(a1:a2),从a3单元格向右填充时,b3单元格公式就会变成=sum(b1:b2)。n二是绝对引用,就是在表示单元格列字母和行数字前加上“$“。绝对引用是 固定的,不会像相对引用一样变化。如用vlookup函数时,查找区域常常是固定的,常用绝对引用。n三是混合引用。明白了上面两种,混合引用也就不难理解,如果不想让列变化,变就在字母前加$,如:$a1;如果不想让行发生变化就在行前加$,如:b$2。

回答:

简单说

三种

相对引用:A1,横向填充,列号“A”随之变化;纵向填充,行号“1”随之变化;

绝对引用:$A$1,不会变化

混合引用:

$A1:横向填充,列号“A”不变;纵向填充,行号“1”随之变化;

A$1:横向填充,列号“A”随之变化;纵向填充,行号“1”不变;

要是不理解,就去表格里体验下就懂了。

Tips:将光标放在公式中引用的单元格,如“A1”上,按F4键,会在如上所说的引用方式中切换,多次按多次切换。

公式:SUM(SUMIF(D2:D16,{"杨过","杨逍"},E2:E16))

=SUM(数量区域*单价区域)➡️Ctrl Shift 回车(进行数组的运算,公式中会出现大括号)

方法2

3)无法求和的SUM

注意:SUMIF(D2:D16,{"杨过","杨逍"},E2:E16)是一个数组公式,指代的结果是{杨过销售额,杨逍销售额},再用SUM公式求和,即可得到杨过与杨逍的总销售额。

文本文档求和的三种方法

3、包含日期的单字段多条件求和

方法1:复制空白单元格➡️选取文本型数字➡️选择性粘贴数值➡️加➡️✅

计算8月20日至9月20日总销售额,有两种方法

方法2:数据➡️分列➡️全部默认➡️完成

方法1:借助SUM和SUMIF实现单字段多条件求和,SUM(8月20日以后的销售额,9月20日之前的销售额)。

方法3:=SUM➡️Ctrl Shift Enter

公式:SUM(SUMIF(A2:A16,{">=2016/8/20",">2016/9/20"},E2:E16)*{1,-1})。

图片 13文本文档求和

相当于=SUMIF(A2:A16,">=2016/8/20",E2:E16)-SUMIF(A2:A16,">2016/9/20",E2:E16),即8月20日至9月20日的销售额=8月20日以上的销售额-9月20日以后的销售额。

二、快速行列汇总

方法1

框选数值及所有需要求和区域➡️Alt =

方法2:借助SUMIFS实现多条件求和,同时满足8月20日以后和9月20日之前两个条件的销售额。

三、小记行快速求和

公式:SUMIFS(E2:E16,A2:A16,">=2016-8-20",A2:A16,"<=2016-9-20")

方法1:分别行用Alt =

sumifs多条件求和

方法2:选取小计区域➡️Ctrl G➡️定位空值➡️Alt =➡️累计行单独Alt =求和

注意:SUMIFS函数是 Excel 2007及以后版本的新增函数,不能在 Excel 2003 中使用,但是可以使用SUMIF函数的普通公式来实现(如方法1)。

图片 14再一次见证Alt = 的魅力

4、模糊条件求和

四、相同区域多表快速合并

SUMIF语法中criteria参数中支持使用通配符(包括问号“?”和星号“*”)。关于通配符的介绍,请移步姐姐之前的文章:妙用Excel通配符,让工作效率倍增。

选取需要求和的区域➡️=SUM➡️选择表1中的B2单元格➡️Shift同時鼠标左键选择最后一个表格➡️Ctrl Enter

计算姓“杨”销售员的总销售额,公式:SUMIF(D2:D16,"杨*",E2:E16)。

图片 15汇总蓝色区域1-12个相同格式表格

模糊条件求和

五、不同区域多表快速合并

5、多列区域求和

数据➡️合并计算

本文由betway必威发布于电脑教程,转载请注明出处:Excel中SUMIF函数的13个高端用法,Excel的援引有两种

TAG标签: betway必威 必威
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。