• 作成日 : 2024年8月30日

入金消込をエクセルで自動化！関数・マクロの活用やポイント解説

売掛金や未収入金の照合や消込を自社のエクセルのテンプレートで行っている会社も少なくありませんが、その作業には時間と労力がかかります。今回はエクセルの関数・マクロを使って債権の入金の照合・消込を行う方法についてご紹介します。入金の消込処理の自動化を図り、業務の精度と生産性を高めて経理業務の負担軽減を実現しましょう。

入金消込をエクセルで自動化するには？

入金消込とは売掛金や未収入金などの債権をリストから消し込む作業のことです。入金消込を手作業で行うと多くの時間を費やしたり、消込間違いや消込漏れなどが発生したりすることがあります。

一方、入金消込をエクセルの関数やマクロで自動化すると、作業の効率化やミスの防止につながります。以下では入金消込を自動化する方法をご紹介します。

エクセルの関数を活用する

関数とは、エクセル上で行う計算のために用意されている数式のことです。関数を利用すると複雑な計算や抽出処理などを自動で行えるため、入金消込にも活用できます。

エクセルのマクロ機能を活用する

マクロとは、エクセル上の定型的な作業を自動化するための機能のことです。マクロを活用すると入金消込のルーティン作業を自動化できます。

入金消込をエクセルで自動化するとどんなミスが防げるか

入金消込をエクセルで自動化すると、手作業で発生する以下のようなミスを防げます。

消込間違い

手作業で消込を行う場合、誤って前後の列の債権を消し込んでしまう可能性があります。しかし、エクセルの関数を利用すると、このようなヒューマンエラーが発生することはなくなります。

消込漏れ

手作業で消込を行っている場合は、消し込んだと思っていた債権が消し込まれていないことが起こりえます。しかし、エクセルの関数を利用するとこのようなミスを防げます。

入金消込をエクセルの関数で自動化する方法

ここでは入金消込をエクセルの関数で自動化する方法を具体的に解説します。

VLOOKUP関数の利用

VLOOKUPは、指定された範囲から指定の値を検索する関数です。例えば、債権管理シート（図1）と入金データのシート（図2）があり、債権管理シートのE列の入金日に入金データシートから日付を転記したいとします。このとき、以下の数式により自動で入力することができます（IFEERROR関数を組み合わせています）。

=IFERROR(VLOOKUP(B3,入金データ!A1:C5,2,FALSE),””)

図1　債権管理シート

図2　入金データ

SUM・SUMIF関数の利用

SUMは、指定されたセルの数値を合計する関数です。一方で、SUMIFとは指定された検索条件に一致するセルの数値を合計する関数です。

例えば先ほどの例で、A株式会社に100,000円（6/28に入金済）、B合同会社に50,000円（6/27に入金済）、C商事に10,000円（未入金）、D株式会社に10,000円（7/31に入金済）、E株式会社に10,000円（7/31に入金済）の債権があったとします。

このときエクセルでSUM・SUMIFを使って、以下のように回収状況を一覧にできます。

まず、債権合計を示す債権管理シートのセルI2にはSUM関数が入っています。債権合計はC列の数値の合計です。この例では以下の数式を入れています。

未回収額を示すセルI4にはSUMIF関数が入っており、例では以下のようになります。

=SUMIF(E:E,””,C:C)

なお、回収額を示すセルI3には単純な数式が入っています。この例では以下の数式を入れています。

=I2-I4

IF・AND関数の利用

債権管理シートの備考欄には、未回収の債権を個別に把握できるようにIF関数とAND関数が入っています。今回の設例では以下の数式を入れて消込（入金日の入力）が行われていない債権については「要確認」と表示されるようにしています（セルF3を例にしています）。

=IF(AND(E3=””,D3<TODAY()),”要確認”,””)

入金消込をエクセルのマクロで自動化する方法

ここからは入金消込をエクセルのマクロで自動化する方法を具体的に解説します。マクロでは定型的な作業を自動化できます。

例えば月次で回収が遅延している債権のみを一覧にして把握するために、ある担当者がエクセルのフィルター機能を使って対象の債権のみを抽出して、他の担当者に連絡するための別のエクセルファイルを作成しているとします。

