现在有免费的精英会计交流群,每天前150名进群免费,与众多优秀财务人一起讨论业务、交朋友!进群加微信号kjsc022
某读者,领导要她统计1-3月的数据,她用最原始的方法搞了4个小时。下个月又得重新将所有公式改变一次,想想都是一件恐怖的事。
这是一份简化后的表格,实际表格内容非常多。
累计,统计每个城市的店铺1-3月数据。
每个城市的店铺,格式都一样,记录着每个月的数据。
她原先的做法,每个表每个单元格逐一加上去。
=深圳!E4+深圳!F4+深圳!G4
=广州!E4+广州!F4+广州!G4
=佛山!E4+佛山!F4+佛山!G4
卢子模拟的数据量很少,不用花多久就可以搞定,而实际上内容非常多,按原来的表格,确实需要花几个小时才可以。
这样好不容易写完公式,领导突然改变主意,要统计1-2月或者1-4月,直接就崩溃,又得改一次。
卢子理解清楚了用意,提供了这么一条公式,只要J1这个单元格改变,就可以自动累计1-N个月的数据。
=SUM(OFFSET(INDIRECT(E$2&"!e1"),ROW()-1,0,1,$J$1))
现在以深圳为例,进行说明。现在要获得1-3月的数据。
如果不考虑变动,可以这样写公式。
=SUM(深圳!E4:G4)
但实际上月份是需要改变的,也就是说区域要改变。
OFFSET函数语法说明:
=OFFSET(起点,向下几行,向右几列,行高,列宽)
起点:深圳!$E$1
向下几行:3
向右几列:0
行高:1
列宽:$J$1决定
将内容嵌套进去:
=SUM(OFFSET(深圳!$E$1,3,0,1,$J$1))
深圳这个希望右拉公式变成广州,这时就得引用单元格才可以。
=SUM(OFFSET(INDIRECT(E$2&"!$E$1"),3,0,1,$J$1))
向下3行,希望下拉公式变成4行,这时就得借助ROW函数。
=SUM(OFFSET(INDIRECT(E$2&"!$E$1"),ROW(A3),0,1,$J$1))
而卢子的最终公式是写ROW()-1,而不是ROW(A3)怎么回事?
她的原始表格是不连续区域的,写ROW(A3)这个下拉的时候会改变,而复制到其他区域是不会改变。
而ROW()-1则不同,不管是下拉,还是复制,行号都会自动改变。
问题到此就解决了。
现在有免费的精英会计交流群,每天前150名进群免费,与众多优秀财务人一起讨论业务、交朋友!进群加微信号kjsc022
来源:Excel不加班,作者:卢子