久々にExcel計算式に嵌る!? VLOOKUPは大嫌いだっ！

久々にExcelで嵌る!?

お恥ずかしながら、Excelなんぞ、あまり計算式を入れないで、表を作るのに特化した使い方をしている(そういう人、結構多いよね)。
たまたま、人が作成したExcelをいじっていたら、滅茶苦茶嵌ってしまったというお話。

おいらの場合は、どちらかというと、計算式＋関数云々より、マクロ派なのだ。なので、大してExcel関数なんぞは知らない。
関数で嵌ってしまうと、マクロに逃げてしまうクチだったりする。
プログラムが組める人ならば、嵌ってしまって考え込むより、プロシージャを入力した方が早いもんね。

で、今回は、どうしてもExcel関数を逃げるわけにはいかない事情があり、どうしても克服しなければならなかった。
別途PCアプリを作り、そこで計算式＋関数の入ったExcelファイルを作ることとなった。
なので、苦手な関数をしっかり使うこととなったのだ。
Excelマクロが作れるアプリだとよかったんだが、ライブラリーでExcelマクロまで組み込める物が存在しないみたいだ。

VLOOKUPって嫌いな関数の１つだ

Excelをフル活用していない理由に、関数がよく分からないことにある。
「VLOOKUP」なる関数なんぞ出た時にゃ、虫唾が走ってしまうのだ。なので、使いこなせる人を、尊敬してしまう。

しかし、食わず嫌いなのもなんなので、頑張って克服してみた(流石に、食べ物の食わず嫌いは、大人になっても直らないが…)。

VLOOKUPは参照する便利な関数だ
Excelマスターな人は、常識な関数ではあるが、おさらいがてら纏めてみる。

VLOOKUP は、テーブルまたは行別の範囲で検索する必要がある場合に使用します。 たとえば、部品番号で自動車部品の価格を検索したり、従業員 ID に基づいて従業員名を検索したりします。

その最も簡単な形式で、VLOOKUP 関数は次のようになります。

=VLOOKUP(検索する場所、返す値を含む範囲内の列番号、近似値または完全一致を返します。1/TRUE、または 0/FALSE として示されます)。

（中略）

=VLOOKUP(ルックアップ値、ルックアップ値を含む範囲、戻り値を含む範囲内の列番号、近似一致 (TRUE)、または完全一致 (FALSE))。

VLOOKUP 関数 (microsoft.com)

分かりやすく書くと、VLOOKUP(参照する値,　参照する範囲,　範囲内の列番号,　一致する条件(TRUE、またはFALSE)）となる。

「参照する値」は、数値だけでなく、文字列でもOK。

「範囲内の列番号」は、その前に指定した「参照する範囲」の左端から数えて何番目の列という意味。例えば、「B2:H15」と範囲を指定した場合、B列が列番号1となり、2はC列、3はD列…となるのだ。結構、この列番号というのが嵌りどころの１つだったりする。

一致する条件は、FALSEにしておけば、完全に一致しない物以外は弾ける。TRUEはワイルドカード的な使い方のようだが、どうも、こちらの思惑通りな感じにはならないことが多そう。TRUEは、あまり使わない方がいいかもね。

VLOOKUPの参照値に嵌る

VLOOKUPで嵌ってしまった原因は、前に説明した「一致する条件」だけでなく、「参照する値」でも嵌ってしまったのだ。

Excelってのは、数値でも、文字列でも入れられるのだが、時として、それが訳の分からない現象を引き起こすことがあるのだ。

例えば、こんな感じ↓

数値123が参照値の場合。セルA4に数値123があるので、そのお隣の「hello」が表示されているという訳。

しかーし。セルA4に数値123を文字列123(セルに「’123」と入力する)に変えたらどうかというと…

しっかり、エラーになった。これを解消するには、セルA4の文字列123を数値123にするか、VLOOKUPの方を「VLOOKUP(“123″～」と文字列を参照するように直すかのどちらかだ。
簡単なものでテストすればよかったのだが、人様が考えてくれたもので初めからテストしたので、相当嵌りまくったのだ。

人様が考えてくれたものは、CONCATENATE関数なる物を使い、セルに書いてある内容を合体させていたのだが、コイツもかなりの曲者だった。セルには、数値が入っていたのだが、コイツでセルに入っている数値を合体させると、何と文字列になってしまうらしい。つまり、セルに数値123、別のセルに数値456が入っていると、見かけ上は123456となるのだが、コイツは数値ではなく、立派な(?)文字列なのだ(「エー加減にせーや」と言いたくなってしまう)。
ついでに、このCONCATENATE関数は古いので、CONCAT関数がお勧めのようだ(使い方などは一緒)。

まぁ、分かってしまえば、何てことはない。

VLOOKUPの嵌るポイントその２

実は、他にもVLOOKUPで嵌りどころがある。範囲指定する重要だったりするのだ。

例えば、VLOOKUPで文字123456を見つけ、その横にある文字aを表示させたい場合。

一見、間違いがないように見えるのだが、これも間違い。この場合、範囲指定がA5:D7となっているが、必ず、検索させる列が左側になるように指定するのだ。
この場合は、C5:D7に修正するのが正しい。そうすると、範囲内の列番号も4から２に修正しないと、エラーになってしまう。

それなら、C列のみ選択すればよいではないかと思うのだが、それも間違い。表示させたいセルの列も含める形にしないと、このVLOOKUPはダメなのだ。奥が深いぜ、VLOOKUP！

ちなみにマクロにしたら…

VLOOKUPをマクロにしたら、どうなるか考えてみた。

上の例だと、for～、またはwhileなどを使ってループさせ、if 　（セルが文字列123456）だった場合、隣のセルを表示…とでもなるかな。

VLOOKUP関数をマスターした今となっては、マクロを組んで云々とするのは、アホ臭くなってしまった。使える物は何でも使うのが一番いいようで。