offset函数数组用法（Excel的offset函数怎样把区域返回到另外一个区域里面）

本文目录

• Excel的offset函数怎样把区域返回到另外一个区域里面
• 用offset函数引用，一列中最后54个数值格式的单元格
• Excel表格怎么统计人事资料
• offset 数组函数,返回行值为1时,为什么结果会重复(返回行值大于1时正常)
• excel求多条件不同数值的函数公式
• excel的offset函数报错
• 怎样让OFFSET（）数组公式显示出引用单元格值为0的显示出零来
• excel用数组公式获取一列中非空（非零）值，看一看
• 据说这是Excel最难的函数，但它在这些场景非常有用

Excel的offset函数怎样把区域返回到另外一个区域里面

在单元格F6里输入函数，如图所示：

输入函数

offset函数的结构式是=offset（参照区域，行数，列数，）。在本函数里以E6为参照区域，往上数5行是王敏，由于是向上数，所以是-5。列数向左数3列，所以是-3。王敏是一个单元格的高度与宽度。

行数、列数、高度、宽度示意图

回车后，下拉即可。

回车----下拉

如果想返回后面的3、6、8只需要右拉即可。

用offset函数引用，一列中最后54个数值格式的单元格

计算A列有多少个数字 count(a:a)

=OFFSET(INDIRECT("A"&COUNT(A:A)),-54+ROW(A1),,)公式下拉

Excel表格怎么统计人事资料

制作员工档案即可

操作方法如下：

第一步：准备工作表

首先要在一个工作簿中准备两个工作表，一个是“员工信息”，用于输入所有员工的资料；另一个是“查阅”，用于逐一显示每个员工的信息。 

在“员工信息”表中输入所有职工的信息，并在第十四列（即N列）中放入各员工的照片，照片可用复制粘贴的方法，也可用“插入－图片－来自文件”的方法。如果图片的尺寸大于N列的单元格，可用鼠标点中图片，待图片四周出现八个小圆圈，就可以用鼠标对图片进行缩放了，最后一定要将图片放置在N列各单元格的中心位置。

第二步：输入公式在“查阅”表中，根据上面插图设置项目，并输入下面公式： E2单元格，显示序号，公式 =OFFSET(员工信息!$A$1,$F$1,0) 公式含义：利用OFFSET函数从“员工信息”表的A1单元格开始，向下偏移某行（$F$1表示在“查阅”工作表的F1单元格中的值，即偏移行数）及向右偏移0列后的单元格内容，显示在本公式所在单元格中。 OFFSET函数的一般用法： ?OFFSET（起始单元格，下偏移行数，右偏移列数) 起始单元格自身的偏移量为零，偏移数值为负数时则向相反方向移动。提示：由于“起始单元格”不在当前工作表中，所以要加上目标工作表的标签名字，即“员工信息!$A$1”，而“下偏移行数”引用的是当前工作表的F1单元格，所以F1前面就不用加上工作表的标签名了。B3单元格，显示姓名，公式 =OFFSET(员工信息!$A$1,$F$1,1)公式含义：同上，利用OFFSET函数取出对应的员工姓名。最后的参数1表示从“员工信息”表的A1单元格向右偏移一列，即“员工信息”表的B列。D3单元格，显示身份证，公式 =OFFSET(员工信息!$A$1,$F$1,2)B4单元格，显示性别，公式 =OFFSET(员工信息!$A$1,$F$1,3)D4 =OFFSET(员工信息!$A$1,$F$1,4)B5 =OFFSET(员工信息!$A$1,$F$1,5)D5 =OFFSET(员工信息!$A$1,$F$1,6)B6 =OFFSET(员工信息!$A$1,$F$1,7)D6 =OFFSET(员工信息!$A$1,$F$1,8)B7 =OFFSET(员工信息!$A$1,$F$1,9)B8 =OFFSET(员工信息!$A$1,$F$1,10)B9 =OFFSET(员工信息!$A$1,$F$1,11)D9 =OFFSET(员工信息!$A$1,$F$1,12)

