☰

自动保持两个 Excel 数据表同步（不使用 VBA）

tag-icon • tag-icon microsoft-excel
自动保持两个 Excel 数据表同步（不使用 VBA）

我正在整理一本用于跟踪股票投资组合的工作簿。主工作表包含一个包含交易列表的表格。在此基础上，我想在另一张工作表上创建一个概览表，每个唯一股票代码只有一行，其中包括成本基础、回报等内容。问题是，当将行添加到交易表时，我尝试过的任何方法都无法正确更新概览表。我得到的最接近的结果是以下内容：

http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from-a-column/

但是，这需要将该公式应用于概览表主列中的每个单元格。即使这样，表格的范围也不会向下扩展以包含有效的新行。本质上，我正在寻找一种方法，可以在不使用 VBA 的情况下自动将行添加到表格中并根据不同表格的变化复制前一行的公式。

举一个具体的例子也许能更好地解释我的目标。假设刚刚添加 GOOG，其他交易已经存在。

这是我最接近获得自动更新行为的一次。

Sheet1
Symbol     Type    Shares    Price
F          Buy     100       12
MSFT       Buy     100       25  
MSFT       Buy     50        28
F          Buy     100       16
GOOG       Buy     25        550

Sheet2
Symbol                              Quantity
F                                   200     
MSFT                                150
------------------------------------------------------------ // Table Boundary
GOOG                                25
{=UNIQUE_LIST(Sheet1[Symbol], A5)}  =SUMIF(Sheet1[Symbol], A5, Sheet1[Shares])
{=UNIQUE_LIST(Sheet1[Symbol], A6)}  =SUMIF(Sheet1[Symbol], A6, Sheet1[Shares])
... // Formulas start at row 2 and extend down tons of extra rows
    // Table boundary didn't extend to include GOOG

理想情况下，我希望 Sheet2 看起来更像这样，仅在需要时才将公式复制下来

Sheet2
Symbol                              Quantity
F                                   200     
MSFT                                150
GOOG                                25
------------------------------------------------------------ // Table Boundary
// Table extended and no extra formulas

添加行

答案1

请记住，通常有很多方法可以用 Excel 实现相同的结果。也许您可以使用数据库函数。在您给出的示例中，如果您将数据库引用为 Sheet1!A:D 或定义引用该引用的名称，那么您可以添加任意多的行。不幸的是，由于您必须指定条件的方式，数据库函数很丑陋且难以使用。

另一个选择是使用数组公式。例如：{=sum(if(sheet1!a2:a999="MSFT",sheet1!b2:b999,0))}

答案2

你写：

但是，这需要将该公式应用于概览表主列中的每个单元格。即使这样，表格的范围也不会向下扩展以包含有效的新行。

如果您无法逃避这两个要求，至少您没有在第一张表中设置列表的最大长度。

答案3

尼尔，

如果您使用的是 XL 2003 或更高版本，则应尝试将源数据放入列表 (XL 2003) 或表格 (相同，在 XL 2007、2010 中重命名)。然后使用列表/表格作为不同工作表上数据透视表的源。据我了解，这满足了您对数据和报告的要求。数据透视表源将随列表/表格扩展和收缩。

唯一需要注意的是，当源数据发生变化时，数据透视表不会自动刷新。您必须点击“刷新”按钮。如果您能够使用 VBA，那么您可以编写事件代码，每次列表/表格中的单元格发生变化时触发数据透视表刷新。

答案4

我经常使用数据透视表。设置数组时，手动输入结束单元格。

例如，我有从范围 A11:C2500 开始的数据。

如果我预计数据将增长到第 10000 行，我将手动将数组更改为读取 A11:C15000，以确保有增长空间。

如果您这样做，您将看到数据透视表随着新项目添加到交易列表而增长。

相关内容