HI,大家好,我是偏愛函式公式,愛用Excel圖表管理倉庫的大叔Mr趙~
今天要分享的案例是如何統計在多個間斷區間條件下的個數。
如下圖,要計算的是區域「B2:F21」內符合多個條件的資料個數。條件分別為位於區間[1000,1150]、[2000,2250]、[4000,4450]、[6000,6650]、[10000,11050]的資料個數。
下面介紹兩種函式方法解決這個問題:
❶ COUNTIFS函式法。
在H8單元格輸入如下公式:
=SUM(COUNTIFS(B2:F21,“>=”&H2:H6,B2:F21,“<=”&I2:I6))
▲ 左右滑動檢視
效果如下圖:
公式解析:
首先用COUNTIFS函式分別統計5個區間符合條件的個數,返回的結果是陣列{4;1;8;2;5};
然後用SUM函式對上面的結果陣列求和。
我們還可以把公式寫成:
=SUM(COUNTIFS(B2:F21,{“>=”,“>”}&H2:I6)*{1,-1})
▲ 左右滑動檢視
用COUNTIFS函式同時統計大於等於區間最小值,和大於區間最大值的個數,返回陣列{98,94;89,88;77,69;56,54;28,23};
用{1,-1}各自相減(大於區間最小值個數減去大於區間最大值個數),得到陣列{98,-94;89,-88;77,-69;56,-54;28,-23};
最後用SUM函式求和。
❷ FREQUENCY函式法。
=SUM(INDEX(FREQUENCY(B2:F21,H2:I6-{1,0}),{2,4,6,8,10}))
▲ 左右滑動檢視
首先用FREQUENCY函式統計,被10個數字分隔成的11個連續區間個數;
然後用INDEX函式把第2、4、6、8、10這5個區間統計的個數找出來;
最後用SUM函式對這5個區間的個數求和。
如果你有更好的方法,不妨一起討論!