Excel查找和引用函数-OFFSET函数使用实例教程.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$72:E$65