|
excelperfect 在這篇文章中，探討一種計算在至少一列中滿足規(guī)定條件的行數(shù)的解決方案，示例工作表如下圖1所示，其中詳細(xì)列出了各個國家在不同年份廢鎳的出口水平。 圖1 假設(shè)我們希望確定2004年出口總額大于或等于1000的國家的數(shù)量，可以使用公式： =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)) 現(xiàn)在，如果我們希望計算2004年和2005年的數(shù)據(jù)中至少有一個滿足此標(biāo)準(zhǔn)的國家數(shù)量呢？由于數(shù)據(jù)較少，我們可以從工作表中清楚地標(biāo)出滿足條件的數(shù)據(jù)，如下圖2所示。 圖2 顯然，“標(biāo)準(zhǔn)的”COUNTIF（S）公式結(jié)構(gòu)不能滿足要求，因為我們必須確保不要重復(fù)計數(shù)。實際上，在這種情況下，大多數(shù)人傾向于使用SUMPRODUCT函數(shù)，即： =SUMPRODUCT(N((B2:B14>=1000)+(C2:C14>=1000)>0)) 但是，如果選擇的話，我們可以使用COUNTIFS函數(shù)構(gòu)造解決方案，因為考慮到該函數(shù)相對于SUMPRODUCT函數(shù)的優(yōu)勢（通常，COUNTIFS函數(shù)引用整列的能力更有效），在某些情況下這可能是值得的。 回到我們剛才要解決的問題，實際上要考慮下列3種互斥情形： 1）2004年的數(shù)字> =1000，而2005年的數(shù)字<1000 2）2004年的數(shù)字是<1000，而2005年的數(shù)字是> =1000 3）2004年的數(shù)字> =1000，而2005年的數(shù)字> = 1000 然后，將每種情形統(tǒng)計的結(jié)果相加。轉(zhuǎn)換成Excel公式為： =COUNTIFS(B2:B14,'>=1000',C2:C14,'>=1000')+COUNTIFS(B2:B14,'>=1000',C2:C14,'<1000')+COUNTIFS(B2:B14,'<1000',C2:C14,'>=1000') 這個公式既不優(yōu)雅也不簡潔。但是，我們可以將其縮寫為： =SUM(COUNTIFS(B2:B14,{'>=','>=','<'}&1000,C2:C14,{'>=','<','>='}&1000)) 這樣，成功地實現(xiàn)了基于COUNTIFS函數(shù)的解決方案來代替通常的SUMPRODUCT函數(shù)公式構(gòu)造。 下面，考慮希望得出的結(jié)果涉及的列數(shù)不只是兩列，甚至可能是多列的情況。例如，假設(shè)要確定從2004年到2012年每年至少有一個數(shù)字大于或等于1000的國家的數(shù)量。如下圖3所示，我們可以在工作表中標(biāo)出滿足條件的數(shù)據(jù)，除了2個國家外，其他11個國家都滿足條件。 圖3 此時，我們根本無法按照上述方法構(gòu)造與SUMPRODUCT函數(shù)解決方案等效的COUNTIFS構(gòu)造。使用SUMPRODUCT函數(shù)的公式： =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)) 可得出正確的結(jié)果11。然而，公式顯得太笨拙了，如果考慮的列數(shù)不是9而是30，那會怎樣！ 幸運(yùn)的是，由于示例中列區(qū)域是連續(xù)的，因此可以在單個表達(dá)式中查詢整個區(qū)域（B2：J14），隨后適當(dāng)?shù)夭倏v這個結(jié)果數(shù)組。 這個解決方案的數(shù)組公式如下： =SUM(N(MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0))>0)) 公式中，比較區(qū)域中的每個元素是否大于或等于1000： B2:J14>1000 結(jié)果是包含以下布爾值的數(shù)組： {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函數(shù)強(qiáng)制轉(zhuǎn)換成數(shù)字： 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} 現(xiàn)在，為了計算每一行中1的數(shù)量，我們使用MMULT。并且，由于上述數(shù)組（一個13行乘9列的數(shù)組）包含9列，因此我們用來形成乘積的矩陣的行數(shù)必須等于該數(shù)組的列數(shù)。這樣，形成第二個矩陣的公式構(gòu)造為： TRANSPOSE(COLUMN(B2:J14)^0) 轉(zhuǎn)換為： TRANSPOSE({2,3,4,5,6,7,8,9,10}^0) 轉(zhuǎn)換為： TRANSPOSE({1,1,1,1,1,1,1,1,1}) 由于必須確保由9個1組成的數(shù)組由9行組成，因此使用TRANSPOSE函數(shù)來轉(zhuǎn)換： {1;1;1;1;1;1;1;1;1} 這樣，就可以將上述兩個數(shù)組傳遞給MMULT函數(shù)，因此： MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0)) 轉(zhuǎn)換為： 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} 然后，確定該數(shù)組中哪些元素大于0，然后將結(jié)果求和。因此，公式： =SUM(N(MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0))>0)) 轉(zhuǎn)換為： =SUM(N({9;7;6;3;9;9;3;9;0;9;8;0;9}>0)) 轉(zhuǎn)換為： =SUM(N({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE})) 轉(zhuǎn)換為： =SUM({1;1;1;1;1;1;1;1;0;1;1;0;1}) 結(jié)果為： 11 如果不想使用數(shù)組公式，可以使用下面的公式替換： =SUM(N(MMULT(N(B2:J14>1000),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(B2:J14)))^0)>0)) 注：本技巧整理自excelxor.com，有興趣的朋友對照原文研讀，收獲更豐。
|
|
|
