スプレッドシートで記録している勝敗成績の連勝数ごとの回数をカウントしたい

Question

やりたいこと

Googleスプレッドシートで、
紅組・白組の勝敗記録を取っています。

行ごとに紅白どちらが勝ったか勝敗を記録して、
連勝したときはその連勝数も記録しています。
（詳しい仕様は後述）

紅白組それぞれで、
「2連勝止まりは●回」
「3連勝止まりは●回」
「4連勝止まりは●回」
という形で連勝数ごとにカウントをしたいと考えていますが、
具体的な実現方法が思いつきません。

実際の表ファイル

以下が実際のGoogleスプレッドシートファイルとなります。
https://docs.google.com/spreadsheets/d/1LhiR3GDR4tqNt6JMHw1CWJUwc4eLdHbi9h3Bm2TsJLQ/edit?usp=sharing

C列に勝った組の名前（紅または白）を入力し、
紅組が勝った行はD列に1、白組が勝った行はF列に1を入力、
紅組連勝数はE列に、白組連勝数はG列に自動で入るようにしています。

このB～G列にある表の成績（連勝数を取っているE列とG列の値）を元にして、
J2に紅組1勝止まり回数、
J3に紅組2連勝止まり回数、
J4に紅組3連勝止まり回数…
M2に白組1勝止まり回数、
M3に白組2連勝止まり回数、
M4に白組3連勝止まり回数…
を自動で入力させたいと思っています。

今回のサンプルファイルの例ですと、
紅組1勝止まり回数は1、
紅組2連勝止まり回数は0、
紅組3連勝止まり回数は2、
紅組4連勝止まり回数は2…
白組1勝止まり回数は0、
白組2連勝止まり回数は2、
白組3連勝止まり回数は0…
というような結果を自動で計算させたいです。

例えば、紅組2連勝止まりだった回数をカウントするために
E列にある「2」をカウントするだけだと
3連勝以上している最中の「2」もカウントされてしまうため適していません。

具体的な実現方法が全く思いつかず、
あまりないケースなのか検索しても参考になる情報がヒットしなかったため、
お知恵をお貸しいただければ幸いです。

よろしくお願いいたします。

Answer 1

E列の連勝数の3行目ですが、以下のようにするとエラーを回避できて、下にそのままコピペで動きます。
E3↓

=IFS(ROW(E3)=3,D3,AND(D3=1,E2>1),D3+E2,D3=1,D3+D2,D3="",0)

本題の方の連勝のカウントは「〇勝止まり回数」ということで、E列、G列の 連勝数の数が 〇 であり、かつ その次の試合は相手が勝っている（一つ下の行の 相手の連勝数が 0より大きい）というルールになるかと思います。

よって、参照を1つずらした形で以下のように COUNTIFSで式にしてあげればよいです。ついでに、I列の〇連勝の部分の数字を取得して数式に組み入れてあげると コピペでもいけますし、ArrayFormula も使えます。

J2↓

=COUNTIFS($E$3:$E$32,REGEXEXTRACT(I2,"[0-9]+"),$G$4:$G$33,">0")

別に1つずらして参照する範囲は相手の連勝数(G列)である必要はなく、D列が空欄やE列が1より小さいなどの条件でも良いです。

Comments

[kikuchi33]

お忙しいところお返事いただき、
誠にありがとうございました！

まずE3セルの件ですが、
ROW関数はこのように使うと良いのですね。

大変勉強になりました。
具体的なコードもありがとうございました。

次に連勝カウントの件ですが、
投稿いただいたコードを使用したところ、
無事に連勝数ごとの回数をカウントできそうです。

ありがとうございます。

I列の数字を取得して数式に組み入れるなんて考えもしませんでしたが
こういったやり方があるのですね。

参照を1つずらすというのも今回初めて知りました。
投稿いただいたコードを見ながら勉強させていただきます。

ただ、大変申し訳ございません。

このコードだと最後の試合で勝った組が連勝中だったときは
「連勝止まり」ではないのでカウントされませんが、
これをカウントさせることはできないでしょうか？

サンプルファイルで言えば、
最後は紅組が4連勝中で記録は止まっています。

連勝止まりでないのですが、
紅組4連勝止まり回数には2と入力させたいと考えています。

引き続きお知恵をお貸しいただければ幸いです。

よろしくお願いいたします。

[sawa]

>このコードだと最後の試合で勝った組が連勝中だったときは「連勝止まり」ではないのでカウントされません
確かに。最後の試合の次の試合はないので、相手チームの勝ちではないですから。確かに最後の部分は4連勝止まりとしてカウントされません。その場合は、回答の最後に書いてる
「1つずらして参照する範囲は相手の連勝数(G列)である必要はなく、D列が空欄」を使うことで希望の集計値になるかと思います。回答を差し替えてもいいのですが、まずは自ら式の修正を試してみることをお勧めします。

[kikuchi33]

お返事ありがとうございます。

なるほど、相手の連勝数列である必要はないとのことで、
再度試してみたところ「E列が1より小さい」という条件で
理想通りの動作になりました。

お忙しいところ丁寧にご回答いただき、
誠にありがとうございました。

この参照を1つずらすというのは、
今回投稿いただいたコードを実際に使ってみ動作することはわかったのですが、
理屈がよくわかりません。

どのようなキーワードで検索した解説記事で勉強すればよいのか、
もし可能であれば教えていただければ幸いです。

よろしくお願いいたします。

[sawa]

問題解決となり良かったです。最後の一歩を自分でやったことで、今後にいかせるものとなっていれば幸いです。

解説記事は、ぱっと思い浮かばないですし、私もうまく説明は出来ませんが、こういったCOUNTIFS等の関数の範囲部分はサイズ(今回でいえば行数)が揃ってればなんでもよくて、それらがセットで一つずつ下に動いて条件に合致しているかを判断する動きをしてるイメージです。

これを応用することで作業列なしで、式の中でバーチャルな表を作って関数を動かせるのがGoogleスプレッドシートのいいところかなと。

[kikuchi33]

ありがとうございます。
お陰様でこの経験を今後に活かせます。

解説についてもありがとうございました。
いろいろサンプルを作って身につけたいと思います。

この度は本当にありがとうございました。