エクセルで入金消込は自動化できる？関数やマクロを使った5つの方法を紹介

企業が抱える入金消込の負担

入金は月末に集中することが多いため、請求業務の中でも入金消込は特に負担の大きい業務です。

Sansan株式会社の調査によると、企業の入金消込処理の平均は月間平均約2500件、大企業では約5400件も発生しており、その作業時間は企業平均で月間約170時間、大企業では約440時間にも及ぶことがわかっています。このように多くの企業が入金消込業務に大きな時間と労力を費やし、課題を感じています。

参照：Sansan株式会社｜「Sansan、「入金消込業務に関する実態調査」を実施～経理担当者の7割以上が課題ありと回答。自社口座への入金確認は8割以上が目視～」

入金消込の自動化に必要なエクセルの機能

エクセルで入金消込を自動化するには、関数やマクロといった機能を活用します。これらの機能を使って、目視や手作業が必要なステップを、できる限り削減することが運用のポイントです。

関数を使用する

エクセルの関数とは、特定の計算やデータ処理を自動で行う機能です。例えばSUM関数であれば、セルに「＝SUM(A1:A5)」と入力すると、A1からA5の数値を合計して表示します。

SUM以外にもエクセルには多くの関数があり、複雑な計算も素早く行えます。

マクロを使用する

マクロとは、繰り返し行う作業を自動化する機能です。書式設定やフィルターによる絞り込みをマクロとして記録しておくと、ボタン一つで作業を実行できます。簡単な作業であれば、実行したい作業を行いながらマクロとして記録できるため、簡単に自動化が実現できます。

エクセルで入金消込を自動化するテクニック4選

エクセルによる入金管理を、自動化するためのテクニックを4つ紹介します。

1. SUMIF関数
2. VLOOKUP関数
3. IF・AND関数
4. マクロ

使用例も記載していますので、貴社の運用に合わせて活用してください。

１.SUMIF関数

SUMIF関数は、特定の条件に合致したセルの値を合計したいときに使う関数です。入金済みの請求額や、特定の請求先への請求合計額を算出する際などに使います。

このように、特定のセルの合計値を算出できます。

SUMIF関数を使って合計金額を自動計算する方法

図のような債権管理表を例に、特定顧客の債権額を自動計算する方法を説明します。

今回は山田商事の債権総額を表示するためにSUMIF関数を設定します。

2024/1/1に発生した100,000円と、2024/2/1に発生した150,000円の債権の合計、250,000円が算出できます。

２.VLOOKUP関数

VLOOKUP関数は、特定のデータを検索したいときに使う関数です。請求番号等をキーにして特定の請求情報を検索する際などに使用できます。

このように、特定の請求書番号の金額を探し出せます。

VLOOKUP関数で特定の情報を探す方法

図のような債権管理表を例に、入力した請求書番号の顧客名と金額を表示させます。

まずは顧客名を表示するためのVLOOKUP関数を設定します。

次に債権額を表示するためにVLOOKUP関数を設定します。

請求書番号を入力すれば顧客名と金額が自動で表示されるようになりました。

VLOOKUP関数で関連データを自動で表示する方法

図のように請求データと入金データを用意します。

請求データの入金日のセルに、入金データ側の入金日が表示されるように、VLOOKUP関数を設定します。

会社名をキーにして入金日が自動的に表示されるようになりました。

３.IF関数とAND関数

IF関数は「もし〇〇だったら」という条件の結果に合わせて、セルに指定した値を返す関数です。請求に対して特定の条件によって「入金済」や「未入金」の文言を表示させる際などに使います。

AND関数はIF関数と同様に「もし〇〇だったら」といった条件を複数設定し、すべての条件が満たされている場合に、セルに特定の値を返す関数です。未入金かつ入金予定日よりも遅れている場合に「要確認」などの表示をする際に使います。

IF・AND関数で未入金の請求に「要確認」と表示する方法

