Excel 最長連続日数を求める6つの方法【配列操作】

はじめに：あなたの自己ベスト、何日間ですか？

日々の目標達成を記録するとき、カウンターが日に日に増えていくのを見るのは、大きなモチベーションになります。

でも、途中でカウンターがリセットされてしまった時、こう思いませんか？

「今までの最高記録って、何日間だったんだろう？」

そう、今回私たちが挑むのは、配列操作の中でも難関との呼び声も高い、「最長連続部分列の長さを求める」問題です！

つまり、○×が記録されたリスト全体の中から、「最も長く続いた○の記録（最大連続日数）」を、数式だけで探し出すのです。

作業列を使った分かりやすい方法から、文字列操作やLAMBDA関数、そしてExcel 4.0の時代から存在する超古典的な関数だけで解き明かす見事なテクニックまで、合計6つのアプローチでこの難攻不落の城に挑みます！

この冒険が終わる頃には、あなたの配列操作スキルが、間違いなく新たな次元へと到達していることでしょう！

本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！

準備：戦場と武器を整える

まずは準備から。禁酒記録のリストを用意しましょう。

A1:B20の範囲に、日付と○×の記録を入力します。B列のリストには、空白のセルがない前提で進めますね！

※表が必要な方は、以下の数式をA1セルに貼り付けてくださいね！

={"日付","禁酒";45931,"○";45932,"○";45933,"○";45934,"×";45935,"○";45936,"○";45937,"×";45938,"○";45939,"○";45940,"○";45941,"○";45942,"○";45943,"×";45944,"×";45945,"○";45946,"○";45947,"×";45948,"○";45949,"×"}

次に、数式を分かりやすくするために、B2:B20の範囲に「リスト」という名前をつけましょう。Web版Excelでは、「数式」タブの「ネームマネージャー」から定義できます。

私たちのゴールは、このリストの中から、最も長く続いた○の連続日数である「5」を、様々な数式で導き出すことです。

6つのアプローチで、最難関を攻略せよ！

アプローチ1：作業列 + MAX（王道にして原点）

考えかた

まずは、最も直感的で分かりやすい方法です。

「連続日数カウンター」を作業列として作成し、その中から単純に最大値（MAX）を見つけます。

以下の記事で、連続日数をカウントする方法を紹介しています。

数式と解説

1. まず、C2セルに以下の数式を入力し、C20セルまでフィルコピーします。（C1セルは空白にしておきます）

=(C1+1)*(B2="○")

これで、C列に各時点での連続日数が計算されますね。

これは、「一つ上のセルの値+1」に、「その行が○なら1、×なら0」を掛けることで、リセットとカウントアップを同時に行うロジックです。

2. 最後に、D2セルに以下の数式を入力します。

=MAX(C2:C20)

MAX関数が、作業列の中から最も大きい数値「5」を探し出し、答えを返してくれます。シンプルで確実な方法です。

…でも、作業列をわざわざ作るのって、なんだか芸術性に欠けると思いませんか？

ここからは、作業列を一切使わず、一つの数式でこの難題を解決する、真の関数パズルの世界です！

アプローチ2：SCAN + MAX（最もエレガントな解）

考えかた

アプローチ1の考え方を、一つのセルで完結させる方法です。

「前の計算結果を使いながら順番に値を積み上げる」処理が得意な、最新関数SCANの出番です。

数式と解説

=MAX(SCAN(0,リスト,LAMBDA(a,b,IF(b="×",0,a+1))))

・SCAN(0,リスト,LAMBDA(a,b,IF(b="×",0,a+1))): この部分は、連続日数のリストをスピルで一気に生成する数式です。

SCAN関数が、リストを一行ずつ処理し、{1;2;3;0;1;2;0;...} という、アプローチ1の作業列と全く同じ配列をメモリ上に作り出します。

・MAX(...): 最後に、MAX関数が、SCANが生み出したこの配列の中から、最も大きい値「5」を見つけ出します。

