从多张表中更改变量？ [英] Change variable from multiple sheets?

问题描述

我有一本excel工作簿，具有多张表格，可以将不同技术组件的成本和收入（设置在不同的表格中）集成在一个系统中。

I have an excel workbook with multiple sheets that aggregate costs and revenues of different technological components (set up in different sheets) in a system.

我想要一个主工作表，用户可以从技术表中更改一小部分重要变量。我也希望这些重要的变量可以在技术表中进行定义和编辑。

I want to have a main worksheet, where users can change a small selection of important variables from the technology sheets. I also want those important variables to be defined and editable on the technology sheets.

我一直在使用命名范围来管理变量，但是我不知道如何将不同页面上的两个单元格链接到一个变量。例如，我想命名一个变量oilprice，在不同的公式中引用。我想要能够从我的工作簿中的主要工作表和电力技术表中更改变量oilprice。

I've been using named ranges to manage variables, but I'm not sure how to link two cells on different sheets to one variable. For example, I want to name a variable "oilprice" that is referenced in different formulas. I want to be able to change the variable "oilprice" from the main worksheet and the electricity technology sheet in my workbook.

同样，我想要检查一下两张表上的打开电力框，并且另一张表上的复选框也会更改。

Similarly, I want to be able to check a box on both sheets for "Turn on Electricity" and have the checkbox on the other sheet change as well.

我一直在看google和stackoverflow，但可以'找到答案。谢谢！

I've been looking around on google and stackoverflow but can't find an answer. Thanks!

推荐答案

命名范围，选项1：覆盖样式公式

使用命名范围，您无法更新多个单元格中的值。您可以使用公式中的逻辑来查看覆盖单元格，并将该值传递给实际的命名范围。如果覆盖次数较小，则会起作用。公式的风格如下：

Named range, option 1: "override" style formula

With named ranges, you are not able to update the value from multiple cells. You could use logic in a formula to look at a "override" cell and pass that value to the actual named range. This works if the number of overrides is small. That style of formula looks like:

=IF(ISBLANK(oilprice2), oilprice1, oilprice2)

其中 oilprice1 和 oilprice2 是保存可能值的单元格。请注意，这些隐含的命令可能会在一段时间后混淆。也就是说，如果 oilprice2 有一个值，它不会改变 oilprice1 也不会 oilprice1 被考虑。

Where oilprice1 and oilprice2 are the cells that hold possible values. Note that there is an implied order in these which can get confusing after a while. That is, if oilprice2 has a value, it will not change oilprice1 nor will oilprice1 be considered.

另一个类似于复选框的选项下面是使用旋转控制或滚动条控件更新值。

Another option similar to the checkboxes below, is to use a spin control or scrollbar control to update the values. Those work across multiple sheets.

对于复选框，这是由code>单元格链接。您可以设置任意多个复选框，以控制单个单元格的值。

For the checkboxes, this is handled by the Cell Link. You can set as many checkboxes as you want to control a single cell's value.

这是一个示例，两个复选框共享相同的 Cell Link = $ C $ 2 。他们都改变了一个被点击。

Here is an example with two checkboxes sharing the same Cell Link = $C$2. They both change when one is clicked.

这篇关于从多张表中更改变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！