☰

将 Excel 电子表格转换为公式

tag-icon • tag-icon microsoft-excel spreadsheet worksheet-function
将 Excel 电子表格转换为公式

我有一个 Excel 电子表格，其中有一个复杂的计算，很难转换成宏或单单元格公式。电子表格有大约 10 个不同的输入（人们在电子表格的不同单元格中输入的值），然后根据该输入输出 5 个独立的计算（在 5 个不同的单元格中）。他们的计算使用电子表格中的一些预先输入的数据（大约 100 个不同的常量）并对它们进行一些查找。

我想将整个电子表格用作另一个电子表格上的公式，以计算一组输入值并生成相应的一组输出值。想象一下，创建不同的表格，其中 10 列为输入变量，5 列为输出，然后将每个输入复制到另一个电子表格中，并将输出复制回结果表中。

例如：

  • A1、A2、A3、...A10 是用于输入值的单元格
  • 通过一系列计算，B1、B2、B3、B4 和 B5 用一些公式进行更新

我是否可以将 A1...A10 的整个系列计算应用到 B1...B5 中，而无需创建一个庞大的公式或 VBA 宏？

我想要从 A100、B100、C100、…J100 开始的 100 行中输入一组输入值，然后执行一些 Excel 魔法：

  1. 将 A100…J100 中的值复制到 A1 到 A10
  2. 等待结果出现在B1至B5中
  3. 将 B1 至 B5 的值复制到 K100 至 O100
  4. 对 100 至 150 的所有行重复步骤 1 至 3

答案1

您可以使用 Excel 的假设分析来做到这一点。我对它不太熟悉，无法说。您能用一个相对简单的宏来完成它。

基本上，宏的工作方式如下：

Open the formula workbook
Set calculation to manual
Loop thru the rows you want to apply the formula to
   Take inputs on current row of result sheet and enter them in the formula workbook
   Force Excel to recalculate
   Take results of formula workbook and enter them in the result sheet
End loop
Turn calculation back on to automatic.

有两种方法可以将结果工作簿中行中的数据传输到公式工作簿中的列中。一种方法是使用 Transpose 进行复制和粘贴，将行转换为列，另一种方法是使用偏移，即在一个范围内偏移列，在另一个范围内偏移行。

后者的示例如下。

    ' set ranges to top left cell
    Set rngFrom = Workbooks("Result.xls").Worksheets("Sheet1").Cells(iCurrentRow, 1)
    Set rngTo = Workbooks("Formula.xls").Worksheets("Sheet1").Range("A1")

    ' transfer values
    For i = 0 To 9
        rngTo.Offset(RowOffset:=i).Value = rngFrom.Offset(ColumnOffset:=i).Value
    Next i

如果您需要有关编写 VBA 宏的具体帮助，请告诉我们。

答案2

我仍然认为你的问题太模糊了。没有明确的例子很难回答。如果你是我，我会尝试找一位 Excel 专家来为你做这项工作。拥有丰富专业知识的人可能会将整个事情变成宏。

不过，我会根据我认为我理解的内容尝试给出答案。

总的来说，我认为你只需要让工作簿 2 中的公式访问工作簿 1 中的值，反之亦然。链接工作簿似乎是最明显的解决方案。微软网站上有一些课程向你展示了如何做到这一点（http://office.microsoft.com/en-us/excel-help/create-and-manage-links-to-other-workbooks-HA001054812.aspx，http://office.microsoft.com/en-us/excel-help/create-a-link-to-another-cell-workbook-or-program-HP005199514.aspx）。

如果您认为我的回答太偏离，请发布一个示例、屏幕截图或视频来展示您想要完成的任务。

答案3

我同意，这个问题有点模糊，而且整个过程有 4 个步骤。哪个步骤最困难？你能举一个小一点的例子，只关注你无法管理的部分吗？

1.将 A100...J100 中的值复制到 A1 至 A10

范围大小不一样，你说的复制是什么意思？如果你想要 A1 中的某个值，那么只需在单元格 A1 中输入 =A100。事实上，你不需要“复制”，只需“引用”。

2.等待结果出现在B1至B5中

您实际上不需要等待，但我想它正在做一些复杂的后台工作。我想您已经弄清楚了这部分。

3.将 B1 到 B5 的值复制到 K100 到 O100 中。同样，如果您想要 K100 中的值，则将 =B1 放入单元格 K100，它就会“复制”它（参考它）

4.对从 100 到 150 的所有行重复步骤 1 到 3。设置好第 100 行后，每个单元格中都会有一个公式。现在，当您选择单元格时，右下角会出现一个小的黑色“手柄”。单击它并将其向下拖动。公式将自动更改为以绝对方式或相对方式引用单元格。例如，如果单元格 A1 中有一些数据，并且您希望所有单元格 A100 到 A150 都“看到”它，那么您可以在单元格 A100 中输入：=$A$1，然后单元格 A101 也会查看 A1，依此类推。因此 $ 符号使其成为绝对引用。

答案4

我知道现在已经很晚了，我希望你的努力能够成功。

我创建了一些这样的电子表格。有些电子表格涉及多达 14 个查找表和几十个中间计算和插值；所有这些都是为了生成一个数字表。这是基于几个用户输入，从实数到下拉列表，并经过大量数据验证。我使用了各种查找、index()/match() 查询。我没有使用任何脚本语言，因为我并不特别在意与启用宏的电子表格相关的持续警告。我为中间计算和查找创建了许多工作表，所有工作表都受密码保护，大多数工作表都是隐藏的。有一个数据输入表，其中包含未受保护的单元格和一个结果表。

最初我希望将它交给某人，然后引导用户完成必要的步骤。结果发现这太复杂了，需要特定主题的特定知识。

最后，我相信我投入了超过 120 个小时，而且我只将其用作自己的工具。为了使其发挥作用，我又添加了一张带有空白“模板”的工作表，该模板的格式是复制并粘贴到 MS Word 文档中。多年来，这种方法一直运行良好。当查找表中的某些数据需要修改时，我只需进行这些更改，数字就会立即反映出这些更改。

祝好，道格

相关内容