本文目录一览：

据说这是Excel最难的函数，但它在这些场景非常有用

一般来说，函数的学习普遍都比较容易，因为你只需要在百度上输入Excel+函数名称，就会出现大量的教程网页，所以要学习函数简直太多资料了。

但在我线下的数据分析培训中，发现有一个函数，普遍人认为它是难以理解和掌握的。

没错，就是OFFSET，那么它到底难在哪里？

其实是因为它和常用函数不一样的地方是： 它返回的结果，并不是一个值，往往是一个单元格区域

这次文章给大家介绍一下OFFSET的语法用法，以及它的一些应用的场景。

以上是函数的语法解释，为了更便于理解，我们来一些使用示范，先来看看数据源：

OFFSET函数使用示范：

1、基础用法→返回一个值

意思就是指，以A1为起点，先向下走一行，再向右走一列，也就是取值的结果就是B2单元格了，所以返回的结果是1，类似的你应该都能理解了：

2、进阶用法→返回一个单元格区域（比较常用）

意思是指以A1为起点，先向下走一行，再向右走一列，再取1行1列（其实就是取一个单元格），即取值的结果还是B2单元格了，所以返回的结果还是1

再来一个看看：

意思是指以A1为起点，先向下走一行，再向右走一列，再取2行2列（其实就是取了一个单元格区域），即取值的结果是B2:C3这个位置，所以返回的结果是一个数组来的。在实际的学习测试中，可以选中2X2的单元格区域，输入公式后再按下CTRL+SHIFT+ENTER，这样就可以让结果显示出来。

经过以上2点介绍，如果你还跟随实操练习的话，肯定是可以明白OFFSET的基本用法了。

但是学习单个函数不难，最难的是嵌套的应用，因为需要考虑场景与逻辑。而这方面的学习，就需要通过实操经验去积累了，当然关注本公众号或者加入数说会员也是一个很好的学习渠道～

1、解决VLOOKUP的查询表Key列一定要在最左的问题

场景还不清楚？直接看图片：

使用vlookup函数，在左图的数据结构是可以的，但若月份在右侧，vlookup就无法查询了！

解决这个问题，网络教程中有些是介绍使用数组函数重构新表，但我非常不建议使用，运算效率极低，数组函数玩深了会容易走火入魔～

使用INDEX+MATCH也是一个很好的解决方案，今天讲OFFSET，我们就介绍用OFFSET去实现：

虽然是有点复杂，但确实是可以实现，这个方法就当开拓思维吧~（实际应用中建议使用index+match）

2、结合定义名称生成动态单元格区域

定义名称是一个很好的功能，它可以把固定单元格定义成一个自定义的名字，也能把OFFSET返回的一个动态区域定义一个名字，然后再进行后续的引用。

所以用OFFSET函数，嵌套COUNTA函数作为取值行数的动态识别，再定义为名称的话，就能获取到一个动态的单元格区域了！

有了动态的单元格区域，那你做透视表，图表，都可以实现自动扩展数据源了！

3、制作综合数据分析模板

你的日报、周报、月报等，梳理好报表逻辑后，整合动态图表来进行呈现分析，将能大大提升你的报表效率！例如这种效果：

在制作该模板的过程，也有使用到OFFSET函数，去构造出动态的分析对象列，指标列，这样做也可以省去做辅助列的操作，也能提升运算效率！

如果你还想学习更多的动态图表高阶应用，欢迎加入《数说》会员，学习以下的系列课程，带你玩好动态图表，提升报表的效率！

----------------------

我的《数说》栏目合计已有3600+的《数说》会员，现已建立起数据分析的专属交流社群，汇集了来自零售、鞋服、互联网、电商、制造、医疗等行业，更方便地让大家可以一起学习，交流，成长。

如果你也想学习 解读数据 报告、提高 数据分析思维 ，提升 Excel实操能力 ，想 与同行交流 的朋友……欢迎加入《数说》栏目吧！

