表界高手都说，函数公式是 Excel 的灵魂！因为它能纵横捭阖，轻松实现各种数据计算。

要说 Excel 灵魂中的精髓，当数「排山倒海」这一招。

掌握此绝技，就能一招制敌，用一个公式搞定海量数据计算，厉害吧。

GIF

要学会「排山倒海」，首先得会「猴子偷桃」，哦不，是「吸星大法」~

哦，就是「引用」啦。

引用就是，不直接输入数据，而是把其他单元格的内容吸过来，自动得到结果。

看一个最简单的例子，一个等号和一个引用。

GIF

在单元格 B2 中输入 =A2，他的值就自动变成了 A2 中的值。引用的好处很明显，修改源数据时，结果也会自动更新。

引用的方式可以分为三种：相对引用、绝对引用和混合引用。

也就是这三种：A1，$A$1，A$1。

逐个看看三种引用方式的区别吧 ~

Excel 公式中默认的引用方式就是相对引用，已经自带排山倒海的功力了。

怎么全都变了呢？

打开【公式】选项卡，点击【显示公式】按钮一看，原来，公式竟然会自动跟随变化。

要算个总价，也是一样轻轻松松啊。

蛋素，即使很多人知道向下填充知道怎么回事，换一种方式就懵逼了。

比如下面这个表格，是一家 10 元店的清单，几样商品的金额都是 10。那怎么算每项商品的总金额？

同样是根据「数量*单价」计算「总额」，如果还是直接写入 B4*C1，然后向下填充，结果就全错了！

你看，红色的数值才是正确的哦~

要解决这个问题，就要上升到排山倒海第三重：绝对引用。

前面的表格计算，引用单价时必须锁定在一个单元格，才能得到正确结果。怎么办呢？

你需要一个神奇的按键 F4。

正在上传...取消

按下【F4键】，C2 就变成了 $C$2，这就是绝对引用。花了点钱($)，就把价格参数锁定在 C2 了。果然还是见钱眼开啊。

有了「相对引用」和「绝对引用」这对好基友，公式的排山倒海威力初见成效。

好啦，练功不能急进，以免走火入魔。更厉害的排山倒海 第4重、第5重…… 且看下回分解。

不知道大家有没有此类的遭遇，我们辛辛苦苦编辑的公式发下去让大家填写反馈，结果反馈来的表，早已没有那些公式的存在了，被改的面目全非了，所以，小编决定今天来共享下如何让我们辛苦编辑的公式不被人修改

1、选定所有单元格，调出【设定单元格格式】→【保护】→取消【锁定】前的√→确实，操作如下：

2、按快捷点Ctrl+G，调出【定位窗口】→点击定位条件→选择【公式】→确定，操作如下

3、调出【设定单元格格式】→【保护】→选定【锁定】前的√→确实，操作如下：

4、点击工具栏的【审阅】→选择【保护工作表】→在弹出的窗口中输入密码→确定

5、在弹出的窗口中再次输入密码

6、设置完毕了，当我们不小心碰到公式的时候，就会弹出如下的对话框，必须输入密码，这样就可以保护我们的表格不被修改了

这个操作其实简单，就是步骤可能相对多点，考验我们的记忆的时候到了，欢迎收藏或分享给更多的同道中人，大家共同捍卫保护我们的表格不被改动

常常在表格中筛选数据或隐藏数据，很多人希望筛选或隐藏后，表格可以自动更新新的数据的计算结果和序号，实现所见及所得。

有些人会使用状态栏，如图，选中“二班”同学的语文成绩，就会在下方看到这些数字的和、平均值、计数等。

这个方法虽然能同时显示多个函数的计算结果，但只能显示你选中的数据的计算结果，不够智能。所以这里推荐另外的两种方法。

使用超级表格

步骤：选中所有的数据，按“Ctrl + T”快捷键一键转成超级表格，接着选中“表格工具”（或“设计”）菜单下的“汇总行”。注意，必须先单元格表格中的任意一个单元格，才看得到“汇总行”。

并选择好每一列要汇总的方式，平均值？计数？然后筛选数据后，就会自动更新计算结果了。

