【文章內(nèi)容簡(jiǎn)介】 ? 即可在 B20單元格計(jì)算出來(lái)本次采購(gòu)商品的總金額， 利用 SUMPRODUCT函數(shù)實(shí)現(xiàn)多條件匯總 利用 SUMPRODUCT函數(shù)實(shí)現(xiàn)多條件匯總 ? 說(shuō)明 ? 從本例可以看出， SUMPRODUCT函數(shù)可以計(jì)算幾個(gè)數(shù)組之間對(duì)應(yīng)元素乘積之和。其實(shí)，它還可以用于多條件的求和。 利用 SUMIFS函數(shù)實(shí)現(xiàn)多重條件的數(shù)據(jù)匯總 ? SUMIFS函數(shù)是 Excel2023中新增的一個(gè)特色的統(tǒng)計(jì)匯總函數(shù)，它解決了 SUNIF函數(shù)只能設(shè)置一個(gè)條件的局限性，可以設(shè)置多個(gè)不同的條件。其語(yǔ)法格式如下： ? SUMIFS(求和區(qū)域，條件區(qū)域 1，條件 1，條件區(qū)域 2，條件 2…… )， ? 其中： ? 求和區(qū)域 ——進(jìn)行求和的單元格區(qū)域，包括數(shù)字或包含數(shù)字的名稱(chēng)、數(shù)組或引用。 ? 條件區(qū)域 1，條件區(qū)域 2， …… 是計(jì)算關(guān)聯(lián)條件的區(qū)域，最多可以設(shè)置 127個(gè)。 ? 條件 1，條件 2， …… 是數(shù)字、表達(dá)式、單元格引用或文本形式的條件，要與前面的條件區(qū)域相對(duì)應(yīng)，最多可以設(shè)置 127個(gè)，格式要求與 SUMIF函數(shù)的一樣。 利用 SUMIFS函數(shù)實(shí)現(xiàn)多重條件的數(shù)據(jù)匯總 ? 某公司工程項(xiàng)目部人員的薪資基本數(shù)據(jù)。現(xiàn)在要求在 J28單元格中匯總出 “第 1項(xiàng)目組中，工齡在 20年以上的男工程師基本工資總額 ” 。 ? 問(wèn)題分析：這里要匯總 “第 1項(xiàng)目組中工齡在 20年以上的男工程師基本工資總額 ”，其中包括了 4個(gè)不同的條件 (項(xiàng)目組、性別、職稱(chēng)、工齡 )，用 SUMIF顯示無(wú)法解決，而不管用 SUM與 IF函數(shù)組合，還是用SUMPRODUCT函數(shù)，公式都較復(fù)雜。對(duì)于這種有多個(gè)條件，并且多個(gè)條件之間是邏輯 “與 ”關(guān)系的情況，利用 SUMIFS函數(shù)可方便地解決。 利用 SUMIFS函數(shù)實(shí)現(xiàn)多重條件的數(shù)據(jù)匯總 ? 本例中，其實(shí)只要在 J28單元格中輸入如下公式即可： =SUMIFS(H2： H26,C2： C26,第 1項(xiàng)目組 ， D2：D26， 男 ,E2： E26,工程師 ， F2： F26， 20) 利用 SUMIFS函數(shù)實(shí)現(xiàn)多重條件的數(shù)據(jù)匯總 ? SUMIFS函數(shù)使用時(shí)，需要注意以下幾個(gè)事項(xiàng)： ? (1)只有當(dāng)求和區(qū)域中的每一單元格滿(mǎn)足為其指定的所有關(guān)聯(lián)條件時(shí)，才對(duì)這些單元格進(jìn)行求和，也就是說(shuō)各個(gè)條件之間是嚴(yán)格的邏輯 “與 ”關(guān)系 。 ? (2)求和區(qū)域中包含 TRUE的單元格計(jì)算為 1，包含 FALSE的計(jì)算為 0。 ? (3)與 SUMIF函數(shù)中的條件區(qū)域和條件參數(shù)不同， SUMIFS中每個(gè)條件區(qū)域的大小和形狀必須與求和區(qū)完全相同。 ? (4)SUMIFS和 SUMIF的參數(shù)順序不同。具體而講， “求和區(qū)域 ”參數(shù)在 SUMIFS中是第一個(gè)參數(shù)，而在 SUMIF中則是第三個(gè)參數(shù) 利用 DSUM函數(shù)對(duì)數(shù)據(jù)庫(kù)表格進(jìn)行多條件匯總 ? Excel中為數(shù)據(jù)處理提供了 12個(gè)數(shù)據(jù)庫(kù)函數(shù)，這些函數(shù)都以 D開(kāi)頭，也稱(chēng)為 D函數(shù)。其中 DSUM函數(shù)的功能就是對(duì)數(shù)據(jù)庫(kù)表格進(jìn)行多條件匯總。其語(yǔ)法格式如下： ? 利用 DSUM函數(shù)對(duì)數(shù)據(jù)庫(kù)表格進(jìn)行多條件匯總 ? DSUM(database,field,criteria) ? 該函數(shù)的功能是：按照 criteria(條件區(qū)域 )的條件，從 database(數(shù)據(jù)庫(kù)區(qū)域 )中查找數(shù)據(jù)，將找到滿(mǎn)足條件的記錄中對(duì)應(yīng) field(字段名 )的內(nèi)容匯總，作為結(jié)果。 ? database為構(gòu)成數(shù)據(jù)庫(kù)的單元格區(qū)域； ? field是 database中的列標(biāo)題，可以是常量文本 (如 “產(chǎn)量”)，也可以是數(shù)據(jù)列在 ? database中所在位置的列序號(hào)： 1表示第一列， 2表示第二列， 3代表第三列 …… ： ? criteria稱(chēng)為條件區(qū)域，該區(qū)域應(yīng)為一個(gè)矩形區(qū)域，它的正確設(shè)置非常重要。 利用 DSUM函數(shù)對(duì)數(shù)據(jù)庫(kù)表格進(jìn)行多條件匯總 ? 某公司的產(chǎn)品銷(xiāo)售記錄清單，現(xiàn)在要求進(jìn)行如下操作： ? (1)匯總東北區(qū)在 3月份的銷(xiāo)售額； ? (2)匯總東北區(qū)和西北區(qū)在 2月份的銷(xiāo)售額； ? (3)計(jì)算華中區(qū)的陳回春對(duì) KJ21/4的總銷(xiāo)售額。 ? 問(wèn)題分析：本例中的條件匯總的實(shí)現(xiàn)，可通過(guò)DSUM函數(shù)來(lái)完成。 利用 DSUM函數(shù)對(duì)數(shù)據(jù)庫(kù)表格進(jìn)行多條件匯總 ? 操作步驟如下： ? (1) 設(shè)置表格，并輸入相關(guān)文字，進(jìn)行表格格式設(shè)計(jì)。 ? (2)為第一個(gè)問(wèn)題設(shè)置條件區(qū)域。本問(wèn)題需要設(shè)置三個(gè)條件 (一個(gè) “銷(xiāo)售區(qū)域 ”，兩個(gè)用來(lái)確定 3月區(qū)間的 “銷(xiāo)售日期”條件 )，分別在 I J K4單元格中輸入 “銷(xiāo)售區(qū)域 ” 、“ 銷(xiāo)售日期 ” 、 “ 銷(xiāo)售日期 ” ，在 I J K5單元格中分別輸入各自上面對(duì)應(yīng)列標(biāo)志應(yīng)該滿(mǎn)足條件的文本或條件表達(dá)式，即 “東北區(qū) ”、 “=202331”、 “202341”(后面兩個(gè)條件的交集，正好就是 3月應(yīng)滿(mǎn)足的條件 )。 ? (3)將光標(biāo)定位于 J6單元格，輸入公式 “=DSUM(A1： G28，銷(xiāo)售額 ,I4： K5)”，然后按 (Enter)鍵確認(rèn)公式，即可在 J6單元格中匯總出東北區(qū)在 3月份的銷(xiāo)售額。 ? 利用 DSUM函數(shù)對(duì)數(shù)據(jù)庫(kù)表格進(jìn)行多條件匯總 ? (4)按照與上面類(lèi)似的方法解決第二個(gè)問(wèn)題。該問(wèn)題的條件區(qū)域包括三行，因?yàn)楸締?wèn)題中匯總的數(shù)據(jù)之間存在著“ 或 ” 的關(guān)系，所以條件要輸入到不同的行中，本問(wèn)題中， J14單元格的輸入公式為 “=DSUM(A1： G28， 銷(xiāo)售額 ， I11： K13)”。 ? (5)再按照與上面類(lèi)似的方法解決第三個(gè)問(wèn)題。該問(wèn)題的條件區(qū)域包括三列兩行，每列各有一個(gè)與其他不同的列標(biāo)志，因?yàn)楸締?wèn)題中匯總的條件共有三個(gè)不同列標(biāo)題確定的三個(gè)條件，而這些數(shù)據(jù)之間存在著 “與 ”的關(guān)系，所以三個(gè)條件要輸入到同一行中。本問(wèn)題中， J20單元格的輸入公式為 “=DSUM(A1： G28， 銷(xiāo)售額 ,I18： K19)”。 ? 以上所有操作完成后，得到如 下 圖所示的最終結(jié)果。 利用 DSUM函數(shù)對(duì)數(shù)據(jù)庫(kù)表格進(jìn)行多條件匯總 數(shù)據(jù)的分類(lèi)匯總統(tǒng)計(jì) ? 在對(duì)數(shù)據(jù)庫(kù)表格進(jìn)行數(shù)據(jù)統(tǒng)計(jì)時(shí)，經(jīng)常需要按照某些指定列的數(shù)據(jù)取值要求，對(duì)數(shù)據(jù)庫(kù)格式表格按分類(lèi)統(tǒng)計(jì)匯總。例如在 “公司工資表 ”中，按照財(cái)務(wù)部、人事部、銷(xiāo)售部等分類(lèi)匯總各部門(mén)人員的獎(jiǎng)金總和；在 “商品銷(xiāo)售表 ”中，按照商品種類(lèi)或者業(yè)務(wù)員姓名，匯總統(tǒng)計(jì)各自對(duì)應(yīng)的銷(xiāo)售總額。為此， Excel提供了 “分類(lèi)匯總 ”功能，它通過(guò) “數(shù)據(jù) ” → “分級(jí)顯示 “→ ”分類(lèi)匯總 ”命令來(lái)實(shí)現(xiàn)。 本部分 通過(guò)多個(gè)實(shí)例來(lái)講解其具體操作。 分類(lèi)匯總的基本知識(shí) ? 分類(lèi)匯總是在 Excel數(shù)據(jù)庫(kù)表格或者數(shù)據(jù)清單中快捷地匯總數(shù)據(jù)的方法，通過(guò)分級(jí)顯示和分類(lèi)匯總，可以從大量的數(shù)據(jù)信息中按照某些特殊的需要提出有用的信息。 數(shù)據(jù)的分類(lèi)匯總統(tǒng)計(jì) ? 某電腦配件公司 2023年 1月份的銷(xiāo)售流水賬 (為操作方便，此處只是保留了部分?jǐn)?shù)據(jù)，其實(shí)實(shí)際數(shù)據(jù)非常多 )。到了月底，公司領(lǐng)導(dǎo)想得到如下信息： ? (1)每一個(gè)業(yè)務(wù)員的銷(xiāo)售總額以及銷(xiāo)售額最高的業(yè)務(wù)員姓名。 ? (2)每一個(gè)客戶(hù)的月份總購(gòu)買(mǎi)金額，以及各個(gè)客戶(hù)主要傾向于購(gòu)買(mǎi)哪些產(chǎn)品。 ? (3)每一類(lèi)電腦散件的總銷(xiāo)售情況，以及在總銷(xiāo)售額中的比例。 ? (4)在本月中，哪個(gè)時(shí)間區(qū)間銷(xiāo)售情況最好。 ? 數(shù)據(jù)的分類(lèi)匯總統(tǒng)計(jì) ? 以上信息直接從該數(shù)據(jù)庫(kù)表格直接獲取幾乎是不可能的，即便是通過(guò)設(shè)置公式進(jìn)行統(tǒng)計(jì)和計(jì)算也比較困難。但是，使用分類(lèi)匯總，輕點(diǎn)幾下鼠標(biāo)，就可以快捷高效得出結(jié)果。 ? 總之，使用分類(lèi)匯總，能夠在數(shù)據(jù)庫(kù)適當(dāng)位置加上統(tǒng)計(jì)結(jié)果，使其變得清晰易懂。 單層分類(lèi)匯總的建立 ? 對(duì)于上面的實(shí)例，數(shù)據(jù)匯總可按業(yè)務(wù)員進(jìn)行，也可按照品名進(jìn)行，還可按照需要的其他字段進(jìn)行。在執(zhí)行分類(lèi)匯總命令之前，首先應(yīng)該對(duì)數(shù)據(jù)進(jìn)行排序，將數(shù)據(jù)庫(kù)中關(guān)鍵字相同的一些記錄集中到一起。當(dāng)對(duì)數(shù)據(jù)排序之后，就可以對(duì)數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)分類(lèi)匯總。 ? 按照品名進(jìn)行分類(lèi)匯總為例，說(shuō)明分類(lèi)匯總的操作。 單層分類(lèi)匯總的建立 ? 具體操作步驟如下： ? (1)對(duì)需分類(lèi)匯總的字段排序，使相同的記錄集中。本例將光標(biāo)定位到 “ 品名 ” 列中某一單元格，然后單擊 “ 開(kāi)始 ’’ 一 “ 編輯 ” 一 “ 排序和篩選 ” 按鈕，然后從彈出的下拉菜單中選擇并單擊 “ 升序 ” 菜單項(xiàng)，即可實(shí)現(xiàn)數(shù)據(jù)按照 “ 品名 ” 升序排列。 ? (2)選定數(shù)據(jù)庫(kù)中任意一個(gè)單元格。 ? (3)運(yùn)行 “數(shù)據(jù) ” → “分析顯示 ”→ “分類(lèi)匯總 ”命令，出現(xiàn) “分類(lèi)匯總 ”對(duì)話(huà)框。在本對(duì)話(huà)框中進(jìn)行如下操作： ? 單擊 “分類(lèi)字段 ”下拉列表按鈕，選擇 “品名 ”字段作為分類(lèi)匯總的字段。 ? 在 “匯總方式 ”下拉列表中，選擇需要的統(tǒng)計(jì)函數(shù)。分類(lèi)匯總可以支持求和、平均數(shù)、最大、最小、計(jì)數(shù)、乘積等共計(jì) 11種函數(shù)。根據(jù)需要，本例選擇 “求和 ”。 ? 在 “選定匯總項(xiàng) ”列表中，選中需要對(duì)其匯總計(jì)算的字段前面的復(fù)選框。本例中選上 “合計(jì)金額 ”復(fù)選框即可。 單層分類(lèi)匯總的建立 ? (4)選擇匯總數(shù)據(jù)的保存方式，可以看出，有以下三種方式可選： ? 替換當(dāng)前分類(lèi)匯總：選擇這種方式時(shí)，新的分類(lèi)匯總會(huì)取代以前舊的分類(lèi)匯總。 ? 每組數(shù)據(jù)分頁(yè)：選擇這種方式時(shí)，各種不同的分類(lèi)數(shù)據(jù)將分頁(yè)保存。 ? 匯總結(jié)果顯示在數(shù)據(jù)下方：選擇這種方式時(shí)，原數(shù)據(jù)下方會(huì)顯示匯總計(jì)算的結(jié)果。上述三種方式可同時(shí)選中， Excel默認(rèn)的選擇是選中第一項(xiàng)和第三項(xiàng)。 ? (5)單擊 “確定 ”按鈕，可以得到分類(lèi)匯總結(jié)果。 單層分類(lèi)匯總的建立 ? 結(jié)果顯示可以看出，分類(lèi)匯總為匯總數(shù)據(jù)提供了非常靈活有用的方式，它主要可以實(shí)現(xiàn)以下功能： ? (1)在數(shù)據(jù)庫(kù)表格上顯示一組數(shù)據(jù)的分類(lèi)匯總及全體總和。 ? (2)在數(shù)據(jù)庫(kù)表格上顯示多組數(shù)據(jù)的分類(lèi)匯總及全體總和。 ? (3)在分組數(shù)據(jù)上求和、統(tǒng)計(jì)個(gè)數(shù)、求平均數(shù)、求最大 /小值、求總體方差等計(jì)算。 ? (4)為了實(shí)現(xiàn)匯總數(shù)據(jù)的分組方式和打印處理，還可以設(shè)置每組數(shù)據(jù)分頁(yè)放置。 單層分類(lèi)匯總的建立 ? 對(duì) “ 品名 ” 列進(jìn)行分類(lèi)匯總結(jié)果 單層分類(lèi)匯總的建立 ? 按 “ 品名 ” 分類(lèi)，顯示到 2級(jí)的結(jié)果 單層分類(lèi)匯總的建立 ? (1)分類(lèi)匯總的顯示效果還可以進(jìn)行清除。操作時(shí)，先打開(kāi) “分類(lèi)匯總 ”對(duì)話(huà)框，然后單擊左下角的 “全部刪除 ”按鈕即可 (只是刪除匯總結(jié)果，原數(shù)據(jù)將完全保留 )。 ? (2)為了保險(xiǎn)起見(jiàn)，在分類(lèi)匯總之前最好先做好原數(shù)據(jù)庫(kù)的備份工作。 ? (3)在進(jìn)行分類(lèi)匯總之前，按照需要分類(lèi)的字段進(jìn)行排序非常重要，否則相同項(xiàng)目將無(wú)法在分類(lèi)匯總的結(jié)果中計(jì)算到一起， 如下圖所示。 單層分類(lèi)匯總的建立 ? 未按分類(lèi)字段排序的結(jié)果 多重分類(lèi)匯總的操作 ? 上面介紹的分類(lèi)匯總僅僅只是按照 “品名 ”關(guān)鍵字對(duì) “合計(jì)金額 ”進(jìn)行了一種匯總運(yùn)算。實(shí)際上。在同一分類(lèi)匯總級(jí)上可能不只進(jìn)行一種匯總運(yùn)算。為此，Excel還提供了對(duì)同一分類(lèi)進(jìn)行多重匯總的功能，這就是所謂的多重分類(lèi)匯總。 ? 若要在同一匯總表中顯示兩個(gè)以上的匯總數(shù)據(jù)時(shí)，只需對(duì)同一數(shù)據(jù)清單進(jìn)行兩次不同的匯總運(yùn)算即可。需要注意的是，第二次分類(lèi)匯總必須在第一次的匯總結(jié)果上進(jìn)行。 多重分類(lèi)匯總的操作 ? 對(duì)于前例，假如想按照客戶(hù)分類(lèi)匯總，匯總時(shí)，一方面對(duì)各個(gè)客戶(hù)的 “合計(jì)金額 ”求和，另一方面還要求求出對(duì)每一個(gè)客戶(hù)的交易次數(shù)，這就屬于多重分類(lèi)匯總。 ? 其具體操作步驟如下： ? (1)對(duì)數(shù)據(jù)庫(kù)表格，先按照 “客戶(hù) ”關(guān)鍵字進(jìn)行排序。 ? (2)以 “客戶(hù)名稱(chēng) ”為分類(lèi)匯總關(guān)鍵字， “求和 ”為匯總方式，匯總字段為 “合計(jì)金額 ”，對(duì)數(shù)據(jù)表格進(jìn)行第一次分類(lèi)匯總。 ? (3)仍以 “客戶(hù)名稱(chēng) ”為分類(lèi)匯總關(guān)鍵字， “計(jì)數(shù) ”為匯總方式，匯總字段為 “業(yè)務(wù)員 ”，對(duì)數(shù)據(jù)表格進(jìn)行第二次分類(lèi)匯總。在本次設(shè)置 “分類(lèi)匯總 ”對(duì)話(huà)框時(shí)，要取消對(duì)話(huà)框中 “替換當(dāng)前分類(lèi)匯總 (C)”復(fù)選框的設(shè)置 多重分類(lèi)匯總的操作 ? 經(jīng)過(guò)上述分類(lèi)匯總后，最后的多重分類(lèi)匯總，顯示效果如圖 414所示 (注意此時(shí)左側(cè)的顯示按鈕已經(jīng)有了四個(gè)層次，分別對(duì)應(yīng) l、 4，本圖顯示到了級(jí)別 3)。 嵌套分類(lèi)匯總的操作 ? 所謂嵌套分類(lèi)匯總，就是在一個(gè)已經(jīng)按照某一個(gè)關(guān)鍵字建立好分類(lèi)匯總的匯總表中，再按照另一個(gè)關(guān)鍵字進(jìn)行另一種分類(lèi)匯總，這里要求的是兩次的分類(lèi)匯總關(guān)鍵字不同。 ? 建立嵌套分類(lèi)匯總的前提仍然是要對(duì)每個(gè)分類(lèi)匯總關(guān)鍵字排序。第一級(jí)匯總關(guān)鍵字應(yīng)該是排序的第一關(guān)鍵字，第二級(jí)匯總關(guān)鍵字應(yīng)該是第二排序關(guān)鍵字，其余的依此類(lèi)推。 ? 在進(jìn)行嵌套分類(lèi)匯總時(shí)，有幾層嵌套匯總就需要進(jìn)行幾次分類(lèi)匯總操作，第二次匯總在第一次的結(jié)果集上操作，第三次在第二次的結(jié)果上操作，其余的依此類(lèi)推。 嵌套分類(lèi)匯總的操作 ? 嵌套分類(lèi)匯總與多重分類(lèi)匯總的相同點(diǎn)在于二者都需要進(jìn)行多次的分類(lèi)匯總操作；區(qū)別