第三步：添加按钮公式完成后还要再添加一个“数值调节按钮”。按“视图－工具栏－控件工具箱”，调出“控件工具箱”工具条，从中选取“数值调节按钮”，然后添加到F1单元格下面。提示：这个“数值调节按钮”与“窗体控件”中的“微调”按钮差不多，只是属性设置不同。

在添加好的调节按钮上点鼠标右键，选“属性”，出现控件的属性设置窗口，如下图：

属性中的LinkedCell表示链接的单元格，图中设置为“F1”，当按动“数值调节按钮”时，F1单元格中就会显示相应的数值。属性中的Max表示显示最大值，可根据员工人数的最大值设置，本例中设置为“100”属性中的Min表示显示的最小值，设置为“1”就行了。设置完成后，就可以按动“数值调节按钮”来逐一显示员工数据了。按动时，当F1单元格显示1，就会通过表中的公式显示序号为1的员工信息；当F1单元格为2时，就会显示序号为2的员工信息。提示：如果按动“数值调节按钮”时，F1格中的数值不变化，这可能是控件还处于“设计”状态，需要点击一下“控件工具箱”工具条中的“退出设计模式”。

第四步：显示照片相对数据来说，显示照片要麻烦一些。在“查阅”工作表的E3:E6是一个合并单元格，并不能直接显示照片，需要插入一个控件来显示照片。从“控件工具箱”中选中“文本框”控件，在E3:E6区域中添加一个大小适中的文本框控件。

文本框添加好后，用鼠标选中它，就发现在表格上方的栏中出现一个公式，需要修改一下。把公式改成“ =图片”

公式中的等号要在英文状态下输入，等号后面的文字“图片”是定义的一个名称，下面就来定义这个名称。按“插入－名称－定义”，出现一个“定义名称”对话框，在“在当前工作簿中的名称”框中输入“图片”，在下面“引用位置”框中输入公式“=OFFSET(员工信息!$A$1,查阅!$F$1,13)”，按“确定”即可。这个引用位置中的公式也是使用了OFFSET函数，与上面介绍的用法一样。

第五步：验证效果上面这些设置完成后，就会看到添加的文本框控件中出现了员工照片，并且可以随着“数值调节按钮”的数值改变而显示对应的员工照片。序号为1的员工资料

序号为2的员工资料

offset 数组函数,返回行值为1时,为什么结果会重复(返回行值大于1时正常)

此公式是个数组函数，你在输入的时候是选择A8：G19区域按ctrl+shift+enter结束的。

原因就是：当你记录个数为N的时候，offset就会只有N行的内存数组。

                  你的记录个数为1，offset的内存数组就只有1行。

                 但是你选了区域，按了三键，每行都要执行offset，但offset只有1行，每行的公式都是一样，每行都要显示，所以就会显示重复的数据

你自己可以做个试验： 如图

当你按三键，A3：E7的行内容就是一样的

但如果你就选择第3行，再输入=A1：E1  结果就不重复了

解决的方法：

   1、用VBA，这个我不会，你得求助VBA高手，而且还很简单。

   2、固定你的数据范围。意思就是你在表中选择的公式输出范围与offset产生的内存数组范围一致。比如你选择A8:G1008输入三键结束的数组公式，你的offset函数就必须是个1000行7列的内存数组。

   然后就可以写公式：

  =if(row(1:1000)《=记录个数,offset(index(k3导出!a:a,起始行号),,,1000,7),"")

excel求多条件不同数值的函数公式

这不是多条件求和，

用offset函数，配和sum函数的数组公式就可以达到目的，

公式可以复制粘贴后直接使用，

具体为：=SUM(OFFSET(A1,MATCH("小明",A1:A6,0)-1,1,1,2))

，数组公式输入结束后需要同时间按下ctrl shift enter 三个键，产生花括号，

效果如图：

如有需要可以继续交流！

excel的offset函数报错

• offset函数是以指定的应用为参考系，通过上下左右偏移得到新的区域的引用。返回的引用可以是一个单元格也可以是一个区域。并且可以引用指定行列数的区域。

