图片 16

SUM就是求和,本文介绍工程化的核心思路

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

方法1

图片 1

因为这里折扣参数是在第2行,C、D列数据是将B列数据*折扣率获取的。因此在这个案例中,我们需要固定B列数据和固定第二行数据,使得公式在下拉和右拉的时候,基准价始终引用的是B列数据,而右拉的时候则是始终引用第二行的折扣数据。这就是一个典型的混合引用。

  • ref_text:地址文本,可以是字符串也可以是一个地址;
  • a1:地址样式
    • FALSE – R1C1 样式
    • TRUE – A1 样式(默认)

图片 2

好啦,SUMIF函数的10大经典用法就介绍完了,教程写的好辛苦啊!写得这么详细,有些知识百度都出来,希望能够对大家有用,更重要的是为大家提供一种解决问题的思路。

复制老师的公式:

上述例子是固定区域进行绝对引用,在实际使用中我们还可以根据实际需要对行、列、或者单元格进行绝对引用。比如在产品系列的利润率是一定的情况下,当我们对下表的产品利润总和进行计算。由于利润率是固定引用B6的数据,此时此时就需要在E2输入公式“=D2*$B$6”(即固定B6进行绝对引用,如果使用相对引用,则会导致引用的利润率数值不准确),这样下拉公式就不会出现数据错误了。

函数返回值为,由上述参数确定的单元格的地址字符串。

1、相对引用

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

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

图片 3


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

在计划栏里=SUMIF(条件区域对应的是计划和实际栏/B2:M2,F4绝对引用,条件是计划N2,F4,求和区域是对应的销售额/B3:M3相对引用)Ctrl+Enter

除了相对引用外,在实际工作中还有很多时候是需要对行列进行绝对引用。这样引用的数值才不会出现错误。比如我们经常使用Vlookup函数制作查询语句,因为查询的范围是固定,所以就需要使用绝对引用固定查询区域。

FORMULATEXT(reference)

计算全国销售总量。由于销售量中有不同类型的错误值,所以不能用SUM直接求和,可以使用SUMIF函数实现排除错误值求和。

文本文档求和的三种方法

因地制宜,相对/绝对引用有讲究

下一课中,将介绍整体切换分类体系的方法,把这节课化简为繁的内容真正用起来。

回答:

6、错列求和

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

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键,会在如上所说的引用方式中切换,多次按多次切换。

公式:SUMIF(B2:B16,”<9e307″,B2:B16)

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

3.混合引用


3、混合引用

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

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

图片 4

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

图片 5

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

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

图片 6

回答:

说个简单的引用记忆法:

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

最终公式为:

一,SUM基本用法

这个错误就是由于相对引用造成的,定位到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键,就能简单地对单元格的相对引用和绝对引用进行切换),这里使用$固定引用区域,这样公式下拉后就不会再出现引用错误了。

图片 7

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

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

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

图片 8

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

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

图片 9

替换9e07结果仍然保持不变

图片 10Alt+=

比如下表是使用Vlookup进行查询A1:E5的数据,如果使用相对引用的语句,在B11输入公式“=VLOOKUP(A11,A1:E5,2,0)”,将函数下拉的时候B13会出现“#N/A”的提示。

函数返回值为,目标单元格的公式文本。

假设 A1 的公式为 =SUM(1, 2),则 A1 的值为 3;B1 的公式为
=FORMULATEXT(A1),则 B1 的值为 =SUM(1,
2),是文本,不作为公式执行计算。

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

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

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

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

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

模糊条件求和

八、SUMIF基本用法

图片 11

ADDRESS

问题:Excel的引用有几种?

公式:SUMIF($A$2:$A$16,$G2,B$2:E$16)。

附加:用SUMPRODUCT函数

相对OR绝对,认识引用

=COUNTA(Genre!$C:$C) - 1

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

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

图片 12

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

图片 13

在这里表示对小于最大值9e07的数据进行求和,也就是对“数值”单元格进行求和。使用9e07不是规定,不是原则,是大家约定俗成的用法。

十、隔列分类汇总

1.相对引用

  • reference:目标单元格地址。

2、绝对引用

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

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


公式:SUMIF(A1:C16,”杨过”,B1:D16)。

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

图5 绝对引用可以避免错误

图片 14

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

所有的姓张的销售员的销售额

上面主要介绍相对引用和绝对引用,在实际操作中还会遇到混合引用,也就是在一个公式中同时需要上述两种引用。比如下表数据中,一款产品针对不同等级会员有多个售价,如果要快速计算出每种会员的价格,此时就需要使用混合引用。

使用 FORMULATEXT 的好处是,让读者可以清晰地看到 B
列中参数的值是怎么得来的。

可是姐姐我偏不信,义无反顾的踏上了这条不归路,希望能够得到大家的鼓励,给姐姐继续走下去的力量。

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

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

INDIRECT

方法2

方法1:SUM快捷键Alt+=

图片 15

并向下填充至 C8 处,可以看到一串 #N/A 出现,这是因为 B
列目前为空,没有任何公式,不急,我们先放着,船到桥头自然直。

计算销售员总销售额。

=SUMIFS(销售额,明细表销售员,”张*”,商品名称,”A”) enter

定位到C2输入公式“=$B2*E$2”(固定B列,固定第2行),这样将公式下拉右拉可以看到引用的就都是固定行列的数据,这样可以快速求出不同会员的实际折扣售价。

将 B3 的公式向下填充至 B9 即可补齐二级分类,效果如下图

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

方法3:=SUM➡️Ctrl+Shift+Enter

平时在工作中,我们经常在Excel函数中对一些元素如单元格、行、列等元素进行相对或绝对的引用。今天我们就来探讨一下这两者的区别,以及我们又该在什么时候进行相对或者绝对引用。

打开 Example 3.xlsx,新建工作表,命名为 **Settings
**,按如下结构填写表格内容。

公式:SUMIF($B$2:$I$2,J$2,$B3:$I3)。

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

图片 16

本文介绍工程化的核心思路,将参数独立成表,为后续质的飞跃做准备,介绍
ADDRESS 函数、INDIRECT 函数用法。

发表评论

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

相关文章