数式や関数などで参照の記述がゴチャゴチャになって分からなくなったりしませんか。今回は、そんなときにゴチャゴチャをスッキリさせる 名前定義 を使った解決法をお伝えします。 絶対参照で範囲指定は後から見たときに絶対に混乱しますから、 範囲指定には必ず名前定義をしておくことをおすすめします。
図では、IF関数でB３の値で判定し、通常単価表の範囲（$G$4:$H$8）をVLOOKUPで検索する式と、割引単価表の範囲（＄I$4:$J$8）をVLOOKUPで検索する式を記述しています。まさにゴチャゴチャしてシンプルではないですね。
数式を書いていると、どうしてもセル参照の記述がゴチャゴチャになってしまいます。 単一セルの参照でも、アドレス表記はA1とかC99とかといった人には分かりにくい表現で、さらに絶対参照のドルマークが付き、範囲選択ではコロンでつないでそれが２つも並び、、、と高度なことをしようとするとどんどん複雑怪奇になっていきます。最後は、正しく動くので間違ってはいないのだろうけど、後で修正しようとしたときは、修正点を探すのに一苦労ということも多いのではないでしょうか。
VLOOKUP 関数や MATCH 関数などの、範囲を指定する引数を絶対参照で書くと、どうしても見づらくなります。 そんなとき、皆さんはどうしているでしょうか。 今回は、 名前定義 を使って範囲に名前をつけることで、そんなゴチャゴチャを解消し、シンプルでわかりやすい記述にしてみたいと思います。
名前定義 の定義の方法
名前定義 の方法は、A1セルの上にある 名前ボックス と呼ばれるところに直接名前を書き込むことで簡単にできてしまいます。名前を定義したい単一セル、または、セル範囲を選択しておいて、名前ボックスをクリックし、つけたい名前を入力します。（今は、B2セルを選択していますので「B2」と表示されています）
この名前ボックスをクリックして入力モードとしておいて、名前を入力します。
定義した名前を、修正したり削除したりするときは、alt M N と順に入力して、名前の管理ダイアログから行います。
修正するときは、修正したい名前の行を選択して、編集(E)…をクリックして編集します。
そして、この名前定義を使うことで、 コピー での 参照ズレ も心配しなくてよくなるという、さらに大きなメリットもあります。
ワークブック workbook 内で使う定数は、例えば 消費税率 割引率 値の最大値、最小値 参照先のシート名 マスターテーブル名 など色々ありますが、式の中に直接書くと、訂正する時に式を直接書き換える必要が出てきます。
訂正箇所が一つなら簡単にできますが、複数のworkbookになってくると、もうお手上げなくらい多くの時間と労力がかかりますし、もうダメだー！ということにもなりかねません。
最初は面倒でも、出来るだけ、定数には名前定義で名前をつけてから式を書いておくことで、式の見やすさがアップし間違いにくく、間違いを発見しやすくなります。
そして何より定数の変更には手間が掛からなくなるという大きなメリットを感じることになるでしょう。
名前定義を使った式
どうですか、シンプルになりましたね。
D3の式は、C３の鉛筆で通常単価表を検索し１５０円を探し出しています。
D4の式では、C4の鉛筆で割引単価表を参照し１３５円を探し出しています。
名前定義 の再利用
そして、この名前定義の機能で定義した名前は、他のワークブックにコピーして流用できるのです。
名前定義 専用のシートを作成して、このシートのセルに名前定義をしておきます。そして、このシートを別のワークブックにコピーすることで、シートのセルを参照している名前定義がコピー先のブックにも全てコピーされ、そのまま使えるのです。
表示された 移動またはコピー ダイアログで コピーを作成する を必ずチェック、移動先ブック名に新しいブックを選択してOKを押す。ちなみに、コピーを作成するにチェックを入れずにOKを押すと、シートが移動してしまいますので、もとのエクセルシートを保存しないようにお願いします。
間違ってしてしまったときは、移動先のシートを元のエクセルBookにコピーし直せばOKです。
このように、いつも使っている名前、たとえば税率などの定数をシートに定義しておき、新しく編集を始めるときに、Bookにシートのコピーで名前定義シートをコピーしてから行うことにすれば、毎回定義し直す必要がなく、どのBookでも同じ名前を使った式を作ることが出来るのです。
定義する名前の工夫
こんな運用はどうでしょうか。
例えばグループで共有する標準の定数シートを作成して、グループ全員で同じ名前を共有するのです。各自で定義する名前と重複するのを防ぐために、標準の名前は先頭にアンダースコアをつけて定義しておいて、各ブックで自由に使える定義は、普通に定義して使えば、重複の心配もなく使えます。
検索範囲を名前定義で切り替える
標準の価格表と割引の価格表を切り替えて使いたいときなど、名前定義をINDIRECTで切り替えて使うことが出来ます。
このやり方は、名前定義 INDIRECT でVLOOKUP の検索範囲を切り替えるでも説明しています。
名前にしておけば、INDIRECT関数で切り替えて使うこともできますから、相当に便利です。
コメント