Excelの使い方はさまざまであり、それでこそ汎用性の高いアプリといえる。ワープロ機能のみでメモ代わりに使っている人から、データベース機能を駆使して、かなり高度なデータ分析をしたり、はたまたマクロを使って、定型的なオペレーションを自動化してりとユーザーのレベルに応じて使えるのだ。
そんなExcelの数々の機能のうち、簡単ではあるものの、とてつもなく便利な機能がある。２つの別々のシートで、あるキーに紐づく情報をシートをまたいで取り出せるというものだ。
VLOOKUP関数
この機能はVLOOKUPという関数を用いて実行する。先ほどの説明だけではよくわからないので、具体例を用いて説明していこう。
たとえば、商品の売上をまとめるとしよう。１つ目のシートには、商品コード、商品名、単価、数量そして商品ごとの売上合計をまとめるとする。この項目のうち、「商品コード」をキーとして、商品ごとに「商品名」、「単価」をまとめた「単価テーブル」を別のシートにつくる。
そして、実際集計してまとめるシート「日次集計表」には、「商品コード」と「数量」だけを入力し、そのほかの項目は、先ほどつくったテーブルから商品コードをキーとしてまとめシートに取出してくるのだ。
こうすることで、毎日の売上げの集計作業は、「商品コード」と「数量」の２項目だけを入力すれば済んでしまう、というものだ。
具体例なので、できるだけシンプルに項目を設定した。なので、こんな内容であれば、最初から表に商品名と単価を入力しておけばいいだけでは？と感じる読者もいるかも知れないが、この商品コードに紐づく項目が増えたり、また商品自体が増えてくると、１つの表にしておいては、いちいち集計する表をメンテしていかなければならない。
一方この方法であれば、単価テーブルのシートをメンテするだけで、日次集計表はメンテする必要がなく、あたらしい商品が出たら、その商品コードを入力するだけで、あとはテーブルの方をつねに正しくメンテしておけば済む。
さて肝心の関数の入力について説明しよう。関数は、テーブルから値を移動したいセルに入力する。この例では、D列からF列までだ。式はいちばん上の行に入力して、コピーする。VLOOKUP関数の引数は以下のとおりだ。
=VLOOKUP（検索値, 範囲, 列番号, [検索の型]）
- 検索値…キーとなる値が入力されるセル。例題では日次集計表シートの”B3”
- 範囲…キーをもとに値を取り出してくる元のシートの範囲。例題では単価テーブルのシートのB3からD7の四角い範囲
- 列番号…キーをもとに値を取り出してくる元のシートの範囲、の左から何列目を取り出すか。例題では商品名を取り出したいので、単価テーブルのシートの左から２列目となるので、"2"
- 検索の型…キーと一致したときのみテーブルの値を取り出させるため、"FALSE"を設定
同じようにE列の単価も式を入力し、列番号のみを２から３に変更する。具体的には、"D3"には、
=VLOOKUP($B3,Sheet2!$B$3:$D$7,2,FALSE)
"D4"には、
=VLOOKUP($B3,Sheet2!$B$3:$D$7,3,FALSE)
と入力する。
そして、売上合計を計算させるため、"F3"には
=C3*E3
を入力。
そして、３行目に入力した式を７行目までコピーして出来上がりだ。なお、ここで注意しなければならないのは、Office講座No.9でも説明したように、式を下方向にコピーすると、オートフィルで参照先の単価テーブルの範囲まで自動的に同じだけ移動してしまうことだ。なので、絶対参照の”$”マークを付けるのを忘れずに。
これで、商品コードと数量を入力するのみで、集計表ができあがる。あとは。売上合計の縦計の式を入力して完成である。
人事の領域では、人事情報管理システムに入っているさまざまな項目をCSVでダウンロードし、社員コードをキーとして、必要な対象者の必要な項目を抜き出し、シートを作るなど、とても活躍する機能である。
ハッキリ言ってこの機能がなければ、Excelを使う意味は1/3以下になってしまうといってもいいだろう。まだ使ったことのない読者はぜひ、この機会に使いこなせるようになってほしい。
