>

办公小才具:Excel援引相对依然相对

- 编辑:乐百家599手机首页 -

办公小才具:Excel援引相对依然相对

问题:Excel的援引有三种?

SUMIF函数可以对范围中符合钦点条件的值求和,该函数具备特别无敌的规格求和法力,在专业中有最为广泛的运用,在事先的稿子中,罂粟妹妹也给大家介绍了该函数的一些精彩应用场景。

后天跻身函数与公式学习的第四日学习“求和函数”。轻松回忆:SUM就是求和;IF是基准所以SUMIF是基准求和;而三个条件求和正是SUMIFS,掌握为标准求和的复数方式。

常常在工作中,大家平常在Excel函数中对部分因素如单元格、行、列等因素进行相对或相对的引用。明日大家就来查究一下那多头的区分,以及大家又该在如何时候实行相对仍旧相对援引。

回答:

但是,今日,罂粟大姨子将带大家深远摸底SUMIF函数,因为它不唯有有奇妙的表面,还大概有加上的内蕴。(当然,最重大缘由是前面有小同伙表示没看懂,那叁回再看不懂就……就……就给表嫂发红包,手把手教!)

乐百家599手机首页 1求和函数

相持O卡宴相对,认知引用

想要真正学会Excel,须求成本一番素养。比如三个微小引用就有几许种分化的连串。赶紧过来看看您都会用哪个种类?

1、单字段单条件求和

一,SUM基本用法

在Excel函数中不能缺少需求对队列的援引,相对援引是指在援引时,援引的队列会趁着地点区别而产生变化。比如在下表中,纵然大家在C1处输入公式“=A1”,当大家下拉公式的时候,公式会相继成为“=A2”、“=A3”,横拉则会挨个成为“=B1”、“=C1”。也正是说无论公式是下拉恐怕横拉,引用的队列数值都会联手产生变化,那就是相对引用。

乐百家599手机首页 2

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

乐百家599手机首页 3

乐百家599手机首页 4


单字段单条件求和

方法1:SUM快捷键Alt =

图1 相对引用

1、相对援用

说白一点,其实正是当你把公式复制到其余单元格时,行或列值的引用或自发性别变化更。

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

乐百家599手机首页 5


2、单字段多规格求和

艺术2:SUM(不接二连三区域用保加萨尔瓦多语状态下的逗号,最多253个区域求和)

若果在D1输入公式“=$A1”,那么在下拉公式的时候,公式会挨个成为“=$A2”、“=$A3”,横拉则保持不改变一向为“=$A1”,也等于说在表示行标志的字母前拉长“$”符号后,表示是对行的锁定,这样公式在横拉后维持对原来行数的援引,这正是绝对引用。假设公式改为“=A$1”,则意味对列的相对化援用,假如公式形成=$A$1则行列均锁定,就是所谓的“行列均相对援引”。

2、相对援用

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

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


总计“杨过”和“杨逍”五个人的总发售额,共有三种办法。

艺术3:名称框中设置好“求和区域”,=SUM大概用赶快键F3,选取“求和区域”

乐百家599手机首页 6

3、混合援用

本来了多少公式,会同一时间出现相对援引和对峙引用这两体系型。

诸如:在B2单元格中输入“=B$1”,那么向右填充时,公式就能够化为“=C$1”,依次类推。

乐百家599手机首页 7

其实若是您有安装Mcrosoft Excel 二〇一一的话,一些比较轻易的多寡填充,能够直接用“Ctrl E”来化解!完全不用记住公式。

乐百家599手机首页 8

像拆分、合并单元格音信,手机号码分段显示和加密。统统都得以在手动输入第二个单元格内容后,按下“Ctrl E”来形成机关填写操作。

如若您到底制作完了的Excel表格被不小心误删了,不用慌,能够在管理器上设置[强力数据复苏软件],点击扫描后,你还是能预览文件内容哦。

乐百家599手机首页 9

回答:

说个简单的援用回忆法:

Excel中的引用有两种:相对引用,相对援引和混合引用。那是健康的传道,非常多少人在刚接触时会相比晕,其实很很简短。

办法1:借助SUMIF举办数学生运动算,杨过总出卖额 杨逍总发售额。

乐百家599手机首页 10Alt =

图2 相对援引

公式中单元格的援引情势:

excel中的每多少个单元格都以用行数字和列字母来表示的(暗中同意设置景况下),类似于队列交叉定位,如下图的369在B3单元格,名称框中也会显得B3。

乐百家599手机首页 11

在公式中,大家想要援用369,就足以用B3来代表,如下图:

乐百家599手机首页 12

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

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

深厉浅揭,相对/相对援引有侧重

拖拉公式时,单元格的引用格局的转移:

输入公式时,为了方便,大家司空眼惯会向下或向右拖拉,以达到同类公式的飞速填充。

拖拉公式时,行列数字和字母是随着单元格的改换而生成的,如下图黄绿区域

乐百家599手机首页 13

假定大家想要固定某些单元格,将在将其行列加“$"符号举行锁定,比方上海教室金色区域的B12单元格,锁定单元格后的公式在向下拖动时,就不会再改动。

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

乐百家599手机首页 14

