エクセルで黒と赤が交互に出た連続回数を数えたい

質問

はじめまして、エクセル初心者です。
どうぞよろしくお願いします。

1　 赤
2　 赤
3　 黒
4　 赤
5　 0
6　 黒
7　 赤
8　 黒
9　 赤
10 黒
11 0
12 黒
13 　赤
14 　赤
15 　0
16 　0

上記のように赤と黒、そしてたまに数字の”0”が、C列のセルに並んでいるとします。

その中から、交互になった（赤と黒）あるいは（黒と赤）を1回とし、その後さらに交互に出た連続回数をカウントしたいのです。

カウントしたものは別セルに、1連続が何回、2連続が何回・・・というように統計を出したいです。
途中ある”0”は、単体でも連続していても、それぞれ1個づつ数をカウントしたいです。

上記ですと・・

行の234が（赤黒赤）で「2」
同じように、行6～10は（黒赤黒赤黒）で「4」
行12、13は（黒赤）で「1」

”0”は4個あるので「4」

最終的には赤と黒と”0”の総合計数から、交互に出た連続回数別の割合を出すのが目的です。

説明が下手でわかりにくいかもしれませんが、どうかよろしくお願いします。

追伸：本当は、赤もしくは黒の連続した回数の統計も取りたいのですが、これは他に参考になる
　　　　回答があったように思いますので、頑張ってみます。

回答No.5

MarcoRossiItaly

＞● D 列には、どんな数式を入力していますか？あるいは、「＋」、「－」、「00」という文字を直接タイプしているだけですか？
　D列はA-Bの結果の（C列）を、マイナス、プラス、プラスマイナスゼロと分けたものです。

結局、数式が入っているのですかね、それとも文字列のみですかね？

「A-B」から「＋」などを表示しているとのご説明から推測すると、IF 関数あたりを使われているのかな？そうだとすると「プラスマイナスゼロ」は、どう表示されているのでしょうか？「±0」か、「±」か、「0」か…。

書式はいじっていないとのことですから、書式によって「±0」などを表示させているわけではないようですね。

例えば「±0」としているのであれば、No.4 の数式は次のとおり修正すれば、正しい回数が算出されます。

E2　=e1+(d1<>d2)*(d1<>"±0")*(d2<>"±0")

それから、「交互に出た連続回数「別」の割合」と質問文に書いてありましたね。すみません、見落としていました。その目的であれば、次の数式でも入力すれば、出ます。

F2　=sumproduct((d1<>d2)*(f1+1))
G2　=(f2>f3)*f2
I1　=sum(g2:g30)/sumproduct(0+(g2:g30>0))

＞あとは、NO１の
　=IF(C3="","",IF(OR(AND(C2&C3="黒赤"),AND(C2&C3="赤黒")),1,""))
　の、黒赤　の部分を　＋－、　赤黒の部分を　－＋　に変えればいいのですね？

そのご質問は、No.3 さんにお尋ねください。とりあえず IF 関数は 2 つ入れ子にして書かなくても、1 つで済みそうに見えます。AND の使われ方も、意図が不明です。何か錯覚されたのかな？

＞あと、セルの最下段までスクロールして見ていないのですが、……

Ctrl+↑、↓、←、→というショートカットキーを押すと、カーソルが高速移動します。移動経路の途中にデータがあったりすると、そこでカーソルがひっかかります。いろいろ動きを試してください。

補足

こんばんは。

皆さまがサクサクと回答下さいますのに、当の本人が遅くて申し訳ありません。

また、どの方がどの回答を下さったているのかよく分からなくてちんぷんかんぷんな返事でごめんなさい。

さて、D列は、どこかの見よう見まねで下記の関数を入れました。

=IF(C3<0,"－",IF(C3>0,"＋",IF(C3=0,0,"")))

結果、＋、－、0の3つがそれぞれ表示されてます。

まず、MarcoRossiItalyさまの式を一通りいれて今からやってみます。

回答No.4

MarcoRossiItaly

＞わたしの最初の例が悪く、うまくいきませんでした。
　……
　……、うまくいきませんでした。

繰り返しおっしゃっている「うまくいかない」とは何なのか、説明がありません。

あなたからの補足を待つのも時間かかるので、想像で話を進めます。
◆「00」というデータが文字列だと、No.1 の数式では正しい値にならない可能性があります。
◆あるいは「＋」、「－」、「00」というのが「それとは全く異なるセルの値に基づく書式」により表示されているだけの場合も、計算が狂うかもしれません。

【確認すべきこと】
● D 列には、どんな数式を入力していますか？あるいは、「＋」、「－」、「00」という文字を直接タイプしているだけですか？
● D 列のセルの書式は、何が指定されていますか？

＞何回かセルのローマ字を変えてトライしましたが、うまくいきませんでした。

