Excelを使った仕事で、できあがったデータをそのまま使うと不可能でも一工夫加えることで可能になることがあります。事務所用の帳簿として仕訳帳と総勘定元帳をエクセルで作っていますが、仕訳帳から総勘定元帳への転記にも一工夫が必要です。
VLOOKUP関数で検索とデータの転記が可能に
仕訳帳と総勘定元帳は、法律上、会社を運営する際に必須とされる会計帳簿です。仕訳帳は日付順に会社の取引をすべて記録する帳簿で、総勘定元帳は仕訳帳で記録した取引を勘定科目別に分けて記録する帳簿。仕訳帳の内容を総勘定元帳へ写す（転記すると言います）という関係になります。具体的には次の画像のようになります。実際には仕訳帳と総勘定元帳のシートは分けて作りますが今回はイメージしやすいように同じシート上で示しています。
取引が少数なら1つ1つの記録をコピー＆ペーストで転記しても良いですが、数が増えると時間がかかりますし重複や漏れが発生して正しく転記できなくなる可能性が高くなります。そこで、効率的で正確な転記を行うためにVLOOKUP関数を使います。
VLOOKUP関数は簡単に説明すると、検索条件に一致するデータを表の中から取り出してくれる関数です。先ほどの消耗品費の「日付」欄にあるVLOOKUP関数で説明します。
「日付」欄に次のような関数が入力されています。
VLOOKUP関数の中身はカンマ（，）によって区切られていて、順番に説明すると
＝VLOOKUP（①検索値（何を検索するか）,②範囲(データを取り出す範囲）,③列番号（検索条件に一致したときに、何列目のデータを表示するか）,④検索方法）となります。
具体的に見てみると、
①は”$B$22”になっていますからB22を検索することになるのですが、B22セルを見ると”消耗品費”とありますので、「”消耗品費”を検索する」ことが分かります。
②はどの範囲からデータを取り出すかを表しますから、”$A$18:$F$20”になっていてA18からF20のセルで囲まれた範囲からデータを取り出すように指定されているのが分かります。
③は検索条件に一致するデータが見つかったら、そのセルを基準に何番目の列のデータを取り出すかを表します。ここでは”２”になっていてB列のデータをとりだすことになります。
④は検索方法で０は「①の検索値とぴったり一致するものを探してくれ、なければエラーと判断してくれ」という指示です。他に１がありますが、１は「①の検索値とピッタリ一致するものを探してくれ、なければそれに近いものを表示してくれ」と言う指示です。近いものを選ばれても混乱するだけですからここは０にして、ピッタリ一致するものを探すように指示しています。
ここで重要なVLOOKUP関数の検索の方法を確認しておきます。VLOOKUP関数は①で指定したデータ（ここでは”消耗品”）について②で指定した範囲の一番左の列を縦方向に（ここではA列）検索しますので、この点に注意しておきましょう。
検索条件に一致するデータが見つかったら、そのセルを基準にして③で指定した数、右へ移動したセルのデータを示します。ここでは”２”なのでA19を基準にして右へ2つ移動したセルＢ19のデータを示します。言葉だと分かりづらいので下の画像を見てみて下さい。
VLOOKUP関数の弱点はデータが重複した時
ここまで見てきたVLOOKUP関数の仕組みを使って、仕訳帳のデータから新聞図書費を総勘定元帳へ転記します。
説明に従って、①の検索値に「新聞図書費」、②の範囲に仕訳帳のデータの範囲（A2セルからH7セルまで）、③は総勘定元帳の項目に合わせて指定（たとえば”摘要”は、仕訳帳ではH列にあるから、A列から数えて8番目なので８を指定）します。
総勘定元帳の”摘要”の列を例に挙げると
=VLOOKUP($E$47,$A$2:$H$7,8,0)
のように入力。
なお、①検索値は$E$47になっていますが、E47セルには「新聞図書費」が入力さています。
”摘要”以外の列も同様に指定しました。
その結果が次の画像です。
仕訳帳のデータでは、新聞図書費は2014年9月6,8,9日の3件分取引記録があるのでそれぞれ転記されているはずですが、そうはなっていません。9月6日の取引が3件転記され、8,9日分は転記できていないのです。ここにVLOOKUP関数の欠点があります。
それは、データの中に検索値が2つ以上存在しても最初にヒットした検索値からしかデータを取り出せないと言うことです。
具体的に見てみると、検索値である”新聞図書費”は仕訳帳のA列に3件ありますが、その全てにヒットするのではなく最初の”新聞図書費”（9月6日分）だけを認識することになるのです。
そのため、三件分のデータを転記するように入力した総勘定元帳のVLOOKUP関数の結果には9月6日の取引記録だけが３度現れています。
重複するデータには独自コードをつけて他と区別する
VLOOKUP関数では重複する勘定科目の取引を取り出すことができませんでした。ですが、ここに一工夫加えればそれも可能になります。重複してしまうと取り出せないのですから重複しないように取引毎に個別のコードをつけてしまうのです。
たとえば、新聞図書費の三件分の取引については日付順に新聞図書費１，新聞図書費２，新聞図書費３，のように通し番号をつけていきます。仕訳帳の中で同じ勘定科目を抽出して通し番号をつけるのはかなり複雑な作業のように思われますがそうでもありません。COUNTIF関数を使うのです。
COUNTIF関数は「①指定した範囲」で「②指定した条件」に合うデータがいくつあるかを数えてくれる関数です。具体的に見てみましょう。
まず、範囲指定ですが”$A$3:A4”となっています。$A$3は絶対参照と言って、この式を他のセルにコピーしてもA3のまま動かないようにするというものです。一方でA4は$マークがついていません。これは相対参照と言って、この式を他のセルにコピーするとその移動距離に合わせてセルも動くと言うものです。
とすると”$A$3:A4”をコピーしていくと、始まりのA3は動きませんが終わりは入力しているセルまでが指定されます。
次に、検索条件ですが勘定科目の列（A列）のセルを指定します。すると、仕訳帳でその勘定科目の中で何件目に出てきたのか（＝通し番号）が分かります。
このCOUNTIF関数で求めた通し番号と勘定科目を結びつけることによって独自コードが完成します。方法は2つのデータを＆でつなぐだけでです。
検索値を勘定科目から独自コードに代えて、VLOOKUP関数を使えば全ての取引を転記できます
はじめに検索値を勘定科目に指定しましたが、次は独自コードに指定してみます。
たとえば、総勘定元帳の”摘要”列の式は次のようになります。
＝VLOOKUP($A44,$C$2:$H$19,６,0)
$A44は、総勘定元帳のA列に独自コードを入力しているので検索値が独自コードになっていることを表しています。
このA列は「総勘定元帳」のタイトルが入っているE41セルと通し番号が入っているB列の「No」を＆で結んで作成。
たとえば、A43セルの「新聞図書費１」は「＝$E$41&B43」と入力されています。以下、A列は「＝$E$41&B44」、「＝$E$41&B45」、…と続きます。
先ほどは最初に出てきた9月6日の取引しか転記されませんでしたが、他の取引についても転記されました。
まとめ
今回ご紹介した方法以外のものも書籍やネット上でたくさんの方が公開されています。
様々な方法を知ることでエクセルを柔軟に使いこなせるようになれば、できることが広がって仕事の効率化が進みます。
＜おまけ＞
天気がすぐれないと気温が上がりませんね。
朝夕はかなり冷えてきていますので体調管理にはご注意下さい。
