数式だけでExcelにFORループを作る方法｜3つの実例でわかりやすく解説

Excel VBAは使いたくないけれど、数式だけでFORループのような繰り返し処理を実現したい——そう考えたことはありませんか？この記事では、数式のみを使ってFORループと同じ動作を実現する方法を具体的に紹介します。

Excel VBAでコードが書ける方は自由自在です。しかし、VBAを書いたことがない方や、ブックにマクロコードを含めたくない方は、シンプルなループ処理ひとつでも工夫が必要になります。そんなときこそ、発想の転換が役立ちます。

作業用ファイルのダウンロード

以下のリンクから、この記事で使用する作業用ファイルをダウンロードできます。

数式でExcelにFORループを作る3つの例

ここでは、数式を使ってExcelにFORループを作る3つの例を順番に解説します。それぞれ詳しく見ていきましょう。

例1：複数の関数を組み合わせてFORループを作る

まず、この例を思いついた背景からお話しします。筆者はUdemyでいくつかの講座を公開しており、そのうちの一つが「7つの実践問題で学ぶExcel条件付き書式」というExcel条件付き書式に関する講座です。[無料アクセスはこちら]

その講座のディスカッションボードに、ある受講生から次のような質問が寄せられました[スクリーンショット参照]。

Udemyの受講生からの質問。

上記の質問をよく読んで、一度自分で解いてみてください。

問題を解く手順：

ここでは、OR、OFFSET、MAX、MIN、ROW関数を組み合わせたExcel数式でFORループを作ります。

• まず、新しいワークブックを開き、上記の値を1つずつワークシートに入力します[セルC5から開始]。
• 次に、範囲全体[セルC5:C34]を選択します。
• 続いて、ホームタブ >> 条件付き書式をクリックします。
• 最後に、ドロップダウンから新しいルールを選択します。

すると、新しい書式ルールダイアログボックスが表示されます。

• ルールの種類を選択してください欄で「数式を使用して、書式設定するセルを決定」を選択します。
• 次に、「次の数式を満たす値を書式設定」フィールドに、次の数式を入力します。

• ダイアログボックス内の書式…ボタンをクリックし、適用する書式を選択します。

すると、セルの書式設定ダイアログボックスが表示されます。

• 塗りつぶしタブから任意の色を選びます。ここでは薄い青の背景色を選択しました。右側にはサンプルが即座に表示されます。なお、色はできるだけ淡い色を選ぶのがおすすめです。濃い色だと入力済みのデータが見えなくなり、フォントの色を変更しなければならなくなる可能性があるためです。
• その後、OKを押して書式を確定します。

• さらに、新しい書式ルールダイアログボックスでOKを押します。プレビュー欄でサンプルを確認できます。

最終的に、書式が適用された数字が表示されます。

次に、この問題を解くアルゴリズムを説明します。

• アルゴリズムを理解しやすくするため、基準となる2つのセルC11とC17を使って全体を説明します。セルC11とC17の値はそれぞれ10と20です（上図参照）。Excel数式に慣れている方なら、OFFSET関数の存在に気づくでしょう。OFFSET関数は基準点を扱う関数だからです。
• ここで、セル範囲C8:C11とC11:C14、そしてC14:C17とC17:C20の値を並べて比較してみましょう[下図参照]。基準セルはC11とC17で、基準セルを含めて合計7個のセルを取り出しています。イメージとしては次のようになります。前半部分ではC9–C12=3、C10-C13=3というパターンが見えます。しかし後半部分には、そのようなパターンはありません。

