久々にExcel計算式に嵌る!? VLOOKUPは大嫌いだっ!
- 2021.10.30
- ソフトウェアあれこれ 活き活き生活?

久々にExcelで嵌る!?
お恥ずかしながら、Excelなんぞ、あまり計算式を入れないで、表を作るのに特化した使い方をしている(そういう人、結構多いよね)。
たまたま、人が作成したExcelをいじっていたら、滅茶苦茶嵌ってしまったというお話。
おいらの場合は、どちらかというと、計算式+関数云々より、マクロ派なのだ。なので、大してExcel関数なんぞは知らない。
関数で嵌ってしまうと、マクロに逃げてしまうクチだったりする。
プログラムが組める人ならば、嵌ってしまって考え込むより、プロシージャを入力した方が早いもんね。
で、今回は、どうしてもExcel関数を逃げるわけにはいかない事情があり、どうしても克服しなければならなかった。
別途PCアプリを作り、そこで計算式+関数の入ったExcelファイルを作ることとなった。
なので、苦手な関数をしっかり使うこととなったのだ。
Excelマクロが作れるアプリだとよかったんだが、ライブラリーでExcelマクロまで組み込める物が存在しないみたいだ。
VLOOKUPって嫌いな関数の1つだ
Excelをフル活用していない理由に、関数がよく分からないことにある。
「VLOOKUP」なる関数なんぞ出た時にゃ、虫唾が走ってしまうのだ。なので、使いこなせる人を、尊敬してしまう。
しかし、食わず嫌いなのもなんなので、頑張って克服してみた(流石に、食べ物の食わず嫌いは、大人になっても直らないが…)。
VLOOKUPは参照する便利な関数だ
Excelマスターな人は、常識な関数ではあるが、おさらいがてら纏めてみる。
VLOOKUP は、テーブルまたは行別の範囲で検索する必要がある場合に使用します。 たとえば、部品番号で自動車部品の価格を検索したり、従業員 ID に基づいて従業員名を検索したりします。
その最も簡単な形式で、VLOOKUP 関数は次のようになります。
=VLOOKUP(検索する場所、返す値を含む範囲内の列番号、近似値または完全一致を返します。1/TRUE、または 0/FALSE として示されます)。
(中略)
=VLOOKUP(ルックアップ値、ルックアップ値を含む範囲、戻り値を含む範囲内の列番号、近似一致 (TRUE)、または完全一致 (FALSE))。
分かりやすく書くと、VLOOKUP(参照する値, 参照する範囲, 範囲内の列番号, 一致する条件(TRUE、またはFALSE))となる。
「参照する値」は、数値だけでなく、文字列でもOK。
「範囲内の列番号」は、その前に指定した「参照する範囲」の左端から数えて何番目の列という意味。例えば、「B2:H15」と範囲を指定した場合、B列が列番号1となり、2はC列、3はD列…となるのだ。結構、この列番号というのが嵌りどころの1つだったりする。
一致する条件は、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の嵌るポイントその2
実は、他にもVLOOKUPで嵌りどころがある。範囲指定する重要だったりするのだ。
例えば、VLOOKUPで文字123456を見つけ、その横にある文字aを表示させたい場合。
一見、間違いがないように見えるのだが、これも間違い。この場合、範囲指定がA5:D7となっているが、必ず、検索させる列が左側になるように指定するのだ。
この場合は、C5:D7に修正するのが正しい。そうすると、範囲内の列番号も4から2に修正しないと、エラーになってしまう。
それなら、C列のみ選択すればよいではないかと思うのだが、それも間違い。表示させたいセルの列も含める形にしないと、このVLOOKUPはダメなのだ。奥が深いぜ、VLOOKUP!
ちなみにマクロにしたら…
VLOOKUPをマクロにしたら、どうなるか考えてみた。
上の例だと、for~、またはwhileなどを使ってループさせ、if (セルが文字列123456)だった場合、隣のセルを表示…とでもなるかな。
VLOOKUP関数をマスターした今となっては、マクロを組んで云々とするのは、アホ臭くなってしまった。使える物は何でも使うのが一番いいようで。
-
前の記事
やっとマウス買った 2021.10.28
-
次の記事
2022年ドローン激動の年 その1 2021.10.31