ExcelのVLOOKUP関数で、わかりにくい会計ソフトの試算表をかんたんにきれいにする方法

会計ソフトの試算表は、とても見やすいとは思えません。
私は使わないようにしています。
Excelでかんたんに作れる方法があるからです。
IMG 1983

会計ソフトの試算表は、プロがみても見づらい

一般的に、会計ソフトから出てくる試算表は、こんな感じです。
今回、サンプルデータでB/S(貸借対照表)、P/L(損益計算書)をプリントアウトすると、4枚でてきます。
IMG 1960

このように中途半端にページが途切れるケースも多いです。
IMG 1961

数字が得意な方(プロも含めて)なら、使いやすいのかもしれませんが、私がみてもわかりにくく感じます。
(私だけかもしれませんが)

そのため、試算表を長らく使っていません。
今回の記事のために、プリントアウトして改めてみてみると、やはりわかりにくい資料です。

普通にExcelで作ると・・・大変

そのため、Excelで別途資料を作っているケースも多いでしょう。
しかし、試算表を印刷して、それを見ながら入力するのは非常にめんどくさいですし、ミスの可能性もあります。

スクリーンショット 2014 06 01 20 35 33

次に考えられるのは、会計ソフトからデータを取り出す方法です。
弥生会計なら、[ファイル]→[エクスポート]で、

スクリーンショット 2014 06 01 19 30 13


■スポンサードリンク
------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

■スポンサードリンク


こんなデータを取り出せます。
(Excelへ変換するボタンもありますが、B/SとP/Lが別のシートになってしまうので、非常に使いにくいです。)
スクリーンショット 2014 06 01 19 28 35

不要な部分を削除して、
スクリーンショット 2014 06 01 19 28 53

データを加工する方法もありますが、これを毎月やっていてはいくら時間があっても足りません。

結局は試算表を使わざるを得ないこともあるでしょう。
(又は、高額なソフトを買うことになります)

VLOOKUP関数を使って仕組みを作る

そこで、ExcelのVLOOKUP関数でしくみを作ります。
VLOOKUP関数とは、特定のデータを探し出して、表示されるもので、データを連動させることができる関数です。

たとえば、左のシートの「売掛金」の数字を、右のシートの「売掛金」欄へ連動させます。
スクリーンショット 2014 06 01 19 34 40

このしくみをつくっておけば、毎月、最新の会計データを貼り付けるだけで表が完成するのです。

スクリーンショット 2014 06 01 19 37 39

VLOOKUP関数は次のように入力します。
(売掛金の場合。セルB4に入力。)

「売掛金」を探すので、探すものが入ってるセルを選択してください。
“=VLOOKUP(”まで入力して、次に、「A4」と入力又はマウスでセルA4を選択します。

■スポンサードリンク

スクリーンショット 2014 06 01 20 24 17

探す「売掛金」と表示したい「22,466,998」がある列を指定します。この場合、シート「会計データ」のE列からF列を指定してください。
列で指定するのは、縦(垂直)に「売掛金」を探すからです。縦、つまりVerticalに探す(LOOKUP)から、VLOOKUPといいます。
このとき、範囲の一番左のE列に、探す「売掛金」があるようにしてください。
一番左の列に「売掛金」がないとエラーになります。

スクリーンショット 2014 06 01 20 24 37

指定した範囲のE列からF列で、何番目を表示させるかを指定します。
この場合、2番目(前月残高)を表示させたいので、2です。

スクリーンショット 2014 06 01 20 25 11

最後に、FALSEと入力してください。
TRUEだと、似ているものを探してきますが、FALSEは完全一致のものを探してくれます。
この場合、完全一致でないと困るので、FALSEにします。
スクリーンショット 2014 06 01 20 25 30

これは一例ですが、完成するのは、B/Sで1枚、P/Lで1枚、計2枚です。
実際は、試算表ではなく、推移表や前期比較試算表を同様に作っています。
IMG 1963

・罫線を必要最低限にする
・条件付き書式でグラフを表示する
・セルの幅を広げて見やすくする
といった工夫もできますので、ぜひ試してみてください。

■B/S

スクリーンショット 2014 06 01 20 35 33

■P/L
スクリーンショット 2014 06 01 19 52 26

条件付き書式はこちらの記事を参考に。
グラフ機能を使わずに、Excelでかんたんにグラフを作る方法 | EX-IT

サンプルはこちらです。
サンプルでは、入力しやすいように、数式を一工夫してします。

EX-ITサンプル VLOOKUP試算表





■スポンサードリンク
【編集後記】
昨日は経理&会計のためのExcel入門セミナーを開催しました。
北海道、三重からお越しいただいた方もいらっしゃって、ありがたいです(^^)

【1日1新】
※詳細は→「1日1新」

バイクケースを電車で運ぶ




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』
###