皆さん、こんにちは。今回はExcelの名前の定義についての基本と活用場面について解説したいと思います。
そもそも「名前の定義」とは・・・
セル範囲や数式に名前を付けること。
名前を付けることでどのようなメリットがあるのか？
- 数値、関数、データ入力規則リストの引数（参照先）がわかりやすくなる。（可読性が上がる）
- 関数利用時に、絶対参照設定を省略できる。
- テーブル設定した場合にフィールドを絶対参照化できる。
- データ入力規則のリスト使用時に他のシートからの可変参照がが可能になる。
などが挙げられます。
３，４についてはこちらをご覧ください。
名前の定義の操作（基本）
名前の定義は「数式」タブ→「定義された名前」グループの中にあります。
名前の定義の設定方法
名前の定義を設定する方法はいくつかあります。ご紹介します。
名前ボックスに入力
名前を設定したい範囲を選択→名前ボックスに任意の名前を入力します。※タイトル行は必要ないので含めずに選択します。
名前の定義に入力
選択範囲して、「数式」タブから「名前の定義」を選びます。新しい名前ボックスに任意の名前を入力します。参照範囲ボックスにて参照範囲を確認または変更できます。
選択範囲から作成
こちらについては、表内を一括で名前設定したい場合に便利です。「日付」「社員コード」「営業社員」「販売額」「四半期」フィールドを一括で名前設定します。
名前の定義を設定したい範囲（タイトル行も含める）を選択して、「数式」タブ→「選択範囲から作成」です。
「選択範囲から名前を作成」ボックス内の上端行にチェックを入れます。これは範囲選択した部分の一番上のセルに入っている文字列を名前にするということです。選択する範囲によって左端列、最下行など使い分けていただければと思います。
「数式」タブ→「名前の管理」で設定した名前を確認できます。下のとおりフィールドごとに名前が設定されています。
数値に名前を設定する
セルだけではなく、特定の数値などにも「名前の定義」が使えます。
「数式」→「名前の定義」で1.1という数値を消費税という名前にします。
参照範囲入力ボックスに＝1.1と入力。名前は消費税と入力します。
名前の定義を設定した数値を数式に利用します。下の画像のように販売額に消費税をかけます。
今回はE5セルの100に消費税をかけます。
通常の数式と同じように販売額であるE5を参照します。そして、先ほど名前の定義をした消費税（1.1）は「数式」タブ→「数式で使用」をクリックすると名前の一覧がでてくるので、そこから選択します。直接入力しても問題ありません。
このように決まった数値について名前を定義することで、数式の可読性が上がります。
関数を使って可読性を確認する
シート１にある「よっちゃんいか」販売実績表を参照して、シート２にある四半期表にSUMIF関数を使ってそれぞれの期の販売合計額を算出するというものです。
シート２に設定した関数の引数をみると、Sheet1!$F$5:$F$ー（シート１の参照先）という形で引数の読解は難しいです。
参照先である「販売額」「四半期」フィールドにそれぞれ四半期、販売額という名前を設定すると、下の画像のように引数の可読性が向上します。また絶対参照の設定も必要ありません。
名前の定義による可変設定
フィールドに名前の定義を設定しました。設定した後に新しくレコードを追加してみます。（3月25日ー）です。※下画像の赤枠部分です。
「名前の管理」で参照範囲を確認すると、新しく追加したレコード部分は名前の範囲に含まれていません。
新たなレコードを名前の定義参照範囲に含める方法（可変設定）
名前の定義を設定した後に、新たに追加されたレコードを参照範囲に含める方法です。
- あらかじめ表をテーブルにしておく
- OFFSET関数を活用する
あらかじめ表をテーブルにしておく
順番として、最初に「挿入」タブ→「テーブル」で表をテーブルに変換します。
そして、フィールドに「名前の定義」を設定します。
レコードを追加します。下の画像のように追加したレコード（赤字部分）も参照範囲設定されています。
OFFSET関数を活用する（表をテーブルにしない場合）
表をテーブルにしない場合は、名前にOFFSET関数を使います。
OFFSET関数は引数で指定したセル範囲の参照を返します。
OFFSET関数・・OFFSET（基準、行数、列数、高さ、幅）
基準・・D4
行数（シフトする行数）・・1
列数（シフトする列数）・・0
高さ・・COUNTA関数を使いD列のデータ個数を数えます。そこからタイトル行「営業社員」のデータを引いています。
幅・・１
つまり、参照範囲として、D4から1行シフトしたD5を基準に高さはCOUNTA関数を使って参照範囲内のデータ個数＝高さとしてます。
すこし難しいですが、このOFFSET関数とCOUNTA関数を入れ子にする方法は、他のサイトでも詳しく紹介されていますので参考にしてみてください。
毎日のExcel作業を短縮できるコツが１日で掴めました！！筆者の「たった１日で即戦力になるExcelマスター講座」の体験談はこちら↓↓
まとめ
名前の定義と活用場面についてまとめてみました。是非参考にしてみてください。
コメント