|
你好，我是刘卓。欢迎来到我的公号，excel函数解析。前天分享了数据库函数dsum和dcount的基础用法。今天来分享下它们的多条件求和、计数以及多维引用的用法。 多条件求和计数 1.多条件且的统计 还是昨天的数据源，A1:F16是构成数据库的区域，H5:I6是条件的区域，其中条件区域的第一行要包含列标签，列标签下方的单元格用来写条件。同一行的条件是且的关系，不同行的条件是或的关系。下图中H5:I6的条件就是“且”的关系，也就是性别为“男”且城市为“杭州”的记录。在K6单元格输入公式=DSUM(A1:F16,"销售数量",H5:I6)，计算性别为“男”且城市为“杭州”的人员的销售总数量。
符合条件的记录已经用颜色标出，只有两条记录。对数据库函数的计算方式可以按下面这样理解：先把符合条件的记录筛选出来，然后再对其中的某一列进行统计汇总。
下图H10:I12是条件区域，现在的条件有两行。第一行的条件是性别为“女”，第二行的条件是城市为“杭州”，它们二者之间是“或”的关系。也就是性别为“女”或城市为“杭州”的记录。在K11单元格输入公式=DCOUNT(A1:F16,,H10:I12)，计算性别为“女”或城市为“杭州”的人员数量。符合条件的记录已经用颜色标出，共9条。 多单元格中使用数据库函数 如下图所示，在J列的单元格中计算不同城市男女的人数。比如，第一个计算的是性别为“男”且城市为“南京”的人数，第二个计算的是性别为“女”且城市为“南京”的人数，……，以此类推。在J5单元格中输入公式=DCOUNT(A$1:F$16,,H$4:I5)-SUM(J$4:J4)，下拉填充。这个公式的条件区域是一个动态区域，随着公式的下拉，会不断的扩展。下面以J7单元格的公式说明一下，dcount的条件区域扩展为H4:I7，所以dcount统计的是以下3类人的人数： (1)性别为“男”且城市为“南京”的人数；(2)性别为“女”且城市为“南京”的人数；(3)性别为“男”且城市为“杭州”的人数。现在只需统计性别为“男”且城市为“杭州”的人数，所以需要将前面的2类人数减去。1.在总表中汇总各业务员的总销售额 如下图所示，在1月到5月的五个分表中，分别记录着各业务员在每月的销售金额。现在的要求是在总表中汇总出各业务员在5个月的总销售额。这是个多表汇总的问题，常用的方法是sumif+indirect。其实我们也可以用dsum+indirect来做，因为dsum也支持多维引用。在总表的B2单元格输入下面的公式，按ctrl+shift+enter结束，向下填充。=SUM(DSUM(INDIRECT(ROW($1:$5)&"月!a:b"),2,A$1:A2))-SUM(B$1:B1)
INDIRECT(ROW($1:$5)&"月!a:b")这部分形成了跨表的多维引用，返回1月的a:b列，2月的a:b列，……，5月的a:b列，共5个区域。 DSUM(INDIRECT(ROW($1:$5)&"月!a:b"),2,A$1:A2)这部分用dsum分别统计5个区域中“李一”的销售金额，得到的结果有5个值，分别为{100;83;39;33;145}。 这5个值分别是“李一”在1月，2月，3月，4月，5月的销售金额。最后用sum加起来就是5个月的总销售额。 当公式下拉时，dsum的条件区域会扩展，这时就不只计算一个人的销售金额了，所以要像例2那样把之前业务员的销售金额减去。 下图是用sumif的验证，结果和dsum是一样的。而且公式还比dsum的长。不过我还是建议大家用sumif，因为dsum会有“bug”（如果你对它不了解的话），在条件区域中，它是默认包含右侧的通配符的。这里就不再详细说明了。
https://pan.baidu.com/s/1WXRgb-H4qb0vS4o4MgCGlQ
|
