[MS Excel]自动计算 [英] [MS Excel] automate a calculation
问题描述
你好，
我设法在Excel中创建了一个Makro，它完成了它应该做的事情（是的！）：做一个目标寻求。
接下来我想要的是它不仅在一行（当前指定的单元格）中搜索，而是在相关列的所有行中搜索（我想从计算值创建图形） ，即确实涉及Range（）。但是我不知道在哪里可以看到如何在射程上进行目标搜索。你能帮我找到吗？
当前版本的代码：
Hi there,
I somehow managed to create a Makro in Excel and it does what it is supposed to do (yeah!): do a goal seek.
what I want next is that it does this goal seek not only in one row (the currently specified cells) but in all rows of the concerned column (I want to create a graph from the calculated values), i.e. a Range() is certainly involved. but I have no idea where to look how to do a goal seek on a range. can you help me find that out?
current version of the code:
推荐答案
Dormilich：
我理解正确：
说你有专栏A
从第1行开始
你做一个目标寻求A1的价值
然后你想要完成并使用A2中的值进行目标寻找
A3 ...运行GS
A4 ...再次运行
等....
如果是这样，那么我们需要做的就是使用偏移方法移动活动单元当前到下一个，或者至少从单元格中获取该值，并使用新值让GS为您运行。
但是，如果您想要使用范围A1 ....在同一时间GS中的＃，这是该功能无法实现的。
（ 使用目标寻求通过调整输入值找到你想要的结果（...）
- 目标搜索仅适用于一个变量输入值。如果要接受多个输入值;例如，贷款金额和贷款的每月付款金额，您使用Solver加载项。有关Solver加载项的更多信息，请参阅另请参阅部分中的链接。
Dormilich:
Do I understand correctly:
Say you have column A
Starting in row 1
You do a goal seek on the value in A1
then you want to move done and use the value in A2 for the goal seek
A3... run GS
A4... run again
etc....
If so then all we need to do is either use the offset method to move the active cell from the current to the next, or at least get that value from the cell, and get GS to run for you using the new value.
If however, you want to use the values in the range A1....A# in the GS at the SAME time, that is not possible with that function.
( Use Goal Seek to find the result you want by adjusting an input value (...)
- Goal Seek works only with one variable input value. If you want to accept more than one input value; for example, both the loan amount and the monthly payment amount for a loan, you use the Solver add-in. For more information about the Solver add-in, follow the links in the See Also section.
从第1行开始
你的目标是寻找A1的价值
然后你想要完成并使用A2中的值来寻找目标
A3 ...运行GS
A4 ...再次运行
等....
Starting in row 1
You do a goal seek on the value in A1
then you want to move done and use the value in A2 for the goal seek
A3... run GS
A4... run again
etc....
， 这就是我想要的。虽然GoalSeek和ChangingCell的单元格都向前移动。
exactly, that’s what I want. though both the cells for GoalSeek and ChangingCell move forward.
但是，如果你想使用A1 ......范围内的值。 GS在同一时间，这是该功能无法实现的。
If however, you want to use the values in the range A1....A# in the GS at the SAME time, that is not possible with that function.
nope，这不适用于标准目标搜索。 （我不打算这样做）。
nope, that would not work with a standard goal seek. (and I do not intend to do that).
如果是这样，那么我们需要做的就是使用offset方法移动活动单元格从当前到下一个，或者至少从单元格中获取该值，并使用新值让GS为您运行。
If so then all we need to do is either use the offset method to move the active cell from the current to the next, or at least get that value from the cell, and get GS to run for you using the new value.
问题在于更改单元格*和*目标单元格需要向前移动。
所以我需要寻找一个方法来返回该范围的当前单元格的数量（或其他），并确定GS的目标单元格。
也许某事沿...？
the problem with that is that the changing cell *and* the target cells need to move forward.
so I need to look for a method that returns the number (or whatever) of the current cell of the range and determine the target cells for the GS.
maybe something along ...?
也是[D4]改变了吗？
您可以使用ActiveCell.Offset（Row，Col）（
并将行，col索引递增以指向您的单元格。
唯一的问题是你需要先选择正确的单元格，然后你需要测试空/空值。
所以说你在A栏中有目标值
B栏中的公式
你需要正确的值专栏c
首先点击A1，第一轮的公式为：
ActiveCell.Offset（0,1）.GoalSeek目标：= ActiveCell.Offset （0,0），ChangingCell：= ActiveCell.Offset（0,2）
现在只需偏移你的行索引，检查值。
is [D4] changing too?
You can useActiveCell.Offset(Row,Col)(
and increment the row,col indexs to point to your cells.
The only cavets is that you need to select the correct cell first and you''ll need to test for empty/null values.
So say you have the target value in Column A
The formula in Column B
and you need the correct value in Column c
Click on A1 first and your formula for the first round would be:
ActiveCell.Offset(0, 1).GoalSeek goal:=ActiveCell.Offset(0, 0), ChangingCell:=ActiveCell.Offset(0, 2)
Now just offset your row index, check for values.
这篇关于[MS Excel]自动计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
