- 作成日 : 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データの照合を自動で行って消込をしたり、入金元・入金日・入金額などから消し込むべき債権を推測したりできます。その結果、消込にかかる時間を大幅に削減できます。また、消し込んだ結果を会計データとしてそのまま反映できるため、記帳作業が不要となるのもメリットです。
デメリット
会計ソフトでは入金データを基に消込を行うため、インターネットバンキングを開設していないと会計ソフトでの自動消込が難しくなります。しかし、会計ソフトでの入金消込をするためにインターネットバンキングを開設すると、銀行によっては口座を維持するためのコストが発生することがあります。
入金消込はエクセルの自動化で効率アップを実感!
入金消込を自動化できていない場合、経理業務に多くの時間を費やすことになります。しかし、入金消込をエクセルで自動化することにより、業務を効率化できます。消込に会計ソフトを利用すると、さらに業務の効率化が期待できるでしょう。ぜひ記事を参考に入金消込の自動化を行い、業務の見直しに取り組んでみてください。
※ 掲載している情報は記事更新時点のものです。
会計の知識をさらに深めるなら
※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容(テンプレートを含む)の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。
関連記事
債権回収代行とは?外部業者のサービスを利用するメリットや注意点を解説
事業をしていると、商品やサービスを先に提供し、代金を後日まとめて受け取る「掛け」で取引をするケースが多くあります。掛けで取引した場合、販売側は代金を請求できる権利を持てるということです。この権利を「債権」といいます。 今回は、「債権回収代行…
詳しくみる売上と請求の違いとは?意味や計上タイミングをわかりやすく解説
売上と請求の違いがわからないという人もいるでしょう。本記事では、売上と請求の違いや売上から請求までの流れについて解説します。あわせて、売上や請求に役立つテンプレートも紹介するので、業務で必要な場合はぜひご活用ください。 売上と請求の違い ま…
詳しくみる分割検収とは?違法となるケースや契約の注意点、対応方法を解説
分割検収とは、プロジェクトをいくつかのフェーズに分けて契約し、フェーズごとに検収や報酬の支払いを行う方法です。主にソフトウェア開発や建設工事で用いられますが、分割検収を実施する場合には、契約内容や仕様を明文化することが大切です。 ここでは、…
詳しくみる電子記録債権とは?仕訳からでんさいネットの利用方法までわかりやすく解説
電子記録債権とは、従来の手形や売掛金に代わる電子化されたデジタルの金銭債権です。債権の発生や譲渡がオンラインで行えるため、企業間決済が効率化するとともに、円滑な資金調達や回収リスクの低減を通じた資金繰り改善にも役立ちます。 本記事では、電子…
詳しくみる法的措置という表現に注意!催促・督促メールの書き方を解説
売掛債権を回収できないとき、催促・督促メールを送ります。支払遅延の初期段階に送るメールであり、言葉のニュアンスには注意が必要です。すぐに支払われる可能性もあり、「法的措置を取らせていただきます」といった強い言葉は使わないようにしましょう。 …
詳しくみる手形要件とは?不備があった場合はどうなる?一覧でまとめて解説
手形は、一定の期日に決められた金額を支払うことを約束する決済方法です。企業間のやりとりで使用される場面もあり、名称を聞いたことのある方も多いと思います。 しかし、実際どのように取り扱えば良いか把握している方は少ないのではないでしょうか。手形…
詳しくみる