戳 【阅读原文】 也能加入《数说》会员喔~

熟练掌握日常生活当中常用的Excel表格函数公式！让你办公

日常生活当中，很多文员是需要与表格之类的文件打交道的，毕竟，这种文件的形式对于公司的业绩来说也是非常的重要的，比如公司统计当年的工作绩效、统计人员情况等，都是需要运用表格进行统计的。Excel是我们日常生活当中最常见也是最常用的表格制作工具，这款办公工具是非常的不错的，而且，这款办公的工具也是非常的实用的。

但是，对于很多Excel使用小白来说，对于这款工具的运用似乎并不娴熟，尤其是对于函数公式的运用似乎并不是特别了解，有的也只是了解一些皮毛知识等，小编为大家整理了日常生活当中最常用的函数公式，整理如下：

1、条件判断：IF函数。运用方法：在选定的单元格内键入公式：=IF(C3=90,"优秀",IF(C3=80,"良好",IF(C3=60,"及格","不及格")))。最后运用快捷键Ctrl+Enter进行填充。

这个函数的目的就是判断成绩所属的等次。

2、条件求和：SUMIF、SUMIFS函数。其目的是求男生的总成绩和男生中分数大于等于80分的总成绩。

运用方法如下：在对应的目标单元格中输入公式：=SUMIF(D3:D9,"男",C3:C9)或=SUMIFS(C3:C9,C3:C9,"=80",D3:D9,"男")。

3、条件计数：COUNTIF、COUNTIFS函数。目的就是计算男生的人数或男生中成绩=80分的人数。运用方法如下：在对应的目标单元格中输入公式：=COUNTIF(D3:D9,"男")或=COUNTIFS(D3:D9,"男",C3:C9,"=80")。

4、数据查询：VLOOKUP函数，这个函数的目的就是查询相关人员对应的成绩。运用方法如下：在目标单元格中输入公式：=VLOOKUP(H3,B3:C9,2,0)。

Excel与word是我们日常生活当中最常见的办公类的软件，这两款软件的应用前景是非常的广泛的，尤其是在办公领域，而Excel表格的应用场景似乎是更加的广泛的，所以，只有熟练的运用上述的几种常见的函数公式，才能更好的提高其工作效率，希望对于办公软件小白有所帮助。

随时更新：Excel表格中常用函数及其作用

换公司啦，职位也由玩法/系统策划改为了数值策划。专门出一篇来纪录日常会用到的Excel常用函数及其功能。

Vlookup函数

VLOOKUP函数是Excel中的一个纵向查找函数，它与LOOKUP函数和HLOOKUP函数属于一类函数。

Vlookup函数中一共有四个值：查找值、数据表、序列数、匹配条件。Vlookup函数的应用场景一般为：有一个数据量较大的表格，数据表的第一列对应着表内的很多项信息。要找到第一列的某个值对应表内的某个数值。

查找值为数据表第一列的某个值，数据表就是整个大的表格，序列数为数据表的某一列（一般不会是第一列）。

匹配条件为是否是模糊查找，即函数 VLOOKUP 查找时是精确匹配，还是近似匹配。如果为FALSE或0 ，则返回精确匹配，如果找不到，则返回错误值 #N/A。如果匹配条件为TRUE或1，函数 VLOOKUP 将查找近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 匹配条件的最大数值。如果匹配条件省略，则默认为模糊匹配。匹配条件最好是明确指定，默认是模糊匹配！

举例：某公司新来了10名员工，入职登记表上有编号、员工姓名、年龄、职位、性别、薪酬等信息。现在要把全部员工的薪酬信息引用出来，列成薪酬表。并且保证入职登记表上的薪酬改动时，薪酬表上的薪酬也会改动。

在薪酬表上的“薪酬”一列使用Vlookup函数。以编号1为例：

