スプレッドシートで縦に並ぶ連続データから条件に当てはまる組み合わせの個数等を計算したい

Question

前提・実現したいこと

Googleスプレッドシートを利用しています。

縦に並ぶ連続データから条件に当てはまる箇所の個数やその値を計算したいと考えています。

発生している問題・エラーメッセージ

以下スプレッドシートをご覧ください。
https://docs.google.com/spreadsheets/d/1AdTri_ojmK8lw5V7LANLTpNUXlbFgRG9OY9N_iHkxQs/edit?usp=sharing

A列に項目、B列に値が記入されています。

条件として、
D1の値未満の値がB列でD3の値以上連続して続いた回数をD5に出したいのですが、
D5に入れる計算式がわかりません。

また、条件に当てはまった箇所のA列の値を抽出したいのですが、
(上記スプレッドシート例ではDとM)
その抽出方法もわかりません。

縦に連続して並ぶデータの組み合わせを条件とした計算式を調べましたが、
全くわかりませんでした。

お知恵をお貸しいただければ幸いです。
よろしくお願いいたします。

Answer 1

もっとシンプルなやり方があるかもしれませんが・・・

１．式を入れるためにC,D列をインサートして開けておきます。
（条件を入れるセルはE列に移動させます）

２．「C2」セルに、数式「=if(B2<$E$1,C3+1,0)」を入力し、2行目から18行目まで下方向にコピーします。

３．「D2」セルに、数式「=if(and(C2>=$E$3,C1=0),1,0)」を入力し、2行目から18行目まで下方向にコピーします。

４．「E5」セル(=連続した数字が出現する回数）に、数式「=SUM(D2:D18)」("18"＝Qまでの行。実際の行数に合わせて変える)を入力します。

５．F8セルの下（F列の9行目以降）にもし数式や数字が入っている場合は、いったん削除します。（F列の9行目以降に数式や数字が入っていると、６で入れる数式がうまく動かないため。

６．「F8」セル（「当てはまる項目」の最初のセル）に、数式「=if(E5>0,filter(A2:A18,D2:D18=1),"")」を入力します。（この数式は配列を返すので、数式を下方向にコピーする必要はありません。）

Comments

[kikuchi33]

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

早速教えていただいた通りに数式を当てはめたところ
こちらが理想とする値が表示されております！

なるほど、C列で連続した回数を出し、
D列で条件に当てはまるときだけフラグを立てるイメージでしょうか。

そしてF列ではD列のフラグの立っている行をフィルターで出すわけですね。

このようにすれば連続した上下の行の値の組み合わせを条件にして
解が出せるのですね。

数式だけでなく詳しい解説もつけていただいたおかげで、
こういったケースでの数式を作るための考え方が知識として身につきました。

今後に活かせそうです。

貴重なお知恵をお貸しいただき、また丁寧なご回答をいただき、
誠にありがとうございました。