どうモメンです。
本日は経理職でおなじみの年１，２回程度の行事【残高確認書の発行】にまつわるあれこれについて。
残高確認書とは、会社で計上されている債権債務の計上額が本当に正しいかどうか担保を取るために、取引先に証明書を書いてもらうものです。一般的には、返信用封筒を同封して取引先に送り、印鑑が押された証明書を回収します。
この手続きは、経理としては、決算手続きの一つとして行われますが、社内に対する内部統制の徹底・けん制の意味も強いです。営業部が売上に関して不正を行っていれば、相手の認識と金額に相違が出てしまうので、場合によっては決算修正を迫られることになるでしょう。もちろん社内では大騒ぎになるでしょう。実際の相違の多くは収益の認識基準の違いによるものや、消費税の端数処理によって発生するものが多いですね。
この残高確認書の発行ですが、大企業はシステムから自動で発行する機能があるのかもしれませんが、中小企業の使っている会計システムではそういう機能はオプションになってしまうので、エクセルで発行している会社も多いのではないでしょうか。
私もエクセルで発行しています。といっても、会社名や金額など、手入力で打ち込んだりはしません。
最初はやっていましたが、ずっと面倒くさいと思っていましたし、実際に経理としてスキルアップして他の重要な決算業務もこなすようになると必然的にあらゆる作業の省力化は目指さざるを得なくなります。
今では会計ソフトから、エクセルにシートに売掛金データをコピペで張り付けるだけで、ワードへのデータ流し込み（差し込み）によって、手入力を一切せずに残高確認書の発行が一瞬でできるようになりました。
その具体的な手順を二通り紹介します。
エクセルのみで作る方法
ひな型をワードで作成してデータの差し込み設定する方が簡単だと、後に分ったのですが、それまでは下記の方法でエクセルだけで残高確認書発行をやっていました。
①残高確認書のひな型になる「見本シート」を一つ作る
めちゃくちゃ省略していますが、まぁ残高確認書はこんな感じでしょう。この空白部分を手入力なしで自動で埋めるのが今回のミッションです。
②会計・販売システム等から取ってきた「会社別・売掛/買掛金残高一覧データ」を別のシートに張り付けておく。会社名は略称じゃなく正式名称で。
③↑②のデータの頭に行を挿入し、会社ごとに「１」から始まる連番を振っておく
連番を振りました。連番の代わりに会社コードを使ってもかまいませんよ。とにかくここからが本番です。
④↑③のデータを、ピボット化し、ピボットテーブルのフィールドで、「１」から始まる連番の行を「フィルター」として選択する
⑤フィルターになった部分を適当にクリックしてから、「ピボットテーブルツール」→「分析」→「ピボットテーブルのアイコンのボタンの下にある▼のツマミを開ける」→「オプションという項目の横にある▼をクリックする」→「レポートフィルターページ」という項目が出たらそこをクリックします。
すると、「番号が名前になったシート」が「１」から始まる連番の数だけ、できあがりました。これが最重要ポイントです。いわば、家を作ってあげたわけです。番号のシート名は表札です。
←これ重要。
⑥「番号が名前になったシート」を一括選択して、①で作ったひな型のデータを全てのシートに貼り付けます。
⑦ひな型のあまり目立たない隅っこに、「シート名の数字を表示させる関数」の式を入れます。
この関数、実はとてもややこしいので数字を手入力でもかまいません。
これで「１」というシートのひな型文書の隅っこに「１」という数字が表示されるようになりました。
⑧次は、先ほど作ったこの「１」という数字を検索値として、VLOOKUP関数で、②のデータシートから、１という連番の行に入っている「会社名」「金額」のデータを残高確認書ひな型の文書の中の「会社名」「金額」の場所にめがけて飛ばしてくることができます。③でふった連番がとても重要なキーとなります。
vlookup関数というのは、簡単に説明すると、「データが揃っていれば勝手に探してきて自動で表示してくれる関数」という感じです。
ここでは関数の詳しい説明は省略させていただきますが、エクセルの数ある関数の中でもvlookupは経理としては極力早いうちに習得しておきたい関数だと思います。
今回の方法は「１」から始まる連番のシート名を必要な会社の数だけ作るという作業がキモでした。手作業で１シートずつ作るのかなと思いきや、ピボットテーブルの機能を活かしてボタン一発で好きなだけ作れるというのが、あまり知られていないマイナーな裏技かと思います。
エクセルのデータを元にワードで差し込み印刷をする方法
エクセルだけで作るやり方は、あまり単純明快で汎用的なやり方じゃないなと後に自己反省しました。
もっと簡単なやり方があるはず・・・そう思ってあっさりと思いついたのがワードの差し込み印刷機能です。
封筒やラベルを作る時に使ったことがあったのですが、文書に使うという発想がありませんでした。しかし、残高確認書のような様式の文書に使う時に一番優れている機能だと思います。
①エクセルに、基準日の売掛金（買掛金）一覧を貼り付けます。会社名と金額が分かるように。
会社名が略称になっている場合、この時点で正式名称に書き換えておかなければなりません。ここに張り付けたデータがそのままワードに飛んでいくので。
1行目は必ず項目名になるようにしましょう。
↑のようなエクセルデータの場合、2行目が項目名です。1行目の情報は不要なので削除しておきます。
張り付けたあとは、極力エクセルデータを触らないようにしましょう。とくに列の入れ替えなどはやめたほうがいいです。
②ワードで残高確認書のひな型の文書を書きます。他の会社のものを参考にしたり、自社でそれまで使っていたフォーマットと同じように作りましょう。
こちらも簡略化したものを作ってみました。下線の部分が、エクセルのデータを差し込みしたい部分です。
③「差し込み文書」→「宛先の選択」→「既存のリストを使用」で、①で作ったエクセルファイルを選びます。
④ワード文書の「〇〇株式会社 御中」の会社名が来る部分にカーソルを置き、リボンの中にある差し込みフィールドの挿入をクリックし、ドロップダウンリストからそこに差し込む項目（会社名）を選びます。
同じ手順で、「金額」や「連番」の項目を入れたい箇所も挿入しました（赤字箇所）
⑤ここからが最大の山場なのですが、ワードの差し込みはエクセルで設定した書式の情報までは飛んでこないのです。
つまり、エクセルでは「￥1,000,000」と表示されているものでも、ワードに飛んでくると「1000000」という非常に残念な表示になっていますので、これを何とかしないといけません。
詳しい説明は省略しますが、とりあえず下記のとおりにやってください。
1、表示を変えたい部分にカーソルを置いて「Alt＋F9」を押す
２、{ MergeField 〇〇 }という表示になったら、〇〇のうしろに ￥# \\0,と付け足して、{ MergeField 〇〇 ￥# \\0, }というように書き換えます。
次にもう一度「Alt＋F9」を押して表示を元に戻し、続けて「F9」を押してデータを更新させます。
結果のプレビューを押すと印刷イメージを確認できます。
大成功ですね！
「￥」マークではなく「〇〇円」表示にさせたい場合でも、mergefiledさえ書き換えれば、可能です。(詳しくはmergefiledでググって下さい)
これで最後に「完了と差し込み」をクリックすればです。あとは一気に印刷されて出てきます。簡単でしょう？
ワードかエクセルか
これらのやり方というのは、誰かに教わるということがなくても、インターネットでざっと1時間ぐらいエクセルやワードの使い方を分かりやすく解説しているサイトなどを見て真似すれば誰でも作れます。大事なのは、「こういう風にすればできるんじゃないか」という着想だけだと思います。というわけで経理はインスピレーションがわりと大事です。
後輩や後任がいれば「調べるのも勉強だから好きに調べて自由に作れ。ただし１日でな」といつか指示してみたいものです。
私はエクセルでもワードでも作れるので言えますが、どちらで作るにせよ、初心者にはやや難易度は高いです。
どちらも一度作ってしまえばあとは使いまわせるという利点はあります。ただ、どうやって作ったかは、きれいさっぱり忘れます。
ゼロから作るとすればワードの差し込み印刷機能は知名度と汎用性があるので、人にやり方を教えるならこちらをすすめるのがセオリーかもしれません。
しかし数字の「1000000」を「￥1,000,000」表記に換えるのとかはめちゃくちゃ面倒臭いですよ・・・
人によっては差し込み印刷そのものもややハードルが高いように感じられるかもしれません。
まぁ、どうしても無理なようであれば、会社名や金額は「手打ち」でかまわないと思います。
大事なのは、残高確認書発行業務は、必ず（誰かが）やらなければならない、ということです。