このエクセルファイルを作成する作業は、マクロを利用するとワンクリックで行えます。以下ではマクロの設定方法をご紹介します。

マクロの記録

まず、エクセルの「表示」タブ＞「マクロ」＞「マクロの記録」の順にクリックします。すると以下のようなポップアップが表示されるため、好きなマクロ名を入力して「OK」をクリックします。ここでは入金遅延一覧と入力しました。

次に実際にマクロで自動化させる作業を記録していきます。まず、先ほどの例では債権管理シートの備考欄にフィルター（「データ」タブ＞フィルターの順にクリック）を掛け、要確認のみにチェックを入れ「OK」をクリックします。

すると、入金が遅延しているC社の債権だけが表示されるため、これをコピーして新しいブック（「ファイル」タブ＞「新規」＞「空白のブック」の順にクリック）のシートに貼り付けます。

新しいブックへの貼り付けが終われば好きな名前を付けてブックを保存します。ブックの保存ができたら元のエクセルファイルに戻ってエクセルの「表示」タブ＞「マクロ」＞「記録終了」の順にクリックします。これでマクロの記録ができました。

マクロの実行

実際にマクロを実効するときはエクセルの「表示」タブ＞「マクロ」＞「マクロの表示」の順にクリックして、保存したマクロ名を選択して「実行」をクリックすると記録した作業が自動で行われます。

入金消込をエクセルの関数・マクロで管理するポイント

債権管理をエクセルで行う場合には、請求年や請求月ごとなど一定期間ごとにエクセルファイルを分けて管理するのがポイントです。債権が膨大にあったり、過去のデータが蓄積されていったりすると動作が遅くなるからです。

また、複雑な関数やマクロを利用している場合にも、エクセルの動作が重くなることがあります。関数やマクロは凝り過ぎず、必要最低限のものにしたほうがよいでしょう。

入金消込にエクセルを活用するデメリット

入金消込にエクセルを活用することにはデメリットも存在します。ここでは3つご紹介します。

エクセルのスキルが必要

当然のことですがエクセルの関数やマクロを利用している以上、エクセルのスキルが求められます。エクセルが苦手な方にとっては使い勝手が悪く感じるかもしれません。

業務が属人化しやすい

エクセルであまりにも複雑な関数やマクロを利用している場合、「その人にしか分からないシート」ができあがり、業務が属人化してしまうことがよくあります。このような場合、その担当者がいないと突発的なことに対応できなくなったり、業務が止まったりすることがあります。

エクセル管理に限界がある

エクセルで入金消込を行うことには限界があります。債権が膨大にある場合や、部分入金があった場合など、エクセルでは管理しきれなくなることがあります。

入金消込を会計ソフトで自動化するメリット・デメリット

入金消込は会計ソフトで自動化することもできます。ここでは会計ソフトで入金消込を自動化するメリット・デメリットをご紹介します。

メリット

会計ソフトでは、売掛金や未収入金とFBデータの照合を自動で行って消込をしたり、入金元・入金日・入金額などから消し込むべき債権を推測したりできます。その結果、消込にかかる時間を大幅に削減できます。また、消し込んだ結果を会計データとしてそのまま反映できるため、記帳作業が不要となるのもメリットです。

デメリット

会計ソフトでは入金データを基に消込を行うため、インターネットバンキングを開設していないと会計ソフトでの自動消込が難しくなります。しかし、会計ソフトでの入金消込をするためにインターネットバンキングを開設すると、銀行によっては口座を維持するためのコストが発生することがあります。

入金消込はエクセルの自動化で効率アップを実感！

入金消込を自動化できていない場合、経理業務に多くの時間を費やすことになります。しかし、入金消込をエクセルで自動化することにより、業務を効率化できます。消込に会計ソフトを利用すると、さらに業務の効率化が期待できるでしょう。ぜひ記事を参考に入金消込の自動化を行い、業務の見直しに取り組んでみてください。

• 監修：守山幸史朗（公認会計士・税理士）

  2013年に公認会計士試験合格後、事業会社及び監査法人勤務を経て、2022年にもりやま会計事務所を開業。事業会社では経理・税務を、監査法人では上場会社・大会社・医療法人などの会計監査やリクルート委員を経験。独立後は主に関西の中小企業を中心に税務顧問のサービスを提供。趣味は魚釣り。