90%的公司都会用到的模板!每月计算期初、收入、支出、余额

VIP学员做的一份表格,卢子对里面的公式进行完善,这个应该很多公司都能用上。

选择月份,每个表格对应的期初、收入、支出、余额自动改变,点链接可以到指定的分表。

到了分表,可以点超链接快速返回汇总表。

涉及到的知识点非常多,这里从简单的开始讲到难的。

1.返回汇总

超链接可以用右键。

也可以用函数HYPERLINK,一般都采用函数,比较灵活。按住Shift键,选择民生和北京,这样就选中了所有分表,在其中一个分表输入公式就等同于所有分表都输入公式。

=HYPERLINK("#汇总!A1","返回汇总")

语法说明,工作表名称和显示内容为可变内容,其他为固定语法,不变。

=HYPERLINK("#工作表名称!A1","显示内容")

2.超链接到分表

按照刚刚的方法,如果要超链接到民生这个表,可以这样设置公式。

=HYPERLINK("#民生!A1","打开")

不过,如果分表多的话,这样一个个改也挺麻烦的。刚好A列已经写好名称,可以直接引用单元格的值。

=HYPERLINK("#"&A4&"!A1","打开")

3.期初

期初就是上个月最后一个对应值,比如现在是2021年7月,就查找2021年6月30日的对应值,如果没有就查找之前最后一个值。

上个月最后一天,可以用日期减1得到。

=A2-1

查找最后满足条件的值,VLOOKUP或者LOOKUP都可以。

=LOOKUP(B2,民生!A:F)

再借助INDIRECT间接引用每个表的区域,就可以。

=LOOKUP($A$2-1,INDIRECT(A4&"!a:f"))

4.余额

期初是上个月的最后一天,余额是这个月的最后一天。

获取当月最后一天,有专门的函数EOMONTH。

=EOMONTH(A2,0)

这样余额也出来了。

=LOOKUP(EOMONTH($A$2,0),INDIRECT(A4&"!a:f"))

5.收入

就是整个月的收入。

遇到这种,最好在每个分表添加一列辅助列,获取月份。

=TEXT(A3,"e年m月")

不用辅助列也行,因为我们刚刚已经知道了两个日期,上个月最后一天$A$2-1,本月最后一天EOMONTH($A$2,0)。整个月就是>上个月最后一天,同时<=本月最后一天。

现在民生整个月的收入就出来了。

=SUMIFS(民生!D:D,民生!A:A,">"&$A$2-1,民生!A:A,"<="&EOMONTH($A$2,0))

语法说明:

=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2)

再按照前面的思路,将所有的区域都用INDIRECT间接引用。

=SUMIFS(INDIRECT(A4&"!d:d"),INDIRECT(A4&"!a:a"),">"&$A$2-1,INDIRECT(A4&"!a:a"),"<="&EOMONTH($A$2,0))

6.支出

收入和支出都是同一个意思,只需将求和区域改下位置就行,其他不变。

=SUMIFS(INDIRECT(A4&"!e:e"),INDIRECT(A4&"!a:a"),">"&$A$2-1,INDIRECT(A4&"!a:a"),"<="&EOMONTH($A$2,0))

要制作一份好用的模板真的不容易,需要考虑的东西实在太多。

推荐:要吗?接近完美的记账凭证模板

上文:11个求和、计数案例,争取都学一次!这个要收藏!

。。

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

(0)

相关推荐