与 30万 读者一起学Excel
本表中对应部门预算金额如何用公式,全选所有部门一次性导入对应部门表中,因为有20多个部门,平常都是一个个复制粘贴上去的,有没有快速方法?
所有部门格式一样,都是要在AX列输入预算金额。
这里为了方便说明,只列举了3个部门,其实不管多少部门,操作都一样。
原先采用复制粘贴的方法确实麻烦,一旦预算表的数据变动又得重新复制粘贴一遍,不够智能。
今天,卢子就教你一次性在多个表输入公式,获取每个部门的预算金额。
这套表有两个优点:
01 每个部门的表格格式一样
02 每个部门的表格在A1有列出部门名称
如果没有这两个优点,难度会增加好多倍。解决这种问题有很多种方法,这里以OFFSET函数为例进行说明。
现在要获取总裁办的预算,可以用公式:
=OFFSET(预算!$A$3,ROW(A1),2)
预算表A3单元格向下1行就是明细项目,明细项目再向右2列就是总裁办的金额。
公式下拉要获取每一行的金额,也就是要向下1、2、3、4……向下生成序号用ROW函数。
这样总裁办的预算就解决了,综合管理部在总裁办再过去1列,是不是将公式里面的2改成3就可以。
=OFFSET(预算!$A$3,ROW(A1),3)
理论上这样也行得通,但是这样一来,跟最开始复制粘贴没区别,需要每个部门的表格都改公式,不智能。
公式只有完全一致,才可以一次性填充所有部门分表,这时就要对OFFSET函数第三参数做一点变动。要获取每个部门的列数,可以借助MATCH函数。
=MATCH(G$1,$3:$3,0)
OFFSET函数是偏移列,比如第3列,只需要想右移动2列即可,也就是说得出来的数字再减去1。
=MATCH(G$1,$3:$3,0)-1
这样最终公式就出来了。
=OFFSET(预算!$A$3,ROW(A1),MATCH($A$1,预算!$A$3:$E$3,0)-1)
公式出来以后,就剩下最后一个问题,如何在所有部门分表同时输入公式。
选择总裁办这张表,按住Shift键点内审部这张表,这样就将所有分表选中,输入公式,双击填充公式。
=OFFSET(预算!$A$3,ROW(A1),MATCH($A$1,预算!$A$3:$E$3,0)-1)
打开每个部门的分表,可以看到所有分表都输入了公式,到此,问题就解决了。
大多数公司都有预算表,你有没考虑过自己也做一份预算表?
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
联系客服