Excel 怎么自动填充公式?

部分工作表的数据并不会一次性输入完成,而是每天追加一部分。假设工作表中有公式(见图4.267),输入新数据后还需要手工填充公式,这无疑拖慢了制表节奏。

有没有方法让Excel根据用户新增的数据自动填充公式呢?

图4.267 产量排名表

解题步骤

让公式自动向下填充有两个方法:其一是设置Excel选项,并改造公式,从而让Excel将第5行以后的公式自动向下填充;其二是将区域转换成表,从而不需要修改公式就可以让公式自动填充,具体步骤如下。

1.将公式“=RANK.EQ(C2,C$2:C$6)”修改为“=RANK.EQ(C2,C:C)”。

2.按组合键<Alt+T+O>,弹出“Excel选项”对话框。

3.打开“高级”选项卡,选择“扩展数据区域格式及公式”,操作界面如图4.268所示。

图4.268 设置选项让Excel可以自动填充公式

选择此选项后并不会立即生效,必须满足它的条件才会自动扩展公式,其条件是当前数据区域已经存在不少于5行数据,数据区域中包含不少于5条公式,同时当前的5条公式必须一致,然后在后面的第一个空行新增数据时才会自动填充公式。

4.在 A7:C7 中分别输入张震、6#、1000,单击 Enter 键后 D7 单元格会自动产生公式“=RANK.EQ(C7,C:C)”,同时前面5个单元格的公式也会相应地更新,效果如图4.269所示。

图4.269 D7单元格自动产生公式

以上方法能实现需求,但是有条件限制,需要修改公式,而且必须已经存在不少于5行资料,然后才能自动填充公式。下面的方法没有任何条件限制,操作方法也简单,步骤如下。

1.重新做一个表,输入图4.270中的数据。其中D2单元格有公式,其他单元格没有公式。

图4.270 新表用于测试的数据

2.选择A1:D2区域,按组合键<Ctrl+T>,弹出“创建表”对话框,然后单击“确定”按钮。

3.在A3单元格输入“黄至中”,当单击Enter键后光标移到B3单元格,此时D2单元格的公式已经自动填充到 D3,只不过由于 C3 还没有输入数值,因此公式的计算结果是错误值,效果如图4.271所示。

4.在B2和C2单元格中分别输入2#和890,单击Enter键后D2单元格的公式会重新计算产量890在C2:C3区域的排名,效果如图4.272所示。

图4.271 自动填充公式到D3

图4.272 更新公式

5.继续在第4行输入数据:陈明、3#、798。单击Enter键,D3单元格的公式会自动向下填充到D4单元格,计算结果为1,同时还会更新D2:D3区域的公式结果,效果如图4.273所示。

图4.273 将公式填充到D4

知识扩展

1.让公式自动向下填充可以减少手工填充公式的时间,采用本例中第二种方法既设置简单又无条件限制。不过区域转换成表后会自动应用表的样式,在表中奇偶行会显示为不同颜色,如果不喜欢这种颜色,可以将其样式由名称“中等深浅2”修改为“无”。

2.本例中第一步将公式“=RANK.EQ(C2,C$2:C$6)”修改为“=RANK.EQ(C2,C2:C)”,其目的是扩大引用范围,避免公式向下填充后计算出错。公式“=RANK.EQ(C2,C$2:C$6)”仅适用于第2行到第6行,填充到第7或以后的单元格就会出错,而公式“=RANK.EQ(C2,C2:C)”则适用于任何一行,因此要让公式向下填充,有必要修改公式。

但是,采用第二种方法填充公式时是不必修改公式的,它会在填充公式时相应地修改引用范围,其计算结果总是正确的。因此不管任何时候,采用第二种方法填充公式既简单又准确。

发布者

Excel22

专为职场萌新准备的免费全面的Excel入门及提高学习网站,也可作为Excel老司机杂耍各种函数的宝典 —— Excel22.com 网址超好记

发表评论

邮箱地址不会被公开。 必填项已用*标注