Cafe HOUKOKU-DOH

~ホッとひと息的な読み物でブレイクするサイト~

【いまさら訊けない】Office基礎講座29 VLOOKUP関数

Excelの使い方はさまざまであり、それでこそ汎用性の高いアプリといえる。ワープロ機能のみでメモ代わりに使っている人から、データベース機能を駆使して、かなり高度なデータ分析をしたり、はたまたマクロを使って、定型的なオペレーションを自動化してりとユーザーのレベルに応じて使えるのだ。

 

そんなExcelの数々の機能のうち、簡単ではあるものの、とてつもなく便利な機能がある。2つの別々のシートで、あるキーに紐づく情報をシートをまたいで取り出せるというものだ。

 

VLOOKUP関数

この機能はVLOOKUPという関数を用いて実行する。先ほどの説明だけではよくわからないので、具体例を用いて説明していこう。

 

たとえば、商品の売上をまとめるとしよう。1つ目のシートには、商品コード、商品名、単価、数量そして商品ごとの売上合計をまとめるとする。この項目のうち、「商品コード」をキーとして、商品ごとに「商品名」、「単価」をまとめた「単価テーブル」を別のシートにつくる。

f:id:HOUKOKU-DOH:20211012154819j:plain

そして、実際集計してまとめるシート「日次集計表」には、「商品コード」と「数量」だけを入力し、そのほかの項目は、先ほどつくったテーブルから商品コードをキーとしてまとめシートに取出してくるのだ。

 

こうすることで、毎日の売上げの集計作業は、「商品コード」と「数量」の2項目だけを入力すれば済んでしまう、というものだ。

f:id:HOUKOKU-DOH:20211013050856j:plain

具体例なので、できるだけシンプルに項目を設定した。なので、こんな内容であれば、最初から表に商品名と単価を入力しておけばいいだけでは?と感じる読者もいるかも知れないが、この商品コードに紐づく項目が増えたり、また商品自体が増えてくると、1つの表にしておいては、いちいち集計する表をメンテしていかなければならない。

 

一方この方法であれば、単価テーブルのシートをメンテするだけで、日次集計表はメンテする必要がなく、あたらしい商品が出たら、その商品コードを入力するだけで、あとはテーブルの方をつねに正しくメンテしておけば済む。

f:id:HOUKOKU-DOH:20211013050859j:plain

さて肝心の関数の入力について説明しよう。関数は、テーブルから値を移動したいセルに入力する。この例では、D列からF列までだ。式はいちばん上の行に入力して、コピーする。VLOOKUP関数の引数は以下のとおりだ。

 

  =VLOOKUP(検索値, 範囲, 列番号, [検索の型])

  • 検索値…キーとなる値が入力されるセル。例題では日次集計表シートの”B3”
  • 範囲…キーをもとに値を取り出してくる元のシートの範囲。例題では単価テーブルのシートのB3からD7の四角い範囲
  • 列番号…キーをもとに値を取り出してくる元のシートの範囲、の左から何列目を取り出すか。例題では商品名を取り出したいので、単価テーブルのシートの左から2列目となるので、"2"
  • 検索の型…キーと一致したときのみテーブルの値を取り出させるため、"FALSE"を設定

f:id:HOUKOKU-DOH:20211012154826j:plain

同じようにE列の単価も式を入力し、列番号のみを2から3に変更する。具体的には、"D3"には、

 =VLOOKUP($B3,Sheet2!$B$3:$D$7,2,FALSE)

"D4"には、

 =VLOOKUP($B3,Sheet2!$B$3:$D$7,3,FALSE)

と入力する。

 

そして、売上合計を計算させるため、"F3"には

 =C3*E3

を入力。

f:id:HOUKOKU-DOH:20211012154823j:plain

そして、3行目に入力した式を7行目までコピーして出来上がりだ。なお、ここで注意しなければならないのは、Office講座No.9でも説明したように、式を下方向にコピーすると、オートフィルで参照先の単価テーブルの範囲まで自動的に同じだけ移動してしまうことだ。なので、絶対参照の”$”マークを付けるのを忘れずに。

blog.houkoku-doh.com

これで、商品コードと数量を入力するのみで、集計表ができあがる。あとは。売上合計の縦計の式を入力して完成である。

f:id:HOUKOKU-DOH:20211012154815j:plain

人事の領域では、人事情報管理システムに入っているさまざまな項目をCSVでダウンロードし、社員コードをキーとして、必要な対象者の必要な項目を抜き出し、シートを作るなど、とても活躍する機能である。

 

ハッキリ言ってこの機能がなければ、Excelを使う意味は1/3以下になってしまうといってもいいだろう。まだ使ったことのない読者はぜひ、この機会に使いこなせるようになってほしい。