経理あれこれ

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

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

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

関連記事

手帳で人生が変わる!系セミナーに感化されすぎた話とその顛末

どうモメンです。 フィジカル・スピリチュアル的にフラクチュアルなコンディションが続いておりました結果、大失態に陥りました。12月の株主優待銘柄を、一つも取りに行くことができませんでした。それどころか、 …

監査法人の会計士に学ぶ、三種の神器は「体力、英語力、ITスキル」。会計士はドS?

中小企業で経理をしているわたくしモメンですが、そこまで年輪濃く刻まれていない仕事経験の中でも、リアルに大手の監査法人から監査を受ける機会に幸運にも3度ほど恵まれました。   その中で感じた大 …

ideco申込者ついに(自分以外)ゼロ!会社内で金融リテラシー教育するべき?

今年も残すところわずかとなりました。 私は総務部門で働いているので、idecoについては、会社の資格証明部分の記入をしなければならない立場です。 「〇〇さんが申し込んできた」というようなことを社内で他 …

【デキストリンで】監査法人の会計士は食事のスピードが異常だった【食後の眠気を防げる?】

どうモメンです。 経理職をしております。 私は今でこそ人並のスピードでお昼ごはんを食べられるようになりましたが、新社会人になった最初の頃は、周りのスピードについていくのが本当に大変で、結構残してしまっ …

no image

でんさいネットで分かる怪しい取引先

そろそろクドくなってきたかもしれませんが、引き続きでんさいネットにまつわる小ネタを。   でんさいネットは、基本的な仕組みは全銀電子債権ネットワークってところがすべての電子債権の記録台帳を持 …

レスポンシブ広告




自己紹介アイコン

このサイトについて

Author : モメン

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

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

最新の投稿

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