实战之路《Excel 数据处理与分析实战宝典_第2版》_8.5 OFFSET 函数

【摘要】 本书摘自《Excel 数据处理与分析实战宝典_第2版》一书中第8章，第5节，耿勇著。

8.5 OFFSET 函数

8.5.1 OFFSET 函数的基本用法

OFFSET函数的语法如下：

OFFSET(reference,rows,cols,height,width)

上述 OFFSET 函数语法的通俗理解：

OFFSET (起点，移动的行数，移动的列数，所要引用的高度，所要引用的宽度)

★ reference 作为偏移量参照系的引用区域。reference 必须为对单元格或相连单元格区域的 引用；否则，函数 OFFSET 返回错误值#VALUE!。

★ rows 相对于偏移量参照系的左上角单元格，上(下)偏移的行数。行数可为正数(代表 在起始引用的下方)或负数(代表在起始引用的上方)。

★ cols相对于偏移量参照系的左上角单元格，左(右)偏移的列数。列数可为正数(代表在 起始引用的右边)或负数(代表在起始引用的左边)。

★ height高度，即所要返回的引用区域的行数。height 必须为正数。

★ width 宽度，即所要返回的引用区域的列数。width 必须为正数。

说明 如果行数和列数的偏移量超出了工作表边缘，函数OFFSET就返回错误值 #REF!。

如果省略 height 或 width,则假设其高度或宽度与 reference 相同。

函数OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。

8.5.3 储值卡余额的计算及查询

很多服务性企业(如连锁餐饮店、网吧)实行储值卡充值消费的模式,那么这种 模式下如何计算卡内余额，又如何实现储值卡余额实时查询呢?

公式解析：对卡号进行出现次数判断。如果是首次出现，则该客户的储值卡余额为充值金额 减去消费金额；如果不是首次出现，则将前一次F 列的余额累加到本次充值金额减去消费金额中。

其中，LARGE((SB$1:$B2=B2)*ROW(SB$1:SB2),2)会定位所计算单元格的余额对应的“卡号” 在 B 列单元格中的位置，然后减去1就确定了该“卡号”在本次出现之前一次的位置。这样就确 定了该卡号对应的前一次储值卡的余额。

公式解析：通过查找相同姓名、与截止日期最近的日期来确定该姓名和最近日期在第2~15 行区域之间所对应的最大行号，据此可以确定 OFFSET 函数在 F1 单元格向下偏移的行数，这样 就确定了该客户最近交易后储值卡的余额。

小结 通过对上述实例的学习，也可以参照此模式设计小企业客户或者供应商应收应付的余额计算、 查询表格等。

8.5.4 OFFSET 与动态数据验证

OFFSET 常常与名称管理器结合生成动态引用区域，而名称与数据验证的完美结合就可以实 现动态的下拉菜单。

定义名称如下。

部门：=OFFSET(Sheet1!$AS2,,COUNTA(Sheetl!SA$2:$AS10))

明 细 组 ： =OFFSET(Sheetl1!$A$1,MATCH(Sheet1!SKS1,部门,0),1,COUNTA (OFFSET (Sheet1!$B$1:$H$1,MATCH(Sheet1!SK$1,部门，0),))

在K2 单元格中设置数据验证，在“来源”处输入“=部门”;在K3 单元格中设置数据验证， 在“来源”处输入“=明细组”。这样就实现了一个二级下拉菜单的功能。

公式解析：

★这是一个典型的 OFFSET 动态引用的实例。公式中主要通过对一级部门名称的 MATCH 定位，再根据 COUNTA 来求得实际二级部门数，最后通过 OFFSET 得到结果。

★其中最主要的公式就是部门明细名称的公式，“MATCH(Sheetl!$K$1, 部门，0)”是该公式最 核心的部分，通过这部分得到行偏移，通过“OFFSET(Sheetl!SBS1:SHS1,MATCH

(Sheetl!SK$1,部门，0),)”部分得到引用列数。

如“财务部”的二级部门列表，先通过MATCH 查找其行号位置，通过内嵌 OFFSET 的动态 引用取得E2:G2 区域，再通过COUNTA 来得到具体明细组数，最后通过“=OFFSET($A$1,1,1,,6)”

来得出最终二级部门区域引用(SE$2:SGS2), 提供给K2 的下拉菜单选项序列供选择。

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