Excel查找和引用函数-OFFSET函数使用实例教程(1).xls

OFFSET 函数功能 以指定的引用为参照系 通过给定偏移量得到新的引用 返回的引 用可以为一个单元格或单元格区域 并可以指定返回的行数或列数 表 达 式 OFFSET reference rows cols height width 参数含义 Reference 作为偏移量参照系的引用区域 Reference 必须为对单元 格或相连单元格区域的引用 否则 函数OFFSET 返回错误值 VALUE Rows 相对于偏移量参照系的左上角单元格 上 下 偏移的行数 如 果使用 5 作为参数 Rows 则说明目标引用区域的左上角单元格比 reference 低 5 行 行数可为正数 代表在起始引用的下方 或负数 代表在起始引用的上方 Cols 相对于偏移量参照系的左上角单元格 左 右 偏移的列数 如 果使用 5 作为参数 Cols 则说明目标引用区域的左上角的单元格比 reference 靠右 5 列 列数可为正数 代表在起始引用的右边 或负数 代表在起始引用的左边 Height 高度 即所要返回的引用区域的行数 Height 必须为正数 Width 宽度 即所要返回的引用区域的列数 Width 必须为正数 说 明 如果行数和列数偏移量超出工作表边缘 函数OFFSET 返回错误值 REF 如果省略height或width 则假设其高度或宽度与 reference 相同 函数 OFFSET实际上并不移动任何单元格或更改选定区域 它只是返回 一个引用 函数 OFFSET 可用于任何需要将引用作为参数的函数 例如 公式 SUM OFFSET C2 1 2 3 1 将计算比单元格 C2 靠下 1 行并靠 右 2 列的 3 行 1 列的区域的总值 ADDRESS ADDRESS 参参数数引引用用 数据1数据2数据3数据4数据5数据6 159131721 2610141822 3711151923 4812162024 公式说明 结果 15 显示单元格 F12 中的值 15 90 对数据区域 D10 F12 偏移得到的新区 域 求和 90 OFFSET C10 D13 0 3 3 3 返回错误值 REF 因为引用区域不在工 作表中 列被偏移A列之外 示示例例一一按序号排名 序号品名型号单价数量总金额排名 1MP3 LQ00120010002000003I23公式 RANK H23 OFFSET H 22 MATCH C23 C 23 C 31 0 COUNTIF C 23 C 31 C23 1MP3 LQ0023009002700002 1MP3 LQ0034008003200001 2MP4 LQ0045007003500002 2MP4 LQ0056006003600001 2MP4 LQ0067005003500002 3MP5 LQ0078004003200001 3MP5 LQ0089003002700002 3MP5 LQ00910002002000003 示示例例二二用函数生成工资条 C36 I42是职工工资数据 财务人员希望通过原始数据打印出方便裁剪的工资条 姓名基本工资津贴职务工资奖金福利费合计 张15005005005005002500 张26006006006006003000 张37007007007007003500 张48008008008008004000 张59009009009009004500 张6100010001000100010005000 姓名基本工资津贴职务工资奖金福利费合计 张15005005005005002500 姓名基本工资津贴职务工资奖金福利费合计 张26006006006006003000 姓名基本工资津贴职务工资奖金福利费合计 张37007007007007003500 姓名基本工资津贴职务工资奖金福利费合计 张48008008008008004000 姓名基本工资津贴职务工资奖金福利费合计 张59009009009009004500 姓名基本工资津贴职务工资奖金福利费合计 张6100010001000100010005000 C46公式 CHOOSE MOD ROW 3 1 C 36 OFFSET C 36 ROW 3 14 示示例例三三动态求值 下表是学生成绩表 求各科目的平均分 最高分 最低分 人数 班级姓名语文数学英语总分学科语文 1张1135111146392平均分122 44 1张2104122120346最高分148 00 1张3127116144387最低分92 00 2张4109133126368人数9 2张5131104107342 2张614891120359K72公式 AVERAGE OFFSET E 72 COUNTA E 72 E 65535 3张792129118339K73公式 MAX OFFSET E 72 COUNTA E 72 E 65535 3张8137127141405K74公式 MIN OFFSET E 72 COUNTA E 72 E 65535 3张911991128338K75公式 COUNT OFFSET E 72 COUNTA E 72 E 65535 视频教程 视频教程 RANK H23 OFFSET H 22 MATCH C23 C 23 C 31 0 COUNTIF C 23 C 31 C23 数学英语 113 78127 78 133 00146 00 91 00107 00 99 AVERAGE OFFSET E 72 COUNTA E