Excel OffSet函数的使用方法，含与Sum、Match、CountIf、If、Or、Row组合实现动态求和、一个重复多个、分段

在 Excel 中，OffSet函数用于返回以某个单元格为基准且距离该单元格指定行数和列数区域的引用。引用的单元格可以在基准单元格的上下或左右，这取绝于OffSet函数的参数 Rows 和 Cols 的正负；引用单元格的区域大小取绝于参数 Height 和 Width 数值。

OffSet函数常与 Sum、Match、CountIf、If、Or、Row 等函数组合使用，例如 Sum + OffSet + Match + CountA函数组合实现动态求和、OffSet + Int + Row函数组合实现把一个重复多个、CountIf + OffSet + Match + CountIf函数组合实现分段统计。

一、Excel OffSet函数语法

1、表达示：OFFSET(Reference, Rows, Cols, [Height], [Width])

中文表达示：OFFSET(引用单元格, 行数, 列数, [返回引用高度], [返回引用宽度])

2、说明：

A、Reference 必须是对单元格或相邻单元格区域的引用，否则会返回值错误 #VALUE!；返回单元格以 Reference 为基准。

B、Rows 指距 Reference 左上角向上或向下的行数。例如 Reference 为 B3，若 Row 为 2，则是指 B3 下 2 行；若 Row 为 -2，则是指B3 上 2 行。即 Row 为正数时，是指基准单元格的下面；Row 为负数时，是指基准单元格的上面。

C、Cols 指距 Reference 左上角从左到右的列数。例如 Reference 为 B3，若 Cols 为 1，是指 B3 右边 1 列（即 C 列）；若 Cols -1，是指 B3 左边 1 列（即 A 列）。即 Cols 为正，指基准单元格右边；Cols 为负，指基准单元格左边。

D、Height 和 Width 必须为正数；如果省略 Height 或 Width，则默认它们与 Reference 相同。

E、如果 Rows 和 Cols 超出工作表边缘，OffSet函数将返回引用错误值 #REF!。

二、Excel OffSet函数的使用方法及实例

（一）Rows 或 Cols 为 0 的实例

1、双击 A10 单元格，把公式 =OFFSET(A1,3,) 复制到 A10，按回车，返回“黄子辛”；再次双击 A10，把公式改为 =OFFSET(A1,,2)，按回车，返回“职务”；操作过程步骤，如图1所示：

图1

2、公式说明：

A、公式 =OFFSET(A1,3,) 中，A1 为基准引用单元格，3 为距离 A1 的行数，省略了 Cols ，默认取 0；省略了 Height 和 Width，默认它们与 A1 的高度和宽度相同。公式的意思是：返回 A1 下 3 行 0 列的单元格引用，即返回 A4 中的“黄子辛”。

B、公式 =OFFSET(A1,,2) 省略了 Rows，默认取 0；省略的 Height 和 Width 取值与上面相同；公式的意思是：返回 A1 右边 0 行 2 列的单元格引用 C1，即返回“职务”。

（二）返回距基准单元格左上角指定行数的上下引用实例

1、双击 A9 单元格，把公式 =OFFSET(A2:B3,1,1,1,1) 复制到 A9，按回车，返回“瓜类”；再次双击 A9 单元格，把 OffSet 第二个参数 1 改为 -1，按回车，返回“分类”；操作过程步骤，如图2所示：

图2

2、公式 =OFFSET(A2:B3,1,1,1,1) 中，A2:B3 为引用单元格区域，意思是：返回 A2:B3 左上角的单元格（即 A2）下 1 行、右 1 列且高度与宽度都为 1 的单元格引用，即返回 B3 中的“瓜类”。把第二个参数改为 -1 后，返回 A2:B3 左上角单元格上 1 行、右 1 列且高度与宽度都为 1 的单元格引用，即返回 B1 中的“分类”。

（三）返回距基准单元格左上角指定列数的左右引用实例

1、双击 C10 单元格，把公式 =OFFSET(C4:D5,3,2,1,1) 复制到 C10，按回车，返回 892；再次双击 C10，把 2 改为 -2，按回车，返回“6月”；操作过程步骤，如图3所示：

图3

2、公式 =OFFSET(C4:D5,3,2,1,1) 中，C4:D5 为引用单元格区域，意思是：返回 C4:D5 左上角单元格（即 C4）下 3 行、右 2 列且高度与宽度都为 1 的单元格引用，即返回 E7 中的 892；把 2 改为 -2 后，变为返回 C4 下 3 行、左 2 列的单元格引用，即返回 A7 中的“6月”。

（四）返回多单元格引用实例

