☰

如何在 Excel 中创建包含外部数据和 Excel 公式的表格？

tag-icon • tag-icon microsoft-excel
如何在 Excel 中创建包含外部数据和 Excel 公式的表格？

我在 Excel 中有一个查询，它从 Oracle 数据库中提取大约五列数据和数千行。我想在同一个表中的这些数据右侧添加其他列，这些列具有各种公式来处理数据库中的列。但这会导致两件事：

  1. 数据库的表刷新需要几分钟而不是几秒钟。
  2. 附加列充满了#REF 错误。

我认为 #2 的情况是刷新会删除包含数据的单元格，然后使用刷新的数据创建新单元格。我似乎记得 Excel 中某个地方有一个选项，说明在这种情况下该怎么做，但我不记得它在哪里了。

至于 #1，不知道。我正在使用 Excel 365 和与 Oracle 的 OLEDB 连接。

当然，这都是我忽略的一些基本知识...谢谢你的帮助。

答案1

问题：数据连接刷新后公式未填充

不确定 OLEDB 是否也一样，但如果您从 CSV/文本文件中提取数据，则连接属性中有一些选项，您可以告诉 Excel 填写与 csv 数据相邻的公式，以及如果新数据的数据行比以前多或少，excel 应该如何处理。理论上，如果设置正确，公式也应该刷新。请参阅我在此处针对类似问题的回答：

https://superuser.com/a/1633714/1241807

另请参阅注释。如果出于某种原因，OLEDB 连接无法使用这些选项，您可以选择将工作表“克隆”到带有引用的新工作表，如注释中所述。问题是……这些引用可能还会破坏 #REF 样式。

为了避免这种情况，您可以在公式中使用间接文本引用，即：

=IF(A1 = 1, B1, C1)

使用：

=IF(indirect("A1") = 1, indirect("B1"), indirect("C1"))

但是，对于大型电子表格来说，这是一个非常糟糕的想法，因为 indirect() 是一个易变函数 - 这意味着它会随着工作簿（甚至其他打开的工作簿）中的每个小变化而重新计算。易变性也可能是你的工作簿已经如此缓慢的原因。你是否在工作簿中使用了 OFFSET、RAND、NOW、TODAY、INDIRECT 或条件格式（仅举几例）？这些都是易变的，可能会造成巨大的速度障碍。也许也可以在这里查看我关于提高工作簿速度的回答：

https://superuser.com/a/1643467/1241807

替代解决方案 1：手动保存公式以防止 #REF 错误

刷新之前，点击公式功能区 > 显示公式或 CTRL+` 切换到公式视图。选择所有公式并复制（CTRL+C）并粘贴到记事本或 Word（CTRL+V）中。清除 Excel 中的公式单元格。现在刷新数据连接。之后，继续将公式从记事本/Word 复制并粘贴回 Excel。

替代解决方案 2：Excel 中的非易失性间接或偏移量

在某些情况下，您可以使用非易失性指数函数和/或相对命名范围而不是易失性间接或偏移函数。要使用绝对引用，例如 $B$3

=INDEX($A$1:$Z$1000, 3 , 2)      ...make the range only as big as necessary as this will also impact speed

对于相对引用，比如从 D3 引用 B3

=INDEX($A$1:$Z$1000, ROW() , COLUMN()-2)

在某些用例中，$A$1:$Z$1000 在数据刷新或特定数据插入或删除时仍可能导致 #REF 错误。对于这些，您可以选择相对命名范围。如果您将光标放在 E2 中，然后使用指的是作为=A2， （不是 2 澳元)，您可以在任何单元格中使用 FourLeft，它将始终返回左侧四列的单元格值。这样，上述公式可以这样写（定义另外两个名称）：

=IF(FourLeft = 1, ThreeLeft, TwoLeft)

使用这两种方法时要小心，因为行为与正常引用不同，尤其是在复制和粘贴、插入或删除数据时。

相关内容