在 Excel 中,公式或公式的一部分都可以用它得出来的值来替换。当然啦,被替换的部分就不再是公式了,不会再跟着引用值的改变而改变了。
Excel 的帮助文件里讲了两种方法,但是最好用的那招就是F9,会这招就可以通吃了。鉴于上篇日志提交后成果泡汤的前车之鉴,这篇的内容继续保持英文。
Replace a formula with its calculated value
Caution When you replace a formula with its value, Microsoft Excel permanently removes the formula. If you accidentally replace a formula with a value and want to restore the formula, click Undo immediately after you enter or paste the value.
- Select the cell that contains the formula.
If the formula is an array formula (A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), select the range that contains the array formula.
How?
- Click a cell in the array formula.
- On the Edit menu, click Go To.
- Click Special.
- Click Current array.
- Click Copy .
- Click Paste on the Standard toolbar (A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.)
- Click the arrow next to Paste Options , and then click Values Only.
Replace part of a formula with its calculated value
Caution When you replace part of a formula with its value, it cannot be restored.
- Click the cell that contains the formula.
- In the formula bar (A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , select the portion of the formula you want to replace with its calculated value. When you select the part of the formula you want to replace, make sure to include the entire operand (Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.) For example, if you select a function, you must select the entire function name, the opening parenthesis, the arguments (The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.), and the closing parenthesis.
- To calculate the selected portion, press F9.
- To replace the selected portion of the formula with its calculated value, press ENTER.
If the formula is an array formula, press CTRL+SHIFT+ENTER.