行插入自动填充单元格中的公式 [英] On row insertion autopopulate a formula in a cell

问题描述

我的电子表格中有一个公式列来计算基于另一列的值。我想知道是否有办法使公式默认，以便当我插入一个新行时，该列的单元格会自动获得预设的公式，不需要从上面复制和粘贴。

I have a formula column in my spreadsheet to calculate values based on another column. I want to know if there is a way to make the formula default so that when I insert a new row, the cell of that column automatically gets the formula pre-set, no need to copy and paste from above again.

这是可取的，因为电子表格将被非技术用户填写。目前的解决方法是使用公式预先复制和粘贴整个列，但这不是理想的，因为文件变得不必要地大，打开缓慢。

This is desirable because the spreadsheet will be filled in by non-technical users. The current workaround is to pre-copy-&-paste the whole column with the formula but this is not ideal as the file becomes unnecessarily large and slow to open.

甚至使用VBA的任何方法都是可以接受的，但非VBA解决方案是可取的。

Any method even with VBA is acceptable but a non-VBA solution is preferable.

推荐答案

我的建议是改变你的使用范围在你的工作表上一张桌子。当您将表插入表格（在表内或表格下方）时，表中的公式向下拖动。尽管如此，您必须稍微更改您的公式，因为您必须在表格中引用表格中的单元格略有不同。

My suggestion would be to change your used range on your worksheet to a table. When you insert a row into a sheet with a table (either within or directly below the table) the formulas within the table drag down. Although, you will have to change your formulas slightly as you must reference the cells within the table slightly differently when they are within a table.

这里有几个链接到某些资源虽然肯定会有一些更好的资源：

Here's a couple links to some resources, although there are bound to be some far better resources:

http://www.databison.com/table-formulas-in-excel/

http://excelsemipro.com/2011/04/formulas-and-structured- data-in-excel-tables /

http://www.jkp-ads.com/Articles/Excel2007Tables.asp

这篇关于行插入自动填充单元格中的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！