図のような債権管理表を例に、未入金の明細の備考欄に「要確認」と表示する方法を説明します。

入金予定日が過ぎている明細の備考欄に「要確認」と表示させるために、備考欄のセルにIF・AND関数を設定します。

このように、入金日が空欄かつ入金予定日を過ぎている明細に、要確認と表示されるようになりました。

４.マクロの記録

マクロとは繰り返し行う動作を自動化するときに使う機能です。請求一覧を入金・未入金で絞り込むような、頻繁におこなう作業を短縮したい際に使います。

マクロにはエクセル上で行った操作を記録する「マクロの記録」という機能があり、特定の作業を簡単に「マクロ」として記録できます。

マクロの記録で未入金の請求情報のみを表示するボタンを作る方法

今回はマクロの記録を使って、債権管理表の備考欄に「要確認」の表示がある明細のみを表示させるボタンと、全明細を表示させるボタンを作ります。

ヘッダーを選択し、リボンの「データ」タブをクリックします。

「フィルター」ボタンをクリックします。

各列のヘッダーにドロップダウンメニューが表示されます。

「開発」タブを選び、「マクロの記録」をクリックします。

開発タブが表示されていない場合：「ファイル」→「オプション」→「リボンのユーザー設定」→「開発」にチェックを入れてください。

マクロ名を入力（例：「未入金」）し、保存先は「作業中のブック」でOKをクリックします。

フィルターをかけたい列のヘッダーで、絞り込み条件（例：「要確認」）を選択し、「OK」をクリックします。

「記録終了」をクリックします。

「開発」タブ→「挿入」→「フォームコントロール」→「ボタン（フォームコントロール）」を選択します。

ボタン上で右クリックし、「マクロの登録」を選択します。

マクロ名「未入金」を選択しOKを選択します。

ボタンに「未入金」と入力します。

同様の手順で、フィルターを外す作業も記録し、ボタン名に「全明細」と入力すれば、全表示明細を表示するボタンも作成完了です。

エクセルで入金消込を自動化する際の3つのポイント

エクセルの関数やマクロは非常に便利ですが、運用する際には次の3つのポイントに注意しなければなりません。

1. 期間ごとにファイルを分ける
2. 属人化しないようにマニュアルを作成する
3. 関数やマクロを書き換えられないように保護する

注意しなければならない理由や、対策を詳しく紹介します。

１.期間ごとにファイルを分ける

1つのファイルで管理し続けると、データが多くなるため処理が遅くなり、ファイルが開きにくくなったり、操作スピードが低下したりしてしまいます。
そのため、過去のデータを検索しにくくなってはしまいますが、期間ごとにファイルを分けるべきです。

ファイルの数にもよりますが、四半期ごとや月ごとにファイルを分ける運用が一般的です。

２.属人化しないようにマニュアルを作成する

関数やマクロを使ったファイルは、作成者がいなくなると内容が分からなくなり、他の人が修正や利用できない属人化のリスクがあります。

こうしたトラブルを防止するためにマニュアルを作成することが重要です。

マニュアルには

• 関数やマクロの目的
• 動作の仕組み
• 操作手順
• 注意点

といった内容を分かりやすく記載し、誰に引き継いでも操作できる状態にしておきましょう。

３.関数やマクロを書き換えられないように保護する

エクセルを使った入金消込でトラブル発生する原因の一つが、関数やマクロを誤って書き換えてしまうケースです。エラーが発生して使えなくなる場合や、誤った結果を返してしまうことでトラブルに発展する場合があります。

関数やマクロを使用したら、利用者が勝手に変更できないように、保護機能を使いましょう。

エクセルを保護するには２つの方法があります。

1. シートの保護機能を利用する
2. マクロにパスワードを設定する

実際の設定画面とあわせて詳しく説明します。

１.シートの保護機能を利用する

シート全体の変更を防止するためには「シートの保護」を利用します。

シート全体を選択し「セルの書式設定」をクリックします。

