|
篩選后求和 下圖左表是數(shù)據(jù)源，現(xiàn)在的要求是對篩選后的考核成績求總和。對篩選后的數(shù)據(jù)匯總，基本都是用subtotal函數(shù)。只需在F2單元格中輸入公式=SUBTOTAL(9,D3:D15)即可。subtotal是個(gè)分類匯總函數(shù)，它可以忽略篩選后的隱藏行，只對可見單元格進(jìn)行數(shù)據(jù)匯總。匯總的方式有很多種，比如求和，求最大，求最小等，其中9代表的就是求和。 SUBTOTAL(9,D3:D15)這個(gè)公式的意思就是，對D3:D15中的可見單元格求和。 篩選后條件求和 上一題是篩選后直接求和，現(xiàn)在的要求是對篩選后銷售部的考核成績求總和。銷售部的數(shù)據(jù)已經(jīng)用顏色標(biāo)出，效果如動(dòng)圖所示。
對于這個(gè)問題，我們可以先用輔助列來完成。首先，把之前的條件篩選清除，恢復(fù)到還未篩選的狀態(tài)。然后在E列添加輔助列，E2單元格輸入公式=SUBTOTAL(3,C3)，下拉填充，現(xiàn)在得到的結(jié)果都是1。SUBTOTAL(3,C3)這個(gè)公式是對一個(gè)單元格非空計(jì)數(shù)，3相當(dāng)于counta。如果這個(gè)單元格所在的行未隱藏，結(jié)果就是1。如果所在的行隱藏，結(jié)果就是0，因?yàn)閟ubtotal不對篩選后隱藏的單元格計(jì)數(shù)。 接下來，在G2單元格輸入公式=SUMPRODUCT((C3:C15="銷售部")*E3:E15*D3:D15)，就可以了。這個(gè)公式的意思是，在C列的單元格中，既是銷售部，同時(shí)所在的行又是可見行的，返回對應(yīng)的成績；不同時(shí)滿足這2個(gè)條件的返回0。最后用sumproduct求和。=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2,ROW(1:13),))*(C3:C15="銷售部")*D3:D15)
篩選后不重復(fù)值計(jì)數(shù) 現(xiàn)在的要求是計(jì)算篩選后不重復(fù)部門的個(gè)數(shù)，并把不重復(fù)部門羅列出來。從下圖中可以看到不重復(fù)的部門只有3個(gè)，分別是生產(chǎn)部、人事部和銷售部。這2個(gè)問題用函數(shù)來做都很復(fù)雜，我這里只提供公式，不做解釋。不重復(fù)個(gè)數(shù)的公式為： =COUNT(0/FREQUENCY(ROW(A:A),SUBTOTAL(3,OFFSET(C2,ROW(1:13),))*MATCH(C3:C15,C3:C15,)))-1
=LOOKUP(,0/FREQUENCY(1,SUBTOTAL(3,OFFSET(C$2,ROW($1:$13),))*ISNA(MATCH(C$3:C$15,C$17:C17,))),C$3:C4)&""
|
