「StiLL」Excel情報489　データ--セル範囲を自動で拡張する方法

【テーマ】今回はセル範囲を自動で拡張する方法を紹介します。VLOOKUP関数などの数式で、参照先にデータが追加されてセル範囲が広がっても、この方法を使えば、数式を修正する必要はありません。

【方法】参照先の表をテーブルに変換し、数式の中の[範囲]をテーブル名にします。

【参考】VLOOKUP関数については[バックナンバー242]、テーブルについては[バックナンバー268]をご参照ください。

今回はセル範囲を自動で拡張する方法を紹介します。

VLOOKUP関数などの数式は引数にセル範囲を指定しますが、

参照先のデータが追加されてセル範囲が広がれば、数式を修正する必要があります。

この方法を使えば、セル範囲が広がっても数式を修正する必要はありません。

例として下図のようにVLOOKUP関数を使って、「商品コード」から「商品名」を求めます。

※

VLOOKUP関数については、以下のバックナンバーをご参照ください。

VLOOKUP関数の参照先の表は下図になります。(シート名は「商品コード表」)

C2セルの「商品名」をVLOOKUP関数で求める場合、下記のような数式になります。

=VLOOKUP(B2,商品コード表!$A$2:$B$4,2,FALSE)

しかし、商品が増えた場合、

[商品コード表]シートにある表の行数が増えるため、VLOOKUP関数の[範囲]も修正しないと、

追加したデータが検索できません。

[商品コード表]にデータを追加する度に数式を修正するのは手間がかかりますが、

下記対応をすることで、解決します。

■参照先の表をテーブルに変換する

予め参照先にする表をテーブルに変換しておきます。

テーブルの変換は、参照先の表のセルを選択した状態で[挿入]→[テーブル]→範囲を確認後→[OK]、

または[ホーム]→[テーブルとして書式設定]→書式の選択→範囲を確認して[OK]で変換できます。

テーブルに変換後、C2セルの数式の[範囲]のみを修正し、C7セルまでフィルをして数式をコピーします。

[範囲]はテーブル名にします。

テーブル名は、任意の名前に変更することも可能です。

テーブル内のセルを選択し、[テーブルツール]→[デザイン]→[プロパティ]グループの[テーブル名]欄から

変更できます。(例では、テーブル名を「商品コード表」に変更しています。)

数式の中の[範囲]をテーブル名にすることで、[商品コード表]に1行追加しても、

自動で[範囲]が拡張され、追加された商品名がC7セルに表示されました。

■ご参考までに

●

テーブルについては、以下のバックナンバーをご参照ください。

●

VLOOKUP関数以外にも、INDEX関数やCOUNTIF関数など、

[範囲]を設定する関数に使用できます。