2009年11月15日

費目集計と3カ国通貨対応出納帳

 EXCELによる会計システム作成の2回目は、①費目集計の方法、②3カ国通貨対応出納帳(受払調書)、③証拠書類台紙の印刷です。

 非常に簡単な関数だけを使って作っています。このため、式を見れば誰でも分かると思うので、EXCELブックをアップし、特に説明はしません。

 Kaikei_Himoku.xls というファイルを下のリンクからダウンロードしてご覧ください。サイズは88KBです。マクロは使っていません。

【追記 2012.4.27】
 リンクがおかしくなっていました。すみません。「さとう」さんのご指摘により、リンク異常が分かりました。ご指摘ありがとうございます。
 念のため、ダウンロードサイトからのリンクに変更しました。
 
 こちらからダウンロードできます。

*** *** 追記 ここまで *** ***

 ブックは、「使用通貨の入力」、「②日表」、「④コード表」、「⑤証書の印刷書式」という4つのシートからなります。

【使用通貨の入力】シート
・この見本では、日本円、USドル、そして現地通貨の3カ国の通貨による受払いを一つのシートで管理できるようにしています。
・初期値として対象国名と現地通貨の単位を入力します。これは、他のシートにリンクしています。

input.jpg


【②日表】シート
・受払簿です。円貨、US$、そして先ほど設定した現地通貨により受払を記載していきます。
・通貨コードを入力すると、それに対応して、出入金のあった通貨の残高が変化します。

Ukeharai.jpg



【費目集計】
・同じシートのセルZ73 から「費目別集計」を行っています。もちろん自動集計です。一部の行・列が非表示になっているので、式を確認する場合は「表示」にしてご覧ください。
・費目集計する方法はいろいろありますが、ここでは「DSUM」という関数を使って集計しています。
・黄色のセルは自動表示です。白いセルにデータを入力していきます。


【④コード表】シート
・この見本では、コード表の費目コードで集計するようにしています。(これは、昔作ったものなのでこのようにしていますが、現在は、直接、費目名で集計するようにしています。コード番号を覚えなくてすみます)。

【⑤証書印刷書式】シート
・日表(受払簿)にリンクしており、証書番号を入力すると、領収書を貼り付ける台紙に必要事項を表示してくれます。つまり、日頃の作業としては、「日表」に日々の受払を入力し、その後、この印刷書式シートに証拠書類番号を入力し、印刷し、それに領収書を貼り付けるだけです。人為的ミスを最小限に抑えることができます。

Print1.jpg


 印刷すると、このように出力されます。このシートに領収書を貼ります。

Print2.jpg


 3つの通貨は、通貨区分コードにより、IF文で条件分けして、それぞれの通貨の残額に反映させています。

 非常にシンプルな作りなのですが、とても役立ちました。ドル勘定と現地通貨勘定、さらに銀行勘定も同様にドル勘定と現地通貨勘定に分かれます。とても複雑なため、これを一括管理する方法として、このシステムを作りました。
 
 このような仕事は、技術職である私の本業ではないので、いかに手を抜けるかを真剣に考え、作ったものです(笑)。

 補足ですが、この見本では日表のデザインに問題があります。式の入っている列とデータを入力する列が入り乱れています。このため、並べ替えの時、注意が必要になります。この不便を避けるには、データ入力する列を一カ所に集め、その部分だけを並べ替えるようにします。

 会計システム作成のコツは、人為的ミスが発生する部分を極力自動化することだと思います。人がやった部分にミスが発生します。これも、チェックプログラムをたくさん入れていけば、ほとんどのミスとその原因をすぐに見つけ出すことができます。これがEXCELを使う最大の利点のように思います。もう一つ重要なことは、マクロをできるだけ使わないということです。他人の書いたマクロを理解するのは意外に大変です。

 今回で、会計関係の記事は終わりにします。前回と今回のテクニックの組み合わせで、マクロを使わずに非常に高性能なシステムを作ることができます。

 管理人が作った会計システムは、チェックプログラムがたくさん入っているため、ほとんどの人為的ミスを回避できました。でも、Excelに不慣れな人は、こちらが想定していないとんでもない操作をすることが分かり、その都度、修正していきました。

 プログラムを知らない人でも操作できるようにプログラミング(というよりも、シートのデザイン)するのが、ポイントです。

 配列を使うとファイルサイズが極端に大きくなるようです。Excelで使える関数はたくさんありますが、条件によっては、別の解決策を探す必要もあります。ところが、別の解決策を探すというのは、そんなに簡単なことではありません。このため、知っている関数か、ネットでヒットした関数を使うことになります。

 今回、公開分は、基本的な部分です。
 Excelを使うと、全自動で決算報告書を作成することができます。ところが、組織により、要求されるフォームはバラバラです。他のフォームは使えません。

【前回の記事を読む】

EXCEL:日々の出納帳から現金受入・支払いだけを抽出して現金出納帳を自動作成する
この記事へのコメント
エクセルファイルもしまだありましたら
リンク張りなおしていただけませんか。
ユーロ、ドル、クローナ、円、ウォンの管理で困っています。
よろしくお願い致します。
Posted by さとう at 2012年04月27日 02:46
>さとうさんへ
 コメント、ご指摘、ありがとうございます。
 リンクがおかしくなっていました。原因は不明です。Seesaa側のトラブルだと思います。
 念のため、専用ダウンローダーへのリンクに変更しました。
 
Posted by ネコ師 at 2012年04月28日 00:59
本当にありがとうございます。
このようなテンプレートサンプルはなかなか探しても見つからなかったのです。
海外のパートナーと一緒に利用するので、自由度が高いエクセルファイルは助かります。
Posted by さとう at 2012年04月28日 08:50
コメントを書く
コチラをクリックしてください