「保護」タブを選択し「ロック」のチェックを外して「OK」をクリックします。

ロックしたいセルを選択し「セルの書式設定」をクリックします。

表示された「セルの書式設定」のダイアログボックスの「保護」タブの選択肢「ロック」にチェックを入れます。

ページ上部にある「校閲」を選択し、「シートの保護」をクリック。ダイアログボックスにパスワードを設定し、許可する操作をチェックして「OK」

指定したセルが保護され、編集できなくなります。

２.マクロにパスワードを設定する

マクロは誤って書き換えられないように、パスワードを設定できます。

Alt + F11キー（Windowsの場合）を押してVisual Basic Editorを開きます。

ツールからVBAProjectのプロパティを選択します。

「保護」タブを選択し、「プロジェクトを表示用にロックする」にチェックを入れてパスワードを設定します。

一度エクセルファイルを閉じて、再度開きます。

マクロを編集しようとすると、パスワード入力画面が表示されます。

エクセルで入金消込を自動化する際の3つの注意点

関数やマクロを活用すれば一部の作業を自動化できますが、エクセルでの入金管理には次のようなリスクがあります。

1. 管理が煩雑になりやすい
2. 業務が属人化しやすい
3. 手動や目視による作業が残ってしまう

入金消込を効率的に行うためには、これらのリスクを事前に理解しておくことが大切です。

１.管理が煩雑になりやすい

期間ごとに別ファイルで保管するため、過去の履歴を調べる際には複数のファイルを開く手間が生じます。

また複数の担当者で入金消込をする場合は、使用するファイルのバージョンも管理しなければなりません。

特に事業規模が拡大する企業では、債権の増加に伴い管理するエクセルデータが増えてしまうため、担当者の作業負担が重くなる可能性があります。

２.業務が属人化しやすい

業務が属人化しやすい点にも注意が必要です。複雑な関数やマクロを使用すると、特定の担当者しか理解できない状態に陥りがちです。

細かくマニュアル整備しないと、担当者が異動や退職した場合、他の人が対応できずに業務が滞るリスクがあります。またマニュアルの作成や更新が属人化し、マニュアルそのものが作られなくなるケースも考えられます。

３.手動や目視による作業が残ってしまう

エクセルの関数やマクロで一部の作業は自動化できますが、金額の確認や名義の突合など、目視確認や手動による作業自体は残ってしまいます。

Sansan株式会社の調査でも、多くの企業が次の2点に課題を感じています。

• 振込名義人と請求先名の突合
• 複数請求が合算された場合の入金確認

これらの業務は関数やマクロによる自動化が難しいため、大幅な効率化につながらないかもしれません。

参照：Sansan株式会社｜「Sansan、「入金消込業務に関する実態調査」を実施～経理担当者の7割以上が課題ありと回答。自社口座への入金確認は8割以上が目視～」

入金消込をシステム化する5つのメリット

入金消込をさらに効率化するにはどうすればよいのでしょうか。有効な手段の1つが消込機能を備えたシステムを導入することです。システム化するメリットは次の5つです。

1. 銀行口座とのAPI連携で入金記録を自動化できる
2. 会計ソフトに消込情報を自動連携できる
3. 請求業務の人員不足解消につながる
4. 複雑な設定が不要で、属人化やミスが起こりにくくなる
5. 検索性が向上し、管理がしやすくなる

それぞれについて具体的に説明します。

１.銀行口座とのAPI連携で入金記録を自動化できる

銀行口座とのAPI連携機能を持っているシステムを導入すれば、入金情報を自動で取得できます。APIとは異なるソフトウエアやシステム同士が情報をやり取りするための仕組みです。多くの銀行では口座情報を連携するためのAPIを用意しており、会計システムが自動でデータを取り込めるようになっています。

Sansan株式会社の調査では多くの会社が用途や部門、顧客ごとに口座を分けて管理していることがわかっています。複数の口座から入金情報を取得するのは非常に手間のかかる作業です。

