汇总表,获取每一家银行上日余额、本日收入、本日支出、本日余额。 每一家银行的列数一样,行数不一样。 这是她原来写的公式,看起来挺复杂的。本日收入和支出都可以获得,但没法获得上日余额。 =SUMIFS(INDIRECT(B4&"!N:N"),INDIRECT(B4&"!A:A"),$A$2,INDIRECT(B4&"!B:B"),$B$2,INDIRECT(B4&"!C:C"),汇总表!$C$2) 卢子看完所有表格后,提出了一个建议,借助辅助列先将年、月、日合并成标准日期,这样会使公式更加简洁。DATE函数就是获取标准日期。 =DATE(A3,B3,C3) 1.上日余额 本日是2019/6/24,上日就是小于这个日期的最大日期。23日和22日都没有余额,所以上日余额就是21日对应的金额。 这种看起来很难,实际上很简单,借助VLOOKUP函数的模糊查找就能轻松搞定。 =VLOOKUP(DATE($A$2,$B$2,$C$2)-1,INDIRECT(B4&"!D:P"),13) 上日,就是本日的日期-1,这样就能查找到小于本日的最大日期。区域从D:P,一共13列,这样就可以查找到上日余额。 2.本日收入 本日收入跟上日余额不一样,需要进行汇总本日的所有收入项目。 这样直接借助SUMIF函数就可以,是不是比原来的公式更加简洁? =SUMIF(INDIRECT(B4&"!D:D"),DATE($A$2,$B$2,$C$2),INDIRECT(B4&"!N:N")) SUMIF函数是单条件求和,SUMIFS函数是多条件求和。 3.本日支出 本日支出跟本日收入原理一样,只是区域不同而已,修改公式的区域即可。 =SUMIF(INDIRECT(B4&"!D:D"),DATE($A$2,$B$2,$C$2),INDIRECT(B4&"!O:O")) 4.本日余额 上日余额+本日收入-本日支出就是本日余额。 =C4+D4-E4 到此问题就解决了,其实辅助列也是挺好的,可以让问题变得更简单。 作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban) |
|