錦子老師

劉飄飄：「錦子老師您好，請教一個數字出現統計如何用公式計算出來，如下圖所示:

有一組數字區域A2~E20，大小為1~9,每列數字不重複，請教當G2=5，A3~E20中若列中5欄資料有5時，則將其上一列儲存格出現1~9數字統計出來，將1~9各個數量分別放入H2~P2儲存格範圍，依此類推G3~G6時，H3~P6儲存格之值為何？謝謝！」

錦子老師：「首先在H8:L8依序輸入5、1、3、6、9。

點取H9儲存格輸入公式：

=SUMPRODUCT(LARGE(($A$2:$E$20=H$8)*ROW($A$2:$E$20),ROW()-8))

再將公式複製到H10:L23儲存格範圍。

【公式解說】

=SUMPRODUCT(LARGE(($A$2:$E$20=H$8)*ROW($A$2:$E$20),ROW()-8))

統計A2:E20儲存格中等於H8儲存格內容(($A$2:$E$20=H$8))所在列(ROW($A$2:$E$20))，目前列號減8(ROW()-8)，傳回含有H8儲存格內容的列號由大到小排列(LARGE(($A$2:$E$20=H$8) *ROW($A$2:$E$20),ROW()-8))。

點取H2儲存格輸入公式：

=COUNTIF(OFFSET($A$1,$H$9-2,0,1,5),H1)+COUNTIF(OFFSET($A$1,$H$10-2,0,1,5),H1) +COUNTIF(OFFSET($A$1,$H$11-2,0,1,5),H1)+COUNTIF(OFFSET($A$1,$H$12-2,0,1,5),H1) +COUNTIF(OFFSET($A$1,$H$13-2,0,1,5),H1)+COUNTIF(OFFSET($A$1,$H$14-2,0,1,5),H1) +COUNTIF(OFFSET($A$1,$H$15-2,0,1,5),H1)+COUNTIF(OFFSET($A$1,$H$16-2,0,1,5),H1) +COUNTIF(OFFSET($A$1,$H$17-2,0,1,5),H1)

再將公式複製到I2:P2儲存格範圍。

【注意】

一個儲存格可以包含的字元總數32,767 個字元，但只顯示1024個字元。

H3:H6儲存格公式與H2不同的只是COUNTIF(OFFSET($A$1,$H$9-2,0,1,5),H1)中的H9依序變成I9~I18、J9~J17、K9~K20、L9~L22(不含0的列)。

【公式解說】

OFFSET($A$1,$H$9-2,0,1,5) 從A1儲存格向下移動H9儲存格的值(20)，為何要減2是因為要抓其上方列的內容，然後框選該列(1列5欄)來統計。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

部落格相關範例