用OFFSET函数定义一个动态区域

用OFFSET函数定义一个动态区域

我们可以给一个单元格或区域定义一个名称，以便在公式中引用。如果区域不是固定的而是一个动态的范围，我们也可以给它定义名称，以后在公式中引用的就是一个动态区域。例如我们可以在A列中定义一个动态区域，是从A1单元格开始的动态连续区域，其包含的行数不固定，操作步骤如下：

1.单击菜单“插入→名称→定义”，打开“定义名称”对话框。

2.在“在当前工作簿中的名称”下的文本框中输入要定义的名称，如“数据A”，在“引用位置”下的文本框中输入

“=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)”，单击“确定”。

公式说明：用OFFSET()函数定义一个动态区域，其参数分别是

Sheet1!$A$1：为作为参照系的引用单元格，是Sheet1表中的A1单元格；

第一个0：偏移的行数；

第二个0：偏移的列数；

COUNTA(Sheet1!$A:$A)：区域高度，即区域中包含的行数，用COUNTA()函数计算A列中非空单元格个数，由这个公式可以看出，如果A列中有多个数据且不连续，将会返回错误结果；

最后一个参数1：区域宽度，即区域中包含的列数；

动态数据展示的实现

在工作表Sheet1中的单元格A1、A2、A3中分别输入“月份”、“销售额”、“销售汇总”，及相应的月份和销售额数据，请按以下步骤完成余下操作。

编辑推荐阅读

● Excel函数应用之数学和三角函数

● Excel函数应用之函数简介

1.单击主选单“插入/名称/定义”命令，弹出“定义名称”对话框，在“在当前工作簿中的名称”文本框中输入“Month”，在“引用位置”文本框中输入公式：

“=offset($A$2,0,0,count($A:$A),1)”，单击“添加”按钮；重复上述步骤，在“在当前工作簿中的名称”文本框中输入“Sales”，在“引用位置”文本框中输入公式：

“=offset($B$2,0,0,count($B:$B),1)”，单击“确定”按钮。

2.在C2单元格输入公式“=SUM(Sales)”，本文充分利用了“名称”的作用。

3.鼠标单击A2，再单击工具栏中的“图表向导”按钮，在“图表向导—4步骤之1—图表类型”对话框中，选择“XY散点图”的第二个图表子类型，单击“下一步”按钮。

4.在“图表向导—4步骤之2—图表源数据”对话框中，单击“系列”标签，修改“X值(X)：”文本框里的内容为“=Sheet1!Month”，修改“Y值(Y)：”文本框里的内容为

“=Sheet1!Sales”。单击“完成”按钮。

5.单击图表，清除图表的“网格线”、“绘图区背景格式”，至此完成。

现在，不管你怎样修改区域A3、B3以下两列的数据，添加/删除，销售汇总和图表都将随着你输入的数据集的变化而动态变化（注：不能删除A2、B2单元格中的数据）。

几点说明

对步骤1中所使用的函数，主要有两个：OFFSET函数和COUNT函数，就是这两个函数的配合实现了动态数据的展示。

COUNT函数的参数是一个单元格区域引用。此时，它只统计引用中的数字，引用的空单元格将被忽略。利用函数 COUNT 可以计算单元格区域引用中数字项的个数，作为OFFSET函数的相对偏移量参数使用。

OFFSET函数实现动态区域的扩展。此函数的功能是以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。

OFFSET函数的语法是：OFFSET（reference，rows，cols，height，width），这里参数“Reference”代表作为偏移量参照系的引用区域，Reference 必须是对单元格或相连单元格区域的引用。否则，函数 OFFSET 返回错误值 #VALUE!。参数“Rows”表示相对于偏移量

参照系的左上角单元格上（下）偏移的行数。行数为正数时表示向起始引用的下方扩展区域，为负数时表示向起始引用的上方扩展区域。参数“Cols”表示相对于偏移量参照系的左上角单元格左（右）偏移的列数。列数为正数时表示向起始引用的右边扩展，为负数时表示向起始引用的左边扩展区域。如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值#REF!。参数“Height”表示所要返回的引用区域的行数，Height 必须为正数。参数“Width”表示所要返回的引用区域的列数，Width 必须为正数。如果省略 height 或width，则假设其高度或宽度与 reference 相同。

注意OFFSET函数实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。在我们的操作中，是将OFFSET函数返回的引用定义名称为Month或Sales，并利用此名称所代表的单元格区域，实现数据汇总和作图。这样，在数据集变化后，相应的汇总和图表都变化了。

＝＝＝＝＝＝＝结束＝＝＝＝＝＝＝＝