您是否有 Excel 中可以调整大小的数据表，即在工作过程中行（列）的数量可以增加或减少？ 如果表格大小“浮动”，那么您将必须不断监控这一时刻并纠正它：
- 引用我们表格的报告公式中的链接
- 根据我们的表格构建的数据透视表的初始范围
- 根据我们的表格构建的图表的初始范围
- 使用我们的表格作为数据源的下拉列表的范围
所有这些总不会让你感到无聊😉
创建一个动态的“橡胶”范围会更加方便和正确，它会根据数据的实际行数和列数自动调整大小。 要实现这一点，有几种方法。
方法一、智能表
突出显示您的单元格范围并从选项卡中选择 主页 - 格式为表格（主页 - 格式为表格）:
如果您不需要添加到表格中的条纹设计作为副作用，那么您可以在出现的选项卡上将其关闭 构造函数（设计）. 以这种方式创建的每个表都会收到一个名称，该名称可以在选项卡的同一位置替换为更方便的名称 构造函数（设计） 在该领域 表名 （表名）.
现在我们可以使用动态链接到我们的“智能表”：
- 表1 – 链接到除标题行之外的整个表格 (A2:D5)
- 表 1[#全部] – 链接到整个表格 (A1:D5)
- 表 1[彼得] – 引用没有第一个单元格标题的范围列 (C2:C5)
- 表 1[#Headers] – 链接到带有列名的“标题”（A1：D1）
此类引用在公式中非常有用，例如：
= SUM（表1[莫斯科]) – 计算“莫斯科”列的总和
or
=VPR(F5;表1;3;0) – 从单元格 F5 在表格中搜索月份并为其发出圣彼得堡总和（什么是 VLOOKUP？）
通过在选项卡上选择创建数据透视表时，可以成功使用此类链接 插入 - 数据透视表（插入 - 数据透视表） 并输入智能表的名称作为数据源：
如果您选择此类表格的一个片段（例如，前两列）并创建任何类型的图表，那么在添加新线时，它们将自动添加到图表中。
创建下拉列表时，不能使用指向智能表格元素的直接链接，但您可以使用战术技巧轻松绕过此限制 - 使用函数 间接 （间接），将文本转换为链接：
那些。 以文本字符串（用引号括起来！）形式的智能表的链接会变成完整的链接，并且下拉列表通常会感知它。
方法二：动态命名范围
如果出于某种原因不希望将数据转换为智能表，那么您可以使用稍微复杂但更微妙和通用的方法——在 Excel 中创建一个引用我们表的动态命名范围。 然后，和智能表一样，您可以在任何公式、报表、图表等中自由使用创建的范围的名称。让我们从一个简单的例子开始：
任务：创建一个动态命名范围，该范围将引用城市列表，并在添加或删除新城市时自动拉伸和缩小大小。
我们将需要任何版本的两个内置 Excel 函数 - 波伊波兹 （匹配） 确定范围的最后一个单元格，以及 指数 （指数） 创建动态链接。
使用 MATCH 查找最后一个单元格
匹配（查找值，范围，匹配类型） – 在范围（行或列）中搜索给定值并返回找到它的单元格的序号的函数。 例如，公式 MATCH(“March”;A1:A5;0) 将返回数字 4 作为结果，因为单词“March”位于 A1:A5 列的第四个单元格中。 最后一个函数参数 Match_Type = 0 表示我们正在寻找完全匹配。 如果未指定此参数，则函数将切换到最接近的最小值的搜索模式——这正是可以成功用于查找数组中最后一个被占用的单元格的方法。
诀窍的本质很简单。 MATCH 在从上到下的范围内搜索单元格，理论上应该在找到最接近给定值的最小值时停止。 如果您指定一个明显大于表中任何可用值的值作为所需值，则 MATCH 将到达表的最末端，什么也找不到，并给出最后一个填充单元格的序列号。 我们需要它！
如果我们的数组中只有数字，那么我们可以指定一个数字作为所需的值，它显然大于表中的任何一个：
为了保证，您可以使用数字 9E + 307（9 乘以 10 的 307 次方，即 9 和 307 个零）——原则上 Excel 可以使用的最大数字。
如果我们的列中有文本值，那么作为最大可能数的等价物，可以插入构造REPEAT(“i”, 255)——由255个字母组成的文本字符串“i”——最后一个字母字母表。 由于 Excel 在搜索时实际上会比较字符代码，因此从技术上讲，我们表格中的任何文本都将“小于”这么长的“yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy”行：
使用 INDEX 生成链接
现在我们知道了表格中最后一个非空元素的位置，它仍然可以形成一个指向我们整个范围的链接。 为此，我们使用以下功能：
索引（范围；行数；列数）
它通过行号和列号给出单元格的内容，例如，我们的表中的函数 =INDEX(A1:D5;3;4) 与前一种方法的城市和月份将给出 1240 - 内容从第 3 行第 4 列开始，即单元格 D3。 如果只有一列，则可以省略其编号，即公式 INDEX(A2:A6;3) 将在最后一个屏幕截图中给出“Samara”。
还有一个不完全明显的细微差别：如果 INDEX 不只是像往常一样在 = 符号之后输入到单元格中，而是用作对冒号之后范围的引用的最后部分，那么它不再给出单元格的内容，但它的地址！ 因此，像 $A$2:INDEX($A$2:$A$100;3) 这样的公式将在输出中引用范围 A2:A4。
这就是 MATCH 函数的用武之地，我们将其插入 INDEX 以动态确定列表的结尾：
=$A$2:索引($A$2:$A$100; 匹配（代表（“我”；255）;A2:A100))
创建命名范围
仍然需要将它们全部打包成一个整体。 打开标签 公式 （公式） 然后点击 名称管理员 （名称管理器）. 在打开的窗口中，单击按钮 创建 （新），在字段中输入我们的范围名称和公式 范围 （参考）:
它仍然可以点击 OK 并且准备好的范围可以在任何公式、下拉列表或图表中使用。
- 使用 VLOOKUP 函数链接表和查找值
- 如何创建自动填充的下拉列表
- 如何创建数据透视表来分析大量数据