但，这个方法也有一些小缺憾。

首先筛选后，每次只能看到一个函数的计算结果，不能同时看到的和、平均值等多个函数的计算结果。

其次，序号不能自动更新，必须和subtotal一起使用才行。

再次，如果不喜欢套用表格样式，修改表格的格式比较麻烦。

使用subtotal函数

使用subtotal函数就没有超级表格那些问题了。多个函数、序号都可以自动更新，表格格式也可随意修改。

那么怎样使用subtotal函数呢？

如图所示，在D13输入公式“=SUBTOTAL(102,D2:D11)”就可以统计出D2:D11区域有多少个学生成绩，当筛选时，会自动计算筛选后的有多少个成绩。

那么序号、平均值、最高分、和等又是怎样的公式呢？

从这张表格我们可以知道，想求哪个函数的结果，只要将subtotal函数的第一个参数设置相应的序号即可。第二个参数是单元格引用，本例中我们将它设置成了“D2:D11”，这也就是整个表格“语文”数据所在单元格了，不管数据怎么筛选，数据都在这个区域之内，因此可以更新计算结果。

注意，汇总行和原始数据之间至少要留一行空白，否则筛选数据时会将汇总行隐藏了。

关于序号

表格中序号公式是“=SUBTOTAL(103,$C$2:C2)*1”，数字“103”表示计算非空单元格的个数，也就是只要“姓名”下单元格不是空的，就可以计算在内。“$C$2:C2”在向下填充时会自动变成“$C$2:C3”、“$C$2:C4”这样就可以计算可见单元格的个数。

为什么公式中要“*1”呢？这是因为subtotal筛选时会遗漏了最后一行，加上“*1”之后就不会有问题了。当然，你也可以使用“+0”的方式规避问题。

谢谢阅读，每天学一点，省下时间充实自己。欢迎点赞、评论、关注和点击头像。

在工作中动态图表对统计观察数据有很大帮助，比普通图表也节省了大量时间，不用时时根据数据做新的图表只要添加新的数据图表会自动更新。

OFFSET函数是达到目的的关键，说起OFFSET函数，真的是像雾像雨又像风，从复杂的数据汇总、到数据透视表再到高级动态图表，都离不开OFFSET函数的默默付出。通过OFFSET函数，可以生成数据区域的动态引用，这个引用再作为半成品，通过后续的处理加工，就可以为图表和透视表提供动态的数据源、为其他函数生成特定的引用了。

offset函数的语法格式=offset（reference,rows,cols,height,width）=（参照单元格，行偏移量，列偏移量，返回几行，返回几列）

参数Reference——参照单元格（单元格或相连单元格区域的引用）。否则，offset函数返回错误值#VALUE!。

参数Rows——行偏移量，正数（代表在参照单元格的下方），负数（代表在参照单元格的上方）。

参数Cols——列偏移量，正数（代表在参照单元格的右边）或负数（代表在参照单元格的左边）。

参数Height——返回几行（必须为正数），即所要返回的引用区域的行数（参照单元格移动完成后，再以此为起点向下扩展几行）。

