「名前の定義」使ってますか？
セル範囲や、特定の数値に好きな名前を付けることができ、その名前をセル参照や定数として数式内で使うことができます。
あるいは特定のセル範囲に名前を付けて、いつでもその範囲を選択できるという便利機能です。
またINDIRECT関数と組み合わせた使い方も定番です。
でも多用したときの問題点もあります。
この記事では
- 名前の定義とは
- 4つの作成方法
- 名前の利用法
- 名前の管理、削除
- 名前のデメリット
などを紹介しています。
「名前」を使いこなせるようになりましょう。
名前の定義とは
エクセルではセル範囲に、好きな「名前」をつけることができます。
セル範囲だけでなく、数値・文字列・数式にも「名前」をつけられます。
セル範囲に名前を定義
表の「単価」列のデータ「B2:B9」に「合計」という名前を付けてみましょう。
「B2:B9」と選択した状態で、
「数式」タブ →「定義された名前」→「名前の定義」
「新しい名前」ダイアログが開きます。
「名前」に「合計」と入力。
「範囲」は、この名前が適用される範囲です。「ブック」と各シート名から選択。シート名を選択するとそのシートでのみ適用される名前になります。
「参照範囲」が入力されているので、そのまま「OK」。
「B2:B9」の範囲に「合計」という名前が付きました。
名前ボックスの矢印をクリックすると、リストに「合計」という名前が確認できます。
クリックすると「B2:B9」範囲が選択されます。
「名前」は数式内での使用も可能です。
B11セルに数式を入力
=SUM(合計)
ちゃんと計算されました。
この場合の「合計」はセル参照として機能しています。
文字列ではないので ” で囲む必要はありません。
定数に名前を定義
数値や文字列などの定数にも名前が定義できます。
「数式」タブ →「定義された名前」→「名前の定義」
「消費税込み」という名前で「1.1」という数値を設定しました。
数値の場合は、「=」の後にそのまま数値を入れるだけです。
数式に名前を定義
数式に名前を付けてみます。
金額をすべて合計する数式に「金額合計」という名前を付けます。
「数式」タブ →「定義された名前」→「名前の定義」
「名前」に「金額合計」
「参照範囲」に「=SUM( 」まで入力して、右のマークをクリック
合計する範囲をマウスでドラッグして、もう一度右のマークをクリック
閉じカッコ) して「OK」
D11セルに
「=金額合計」と入力
「=SUM(D2:D9)」という数式を「=金額合計」という名前に置き換えました。
相対参照にできる？
すでにお気づきと思いますが、名前を定義すると参照範囲は自動的に絶対参照になります。
相対参照にすることも可能です。
「参照範囲」を入力したら、F2 キーを押して「編集モード」にし、
セル参照部分にカーソルを合わせ、F4 キーを数回押せば、通常のセル参照のように相対参照にできます。
このとき、相対参照の基準はアクティブセルです。
ただ「名前」をつかう目的として、特定のセル範囲を何度も利用することがほとんどと思われます。以降は初期値の絶対参照を前提にして解説をすすめます。
名前に使える文字
名前に使う文字には制限があります。
- 先頭文字は、文字・_ (アンダースコア）・￥
- 2文字目以降は、文字・数字・. (ピリオド)・_ (アンダースコア）
- 「A1」「R1C1」「C」「R」などセル参照をあらわす文字は使えない。(「C」「R」は列・行全体をあらわすセル参照)
- 最大255文字
- 大文字・小文字は区別しない
- 同じ適用範囲 (ブックかシート) の中に同じ名前は付けられない
エクセル2007以降、最終行は1048576行・最終列は XFD列です。
したがって「3文字以下のアルファベット + 数字」はかなりの確率でセル参照になる可能性があります。
覚えきれない場合は日本語・英語の単語を使っておけば間違いありません。
名前の作成方法
前項で、リボンから名前を定義する方法を紹介しました。
名前を定義するにはまだいくつか方法があります。
名前ボックスの利用
セル範囲を選択したうえで、名前ボックスに直接入力して、そのセル範囲に名前を定義することができます。
名前ボックスに「合計」と入力
ちなみに名前ボックスをアクティブにするショートカットキーは
Alt + F3 名前ボックスをアクティブに
ボックス右側の矢印をクリックすれば、定義された名前のリストが展開されます。
また名前ボックスをアクティブにした状態で、「Alt + ↓」でも同じです。
名前ボックスで名前を定義したときは、名前の適用範囲は「ブック」に限定されます。
まとめて名前定義
複数の名前をまとめて定義することも可能です。
ある会社の社員名簿です。
それぞれの部署の社員名のセル範囲を、部署名で定義します。
表全体を選択して
「数式」→「定義された名前」→「選択範囲から作成」
「選択範囲から名前を作成」ダイアログが開きます。
「上端行」のみにチェック。
それぞれの列を、上端の行(部署名)を名前にして定義するという意味です。
各部署の社員名が、部署の名前で定義されました。
「選択範囲から作成」のショートカットキーは
Ctrl + Shift +F3 選択範囲から名前を作成
自動的に定義される名前
自動的にセル範囲に名前が定義されることもあります。
テーブル
テーブルを作成すると「テーブル名」で名前定義されます。
テーブル名で定義されるのは、テーブルの「データ部分」です。見出しは含まれません。
印刷範囲・印刷タイトル
「ページレイアウト」タブや「ページ設定ダイアログ」などから、印刷範囲や印刷タイトルなどを設定すると、そのセル範囲に自動的に名前が定義されます。
印刷範囲は「Print_Area」、印刷タイトルは「Print_Titles」という名前が定義されます。
名前の適用範囲はそれぞれのシート内です。
名前の利用方法
「名前」にはどんな利用方法があるでしょう。
範囲選択
セル範囲に名前をつけることで、簡単にそのセル範囲を選択できます。
名前ボックスから選択
「ジャンプ」から選択
数式内で使用
数式内で「名前」を使用する方法です。
- セル範囲に名前を定義 → セル参照として使用
- 数値・文字列に名前を定義 → 定数として使用
使用例 VLOOKUP関数の検索範囲として使用
表は「社員ID」と「氏名」を入力した名簿です。
表全体を「社員名簿」という名前で定義ずみです。
D3セルに入力した社員IDを参照して、氏名を検索する VLOOKUP関数を E3セルに入れます。
=VLOOKUP(D3,
まで入力し、
「数式」タブ → 「定義された名前」→「数式で使用」をクリック
リストから「社員名簿」を選択しクリック。
数式内に名前が貼り付けされました。
名前の貼り付けにはショートカットキーもあります。
F3 名前の貼り付け
「名前の貼り付け」ダイアログが開き、貼りつける名前を選択できます。
使用例 INDIRECT関数と組み合わせ
INDIRECT関数は、「セル参照をあらわす文字列」を引数に入れると、戻り値としてそのセル参照を返すという関数です。
セル範囲に定義された名前は、そのままセル参照としてあつかえます。したがってINDIRECT関数の引数として利用できます。
さきほどの社員名簿をすこし複雑にした表です。
「東京支社」と「大阪支社」のふたつの名簿があります。
G3セルに入力された支社名と、H3セルに入力された社員IDから氏名を検索します。
東京支社の名簿全体を「東京」、大阪支社の名簿全体を「大阪」という名前で定義ずみです。
I3セルに
=VLOOKUP(H3,INDIRECT(G3),2,FALSE)
と入力。
INDIRECT関数が G3セルを参照することで、「東京」「大阪」という名前のセル範囲が参照されます。
定数として使用
- 数式内でひんぱんに出てくる定数などを、名前として定義しておく。あとから定数を変更するときも、名前の定義を変更するだけでよい。
- 自社の社名や住所などに、「社名」「住所」など名前をつけて、入力を簡略化する。
などの使用例が考えられます。
数式オートコンプリート
名前の頭文字をアルファベットにしておくと、数式オートコンプリートの候補として表示され、便利です。
「table名簿」というテーブル名はオートコンプリートの候補に表示される。
矢印 で選択して Tab で確定
テーブルと組み合わせ
テーブルの列を名前で定義すると、データが増えたときに自動的に名前の範囲も拡張されます。
データが増えても自動的に対応
プルダウンリストに使用
「入力規則」のリストに名前を利用するのも定番のつかい方です。
セル範囲に「IDリスト」と定義。
リストの「元の値」に
=IDリスト
入力欄に名前定義
定型書類の入力するエリアだけ「入力欄」など名前を付けておくと、消去・入力が効率的になります。
名前の管理・削除
「数式」タブ → 「定義された名前」→「名前の管理」
「名前の管理」ダイアログが開きます。
作成・編集・削除ができます。
また「フィルター」をかけて表示する名前をしぼり込むことも可能です。
「ブック内のテーブルの数を把握したい」などのとき便利です。
「名前の管理」のショートカットキーは
Ctrl + F3 「名前の管理」ダイアログを開く
また、「数式で使用」の「名前の貼り付け」ダイアログ (ショートカットキー F3 ）の「リスト貼り付け」をクリックすると、定義された名前とその参照範囲をセル上にペーストできます。
「名前」のデメリット
非常に便利な「名前の定義」ですが、デメリットもあります。
「他の人が見るとわかりにくい」ということです。
「名前の管理」ダイアログなどの知識があればいいですが、そうでない人にとっては理解不能の数式となる可能性があります。
他者と共有するブックにおいては、事前の情報の共有が必要です。
まとめ
最後の項で「名前」のデメリットについて述べましたが、逆に言うとそれぐらいしかデメリットがないということです。
他の人が編集する可能性がないならば、どんどん使っていきましょう。
数式内での貼り付けは、ショートカットキー F3 と、とてもシンプルです。