|
你好，我是劉卓。歡迎來到我的公號(hào)，excel函數(shù)解析。前天分享了數(shù)據(jù)庫函數(shù)dsum和dcount的基礎(chǔ)用法。今天來分享下它們的多條件求和、計(jì)數(shù)以及多維引用的用法。 多條件求和計(jì)數(shù) 1.多條件且的統(tǒng)計(jì) 還是昨天的數(shù)據(jù)源，A1:F16是構(gòu)成數(shù)據(jù)庫的區(qū)域，H5:I6是條件的區(qū)域，其中條件區(qū)域的第一行要包含列標(biāo)簽，列標(biāo)簽下方的單元格用來寫條件。同一行的條件是且的關(guān)系，不同行的條件是或的關(guān)系。下圖中H5:I6的條件就是“且”的關(guān)系，也就是性別為“男”且城市為“杭州”的記錄。在K6單元格輸入公式=DSUM(A1:F16,"銷售數(shù)量",H5:I6)，計(jì)算性別為“男”且城市為“杭州”的人員的銷售總數(shù)量。
符合條件的記錄已經(jīng)用顏色標(biāo)出，只有兩條記錄。對(duì)數(shù)據(jù)庫函數(shù)的計(jì)算方式可以按下面這樣理解：先把符合條件的記錄篩選出來，然后再對(duì)其中的某一列進(jìn)行統(tǒng)計(jì)匯總。
下圖H10:I12是條件區(qū)域，現(xiàn)在的條件有兩行。第一行的條件是性別為“女”，第二行的條件是城市為“杭州”，它們二者之間是“或”的關(guān)系。也就是性別為“女”或城市為“杭州”的記錄。在K11單元格輸入公式=DCOUNT(A1:F16,,H10:I12)，計(jì)算性別為“女”或城市為“杭州”的人員數(shù)量。符合條件的記錄已經(jīng)用顏色標(biāo)出，共9條。 多單元格中使用數(shù)據(jù)庫函數(shù) 1.統(tǒng)計(jì)不同城市男女的人數(shù)如下圖所示，在J列的單元格中計(jì)算不同城市男女的人數(shù)。比如，第一個(gè)計(jì)算的是性別為“男”且城市為“南京”的人數(shù)，第二個(gè)計(jì)算的是性別為“女”且城市為“南京”的人數(shù)，……，以此類推。在J5單元格中輸入公式=DCOUNT(A$1:F$16,,H$4:I5)-SUM(J$4:J4)，下拉填充。這個(gè)公式的條件區(qū)域是一個(gè)動(dòng)態(tài)區(qū)域，隨著公式的下拉，會(huì)不斷的擴(kuò)展。下面以J7單元格的公式說明一下，dcount的條件區(qū)域擴(kuò)展為H4:I7，所以dcount統(tǒng)計(jì)的是以下3類人的人數(shù)： (1)性別為“男”且城市為“南京”的人數(shù)；(2)性別為“女”且城市為“南京”的人數(shù)；(3)性別為“男”且城市為“杭州”的人數(shù)。現(xiàn)在只需統(tǒng)計(jì)性別為“男”且城市為“杭州”的人數(shù)，所以需要將前面的2類人數(shù)減去。1.在總表中匯總各業(yè)務(wù)員的總銷售額 如下圖所示，在1月到5月的五個(gè)分表中，分別記錄著各業(yè)務(wù)員在每月的銷售金額?，F(xiàn)在的要求是在總表中匯總出各業(yè)務(wù)員在5個(gè)月的總銷售額。這是個(gè)多表匯總的問題，常用的方法是sumif+indirect。其實(shí)我們也可以用dsum+indirect來做，因?yàn)閐sum也支持多維引用。在總表的B2單元格輸入下面的公式，按ctrl+shift+enter結(jié)束，向下填充。=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個(gè)區(qū)域。 DSUM(INDIRECT(ROW($1:$5)&"月!a:b"),2,A$1:A2)這部分用dsum分別統(tǒng)計(jì)5個(gè)區(qū)域中“李一”的銷售金額，得到的結(jié)果有5個(gè)值，分別為{100;83;39;33;145}。 這5個(gè)值分別是“李一”在1月，2月，3月，4月，5月的銷售金額。最后用sum加起來就是5個(gè)月的總銷售額。 當(dāng)公式下拉時(shí)，dsum的條件區(qū)域會(huì)擴(kuò)展，這時(shí)就不只計(jì)算一個(gè)人的銷售金額了，所以要像例2那樣把之前業(yè)務(wù)員的銷售金額減去。 下圖是用sumif的驗(yàn)證，結(jié)果和dsum是一樣的。而且公式還比dsum的長(zhǎng)。不過我還是建議大家用sumif，因?yàn)閐sum會(huì)有“bug”（如果你對(duì)它不了解的話），在條件區(qū)域中，它是默認(rèn)包含右側(cè)的通配符的。這里就不再詳細(xì)說明了。
https://pan.baidu.com/s/1WXRgb-H4qb0vS4o4MgCGlQ
|
