拡張や縮小が発生するリストを名前付き範囲として登録する(OFFSET関数)

エクセル「名前付き範囲」のを拡張可能にする設定方法について詳しく解説します。データが頻繁に追加されたり、減少したりするリストを対象とします。

リストの範囲が頻繁に変わる場合、それを毎回手動で更新するのは大変です。そこでエクセルの「名前付き範囲」機能を使うと、一度設定すればリストの範囲が自動的に更新されるので、作業効率を大幅に上げることが可能です。

では具体的な設定方法を見ていきましょう。今回はセルB2から始まるリストを例に取ります。

まず、エクセルの「数式」タブをクリックし、「名前の管理」を選択します。

新しいウィンドウが開きますので、「新規」ボタンをクリックします。

名前を入力するフィールドが表示されますので、ここに範囲名を入力します。この名前は後でこの範囲を参照するために使用します。適切な名前を選ぶことで、数式の見通しがよくなり、エラーを防ぐことができます。

次に、「参照」フィールドに以下の数式を入力します(リストの起点をB2とする場合)：

OFFSET($B$2,0,0,COUNTA($B$2:$B$1048576),COUNTA($B$2:$XFD$2))

この数式の意味を簡単に説明します。

1. OFFSET($B$2,0,0,...): OFFSET関数は、基準となるセルから指定した行数と列数だけずらした位置のセルまたはセル範囲を参照します。この場合、基準となるセルはB2で、行と列のずらし方は0なので、範囲はB2から始まります。
2. COUNTA($B$2:$B$1048576): COUNTA関数は、非空のセルの数を数えます。この場合、B2からエクセルの最終行である1048576行までの非空のセル（つまり、何かしらの値または文字列が入力されているセル）の数を数えます。これにより、リストの行数（つまり、縦の長さ）が決まります。
3. COUNTA($B$2:$XFD$2): 同じくCOUNTA関数を使い、B2からXFD2まで（つまり、エクセルの最終列まで）の非空のセルの数を数えます。これにより、リストの列数（つまり、横の長さ）が決まります。

この数式を入力することで、リストの範囲が動的に変化することをエクセルに指示します。つまり、新しいデータが追加されても、リストの範囲は自動的に更新されます。

以上で設定は完了です。「OK」をクリックしてウィンドウを閉じてください。

以上が、拡張や縮小が発生するリストを名前付き範囲として登録する方法です。この手法を利用することで、データが増えた場合や減った場合でも、手作業で範囲を調整する必要がなくなります。

名前付き範囲の使い方(参考)

名前付き範囲をどのように利用するのか、具体的な使い方を解説します。

エクセルの名前付き範囲を活用すれば、数式の見通しが良くなり、間違いを減らすことが可能です。さらに、名前付き範囲を使えば、データ範囲が拡大または縮小したときに、手動で範囲を修正する必要がありません。それでは、具体的な利用法について見ていきましょう。

まず、名前付き範囲を使った基本的な数式の入力方法について説明します。例えば、ある範囲の平均を求めたい場合、通常は以下のように数式を入力します：

しかし、名前付き範囲を使うと、セル範囲を指定する代わりに名前を使用できます。たとえば、前回の記事で「MyList」と名付けた名前付き範囲があるとします。この場合、以下のように数式を書くことができます：

この方法の利点は、数式が読みやすくなること、そして範囲が変更になった場合でも、数式を修正する必要がないことです。

次に、名前付き範囲を利用したデータ検索について解説します。VLOOKUP関数やHLOOKUP関数を使ってデータを検索する際、名前付き範囲を使うことで範囲の指定を簡単にできます。

例えば、VLOOKUP関数で「MyList」の中から特定のデータを検索する場合、以下のように書くことができます：

このように、名前付き範囲を利用することで、エクセル作業が一段と効率化されます。