「ローマ字」とは？セル番地のこととか？

上の◆に挙げた条件に該当しない場合は、No.1 の数式でたぶん正しく計算できると思います。補足でお示しのデータが 1 行目から入力されているものとすると、No.1 の数式中の「b1」を「e1」に、各「a」を「d」に書き換えて、E2 セルに入力するだけのことです。

E2　=e1+(d1<>d2)*(d1<>0)*(d2<>0)

オートフィルすれば、E30 に、知りたい値である「11」が表示されているはずです。

補足

さっそくの回答ありがとうございました。

何回かセルのローマ字を変えてトライ…とはおっしゃるとおり、関数の中のセル番地（というのですか）のことです。
お手数かけました。

【確認すべきこと】について

● D 列には、どんな数式を入力していますか？あるいは、「＋」、「－」、「00」という文字を直接タイプしているだけですか？

D列はA-Bの結果の（C列）を、マイナス、プラス、プラスマイナスゼロと分けたものです。
したがって 00はありません。（０）がひっついていてすいません。

● D 列のセルの書式は、何が指定されていますか？

書式？ですか。とくに何もいじってません。

あとは、NO１の

=IF(C3="","",IF(OR(AND(C2&C3="黒赤"),AND(C2&C3="赤黒")),1,""))

の、黒赤　の部分を　＋－、　赤黒の部分を　－＋　に変えれば
いいのですね？

あと、セルの最下段までスクロールして見ていないのですが、
ローソク10分足の1年分データですので、おそらく3万くらいあると思います。

この辺は特に問題ないですよね。

とりあえず今晩トライしてみます。
たいへんお手間かけました。ありがとうございました。

回答No.3

KURUMITO

お示しのデータがＣ２セルから下方に入力されているとします。
作業列をＤおよびＥ列に作って対応します。
Ｄ3セルには次の式を入力して下方にドラッグコピーします。

=IF(C3="","",IF(OR(AND(C2&C3="黒赤"),AND(C2&C3="赤黒")),1,""))

Ｅ3セルには次の式を入力して下方にドラッグコピーします。

=IF(C3="","",IF(D3="",0,IF(AND(D3<>"",D4<>""),E2+0.01,E2*100+1)))

そこでお求めの答えですがＧ2セルからＩ8セルの範囲に表としてしまします。
Ｇ2セルには総合計数、Ｇ3セルには連続回数1回、Ｇ4セルには2回、Ｇ5セルには3回、Ｇ6セルには4回、・・と入力します。
Ｈ2セルには次の式を入力します。

=SUM(D:D)+COUNTIF(C:C,0)

Ｈ3セルには次の式を入力して下方にドラッグコピーします。

=IF(G3="","",COUNTIF(E:E,ROW(A1)))

Ｉ2セルには総合計数との割合と文字を入力します。

Ｉ3セルには次の式を入力して下方にドラッグコピーします。

=IF(H3="","",ROUND(H3/H$2,3))

難しい関数を使って処理するよりもできるだけ作業列を使って簡単な関数で処理することを考えてみるようにしてはどうでしょう。作業列が目障りでしたらそれらの列を選択してから右クリックして「非表示」を選択すればよいでしょう。

補足

皆さま、本当にありがとうございます。

わたしの最初の例が悪く、うまくいきませんでした。

改めて、
下記が実際のものですが、セルDに＋－ゼロがあり交互にでる連続回数の統計をとりたいのです。

何回かセルのローマ字を変えてトライしましたが、うまくいきませんでした。

大変お手数かけますが、もう一度よろしくお願いします。

セルA　 セルB　 セルC　セルD

149.77 149.63 0.14 ＋
149.64 149.7 -0.06 －
149.69 149.73 -0.04 －
149.74 149.63 0.11 ＋
149.62 149.72 -0.1 －
149.73 149.64 0.09 ＋
149.63 149.47 0.16 ＋
149.48 149.46 0.02 ＋
149.47 149.51 -0.04 －
149.52 149.53 -0.01 －
149.54 149.57 -0.03 －
149.56 149.6 -0.04 －
149.61 149.51 0.1 ＋
149.52 149.49 0.03 ＋
149.48 149.45 0.03 ＋
149.46 149.38 0.08 ＋
149.39 149.15 0.24 ＋
149.16 149.1 0.06 ＋
149.11 149.26 -0.15 －
149.25 149.24 0.01 ＋
149.25 149.25 　　0 0
149.25 149.26 -0.01 －
149.25 149.2 0.05 ＋
149.19 149.18 0.01 ＋
149.17 149.22 -0.05 －
149.21 149.23 -0.02 －
149.24 149.32 -0.08 －
149.29 149.35 -0.06 －
149.34　149.44 -0.1 －
149.43 149.37 0.06 ＋

回答No.2

