excel表格公式不更新_一步更新多个Excel公式

excel表格公式不更新

You've probably used the Excel Paste Special command to multiply cells by a specific percentage, or to add the same amount to a group of cells. Today, you'll see how to update multiple Excel formula cells in one step.

您可能已经使用Excel Paste Special命令将单元格乘以特定百分比，或将相同的数量添加到一组单元格中。 今天，您将看到如何一步一步地更新多个Excel公式单元格。

This screen shot shows a cell that has been copied, and is ready for the Paste Special command.

此屏幕快照显示了已复制的单元格，可用于“选择性粘贴”命令。

For example, in the screenshot below, you could copy cell D2, and use the Paste Special, Add, command to add that amount to cells B2:B5.

例如，在下面的屏幕截图中，您可以复制单元格D2，然后使用“选择性粘贴，添加”命令将该金额添加到单元格B2：B5中。

After using the Paste Special command, each value in B2:B5 is increased by $1.50, as expected.

使用选择性粘贴命令后，B2：B5中的每个值将按预期增加$ 1.50。

配方细胞专用糊 (Paste Special For Formula Cells)

Excel Paste Special works well with values, and you can also use it to paste and modify formulas. In the worksheet shown below, the formulas in row 6 calculate an adjusted rate.

Excel Paste Special可以很好地与值配合使用，您也可以使用它来粘贴和修改公式。 在下面显示的工作表中，第6行中的公式计算调整后的比率。

A new factor -- Discount -- has been added to the workbook, and it needs to be included in the adjusted rates.

工作簿中已添加了一个新因素-折扣-并将其包含在调整后的费率中。

Each formula in row 6 is slightly different, so you can't just change one formula and copy it across. Fortunately, you can use the Paste Special command to modify all the formulas at once.

第6行中的每个公式都略有不同，因此您不能只更改一个公式并复制整个公式。 幸运的是，您可以使用“选择性粘贴”命令来一次修改所有公式。

However, pasting formulas is a little trickier than pasting values. For example, if you copy cell B2, and use it to multiply in the Paste Special command, the results might not be what you need.

但是，粘贴公式比粘贴值要复杂一些。 例如，如果您复制单元格B2，并使用它在“选择性粘贴”命令中相乘，则结果可能不是您所需要的。

Instead of pasting a reference to cell B2, the Paste Special command pastes the formula that's in cell B2 (green highlighting below).

“选择性粘贴”命令没有粘贴对单元格B2的引用，而是粘贴了单元格B2中的公式(下面的绿色突出显示)。

Because the pasted formula has a relative reference, the results in row 6 aren't correct.

由于粘贴的公式具有相对参考，因此第6行中的结果不正确。

To solve the problem, you can create a temporary formula, in cell D2, with an absolute reference to cell B2, and use that for the Paste Special.

若要解决此问题，您可以在单元格D2中创建一个临时公式，该单元格具有对单元格B2的绝对引用，并将其用于选择性粘贴。

Then, copy cell D2, and use Paste Special, Multiply, on cells B6:E6. Now the modified formulas are multiplied by cell B2 (green highlighting), and the results in row 6 are correct.

然后，复制单元格D2，并在单元格B6：E6上使用“选择性粘贴，相乘”。 现在，将修改后的公式乘以单元格B2(绿色突出显示)，并且第6行的结果正确。

After using the Paste Special command, you can clear the contents of cell D2, where you entered the temporary formula.

使用选择性粘贴命令后，您可以清除在其中输入临时公式的单元格D2的内容。

观看视频 (Watch the Video)

To see the Excel Paste Special Multiply steps, watch this short video tutorial.

要查看Excel选择性粘贴特殊步骤，请观看此简短视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2010/06/11/update-multiple-excel-formula-in-one-step/

excel表格公式不更新