エクセル2010 同じ数字や文字が連続する数をカウントするには？

エクセル2010使用です。
とある表の列（A列とします）にデータが入っています。
とりあえず10個のデータがあったとします

例
りんご
みかん
みかん
みかん
りんご
りんご
パイナップル
りんご
みかん
みかん

さて、このデータのうち
りんご、が連続して出現した場合のその連続回数の最大値、平均値、最小値を計算して、表の下に表示したいです。
同様にみかん、パイナップルの連続登場回数の最大値、平均値、最小値も表示させたいです。

補足：データは16バイト文字列、すなわち、全角漢字、全角平仮名、全角カタカナとします。
8バイト文字列、すなわち、半角英数文字や数値データは登場しないものとします。
登場するデータの種類は限定されているので、「未知のデータが登場することはない」との前提で結構です。
算出結果を表示させるセルは、データごとにそれぞれ別のセルで構いません。
あくまでも「連続している場合の連続回数の最大値、平均値、最小値」です。「データの個数」ではありません。

以上をセル関数で計算、表示する方法を教えてください。

No.4 ベストアンサー 回答者： tom04 回答日時： 2018/01/31 17:07

こんにちは！

安直な方法ですが・・・
↓の画像のように作業用の列を3列設けてみました。
作業列1はＡ列を重複なしに表示するため、作業列2・作業列3は最大値・最小値・平均値を求めるためです。

作業列1のB2セルに
=IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")

C2セルに
=IF(A3<>A2,ROW()-1,"")

D2セルに
=IF(C2="","",C2-MAX(C$1:C1))

という数式を入れそれぞれこれ以上データはない！という位まで下へコピーしておきます。

F2セルに
=IFERROR(INDEX(A:A,SMALL(B:B,ROW(A1))),"")

G2セルに
=IF(F2="","",MAX(IF(A$2:A$1000=F2,D$2:D$1000)))

G2セルは配列数式なのでCtrl＋Shift＋Enterで確定！（←必須★）

H2セルに
=IF(F2="","",MIN(IF(A$1:A$1000=F2,D$1:D$1000)))

こちらも配列数式なのでCtrl＋Shift＋Enterで確定！（★）

I2セル（配列数式ではありません）に
=IF(F2="","",AVERAGEIF(A:A,F2,D:D))

という数式を入れ、F2～I2セルを範囲指定　→　I2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　作業列が目障りであれば、どこか遠く離れた列にするか
非表示にしてください。m(_ _)m

この回答へのお礼

ご回答ありがとうございます。

お礼日時：2018/02/01 17:34

No.3 回答者： fakeflake 回答日時： 2018/01/31 11:18

1つの関数式でそれをするのは無理があると思います。

作業セルを使うと簡単にできますが。

果物の名前が入ってるのがA列の1行目からだとすると、

B列で「りんご」だけを数える
C列で「みかん」だけを数える

みたいに専用にカウントするセル（作業セル）を作ります。で、後でこれを集計するという方法です。集計の経過を印刷したくないとか見せたくないって場合は、作業セルを非表示にするか、印刷範囲外に作ります。

りんごを数える作業セルの式はこんな感じで。わざとaをくっつけて文字列にしているので複雑に見えますが、これはカウントしてる途中経過を数値にせず、結果だけを数値にしたいためです。

セルB1（数え始め）
=IF($A1="りんご","1a","")

セルB2（それ以降はオートフィル）
=IF($A2="りんご",IF(RIGHT(B1,1)<>"a","1a",MID(B1,1,LEN(B1)-1)+1&"a"),IF(B1="","",MID(B1,1,LEN(B1)-1)))

こうすればB列の最大値と最小値が、りんごの最大最小連続登場回数になります。ところで、この式だと答えとして現れうる最小値は1（連続データがない）ですが、それでいいですか？

1だった場合には答えとして出さないようにすることは、最後のIF関数をいじるだけで可能です。でもそうすると、今回の例のパイナップルみたいに1度も連続して登場しないものがデータ中に含まれていたら、値が取り出せないことになります。

この回答へのお礼

ご回答ありがとうございます。

お礼日時：2018/02/01 17:34

No.2 回答者： 銀鱗 回答日時： 2018/01/31 10:36

(´・ω・`)？

いきなり一つの数式で解決しようと考えていませんか？
こういう問題は、一つずつ手順を追って処理することで解決できますよ。

紙の上で処理する時、どんな手順で作業をするかを考えてみましょう。
連続する最大値を求める手順なら、
一番上のデータを比較する対象がないので、二つ目のデータから ”比較” を行いますよね。
（質問の例では一つ目のデータ「りんご」は比較せず、二つ目のデータ「みかん」から）
二つ目のデータは一つ目のデータと同じならその横に２と書き、違えば1と書く。（この場合は１）
三つ目のデータは二つ目のデータを比較して、同じなら二つ目のデータに対応する数字（この場合は１）に１を加えた値を横に書き、違えば１と書く。
これを最後のデータまで繰り返す。

次に
「りんご」の横にある数字で一番大きいものを選ぶ。
「みかん」の横にある数字で一番大きいものを選ぶ。
「パイナップル」の横にある数字で一番大きいものを選ぶ。

などじゃないかな。

・・・
集計したい項目が書かれた列の横に作業用の列を設けてみましょう。
そうしたうえで何が分からないのかを考え、その解決方法を探るようにすると
以後、同様の問題が現れたときに自力で対処できるようになります。
こういった考え方ができるようになったら、もっと高度な集計方法を考えてみましょう。
がんばれ。


・・・余談・・・
自分なら作業列まで作ったらピボットテーブルを作って処理する。

この回答へのお礼

ご回答ありがとうございます。

お礼日時：2018/02/01 17:34

No.1 回答者： GooUserラック 回答日時： 2018/01/31 10:31

通常の関数ではほぼ無理かと思われます。

マクロ（ＶＢＡ）を使用しても良いのならばどうにかなるかもしれません。
なお、連続していないものは除外しても良いのでしょうか？除外した場合はりんごの平均値は「２」になると思いますがそれで良いのでしょうか？

この回答へのお礼

ご回答ありがとうございます。

お礼日時：2018/02/01 17:34