真正的Excel函数高手,都能玩转美元

要想在工作中灵活掌握函数公式,有一些底层逻辑和原理,是需要反复运用和琢磨的,就比如:相对引用和绝对引用。

在Excel中使用函数公式的目的是什么?

如果,写公式本身就要花很多时间,反复复制粘贴然后,逐个修改,是不是就太麻烦了?

Excel函数公式的魅力就在于,它可以自动灵活变化,而控制这种变化的开关就是「引用」方式。

它是函数小白和函数高手之间的一条分水岭

能否让函数公式乖乖帮你工作,就看你能不能玩转美元符号$(相对引用和绝对引用),按场景灵活切换运用了。

一、什么是引用?

算两个数的总和,可以直接写公式 =10+20。

也可以先把数字写到格子里,然后公式写成=A1+A2。

这样写,修改格子里的数据时,结果自动变化。而不用重新手工修改公式

一个公式,就是一套计算规则,算法不变,就不用修改。

原始数据改变,结果自动更新,这就是Excel公式的基础:「引用」。

二、相对引用:引用位置跟着公式跑

要计算2列数据的和,每一行都需要写一个公式。

要是1000行,就得写1000次,那也太笨了。

所以,公式向下填充时,Excel默认引用位置会跟着公式跑。

公式所在的格子和引用的格子,默认始终保持固定的距离。这种引用方式,就是「相对引用」。

三、绝对引用:用美元锁住引用位置

可是,在有的情况下,放手让引用位置跟着跑,是会出错的。

比如,下面的例子,第二个结果开始全部是0。

怎么才能让后面的价格别跑呢?

给钱收买它,而且要用美元$。

方法很简单,直接按F4键就可以了。

(注意:有些笔记本的键盘要同时按Fn键)

=B2*$E$2

四、混合引用:按需锁定行或列

绝对引用,是行和列都被固定住。

我们还可以根据需要,只固定行,或者只固定列。

比如,只锁定列向下填充,行会变,但向右填充时,列不会变。

=$A1

而只锁定行向下填充时,行不变,向右填充时,列会变。

=A$1

什么时候锁定行,什么时候锁定列?就看你想要公式填充复制时,到底要怎么变化。

五、复制公式时,如何让公式的相对引用位置不发生变化?

特别留意,选中单元格后直接复制粘贴,里边的公式会发生变化。

因为=A1,公式没有锁定

复制公式想要保持原样,怎么办?

需要在编辑栏里,直接复制公式,再粘贴到目标位置。

搞清楚几种引用方式的区别。

下面,就来看看具体的实战应用场景,看看这些引用方式怎么起作用的。

实例1:巧借引用算累计总和

累计求和,,利用相对引用的原理,用一个公式就可以搞定。

=B3+C2

可是,一列数据里存在2个不同的公式。万一,其他人不知道,在修改表格时就很可能出错。

所以,更好的书写习惯是,一列数据,只用一种公式。

怎么办呢?

换成Sum函数:

=Sum($B$2:B2)

用$把起点锁住了,公式向下填充,起点不变,终点始终跟着公式走,这样就形成了一个动态变化的求和区域。

我们把这种引用方式,称为「拉灯模式」或「拉窗帘」模式。公式往下拉,区域会向下展开扩大。

掌握这个思路,可以让很多公式运用变灵活。

实例2:巧借引用实现分组编号

按照分组进行组内编号,如果有100个人,手工写就很麻烦。但是,使用Countif函数结合「拉灯模式」,就可以一个公式搞定。

=Countif($A$2:A2,A2)

公式在第一个单元格时,这个写法看起来很奇怪,怎么全是A2?

但是,当公式向下填充后,就能看出来其中的奥妙。

它的含义是,统计A列中A2单元格到当前行,等于同一行数据的个数。

实例3:巧借引用简化Sumif函数

来一个更加复杂一点点的场景,隔列计算总和。

公式小白会一个格子一个格子加起来,能解决问题也挺好,就是书写和修改都很麻烦。而公式高手,都是一招搞定的。

=sumif($B$2:$I$2,J$2,B$3:I$3)

如果你能把上面这个公式弄明白,那就达到了函数公式四级的水平。

现在知道什么时候用,什么时候不用$了吗?

在Excel中,重复操作必有批量之法,批量操作必有规律可循。

写函数公式本身,也是一样的。以后有机会接触更多实例,再给大家分享更多公式引用的高级用法。

>

为了写这篇文章,光是做图就花了2个小时,看在诚意满满的分上,点个「在看」再走呗???

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20191108A0M79Z00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券