「StiLL」Excel情報489 データ--セル範囲を自動で拡張する方法
【テーマ】今回はセル範囲を自動で拡張する方法を紹介します。VLOOKUP関数などの数式で、参照先にデータが追加されてセル範囲が広がっても、この方法を使えば、数式を修正する必要はありません。 【方法】参照先の表をテーブルに変換し、数式の中の[範囲]をテーブル名にします。 【参考】VLOOKUP関数については[バックナンバー242]、テーブルについては[バックナンバー268]をご参照ください。
今回はセル範囲を自動で拡張する方法を紹介します。
VLOOKUP関数などの数式は引数にセル範囲を指定しますが、
参照先のデータが追加されてセル範囲が広がれば、数式を修正する必要があります。
この方法を使えば、セル範囲が広がっても数式を修正する必要はありません。
例として下図のようにVLOOKUP関数を使って、「商品コード」から「商品名」を求めます。
VLOOKUP関数については、以下のバックナンバーをご参照ください。
【バックナンバー242】 関数--検索機能 VLOOKUP
VLOOKUP関数の参照先の表は下図になります。(シート名は「商品コード表」)
C2セルの「商品名」をVLOOKUP関数で求める場合、下記のような数式になります。
=VLOOKUP(B2,商品コード表!$A$2:$B$4,2,FALSE)
しかし、商品が増えた場合、
[商品コード表]シートにある表の行数が増えるため、VLOOKUP関数の[範囲]も修正しないと、
追加したデータが検索できません。
[商品コード表]にデータを追加する度に数式を修正するのは手間がかかりますが、
下記対応をすることで、解決します。
参照先の表をテーブルに変換する
予め参照先にする表をテーブルに変換しておきます。
テーブルの変換は、参照先の表のセルを選択した状態で[挿入]→[テーブル]→範囲を確認後→[OK]、
または[ホーム]→[テーブルとして書式設定]→書式の選択→範囲を確認して[OK]で変換できます。
テーブルに変換後、C2セルの数式の[範囲]のみを修正し、C7セルまでフィルをして数式をコピーします。
[範囲]はテーブル名にします。
テーブル名は、任意の名前に変更することも可能です。
テーブル内のセルを選択し、[テーブルツール]→[デザイン]→[プロパティ]グループの[テーブル名]欄から
変更できます。(例では、テーブル名を「商品コード表」に変更しています。)
数式の中の[範囲]をテーブル名にすることで、[商品コード表]に1行追加しても、
自動で[範囲]が拡張され、追加された商品名がC7セルに表示されました。
■ご参考までに
テーブルについては、以下のバックナンバーをご参照ください。
【バックナンバー268】 挿入--「テーブル」
VLOOKUP関数以外にも、INDEX関数やCOUNTIF関数など、
[範囲]を設定する関数に使用できます。
Copyright(C) アイエルアイ総合研究所 無断転載を禁じます