こんにちは。
今日は、VLOOKUP関数の応用編その1、という事で、複数の列に対してVLOOKUP関数を使うときにちょっとラクする方法をご紹介してみたいと思います。
まずは基本が大切ですので、まだVLOOKUP関数をよく理解出来ていないかな、と思う方は、下の記事を先に読んで練習してみてください。
上の練習問題をやって頂くと感じると思いますが、同じ関数を複数列に、何回も入力しなければいけないので非常に面倒くさいです。
でも、仕事でVLOOKUP関数を使うと、こういう場面がよくあります。
そこで、最初に入力する式をちょっとだけ工夫して、列方向に式のコピーをしてしまおうというのが今回の狙いです。
前回と同じデータを使いますので、すでにデータのダウンロードが済んでいる方はそちらをご利用ください。
まだデータのダウンロードをしていない方は、以下の内容をよくご確認のうえ、自己責任でダウンロードをお願いします。
ダウンロードデータを利用される方は、下記「ダウンロード」をクリックしてご利用ください。「VLOOKUP練習問題①」というファイル名のエクセルデータが、各パソコンの[ダウンロード]フォルダに入ります。
エクセルデータを開いた際、「保護ビュー」が表示された場合は下の記事を参考にご対応ください。
※スマートフォンやタブレットからのダウンロードは操作保証致しかねますので、パソコンからご利用いただくようお願いします。
データの確認
それではまず、データの確認をしてみましょう。
- 第1四半期
- 第2四半期
- マスタ
と、シートがと3つに分かれています。
パソコンをご利用の方は、それぞれの画像をクリックすると拡大表示されます。シート見出し（赤で囲んである部分）を確認してみましょう。
確認が終わったら、画面右上の「×」で拡大表示を終了できます。
第1四半期シートを完成させよう
それでは、第1四半期シートを使って確認していきましょう。
VLOOKUP関数で担当者を表示する
まずは担当者をVLOOKUP関数で自動的に表示させます。
この後、隣の販売店の列に式をコピーしたいので、ちょっとだけ工夫が必要です。
VLOOKUP関数の手順
それではVLOOKUP関数の挿入手順を確認していきましょう。
セルD4を選択する
まずは答えを表示したいセル（今回はD4）を選択します。
VLOOKUP関数のボックスを表示する
次にVLOOKUP関数を入力する［関数の引数］ボックスを表示しましょう。
入力モードが半角英数の状態で
＝vl
と入力し、上図のようにVLOOKUP関数の候補を表示させます。
この状態で
- キーボードの[tab]キーを押す
- 画面の青い部分をマウスでダブルクリックする
のどちらかを行い、VLOOKUP関数を確定させてください。
ボックスを使うのが苦手な方や、いつも自分がやっている方法でやりたい方は、いつもの自分のやり方でもちろん構いません。
操作自体にあまり慣れていない方、しっかりとした手順を覚えたい方などは、ご紹介している方法でやってみましょう。
VLOOKUPの文字が大文字になったら
- [fx]をマウスでクリックする
- キーボードで[Shift]＋[F3]を押す
のどちらかを使って［関数の引数］ボックスを表示しましょう。
引数を設定する
続けて引数の設定をおこないます。
引数については、「VLOOKUP関数 練習問題 Part.1」の記事で説明を入れていますので、悩んだ場合は再度そちらの記事をご確認ください。
VLOOKUP関数で設定する引数は
- 検索値
- 範囲
- 列数
- 検索方法
の4つですが、複数列で式を使いまわしたい時に工夫が必要なのは
- 検索値
- 列数
の2つです。
そのうち、式を立てる段階で工夫が必要なのは「検索値」のみです。
列数については、式を立てて隣の列にコピーした後に手入力で修正を加えます。
では早速内容を見ていきましょう。
「VLOOKUP関数 練習問題 Part.1」の記事で検索値を設定した際は、セルC4をクリックしただけで次のステップに進みました。
しかし今回は、式を立てた後に右横の列に式をコピーして使いたいです。
このときに考えないといけない事は、
「式のコピーをするとセル参照がズレる」
というエクセルの特徴です。
これを「相対参照」と言います。
エクセルのテキストを見たり、講座を受講したことがある人は見覚え・聞き覚えがあるかも知れませんね。
絶対参照と複合参照
式をコピーした時にセル参照がズレないようにするため、通常は「絶対参照」を使います。
では今回も、絶対参照で固定して大丈夫でしょうか？
答えは「×」です。
絶対参照でセルC4を固定してしまうと、右にズレなくなると同時に、下にもズレなくなってしまいます。それでは正しい答えが表示されません。
右にはズレて欲しくないけど下にはズレて欲しい
そういった場合に使うのが「複合参照」です。
複合参照の設定方法
早速設定方法を確認しましょう。
セルC4をクリックで選択した直後にF4を押すと「絶対参照」になりますが、続けてF4を押すと「複合参照」の状態に変わります。
上図を見るとわかるように、複合参照には
- C$4
- $C4
の二種類があります。
この2つの違いは、＄（ドルマーク）の位置です。
＄（ドルマーク）がどこにあるかによって、どっち方向を固定するのかが変わります。
今回は右に式をコピーした時にズレないように、列方向だけを固定したいので、
- $C4
を使います。
セルC4をクリックで選択し、F4を3回押せばOKです。
セルC4をクリック（↓）
F4を1回押す（↓）
F4を2回押す（↓）
F4を3回押す（↓）
上図の状態になったら検索値の設定は終了です。
続けて残りの引数（範囲・列番号・検索方法）をいつものように設定しましょう。
オートフィルで式をコピーする
式が完成したら、担当者欄を完成させましょう。
オートフィルを使って縦方向（行方向）に式をコピーしてください。
式をコピーして販売店を表示する
担当者欄の表示が完成したら、作成した式をコピーして販売店欄を表示していきます。
式のコピー
先ほど作成したVLOOKUP関数の式が入力されている列の先頭、セルD4を選択し、オートフィルを使って隣のE4にコピーしてみましょう。
セルD4を選択し、
セルの右下角に「＋」マークを表示させた状態で
隣のE4に式をコピーします。
結果を見てみると、D列と同じように担当者が表示されていませんか？
「何か間違えたのかな？」
と思う方もいるかも知れませんが、これで正しいです。
何故なら、式を立てた段階で列番号を2と設定しているからです。
手入力した数字は、式をコピーしても勝手に変わることはありません。
正しい答え（＝販売店）にするためには、自分で列番号を修正する必要があります。
列番号を修正する
では、列番号の修正方法を確認していきましょう。
［関数の引数］ボックスを再表示する
セルE4を選択し、
- [fx]をマウスでクリックする
- キーボードで[Shift]＋[F3]を押す
のどちらかの方法で［関数の引数］ボックスを再表示させます。
注意点
［関数の引数］ボックスを再表示させる際、セルD4とE4を両方選択した状態のままボックスを再表示させる方がよくいます。
必ずセルE4を選択する
のを忘れないようにしてください。
［関数の引数］ボックスを再表示させる際、よくやってしまう間違いなので、ぜひ気を付けてください。
列番号を修正する
［関数の引数］ボックスを再表示させたら、列番号を手入力で修正しましょう。
今回は、「2」を「3」に修正します。
数字を変更すると、［関数の引数］ボックスの下にある「数式の結果」という場所の答えが販売店の情報に変わっているのがわかります。
「数式の結果」は、「このあと［OK］を押して式を確定させるとこういう表示になりますよ」ということを確認できる場所です。
［OK］を押して式を確定し、オートフィルを使って最終行までコピーしてみましょう。
最終行まで正しく答えが表示されたでしょうか。
販売単価は式の使いまわしができない
同じように、販売単価も式を使いまわしたいなと考えるところですが、残念ながら販売単価は式を立て直す必要があります。
何故なら、範囲として選択する表が別だからです。
式の使いまわしができるのは、
- 同じ範囲を使う場合
ということになります。
問題の解答例
解答を確認したい方は、下記「ダウンロード」より解答ファイルをダウンロードしてご利用ください。
ファイルのダウンロードに抵抗がある方、スマホやタブレット等、ダウンロードできない環境の方は、式を記載しておきますのでそちらをご確認ください。
解答例のダウンロードファイル
練習問題をダウンロードした時と同じ要領で、下記よりダウンロードしてください。
「VLOOKUP応用①解答例」というファイル名のデータが、各PCのダウンロードフォルダに入ります。
解答例の確認
解答例を記載しておきますので、問題を解き終わったらご確認ください。
全部の式を表示すると数が多いので、上から5つだけ記載しておきます。
担当者の式
セルD4：=VLOOKUP($C4,マスタ!$B$3:$D$12,2,0)
セルD5：=VLOOKUP($C5,マスタ!$B$3:$D$12,2,0)
セルD6：=VLOOKUP($C6,マスタ!$B$3:$D$12,2,0)
セルD7：=VLOOKUP($C7,マスタ!$B$3:$D$12,2,0)
セルD8：=VLOOKUP($C8,マスタ!$B$3:$D$12,2,0)
販売店の式
セルE4：=VLOOKUP($C4,マスタ!$B$3:$D$12,3,0)
セルE5：=VLOOKUP($C5,マスタ!$B$3:$D$12,3,0)
セルE6：=VLOOKUP($C6,マスタ!$B$3:$D$12,3,0)
セルE7：=VLOOKUP($C7,マスタ!$B$3:$D$12,3,0)
セルE8：=VLOOKUP($C8,マスタ!$B$3:$D$12,3,0)
いかがでしたか？
VLOOKUP関数をよく使う、という方でも、使いまわしはしていなかったという方が多いのではないでしょうか。
当サイトの一番の目的は
パソコンを便利に使って楽をすること
ですので、これからもどんどん楽できる情報をお届けしていきたいと思います。
それではまた。
