Shimesaba-ba blog

大手日系企業で経営管理や経理業務を行い、英語や海外とは無縁な状態から海外で業務を行い、グローバル企業へ転職。英語、経営管理、ビジネスについての経験や学んだことをアウトプットします。

VLOOKUP関数の実務的な使い方3、列番号の可変と重複データの識別

VLOOKUP関数の実務的な使い方3です。

以前の記事に記載した通り、

この関数を使う時はよくエラーが起こってしまう

関数です。エラー対策は下記をご参照下さい。

www.shimesaba-ba.com

 

またうまく目的のデータを拾えないこともよくあります。

 

www.shimesaba-ba.com

 

今回もVLOOOKUPを使いこなすために

セットで知っておくと便利な技をご紹介します。

 

エクセルは触りながら学ぶのが一番なので、

今日も無料サンプルをお配りします。

 

drive.google.com

是非こちらを触りながら

記事をご覧になってみて下さい。

 

ファイルを見て触りながら読んでいただけると

理解が深まりますし、仮に理解が全てできていなくても、

触ることで理解ができたりします。

うまくいけばほぼそのまま

あなたの仕事に転用できるかもしれません。

f:id:Shimesaba-ba:20200920145312p:plain

 

 

 

第三引数の列番号指定を可変にする

 

VLOOKUP関数を使う時に下記のように

式を横にコピーすると列番号が直数字のため

可変になっておらずうまくコピーできないこと

ありますよね?マニュアルでイチイチ

修正するのは今日で卒業できます。

f:id:Shimesaba-ba:20201010201219p:plain



COLUMN関数で該当の列番を可変にする

COLUMN関数とはA列から数えて

指定したのセルが何列目か

を返してくれる関数です。

 

VLOOKUP関数を使う時は

A列から何列目かではなく、

VLOOKUP関数上の範囲の一番左から数えたいので

COLUMN関数の引き算を使って求めます。

 

f:id:Shimesaba-ba:20201010201649p:plain

4月は"3"列目

f:id:Shimesaba-ba:20201010202118p:plain

 

 ポイントは起点側を絶対参照($マーク)

で固定することです。

それからCOLUMN関数で使う行は

ラベルの行は消す可能性が低いので

5行目をつかいます。

 

 

これを横にコピーすると自動で列番号が

変わります。

 

5月は"4"列目

f:id:Shimesaba-ba:20201010202332p:plain


6月は"5列目"

f:id:Shimesaba-ba:20201010202408p:plain

 

このCOLUMN関数を

VLOOKUP関数の第三引数の所

に入れ込むと正しいデータが反映されました。

f:id:Shimesaba-ba:20201010203215p:plain



6月に入っている計算式です。

f:id:Shimesaba-ba:20201010203130p:plain


これによる最大のメリットは

列を削除しても式が壊れないので

メンテが原則不要になることです。

 

サンプルを使って、

試しにどこか削除してみて下さい。

 

重複データを識別して検索する

全て異なるデータであれば良いのですが、

1つの列に重複データがあることないでしょうか?

 

例えば田中さんという苗字が2回でてきているが

名前が違うので区別したい、

あるいは同じ商品名が2回出てきているが、

旧モデルと新モデルが違うので区別したい

なんてことないでしょうか?

 

こういう時にエクセルは

一番初め(上)にでてくる

データを拾ってきてしまいます。

f:id:Shimesaba-ba:20201010205756p:plain

それぞれ区別して該当データを拾いたい時

はどうしたら良いのでしょうか?

 

COLUMN関数を使って重複データを区別

これを実現するのにCOUNTIF関数を使います。

この関数はある範囲の中で同じデータが

何回登場したかを表してくれます。

 

下記のように作業列を追加して

番号を振ります。

f:id:Shimesaba-ba:20201010210201p:plain

 

 

具体的に田中さんの1人目を1、

田中さんの5人目を5と表示するにはどうすれば

良いのでしょうか?

 

ポイントは一番上の名前があるセルを

固定することで式を下にコピーすると

一番上からそのセルまでの中で何回同じ名前が

出たか数えられるようになります。

f:id:Shimesaba-ba:20201010210436p:plain


そうしたら後は

オリジナルの名前の列に今作業列で追加した

番号を追加することで同じ名前でも

区別ができるようになります。

これを実現するのはアンド関数を使います。

 

f:id:Shimesaba-ba:20201011135801p:plain

 

とても簡単な関数です。

 "田中1"というセルの式はどうなっているかというと

単純にくっつけたいセルとセルの間に

"&"を入れるだけです。

 

f:id:Shimesaba-ba:20201011140225p:plain

 

そうすると5人目の田中さんもきちんと

区別されて該当するデータ(部署名)を

拾ってきてくれるようになりました。

 

注意事項は2つです。

 

1つは検索するキーを

元の名前の列ではなく、

番号を追加した各名前が区別できた列(J列)を

キーにします。

 

2つ目はVLOOKUP関数の列の始まりを

新しい名前の列(J列)から

初めるようにして下さい。

 

なぜならVLOOKUP関数は

検索するキーに該当する列をデータ範囲の中で

一番左になるように設定しないといけないからです。

さもないとエラーになりますので気を付けてください。

f:id:Shimesaba-ba:20201011140535p:plain

 

 まとめ

VLOOKUP関数を4月から3月まで横にコピーしても

第三引数の列番号の設定が直打ちの場合、

うまくデータが拾えません。

このように列番号を可変にするにはCOLUMN関数を

使ってみて下さい。

 

また、データ範囲の中で重複データがあることがあります。

この状態でVLOOKUP関数を使うと一番初めに

該当したデータを拾ってきてしまいます。

重複データをきちんと区別して検索してもらいたい時は、

作業列を追加してCOUNTIF関数で重複データにナンバリングして

区別をすることができます。

 

 

実際に手を動かす重要性

 

学生の時、 教科書を読みこんで理解できたとしても

問題が意外と解けない経験なかったなんて

経験ないでしょうか?

 

仮に今何となく理解できていても、

1週間後に自分で再現してみて下さい。

意外とできないことって多くないですか?

ヒトの記憶力は自分が思っているより低いのです。

www.shimesaba-ba.com

 

なので、今回で言えば内容を読み込むよりも

とにかくエクセルを開いて触ってみて下さい。

 

例えばこの無料の表をコピーして

自分の管理表に転用してみたりするのも良いですね。

 

そうするとわからないことが出てきたりするので

そこだけ調べてみて下さい。

 

私はこの繰り返しをすることで

時間が経過しても使える

自分のスキルになってきました。