はじめに：三日坊主よ、さようなら！
「よし、今日から禁酒するぞ！」「毎日筋トレを続けるぞ！」
そう心に誓ったものの、記録をつけるのが面倒で、いつの間にか挫折してしまった…。そんな経験、ありませんか？
もし、日々の頑張りを「○」「×」で記録するだけで、Excelが自動で「連続達成日数」を計算してくれたら、モチベーションがぐんと上がると思いませんか？
「え、そんな便利なカウンター、どうやって作るの？」
今回の記事は、まさにそのお悩みにお応えします！
「×」がついたら「0」にリセットされ、「○」が続くと1ずつ増えていく。
この「継続とリセット」のロジックは、禁酒や勉強、トレーニングの記録など、様々な場面で応用できる、脱初心者を目指す上での必須スキルです。
基本のIF関数から、関数好きの心をくすぐるマニアックな数式、そして最新のLAMBDA関数まで、合計6つものアプローチで、この問題に挑みます！
本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！
ステップ1：冒険の準備 – 記録表を用意しよう
何事も、まずは準備から。まっさらなシートに、以下のようなシンプルな記録表を用意しましょう。
A列に日付、B列にその日の結果を「○」か「×」で入力します。C1セルには「連続日数」と見出しを入れておきましょう。
ゴールは、C2セル以下に、B列の記録に応じた連続日数を数式で表示させることです。
Part 1：いにしえの技 – フィルコピーで一行ずつ計算する
まずは、Excelの古き良き時代から伝わる、数式を一行ずつコピーしていく方法です。
この方法は、処理の仕組みが分かりやすく、基本を理解するのに最適ですよ。
アプローチ1：IF + N（王道にして基本）
考えかた
ロジックは非常にシンプルです。
「もし、その日が『×』なら0にする。そうでなければ（『○』なら）、一つ上のセルの日数に1を足す」。
これを素直に数式にしてみましょう。
数式と解説
C2セルに、以下の数式を入力し、下までフィルコピーします。
=IF(B2="×",0,N(C1)+1)
・IF(B2="×", 0, ...): まず、IF関数が、B2セルが「×」かどうかを判定します。もし「×」なら、無条件で「0」を返してカウンターをリセットします。
・... , N(C1)+1): 「×」でなければ、こちらが実行されます。
C1+1としたいところですが、C1セルには「連続日数」という文字列が入っているため、このままではエラーになってしまいますよね？
ここで輝くのがN関数です！
N関数は、引数が数値ならその数値を、文字列なら「0」を返す、という面白い性質を持っています。
つまり、N(C1)と書くことで、見出しの文字列を安全に「0」に変換し、初日の計算を0+1=1と正しく行えるようにしてくれるのです！
2日目以降は、上のセル（C2, C3…）が数値なので、N(C2)はC2の数値をそのまま返してくれます。
アプローチ2：論理値の乗算（IF関数からの脱却）
考えかた
IF関数を使わずに、もっと“通”な書き方をしてみましょう。
Excelでは、計算式の中で論理値のTRUEが「1」、FALSEが「0」として扱われる性質があります。これを利用します。
数式と解説
C2セルに、以下の数式を入力し、フィルコピーします。
=(N(C1)+1)*(B2="○")
・(N(C1)+1): この部分は、常に「一つ上のセルの日数 + 1」を計算しています。つまり、リセットされなかった場合の「あるべき次の日数」を常に用意しています。
・(B2="○"): この部分は、B2セルが「○」ならTRUE(1)、「×」ならFALSE(0)を返します。
・... * ...: 最後に、この2つを掛け合わせます。
もしその日が「○」なら、「あるべき次の日数 × 1」となり、日数が正しく加算されます。
もし「×」なら、「あるべき次の日数 × 0」となり、結果は強制的に「0」にリセットされる、というわけです。エレガントですよね！
アプローチ3：LOOKUP（最終行検索の応用）
考えかた
ここまでは「一つ上のセル」を参照していましたが、この方法は全く違うアプローチを取ります。
「今日の日付から、一番最後に出てきた『×』の日付を引き算する」ことで、連続日数を計算します。高度な古典的テクニックですよ！
数式と解説
C2セルに、以下の数式を入力し、フィルコピーします。
=IFERROR(IF(B2="×",0,ROW(A1)-IFNA(LOOKUP(2,1/($B$2:B2="×"),ROW($B$2:B2)-ROW($B$2)+1),0)),1)
・IF(B2="×",0,...): まず、その日が「×」なら0、これは共通ですね。
・LOOKUP(...): ここが心臓部。「今日までの中で、最後に出てきた『×』が何日目か」を探しています。
・($B$2:B2="×"): B列を上から自分まで見て、「×」の場所だけがTRUEになる配列を作ります。
・1/...: このTRUE/FALSEの配列で1を割ります。1/TRUEは「1」、1/FALSEはエラー「#DIV/0!」になります。
・LOOKUP(2, {エラー,エラー,1,...}, ROW($B$2:B2)-ROW($B$2)+1): LOOKUP関数で、このエラーと1が混在する配列から「2」を探します。
LOOKUPは、検索値（2）より大きい値がない場合、エラーを無視して配列の中の最後の数値（今回の場合は1）を見つけてくれます！
そして、その位置に対応する「相対的な行番号」を返します。
・IFNA(...,0): まだ一度も「×」が登場していない場合、LOOKUPはエラー(#N/A)を返します。IFNA関数がそれをキャッチし、代わりに「0」を返してくれます。
・ROW(A1)-...: 「今日が何日目か」から「最後の『×』が何日目か」を引き算して、連続日数を計算します。
・IFERROR(...,1): 最後に、数式全体をIFERRORで囲っています。これは、最初の行（B2）が「○」の場合など、エラーに対する処理です。
もしエラーが出た場合でも、最初の1日目として「1」を正しく表示させるための、より堅牢な記述になっています。
アプローチ4：ROW & MAX（配列計算）
考えかた
アプローチ3と発想は似ていますが、最後の「×」の位置を、MAX関数を使った配列計算で特定する方法です。
数式と解説
C2セルに、以下の数式を入力し、フィルコピーします。
=IF(B2="×",0,SUM((ROW(B$2:B2)>MAX((B$2:B2="×")*(ROW(B$2:B2))))*(B$2:B2="○")))
・MAX((B$2:B2="×")*(ROW(B$2:B2))): この部分で、今日までの範囲で最後に出てきた「×」の「絶対行番号」を特定しています。
(B$2:B2="×")で「×」の場所が1になる配列を作り、それに行番号を掛け合わせ、その最大値（MAX）を取る、というロジックです。
・ROW(B$2:B2) > ...: 「行番号が、最後の『×』の行番号よりも大きい」という条件で、最後の「×」以降の行だけがTRUEになる配列を作ります。
・... * (B$2:B2="○"): さらに、その中で「○」である行だけをTRUEとして絞り込みます。
・SUM(...): 最後に、このTRUE(1)の配列を合計することで、「最後の『×』以降にある『○』の個数」＝連続日数が計算できる、というわけです。
Part 2：現代の魔法 – スピルで一気に計算する
さて、ここからが本番です！
現代のExcelが誇る「スピル」機能と、LAMBDA関数ファミリーを使って、一つの数式でこの問題を一気に解決します。
アプローチ5：SCAN + LAMBDA（最もエレガントな解）
考えかた
今回の問題のように、「前の計算結果を使いながら、順番に値を積み上げていく」処理は、最新関数SCANの最も得意とするところです。
これ以上に美しく、分かりやすい解はないかもしれません。
数式と解説
C2セルに、以下の数式を一つ入力するだけです。
=SCAN(0,B2:B20,LAMBDA(a,b,IF(b="×",0,a+1)))
・SCAN(0, B2:B20, ...): SCAN関数は、「初期値0」からスタートして、「B2:B20」の範囲を上から一つずつ順番に処理していきます。
・LAMBDA(a,b,...): ここが、各行で実行される計算ルールです。
・a: 「累積値（accumulator）」と呼ばれ、前の行の計算結果が自動で入ります。最初の行では「初期値0」が入ります。
・b: 「B2:B20」の中から、今まさに処理しているセルの値（”○”か”×”）が入ります。
・IF(b="×",0,a+1): 計算ルールは驚くほどシンプル。
「もし、今のセルの値(b)が『×』なら、結果を0にリセットしなさい。そうでなければ、前の行の結果(a)に1を足しなさい」。
まさに、やりたかったことそのものですよね！
SCAN関数は、この計算を各行で行い、その途中経過をすべて配列として返してくれるため、一瞬で連続日数のリストが完成するのです。
アプローチ6：MAP + XMATCH + LAMBDA（究極の関数パズル）
考えかた
最後は、究極の関数パズルです。SCAN関数のように前の結果を使わず、各行が「独立して」自分の連続日数を計算する方法です。
アプローチ3のLOOKUPの考え方を、最新関数で再現します。
数式と解説
C2セルに、以下の数式を一つ入力します。
=MAP(SEQUENCE(ROWS(B2:B20)),LAMBDA(i,LET(現在の値,INDEX(B2:B20,i), 現在までの範囲,CHOOSEROWS(B2:B20,SEQUENCE(i)), 最後のバツの位置,IFNA(XMATCH("×",現在までの範囲,0,-1),0), IF(現在の値="×",0,i-最後のバツの位置))))
・MAP(SEQUENCE(ROWS(B2:B20)), ...): MAP関数が、1から19までの連番（行インデックス）を、LAMBDA関数に一つずつ渡して処理していきます。
・LAMBDA(i, LET(...)): 「i」が現在の行インデックス（1, 2, 3…）です。
LET関数で、計算を分かりやすく分割します。今回は名前に日本語を使ってみました！
・現在の値: 現在処理している行の値（”○”か”×”）を取得します。
・現在までの範囲: 1行目から現在の行(i)までの範囲を動的に作り出します。
・最後のバツの位置: ここで輝くのが最新の検索関数XMATCHです！
XMATCH("×",現在までの範囲,0,-1)は、範囲の中から「×」を「末尾から逆順に(-1)」探します。
これにより、「今日までの中で、最後に出てきた『×』が何番目か」を一発で特定できます。MATCH関数にはない、この逆順検索が非常に強力なのです！
・IF(現在の値="×",0,i-最後のバツの位置): 最後に、もし今日が「×」なら0を、そうでなければ「今日の行インデックス(i)」から「最後の『×』の行インデックス(最後のバツの位置)」を引き算して、連続日数を計算します。
まとめ：あなたのスキルも、連続成長！
たった一つのシンプルなゴールに対して、これほどまでに多様なアプローチが存在することに、驚かれたのではないでしょうか。
実務でサッと作るなら、アプローチ1やアプローチ5がおすすめです。
しかし、他の関数パズルたちも、Excelの関数の挙動や配列計算の仕組みを、より深く理解するための最高のトレーニングになります。
今回学んだ「継続とリセット」のロジックは、あなたのアイデア次第で無限に応用が可能です。
この冒険が、皆さんのExcelスキルを連続成長させるきっかけになれば、これほど嬉しいことはありません。