• このパターンを踏まえてアルゴリズムを構築しましょう。共通の数式を作る前に、まずセルC11とC17専用の数式を示し、その後すべてのセルに対応した汎用数式へと修正していきます。基準点（C11やC17など）に対して、その周囲のセルを含めた合計7個のセルを取り出し、数式内で配列として並べます。そして配列同士の差を求め、差が3に等しいものが一つでもあれば、その基準セルはTRUEと判定されます。
• これはOFFSET関数を使えば簡単に実現できます。OFFSET関数は配列を返すためです。たとえば基準セルC11の場合、数式は次のように書けます：=OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3)。この数式は何を返すのでしょうか？最初のOFFSET関数は配列{10; 11; 12; 15}を返し、2番目のOFFSET関数は配列{5; 8; 9; 10}を返します。そして{10; 11; 12; 15} – {5; 8; 9; 10} = {10-5; 11-8; 12-9; 15-10} = {5; 3; 3; 5}となります。この配列を=3で論理テストすると、Excelは内部で{5=3; 3=3; 3=3; 5=3} = {FALSE; TRUE; TRUE; FALSE}と計算します。この配列にOR関数を適用するとTRUEが返されるため、セルC11はTRUEと判定されます。
• これでアルゴリズムの仕組みは理解できたはずです。ただし、ここで一つ問題があります。この数式はセルC8以降でしか機能しません。C8より上には3つのセルしかありません。そのため、セルC5、C6、C7ではこの数式が使えず、修正が必要になります。
• セルC5～C7については、上側の3つのセルを考慮しないように数式を調整します。たとえばセルC6の場合、セルC11用の数式=OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3)のままでは機能しません。
• そこで、セルC5用の数式は次のようになります：OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3)。
• セルC6用の数式は次のようになります：OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3)。
• セルC7用の数式は次のようになります：OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3)。
• そしてセルC8用の数式は次のとおりです：OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3)[これが汎用数式です]。
• セルC9も同じく：OR(OFFSET(C9, 0, 0, 4, 1)-OFFSET(C9,-3, 0, 4, 1)=3)[汎用数式]。
• さて、これらの数式に何かパターンが見えませんか？最初のOFFSET関数のrows引数は3から0へ減少し、height引数は1から4へ増加しています。2番目のOFFSET関数のrows引数は0から-3へ減少し、height引数は1から4へ増加しています。
• 第一に、最初のOFFSET関数のrows引数は次のように修正します：MAX(ROW(C$5)-ROW(C5)+3,0)
• 第二に、2番目のOFFSET関数のrows引数は次のように修正します：MAX(ROW(C$5)-ROW(C5),-3)
• 第三に、最初のOFFSET関数のheight引数は次のように修正します：MIN(ROW(C5)-ROW(C$5)+1,4)
• 第四に、2番目のOFFSET関数のheight引数は次のように修正します：MIN(ROW(C5)-ROW(C$5)+1,4)
• この修正内容をじっくり考えてみてください。決して難しくはありません。これら4つの修正こそが、Excel VBAのFOR LOOPと同じ役割を果たしていますが、すべてExcel数式だけで構築しているのです。
• 以上で、汎用数式がセルC5:C34全体でどのように機能するのかが分かりました。

というわけで、スプレッドシートにおけるループ処理の話をしてきました。これはまさにExcelでのループ処理の完璧な例です。数式は毎回7個のセルを取り出し、特定の値を見つけるためにそれらを処理しています。

例2：IF関数とOR関数でFORループを作る

この例では、セルに値が入力されているかどうかをチェックする場面を想定します。Excel VBAのFOR Loopなら簡単に実現できますが、ここではExcel数式だけで行います。

IF関数とOR関数を組み合わせたExcel数式でFORループを作れます。さらに、この数式は用途に合わせて自由にカスタマイズ可能です。手順は以下のとおりです。

手順：

• まず、Statusを表示させたい別のセルE5を選択します。
• 次に、セルE5に次の数式を入力します。

• その後、ENTERキーを押して結果を表示します。

数式の解説

ここで、OR関数は与えられた論理式のうち一つでもTRUEであればTRUEを返します。

• 第一に、B5=”” は1番目の論理式で、セルB5に値が入っているかどうかをチェックします。
• 第二に、C5=””は2番目の論理式で、セルC5に値が入っているかどうかをチェックします。
• 第三に、D5=”” は3番目の論理式で、同様にセルD5に値が入っているかどうかをチェックします。

一方、IF関数は指定された条件に応じて結果を返します。

• OR関数がTRUEを返した場合、Statusには「Info Missing」が表示されます。そうでなければ「Done」が表示されます。

• その後、フィルハンドルアイコンをドラッグして、残りのセルE6:E13にオートフィルします。または、フィルハンドルアイコンをダブルクリックしても構いません。

最終的に、すべての結果が表示されます。

例3：SUMIFS関数でFORループを作る

今度は、特定の人物の請求額の合計を出したい場合を想定します。このような場合にも、Excel数式によるFORループが活用できます。ここではSUMIFS関数を使ってExcelにFORループを作成します。手順は以下のとおりです。

手順：

• まず、Statusを表示させたい別のセルF7を選択します。
• 次に、セルF7に次の数式を入力します。

• その後、ENTERキーを押して結果を表示します。

数式の解説

• $C$5:$C$13は、SUMIFS関数が合計を計算する対象のデータ範囲です。
• $B$5:$B$13は、SUMIFS関数が指定した条件を照合するデータ範囲です。
• E7が条件（検索値）です。
• つまり、SUMIFS関数はセルE7の値に該当する支払額を合計します。

• その後、フィルハンドルアイコンをドラッグして、残りのセルF8:F10にオートフィルします。

最終的に、次のような結果が得られます。

まとめ

この記事がお役に立てば幸いです。ここでは、数式を使ってExcelにFORループを作る3つの実用的な例を解説しました。Excel関連のコンテンツをもっと学びたい方は、ぜひ当サイトExceldemyをご覧ください。ご質問やご意見、提案などがあれば、下のコメント欄でお気軽にお知らせください。

関連記事

• Excel VBAでDo Whileループを使う方法
• VBA ExcelのFor Nextループ（ステップとループを抜ける方法）