Help us understand the problem. What is going on with this article?

[Excel]VLOOKUPを構造化参照+INDEX+MATCHで置き換えて保守力を上げる

More than 1 year has passed since last update.

VLOOKUPの課題

  • 一番左の列しか検索のキーにできない
  • 列の順番が変わっただけで崩れる
  • そもそもA1:H100とか書かれてもどこを参照しているか分かりづらい
  • VLOOKUPのセルを見直すたびに式を読み解く時間がもったいない

VLOOKUPをもっと保守しやすくしたい。

お題

下記一覧から、町域CD「151010005」の町域を取得する。

住所csvsample.png

VLOOKUPだと

=VLOOKUP(151010005,D2:J10000,7,FALSE)

VLOOKUPを使いこなしていれば理解も早いが、これを半年後に見たときに「7とは?」とか「D列は何?」とか式の意味を思い出すだけでも辛い。
式が複雑になればなるほど辛い。
列の順番を入れ替えたりすると式を見直さないといけない。
また、検索範囲を10000行としたので10000行より多いデータがあるとNG。
D:Jと書くこともできるが処理速度が懸念。

INDEX + MATCH で少しすっきり

INDEX+MATCHで置き換える方法が有名。

=INDEX(J2:J10000,MATCH(151010005,D2:D10000,0))

VLOOKUPと違い、検索キーとなる列を一番左にするというような制約がないため、「住所CDを取得する」という変更にもすぐに対応できる。
列の順番が変わっても同じ式が使える。
ただ、やっぱり半年後に見たときに「J列とは?D列とは」ってなりそう。
行数制限や処理速度の問題もまだ残る。

構造化参照ですっきり

一覧がテーブルになっているのなら構造化参照を使ってしまう。

=INDEX(住所マスタ[町域],MATCH(151010005,住所マスタ[町域CD],0))

何を検索キーにして何を表示しようとしているか見てわかる。半年後でもたぶん分かる。
データが増えても検索範囲は自動的に適切な範囲になる。
さらにうれしいのは項目をサジェストしてくれる。テーブルを見ながら式を考える必要がない。
シートを跨いで式を作る時に大活躍。

↓こんな感じ
サジェスト.png

VLOOKUP+COLUMNで頑張ってみたりINDEX+MATCH+名前定義で頑張ってみたりした時もあったけど、今は構造化参照がマイブーム。
たくさん使いたい。

しかし自分しか理解できない式は使えない。
みんなが使うエクセルはまだまだVLOOKUPになる。
構造化参照、もっと浸透してほしい。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away