如何制作动态大小的数据表? [英] 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] 列中的每个单元格:
The following formula is automatically propogated to every cell in the [MonthBegin] column:
=DATE( 2011, 7 + ( ROW( ) - 2 ), 1 )
每个其他列都有一个类似的列公式,可以根据 [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 事件过程.你需要放入Workbook模块
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屋!