如何制作动态尺寸的数据表？ [英] How do you make a dynamically-sized data table?

问题描述

我正在使用Excel 2010。

I am using Excel 2010.

我有一个类似于以下内容的每月数据表：

I have a "monthly" data table that looks similar to this:

MonthBegin    InventoryExpenses  Overhead  TotalSales  TotalSalesIncome  TotalProfit
July-11       $1,500             $4,952    89          $7,139            $687
August-11     $2,200             $4,236    105         $8,312            $1,876
September-11  $1,100             $4,429    74          $6,691            $1,162

以下公式自动传播到[MonthBegin]列中的每个单元格： / p>

The following formula is automatically propogated to every cell in the [MonthBegin] column:

=DATE( 2011, 7 + ( ROW( ) - 2 ), 1 )

其他每个colmun都有类似的列公式，该列公式会根据[MonthBegin]中列出的月份自动从另一个来源中提取适当的数据， ]列。

Every other colmun has a similar column-formula that automatically pulls the appropriate data from another source, based on the month listed in the [MonthBegin] column.

使用此配置，我可以在表格中的任意位置插入新行，下个月将自动

With this configuration, I can just insert a new row anywhere into the table and the next month will automatically appear at the bottom in the correct order (which I find nifty).

但是我需要将其带入下一个自动化级别，以取悦管理。

如何做到这一点，以便当月结束时电子表格会自动在10月添加一行？

But I need to take this to the next level of automation, to please management.
How can I make it so that the spreadsheet automatically adds a row for October once the month is over?

我一直在考虑使用动态范围对于该表：

I've been considering using a dynamic range for the table:

=OFFSET(A1,0,0,( ( YEAR( TODAY( ) ) - 2011 ) * 12 ) + ( MONTH( TODAY( ) ) - 7 ),6)

...但是赢得了Excel我不接受这样的表区域公式，因为它不是静态的。

谁能向我解释如何通过我的数据表获得此功能？

... but Excel won't accept such a formula for the table area, I assume because it is not static.
Can anyone explain to me how to gain this functionality with my data table?

推荐答案

您不能动态仅添加带有公式的新行。

You can't dynamically add a new row with formula only.

这是将完成此操作的VBA事件过程。您需要放入工作簿模块

Here is a VBA event procedure that will do the trick. You need to put in the Workbook module

Option Explicit

Private Sub Workbook_Open()
Dim lo As ListObject
Dim iTot As Long
Set lo = ListObjects("MyTable")
iTot = lo.Range.Rows.Count

'Add this statements before the Range with your worksheet name
'ThisWorkbook.Worksheets("Sheet1")
If Now() > Range("A" & iTot).Value Then
    Range("A" & lo.Range.Rows.Count + 1).Formula = "=DATE( 2011, 7 + ( ROW( ) - 2 ), 1 )"
End If
End Sub

别忘了更改表的名称并添加工作表的名称（请参见代码内的注释）

Don't forget to change the name of your table and to add the name of your Worksheet (see the comment inside the code)

这篇关于如何制作动态尺寸的数据表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！