Excelで動的名前範囲を作成するにはどうすればよいですか？

通常、名前範囲はExcelユーザーにとって非常に便利です。列内の値のシリーズを定義し、その列に名前を付けることで、セル参照ではなく名前でその範囲を参照できます。しかし、ほとんどの場合、将来参照範囲のデータ値を拡張するために新しいデータを追加する必要があります。このような場合、数式 > 名前管理に戻り、新しい値を含むように範囲を再定義する必要があります。これを回避するために、動的な名前範囲を作成することができます。これは、リストに行や列を追加するたびにセル参照を調整する必要がないことを意味します。

テーブルを作成してExcelで動的な名前範囲を作成する

関数を使用してExcelで動的な名前範囲を作成する

VBAコードを使用してExcelで動的な名前範囲を作成する

テーブルを作成してExcelで動的な名前範囲を作成する

Excel 2007以降のバージョンを使用している場合、動的な名前範囲を作成する最も簡単な方法は、名前付きExcelテーブルを作成することです。

例えば、次のようなデータ範囲があり、これが動的な名前範囲になる必要があります。

1. まず、この範囲に範囲名を定義します。範囲A1:A6を選択し、名前ボックスに「 Date」と入力してEnterキーを押します。同様に、範囲B1:B6に「Saleprice」という名前を定義します。同時に、空白のセルに =sum(Saleprice) という数式を作成します。スクリーンショットをご覧ください:

2. 範囲を選択し、挿入 > テーブルをクリックします。スクリーンショットをご覧ください:

3. [テーブルの作成] プロンプトボックスで、[テーブルにヘッダーがある] をチェックします（範囲にヘッダーがない場合はチェックを外します）。[OK] ボタンをクリックすると、範囲データがテーブルに変換されます。スクリーンショットをご覧ください:

4. そして、データの後に新しい値を入力すると、名前範囲は自動的に調整され、作成された数式も同様に変更されます。次のスクリーンショットをご覧ください:

注意:

1. 新しく入力するデータは既存のデータに隣接している必要があります。つまり、新しいデータと既存のデータの間に空白の行や列があってはいけません。

2. テーブル内では、既存の値の間にデータを挿入することができます。

関数を使用してExcelで動的な名前範囲を作成する

Excel 2003以前のバージョンでは、最初の方法は利用できませんので、別の方法があります。以下のOFFSET( ) 関数はこれを行うことができますが、少し面倒です。例えば、範囲名を定義したデータ範囲があるとします。例として、A1:A6 の範囲名は Date、B1:B6 の範囲名は Salepriceで、同時にSaleprice の数式を作成しています。スクリーンショットをご覧ください:

次の手順で範囲名を動的な範囲名に変更できます:

1. 数式 > 名前管理 をクリックします。スクリーンショットをご覧ください:

2. [名前管理] ダイアログボックスで、使用したい項目を選択し、[ 編集] ボタンをクリックします。

3. 表示される [名前の編集] ダイアログで、この数式 =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1) を [参照先] テキストボックスに入力します。スクリーンショットをご覧ください:

4. [OK] をクリックし、ステップ2とステップ3を繰り返して、この数式 =OFFSET(Sheet1!$B$1, 0, 0, COUNTA($B:$B), 1) を Saleprice 範囲名の [ 参照先] テキストボックスにコピーします。

5. これで、動的な名前範囲が作成されました。データの後に新しい値を入力すると、名前範囲は自動的に調整され、作成された数式も同様に変更されます。スクリーンショットをご覧ください:

注意: 範囲の途中に空白のセルがある場合、数式の結果は正しくありません。空白でないセルがカウントされないため、範囲が本来より短くなり、範囲の最後のセルが除外されるからです。

ヒント: この数式の説明:

• =OFFSET(基準,行,列,[高さ],[幅])
• 
• =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)

• 基準は開始セルの位置に対応し、この例では Sheet1!$A$1 です。
• 行は開始セルに対して下方向に移動する行数を指します（負の値を使用すると上方向に移動します）。この例では、0 はリストが最初の行から始まることを示します。
• 列は開始セルに対して右方向に移動する列数を指します（負の値を使用すると左方向に移動します）。上記の例の数式では、0 は右方向に 0 列拡張することを示します。
• [高さ] は調整後の位置から始まる範囲の高さ（または行数）に対応します。$A:$A は、列Aに入力されたすべての項目をカウントします。
• [幅] は調整後の位置から始まる範囲の幅（または列数）に対応します。上記の数式では、リストは1列の幅になります。

これらの引数を必要に応じて変更できます。

VBAコードを使用してExcelで動的な名前範囲を作成する

複数の列がある場合、残りのすべての列に個別の数式を繰り返し入力することは可能ですが、それは長く反復的なプロセスです。作業を簡単にするために、動的な名前範囲を自動的に作成するコードを使用できます。

1. お使いのワークシートをアクティブにします。

2. ALT + F11 キーを押し続け、Microsoft Visual Basic for Applications ウィンドウを開きます。

3. 挿入 > モジュールをクリックし、モジュールウィンドウに次のコードを貼り付けます。

VBAコード：動的な名前範囲を作成する

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. そして、F5キーを押してコードを実行すると、最初の行の値で名前が付けられたいくつかの動的な名前範囲が生成され、さらに全体のデータをカバーする「MyData」という動的な範囲も作成されます。

5. 行や列の後に新しい値を入力すると、範囲も拡張されます。スクリーンショットをご覧ください:

注意:

1. このコードでは、範囲名は 名前ボックスに表示されません。範囲名を簡単に表示して使用するために、 Kutools for Excelをインストールしました。 ナビゲーションペインにより、作成された動的な範囲名が一覧表示されます。

2. このコードでは、データの全体範囲は縦方向または横方向に拡張できますが、新しい値を入力する際、データ間に空白の行や列があってはいけません。

3. このコードを使用する場合、データ範囲はセルA1から始まる必要があります。

関連記事:

Excelで新しいデータを入力した後、チャートを自動更新するにはどうすればよいですか？