口座連携の機能を備えた入金消込システムであれば、銀行口座からリアルタイムに入金情報を取得できるため、口座ごとに入金情報を書き出す手間や、入金消込表への転記作業が不要です。

参照：Sansan株式会社｜「Sansan、「入金消込業務に関する実態調査」を実施～経理担当者の7割以上が課題ありと回答。自社口座への入金確認は8割以上が目視～」

２.会計ソフトに消込情報を自動連携できる

会計システムとの連携機能があれば、消込データが自動連携されるため、作業効率が大幅に向上します。エクセルの場合、インポートする際に、誤ったファイルを取り込んでしまうことが無いため、トラブルの防止にもつながります。

特に大量の入金データを扱う場合や、頻繁に消込作業を行う場合、自動連携機能は非常に有効です。

３.請求業務の人員不足解消につながる

システム化によって、突合作業の自動化により入金消込にかかる時間を大幅に削減できます。一般的に入金消込では、入金データと売掛金元帳の請求データを見比べて、一件ごとに突合する作業が必要です。

しかしエクセルの場合、関数やマクロなどの機能を使っても突合自体は自動化できず、目視による確認に膨大な時間がかかります。

システム化すれば、口座番号や振込依頼人名からどの請求に対する入金なのかを自動的に判断するため、確認にかかっていた時間の大部分を他の業務に割けるのです。

４.複雑な設定が不要で、属人化やミスが起こりにくくなる

システム化することで、関数やマクロのような複雑な設定不要で、簡単に入金消込を自動化できます。エクセルの場合、数式やマクロの設定が必要なことに加えて、設定の更新が属人化してしまうリスクがありました。

多くの入金消込システムは、自動化の仕組みが元々の機能として組み込まれているため、数式の入力ミスやマクロの誤操作といったトラブルを回避できます。　

５.検索性が向上し、管理がしやすくなる

検索性の向上もシステム化の大きなメリットです。エクセルの場合、期間ごとに複数のファイルに分散して管理されることがあるため、過去のデータを探すには、まず複数のファイルをそれぞれ開く必要があります。開いたファイルごとに検索・確認作業が必要になるため、業務効率が低下する要因となります。

一方、入金消込システムの場合は、すべてのデータが一つのシステム上で一元管理されます。検索バーにキーワードを入力するだけで、必要な情報をすぐに見つけることができるため、過去のデータを確認する作業が大幅に簡素化され、作業効率の向上が期待できます。

過去の入金情報を頻繁に参照する必要がある入金消込業務において、検索性の向上は非常に重要です。

まとめ

エクセルの関数やマクロ機能を活用することで、入金消込業務の一部は自動化できます。しかし、入金情報と請求データの突合作業は自動化できないため、大幅な効率化は困難です。

また複雑な関数やマクロは、管理のためにマニュアルを作成しなければならず、ファイルの管理やマニュアルの作成自体が属人化してしまう可能性があります。

このような課題を解決するには、システムを活用して請求データと入金データを紐づけて入金消込を自動化することが有効です。

「Bill One債権管理」は、請求書の発行から入金消込まで、全社の請求業務を自動化するクラウド債権管理サービスです。

入金状況がリアルタイムで共有できるため、経理・財務はもちろん、全社で債権の状況を把握できます。

Bill One債権管理の特長

• 請求先ごとに固有のバーチャル口座を振込先として請求書を作成・発行
• 名義不一致や複数の請求分を一括した合算入金も自動で消込処理
• 現在利用中の基幹システムとも柔軟に連携可能
• 発行済みの請求書と入金状況をリアルタイムに一覧表示・管理可能
• 請求書の作成・発行から入金消込、社内での照会・共有までをBill One上で完結

請求業務をワンストップで自動化し、債権と入金の情報を一元管理することで、 企業の債権管理を強固にする「Bill One債権管理」。ぜひ導入をご検討ください。