excel統計至少在一列中滿足條件的行數

Excel統計至少在一列中滿足條件的行數

在這篇文章中，探討一種計算在至少一列中滿足規定條件的行數的解決方案，示例工作表如下圖1所示，其中詳細列出了各個國家在不同年份廢鎳的出口水平。

圖1

假設我們希望確定2004年出口總額大于或等于1000的國家的數量，可以使用公式：

=COUNTIF(B2:B14,”>=1000″)

或者：

=SUMPRODUCT(N(B2:B14>=1000))

如果希望計算2004年和2005年有多少個國家的出口總額大于或等于1000，可以使用公式：

=COUNTIFS(B2:B14,”>=1000″,C2:C14,”>=1000″)

或者：

=SUMPRODUCT(N(B2:B14>=1000),N(C2:C14>=1000))

現在，如果我們希望計算2004年和2005年的數據中至少有一個滿足此標準的國家數量呢?由于數據較少，我們可以從工作表中清楚地標出滿足條件的數據，如下圖2所示。

圖2

顯然，“標準的”COUNTIF(S)公式結構不能滿足要求，因為我們必須確保不要重復計數。實際上，在這種情況下，大多數人傾向于使用SUMPRODUCT函數，即：

=SUMPRODUCT(N((B2:B14>=1000)+(C2:C14>=1000)>0))

但是，如果選擇的話，我們可以使用COUNTIFS函數構造解決方案，因為考慮到該函數相對于SUMPRODUCT函數的優勢(通常，COUNTIFS函數引用整列的能力更有效)，在某些情況下這可能是值得的。

回到我們剛才要解決的問題，實際上要考慮下列3種互斥情形：

1)2004年的數字> =1000，而2005年的數字<1000

2)2004年的數字是<1000，而2005年的數字是> =1000

3)2004年的數字> =1000，而2005年的數字> = 1000

然后，將每種情形統計的結果相加。轉換成Excel公式為：

=COUNTIFS(B2:B14,”>=1000″,C2:C14,”>=1000″)+COUNTIFS(B2:B14,”>=1000″,C2:C14,”<1000″)+COUNTIFS(B2:B14,”<1000″,C2:C14,”>=1000″)

這個公式既不優雅也不簡潔。但是，我們可以將其縮寫為：

=SUM(COUNTIFS(B2:B14,{“>=”,”>=”,”<“}&1000,C2:C14,{“>=”,”<“,”>=”}&1000))

這樣，成功地實現了基于COUNTIFS函數的解決方案來代替通常的SUMPRODUCT函數公式構造。

下面，考慮希望得出的結果涉及的列數不只是兩列，甚至可能是多列的情況。例如，假設要確定從2004年到2012年每年至少有一個數字大于或等于1000的國家的數量。如下圖3所示，我們可以在工作表中標出滿足條件的數據，除了2個國家外，其他11個國家都滿足條件。

圖3

此時，我們根本無法按照上述方法構造與SUMPRODUCT函數解決方案等效的COUNTIFS構造。使用SUMPRODUCT函數的公式：

=SUMPRODUCT(N((B2:B14>=1000)+(C2:C14>=1000)+(D2:D14>=1000)+(E2:E14>=1000)+(F2:F14>=1000)+(G2:G14>=1000)+(H2:H14>=1000)+(I2:I14>=1000)+(J2:J14>=1000)>0))

可得出正確的結果11。然而，公式顯得太笨拙了，如果考慮的列數不是9而是30，那會怎樣!

幸運的是，由于示例中列區域是連續的，因此可以在單個表達式中查詢整個區域(B2：J14)，隨后適當地操縱這個結果數組。

這個解決方案的數組公式如下：

=SUM(N(MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0))>0))

公式中，比較區域中的每個元素是否大于或等于1000：

B2:J14>1000

結果是包含以下布爾值的數組：

{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}

使用N函數強制轉換成數字：

N(B2:J14>1000)

得到：

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

現在，為了計算每一行中1的數量，我們使用MMULT。并且，由于上述數組(一個13行乘9列的數組)包含9列，因此我們用來形成乘積的矩陣的行數必須等于該數組的列數。這樣，形成第二個矩陣的公式構造為：

TRANSPOSE(COLUMN(B2:J14)^0)

轉換為：

TRANSPOSE({2,3,4,5,6,7,8,9,10}^0)

轉換為：

TRANSPOSE({1,1,1,1,1,1,1,1,1})

由于必須確保由9個1組成的數組由9行組成，因此使用TRANSPOSE函數來轉換：

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

這樣，就可以將上述兩個數組傳遞給MMULT函數，因此：

MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0))

轉換為：

MMULT({1,1,1,1,1,1,1,1,1;0,0,1,1,1,1,1,1,1;0,0,1,1,0,1,1,1,1;0,0,0,0,0,1,1,1,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1,1;0,1,0,0,0,0,1,0,1;1,1,1,1,1,1,1,1,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1},{1;1;1;1;1;1;1;1;1})

得到：

{9;7;6;3;9;9;3;9;0;9;8;0;9}

然后，確定該數組中哪些元素大于0，然后將結果求和。因此，公式：

=SUM(N(MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0))>0))

轉換為：

=SUM(N({9;7;6;3;9;9;3;9;0;9;8;0;9}>0))

轉換為：

=SUM(N({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}))

轉換為：

=SUM({1;1;1;1;1;1;1;1;0;1;1;0;1})

結果為：

11

如果不想使用數組公式，可以使用下面的公式替換：

=SUM(N(MMULT(N(B2:J14>1000),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(B2:J14)))^0)>0))

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。