経理職・会計事務所での Excel（エクセル） VLOOKUP 関数の使用例、「科目コード」から「科目名」を検索する

Excel（エクセル）の VLOOKUP 関数の使い方について、これまで何回か説明しました。

また、経理職・会計事務所での使用例についても紹介致しました。

さて、今日は経理職・会計事務所での使用例を追加したいと思います。

Excel で仕訳を入力する際に、科目をコード（番号）で入力し科目名に変換するケースを紹介します。

1、会計ソフトに合わせて科目は科目コードで入力し、VLOOKUP 関数を使って科目名に変換する

会計ソフトに仕訳を入力する際には、科目コード（例えば3桁で、「111」）を使います。

まず、そもそも仕訳入力を減らすために

・銀行取引の履歴を csv ファイルで入手して、マクロ（Excel VBA）を使って会計ソフトにインポートできるように並び替えて使う
・freee を使って銀行取引のデータを取り込む

などのことを積極的にやった方が良いと私は思います。

しかし、やはり１つ１つ仕訳を手入力しなければならないケースもあるわけですが、その場合

①会計ソフトに直接入力する
②Excel で自分がカスタマイズしたシートに入力し、後で会計ソフトにインポートする

というやり方などがあります。

さて、今日は②の仕訳を Excel に入力する場合を考えてみたいと思います。

前に、こちらの記事↓の中で説明した Excel で作った振替伝票を例に取り上げます。

Excel に科目を入力する際には、普段使う会計ソフトと同様に入力する前提です。

このように↓、仕訳入力箇所（JDL の会計データ入力と同様に入力）と、振替伝票として表示するプリントアウト箇所に分けています。

科目はコード（番号）で入力します。

仕訳入力箇所に入力した内容を、数式、関数を用いてプリントアウト箇所に反映させています。

まず、科目コードを普通に「=」を使って写します。

・借方科目については、セルAK22に「=L22」
・貸方科目については、セルAP22に「=M22」

のように入力してあります。

次に、プリントアウト箇所の、「科目コード」から「科目名」を抽出しています。
ここで VLOOKUP 関数を使っています。

ファイルの中に「科目一覧」という名前のシートを作ってあり、その中にJDL会計データ入力からエクスポートした「科目コード」と「科目名」の一覧が入っています。

VLOOKUP 関数で、「科目一覧」シートから、科目コードに該当する科目名を引っ張り出すために

借方、貸方とも数式を埋め込んであります。

2、VLOOKUP 関数の使い方

さて、以前にこちらの記事で説明した内容と同じですが VLOOKUP 関数の使い方を説明しておきます。

VLOOKUP 関数 は「=VLOOKUP(検索値,範囲,列番号,検索方法）」となり4つの引数を用います。
少々ややこしい関数ですが、まず「引数が4つ」ということを覚えると良いと思います。

例えば、次のように従業員の情報（名前、性別、年齢）をまとめた表があったとします。

そして、セル「H4」に入力する従業員の「名前」から「性別」と「年齢」の情報を引っ張り出したい場合、

・性別を表示させるセル「H5」に「=VLOOKUP(H4,B6:D15,2,FALSE)」
・年齢を表示させるセル「H6」に「=VLOOKUP(H4,B6:D15,3,FALSE)」

と関数を入力します。

その状態で従業員の「名前」をセル「H4」に入力すると、その従業員の「性別」と「年齢」の情報がそれぞれセル「H5」とセル「H6」に表示されます。例えば名前が「あああ」の従業員であれば↓のようになります。

同様に、名前が「おおお」の従業員であれば↓のようになります。

このように、個々の従業員の情報を「名前」という条件から検索できるようになるのです。

さて、4つの引数（検索値,範囲,列番号,検索方法）の意味について説明したいと思います。

検索値

まず、「検索値」というのは検索の条件のことです。この例では従業員の「名前」という条件で検索していることになります。

性別を表示させるセル「H5」に「=VLOOKUP(H4,B6:D15,2,FALSE)」と入力しました。「検索値」はセル「H4」となっており「H4」に入力された「名前」と一致する従業員の情報を取り出すことになります。

範囲

「範囲」とは検索の条件である「検索値」の候補となるデータごとの情報の集まりです。上の例ではB6～D15が該当し「B6:D15」のように書きます。

ここで1点注意が必要なのですが、「検索値」の候補となるデータはこの「範囲」の一番左側の列（B列）にしなければなりません。

「名前」の候補がB列に並んでなければならないということです。

「あああ～こここ」までの名前の中から「検索値」に一致するものを探し出します。

列番号

「列番号」とは「範囲」の中で「検索値」の候補となるデータの列を1列目とした場合に、何列目の情報を取り出したいかを表します。

・1列目（B列）　「名前」
・2列目（C列）　「性別」
・3列目（D列）　「年齢」

となります。

「性別」を表示させるセル「H5」の場合は、この「列番号」は2（列目）となります。

検索方法

最後の「検索方法」ですが、「TRUE」、「FALSE」のいずれかを入力します。

・TRUE　 近似一致
・FALSE　完全一致

という意味です。

上の例ではFALSE としており、セル「H4」に入力した「検索値」とB列の「検索値」の候補となるデータが 完全に一致するものを取り出します。

セル「H4」に「おおお」と入力した場合は、B列の名前（検索値の候補となるデータ）から「おおお」と完全に一致する場合の情報を取り出すということです。

＊なお、「検索方法」をTRUEにして良いケースというのは「検索値」の候補となるデータが数値の場合です。この場合についてはまた別途どこかで説明したいと思います。

セル「H5」の数式により

①B列の中で検索値「おおお」と完全に一致するデータを探す
②「おおお」と同じ行で 2列目のデータを探す

ということをやっているのです。

3、別のシートからの検索

さて、今回の振替伝票で VLOOKUP 関数を使う場合についてですが、「範囲」が同じシート内にはなく別のシート「科目一覧」の中にあります。

VLOOKUP の「範囲」を入力する際に、「科目一覧」シートの中の該当するセルを選択します。

数式は
「=VLOOKUP(AK22,科目一覧!$A$8:$B$500,2,FALSE)」
のようになります。

別のシートから検索する場合には、「範囲」を記述するときに「科目一覧! 」のようにシート名に！を付けて表記し、その後に範囲（$A$8:$B$500）を書きます。

なお、範囲の書き方ですが「A8:B500」ではなく行と列の前に「$」を付けて「$A$8:$B$500」とすることによって固定されます。

1つの振替伝票の中には複数の行があるので、一番上の「=VLOOKUP(AK22,科目一覧!$A$8:$B$500,2,FALSE)」という数式を、他の行にコピペしていますが、範囲の行と列の前に「$」を付けることによってコピペした後も、範囲だけは固定されます（ずれ込まない）。

3、まとめ

さて、経理職・会計事務所の業務における VLOOKUP 関数の活用事例を紹介しました。

VLOOKUP 関数を使えれば、かなり色々なことに応用が効くようになります。

自分で表を作成したり、数式を組み込んだりすることが楽しくなりますので、是非覚えて頂きたいと思います。