Excel为什么在序列输入时经常会增减选项

Excel函数 2023-01-08

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

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

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

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

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

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

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

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

OFFSET 函数的公式：

【公式】

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

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

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

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

男性为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)

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

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

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

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

如何指定范围

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

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

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

此表中，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为什么在序列输入时经常会增减选项的下载地址：
本地下载

上一篇：excel VB 利用GDI+保存图片为JPG、TIFF、PNG、GIF、BMP等格式下一篇：Excel单元格内的换行，以及换行后的数据

Excel为什么在序列输入时经常会增减选项

Excel为什么在序列输入时经常会增减选项的下载地址：
本地下载

热门文章

最新文章

标签列表

Excel为什么在序列输入时经常会增减选项

Excel为什么在序列输入时经常会增减选项的下载地址： 本地下载

热门文章

最新文章

标签列表

Excel为什么在序列输入时经常会增减选项的下载地址：
本地下载