hallo-2007

一案です。
=SUMPRODUCT((A1:A16="黒")*1,(A2:A17="赤")*1)
で黒赤の連続回数
=SUMPRODUCT((A1:A16="赤")*1,(A2:A17="黒")*1)
で赤黒の連続回数
=SUMPRODUCT((A1:A16="黒")*1,(A2:A17="赤")*1)+SUMPRODUCT((A1:A16="赤")*1,(A2:A17="黒")*1)

黒赤、或いは赤黒の連続回数

お礼

お礼がたいへん遅くなりました。

ご回答ありがとうございました。
初心者ですので、今後ともよろしくお願いします。

回答No.1

MarcoRossiItaly

A1:A16 に色が入力されているなら

B2　=b1+(a1<>a2)*(a1<>0)*(a2<>0)

を入力し、下方向にオートフィル

お礼

お礼がたいへん遅くなりました。

ご回答ありがとうございました。
初心者ですので、今後ともよろしくお願いします。

補足

ここは連続で補足入力はできないようですね…

　A　　　　B　　　C　　　D
149.76 149.89 -0.13 －
149.89 149.92 -0.03 －
149.91 150.01 -0.1 －
150.02 150.05 -0.03 －
150.04 149.91 0.13 ＋
149.9 149.83 0.07 ＋
149.84 149.94 -0.1 －
149.93 149.75 0.18 ＋
149.74 149.73 0.01 ＋
149.72 149.74 -0.02 －
149.74 149.64 0.1 ＋
149.65 149.61 0.04 ＋
149.62 149.5 0.12 ＋
149.49 149.65 -0.16 －
149.64 149.6 0.04 ＋
149.61 149.78 -0.17 －
149.77 149.8 -0.03 －
149.79 149.76 0.03 ＋
149.77 149.63 0.14 ＋
149.64 149.7 -0.06 －
149.69 149.73 -0.04 －
149.74 149.63 0.11 ＋
149.62 149.72 -0.1 －
149.73 149.64 0.09 ＋
149.63 149.47 0.16 ＋
149.48 149.46 0.02 ＋
149.47 149.51 -0.04 －
149.52 149.53 -0.01 －
149.54 149.57 -0.03 －
149.56 149.6 -0.04 －
149.61 149.51 0.1 ＋
149.52 149.49 0.03 ＋
149.48 149.45 0.03 ＋
149.46 149.38 0.08 ＋
149.39 149.15 0.24 ＋
149.16 149.1 0.06 ＋
149.11 149.26 -0.15 －
149.25 149.24 0.01 ＋
149.25 149.25 0 0
149.25 149.26 -0.01 －
149.25 149.2 0.05 ＋

の状態で、E2に書式をいれオートフィルすると下記のようになりました。
わたし、根本的に間違っているのでしょうか

149.76 149.89 -0.13 － "
"
149.89 149.92 -0.03 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>0)
149.91 150.01 -0.1 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>1)
150.02 150.05 -0.03 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>2)
150.04 149.91 0.13 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>3)
149.9 149.83 0.07 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>4)
149.84 149.94 -0.1 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>5)
149.93 149.75 0.18 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>6)
149.74 149.73 0.01 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>7)
149.72 149.74 -0.02 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>8)
149.74 149.64 0.1 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>9)
149.65 149.61 0.04 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>10)
149.62 149.5 0.12 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>11)
149.49 149.65 -0.16 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>12)
149.64 149.6 0.04 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>13)
149.61 149.78 -0.17 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>14)
149.77 149.8 -0.03 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>15)
149.79 149.76 0.03 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>16)
149.77 149.63 0.14 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>17)
149.64 149.7 -0.06 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>18)
149.69 149.73 -0.04 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>19)
149.74 149.63 0.11 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>20)
149.62 149.72 -0.1 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>21)
149.73 149.64 0.09 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>22)
149.63 149.47 0.16 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>23)
149.48 149.46 0.02 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>24)
149.47 149.51 -0.04 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>25)
149.52 149.53 -0.01 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>26)
149.54 149.57 -0.03 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>27)
149.56 149.6 -0.04 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>28)
149.61 149.51 0.1 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>29)
149.52 149.49 0.03 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>30)
149.48 149.45 0.03 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>31)
149.46 149.38 0.08 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>32)
149.39 149.15 0.24 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>33)
149.16 149.1 0.06 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>34)
149.11 149.26 -0.15 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>35)
149.25 149.24 0.01 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>36)
149.25 149.25 0 0 E2　=e1+(d1<>d2)*(d1<>0)*(d2<>37)
149.25 149.26 -0.01 － E2　=e1+(d1<>d2)*(d1<>0)*(d2<>38)
149.25 149.2 0.05 ＋ E2　=e1+(d1<>d2)*(d1<>0)*(d2<>39)