Excelを使って関数や表計算をしていると、同じ計算を隣のセルにコピー（オートフィル）したときに、範囲がずれてしまうという経験、よくありませんか？
特に VLOOKUP や INDEX/MATCH のような関数で固定された検索範囲を使いたい場合、セル参照がずれると正しい値が返されなくなってしまいます。$を使えばよいのですが、$A$1:$B$2などになりとても読みにくいです。
また、検索範囲に1行追加された時などに全ての範囲を更新しないといけないのも面倒です。
この記事では、「名前の定義と参照」という機能を使って、オートフィルでも範囲がずれず、行の追加でも更新不要にする方法を紹介します。
まずは、良く起きる現象の例
たとえば次のような数式を使っていたとします。
=VLOOKUP($G$2,A2:D4, 2, FALSE)
この式を下のセルにコピーすると、参照先が自動で変化し、
=VLOOKUP($G$2, A3:D5, 2, FALSE)といったように、検索範囲A2:D5 が A3:D6
本来固定したいのに、Excelの相対参照の仕組みによって、意図しないズレが生まれてしまうわけです。
ありがたい機能だけど、こういう時は有難迷惑。
絶対参照で対応する方法（基本）
ひとつの対策として「絶対参照」があります。 $ を使って参照先を固定する方法です。
=VLOOKUP($G$2, $A$2:$D$4, 2, FALSE)これならオートフィルしても範囲はずれません。
しかし、参照したい範囲のデータが増え、$A$2:$D$4$A$2:$D$5
1つずつ手作業で範囲更新は大変…
名前の定義とは？Excelの便利な参照機能
そこで登場するのが「名前の定義」です。
名前の定義とは
セル範囲にわかりやすい名前をつけて参照できる機能です。
たとえば $B$2:$D$5 という範囲に「商品一覧」という名前を付けておくと、
=VLOOKUP($G$2, 商品情報, 2, FALSE)と書くだけで済み、かつ参照も固定されます。
また、データが増えた時には、「商品一覧」という名前の範囲を更新するだけのため、関数本体を変更する必要がなくなります。
名前の定義の設定方法
名前の管理で設定
- [数式] タブ → [名前の管理]
- 左上の[新規作成] をクリック
- 「名前」と「参照範囲」を設定して保存
たとえば、名前：「商品情報」
参照範囲：=$A$2:$D$5
これでOKです。
名前の定義を使うメリット
- オートフィルしても範囲がずれない
- 関数が読みやすくなる
- 複数の数式で同じ範囲を再利用できる
- 範囲が変更になっても名前の定義を変更するだけで済む
名前の定義は、単に見た目の美しさだけでなく、作業効率やミス削減にも大きく貢献します。
実践：名前の定義でVLOOKUPをずらさず使う
例：商品コードから商品名を取得
たくさんある商品の情報を商品コードから抽出する関数です。左の商品情報の一覧から、商品コードだけが分かる状態の時に、いちいち探さなくても、プルダウンで商品コードを選べば情報をピックアップできます。
- A列に商品コード
- B列に商品名
- C列に価格
- D列に在庫数
A2:D5に「商品情報」という名前を定義- 次のような式を使います：
=VLOOKUP($G$2, 商品情報, 2, FALSE)商品が増えていっても名前の定義の「商品情報」範囲を変更するだけで全ての関数の範囲が更新されます。
INDEX・MATCH関数でも活躍！
名前の定義は、INDEX や MATCH を使った複雑な参照でも力を発揮します。
=INDEX(商品情報, MATCH(A2, 商品コード列, 0), 2)「商品コード列」に別途名前を付けておけば、見やすくて保守しやすい数式になります。
まとめ：名前の定義を活用して、範囲ずれのないオートフィルを実現しよう
オートフィル時の範囲ずれを防ぐには、名前の定義による絶対参照がとても効果的です。
数式が読みやすくなり、複雑な関数もスマートに管理できるようになります。
シートが多い時は特に、名前の定義が大活躍！
本記事のポイント
- オートフィルでは相対参照がずれる原因になる
- 絶対参照で固定もできるが管理が大変
- 名前の定義を使えば、わかりやすく安定した参照が可能
- VLOOKUP、INDEX/MATCH、テーブルなどあらゆる場面で応用できる
Excelの見通しを良くし、将来的な修正や拡張もラクになる「名前の定義」。
今日からぜひ取り入れてみてください。作業効率が大きく変わります！
コメント