如何在Excel中创建动态表(使用TABLE和OFFSET函数)

excel中的动态表是在其中插入新值后,该表会自行调整其大小的表,要在excel中创建动态表,我们有两种不同的方法,一次是从表格部分,而另一部分则使用偏移功能,因此在动态表中,报表和数据透视表也会随着动态表中数据的更改而更改。

Excel中的动态表

它本身是动态的,它意味着具有持续变化或活动变化特征的处理器系统。类似地,在Excel中,当我们在工作簿中创建列表或数据并从中生成报告时,但是如果我们添加任何数据或删除一个数据或移动或更改数据,则整个报告可能会不准确。 Excel为它提供了一个动态表解决方案。

现在出现了一个问题,为什么我们需要动态范围或动态表。答案是因为每当更新或修改列表或数据范围时,都不能确定报告是否会根据数据更改进行更改。

基本上,动态表有两个主要优点:

  1. 动态范围将根据数据更改自动扩展或收缩。
  2. 刷新数据透视表时,可以自动更新基于excel中动态表的数据透视表。

如何在Excel中创建动态表?

在excel中,有两种使用动态表的基本方法-1)使用TABLES和2)使用OFFSET函数。

您可以在此处下载此动态表格Excel模板–动态表格Excel模板

#1 –使用表格在Excel中创建动态表格

使用表,我们可以在excel中建立动态表,并以动态表为基础。

例子

我们有以下数据,

如果我们使用A1:E6的正常数据范围创建数据透视表,则如果在第7行中插入数据,该数据将不会反映在数据透视表中。

因此,我们将首先创建一个动态范围。

#1 – 选择数据,即A1:E6。

#2 – 在插入中,用Tab键单击表格部分下的表格。

#3 – 弹出对话框。

由于我们的数据具有标题,因此请记住选中“我的表具有标题”框,然后单击“确定”。

#4 – 我们的动态范围已创建。

#5 – 选择数据,然后在excel表部分下的“插入”选项卡中,单击数据透视表。

#6 – 创建表后,它的取值范围为表2。单击“确定”,然后在数据透视表中将“行中的产品”和“值中的销售额”拖动。

#7 – 现在在Sheet中,我们的表在第7个位置插入另一个数据

在数据透视表中刷新数据透视表。

我们的动态数据透视表已自动更新了数据透视表中产品6的数据。

#2 –使用OFFSET函数在Excel中创建动态表

我们还可以使用OFFSET函数在excel中创建动态表。让我们看一个这样的例子。

例子

我有我的产品的价目表,我可以将其用于计算,

选择数据并命名

现在,每当我参考数据集价目表时,它将带我到具有我的价目表的B2:C7范围内的数据。但是,如果我更新数据的另一行,由于我们的列表是静态的,它仍将我带到B2:C7的范围。

我们将使用偏移功能使数据范围成为动态范围。

#1 – 在“定义的范围内的公式选项卡”下,单击“定义的名称”,然后会弹出一个对话框。

#2 – 在“名称框”中键入任何名称,我将使用PriceA。范围是当前工作簿,当前是指当前选定的单元格B2。

在引用中写以下公式,

=偏移量(Sheet2!$ B $ 2,1,0,counta(Sheet2!$ B:$ B)-1,2)

=偏移量(

#3 – 现在选择起始单元格为B2,

#4 – 现在我们需要输入1,0,因为它将计算要移动的行数或列数

#5 – 现在我们需要它来计算B列中的任何数据,并将其用作行数,因此请使用COUNTA函数并选择B列。

#6 – 由于我们不希望将作为产品标题的第一行计为(-)1。

#7 – 现在,列数将始终为2,因此键入2,然后单击“确定”。

#8 – 默认情况下,该数据范围是不可见的,因此要查看此数据,我们需要点击“公式”标签下的“名称管理器”,然后选择“产品”,

#9 – 如果我们点击指的是它显示的数据范围,

#10 – 现在,在表6中添加另一个产品。

#11 – 现在,在名称管理器中单击“产品表”,它还引用插入的新数据,

这就是我们可以使用“偏移”功能制作动态表格的方式。

要记住的事情

  1. 刷新后,基于动态范围的数据透视表将自动更新。
  2. 在“定义的名称”中使用偏移功能可以从“公式”选项卡的“名称管理器”中看到。