Excel不加班 / 待分类 / 如果你不能改掉这个坏习惯,只能逼自己成...

分享

   

如果你不能改掉这个坏习惯,只能逼自己成为Excel高手!

2021-09-19  Excel不加...

前2天刚发文章提到不规范日期,转眼就又有粉丝发来同类的案例。与其每次都求助别人,还不如努力改变自己。卢子现在就以其中的一小部分进行说明。

明细表都是相同的格式,日期是8位数,好多年的数据混合在一起,这次只看付款日期、核销金额2列。

提问表只看付款金额,就是统计每个明细表8月到12月的金额。

说句实话,这表格设计的不好,一来五颜六色看得眼花,二来明细表的日期写的不规范,三来提问表的月份没有明确到年份。

现在假设是统计统计2021年的每个月数据。

先将明细表的日期转换成以横杆作为分隔符号的日期。这里都是数字所以用0处理。

=TEXT(F5,"0-00-00")


再将横杆的日期转换成年月的形式。文本型(数值型)的日期,同样是e代表4位数的年,m代表月。

=TEXT(TEXT(F5,"0-00-00"),"e年m月")


提问表的月份在前面加年份。

="2021年"&B2


现在两边都有辅助列,要统计ABB这个表的付款金额。

=SUMIF(ABB!K:K,提问!G2,ABB!G:G)


不过明细表有一大堆日期,再加上辅助列就有点乱,现在不用辅助列处理。按照辅助列的思路,进行条件求和,不过SUMIF没法嵌套函数,用SUMPRODUCT取代。

=SUMPRODUCT((TEXT(TEXT(ABB!$F$5:$F$390,"0-00-00"),"e年m月")="2021年"&B2)*ABB!$G$5:$G$390)


再重温下SUMPRODUCT的函数语法,里面的参数都可以嵌套其他函数。

=SUMPRODUCT((条件区域=条件)*求和区域)

现在是多表求和,嵌套INDIRECT就可以依次求和。全部套完,发现有一个表是返回#VALUE!。

=SUMPRODUCT((TEXT(TEXT(INDIRECT(C2&"!$F$5:$F$390"),"0-00-00"),"e年m月")="2021年"&B2)*INDIRECT(C2&"!$G$5:$G$390"))


这种是因为数据源存在文本,文本运算了就是这种错误,比如合计*数字。

这时又得动用SUMPRODUCT函数另一种语法,参数用逗号隔开,遇到文本的当做0处理,非数字参数需要在前面加--。

=SUMPRODUCT(--(条件区域=条件),求和区域)

最终公式出来了。

=SUMPRODUCT(--(TEXT(TEXT(INDIRECT(C2&"!$F$5:$F$390"),"0-00-00"),"e年m月")="2021年"&B2),INDIRECT(C2&"!$G$5:$G$390"))


假设现在不区分年份。

=SUMPRODUCT(--(TEXT(TEXT(INDIRECT(C2&"!$F$5:$F$390"),"0-00-00"),"m月")=B2),INDIRECT(C2&"!$G$5:$G$390"))


绕了一大圈,函数套了又套才解决。如果换个思路,换成标准日期,并且所有表格都在同一个表,30秒就能用透视表解决了。

推荐:你真的会分列吗?

上文:一个函数,搞定80%的对账


看完这个案例,你还敢随心所欲做表吗?

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

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多

    ×
    ×

    ¥.00

    微信或支付宝扫码支付:

    开通即同意《个图VIP服务协议》

    全部>>