卢子早期基本上都是在各大Excel论坛、Excel交流群免费帮人解答疑难。后来听从一个朋友的建议,在淘宝上开了一家帮人解答疑难的店铺。在这期间,我发现一个现象,付费的人普遍比以前那些人更有礼貌。就因为这个原因,我慢慢喜欢上在淘宝上帮人解答问题,做自己喜欢的事,还能挣点零花钱,何乐而不为。这里挑选一些我在淘宝帮人解答的问题,进行讲解。
现在有4个明细表,表格的格式完全一样,就只是行数不一样而已,分别是成都、深圳、海口、武汉,在最后的汇总表对这4个表进行统计。原先是先用公式在明细表统计好,再引用过去。
统计C列的性别,男女人数分别多少
=COUNTIF(C3:C40,"男")
=COUNTIF(C3:C40,"女")
统计F列客户状态,新客户跟旧客户人数分别为多少
=COUNTIF(F3:F40,"新客户")
=COUNTIF(F3:F40,"旧客户")
统计各种金额:消费金额、应收、扣款项目、实收
=SUM(G3:G40)
=SUM(J3:J40)
=SUM(K3:K40)
=SUM(L3:L40)
4个明细表设置的公式都一样,只是区域不一样而已。
分别统计好后,就在汇总表依次引用,如C3的公式就是:
=成都!B41
这样做会出现2个问题:
明细表的区域限制死了,如果后续有数据添加,不能自动统计;
这种统计只是针对1个月份,如果有多个月份也不能统计。
看到这里,卢子大概就有了一个思路,就问lee:你每个明细表下面的统计能否删除掉?
lee:这个是为了方便统计用的,如果有更好的方法统计可以删除。
得到了一个肯定的说法后,卢子就对这个表格进行了小小的变动。
将明细表的各种统计删除。
在日期后面插入一列,获取月份。
=MONTH(A3)&"月"
用同样的方法,将其他明细表也这样操作。
为了方便测试,卢子在成都这个表添加了几行2月份的数据。
同时也在汇总表添加2月份的区域,并将原来的公式删除。
男性、女性、新客户、旧客户人数所使用的公式一样,只是区域跟条件略做更改而已。
=COUNTIFS(INDIRECT(B3&"!d:d"),"男",INDIRECT(B3&"!b:b"),A3)
=COUNTIFS(INDIRECT(B3&"!d:d"),"女",INDIRECT(B3&"!b:b"),A3)
=COUNTIFS(INDIRECT(B3&"!g:g"),"新客户",INDIRECT(B3&"!b:b"),A3)
=COUNTIFS(INDIRECT(B3&"!g:g"),"旧客户",INDIRECT(B3&"!b:b"),A3)
消费金额、应收、扣款项目所使用的公式一样,只是区域跟条件略做更改而已。
=SUMIF(INDIRECT(B3&"!b:b"),A3,INDIRECT(B3&"!h:h"))
=SUMIF(INDIRECT(B3&"!b:b"),A3,INDIRECT(B3&"!k:k"))
=SUMIF(INDIRECT(B3&"!b:b"),A3,INDIRECT(B3&"!l:l"))
实收:
=H3-K3
尾款:
=H3-I3-K3
本来做完后卢子就只是把最终的附件发给lee,但lee实在水平有限,典型的菜鸟。看了以后一头雾水,啥都不懂,一定要卢子给她解释。为了解释这2条公式,卢子足足花了一个小时。
lee:我的本和笔都准备好了。
卢子:
=COUNTIF(C3:C40,"男")
=COUNTIF(区域,条件)
这个语法能否懂吗?
因为你原来区域是固定的C3:C40,现在你区域不固定,也就是区域要大一点,那你干脆就引用整列,写C:C。
就是统计C列性别为男的次数,像其他的统计新旧客户人数的也一样的道理。
现在进入核心部分,因为有很多表,不可能每个表单独设置公式。
lee:嗯。
卢子:你当然希望同一列下拉就全部表格汇总出来。
lee:对的。
卢子:
正常的话,成都就用=COUNTIF(成都!C:C,"男")
下面的深圳就改成=COUNTIF(深圳!C:C,"男")
这样好麻烦。
lee:是,要是有100张那要改100次。
卢子:
你看,这样下拉就全部出来了
=B3&"!c:c"
所有表格的区域都出来,对吧
lee:慢点,出来区域和后面汇总分表数据啥关系?
卢子:要制作区域出来,这个能理解吧。如统计成都就用=COUNTIF(成都!C:C,"男")。
lee:你的意思是不是,汇总表区域下面城市就用,=B3&"!c:c"这个公式来设置。
lee:这个公式翻译成汉语是啥意义
卢子:刚刚那个b3&"!c:c"就是把内容合并起来而已,就是在区域后面增加!c:c,成都!c:c这样。
lee:喔,懂了。
卢子:因为我们是通过&连接起来的,不是直接用区域生成的,所以需要增加一个函数INDIRECT,才能进行计算
=COUNTIF(INDIRECT(B3&"!c:c"),"男")
也就是这条公式的由来。
lee:INDIRECT这个函数是啥意思?
卢子:因为你不是直接引用,是用公式生成,所以在那里增加这个函数。
lee:直接引用和公式生成哪个方便点啊?
卢子:刚刚不是解释了吗,如果有100个表,你就得直接引用100次,你不搞死人。
lee:喔喔喔喔,明白了。
大神,前面分表是一个月的,总表汇总一个月,那如果有多个月需要怎么做?
卢子:那就用COUNTIFS函数,COUNTIF函数是单条件计数,COUNTIFS函数是多条件计数。
COUNTIFS函数语法:
COUNTIFS(条件区域1,条件1, 条件区域2,条件2……)
刚刚我在你的表格增加了一列计算月份,这样有了前面COUNTIF函数的基础,设置公式就变得很简单。两个函数的语法基本一样,只是COUNTIFS可以多条件计数而已。
=COUNTIFS(INDIRECT(B3&"!d:d"),"男",INDIRECT(B3&"!b:b"),A3)
男女人数跟新老客户用的公式都一样,其他只要更改区域就可以。
前面都是计数,后面的是求和。单条件求和用SUMIF函数,多条件用SUMIFS函数。现在都是单条件求和,那就用SUMIF函数,语法如下:
SUMIF(条件区域,条件,求和区域)
消费金额的公式为:
=SUMIF(INDIRECT(B3&"!b:b"),A3,INDIRECT(B3&"!h:h"))
其他的也相应改变区域跟条件即可。大概就这样,你自己好好理解下。
lee:不打扰你了,我自己好好琢磨下。
联系客服