入金消込作業をエクセルで効率化する方法～関数・マクロでの自動化
入金消込は時間も手間もかかる負担の大きな作業です。企業によっては消込担当を置き、集中的に時間をかけて作業を行うケースも見られます。請求書の控えと通帳を突き合わせて確認をしたり、得意先元帳で確認をしたりと、その方法は多様です。
作業効率を改善する方法としてエクセルを使った管理が考えられますが、どのように管理をしたらよいのか、どのように書類を作成すればいいのかなど、準備には手間や時間がかかります。また効率を考えエクセルで管理をするにしても、チェックは目視で行わなければならず、作業の大変さは変わらないという側面もあります。ではエクセルを使って入金消込作業を効率化するにはどうしたらいいのでしょうか。その方法を紹介します。
目次
入金消込作業を効率化するポイント
入金消込とは、取引先に請求している売掛金の入金状況を確認し、入金があった売掛金データと突合する作業です。取引先が多くなると請求数も増えてきて、その分大量の入金消込作業が発生します。また、入金消込作業に漏れがあると、既に入金済みの顧客へ再請求してしまうことにもなりかねません。細心の注意を払い慎重に作業をする必要があります。
効率化を重視するのであれば、決済代行サービスや入金消込システムなど外部サービスを導入するのもひとつの方法です。しかし、取引規模が大きくないうちは、導入コストを考えると費用対効果が合わないことも考えられます。費用を最小限に抑えながらの作業の効率化には、エクセルでの効率化はひとつの有効な手段となりえます。
関連記事：消込とは？具体的な仕訳の流れやよくある課題、システム化のメリットを解説
入金消込作業をエクセルで効率化する方法
入金消込作業をエクセルで効率化するための主な方法には、下記の3つが挙げられます。
＜入金消込作業をエクセルで効率化する方法＞
- 無料で配布されている入金管理表を使用する
- エクセルマクロを活用する
- エクセル関数で行う
無料で配布されている入金管理表を使用する
エクセルで入金管理表を作成する場合、少なくとも取引先名・金額・入金期限などの項目は準備しないといけません。また、自社の事業内容や業務内容によって必要な項目は異なります。ある程度のイメージができていれば作成しやすいのですが、イメージができていない場合は作成するにも一苦労です。
そのような場合には、インターネットで無料配布されているテンプレートを使うとよいでしょう。「入金管理表 エクセル 無料」で検索をすると、無料配布をしているサイトがたくさん出てきます。経理に関する情報が記載されているサイトや、テンプレート全般を取り扱っているサイトをチェックし、必要な項目を管理できるテンプレートをダウンロードしましょう。1種類だけでなく、気になるものを複数ダウンロードし、作業しやすいものを選ぶことがポイントです。
エクセルマクロを活用する
入金消込を行う際には、入金管理表のリストから未入金のみを抽出するフィルタをかけたり、入金一覧のデータから照合して色をつけたりすることがあります。しかし、このフィルタをかける・解除するという作業も、取引数が多いと時間がかかります。
このような決まった作業や繰り返し行う作業は、エクセルのマクロ機能を活用することで、大幅な時間短縮を図れます。マクロとは、エクセルで行った作業を記録し、あとからその作業を実行させる機能です。実行したい作業を一度行い記録するだけなので、簡単な作業であればプログラミングなどの特別な知識は必要とされません。
また、シート上にボタンを作成し、記録した作業を登録することも可能です。記録された作業がボタンを押すだけで実行されるため、エクセルの知識が少ない従業員でも、簡単に作業を実行できます。
エクセルマクロの登録・解除
実際によく使う下記のような操作を、マクロ機能を使って行う方法を紹介します（本記事ではExcel2013を使用)。
- 未入金のみを表示する操作
- すべてを表示する操作
あらかじめデータにフィルタを設置し、ボタンの準備をしておいてください。
はじめに[開発]タブをクリックします。開発タブが表示されていない場合は、[ファイル] タブで、[オプション]、[リボンのユーザー設定] の順に移動し [リボンのユーザー設定] および [メイン タブ] の下の [開発] チェック ボックスをオンしてください。
これで開発タブが表示されます。「マクロの記録」をクリックすると、マクロ設定ダイアログが開きます。
マクロ名はわかりやすい名前を付けると後で設定がしやすいので、今回は「未入金」という名前を付けます。名前を付けたら「OK」をクリックします。すると「マクロの記録」だったボタンが「記録終了」に変化します。
この状態で操作を記録していきます。
まずは未入金の取引先のみを表示する作業として、「入金日」のフィルタを開き、一度「（すべて選択）」を解除し、「（空白セル）」にチェックマークを付けて「OK」をクリックします。未入金の取引先のみが表示されましたので、「記録終了」ボタンをクリックします。これでマクロの記録は終了です。
同じような方法で「全表示」する作業を登録します。「（すべて選択）」のチェックを一度解除し、再度チェックを入れます。
登録したマクロを、ボタン（図形）に設定します。対象のボタンを右クリックし、「マクロの登録」をクリックします。
未入金ボタンに設定するマクロを選択します。「未入金」と名前の付いたものを選択し、OKをクリックします。これで、ボタンを押すことにより未入金の取引先のみが表示されるようになります。同じように「全表示」の設定も行ってください。これでマクロの設置が完了しました。
マクロをボタンから解除したい場合は、ボタンを右クリックし「マクロの登録」をクリック。マクロ名に入っているものを削除し、OKをクリックすれば解除できます。
マクロを使ったファイルは「エクセルマクロ有効ブック（.xlsm）」で保存することになります。セキュリティソフトによってはマクロ入りのファイルが開けない場合があるため、設定の調整が必要になります。
また、高度なマクロを使い自動化をしたいという場合は、マクロ言語の直接入力が必要です。VBA(Visual Basic for Applications)というVB(Visual Basic)をベースにして作成されている専用の言語です。このVBAを使って自動化をする場合には、高度なスキルと知識が必要となります。
エクセル関数で行う
エクセルの関数でも消込作業を効率化できます。関数とは特定の目的のために用意されている計算式のことで、エクセルでよく使われる機能のひとつです。セルに関数と計算に必要な値を入力することで、複雑な計算を自動的に行ってくれます。消込作業で役に立つ代表的な関数をいくつか紹介していきましょう。
SUM・SUMIF
SUMもしくはSUMIFは、指定した範囲の合計を表示する関数です。入金管理表から残りの売掛金を算出する場合や、数値の合計を確認する際などに用いられます。SUMIFは、条件に合致する数値だけを合計する際などに使用します。
=sum(合計範囲)
=sumif(条件範囲,検索条件,合計範囲)
合計金額が表示されているF1セルには「=SUM(B:B)」が入ります。すべての合計になるので、列をそのまま指定します。入金済みの金額が表示されているH1のセルには「=SUMIFS(B:B,C:C,”<>”)」が入ります。こちらは入金日が入っている（空欄ではない）条件を満たした箇所の金額のみ合計する、という式になります。条件である”空欄ではない”は「＜＞」で設定できます。未入金の金額が表示されているJ1のセルには「=SUMIFS(B:B,C:C,””)」が入ります。入金日が空欄である金額のみ合計する、という式になります。空欄は「””」で設定できます。
VLOOKUP
VLOOKUPは、特定の条件が該当するものを、任意の範囲から探し出す関数です。取引先コードに合致する取引先名を、取引先一覧表から探して表示する処理などに利用されます。
＝VLOOKUP（検索値, 範囲, 列番号, 検索の型）
請求書番号（G1）から取引先会社名、金額、入金日を抽出する処理を例として見ていきましょう。
会社名が表示されているH1のセルには「=VLOOKUP(G1,A:D,2,FALSE)」が入ります。
金額が表示されているI1のセルには「=VLOOKUP(G1,A:D,3,FALSE)」が入ります。
入金日が表示されているI1のセルには「=VLOOKUP(G1,A:D,4,FALSE)」が入ります。
VLOOKUPは範囲指定をした一番左の列を検索し、一致した行の指示された列の内容を表示します。そのため列番号には、取引先名の場合は「2」、金額の場合は「3」、入金日の場合は「4」が入ります。また、検索する値と完全一致したものを表示したい場合は、検索の型部分には「FALSE」を入力します。省略、もしくは「TRUE」を入力した場合は近似値が表示されます。また、その際のデータは、検索列（1列目）を昇順にしておく必要があります。
IF
IFは、条件を満たしているかどうかで別々の処理を行う関数です。他の関数と組み合わせて使うことが多く、COUNTIFと合わせて利用すると「入金一覧表に名前があれば入金管理表に○をつける」というような処理を行うことができます。
=IF(論理式,値が真の場合,値が偽の場合)
=COUNTIF(範囲,検索条件)
入金チェックのD1セルには「=IF(COUNTIF(G:G,B2)>0,”〇”,””) 」が入ります。COUNTIF関数で、入金があった取引先を記載するG列を検索しています。G列にB2セルと同じ内容のものがいくつあるかを数える式です。そしてIF関数で、G列に同じもの（0を超える数値）があったら〇を表示し、なければ空欄、という式になっています。金額の確認まではできないため、同一金額の入金を管理する際に役立ちます。
エクセルだけでなく入金管理システムの導入も検討を
上記のように、エクセルの機能の活用によって入金管理を効率化できます。しかし、振り込まれたデータとの突き合せチェックなど、手作業でやらなければいけない処理が多く残ることに変わりはありません。さらに作業の効率化を図るのであれば、入金管理全体が自動化されるシステムの導入がおすすめです。入金管理システムに備わっている機能もサービスによって異なりますが、入金があったときにはシステムに金額や振込元などのデータが即座に反映されます。取引先ごとに入金状況や過不足の状況などをまとめて確認できるため、スムーズな入金チェックが可能です。
また、消込作業にかかっていた目視チェックの時間を別の作業時間にあてることができるようになり、業務全体を効率化させることにもつながります。詳しくは下記の記事も合わせてご覧ください。
関連記事：入金消込の自動化による効率改善～自動化の方法とそのメリット・デメリットと導入時の注意点
経理業務を効率化するなら「Paid（ペイド）」
消込は取引先ごとに行う必要があるため、取引先が増えれば増えるほどコストがかかり、経理業務を圧迫してしまいます。
「Paid（ペイド）」は、請求業務の代行と未払い時の保証がセットになった企業間決済サービスです。取引先への請求・回収をPaidが行い、全ての取引先への請求金額を一本化して貴社にお支払いするため、取引先ごとの消込作業も不要になります。
経理業務を効率化することができれば、空いた時間を財務分析や外部とのコミュニケーションなど、事業を拡大するための業務に使うことができるようになります。
関連記事：3人は必要な経理業務が1人で対応可能！決算スケジュールも短縮できています～株式会社Saleshub
まとめ
入金消込作業とは、売掛金と入金データを突合する作業です。入金消込作業を効率化するためには、無料で配布されているエクセルのテンプレートを使って入金管理表を作成し、マクロや関数などを組み込む方法があります。さらなる効率化・自動化を図るのであれば、入金管理システムの使用がおすすめです。入金データの取得や出力など手作業が必要であった処理を自動化し、効率性と正確性をともに向上させる大きなメリットを享受できます。
