|
|
|
|
|
|
|
|
|
|
|
|
「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) アイエルアイ総合研究所 無断転載を禁じます |
|
|
|
|
|
|
|
|
|
|
|
|