方法1

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

地点咱们领悟了什么是相对引用和相对援用,那么在实质上中国人民解放军海军事工业程大学业作中,绝对援引和相对引用各自应该运用在什么样场馆?

锁定行列的两样,分为二种援用情势:

从前有说,每一个单元格有行数字和列字母两元素举办固定,那么同一个单元格的锁定也就有二种方式,实行行数字锁定可能拓展列字母锁定,锁定哪个就在哪些前边加$符号就可。

队列同不平时间锁定的,如$B$13,正是相对援引,拖动公式时,行列都不会扭转。

行锁定或许列锁定的,如B$13和$B13,正是勾兑援用,拖动公式时,锁定的行或列不会变卦。

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

措施2:借助SUM和SUMIF达成单字段多规格求和,SUM(杨过贩卖额,杨逍出售额)。

2)累计求和

1.相对引用

引用情势的改造:

1、手动输入:锁定哪个在哪些前边输入$符号(罗马尼亚语符号)

2、F4键转变:输入公式后,选中单元格名称,例如B13,按F4键可开始展览三种援用的调换,此时,只要记着哪些方向锁定,$符号就在哪个的前面就好了。


在意分享高效专门的职业才干,助你“职场UP”,应接关心。

回答:

公式里面包车型地铁引用有直接援引和直接引用。一般大家常用A1援引,如若编写宏代码就能够大批量使用凯雷德1C1援用。

直接援引有相对引用,相对援引和交集援引,楼春日经证实,这里不再赘言。

直接援用,一般由函数OFFSET,INDIRECT达成。譬如求A1:A10的和:

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

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

INDIRECT的纳瓦拉1C1引用求和公式为=SUM(INDIRECT("Lacrosse1C1:Odyssey10C1",))

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 回车(进行数组的运算,公式中会出现大括号)

是因为相对引用是对队列进行协同的变通,由此对于未有特殊要求的公式,常规状态下都应有使用相对援用。那样在公式进行下拉仍旧横拉的时候,公式的援引值才不会出错。比方大家常用的SUM求和函数,在临近“=SUM”公式中,这里对区域拓展相对引用,假如区域数值发生变化,比方删除也许扩张了行数,那么公式会活动进行援引,进而一贯保证引用的科学。

方法2

3)无法求和的SUM

乐百家599手机首页 15

专注:SUMIF(D2:D16,{"杨过","杨逍"},E2:E16)是四个数组公式,指代的结果是{杨过出售额,杨逍出售额},再用SUM公式求和,就可以猎取杨过与杨逍的总出售额。

文本文书档案求和的三种艺术

图3 相对引用区域

3、包罗日期的单字段多规格求和

艺术1:复制空白单元格➡️选用文本型数字➡️选用性粘贴数值➡️加➡️✅

2.相对援引

总计12月11日至12月15日总出售额,有三种办法

办法2:数据➡️分列➡️整体暗中认可➡️完毕

除外相对援用外,在骨子里职业中还大概有为数十分的多时候是急需对队列实行相对援用。那样援引的数值才不会见世谬误。比方我们平时使用Vlookup函数制作查询语句,因为查询的界定是定点,所以就需求利用相对化援引固定查询区域。

乐百家软件,措施1:借助SUM和SUMIF实现单字段多规格求和,SUM(5月二十二日之后的发售额,六月17日事先的出售额)。

方法3:=SUM➡️Ctrl Shift Enter

比方下表是使用Vlookup进行查询A1:E5的数目,假若运用相对援用的讲话,在B11输入公式“=VLOOKUP(A11,A1:E5,2,0)”,将函数下拉的时候B13会出现“#N/A”的提示。

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

乐百家599手机首页 16文本文书档案求和

乐百家599手机首页 17

也正是=SUMIF(A2:A16,">=二零一四/8/20",E2:E16)-SUMIF(A2:A16,">2014/9/20",E2:E16),即6月二十四日至五月26日的贩卖额=五月二十七日以上的发售额-六月十日从此的出卖额。

二、快捷行列汇总

图4 相对援用导致援引错误

方法1

框选数值及具有需须要和区域➡️Alt =

本条荒唐正是由于绝对引用产生的,定位到B13,能够观察这里的公式是“=VLOOKUP(A13,A3:E7,2,0)”,约等于在A3:E7的界定去搜寻数据,因为产品A实际是在A2单元格(不在A3:E7区域),所以变成征引错误。由此要落到实处引用准确性,大家就须要对A1:E5区域开始展览相对引用。同上稳固到B11,将公式改变为“=VLOOKUP(A11,$A$1:$E$5,2,0)”(在Excel中输入公式时,只要按下F4键,就能够大约地对单元格的相对引用和相对引用进行切换),这里运用$固定援用区域,那样公式下拉后就不会再出新援用错误了。

乐百家599手机首页,方法2:借助SUMIFS落成多规格求和,同不常间知足九月二十五日以后和九月二十二日事先四个标准的发卖额。

三、小记行急忙求和

乐百家599手机首页 18

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

格局1:分别行用Alt =

图5 绝对援引能够制止不当

本文由乐百家软件发布,转载请注明来源:办公小才具:Excel援引相对依然相对