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

问题描述

我正在使用Excel2016.我有一个带标题的表，当我插入公式时，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屋！