技巧233 理解OFFSET函数.xls

OFFSET引引用用函函数数 OFFSET reference rows cols height width 以指定的引用为参照系 通过给定偏移量得到新的引用 返回的引用可以为一个单元格或单元格区域 并可以指定返回的行数或列数 基基本本原原理理 基点reference公式 OFFSET A5 2 2 5 3 C 7 E 11 ROWS 2 偏移后基点 COLUMNS 2HEIGHT 5 WIDTH 3 以上图示为OFFSET常规偏移示例 函数结果为引用 REF 注注意意事事项项 1 首先必须注意 reference必须为引用 包括函数产生的三维引用 不能为常量或数组 2 行偏移 rows 列偏移 cols为必输项 如省略必须用 逗号进行占位 缺省值为0 即不偏移 行数 height和列数 width为可选项 可省略 缺省视为与基点相同 常常规规应应用用 一 基点与行数 列数的关系 通常情况下 如果基点以单元格区域作为引用范围 如果不指定行数或列数 系统默认是引用区域的行数和列数来确定结果 公式1 OFFSET A5 C5 2 2 公式结果 C 7 E 7 公式2 OFFSET A5 2 2 3 公式结果 C 7 E 7 由于引用基点为A5 C5 3列区域 同时公式1中未指定行数或列数 所以最终结果仍然为3列区域 公式1与公式2结果相同 两者等价 公式3 OFFSET A5 C9 2 2 公式结果 C 7 E 11 公式4 OFFSET A5 2 2 5 3 公式结果 C 7 E 11 同理 公式3与公式4结果相同 两个公式等价 二 行数与列数对引用基点的影响 在上面的公式1和公式3中 都是引用基点为单元格区域引用 同时都未设置行数与列数的情况下的结果 但有时 基点为区域引用时 用户同时又指定了行数或 和 列数 那么用户必须清楚了解实际引用的基点区域 才能得出正确结果 例如已经定义名称 DATA OFFSET应用基础 P 32 R 36 例1 利用名称作为基点 得到右图的浅蓝色子区域公式 OFFSET DATA 1 2 公式结果 P 33 R 34检验 6 由于公式中指定了最终结果的行数 2 未指定列数 那么公式实际上以P32 R32区域作为引用基点 公式等同于 公式1 OFFSET P32 R32 1 2 公式2 OFFSET P32 1 2 3 检验 6 例2 利用名称作为基点 得到右图中红色字体区域公式 OFFSET DATA 2 1 3 2 公式结果 Q 34 R 36检验 6 由于公式中指定了最终结果的行数 3 和列数 2 那么系统只能将DATA中的左上角单元格 P32 作为引用基点 并不是整个DATA区域 公式等同于 OFFSET P32 2 1 3 2 公式结果 Q 34 R 36检验 6 By Gdliyy E 序序号号姓姓名名工工资资 1A100 2B200 3C300 4D400 5E500 以指定的引用为参照系 通过给定偏移量得到新的引用 返回的引用可以为一个单元格或单元格区域 并可以指定返回的行数或列数 2 行偏移 rows 列偏移 cols为必输项 如省略必须用 逗号进行占位 缺省值为0 即不偏移 行数 height和列数 width为可选项 可省略 缺省视为与基点相同 通常情况下 如果基点以单元格区域作为引用范围 如果不指定行数或列数 系统默认是引用区域的行数和列数来确定结果 由于引用基点为A5 C5 3列区域 同时公式1中未指定行数或列数 所以最终结果仍然为3列区域 公式1与公式2结果相同 两者等价 但有时 基点为区域引用时 用户同时又指定了行数或 和 列数 那么用户必须清楚了解实际引用的基点区域 才能得出正确结果 由于公式中指定了最终结果的行数 3 和列数 2 那么系统只能将DATA中的左上角单元格 P32 作为引用基点 并不是整个DATA区域 基基点点员员工工编编号号员员工工姓姓名名性性别别年年龄龄 A0711张三男10 A0795李四男20 A0733王五女30 B1234赵六男40 A2345张大男50 A0123刘二女60 偏移B1123田七男50 公式 offset B1 7 2 3 3 检验 9 等价于 OFFSET B1 7 3 1 2 3 3 解释 1 通常情况下 用户都是输入正数来控制产生引用的行数和列数 偏移方向为向下取行数或向右取得列数 2 值得提醒的是 EXCEL支持用户在行数 height和列数 width参数中输入负数