HOME 業務効率 管理会計にも活用できる!エクセルでVLOOKUP関数を使いこなす
業務効率 2019/08/06

管理会計にも活用できる!エクセルでVLOOKUP関数を使いこなす

この記事をシェアする
  • このエントリーをはてなブックマークに追加

経理の仕事では、財務会計や給与計算、販売管理、勤怠管理など、目的ごとに複数のシステムやソフトを利用することが多くあります。さらにこれらのデータは、管理会計や経営計画のための資料用に加工されることもあるのではないでしょうか。その際多くの人が利用するのがおなじみの「Microsoft Office Excel(以下、エクセル)」かと思います。今回は、エクセルでのデータ集計作業には欠かせない「VLOOKUP関数」を紹介します。

入力値に対応するデータを簡単に抽出できる「VLOOKUP関数」

エクセルの関数というと「SUM」など、計算に使う関数を思い浮かべる人も多いのではないでしょうか。
今回ご紹介するVLOOKUP関数は「検索/行列関数」と呼ばれ、計算ではなく指定した範囲内から検索条件に対応するデータを取得するために使う関数です。
例えば、請求書をエクセルで作成する場合、商品番号、商品名、単価をいちいち確認しながら手打ちするのは大きな時間ロスとなります。
そこで利用するのがVLOOKUP関数です。これを使ってあらかじめ「商品番号、商品名、単価」などの参照リストと、そこからデータを取得するルールを設定しておくと、商品番号を入力するだけでデータが表示されるようになります。

図で説明すると以下のようになります。
VLOOKUP関数を使うと、例えば上図の「A」に商品コードを入力するだけで、その商品コードに対応する商品名と単価を「C」の表の中から検索・抽出し、「B」に表示させることができます。

しかし、この便利さとは裏腹に、数式が長いことから扱いづらい印象を与えてしまうことも少なくないようです。 ただ、慣れてしまえばとても簡単な関数です。まずは初めての人にもわかりやすいようにVLOOLUP関数の記述方法についてチェックしていきましょう。

VLOOKUP関数の構成は?

=VLOOKUP(検索値,範囲,列番号,検索方法)
上記がVLOOKUP関数の構成です。この関数で用いる「検索値」、「範囲」、「列番号」、「検索方法」について、それぞれ以下で説明します。

検索値
=VLOOKUP(検索値,範囲,列番号,検索方法)
参照リストから検索したい数字や文字列、セル値の指定をします。先ほどの図では「A」に当たる部分であり、この場合は商品コードを検索しました。なお、文字列で検索値を指定する場合は「””(ダブルクォーテーション)」で囲む必要があります。英字の大文字と小文字は区別されないので、こちらも注意してください。

範囲
=VLOOKUP(検索値,範囲,列番号,検索方法)
検索時の参照リストを指定します。図の場合は「C」の商品リストに当たります。商品リストの範囲は、入力セルの位置によって変化しないので、絶対参照を指定する「$」を行数の前に記述してください。(例:F$3:H$12)

列番号
=VLOOKUP(検索値,範囲,列番号,検索方法)
範囲の先頭から数えた列数を指定します。図の場合、「商品名」の値を表示させたい場合は「2」、「単価」の場合は「3」となります。

検索方法
=VLOOKUP(検索値,範囲,列番号,検索方法)
検索を「近似値検索」とするか「完全一致検索」とするかを「TRUE」または「FALSE」で指定します。
TRUEを指定すると「近似値検索」となり、完全一致する値が見つからない場合に検索値に近い数値を検索して表示します。文字列検索で使われることはほとんどなく、主に数値検索で使用される方法です。
一方、FALSEは「完全一致検索」となり、検索値と完全に一致するセルを検索し、見つからない場合はエラー値「#N/A」が表示されます。
なお、検索方法を指定しなかった場合はTRUEが適用されることになります。

VLOOKUP関数を使って、データの不一致を探してみよう

では実際にVLOOKUP関数を使ってみましょう。可能であれば、エクセルかスプレッドシートを作成して手を動かしながら読んでみてください。 ここでは、実際に経理の現場でよくある「売上管理ソフトと会計ソフトの商品名と単価の一致チェック作業」を例に説明します。
まずは、突き合わせる2つのデータをそれぞれ用意します。
ここでは2つのデータにおける単価の不一致をチェックしていきます。この場合、「システムAの商品の単価-システムBの商品の単価」がイコールとなるかで確認できますが、上記の図のように、商品名の並びが異なるケースでは同じ項目を探すのに手間がかかってしまいます。そこでVLOOKUP関数を使って、システムAのデータの中からシステムBの商品と同じ項目を検索し、同じ行に単価を表示させることとします。
  • VLOOKUP関数をH4のセルに入力します。
  • 検索値をセルE4に指定します。
  • 検索範囲をB4 C13(B$4:C$13)に指定します。
  • 検索後表示させたいのは単価なので、検索範囲のうち単価が表示されている2列目を列に指定します。
    最終的な数式は=VLOOKUP(E4,B$4:C$13,2,FALSE)となりました。
    システムAのデータのボールペン単価100円が表示されました。
  • 次にシステムAの単価とシステムBの単価の差異を出します。 I4のセルにて「システムAの単価」(H4)から「システムBの単価」(F4)を引いてください。この時、「0」の場合は差異がなく、それ以外の数字の場合は差異があるということになります。
  • あとは数式の入ったセルを選択して表の最下部までドラッグするだけで、すべてのデータを検索し自動的に計算してくれます。
    「替え芯」に対応するデータがエラー(#N/A)になっていますが、これはシステムAに「替え芯」の項目がないことを示しています。これにより、システムAとシステムBの項目には単価だけでなく商品名にも不一致があることがわかりました。
**********

会計ソフトなどのデータを自分なりに集計して分析する際は、エクセルの関数を使いこなせるかどうかで作業効率やアウトプットに大きな差が出ます。今回ご紹介したVLOOKUP関数についても、データの差分確認の他にも、商品名の自動入力など、用途はたくさんあります。それぞれの機能を自分なりに応用できる自由さもエクセルの魅力のひとつです。それをどれだけ活用できるかが”できる経理”として重要なポイントとなりますので、実践しながら身に付けていってください。

関連リンク:MJSのセミナーでエクセルをマスターする

この記事をシェアする
  • このエントリーをはてなブックマークに追加
人気記事ランキング - Popular Posts -
記事カテゴリー一覧 - Categories -
セミナー情報 - Seminar Information -
関連サイト - Related Sites -