☰

从其他表更新表

tag-icon • tag-icon microsoft-excel
从其他表更新表

我有一张表格，其中包含有关我工作场所当前项目的信息（主数据表）。我试图在单独的选项卡中创建一个表格，该表格与主数据表保持同步，但同时允许我对数据进行排序。这个想法是，管理人员可以根据数据创建多个报告，而不必每次都重新输入他们的标准（例如，在一个选项卡中按风险因素排序的 HVAC 项目，在另一个选项卡中按风险因素排序的电气项目，等等）。当新数据添加到主数据表时，这些报告应该自动更新。这是供管理层（不是我）使用的，所以我试图让它尽可能简单。我试过使用数组公式，但这些公式不允许排序。我试过自定义视图，但它们不保留排序顺序。我试过数据透视表，但它们似乎更适合数字摘要。我非常沮丧和困惑。不幸的是，我之前变得如此沮丧，以至于我删除了制作自动更新表格的尝试 :(。幸运的是，我仍然有主数据表！

有人有什么建议吗？如果更多信息有帮助，请告诉我！我正在使用 excel 365。

托马斯·肯宁斯

答案1

创建链接克隆表：

按照以下步骤创建参考克隆表，其中包括将镜像主表上更改的公式。您还可以创建 VBA 宏来快速生成链接克隆表，但手动操作非常简单：

  1. 创建主工作表的副本：右键单击工作表名称，单击“移动或复制”，勾选“创建副本”，然后单击“确定”。（这样，我们就可以得到原始格式，但会用链接到源数据的公式替换以下步骤中的所有值。）
  2. 使用公式编辑新工作表中的单元格 A1=IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)
  3. 现在复制单元格 A1 (CTRL+C)，选择全部 (CTRL+A) 或最好是较小的相关区域，粘贴 (CTRL+V)，按 CTRL 打开粘贴弹出菜单，选择仅公式 (按 F)

注意事项：

  • 您可能必须将克隆的工作表公式扩展到最后一行和/或最后一列之外，以便新添加的数据也能反映出来（不幸的是，这不包括应用于新数据的任何格式）
  • 主表上的格式更改不会自动更新（但是您可以使用格式刷将更改重新应用到克隆）
  • 如果您在克隆的工作表中使用过滤器，则需要在主工作表中进行更改后重新应用过滤器（ALT+CTRL+L）。

您可以使用 VBA 中的 Worksheet_change 事件自动重新应用过滤器，但这需要将电子表格保存为启用宏的文件，并带有相关的权限弹出窗口和警告 - 根据我的管理经验 - 这些将被取消/忽略/拒绝。最好在顶行添加一个巨大的红色横幅“按 CTRL-ALT-L 刷新报告”。

灵活的克隆公式来处理插入的行或列：

=IF(ISBLANK(INDEX(Sheet1!$A:$Z, ROW(), COLUMN())),"",INDEX(Sheet1!$A:$Z, ROW(), COLUMN()))

或者更正确地说（为了解释插入新的 A 列，并防止上述引用更改为 Sheet1!$B:$AA）

=IF(ISBLANK(INDEX(INDIRECT("Sheet1!$A:$Z"), ROW(), COLUMN())),"",INDEX(INDIRECT("Sheet1!$A:$Z"), ROW(), COLUMN()))

尽管后者易变且因此占用大量 CPU。

在Excel 365你可以做与单个动态数组公式相同在单元格 A1 中：

=LET(usedrange, Sheet1!$A$1:INDEX(Sheet1!$A:$Z,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)), IF(ISBLANK(usedrange), "", usedrange))

相关内容