経理あれこれ

【経理ハック】残高確認書発行はエクセル+ワードの差し込み印刷で一発【手打ちしなし】

投稿日:2018年4月11日 更新日:

どうモメンです。

本日は経理職でおなじみの年1,2回程度の行事【残高確認書の発行】にまつわるあれこれについて。

残高確認書とは、会社で計上されている債権債務の計上額が本当に正しいかどうか担保を取るために、取引先に証明書を書いてもらうものです。一般的には、こちらから返信用封筒を同封して取引先に送り付けて、証明書を回収します。

この手続きは、経理としては、決算手続きの一部であるというより、社内に対する内部統制の徹底・けん制の意味が強いようです。営業部が売上に関して不正を行っている場合、この残高確認を取ることで、おのずと事実がバレてしまうということです。

 

この残高確認書の発行ですが、大企業はシステムから自動で発行する機能があるのかもしれませんが、中小企業の使っている会計システムではそういう機能はオプションになってしまうので、エクセルで発行している会社も多いのではないでしょうか。

 

私もエクセルで発行しています。といっても、会社名や金額など、テンキーで打ち込んだりはしません。

昔はやってましたが、経理の仕事をやる中で、だんだん面倒くさがりになってくるため、おかげでエクセルスキルが上達してきて今では会計ソフトから、エクセルにシートに売掛金データを張り付けるだけで、ワードへのデータ流し込み(差し込み)によって、手入力を一切せずに残高確認書の発行ができるようになりました。

具体的な手順を二通り紹介します。

エクセルのみで作る方法

ひな型をワードで作成してデータの差し込み設定する方が簡単だと、後に分ったのですが、それまでは下記の方法でエクセルだけで残高確認書発行をやっていました。

 

①残高確認書のひな型になる「見本シート」を一つ作る

②会計システムから取ってきた「会社別・残高一覧表データ」を新しいシートに張り付ける。会社名は略称じゃなく正式名称で。

③↑②のデータの頭に行を挿入し、会社ごとに「1」から始まる連番を振っておく

ここからが本番です。

④↑③のデータを、ピボット化し、ピボットテーブルのフィールドで、「1」から始まる連番の行を「フィルター」として選択する

⑤フィルターになった部分を適当にクリックしてから、「ピボットテーブルツール」→「分析」→「ピボットテーブルのアイコンのボタンの下にある▼のツマミを開ける」→「オプションという項目の横にある▼をクリックする」→「レポートフィルターページ」という項目が出たらそこをクリックします。

すると、「番号が名前になったシート」が「1」から始まる連番の数だけ、できあがりました。これが最重要ポイントです。いわば、家を作ってあげたわけです。番号のシート名は表札です。

 

⑥「番号が名前になったシート」を一括選択して、①で作ったひな型のデータを全てのシートに貼り付けます。

⑦ひな型のあまり目立たない隅っこに、「シート名の数字を表示させる関数」の式を入れます(これが結構ややこしい)。

これで「1」というシートのひな型文書の隅っこに「1」という数字が表示されるようになりました。

⑧次は、先ほど作ったこの「1」という数字を検索値として、VLOOKUP関数で、②のデータシートから、1という連番の行に入っている「会社名」「金額」のデータを残高確認書ひな型の文書の中の「会社名」「金額」の場所にめがけて飛ばしてくることができます。

とにかくvlookup関数を使いこなせると、経理は飛躍的にエクセル技術が上がります。

私の方法では「1」から始まる連番のシート名を必要な数だけ作っておく、というのが最大のポイントでした。手作業でシートを作ってもいいのですが、シートをたくさん作って名前を書いていくのは結構面倒臭いんです。

 

エクセルのデータを元にワードで差し込み印刷をする方法

エクセルだけで作るやり方は、あまり単純明快で汎用的なやり方じゃないなと後に自己反省しました。

もっと簡単なやり方があるはず・・・そう思ってあっさりと思いついたのがワードの差し込み印刷機能です。

封筒やラベルを作る時に使ったことがあったのですが、文書に使うという発想がありませんでした。しかし、残高確認書のような様式の文書に使う時に一番優れている機能だと思います。

 

①エクセルに、基準日の売掛金(買掛金)一覧を貼り付けます。会社名と金額が分かるように。

