Excelの動的名前付き範囲とは？OFFSET・INDEX・VBAなど4つの作成方法を徹底解説

Excelの動的名前付き範囲（Dynamic Named Range）は、その存在を知っているユーザーがまだ多くない、隠れた便利機能のひとつです。データの追加や削除に合わせて範囲が自動的に拡張・縮小されるため、集計やグラフ作成の効率が大幅に向上します。本記事では、動的名前付き範囲を活用するためのヒントとテクニックを、具体例とともにわかりやすく解説します。

OFFSET関数で動的名前付き範囲を作る

動的な範囲を作成する際に最もよく使われるのが、OFFSET関数です。

OFFSET関数の構文

ExcelのOFFSET関数は、次のような構文で表されます。

OFFSET(基準, 行数, 列数, [高さ], [幅])

各引数の意味は以下のとおりです。

1. 基準（reference） – オフセットの起点となるセル参照。
2. 行数（rows） – 基準セルから上下に移動する行数。
3. 列数（cols） – 基準セルから左右に移動する列数。
4. 高さ・幅（height, width） – 基準セルを起点とした選択範囲の高さと幅。

OFFSET関数による動的名前付き範囲の作成手順

OFFSET関数は、「名前の管理」ダイアログボックスで動的名前付き範囲を定義する際に活用できます。

手順は以下のとおりです。

「数式」タブを開き、「定義された名前」グループにある「名前の定義」をクリックします。すると、下図のような「新しい名前」ダイアログボックスが表示されます。

ここでは、A1の1つ下のセルから同じ列内で始まり、K1セルとK2セルに入力された値の分だけ行数・列数を選択する動的名前付き範囲を作成します（下図の例を参照）。

「参照範囲」フィールドに入力する数式は次のようになります。

=OFFSET(Sheet1!$A$1;1;0;Sheet1!$K$1;Sheet1!$K$2)

OFFSET関数のすべての要素は動的に設定できます。たとえば、特定の曜日の日付だけを選択したい場合にも応用可能です。

いくつかの出力例を見てみましょう。

=OFFSET(Sheet1!$A$1;1;0;6;2) ＝ A2:B7の範囲を参照します。

K1〜K4セルの入力値に応じて範囲が変わるようにするには、次のような数式を使用します。

=OFFSET(Sheet1!$A$1;Sheet1!$K$3;Sheet1!$K$4;Sheet1!$K$1;Sheet1!$K$2)

他のExcel関数と同様に、OFFSET関数も結果として数値を返す関数と組み合わせることができます。たとえば、月曜日を基準として「先週を除くすべての火曜日」を選択したい場合は、同じ例をもとに次のような数式になります。

=OFFSET(Sheet1!$A$1;1;1;COUNT(Sheet1!$B:$B);1)

あわせて読みたい：セルの値に基づくExcel動的名前付き範囲の作り方（5つの簡単な方法）

類似記事

• セルの値に基づくExcelの動的範囲
• Excel VBA：セルの値に基づく動的範囲（3つの方法）
• Excel VBAで最終行の動的終行の動的範囲を扱う方法（3つの方法）

INDEX関数で動的名前付き範囲を作る

動的名前付き範囲には、OFFSET関数以外の関数も利用できます。たとえばINDEX関数を使えば、「月曜日」のデータが何件あってもすべて選択できます。

=Sheet1!$A$2:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))

さらに、参照そのものを動的にしたい場合は、複数の関数を組み合わせます。たとえば、あるセルに曜日名を入力すると、表の中からその曜日に該当する日付をすべて選択したい場合、次のような数式が使えます。

=OFFSET(INDIRECT(ADDRESS(2;MATCH(Sheet1!$K$6;Sheet1!$1:$1;0)));0;0;COUNT(Sheet1!$A:$A);1)

あわせて読みたい：ExcelのOFFSET関数で複数列の動的範囲を効果的に使う方法

VBAで動的名前付き範囲を作る

業務では、同じ操作を繰り返し実行しなければならない場面によく遭遇します。動的名前付き範囲の作成も例外ではありません。こうした反復作業を避けるために、VBAを活用できます。

VBAの仕組みは数式とまったく同じですが、名前付き範囲を追加するための固有の構文に従う必要があります。

構文は次のとおりです。

ActiveWorkbook.Names.Add Name:="NAME", RefersTo:="選択したい範囲"

たとえば、前述の例のひとつはVBAでも簡単に再現できます。完全なマクロは次のようになります。

Sub naming()

  ActiveWorkbook.Names.Add Name:="NAME9", RefersTo:="=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)"

End Sub

あわせて読みたい：Excel VBAで動的名前付き範囲を作成する方法（ステップバイステップガイド）

空白セルを含む場合の動的名前付き範囲

すべてのセルが入力されておらず、一部のセルが空白になっているケースにはよく直面します。このような場合に重要なのは、参照を求めるために使用する数式の定義を正しく理解することです。たとえばCOUNTA関数は、範囲内の空白以外のセルをすべてカウントする関数です。空白セルを含めるかどうかで、使い方が変わってきます。

下の画像は、空白セルがある場合の状況を示しています。

すべての「月曜日」を選択したい場合、これまでの例と同じ数式を使うと、空白セル（A5）が存在するため、最後の月曜日が選択されません。この問題を解決するには、次のように別の数式を組み合わせて使用します。

=OFFSET(Sheet1!$A$1;1;0;SUMPRODUCT(MAX((Sheet1!$A:$A<>"")*ROW(Sheet1!$A:$A)))-1;1)

この数式により、A2からA7までのすべてのセルが選択されます。

ここまでの解説と例が、皆さまの作業をより速く、より効率的に進める助けになれば幸いです。動的名前付き範囲は多くの可能性を秘めた奥深い機能であり、スキルと創意工夫次第で強力なツールになります。関数の組み合わせは無限にあり、その活用方法を見つけるのはあなた次第です。

あわせて読みたい：Excelでセルの値に基づく動的合計範囲を作る方法（4つの方法）

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

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

関連記事

• Excel VBAで動的範囲を使う方法（11の方法）
• Excelテーブルの動的範囲を活用したデータの入力規則ドロップダウンリスト
• Excelで動的グラフ範囲を作成する方法（2つの方法）
• Excelで数値の範囲を作成する方法（3つの簡単な方法）