Excel(エクセル)には『名前の定義』という機能が存在します。
『名前』？？何それ？？と、機能のイメージすら湧かない方は多いはず。もちろん、私もその一人でした。
例えば、『名前の定義』機能を使うと、B4:E15のセル範囲に”商品リスト”といったオリジナルの名前を登録し、「=VLOOKUP(G4,商品リスト,3,FALSE)」といった数式の使い方ができるようになります。
このように、登録したセル範囲の名前は、数式の中で使うことでセルの引数の設定が簡単になります。
VLOOKUP関数や検索範囲の設定や、プルダウンの設定や、セルの移動に利用すると便利です。
この記事では『名前の定義』機能を使った実例を６つ紹介しますので、ぜひ参考にしてみて下さい。
「名前」を数式に利用する一例
「名前の定義」機能でセル範囲や固定値に名前を付ける
エクセルのセル範囲を数式で利用する際は、「B4:E15」といったように、セル番号をコロン「:」で挟んで表示します。
また、セル範囲を動かないように固定にしたい場合は、「$B$4:$E$15」といったように絶対参照の「$」を列番号と行番号の手前に挿入する必要があります。
絶対参照の「$」は「F4」キーを使うことで簡単に挿入することが出来ますが、「$B$4:$E$15」を関数の引数に設定するのは意外とめんどくさいですよね。
「名前の定義」機能を使うと「$B$4:$E$15」というセル範囲を好きな名前に設定することが出来ます。
例えば、セル範囲を「商品リスト」という名前にした場合、「=VLOOKUP(G4,商品リスト,3,FALSE)」というような関数の使い方が可能となります。
また、セル範囲だけでなく、「10％」といった固定値に対して名前を付けることも可能です。
例えば「消費税」という名前で登録して、「＝A3*消費税」という数式で消費税を算出することが出来ます。
「*0.1」や「*10％」と数式に入力した方が簡単と思われがちですが、例えば消費税率が15％に変わった際などは「名前」の登録内容を「15％」に変更するだけですべてのセルの消費税を一括で変更することが可能です。
全てのセルの「10％」を「15％」に変えるのは変更漏れも起きやすいし、時間もかかってしまいますが、名前を使うとその心配もありません。
「名前の定義」を使うメリット
「B4:E15」などのセル範囲に「名前」を付けるメリットを６点紹介します。
『１点目：参照範囲が分かりやすい』
セル範囲を「商品リスト」などの名前を登録することで、関数の引数を理解しやすくなる。
『２点目：入力誤りの防止』
セル範囲「B4:E15」を「商品リスト」と名前を付けることで、「B4:E14」といったセル範囲の入力誤りを減らすことが出来ます。
『３点目：名前のセルまでの移動が楽』
セル範囲に名前を付けるとその「名前」の位置まで一瞬で移動することが出来ます。見出しのセルに名前を付けると便利です。
『４点目：関数の作成が速くなる』
「$B$4:$E$15」のセル範囲を関数で挿入するより、「商品リスト」などの「名前」を使った方が関数を簡単に素早く作成することが出来ます。
『５点目：セル範囲や固定値を修正しやすい』
「名前」のセル範囲は後から修正することが出来ます。「名前」を複数のセルで利用していた場合、「名前」の修正だけで一括で関数の修正が完了させることが出来ます。
『６点目：印刷範囲として利用』
印刷範囲に「名前」を登録しておくことで、印刷する度に印刷範囲を指定する必要が無くなります。
セル範囲に『名前』を登録する方法
それでは実際にセル範囲に「名前」を付ける方法を紹介します。
方法は簡単です。数式バーの左横、A列の列番号の上側に設置されている「名前ボックス」を利用します。
まず「名前」を付けたいセル範囲を選択します。見出し行は選択してもしなくてもどちらでも良いです。
セルを選択した状態のままで「名前ボックス」に、任意の名前を入力して「Enter」キーを押してください。「Enter」キーで確定しないと登録が完了しないので忘れないにお願いします。この作業で「名前」の登録は完了です。
「名前」が登録されたか確認しておきましょう。セル範囲を再度選択すると、「名前ボックス」に登録した名前が表示されていれば無事登録が完了しています。
登録した名前は、「数式」タブ→「名前の管理」からでも確認することが出来ます。
「名前の管理」画面では、登録した名前や参照範囲を確認したり編集することが出来ます。
「名前」を「選択範囲から作成」で一括で設定する方法
表の各列に「名前」を登録することで、関数の引数設定を簡単にすることが出来ます。
さきほどの「名前ボックス」を使っていると時間がかかってしまうので、「数式」タブの「選択範囲から作成」コマンドから一括で登録することが出来ます。
表全体を選択してから、「数式」タブ→「選択範囲から作成」をクリックします。
「選択範囲から名前を作成」画面が表示されるので、「以下に含まれる値から名前を作成」で「上端行」にチェックを入れた状態で「OK」を押します。
ここで「左端列」にチェックを入れておくと、表の行毎にも「名前」が設定されます。
列見出しを除いたセル範囲を選択すると、「名前ボックス」に列見出しの「名前」が登録されていることを確認することが出来ます。
「数式」タブの「名前の管理」から列毎に名前が登録されていることを確認することが出来ます。また、「値段(円)」列が「値段_円」という「名前」で登録されているように一部の名前は自動で変換されます。
「名前の定義」を使って固定値に名前を登録する方法
「名前」は「10％」という固定値に対しても登録して数式に利用することが可能です。
この固定値はセル範囲という概念が存在しないので、「名前ボックス」からは登録出来ません。
固定値に名前を付けるときは、「名前の定義」コマンドを利用します。
「数式」タブの「名前の定義」を選択します。
「新しい名前」画面が表示されます。登録したい「名前」を入力し、参照範囲に固定値を入力します。
「＝」を先頭に挿入して、「＝10％」と入力します。仮に「＝」を付けずに「10％」で登録しても自動で「＝10％」と登録値が変更されます。
「数式」タブの「名前の管理」で、登録した「名前」を確認することが確認出来ます。このように、セル範囲だけでなく、「＝10％」という固定値(数値）も名前に登録が可能です。
実例１：「名前」を使ってVLOOKUP関数を利用
それでは、登録した「名前」を関数に使う方法を紹介します。
まず一つ目の実例として、下の表のH4セルにVLOOKUP関数を挿入し、「検索範囲」に「名前」を使ってみましょう。「B4:E15」のセル範囲には「商品リスト」という名前を設定しています。
VLOOKUP関数の使い方に慣れていない方はこちらの記事も参考にしてね。
E4セルにVLOOKUP関数を挿入し、１つ目の引数「検索値」までを入力します。
＝VLOOKUP(G4,
続いて、２つ目の引数「検索範囲」を指定します。普段であれば、検索範囲をカーソル操作で選択して「B4:E15」と選択するところですが、設定した名前である「商品リスト」とキーボードで手入力します。
＝VLOOKUP(G4,商品リスト,
VLOOKUP関数の３つ目の引数「列番号」と４つ目の引数「検索方法」を入力して、「Enter」キーで関数を確定すれば完了です。下のように、VLOOKUP関数に「名前」を使って検索値を抽出することが出来ました。
＝VLOOKUP(G4,商品リスト,3,FALSE)
実例２：「名前」を使ってXLOOKUP関数を利用
XLOOKUP関数で「名前」を使うと、VLOOKUP関数より簡単に引数を設定することが出来ます。
下の表のH4セルにXLOOKUP関数を挿入し、２つ目の引数「検索範囲」と３つ目の引数「戻り範囲」に「名前」を使ってみましょう。「B4:E15」のセル範囲には「商品リスト」という名前を設定しています。
XLOOKUP関数はVLOOKUP関数の利便性がUPした関数だよ。こちらの記事も参考にしてね。
H4セルに以下のXLOOKUP関数を挿入します。
=XLOOKUP(G4,商品番号,商品名,FALSE)
２つ目の引数「検索範囲」と３つ目の引数「戻り範囲」に「名前」をキーボード入力します。
そうすると、XLOOKUP関数に「名前」を使って検索値を抽出することが出来ました。
「名前」の登録さえ事前に完了しておけば、関数の引数の設定は、「名前」を使った方が楽ですよね。
実例３：「名前」を使って２段階プルダウンを作成
「名前」をプルダウンで利用すると、下の表のように１つ目のプルダウンで選択した「部署」に合った「担当者」をプルダウンリストに表示させることが出来ます。
２段階プルダウンの詳細な設定手順を知りたい方は、こちらの過去記事を参考にしてください。この記事は「名前」にフォーカスを当てて説明します。
まず、使っていないワークシートや空きスペースに部署別の従業員リストを表に纏めて、部署列毎に「名前」を登録します。「名前」は１つ目のプルダウンリストの名称と必ず合わせて下さい。
２つ目のプルダウンを設定するセルを選択してから、「データ」タブの「データの入力規則」をクリックします。
「データの入力規則」画面で「入力値の種類」は「リスト」を選択します。
「元の値」は「=INDIRECT(C3)」と入力し、「OK」で画面を閉じます。
INDIRECT関数は、参照するセルの文字列を利用して参照を求める関数です。
今回参照するセルとは、セルC3なので、C3セルでプルダウンで選択する「調達部」「経理部」「総務部」のいずれかの文字列が参照されます。
「調達部」「経理部」「総務部」は「名前」で登録しているので、間接的に従業員リストの各列のリストが２つ目のプルダウンに表示されるという仕組みです。
プルダウンを設定したセルを▼ボタンで開いて確認すると、C列の部署名に合った名前リストだけが表示されることを確認することが出来ます。
このプルダウンに「名前」とINDIRECT関数を利用する方法は、アンケートなどの作成に非常に役に立ちます。
以下の記事で設定手順を１から紹介していますので、合わせて確認してみて下さい。
実例４：「名前」を使って税込金額を算出
固定値の「名前」を利用する実例を紹介します。
今回は消費税込みの金額を簡単に算出するために、「110％」の固定値を「税込」という名前で登録します。
C3セルには「税抜き金額」が入力されていますので、「=C3*税込」と入力すると税込金額を算出することが出来ます。
これだけだと、「＝C3*1.1」と入力するのと一見変わりはありません。しかし、税率が10％から12％に変更されたとき、「*1.1」を利用しているセルをすべて変更する必要があります。
一方、「名前」を使うことで、「名前の管理」画面から登録した固定値を「＝110％」から「＝112％」に変更することで、すべてのセルを一括で修正することが出来ます。
このように、「固定値」として登録しておきたい「名前」は人それぞれです。普段利用している数式を思い出しながら、便利な「名前」を探してみましょう。
実例５：「名前」を使って印刷を楽に！
印刷範囲に「名前」を登録しておくと印刷が楽になります。
下の図のように、印刷したい範囲にだけ「名前」を登録しておきます。
印刷する時には、「名前ボックス」から登録した「名前」をプルダウンで選択します。そうすると、対象のセル範囲が選択されます。
「ファイル」タブの「印刷」メニューに移動し、印刷設定で「選択した部分を印刷」を選択すると、選択範囲だけを印刷することが出来ます。
実例６：「名前」を使ってセルを移動(ワープ)！
スクロールが必要な大きなデータや、複数のワークシートを使っているExcelファイルは、移動したいセルに「名前」を付けておくと便利です。
例えば、下の図のように表が細切れになっている表の左上のセルに「大阪」や「愛知」などのタイトルを付けて、このセルに対してそれぞれ「名前」を登録します。
表タイトルに移動したい時には、「名前ボックス」から登録した「名前」をプルダウンで選択します。
そうすると、対象のセル範囲まで一瞬で移動することが出来ます。このように何度も移動したいセルがある場合は、「名前」を登録しておくと楽ですよね。
数式に名前を利用する際に便利なテクニック
上の見出しで数式や関数に「名前」を使う方法を複数紹介しましたが、「名前」は関数に直接キーボード入力して利用しました。
ただ、どんな「名前」を登録したか忘れたら使いたくても使えないですよね。
「名前」を忘れた時は、「数式」タブの「数式で使用」を利用しましょう。関数を作成している途中で「数式で使用」をクリックし、対象の「名前」をプルダウンリストから選択します。
そうすると、関数の引数に「名前」を簡単に挿入することが出来ます。「名前」の入力がめんどくさい時にも利用出来ますよね。
「名前の管理」でセル範囲を変更したり削除する方法
登録した「名前」の参照範囲を変更したり、「名前」を削除したい時は「名前の管理」を利用しましょう。
「数式」タブの「名前の管理」から、編集や削除、セルの参照範囲などを変更することが出来ます。