查找值 为A3，即编号1。 数据表 为$A$3:$F$12，即整个A3到F12的区域。加“$”表明该引用为绝对引用，下拉时不会改变引用区域。 序列数 为6，即整个表的第6列，即“薪酬”列。 匹配条件 填FALSE，即为精确匹配。填完编号1的员工后，下拉到编号10即可。

CONCATENATE函数

功能：把多个字符文本或数值连接在一起，实现合并的功能。一般可用于若一个格内同时存在文字和引用的数字。可先将文字和引用数字分别放在不同格内，然后用函数将这几个字符串合在一起。

如何利用公式实现111222333444...一直顺延下去（连续出现n个数字）

在起始E1填入“1”。第二格E2开始写入公式。

1.COUNT($E$1:E1)，计算所写公式格数到E1的距离。下拉后会变成“123456789”。

2.在COUNT函数外嵌套MOD函数。MOD(COUNT($E$1:E1),3)，计算这些距离除以3所得的余数。下拉后形成120120120不停重复。

3.在MOD函数外嵌套IF函数，判断余数是否为0，若为0，则给上一格值+1后再赋予本格，不为0则直接将上一格值赋予本格。下拉后就形成了111222333......

IF(MOD(COUNT($E$1:E1),3)=0,E1+1,E1)

若想连续重复n次，则MOD函数中的除数为n即可。

MOD函数

MOD函数是一个求余函数，即时两个数值表达式做除法运算后的余数。最后得到的值即为数值除以除数得到的余数。

利用刚刚的111222333......数列，横向一行一行引用表格

现在有一个表格，有三列，需要横向引用。引用完第一行后，继续引用第二行。

先利用刚刚的公式拉一个辅助列。辅助列的格式为111222333...同一个数字连续重复n次，n取决于表格有几列。

辅助列第一行，即第一个“1”，用INDEX函数。数组为第一列，行序数为E1，即第一个“1”。注意数组对第一列的引用为绝对引用。

辅助列第二行，即第二个“1”，用INDEX函数。数组为第二列，行序数为E2，即第二个“1”。

辅助列第三行，即第三个“1”，用INDEX函数。数组为第三列，行序数为E3，即第三个“1”。

把第一行的各个列引用完后，下拉，即可获得横向引用表格。

INDEX函数

INDEX函数就是引用表格中的某个值。

数组：要引用的区域。行序数：要引用的行数。列序数：要引用的列数。

上图的例1中，数组为B1到B12的一列绝对引用，行序数为E2，E2的值为1，即B1-B12列的第一行。因为只有一列，所以列序数不用填。

我在EXCEL里编写了自定义函数，但是有很多数据，运算时间非常缓慢

我也是经常使用EXCEL函数，也曾经面对和楼主同样的问题，其实你的问题没问到点上：

表格写入函数，特别是如果反复调用，这个文件的冗余垃圾可定会越来越多，文件会越来越大，打开也就会越来越慢，我遭遇的一个单文件24M的表格文件，打开需要10分钟，可是经过瘦身之后，只有5M多，打开就快多。

说白了，其实并不是你的电脑慢，而是这个表格写入内存需要时间。

我的解决办法仅供参考：

1、新建一个空白表格文件B（暂定）

2、打开你的表格原文件A，复制A表格某标签的全部内容，选择性粘贴（数据）到B文件上，然后再选择性粘贴（格式）到B文件上。（如果表格中有合并的单元格，可能需要分区域多次粘贴）

3、把A表格中的函数以“文本形式”粘贴到B表格中，当然只需要粘贴第一行，或第一列，其余的可以拖拽（这个你明白的）。

4、全部做完后，把原文件A的文件名更改为“A（备份-1）”，把B文件改名为A。

5、文件全部“复制”好后，把这个文件作为一个“模板”，另存一下。下次做的时候，就不用再这样麻烦了。

说明：

一定不要整页粘贴，这样会把表格中的冗余垃圾也带过来的。

目前我使用函数表格中，每行有46个函数，共500余行，且包含了筛选、综合判断、阵列等多种函数，按这个方法使用，也都OK的。楼主试试吧。