Calc:单变量求解
出自OOo 中文指南手册翻译
目录 |
工具>单变量求解颠倒了一个公式通常的顺序。通常，当输入确定的参数后，执行一个公式获得结果。单变量求解与之相反，对于一个完整的公式其中某一参数需要输入什么值才能获得想要的结果。 Tools > Goal Seek reverses the usual order for a formula. Usually, you run a formula to get the result when certain arguments are entered. By contrast, with Goal Seek, you work with a completed formula to see what values you need in an argument to get the results that you want.
举个简单的例子，设想一个公司的首席财务官正在做下一年每一季度的销售计划。她知道公司全年收入必须达到多少才能使股东满意。她也有一个关于头三个季度公司收入的好打算，因为那些合同已经签订了。然而对于第四季度，收入是不确定的。那么公司要在第四季度赚多少才能达成目标呢？要回答这一问题，首席财务官输入头三季度里每一季度的计划收入和全年的计划收入。然后她对代表第四季度销售的单元格执行单变量求解，就获得了答案。 To take a simple example, imagine that the Chief Financial Officer of a company is developing sales projections for each quarter of the forthcoming year. She knows what the company’s total income must be for the year to satisfy stockholders. She also has a good idea of the company’s income in the first three quarters, because of the contracts that are already signed. For the fourth quarter, however, no definite income is available. So how much must the company earn in Q4 to reach its goal? To answer, the CFO enters the projected earnings for each of the other three quarters and the projection for the entire year. Then she runs a goal seek on the cell for Q4 sales, and receives her answer.
其他单变量求解的用法就更复杂了，但方法是一样的。要执行单变量求解，至少一个参数的值之一必须引用自一个单元格或范围。在一次单变量求解中只能改变一个参数。在获取一次单变量求解的结果后，可以用结果替换被引用单元格里的值，或者记录下结果留待以后使用，可能作为方案之一。 Other uses of goal seek may be more complicated, but the method remains the same. To run a goal seek, at least one of the values for an argument must be a referenced cell or range. Only one argument can be altered in a single goal seek. After you get the result of a goal seek, you can replace the original value in the referenced cell with the result, or record the result elsewhere for later use, possibly as a scenario.
在单变量求解的帮助下，可以计算出导致公式得到指定结果的参数值。因此定义的公式要有几个定值和一个变量及公式的结果。 With the help of Goal Seek you can calculate a value that, as part of a formula, leads to the result you specify for the formula. You thus define the formula with several fixed values and one variable value and the result of the formula.
[编辑] 单变量求解例子 Goal Seek example
要计算年息(I)，创建一个表包含本金(C)，年数(n)，利率(i)。公式是I = C*n*i。 To calculate annual interest (I), create a table with the values for the capital (C), number of years (n), and interest rate (i). The formula is I = C*n*i.
我们假设利率i是7.5%，年数n (1)将保持常数。然而你想知道要获得一个特别回报I需要投资多少本金C。本例中，计算年回报$15,000需要多少本金C。 Let us assume that the interest rate i of 7.5% and the number of years n (1) will remain constant. However, you want to know how much the investment capital C would have to be modified in order to attain a particular return I. For this example, calculate how much capital C would be required if you want an annual return of $15,000.
在几个单元格里分别输入本金C（任意值如$10,000），年数n (1)，及利率i(7.5%)。在另一个单元格里输入计算利息I的公式。这里不要使用C，n和i，而是用相应单元格的引用值。本例中，分别是B1，B2和B3。 Enter each of the values for Capital C (an arbitrary value like $100,000), number of years n (1), and interest rate i (7.5%) in one cell each. Enter the formula to calculate the interest I in another cell. Instead of C, n, and i use the reference to the cell with the corresponding value. In our example, these are B1, B2, and B3.
- 置光标于公式单元格（该单元格包含利息I），然后选择工具>单变量求解。 Place the cursor in the formula cell (the cell containing the interest I), and choose Tools > Goal Seek.
- 在单变量求解对话框里，目标单元格字段已经输入正确的单元格。 On the Goal Seek dialog, the correct cell is already entered in the Formula cell field.
- 放置光标在可变单元格字段。在表格中，点包含要改变值的单元格，本例中是本金C的单元格。 Place the cursor in the Variable cell field. In the sheet, click in the cell that contains the value to be changed, in this example it is the cell with the capital value C.
- 在目标值字段里输入公式的预期结果。本例中是15000。下面的图显示了单元格和字段。 Enter the desired result of the formula in the Target value field. In this example, the value is 15000. The figure below shows the cells and fields.
- 点确定。出现一个对话框通知单变量求解已成功。点是在变量单元格里输入结果。结果显示如下。 Click OK. A dialog appears informing you that the Goal Seek was successful. Click Yes to enter the result in the cell with the variable value. The result is shown below.
[编辑] 进度表
| Calc:单变量求解 中文化进度 | 翻译 | 0 | 校对 | 0 | 插图 | 2/2 | 上次编辑 | 20100614131116 | 编者 | Yookee |