作業列を数式の中に内包したような、非常にスマートな数式ですね！

アプローチ3：TEXTSPLIT + LEN（文字列操作の閃き）

考えかた

発想を180度変えましょう！

もし、○×のリストが「○○○×○○×○○○○○…」という、ながーい一本の文字列だったら？

「×」を区切り文字として文字列をバラバラに分解し、残った「○」の塊の長さを比べれば、答えが分かりそうですよね？

数式と解説

=MAX(LEN(TEXTSPLIT(TEXTJOIN("",TRUE,リスト),"×")))

この数式、内側から見ていくと、まるで魔法のようです。

1. TEXTJOIN("",TRUE,リスト): まず、TEXTJOIN関数で、「リスト」範囲の○と×を、区切り文字なしで連結し、"○○○×○○×..."という一つの巨大な文字列を作ります。

2. TEXTSPLIT(...,"×"): 次に、TEXTSPLIT関数が、この巨大な文字列を「×」を基準に分割します。結果として、{"○○○", "○○", "○○○○○", ...} という、「○」の連続部分だけが並んだ文字列の配列が生まれます。

3. LEN(...): この配列の各要素に対して、LEN関数で文字数（つまり連続日数）を計算します。{3, 2, 5, ...} という数値の配列に変わりますね。

4. MAX(...): 最後に、MAX関数が、この長さの配列の中から最大値「5」を見つけ出します。数値計算ではなく、文字列操作で解いてしまう、見事なアプローチです！

アプローチ4：LET + VSTACK + DROP（配列引き算の技巧）

考えかた

これは、非常に数学的なアプローチです。

「×」が登場する「位置」をすべてリストアップし、その位置と位置の「間隔」を計算することで、○の連続回数を求めます。

数式と解説

=LET(
失敗した位置,VSTACK(0,FILTER(SEQUENCE(ROWS(リスト)),リスト="×"),ROWS(リスト)+1), 連続日数,DROP(失敗した位置,1)-DROP(失敗した位置,-1)-1,
MAX(連続日数)
)

LET関数で、処理を分かりやすく分割しています。

・失敗した位置: まず、FILTER(SEQUENCE(ROWS(リスト)),リスト="×")で、「×」が登場する行番号（相対位置）のリスト {4;7;13;...} を作ります。

そして、VSTACK関数を使って、そのリストの先頭に「0」（スタート地点）、末尾に「20」（ゴール地点）を追加し、{0;4;7;13;...;20}という「境界線」のリストを完成させます。

Excelの説明画像

・連続日数: ここが最大のトリック！DROP(失敗した位置,1)で先頭の0を除いたリストと、DROP(失敗した位置,-1)で末尾の20を除いたリストを作り、それらを引き算します。

{4-0; 7-4; 13-7; ...} という計算で、各「×」間の距離が求まります。最後に「-1」することで、間の「○」の個数が計算できます。

・MAX(連続日数): 最後に、計算された連続日数のリストから最大値を探します。

アプローチ5：超絶技巧の文字列操作

考えかた

これは、もはや実用性を度外視した、文字列操作の限界に挑むような数式です。

アプローチ3と考え方は似ていますが、TEXTSPLITを使わずに、SUBSTITUTEとMID関数で無理やり文字列を分割します。

数式と解説

=MAX(LEN(TRIM(MID(SUBSTITUTE(TEXTJOIN("",TRUE,リスト),"×",REPT(" ",ROWS(リスト))),(ROW(INDIRECT("A1:A"&ROWS(リスト)))-1)*ROWS(リスト)+1,ROWS(リスト)))))

この長い数式、内側からゆっくり解き明かしていきましょう。

1. TEXTJOIN("",TRUE,リスト): まず、○×のリストを一本の文字列 "○○○×○○..." にします。

2. SUBSTITUTE(...,"×",REPT(" ",ROWS(リスト))): 次に、文字列の中の「×」を、大量のスペースに置き換えます。