1、双击 A8 单元格，把公式 =OFFSET(A2:B3,2,2) 复制到 A8，按回车，返回值错误 #VALUE!；再次双击 A8，在公式后添加参数高度和宽度 1，按回车，返回 5；再双击 A8，把后面两个 1 都改为 2，按 Ctrl + Shift + 回车，也返回 5；操作过程步骤，如图4所示：

图4

2、公式 =OFFSET(A2:B3,2,2) 与 =OFFSET(A2:B3,2,2,2,2) 是一样的，因为省略的高度与宽度默认用 A2:B3 的高度和宽度，按回车时它们返回值错误 #VALUE!，而按 Ctrl + Shift + 回车，却能返回 5，因为返回的是一个数组，这种情况通常用于与其它函数组合，具体见下文的实例。公式 =OFFSET(A2:B3,2,2,1,1) 按回车能返回 5，因为只返回一个单元格。

（五）返回 #REF! 错误的实例

1、双击 A9 单元格，把公式 =OFFSET(A2:B3,-2,2,1,1) 复制到 A9，按回车，返回引用错误 #REF!；操作过程步骤，如图5所示：

图5

2、公式 =OFFSET(A2:B3,-2,2,1,1) 的 -2 表示 A2 上面的两行，而 A2 上只有一行，所以返回引用错误。

三、Excel OffSet函数的扩展使用实例

（一）OffSet + Match函数组合

OffSet + Match函数组合的详细解析，在《Excel Match函数的使用方法的8个实例，含与Sum、OffSet、Indirect组合实现提取数据和对变化区域动态跨表求和》一文中已经介绍，查看请点击书名号中的文章标题。

（二）Sum + OffSet + Match + CountA函数组合实现动态求和

1、假如要求任意月份的各类服装销量之和。双击 B11 单元格，把公式 =SUM(OFFSET(A1,MATCH(A11,A2:A9,),1,1,4)) 复制到 B11，按回车，返回“2月”的各类服装销量之和 1601；双击 B13，把公式 =SUM(OFFSET(A,MATCH(A13,A:A,),1,1,4)) 复制到 B13，按回车，返回“1月”的各类服装销量之和 1614；选中 B13，把鼠标移到 B13 右下角的单元格填充柄上，鼠标变为加号后，按住左键，往下拖，则求出“4月和7月”的各类服装销量之和；操作过程步骤，如图6所示：

图6

2、公式说明：

两个公式是一样的，只不过后一个公式要往下拉，把 A1、A2 和 A9 的行加 $ 变为绝对引用，下面只以后一个公式 =SUM(OFFSET(A,MATCH(A13,A:A,),1,1,4)) 为例说明。

A、A:A 以数组形式返回 A2 至 A9 中的所有月份，往下拖，A2 和 A9 都不会自动加 1，如 A2 不会变为 A3、A4 等。

B、MATCH(A13,A:A,) 用于返回 A13（即“1月”）在 A2 至 A9 中的位置 1；Match函数省略了最后一个参数，默认取 0 表示精确匹配。

C、则 OFFSET(A,MATCH(A13,A:A,),1,1,4) 变为 OFFSET(A,1,1,1,4)，接着以 A1 为基准，返回 A1 下 1 行、1 列且高度为 1、宽度为 4 的单元格引用，即返回 B2:E2。

D、则公式变为 =SUM(B2:E2)，最后对 B2:E2 求和，所以返回 1614。

提示：如果列数比较多，公式中的最后一个参数 4 可以用 CountA(B:E) 或 COUNTA(:)-1 代替，即用CountA函数统计列数，则公式变为 =SUM(OFFSET(A,MATCH(A13,A:A,),1,1,CountA(B:E)))。: 表示引用第 1 行。

3、如果要动态求任意列的和，应用此公式 =SUM(OFFSET(A,1,MATCH(A11,B:E,),COUNTA(A:A),1))，演示如图7所示：

图7

（三）OffSet + Int + Row函数组合实现把一个重复多个

1、假如要把 A 列的每个水果名称重复三个。双击 A9 单元格，把公式 =OFFSET(A,INT((ROW(A1)-1)/3),0) 复制到 A9，按回车，返回“龙眼”；选中 A9，用往下拖的方法把剩余名称重复三次；操作过程步骤，如图8所示：

图8

2、公式 =OFFSET(A$2,INT((ROW(A1)-1)/3),0) 说明：

A、ROW(A1) 用于返回 A1 的行号 1，当往下拖时，A1 会变为 A2、A3 等，再用 Row 函数返回它们的行号。

B、公式中的 3 表示重复次数；INT((ROW(A1)-1)/3) 用于把 (ROW(A1)-1)/3 的结果取整，只保留整数部分且不四舍五入；例如：(ROW(A1)-1)/3 结果为 0/3，则 INT(0) 返回 0；(ROW(A4)-1)/3 结果为 4/3，则 INT(4/3) 返回 1。