• 2

  它的语法形式是 OFFSET(reference,rows,cols,height,width)，下面就以一个实例应用做解释 。以A1单元格作为参考系，向下移动2行，向右移动2列，我们可以直接找到引用的单元格是 C3，结果是6。

  请点击输入图片描述

• 3

  在D3单元格输入公式=OFFSET(A1,2,2,1,1)，其中A1是参考系，接着的2,2分别表示下，右移动的行数和列数，同样向上，左则是负数。最后面的1,1表示引用的区域大小是一行一列，也就是一个单元格。

  请点击输入图片描述

• 4

  结果和我们肉眼看到的一样，函数公式中最后面的1,1可以省略，如下图，其就表示直接引用一个单元格。

  请点击输入图片描述

怎样让OFFSET（）数组公式显示出引用单元格值为0的显示出零来

1加辅助塔B，计算公式如下： B1 = IF（AND（OFFSET（A1，0）,1-A1：OFFSET（A1 ,5-A1，0） - $ A $ 1：$ A $ 5 = 0），“Y”，“”） 数组公式（CTRL + SHIFT + ENTER），并把它复制下来。 2过滤器列B“Y”值 数据 - 筛选 - 自动筛选“-B列下拉式选框”Y“ （完） -------------------------------- 以下为说明 公式说明： 如果其中的小区的五个连续细胞，标准序列A1：A5完全一致，则标志?。否则为空。 OFFSET（A1 ,1-A1，0）：一个单元格的值，则判断的过渡数。实施例中，2的值，向上移动一个单元格。 OFFSET（A1 ,5-A1，0）：一个单元格的值时，判断向下的行数。如价值，下三个单元。 OFFSET（A1 ,1-A1，0）：OFFSET（A1 ,5-A1，0）：的细胞基础，取决于除去五个连续单元的值的A细胞 （OFFSET（A1，0 ,1-A1）：OFFSET（A1 ,5-A1，0） - $ A $ 1：$ A $ 5 = 0）：两者相减为零。完全相同的序列。 $ A $ 1：$ A $ 5：可选连续间隔的标准进行比较。 扩展： 上述五个单元格。如果你想采取连续10个细胞，如1,2,3,4,5,6,7,8,9,10： B1 = IF（AND（OFFSET（A1，0）,1-A1：OFFSET（A1 ,10-A1，0） - $ A $ 1：$ A $ 10 = 0），“Y”，“”） 数组公式（CTRL + SHIFT + ENTER），并把它复制下来。

excel用数组公式获取一列中非空（非零）值，看一看

假如在A1:A10区域中有一列数据，其中包含数值“0”和空的单元格，现在需要将其中非零、非空的数据提取出来，并且按原数据的顺序排列，如图所示，可以使用下面的数组公式。在B1单元格中输入数组公式：=OFFSET($A$1,SMALL(IF($A$1:$A$100,ROW($1:$10),),ROW(A1))-1,0)公式输入完毕后，按Ctrl+Shift+Enter结束，然后向下填充即可。公式说明：上例A1:A10区域中，第1、2、4、10行包含非零、非空数据，先用“IF($A$1:$A$100,ROW($1:$10),)”来产生一个数列“{1;2;;4;;;;;;10}”，然后用SMALL函数来获取非空数值，最后用OFFSET函数返回单元格数据。OFFSET函数也可以用INDEX函数代替，如B1单元格中的数组公式可以写成：=INDEX($A$1:$A$10,SMALL(IF($A$1:$A$100,ROW($1:$10),),ROW(A1)))如果要仅仅获取A列中非空数据，即返回的数据中包括数值“0”，将上述公式中的“$A$1:$A$100”改为“$A$1:$A$10”即可：=OFFSET($A$1,SMALL(IF($A$1:$A$10,ROW($1:$10),),ROW(A1))-1,0)

据说这是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实操能力 ，想 与同行交流 的朋友……欢迎加入《数说》栏目吧！

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