- 作成日 : 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データの照合を自動で行って消込をしたり、入金元・入金日・入金額などから消し込むべき債権を推測したりできます。その結果、消込にかかる時間を大幅に削減できます。また、消し込んだ結果を会計データとしてそのまま反映できるため、記帳作業が不要となるのもメリットです。
デメリット
会計ソフトでは入金データを基に消込を行うため、インターネットバンキングを開設していないと会計ソフトでの自動消込が難しくなります。しかし、会計ソフトでの入金消込をするためにインターネットバンキングを開設すると、銀行によっては口座を維持するためのコストが発生することがあります。
入金消込はエクセルの自動化で効率アップを実感!
入金消込を自動化できていない場合、経理業務に多くの時間を費やすことになります。しかし、入金消込をエクセルで自動化することにより、業務を効率化できます。消込に会計ソフトを利用すると、さらに業務の効率化が期待できるでしょう。ぜひ記事を参考に入金消込の自動化を行い、業務の見直しに取り組んでみてください。
※ 掲載している情報は記事更新時点のものです。
会計の知識をさらに深めるなら
※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容(テンプレートを含む)の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。
関連記事
売掛金と買掛金を相殺する会計処理・仕訳方法は?事例や注意点も解説
売掛金と買掛金は、条件が合えば相殺できます。そもそも、売掛金と買掛金を相殺するとはどういうことなのでしょうか。この記事では、売掛金と買掛金を相殺する事例や相殺できる条件、会計処理の方法などを解説します。 売掛金と買掛金は相殺できる? 売掛金…
詳しくみる誤送金したら返金してもらえる?対応方法や返金依頼文書の具体例を解説
誤送金とは、振込先や振込金額を間違えて送金することです。誤送金が発生した場合、返金依頼文書を作成したり、金融機関に対して「組戻し」の依頼をしたりするなどの対応が必要です。本記事では、誤送金が発生する原因や対応方法、万が一返金を拒否された場合…
詳しくみる売上と請求の違いとは?意味や計上タイミングをわかりやすく解説
売上と請求の違いがわからないという人もいるでしょう。本記事では、売上と請求の違いや売上から請求までの流れについて解説します。あわせて、売上や請求に役立つテンプレートも紹介するので、業務で必要な場合はぜひご活用ください。 売上と請求の違い ま…
詳しくみるエクセルの突合とは?メリット・デメリットや活用例をわかりやすく解説
日常の業務において「新旧の顧客リストを比較したい」「実際の在庫数と記録された在庫数を照合したい」など、異なる2つのデータを比較して違いを見つけたいことがあります。そのような場合、エクセルの関数が役に立つかもしれません。 この記事では、エクセ…
詳しくみる入金消込サービスとは?自動化・効率化できる?よくある課題も解説
入金消込とは、売掛金の入金を消し込む作業のことです。手入力で行う入金消込にはさまざまな問題があります。入金消込は、できるだけ自動化や効率化を進めて手入力によるミスが生じないようにすることが重要です。この記事では、入金消込の課題や自動化・効率…
詳しくみる割引手形とは?種類や不渡手形になるまでのプロセスを解説
販売代金を現金ではなく約束手形で受け取った場合に「受取手形」で仕訳を起こすことは良く知られていますが、「割引手形」や「裏書手形」、「不渡手形」がどういった種類の手形であるのか意外と知らない人が多いのではないでしょうか? ここでは、割引手形と…
詳しくみる