Q&A
やりたいこと
Googleスプレッドシートを使っています。
通常のCOUNTIF関数はセル単位で条件を指定するのものかと思いますが、
複数セルのセット単位で条件を指定したいと考えています。
（この時点で説明が難しく、うまく表現できず申し訳ございません）
具体的には、以下のサンプルファイルのシート2を見ていただきたいのですが、
（以下の文章での説明に限界があるため）
https://docs.google.com/spreadsheets/d/1LhiR3GDR4tqNt6JMHw1CWJUwc4eLdHbi9h3Bm2TsJLQ/edit?usp=sharing
###▼サンプルI2:J2（どちらかの組が4連勝以上した後に、もう片方の組が4連勝以上したかどうか）
D3:G64の中で、
(1)E列を縦方向に見て、値が4以上のセルの下のセルの値が0だったとき
(2)上記E列の値が0だったセルの行から下のG列を見て値が4以上のセルの下のセルの値が0だったとき
（もしくはその反対=（1）でG列をベースに検索し（2）でE列をベースで検索）
これら2つの条件すべてに何回当てはまったケースがあったかを
カウントしたいと思っています。
（当てはまった回数をJ2に出力したい）
サンプルファイルではI2メモに記載の部分をカウントして
J2には2と出力したい。
###▼サンプルI3:J3（3連続以上の交互の単勝（連勝ではない）が何回出たか）
また、D3:G64の中で、
(1)E列を縦方向に見て、値が1のセルの下のセルの値が0だったとき
(2)上記E列の値が0だったセルの行から下のG列をみて値が1のセルの下のセルの値が0だったとき
(3)上記G列の値が0だったセルの行から下のE列をみて値が1のセルの下のセルの値が0だったとき
(4)以下、2連勝以上が出たら終了
（もしくはその反対）
これら4つの条件すべてに何回当てはまったケースがあったかを
カウントしたいと思っています。
（当てはまった回数をJ3に出力したい）
サンプルファイルではI3メモに記載の部分をカウントして
J3には4と出力したい。
###▼サンプルI4:J4（4連続の交互の単勝（連勝ではない）が何回出たか）
また、D3:G64の中で、
(1)E列を縦方向に見て、値が1のセルの下のセルの値が0だったとき
(2)上記E列の値が0だったセルの行から下のG列をみて値が1のセルの下のセルの値が0だったとき
(3)上記G列の値が0だったセルの行から下のE列をみて値が1のセルの下のセルの値が0だったとき
(4)上記E列の値が0だったセルの行から下のG列をみて値が1のセルの下のセルの値が0だったとき
（もしくはその反対）
これら4つの条件すべてに何回当てはまったケースがあったかを
カウントしたいと思っています。
（当てはまった回数をJ4に出力したい）
サンプルファイルではI4メモに記載の部分をカウントして
J4には3と出力したい。
##これら3つの計算方法について
技術的に可能なものでしょうか？
私の知識ではどのような関数を用いたらいいかが検討が付きません。
もし技術的に可能なのであれば
お知恵をお貸しいただければ幸いです。
よろしくお願いいたします。
回答1件
0
ベストアンサー
ルールが複雑なものは作業用の列・行を作って整理することで、ルールをわかりやすくすることが出来ます。
今回のケースもこのまま一足飛びにカウントは出来ないので、紅、白それぞれの連勝数をカウントしているE列、G列の値を使った作業列を作って整理すると良いです。
とはいえ、どういう作業列をつくってどう集計すればいいのかわからないと思うので、回答例を記載しておきます。細かい検証はしてないので、間違いがあるかもしれない点と、あくまでも1つの手法であって、COUNTIFなど他の関数を使って実現する方法もあるという点をご承知おきください。
最終的に作業列を式内で処理できて、比較的短い式となるものを作りました。
今回のケースだと文字列として処理した方が楽そうってとこがポイントでしょうか。
正規表現のところは検索すれば解説サイトが色々あります。
▼サンプルI2:J2（どちらかの組が4連勝以上した後に、もう片方の組が4連勝以上したかどうか）
・作業列で 紅の4連勝を 1、白の4連勝を 2 と置き換え、他は全て消去
・JOINで文字列化 → 1221
・同じチームの4連勝は無視していいので、 1の繰り返しや 2の繰り返し を置き換え → 121
・最初の4連勝はカウントしないので、文字数-1 としたものが求める数値 2
=LEN(REGEXREPLACE(REGEXREPLACE(JOIN(,ARRAYFORMULA(IF(E3:E70=4,1,IF(G3:G70=4,2,)))),"1+","1"),"2+","2"))-1
▼サンプルI3:J3（3連続以上の交互の単勝（連勝ではない）が何回出たか）
・作業列で 紅の連連勝と白の連勝数を合算
・JOINで文字列化 → 1234512345671111121234123411111211111231111...
・1が3つ以上繰り返される箇所を A に置き換え → 123451234567A12341234AA3A300...
・数字の部分を全て消去 → AAAA
・Aの数(残ってる文字の数) が求める数値 4
=LEN(REGEXREPLACE(REGEXREPLACE(JOIN(,ARRAYFORMULA(E3:E70+G3:G70)),"111+[02]","A"),"\d",))
▼サンプルI4:J4（4連続の交互の単勝（連勝ではない）が何回出たか）
・作業列で 紅の連連勝と白の連勝数を合算
・JOINで文字列化 → 1234512345671111121234123411111211111231111...
・11110 と111112を A に置き換え → 123451234567A12341234AA3111A3000...
・1の繰り返し+A の箇所は 5連続以上の交互の単勝なので消去 → 123451234567A12341234AA330..
・数字の部分を全て消去 → AAA
・Aの数(残ってる文字の数) が求める数値 3
=LEN(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(JOIN(,ARRAYFORMULA(E3:E70+G3:G70)),"1111[01][02]","A"),"1+A",),"\d",))
試合数が何千、何万となると動きが悪くなりそうですが。あとは、必ず勝敗が付く（引き分け等はなし）という条件が必要です。
投稿2020/07/16 01:40
編集2020/07/16 23:57
総合スコア3002
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2020/07/16 16:36
2020/07/17 00:07 編集
2020/07/17 01:45