C、则公式变为 =OFFSET(A$2,0,0)，最后用 OffSet 返回 A2 下面 0 行 0 列的单元格引用，即 A2 中的“龙眼”。若 A1 变为 A4，则公式变为 =OFFSET(A$2,1,0)，最后用 OffSet 返回 A2 下 1 行 0 列的单元格引用，即 A3 中的“青皮西瓜”。

（四）CountIf + OffSet + Match + CountIf函数组合实现分段统计

1、假如要统计任意部门的“员工”个数。双击 G2 单元格，把公式 =COUNTIF(OFFSET(C1,MATCH(E2,B2:B10,),,COUNTIF(B2:B10,E2)),F2)复制到 G2，按回车，返回统计结果 2；双击 E2 单元格，把“行政”改为“销售”，按回车，返回“销售部员工”的统计结果 3；操作过程步骤，如图9所示：

图9

2、公式 =COUNTIF(OFFSET(C1,MATCH(E2,B2:B10,),,COUNTIF(B2:B10,E2)),F2) 说明：

A、MATCH(E2,B2:B10,) 用于返回 E2（即“行政部”）在 B2 至 B10 中的位置 3，Match 也省略了最后一个参数，默认取 0。

B、COUNTIF(B2:B10,E2) 用于在 B2 至 B10 中统计 E2（行政部）的个数，结果为 3，B2:B10 是统计范围，E2 是条件。

C、则 OFFSET(C1,MATCH(E2,B2:B10,),,COUNTIF(B2:B10,E2)) 变为 OFFSET(C1,3,,3)，意思是以 C1 为基准，返回 C1 下 3 行 0 列且高度为 3 的单元格引用，即返回 C4:C6。

D、则公式 =COUNTIF(C4:C6,F2)，最后在 C4 至 C6 中统计 F2（员工）的个数，行政部的员工只有两个，因此返回结果 2 正确。

提示：要统计的列（如“部门”）必须排序。

（五）If + Or + CountIf + OffSet + Row函数组合实现断码检查

1、假如要判断服装是否断码，假定连续四个尺码不为 0 为齐码，否则为断码。双击 H2 单元格，把公式 =IF(OR(COUNTIF(OFFSET(A$1,ROW(A1),ROW($1:$3),,4),">0")>=4),"否","是") 复制到 H2，按回车，返回“是”；选中 H2，用于单击单元格填充柄的方法返回剩余服装是否断码；操作过程步骤，如图10所示：

图10

2、公式 =IF(OR(COUNTIF(OFFSET(A$1,ROW(A1),ROW($1:$3),,4),">0")>=4),"否","是") 说明：

A、A$1 在上面的实现动态求和已经解释过；ROW(A1) 用于返回 A1 的行号 1；ROW($1:$3) 用于返回 1 到 3 的数组，即 {1;2;3}；4 为OffSet函数的宽度参数，用于取 4 个连续的单元格数值。

B、为什么要返回 1 到 3 的数组？经过上面运算，OFFSET(A$1,ROW(A1),ROW($1:$3),,4) 变为 OFFSET(A$1,1,{1;2;3},,4)，接着以 A1为基准，返回 A1 下 1 行，由于 Cols 参数是一个数组，第一次只能从数组中取出第一个元素 1，因此 OffSet 第一次返回 A1 下 1 行 1 列且宽度为 4 的单元格引用，即 B2:E2；第二次从数组中取第二个元素 2， OffSet 第二次返回 A1 下 1 行 2 列且宽度为 4 的单元格引用，即 C2:F2；同理，OffSet 第三次返回 D2:G2；取 1 到 3 的数组恰好到表格尺码的最后一列（即 G 列）；OffSet 最后返回 {#VALUE!;#VALUE!;#VALUE!}，数组中的 #VALUE! 依次代表 B2:E2、C2:F2 和 D2:G2。

C、则 COUNTIF(OFFSET(A$1,ROW(A1),ROW($1:$3),,4),">0") 变为 COUNTIF({#VALUE!;#VALUE!;#VALUE!},">0")，接着，以 ">0" 为条件对每个引用区域进行统计，B2:E2 有一个 0，因此统计结果 3；C2:F2 和 D2:G2 都有一个 0，统计结果也都为 3。

D、则公式变为 =IF(OR({3;3;3}>=4),"否","是")，接着从数组中取出第一个元素 3，并与 4 比较，由于不成立，因此返回 False；继续取第二、三个元素，它们都不成立，也返回 False；则公式变为 =IF(OR{FALSE;FALSE;FALSE},"否","是")，由于数组中全为假，所以 Or 返回FALSE，则公式变为 =IF(FALSE,"否","是")，If 的条件为假，所以返回“是”。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。