如何防止 Excel 自动复制表格中的公式? [英] How do I prevent Excel from automatically replicating formulas in tables?

问题描述

我使用的是 Excel 2016.我有一个带有标题的表格，当我插入公式时，Excel 会自动将公式复制到列中的所有其他单元格.虽然这通常没问题，但它错误地计算了表头.我以为我可以更改顶行以排除标题，但 Excel 更新了我不想要的列的其余部分.

I'm using Excel 2016. I have a table with headers and when I plug in a formula, Excel is automatically replicating the formula to all other cells in the column. While that would normally be fine, it's wrongly calculating the table headers. I thought I could just change the top row to exclude the header but Excel updates the rest of the column which I don't want.

我想关闭此自动公式复制功能，或者想办法自定义顶行中的公式，使其不计算标题值.

I would like to either turn this automatic formula replication feature off or figure out a way to customize the formula in the top row so it doesn't calculate the header value.

这是我正在使用的公式，除了添加总计"行之外，我没有对表格做任何特别的事情:

Here's the formula I'm using and I didn't do anything special with the table outside of add a 'Totals' row:

=SUM(B2+C1-D2)

推荐答案

您可以停止创建计算列.默认情况下，自动填充公式以在 Excel 表中创建计算列的选项处于启用状态.如果在表格列中输入公式时不希望 Excel 创建计算列，则可以关闭填充公式的选项.如果您不想关闭该选项，但又不想在处理表时始终创建计算列，则可以停止自动创建计算列.

You can stop creating calculated columns. The option to automatically fill formulas to create calculated columns in an Excel table is on by default. If you don’t want Excel to create calculated columns when you enter formulas in table columns, you can turn the option to fill formulas off. If you don’t want to turn the option off, but don’t always want to create calculated columns as you work in a table, you can stop calculated columns from being created automatically.

打开或关闭计算列

1) 在文件标签上，点击选项.

1) On the File tab, click Options.

2) 点击校对.

3) 在自动更正选项下，点击自动更正选项.

3) Under AutoCorrect options, click AutoCorrect Options.

4) 单击键入时自动套用格式标签.

5) 在工作时自动下，选中或清除在表格中填充公式以创建计算列复选框以打开或关闭此选项.

5) Under Automatically as you work, select or clear the Fill formulas in tables to create calculated columns check box to turn this option on or off.

停止自动创建计算列

在表格列中输入第一个公式后，单击显示的自动更正选项按钮，然后单击停止自动创建计算列.

After entering the first formula in a table column, click the AutoCorrect Options button that is displayed, and then click Stop Automatically Creating Calculated Columns.

这篇关于如何防止 Excel 自动复制表格中的公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！