ROWS(リスト)はリストの行数（今回は19）を返すので、「×」が19個のスペースに変わります。これで、○の塊がスペースで区切られた状態になります。

3. MID(..., (ROW(...)-1)*19+1,19): ここが最大の力技です。MID関数を使って、この長い文字列を、19文字ごと（リストの行数ごと）に強制的に分割していきます。

(ROW(...)-1)*19+1の部分が、1文字目、20文字目、39文字目…というように、分割の開始位置を計算しています。

4. TRIM(...): 分割された文字列の断片には、前後に余分なスペースがたくさんついています。これをTRIM関数で綺麗に取り除きます。すると、{"○○○", "○○", "○○○○○", ...} という、○の塊だけが残ります。

5. LEN(...): この配列の各要素の長さをLEN関数で数えます。

6. MAX(...): 最後に、その中の最大値を見つけ出します。まさに「暇つぶし」の極みですね！

アプローチ6：FREQUENCY – 古の関数の輝き

考えかた

さあ、いよいよ本日のメインディッシュです！

最新の動的配列関数など存在しなかった、遥か昔のExcelでもこの問題を解くことができた、魔法のような数式を紹介します。

主役は、統計関数であるFREQUENCYです。

以下の記事で、FREQUENCY関数を紹介していますよ！

「この記事を書きたいと思ったのは、この数式の美しさを伝えたかったから、と言っても過言ではありません！」

数式と解説（前半）

=MAX(FREQUENCY(
IF(リスト="○",ROW(INDIRECT("A1:A"&ROWS(リスト)))),
IF(リスト<>"○",ROW(INDIRECT("A1:A"&ROWS(リスト))))
))

この数式、実はExcel 4.0（1992年発売！）の時点で存在する、非常に古い関数だけで構成されています。Windows 3.1が発売した頃ですね！

それだけ大昔から、このロジックは存在していたのです。

FREQUENCY関数は、通常、FREQUENCY(データ配列, 区間配列) のように使い、データがどの区間にいくつ入っているか（度数分布）を集計する関数です。これを、驚くべき方法で応用します。

1. データ配列: IF(リスト="○",ROW(INDIRECT("A1:A"&ROWS(リスト))))
この部分で、「○」が登場する行の行番号（1, 2, 3, 5, 6, 8…）だけを抜き出した配列を作ります。これが、度数分布の元となる「データ」です。

2. 区間配列: IF(リスト<>"○",ROW(INDIRECT("A1:A"&ROWS(リスト))))
そしてこちらが最大のキモ！「×」が登場する行の行番号（4, 7, 13…）だけを抜き出した配列を作ります。これが、度数を区切るための「区間」になります。

数式と解説（後半）

3. FREQUENCY(…): FREQUENCY関数は、データ配列（○の位置）を、区間配列（×の位置）で区切って、各区間にデータがいくつあるかを数えます。

つまり、「最初の×までに○は何個？」「次の×までに○は何個？」…という計算を、一発で実行してくれるのです！

より具体的に：
4以下の数値は3つ
4より大きく7以下の数値は2つ
7より大きく13以下の数値は5つ
13より大きく14以下の数値は0
14より大きく17以下の数値は2つ
17より大きく19以下の数値は1つ
19より大きい数値は0

結果として、{3;2;5;0;2;1;0}という、各連続○の長さの配列が返されます。

4. MAX(…): 最後に、この配列の最大値を取れば、答えの「5」が導き出されます。見事なアプローチですよね！

ちなみに、ROW(INDIRECT(...))の部分は、現代のExcelならSEQUENCE(ROWS(リスト))と書くことで、少しだけ短くできますよ。
=MAX(FREQUENCY(
IF(リスト="○",SEQUENCE(ROWS(リスト))),
IF(リスト<>"○",SEQUENCE(ROWS(リスト)))
))