我有一个相当大的 Excel 表,其中前 20 列包含输入数据,另外 15 列包含使用输入数据进行计算的公式。
我经常删除整个输入数据集并用新的数据集替换它,输入数据量各不相同,范围从 4.000 到 10.000 行。
Excel 是否支持根据输入数据的行数自动调整公式部分?我现在手动执行此操作,如果输入数据小于之前的设置,则删除行,如果输入数据变大,则复制公式单元格。这非常繁琐,所以我正在寻找一种自动化的方法。
如果没有输入数据,某些公式会生成#N/A,并且我会使用数据透视表压缩计算数据,因此我需要与输入数据行完全匹配的公式。
我使用的是 Excel 2003。
答案1
要添加行,您只需选择每个公式列中的最后一个单元格,然后双击右下角,就像您要拖动一样,但改为双击。这将向下填充公式以匹配左侧的值。
之前(双击小黑框的位置):
后:
答案2
一些可能的解决方案....
1) 您能使用计算字段 - 即数据透视表公式吗?如果您的公式仅引用每行上的列(本身),则计算字段效果很好。由于它们是数据透视表的一部分,因此它们不会占用空间,也不会减慢电子表格的速度。如果您的公式是垂直的或复杂的,则可能不起作用。
2) 使用自动表格格式(插入表格),而不是纯数据。在自动表格模式下输入的公式将在您删除或添加行时被删除或添加。
3) 在公式中使用 IF(ISERROR(公式,然后空白或零,否则公式) 来隐藏无效结果。如果您的数据透视表计数数据,这将不起作用 - 因为它可能会计算那些空行,但如果您主要依靠值的总和,它应该会很好地工作。
答案3
如果您经常这样做,您可以在工作表上创建一个 VBA 按钮控件,以清除前 20 列中的所有数据,并仅保留第一行中接下来 15 列的公式。这样,您只需单击按钮,然后粘贴新数据,然后双击填充第 1 行 U 列至 AJ 列。