任意の数値の次に来る数値をカウントしたいです。

質問

エクセルにて任意の数値の次に来る数値をカウントしたいです。
次のようなデータがあります。

【A1セル】【A2セル】
A　,　9
A　,　8
A　,　1
A　,　5
A　,　2
B　,　1
B　,　6
B　,　1
B　,　5
B　,　2

この場合、例えば"1"の次に来る数値をカウントすると
A2セル"1"の次に来ている数値は、"5","6","5"つまり"5"が2回、"6"が1回とカウントしたいです。

また、フィルター機能を利用した場合も自動的に数値が変化するように出来れば嬉しいです。
(Bでソートかけた場合、1の次に来る数字"5"が1回、"6"が1回といった具合に)

仕事上、突然上記の内容で処理をしなければならなくなり
大変困っております・・・。

ご教授頂ければと思います・・・。

回答No.3

KURUMITO

フィルター機能でお望みのデータを表示させることが難しいので、次のようにしてA,Bなどの項目も自動で表示させそれぞれの項目ごとに希望の数値の次に来る数値とその回数を表示させることが適当のように思います。
シート1のA列およびB列にお示しのデータが有るとしてそれらのでーだが2行目から下方に入力されているとします。
A、B名の項目名も自動で、数値なども自動で表示させるには作業列を作って対応することにして結果の表示はシート2で行うことにします。
初めにシート1のC2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,MAX(C$1:C1)+1,""))

D2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(B1=Sheet2!A$1,B2,"")

E2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(D2="","",IF(COUNTIF(D$2:D2,D2)=1,MAX(E$1:E1)+1,""))

シート2ではA1セルに任意の数値を入力します。例えば1と入力します。
シート2のB1セルには次の式を入力して右横方向にオートフィルドラッグコピーします。

=IF(COLUMN(A1)>MAX(Sheet1!$E:$E),"",INDEX(Sheet1!$D:$D,MATCH(COLUMN(A1),Sheet1!$E:$E,0)))

これでA1に入力して任意の数値の下の数値が表示されます。
A2セルには合計数とでも文字を入力します。
A3セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!C:C),"",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!C:C,0)))

B2セルには次の式を入力して右横方向にオートフィルドラッグコピーします。

=IF(B1="","",SUM(B3:B100))

B3セルには次の式を入力して右横方向にオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグコピーします。

=IF(OR($A3="",B$1=""),"",SUMPRODUCT((Sheet1!$A$1:$A$1000=$A3)*(Sheet1!$D$1:$D$1000=B$1)))

これでA,Bなど項目別に任意の数値の下の数値ごとの個数が表示されますし。2行目にはすべての項目について任意の数値の下の数値の総数が表示されます。

お礼

ありがとうございます。

試したところ、うまく行きました。
しかし、やはりフィルター機能を使っての仕様が良いとの事で
根本的な解決には行きませんでした・・・。

回答No.2

MackyNo1

>A2セル"1"の次に来ている数値は、"5","6","5"つまり"5"が2回、"6"が1回とカウントしたいです。

質問の意味がB列の数字で１の下の行の数字の出現回数をカウントしたいということなら。例えば以下のような数式で表示できます（添付画像参照）。

=COUNT(INDEX(1/(($B$2:$B$15=1)*$B$3:$B$16=E2),))

＞また、フィルター機能を利用した場合も自動的に数値が変化するように出来れば嬉しいです。

フィルタを行った場合は実際１つ下のデータではないので、きわめて複雑な配列数式を使う必要があります（検索条件が入力してあるなら比較的に簡単な数式で対応できます）。

どのような目的で、ご質問のような操作をしたいのかにもよりますが、急ぎの用件なら、簡便にフィルタしたデータを別シートにコピー貼り付けしてCOUNTIF関数で対応するのが良いと思います。

お礼

ありがとうございます。

試したところ、うまくいきました。
しかし、やはりフィルター機能を使っての仕様が良いとの事で
根本的な解決には行きませんでした・・・。

回答No.1

keithin

Ａ１とかＡ２ということは，あなたがご質問で書いた絵とは違って，ＡＡＡＡＢＢＢＢとか１５１６１２３とかは「横向け」に並べているんですか？
それともＡ１とＢ１の書き間違いで，データは下向けに並んでるんでしょうか。

○急ぎでしたら簡単な方法：

Ａ１以下「下向け」にＡＡＡＡＢＢＢＢ…を並べる
Ｂ１以下「下向け」に９８１５２１６１５２…を並べる
Ｃ１に
=IF(SUBTOTAL(9,B1)=1,B2,"")
と記入し，リスト下端までコピー貼り付ける

Ｅ１に「出現データ」，Ｆ１に「出現数」と記入する
Ｅ２に
=SMALL(C:C,SUM($F$1:F1,1))
Ｆ２に
=COUNTIF(C:C,E2)
とそれぞれ記入し，下向けにコピー貼り付ける

お礼

ありがとうございます。

試したところ、うまく行きました。
しかし、やはりフィルター機能を使っての仕様が良いとの事で
根本的な解決には行きませんでした・・・。