経理あれこれ

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

投稿日: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

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

関連記事

【経理ハック】普段使いのエクセルファイルを集めたハイパーリンク集ファイルを作ろう【デスクトップ保存】

どうモメンです。 本日は、共有フォルダー内のファイル整理に関するあれこれについて。 経理の仕事をしていますのでエクセルもよく使いますが、パソコンのローカル保存は一切せず全てのファイルは共有ファイルの中 …

このままでは経理の仕事はいずれ書類整理だけになってしまう?それでもイイじゃないの

私は整理整頓が得意な方ではありませんでした。しかし総務経理は非常に紙の書類が多いのです。   経理で本当に必要な書類なんてごくわずかしかないのですが、それ以上にあらゆる書類が、ごみ処理場のよ …

恐怖!?監査の現金実査対応

どうしよう、4月1日に親会社の監査法人の会計士が現金実査と棚卸実査に来て、うちの試算表と預金の残高を突合するって言ってきたよ、今日は3月31日だけど、明日の朝までに現預金勘定の仕訳全部切って合わせるの …

営業がやりたくない・・・経理をやりたいと思った履歴書に書けない本音の志望動機

今回は経理をやりたいと思った理由についてお話したいと思います。全てが履歴書に書けるような前向きな理由ばかりではありません。ここは本音でいきましょう。 営業がやりたくない 私は大学で一応会計を学びました …

google homeにプッシュ通知の内容を読み上げて欲しい!IFTTTのthen(アクション)を走らせたい!

どうも、日々ゴーストのささやきを聞くモメンです。 google home(私のはmini)は使いこなしている方ですが、もっとできることがあるはずとずっと思っています。 個人的に一番不満なのは、なぜいち …

レスポンシブ広告




自己紹介アイコン

このサイトについて

Author : モメン

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

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

最新の投稿

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