参数Width——返回几列(必须为正数），即所要返回的引用区域的列数（参照单元格移动完成后，再以此为起点向右扩展几行）。

第四、第五参数height（范围高）和width（范围宽）：帮助里面讲的应该是正数，因为范围的高和宽，通常都是正数。若以负数表示，则不但没有高和宽的区域，反而是一个负值（即比0值还小），数据变成反过来了。很多情况，反过来的区域是不存在于工作表当中。也不是所有引用区域都不存在，就像有些格式，引用参数是从下面往上（第四参数height是个负数）规划出一个引用区域，只要引用区域的范围高小于源数据的高，就不会超出工作表，公式照样成立。

下面用一个实例来说明下，一个销售数据表每天的数据是随时更新的，我们要柱形图随着数据的更新也变化，总是显示最近一周的数据图。

首先，在【公式】选项卡下，单击【定义名称】，分别定义两个名称：日期=OFFSET($A$1,COUNT($A:$A),0,-7)。销售额=OFFSET($B$1,COUNT($A:$A),0,-7)。

同理

说说定义名称日期这个公式的意思：

COUNT函数对A列数值计数，结果作为OFFSET函数的行偏移参数。

OFFSET函数以A1为基点，向下偏移的行数是COUNT的结果，也就是A列有多少个数值，就向下偏移多少行。这时候就相当于到了A列数值的最后一行，给定的偏移列数是0，新引用的行数是-7，得到从A列数值的最后一行开始，向上7行这样一个动态的区域。如果A列的数值增加了，COUNT函数的计数结果就增加了，OFFSET函数的行偏移参数也就随之变化。就相当于一竿子捅到底，然后向上引用7行，所以得到的始终是最后7行的引用。

下面就是根据数据插入图表：

点击编辑设置：

再输入几个数据与上图对比看下结果：

关于序号的自动更新，网上已有很多文章，大部分都是使用=ROW()实现的。但这种方法是半自动。如果插入行，还需要向空行中复制公式，有些不方便。

有没有删除行或插入行后，不需要任何操作，序号就自动更新的方法呢？不止于此，今天提供的方法，还可以实现隐藏行后序号也可以自动更新。嘿嘿！！这效果能让你完全满意吧！

效果演示

插入行号，序号依然连续

删除行后，序号依然连续

筛选后，序号依然连续

看到这儿，估计你已忍不住想问方法了。

操作步骤：

1 、插入辅助列，输入公式 =1。加入这列是方便在序号列使用公式统计行数。

2 、在序号列输入公式=SUBTOTAL(2,B$1:B2)

公式说明：

SUBTOTAL函数是excel中唯一能忽略隐藏行的统计函数2 表示是统计B列的数字个数B$1:B2是利用$设置一个不断拉伸的区域

3 、插入选项卡 - 表格。这步的目的可以在插入行后，公式可以自动复制到空行中。

Excel解释说明：至此，功能强大的序号自动更新功能设置完成，以后你可以任性的删除行、插入行、筛选行，而不用操心序号不连续了。

转载自百家号作者：Excel函数公式

在实际的工作中，经常会遇到函数公式是正确的，但是计算结果却不正确的情况，遇到此类情况，往往是一脸的无助……今天我么就针对函数公式完全正确但得不到正确结果的情况做一下分享和解读。

一、输入公式后不计算，却在单元格内显示公式本身。

如图所示，在目标单元格中输入公式：=C3*D3后，并未得到正确的计算结果，而是显示公式本身。

原因：

将目标单元格的格式设置成了文本类型。

解决办法：

方法：

1、选定目标单元格。

2、删除原有公式，并设置单元格格式为【常规】。

3、输入公式并填充。

二、修改数据源后结果不自动更新。

如图所示，第一次计算可以得到正确的计算结果，但是修改数据源之后，销售额并不自动更新。这很显然不符合我们的实际应用。

原因：

目标单元格的【计算选项】为【手动】模式。

方法：

1、选定目标单元格，【公式】-【计算选项】-【自动】。

三、目标单元格格式为【常规】，可单元格内还是显示公式。

从图中可以看出，目标单元格的格式为【常规】，且通过分列也无法显示计算结果，很显然不是单元格格式的问题。

原因：

目标单元格设置了【显示公式】。

1、选中目标单元格，【公式】-【显示公式】。

结束语：

公式正确但得不到计算结果的情况，主要有以上三种表现，对于使用Excel的人来说具有极大的帮助作用和指导意义，同时对于提高工作效率具有很大的帮助。

如果大家有更多的实用技巧，欢迎大家在留言区讨论交流哦！

EXCEL表格的公式问题 , 在EXCEL表格里面设置的自动求和公式等，突然不进行运算，复制时总是显示复制的数值，也不进行自动累加了。但双击进入单元格后，再按一下回车就自动求和了，可在输入其他数据又不变了。

如何解决那？

2003版的在工具-选项-公式中修改设置

2010版的在文件-选项-公式中修改设置

填充序号问题是我们在Excel表格制作中经常遇到的一个问题，以前用的都是固定方式，删除一行后，下面的序列号就不对了，又要重新做。当修改的次数多时，改的也多，工作量大也容易出错。今天就教大家一个利用公式填充序列号的方法，这个方法能自动更新中断的序列号，不用再操心序号的问题，效率很高。下面就把详细把过程讲一下，大家掌握用公式填充序号后能大大提升工作效率，一起来看一下。

1、以下是要填充的Excel表格

2、在需要输入序号的A2单元格处输入公式 =ROW()-1

3、回车后，得到的序号是1

4、鼠标放在A2单元格往下拉

5、得到的序号是1、2、3、4的顺序

6、关键点来了，当我们删除李四所在的行时

7、王五行也移上去了，同时王五所在行序号变成新的了，从上到下序号没有中断，序号变成了 1、2、3

总结：通过上面用公式填充序号，可以快速生成所需要序号，并且无论怎么删除都不会中断，用时短，效率高。快去试试吧！

工作中，表格中一般会有序号（No）一列，我们经常会对表格中的数据进行筛选、隐藏等操作。那么，当我们筛选、隐藏数据后如何保持序号（No）的同步连续更新呢？

一、筛选No自动更新。

方法：

1、选定目标单元格。

2、输入公式：=SUBTOTAL(3,B$3:B3)。

3、Ctrl+Enter填充。

参数说明：

1、3为特定含义的代码，不用做更改。

2、B$3：B3为序号（No）列后的第一个活动单元格。

二、隐藏No自动更新

方法：

1、选定目标单元格。

2、输入公式：=SUBTOTAL(103,B$3:B3).

3、Ctrl+Enter填充。

三、筛选汇总自动更新。

方法：

1、选定目标单元格。

2、输入公式：=SUBTOTAL(9,B$3:B3).

3、Ctrl+Enter填充。

四、筛选汇总自动更新。

方法：

1、选定目标单元格。

2、输入公式：=SUBTOTAL(109,B$3:B3).

3、Ctrl+Enter填充。

五、小结：

通过上面的四个示例，相信大家对序号和汇总的自动更新已经掌握了，但是对第一参数可能还心存疑虑，请看下图：

第一列为功能代码，对应的为相应的功能函数，大家在应用的时候没有必要全部记忆功能代码，只需在输入参数时根据预提示功能输入相应的功能代码即可。是不是非常的简单……

经常在表格中插入或删除行，序号却要手动调整，十分麻烦。这一篇将介绍不论插入行还是删除行，表格中的序号都会自动更新的方法。

方法一。

先看效果，增减行数时，不仅序号可以自动更新，单元格的填充色也会跟着更新。

步骤很简单，在A2单元格中输入公式“=ROW()-1”，接着向下填充。ROW()函数用于获取行号，因此增减行数时，序号就会自动更新了。

不过很多朋友可能会得到如下图所示的结果，序号更新了，可是新增加的行没有自动填充序号。

这是因为，当你输入ROW公式之后，需要选中表格，点击“插入”——“表格”，这样才能制作出一个方便组合和分析的表格，才能有第二张图的效果。

方法二。

和方法一基本相同，只是使用SUBTOTAL函数替换ROW函数，在B2单元格输入公式“=SUBTOTAL(103,$C$2:C2)”，然后填充到其他单元格。效果和ROW也略有不同。

增加行时：使用ROW公式会自动更新所有的序号；而当增加的行是空白的，使用SUBTOTAL公式，则填充和上一行相同的序号，当填充了新的内容后，SUBTOTAL公式才会自动更新序号。

删除行时：和增加行类似，ROW公式会相应的更新之后的所有序号；当下一行单元格没有内容时，SUBTOTAL公式将使下一行序号和上一行保持一致。

隐藏行时：ROW公式不会更新序号，SUBTOTAL公式会同步更新序号。

不论方法一还是方法二，WPS表格中都不适用。

Word\WPS文字中的表格。

Word\WPS文字中的表格就简单多了，只要填充项目编号，不论是增加几行还是减少几行，序号都会自动更新。谢谢阅读，欢迎点赞、评论、关注和点击头像。