像 C 列中的“负责人选项表”这一项目的选项,在实际工作中调整这个选项的数量的情况其实非常普遍。如输入商品名称等操作,输入选项会因为商品的改动或下架有所调整。遇到这样的情况,如果“负责人选项表”所指定的范围是 C2:C4这种固定范围的话,如果之后要在单元格 C5中追加新的负责人名字,那么就无法出现在单元格 A2的序列输入选项列中。

输入新的负责人,无法显示在序列中

Excel在序列输入时经常会增减选项-Excel22

如此一来,想要把 C5也放入指定范围中,我们需要再次设置【来源】指定的范围。如果不需要经常增减选项的话,这样的操作也不会花太多时间,但若是需要频繁修改【来源】的范围,那么就麻烦了。如果序列输入可以自动对应【来源】内容做出调整,即使需要频繁修改也不会觉得麻烦。

为此,请在“负责人选项表”名称的引用位置里输入如下公式:

=OFFSET(负责人!$C$1,1,0,COUNTA(负责人!$C:$C)-1,1)

引用位置中输入=OFFSET(负责人!$C$1,1,0,COUNTA(负责人!$C:$C)-1,1)

Excel在序列输入时经常会增减选项-Excel22

这里使用的是 OFFSET 函数。这个函数非常重要,请务必掌握。这一函数的要点有两个:

  • 确定作为基准的单元格,将其理解为引用的单元格从这一位置“偏离”
  • 以基准单元格偏离后为起点来指定单元格的范围

OFFSET 函数的公式:

【公式】

=OFFSET(基准单元格,偏离行数,偏离列数)

OFFSET 函数的语法为:“第一参数指定的单元格(基准单元格)开始,第二参数指定向上或向下偏移几行,第三参数指定从第二参数偏离后的位置向右或向左偏移几行”。第二参数为正数则向下移动,为负数则向上移动。第三参数为正数则向右移动,为负数则是向左移动。

下面来看一下使用案例吧。下图中的工作表是 A1:D3为范围以性别和课程来分类的费用表。

A1:D3为范围以性别和课程来分类的费用表

Excel在序列输入时经常会增减选项-Excel22

男性为1、女性为2,并用括号括起来。每项各自以单元格 A1为基准,男性的费用在单元格 A1的下一行,女性的费用在单元格 A1的下两行。

关于课程,初级为1、中级为2、高级为3。也是以单元格 A1为基准,初级在 A1向右一列,中级在 A1向右第二列,高级在 A1向右第三列。

这时,在单元格 B5输入代表性别的数值,在单元格 B6输入代表课程的数值,单元格 B7中就会显示相应的费用金额。想要建立这种结构,需要在单元格 B7输入以下函数公式:

=OFFSET(A1,B5,B6)

在单元格 B7中输入=OFFSET(A1,B5,B6)

Excel在序列输入时经常会增减选项-Excel22

这个公式可以导出以单元格 A1为基准,A1~B5指定的数字向下、B6指定的数字向右偏移的单元格的值。

如图所示,第二参数单元格 B5为1,第三参数指定的单元格 B6为2。如此一来,A1向下偏移一格、再向右偏移两格……即指向 C2的值。这利用的是 OFFSET 函数的基础逻辑:第一参数指定的单元格为基准,第二参数指定的数字向下,再从这一位置以第三参数指定的数字向右移动所指向的单元格。

第二参数指定的数字若为负数,则第一参数为基准向上移动,第三参数指定的数字若为负数,则第一参数为基准向左移动。

顺带一提,运用这一函数也可以解决“在 VLOOKUP 函数中,是否能获取位于检索列左侧的数值吗”这一问题(参考 P116)。

如何指定范围

另外,通过 OFFSET 函数,还能以从基准单元格按第二参数数值向下、第三参数数值向右偏移的位置为起点,再次指定范围。但是此时需再追加2个参数。

=OFFSET(基准单元格,偏移行数,偏移列数,高度,宽度)

在下表中,B 列为每天的销售额。在单元格 D1中输入想要知道从1号开始到第 N 天的累计销售额的天数,单元格 G1就会自动显示销售额数据。

Excel在序列输入时经常会增减选项-Excel22

此表中,D1的数值为2,销售额 G1中则显示1日~2日两天的累计销售额。

在单元格 G1中,需要输入以下函数公式:

=SUM(OFFSET(B1,1,0,D1,1))

一般要计算数值的和,都会用到 SUM 函数,SUM 函数可计算出括号内指定的单元格范围内的和。SUM 函数括号内的 OFFSET 函数就在指定的单元格范围。

首先,我们只看 OFFSET 函数部分,确认它所指定的范围。这是以单元格 B1为基准,向下移动1格、向右移动0格,也就是不向右移动。于是,偏移的目标单元格为 B2。

再以 B2为起点,指向第四参数指定的行数(此表中单元格 D1的值为2,即2行)和第五参数指定的1列的范围(具体为 B2:B3)。

这里需要掌握的重要信息为:OFFSET 函数的第四参数指定的范围的行数若发生变化,OFFSET 函数指定的范围也会有所变化。

OFFSET 函数所指定的范围,可利用“根据单元格 D1的值,纵向扩展”这一点灵活应对。

  • D1值为3➛B2:B4
  • D1值为5➛B2:B6

应用这个方法,即便是序列输入模式,可以应对【来源】范围中数据有所增加的情况,选项也会自动增加。

那么接下来,我们再来看一下刚刚以“负责人选项表”为引用范围输入的公式。

=OFFSET(负责人!$C$1,1,0,COUNTA(负责人!$C:$C)-1,1)

我们来分析一下这个公式。首先,以“负责人”工作表中单元格 C1为基准,向下移动1格、向右移动0格的目标单元格,即单元格 C2为起点的范围。

想要指定这个范围的行数,需要使用 COUNTA 函数。通过 COUNTA 函数,将整个 C 列的含数据的单元格的行数减去1。由于 C 列中含数据的单元格中含有第一行“负责人选项表”这一项目,因此需要减去这一行。

然后,用第五参数指定范围的宽度为1。

第四参数的 COUNTA 函数一般所取的是整个 C 列中含数据的行数减1后得到的数字,所以当 C 列中追加负责人后,“负责人选项表”的范围也应自动进行相应的扩大。

如此一来,单元格指定范围的“负责人选项表”下的数值,与单元格 A2的菜单中的下拉选项的数值必须要一致。

“负责人选项表”与单元格 A2的菜单中的下拉选项的数值一致

Excel在序列输入时经常会增减选项-Excel22