Excel コード表にない値を見つけ出す VLOOKUP関数/COUNTIF関数

Exceで知っておくと便利な関数のご紹介。今回は表の整形ではなく、エラーチェック系のテクニックです。

Excelしか入っていない環境のときや、わざわざAccessにデータをいれるまでもない、といったときに便利でしょう。

その関数はVLOOKUP。

アビバのCMでも紹介されている関数で、Excel使いの人の中ではメジャーな関数だと思います。一口で言うと、コード表から値を引っ張ってくる関数です。この関数が主に活躍するのは、CMにある通り、本来は入力シーンで、入力する画面ではコード入力、その隣の列にコードの値を表示する、といったものです。以下に、書式と引数のポイントを挙げます。特に引数で注意するべきなのは、範囲と検索の型だと思います。

書式

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

検査値

入力するコード

範囲

いわゆるコード表。コードでソートをかけておくことが重要。ちなみにコードは範囲の中で一番左にくるように設定します。ちなみに大抵VLOOKUPは縦方向へドラッグして使うことが多いと思いますので、ドラッグしても参照する範囲がズレないように、「$」などをうまく使って、範囲を固定しましょう。

列番号

上記コード表の範囲で、何列目にあたる部分を表示させたいか。範囲の中で一番左にくる列番号は1になります。

検索の型

検索値 と完全に一致する値だけを検索するか、その近似値を含めて検索するかを、論理値で指定。この引数を省略すると、デフォルトはTrueに設定されているので、近似値をひろってきてしまいます。しかしコード入力する際、近似値などという厳密さのかけるものなど欲しくない場合が殆どなので、フツーFalseを設定して、コード以外のものが検査値にあれば、わざとエラーを見せる、といった使い方の方が多いのではないかと思います。このオプションのことをうっかり忘れていると、痛い目をみると思いますので、ご注意。なまじ省略可能な引数だからコワイんですよねー。

というわけで、入力シートへ仕込んでおくと便利なこの関数を、「即座にエラー表示となって間違ったコード入力を発見できてしまう」という捉え方をしている私は、後追いでのコードのイレギュラー値の抽出シーンなどでも多用しています。たとえば、Excelデータを渡されて、「A表にあって、B表にない行はどれかな？」とか頼まれるとき。

▲「範囲」はドラッグしてもズレないように、「$」を入れて工夫してください。

他人から咄嗟に渡されるものはExcelのものが殆どですので、列数がたくさんあって、Accessに入れるのも面倒くさいなぁ、と思えば、Accessのクエリの代用としてVLOOKUP関数を利用します。ExcelのA表のヨコにでも、B表のコード一覧にあるかないかのフラグを立ててあげれば、依頼人はそれなりに満足することが多いわけですから、既存のExcel表をそのままサクッと利用できるなら、その方がラクなわけですね。

Excelのまま、ちゃちゃっとB表をコード表に見立てて、B表のコードに当たるものをソートし、A表のコードを検査値と見立ててVLOOKUPを使います。そうした場合の検索の型は当然False設定で、わざとエラーを表示して、エラー表示をフラグ代わりに利用します。

計算式の結果は「フィルタ」機能で引っかかりますので「#N/A」で、エラーの行をピックアップすることも可能です。「#N/A」をそのまま表示するのは見栄えが悪いというのなら、「ISERROR」関数などで囲めば、論理値になります。

さらにVLOOKUP関数を知らないようなExcelの操作が危なそうな人に、調査結果を渡すときには「形式を選択して貼り付」で「値」オプションを選んで、値を固定化（参考)ておく、などの配慮をしておくと完璧でしょう。まぁわざとこのように値を固定化してExcelを渡すと、「ちょっと！俺、式も知りたいんだけど！」と言われることもありますが、そのときはそのときで、式の入ったモトネタを渡してあげればいいと思います。

ちなみに「コード表にない値を見つけ出す」という意味では、COUNTIF関数でも代用できるワザですね。、COUNTIF関数の引数として、B表のコードを範囲に指定して、A表のVLOOKUPでいうところの検査値を、検索条件として設定すれば、B表にないものはすべてゼロになるわけです。この「0」をフィルタにかけてあげれば、B表にないものの一覧は簡単に抽出できますね。COUNTIF関数も面白いと思いますので、いずれまた取り上げたいと思います。

いずれにせよこのテのワザのひとつをイディオムとして覚えておけば、役に立つでしょう。

mubmle→それにしてもこのテの作業の手伝いで「フラグ不要。色つけておいて」と頼まれることほど、萎えることはありません。「え。フラグ消しちゃっていいんですか？フラグが立っていれば、フィルタで抽出するの簡単ですよ？」と愕然として思わず念押しするわけですが、「『色をつける』って話になっているから、フラグはいらないや」とのお返事。

だーかーらー、色なんて飾りなんだってば！！

と思いつつも、そこまでハッキリ云われれば、単なるヘルプの身としては何も云えません。結構行数のある表だと、「どんな手作業をやるのか知らないけど、遅くて、不正確になるだろうなー」と想像もつくわけです。まーそれだけ要領の悪いことをやっているからこそ、Excel/Accessをちょっと使えば瞬殺の程度の作業を、部外者の私に頼んでくるわけですが。