問與答85： 如何統計匯總篩選過的列表數據？

excelperfect

Q：如下圖1所示，需要獲取單元格區域C7:C13中出現的L的數量，及對應的分數之和，但是我們對單元格區域A6:D13應用了篩選，如果篩選的團隊是“West”，那么相應的L的數量是2；如果篩選的團隊是”East“，則相應的L的數量是1；如果沒有篩選，則相應的L的數量是3？

圖1

在圖1中，單元格C15使用了公式：

=COUNTIF(C7:C13,B2)

單元格C16使用了公式：

=SUMIF(C7:C13,B2,D7:D13)

這對于沒有進行數據篩選的數據表來說，是正確的。但是，如果我們對數據應用了篩選，則上述兩個公式的結果就不正確了，如下圖2所示，我們篩選出“East”團隊后的統計：

圖2

很顯然，此時出現在篩選后的數據表中的L只有1次，但上述兩個公式的結果沒有變化，它們忽略了篩選數據而是仍然應用到原來所有的數據中。

如何使用公式，在單元格D2和D3中得到正確的結果？

（注：本案例整理自chandoo.org論壇。）

A：先給出公式，再進行分析。

在單元格D2中輸入公式：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),--(C7:C13=B2))

傳遞給SUMPRODUCT函數的是生成的兩個中間數組：一個是代表所有有效篩選的數據的列表，另一個是代表所有與條件匹配的未篩選的數據的列表，兩個數組的乘積將是一個包含與條件匹配的篩選的數據的數組。

使用SUBTOTAL函數統計單元格區域中有效條目數。指定其參數function_num為3，代表使用COUNTA函數。指定其參數Ref1為：

OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)

即要分類統計的單元格區域。其中：

ROW(C7:C13)-MIN(ROW(C7:C13))

返回數組：

{0;1;2;3;4;5;6}

代入公式中：

OFFSET(C7:C13,{0;1;2;3;4;5;6},,1)

返回數組：

{“W”;”L”;0;”L”;”W”;”L”;”W”}

將其傳遞給SUBTOTAL函數并使用Counta函數累加所使用的數組中每個值的次數。因為SUBTOTAL函數會忽略篩選后的隱藏值，因此應用篩選后其返回的值會不同：

對于上圖1中沒有應用篩選的數據表，SUBTOTAL函數生成的數組為：

{1;1;0;1;1;1;1}

表示在單元格區域C7:C13中，1代表有效的條目，0代表該單元格沒有文本或值。

對于上圖2中應用篩選的數據表，SUBTOTAL函數生成的數組為：

{1;0;0;1;0;0;0}

表示在單元格區域有兩個單元格與條件（示例中為“East”）匹配，即1所處位置的單元格。

對于公式中的：

--(C7:C13=B2)

將單元格區域C7:C13中的數值與單元格B2中的數值比較，得到數組：

{0;1;0;1;0;1;0}

將上述生成的中間數組傳遞給SUMPRODUCT函數，這樣：

對于沒有篩選的數據，公式轉換為：

=SUMPRODUCT({1;1;0;1;1;1;1}, {0;1;0;1;0;1;0})

=SUMPRODUCT({1*0;1*1; 0*0; 1*1; 1*0; 1*1; 1*0})

=3

對于應用了篩選的數據，公式轉換為：

=SUMPRODUCT({1;0;0;1;0;0;0}, {0;1;0;1;0;1;0})

=SUMPRODUCT({1*0;0*1; 0*0; 1*1; 0*0; 0*1; 0*0})

=1

下面，來匯總滿足條件的條目的分數，在單元格D3中輸入公式：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),––(C7:C13=B2),(D7:D13))

或者：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),(C7:C13=B2)*(D7:D13))

其主要運行原理與上文相同。