使用 Microsoft Excel 时，用户通常不会事先知道表格中将包含多少信息。 因此，我们并不了解在所有情况下都应涵盖哪些范围。 毕竟，一组单元格是一个变量概念。 为了摆脱这个问题，有必要使范围生成自动化，以便它完全基于用户输入的数据量。
在 Excel 中自动更改单元格范围
Excel 中自动范围的优点是它们使使用公式变得更加容易。 此外，它们还可以显着简化包含大量公式的复杂数据的分析，其中包含许多函数。 你可以给这个范围一个名字，然后它会根据它包含的数据自动更新。
如何在Excel中进行自动范围更改
假设您是需要投资某个对象的投资者。 因此，我们希望了解在这笔资金用于该项目的整个时间内，您总共可以获得多少收入。 然而，为了获得这些信息，我们需要定期监控这个对象给我们带来了多少总利润。 制作与此屏幕截图相同的报告。
乍一看，解决方案很明显：您只需对整列求和。 如果条目中出现，金额将独立更新。 但是这种方法有很多缺点：
- 如果问题以这种方式解决，将无法将 B 列中包含的单元格用于其他目的。
- 这样的表会消耗大量的 RAM，这将导致无法在弱计算机上使用文档。
因此，有必要通过动态名称来解决这个问题。 要创建它们，您必须执行以下操作序列：
- 转到位于主菜单中的“公式”选项卡。 将有一个“定义的名称”部分，其中有一个“分配名称”按钮，我们需要单击该按钮。
- 接下来，将出现一个对话框，您需要在其中填写屏幕截图所示的字段。 需要注意的是，我们需要应用函数 = 位移 与函数一起 查看创建自动更新范围。
- 之后我们需要使用函数 SUM，它使用我们的动态范围作为参数。
完成这些步骤后，我们可以看到当我们在其中添加新元素时，属于“收入”范围的单元格的覆盖范围是如何更新的。
Excel中的OFFSET函数
让我们看看我们之前在“范围”字段中记录的函数。 使用功能 处置 给定 B 列中填充了多少个单元格，我们可以确定范围的数量。 函数参数如下：
- 起始单元格。 使用此参数，用户可以指示范围中的哪个单元格将被视为左上角。 它将向下和向右报告。
- 按行偏移的范围。 使用此范围，我们设置从范围的左上角单元格开始偏移的单元格数。 您不仅可以使用正值，还可以使用零和负值。 在这种情况下，位移可能根本不发生，或者会以相反的方向进行。
- 按列的范围偏移。 该参数与上一个参数类似，只是它允许您设置范围的水平移动程度。 在这里，您还可以同时使用零值和负值。
- 高度范围的数量。 事实上，这个论点的标题让我们明白了它的含义。 这是范围应增加的单元格数。
- 宽度范围的值。 该论点与前一个类似，只是它涉及列。
如果不需要，则不需要指定最后两个参数。 在这种情况下，范围值将只有一个单元格。 例如，如果您指定公式 =偏移量（A1；0；0），此公式将引用与第一个参数中的单元格相同的单元格。 如果垂直偏移设置为 2 个单位，那么在这种情况下，单元格将引用单元格 A3。 现在让我们详细描述一下函数的含义 查看.
Excel 中的 COUNT 函数
使用功能 查看 我们确定总共填充了 B 列中的单元格。 也就是说，使用两个函数，我们确定范围内有多少个单元格被填充，并根据接收到的信息，确定范围的大小。 因此，最终公式如下： =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
下面我们来看看如何正确理解这个公式的原理。 第一个参数指向我们的动态范围从哪里开始。 在我们的例子中，这是单元格 B2。 其他参数具有零坐标。 这表明我们不需要相对于左上角单元格的偏移量。 我们所填写的只是范围的垂直大小，我们使用函数作为 查看，它决定了包含一些数据的单元格的数量。 我们填写的第四个参数是单位。 因此，我们表明范围的总宽度应该是一列。
因此，使用函数 查看 用户可以通过仅加载包含某些值的那些单元格来尽可能有效地使用内存。 因此，在工作中不会出现与电子表格将在其上运行的计算机性能不佳相关的额外错误。
因此，为了根据列数确定范围的大小，您需要执行类似的操作序列，只有在这种情况下，您需要在第三个参数中指定单位，在第四个参数中指定公式 查看.
我们看到，借助 Excel 公式，您不仅可以自动进行数学计算。 这只是沧海一粟，但实际上它们允许您自动化几乎所有想到的操作。
Excel 中的动态图表
因此，在最后一步中，我们能够创建一个动态范围，其大小完全取决于它包含多少填充单元格。 现在，基于此数据，您可以创建动态图表，一旦用户进行任何更改或添加额外的列或行，该图表就会自动更改。 这种情况下的动作顺序如下：
- 我们选择我们的范围，然后我们插入一个“带分组的直方图”类型的图表。 您可以在“图表 - 直方图”部分的“插入”部分找到此项目。
- 我们在直方图的随机列上单击鼠标左键，然后函数 =SERIES() 将显示在函数行中。 在屏幕截图中，您可以看到详细的公式。
- 之后，需要对公式进行一些更改。 您需要替换“Sheet1！”之后的范围到范围的名称。 这将导致以下功能： =ROW(Sheet1!$B$1;;Sheet1!收入;1)
- 现在仍然需要向报告中添加一条新记录，以检查图表是否自动更新。
让我们看一下我们的图表。
让我们总结一下我们是如何做到的。 在上一步中，我们创建了一个动态范围，其大小取决于它包含多少元素。 为此，我们使用了功能组合 查看 и 处置. 我们为这个范围命名，然后我们使用对这个名称的引用作为我们直方图的范围。 在第一阶段选择哪个特定范围作为数据源并不那么重要。 主要是稍后将其替换为范围的名称。 这样可以节省大量内存。
命名范围及其用途
现在让我们更详细地讨论如何正确创建命名范围并使用它们来执行为 Excel 用户设置的任务。
默认情况下，我们使用常规单元格地址以节省时间。 当您需要一次或多次写入范围时，这很有用。 如果需要一直使用或需要自适应，则应使用命名范围。 它们使创建公式变得更加容易，并且用户分析包含大量函数的复杂公式也不会那么困难。 让我们描述创建动态范围所涉及的一些步骤。
这一切都从命名单元开始。 为此，只需选择它，然后在其名称字段中写入我们需要的名称。 重要的是要容易记住。 命名时需要考虑一些限制：
- 最大长度为 255 个字符。 这足以指定一个你内心想要的名字。
- 名称不得包含空格。 因此，如果它包含多个单词，则可以使用下划线字符分隔它们。
如果稍后在此文件的其他工作表上我们需要显示此值或应用它来执行进一步的计算，则无需切换到第一个工作表。 您只需写下此范围单元格的名称即可。
下一步是创建一个命名范围。 程序基本相同。 首先，您需要选择一个范围，然后指定其名称。 之后，此名称可用于 Excel 中的所有其他数据操作。 例如，命名范围通常用于定义值的总和。
此外，可以使用“设置名称”工具使用“公式”选项卡创建命名范围。 在我们选择它之后，将出现一个窗口，我们需要在其中为我们的范围选择一个名称，并指定它将手动扩展到的区域。 您还可以指定此范围的操作位置：在单张纸内或整本书中。
如果已经创建了一个名称范围，那么为了使用它，有一个称为名称管理器的特殊服务。 它不仅允许编辑或添加新名称，还可以在不再需要时删除它们。
应该记住，当在公式中使用命名范围时，删除它后，公式不会自动被正确的值覆盖。 因此，可能会出现错误。 因此，在删除命名范围之前，您需要确保它没有在任何公式中使用。
创建命名范围的另一种方法是从表中获取它。 为此，有一个名为“从选择中创建”的特殊工具。 据我们了解，要使用它，您必须首先选择我们将编辑的范围，然后设置我们拥有标题的位置。 因此，基于这些数据，Excel 将自动处理所有数据，并自动分配标题。
如果标题包含多个单词，Excel 会自动用下划线分隔它们。
因此，我们弄清楚了如何创建动态命名范围以及它们如何让您自动化处理大量数据。 如您所见，使用几个功能和内置在功能中的程序工具就足够了。 根本没有什么复杂的，尽管乍一看对于初学者来说似乎很复杂。