【エクセル】データの変更があっても大丈夫なVLOOKUPの使い方

エクセルの教本でも、初心者から1ステップあがる関数として上げられているのがVLOOKUP関数です。

作業列を追加するだけで、指定したセルの値を取得し、VLOOKUP関数を入力したセルに反映してくれるので表を作る時に重宝する関数です。

しかし、試しに作成した場合や変更される可能性の高い表の作成する場合、列番号指定がめんどくさいのも事実です。

データ位置の変更で列番号の指定を変えないといけないので、作成後はあまり変更したくなくなります。

・・・作成段階から煮詰めれば良いことではありますが。

しかし、変更しやすい柔軟性の高い表が便利なのも事実です。

そんな決め打ちしていない表でVLOOKUPを使うにはどうするのか？

先日社内（の自分の中だけ）で起きていた事です

管理方法の変更によるスリム化？

先日、自社の工事部の施行記録をしている集計表を、管理方法の変更により作り替えることになりました。

以前に書いた記事で紹介したものではあるんですが。

作業時間が50分短縮された事例ーエクセルシートを作るための考え方ー【業務効率化】

ダウンロードしたデータを貼り付ければほぼ作業が終わるので便利だったのですが、動作の重い関数を複数埋め込んでいるのと、1ヶ月分の現場データを入れ込んでいるのでひたすら重い。

集計した内容を使って何かをするのに、機能的とは言えない状態でした。

そこで管理方法を変えると共に、セルの参照方法をINDEX関数とMATCH関数の複合から、VLOOKUP関数一本にすることにしました。

合わせて、このエクセルシートにデータ入力はエクセルではなくHTMLフォームでやってもらっていたので、そちらも見直す事に。

なので、エクセルで使用するデータ列が変動する可能性が大いにあったわけです。

VLOOKUP関数は列番号を指定する

HTMLフォームをほぼ作り直す事にしたので、エクセルシートの変更は別のスタッフに依頼。

メインで使うのもこのスタッフなので、不具合が起きても自分で修正できるようにという意味もありました。

なので、VLOOKUP関数を使用するという事だけ決めて、あとは相談を受けつつ作り上げていきました。

で、実際に使い始めるとやはり変更した方が良いところや不具合がぽろぽろと。

その中で一番面倒だったのが、VLOOKUP関数の調整でした。

VLOOKUP関数の引数は、

VLOOKUP(検索値, 範囲, 列番号, 検索の型)

となります。

検索の型はおいといて、VLOOKUP(A, B, C, 検索の型)とすると、「Bの範囲にあるセルの中で、Aという値がある行の、C番目の列のセルの値を取り出して！」という命令を出す関数です。

・・・余計分かりにくくなった気もしますが、そういう事です笑

数字での指定は絶対指定になってしまう

本題に戻って、今回のデータ列の変更を行う場合に問題となるのは、列番号の指定を普通にすると「絶対指定」となってしまう事です。

例えば、

VLOOKUP(A1, A5:Z5, 5, FALSE)

のような指定を行いますが、この列番号の「5」が常に5列目を指定します。

データ列の変更で1列目から4列目を削除したり増やしたりすると、実際に指定したい列が前後(左右の方が分かりやすいですかね？)にずれてしまうのです。

そのような列の操作を行った場合、VLOOKUPの列番号も全て変更しなければなりません。

データ量が少なかったり、列数が少なければ手作業でも良いかなとは思います。また、多くても同じ列のセルを取得するのであれば、最初のセルを変更してコピペでも良いですが。

まぁめんどくさいです笑

変更の回数が少なければまだ耐えられますが、それでも面倒笑

ということで、列番号の指定を関数で行います。

列番号を取得するCOLUMN関数

やる事は簡単で、列番号を指定するところにCOLUMN(指定したい列の見出しセル)と入力します。

この関数は入力したセルの列番号を取得する関数です。

何でもないところに=COLUMN()と入力すると、その列番号がセルに表示されます。

COLUMN関数の引数にセルを指定する指定すれば、そのセルの列番号を取得してくれます。

エクセルで削除や追加をすると、関数や計算でセル番号を使用している場合、その操作に基づいてセル番号が増減してくれます。

なので、ほぼ自動でVLOOKUP関数で取得したい値のあるセルがある列が変わってくれるという事です。

これでデータの変更があっても操作が楽になります。

ちょっとしたひと手間ですが

なかなか無いかなと思いつつ、ひと手間かける事で後の作業が楽になる例にちょうど良いかなと。

記事にすることにしました。

エクセルで表を作成していると、このような微妙にめんどくさく感じる操作があることが良くあります。

そのような時にも、ちょっとした関数を利用する事で改善されることも多々あるので、ちょうど良い機能や関数が無いか調べてみると良いですよ。

エクセルシート作成のご依頼受付中！

株式会社東建工業－インターネット事業部－では、エクセルシートの作成代行もしています。 ・日頃使う日報などの書式の作成 ・備品などの管理で使用する表 ・複雑な計算が必要な業務書類 など、ご要望に合わせて作成します。すでに使用されているシートの変更・調整も可能です。 ・入力業務を効率化したい ・管理を楽にしたい といったお悩みがあればぜひご相談下さい！

お問い合わせはコチラから！