VLOOKUP関数の実務的な使い方３です。
以前の記事に記載した通り、
この関数を使う時はよくエラーが
起こってしまう関数です。
エラー対策は下記をご参照下さい。
またうまく目的のデータを拾えない
こともよくあります。
それを踏まえて
今回もVLOOOKUPを使いこなすために
セットで知っておくと便利な技を
ご紹介します。
エクセルは触りながら学ぶのが一番なので、
今日も無料サンプルをお配りします。
是非こちらを触りながら
記事をご覧になってみて下さい。
ファイルを見て触りながら読んでいただけると
理解が深まりますし、仮に理解が全てできていなくても、
触ることで理解ができたりします。
うまくいけばほぼそのまま
あなたの仕事に転用できるかもしれません。
第三引数の列番号指定を可変にする
VLOOKUP関数を使う時に下記のように
式を横にコピーすると列番号が直数字のため
可変になっておらずうまくコピーできないこと
ありますよね？マニュアルでイチイチ
修正するのは今日で卒業できます。
COLUMN関数で該当の列番を可変にする
COLUMN関数とはA列から数えて
指定したのセルが何列目か
を返してくれる関数です。
VLOOKUP関数を使う時は
A列から何列目かではなく、
VLOOKUP関数上の範囲の一番左から数えたいので
COLUMN関数の引き算を使って求めます。
4月は"３"列目
ポイントは起点側を絶対参照（＄マーク）
で固定することです。
それからCOLUMN関数で使う行は
ラベルの行は消す可能性が低いので
5行目をつかいます。
これを横にコピーすると自動で列番号が
変わります。
5月は"4"列目
6月は"５列目"
このCOLUMN関数を
VLOOKUP関数の第三引数の所
に入れ込むと正しいデータが反映されました。
6月に入っている計算式です。
これによる最大のメリットは
列を削除しても式が壊れないので
メンテが原則不要になることです。
サンプルを使って、
試しにどこか削除してみて下さい。
重複データを識別して検索する
全て異なるデータであれば良いのですが、
1つの列に重複データがあることないでしょうか？
例えば田中さんという苗字が2回でてきているが
名前が違うので区別したい、
あるいは同じ商品名が2回出てきているが、
旧モデルと新モデルが違うので区別したい
なんてことないでしょうか？
こういう時にエクセルは
一番初め（上）にでてくる
データを拾ってきてしまいます。
それぞれ区別して該当データを拾いたい時
はどうしたら良いのでしょうか？
COLUMN関数を使って重複データを区別
これを実現するのにCOUNTIF関数を使います。
この関数はある範囲の中で同じデータが
何回登場したかを表してくれます。
下記のように作業列を追加して
番号を振ります。
具体的に田中さんの１人目を１、
田中さんの5人目を５と表示するにはどうすれば
良いのでしょうか？
ポイントは一番上の名前があるセルを
固定することで式を下にコピーすると
一番上からそのセルまでの中で何回同じ名前が
出たか数えられるようになります。
そうしたら後は
オリジナルの名前の列に今作業列で追加した
番号を追加することで同じ名前でも
区別ができるようになります。
これを実現するのはアンド関数を使います。
とても簡単な関数です。
"田中１"というセルの式はどうなっているかというと
単純にくっつけたいセルとセルの間に
"&"を入れるだけです。
そうすると5人目の田中さんもきちんと
区別されて該当するデータ（部署名）を
拾ってきてくれるようになりました。
注意事項は２つです。
１つは検索するキーを
元の名前の列ではなく、
番号を追加した各名前が区別できた列（J列）を
キーにします。
2つ目はVLOOKUP関数の列の始まりを
新しい名前の列（J列）から
初めるようにして下さい。
なぜならVLOOKUP関数は
検索するキーに該当する列をデータ範囲の中で
一番左になるように設定しないといけないからです。
さもないとエラーになりますので気を付けてください。
まとめ
VLOOKUP関数を4月から3月まで横にコピーしても
第三引数の列番号の設定が直打ちの場合、
うまくデータが拾えません。
このように列番号を可変にするにはCOLUMN関数を
使ってみて下さい。
また、データ範囲の中で重複データがあることがあります。
この状態でVLOOKUP関数を使うと一番初めに
該当したデータを拾ってきてしまいます。
重複データをきちんと区別して検索してもらいたい時は、
作業列を追加してCOUNTIF関数で重複データにナンバリングして
区別をすることができます。
実際に手を動かす重要性
学生の時、 教科書を読みこんで理解できたとしても
問題が意外と解けない経験なかったなんて
経験ないでしょうか？
仮に今何となく理解できていても、
1週間後に自分で再現してみて下さい。
意外とできないことって多くないですか？
ヒトの記憶力は自分が思っているより低いのです。
なので、今回で言えば内容を読み込むよりも
とにかくエクセルを開いて触ってみて下さい。
例えばこの無料の表をコピーして
自分の管理表に転用してみたりするのも良いですね。
そうするとわからないことが出てきたりするので
そこだけ調べてみて下さい。
私はこの繰り返しをすることで
時間が経過しても使える
自分のスキルになってきました。
