エクセルで直近の連続回数を求める

ある条件の結果、合格を1、不合格を0として記録し、直近で連続合格した回数を求めたいのですが、どのような関数を使うと良いでしょうか。

図では7回目まで終了していて、
【条件A】の場合、直近の結果が0なので連続回数は「0」。(返す値は0か空白)
【条件B】の場合、直近の結果が空白なので連続回数は「0」。(返す値は0か空白)
【条件C】の場合、連続回数は「2」。

各条件の結果には空白のセルも混在し、回数が上がると列を増やす予定で、その右端に連続回数を求める仕様です。出来れば、マクロを使わず関数で求めたいと思っています。

よろしくお願いします。

No.2 回答者： tom04 回答日時： 2014/09/14 18:05

こんにちは！

画像では最大7までの列に数値が入っていますので、
直近とは7回まで「試験」？という解釈です。

VBAでやれば簡単なのですが、関数での方法をご希望だというコトですので
一例です。
↓の画像のようにSheet2を作業用のSheetとして使用します。

まずSheet2のA1セルに
=IF(COUNT(Sheet1!A2:K100),MAX(IF(Sheet1!A2:K100<>"",COLUMN(A1:K1))))
これは配列数式ですのでCtrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のA1セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
（Sheet1の100行目まで対応できる数式とします）
これで実施回数の列番号（画像では7回までですので、列番号として「8」が表示されます）

次にSheet2のB2セル（Sheet1の「0」や「1」を入力するセルと同じセル番地に
=IF(OR(Sheet1!$A2="",COLUMN()>$A$1),"",IF(Sheet1!B2=1,A2+1,Sheet1!B2))
という数式を入れSheet1の回数と同じ列数・行数分だけフィルハンドルでコピー！

このデータを利用して
Sheet1のL2セルに
=IF(A2="","",IF(INDEX(Sheet2!2:2,,Sheet2!A$1)=0,"",INDEX(Sheet2!2:2,,Sheet2!A$1)))
という数式を入れフィルハンドルで下へコピー！

これで画像のような感じになります。m(_ _)m

No.1 回答者： mike_g 回答日時： 2014/09/13 16:15

貴方の説明では「直近」はＨ列ということのようだけど、一般的には「直近」の列はどうやって判断するのですか？

「直近」がＩ列なら、全ての「条件」とも「返す値は0か空白」になりますよね。