会社名が略称になっている場合、この時点で正式名称に書き換えておかなければなりません。ここに張り付けたデータがそのままワードに飛んでいくので。

1行目は項目の見出しになっているリスト形式のデータであることが前提です。

張り付けたあとは、極力データを触らないようにしましょう。とくに列の入れ替えなどはやめたほうがいいです。

 

②ワードで残高確認書のひな型の文書を書きます。他の会社のものを参考にしたり、自社でそれまで使っていたフォーマットとそっくり同じように作りましょう。

③「差し込み文書」→「宛先の選択」→「既存のリストを使用」で、①で作ったエクセルファイルを選びます。

④ワード文書の「〇〇株式会社 御中」の会社名が来る部分にカーソルを置き、リボンの中にある差し込みフィールドの挿入をクリックし、ドロップダウンリストからそこに差し込む項目(会社名)を選びます。

同じ手順で、金額を入力しなければならない部分も、差し込みフィールドの挿入で、「金額」の項目を挿入します。

⑤ここからが最大の山場なのですが、ワードの差し込みはエクセルで設定した書式の情報までは飛んでこないのです。

つまり、エクセルでは「¥1,000,000」と表示されているものでも、ワードに飛んでくると「1000000」という非常に残念な表示になっていますので、これを何とかしないといけません。

詳しい説明は省略しますが、とりあえず下記のとおりにやってください。

1、表示を変えたい部分にカーソルを置いて「Alt+F9」を押す

2、{ MergeField 〇〇  }という表示になったら、〇〇のうしろに ¥# \\0,と付け足して、{ MergeField 〇〇 ¥# \\0, }というように書き換えます。

これで最後に「完了と差し込み」をクリックすればです。あとは一気に印刷するだけ。簡単です。

 

ワードかエクセルか

私は最初にややこしい技を駆使してエクセルで作ってしまったので、後にワードの方が簡単とひらめいた時もネットで調べながらものの1時間ほどで思っていた通りにすんなりとできましたが、人によっては「エクセルとワードの合わせ技」そのものも、ややハードルが高いように感じられるかもしれません。

どうしても無理なようであれば、会社名や金額は「手打ち」でかまわないと思います。

 

大事なのは、残高確認書発行業務は、必ず(誰かしらが)やらなければならない、ということです・・・

ウィジェット用




ウィジェット用




-経理あれこれ
-, , , , , , , ,

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

でんさいネットで支払った時にありがちなトラブル「入金確認できないんですけどー」という電話

でんさいネット導入されて間もない頃に、図書館で「電子記録債権法」に関する本を見つけて勉強してました。 当時、監査に来られた会計士がでんさいネットのことをあまり知らなかったらしく得意げにあれこれ説明しま …

【GLAMOURDISEのマカロンにハマる】ドケチにとって200円の価値とはどのようなものか「自分へのご褒美」で考える

どうモメンです。 口に出すことははばかられますが、ドケチなので、コンビニスイーツはやはり好きです。 ただどのような世界にも「上には上」がいますから・・・コンビニスイーツがドケチ御用達といってしまうと疑 …

FAXは経理では通信インフラとして主役級の活躍。請求書専用FAXが欲しい!

よく日本でガラパゴス化している現象として、二つ折り携帯電話(ガラケー)が有名でしたが、FAXもそうだと聞いて驚いた記憶があります。 海外でFAXは通信インフラとしてかなりレトロな感じになってしまってい …

イオンなど誰にでも身近な企業の株主優待に関する記事まとめ

どうモメンです。 昨年は株主優待を積極的に狙うようになりました。株式投資そのものは初心者ではありませんでしたが、優待についてはそれまであまり興味なかったんです。 しかし自分でやってみると桐谷さんや多く …

「経理の仕事ってこんな感じ」というまとめ記事

どうモメンです。日々経理職として感じることを書いた記事をまとめてみました。 経理をやっている人間は非常にドライな性格の人が多いと思います。意外かもしれないですが経理の仕事は、本質的なところを理解してい …

レスポンシブ広告




自己紹介アイコン

このサイトについて

Author : モメン

経理担当。趣味、投資や節約、ガジェットいじり、大相撲。

画像はヒゲペンギンの赤ちゃん

最新の投稿

%d人のブロガーが「いいね」をつけました。