SUMPRODUCT函數的3個經典案例

2019-09-06 08:20:06

SUMPRODUCT函數向來被稱之為萬能函數,可以進行各種條件計數和條件求和。今天技巧妹要分享的是SUMPRODUCT函數的3個比較典型但是你不一定熟悉的應用。


1、模糊條件求和

SUMPRODUCT函數經常用來根據指定條件進行精確查找,其實也可以進行模糊查找。如下圖表格所示,如何統計城區小學所有男學生的成績之和?



解決:這裏城區小學包括城區一小、城區二小和城區三小,可以進行模糊條件查找。因為SUMPRODUCT函數函數是不支持通配符*或者?的,所以我們在進行模糊條件查找時,需要結合其它函數來實現。輸入公式

=SUMPRODUCT(ISNUMBER(FIND("城區",C2:C13))*(B2:B13="男"),

D2:D13)



説明:先用FIND函數在C2:C13這個區域中查找“城區”這個字符串,若存在返回相應位置,不存在則返回#VALUE!錯誤值;ISNUMBER函數是用來檢測是否為數值,是的話返回TRUE,否則返回FALSE;最後用SUMPRODUCT函數進行多條件求和。


2、按季度求和


如下圖表格所示,我們如何求出各個季度的成交總數?



解決:選中統計表中的空白單元格區域,在E2單元格中輸入公式

=SUMPRODUCT(N(CEILING(MONTH($A$2:$A$15)/3,1)=D2),$B$2:$B$15),

按Ctrl+Enter組合鍵完成所有公式填充。



説明:先利用MONTH函數求出日期所在的季度,再結合CEILING函數向上舍入為最接近的指定基數的倍數,從而判斷出對應的季度,然後用N函數把邏輯值轉化為數值,最後利用SUMPRODUCT函數是將數組間對應的元素相乘,並返回乘積之和。


3、二維區域條件求和


在工作中,我們經常需要根據一維表數據源,在二維表裏進行分類統計。如下圖表格所示,如何統計各門店各類商品的銷量?



解決:選中二維表中的空白單元格區域,在F2單元格中輸入公式

=SUMPRODUCT(($A$2:$A$25=F$1)*($B$2:$B$25=$E2),$C$2:$C$25),

按Ctrl+Enter組合鍵完成所有公式填充。



説明:這裏用到的實際上是SUMPRODUCT函數的多條件求和,公式中第一個條件是指定的門店,注意F1單元格的引用是鎖定行,第二個條件指定的商品,注意E2單元格的引用是鎖定列。


教程推薦

原價169元   優惠價99元


購買須知

1、本教程共計85課時,現已全部更新完畢;

2、點擊文末閲讀原文購買教程後,添加微信號officeskill(技巧妹)或掃描下方二維碼加入學習交流羣並領取操作素材。


↓↓↓點擊 閲讀原文 瞭解更多教程詳情

已同步到看一看
在看



熱點新聞