通帳の入金チェックを楽にする方法｜「ｶ)ｱｵﾊﾞｼﾖｳｼﾞ」を会社名に変換するExcelの式

月末、ネットバンキングの入金明細を見ながら、請求書の一覧に「入金済み」の印を付けていく作業。経理を担当している方なら、おなじみだと思います。

この作業が面倒な理由は、通帳の名義が会社名と一致しないことです。

・請求先：株式会社青葉商事

・通帳の名義：ｶ)ｱｵﾊﾞｼﾖｳｼﾞ

この記事では、Excelの式だけで、通帳の名義から取引先名を自動で引けるようにする方法を紹介します。式は、Microsoft 365 の Excel（日本語版）で動くことを確かめています。

手順1：「取引先マスタ」を作る

まず、取引先ごとに「通帳に出る名義」を登録した表を作ります。

・株式会社青葉商事 → ｶ)ｱｵﾊﾞｼﾖｳｼﾞ

・さくら歯科クリニック → ｻｸﾗｼｶｸﾘﾆﾂｸ

ポイントは、振込名義を通帳に出たとおりにコピーして登録することです。銀行の名義は、小さい「ョ」が大きい「ヨ」になっていたり（ｼヨｳｼﾞ）、「株式会社」が「ｶ)」と略されていたりします。自分で入力すると、ここで食い違いが起きます。

手順2：名義の書き方をそろえる式

通帳の明細には「振込 ｶ)ｱｵﾊﾞｼﾖｳｼﾞ」のように、余計な文字や空白が付くことがあります。そこで、次の式で書き方をそろえます（A2に摘要が入っている場合）。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(A2),"振込",""),"フリコミ","")," ",""),"　","")

・JIS：半角カナを全角にそろえる

・SUBSTITUTE：「振込」「フリコミ」と、半角・全角の空白を取り除く

「振込 ｶ)ｱｵﾊﾞｼﾖｳｼﾞ」は「カ）アオバシヨウジ」になります。取引先マスタの振込名義にも、同じ式で「そろえた形」の列を作っておきます。

手順3：取引先名を引く式

そろえた名義（C2）を、取引先マスタのそろえた名義（G列）から探して、取引先名（E列）を返します。

=IFERROR(XLOOKUP(C2,$G$2:$G$100,$E$2:$E$100),"名義不明")

XLOOKUPが使えないExcel（2019以前）では、こちらを使います。

=IFERROR(INDEX($E$2:$E$100,MATCH(C2,$G$2:$G$100,0)),"名義不明")

手順4：金額をチェックする式

請求額（H2）と入金額（B2）を比べて、判定を出します。振込手数料を引いて振り込まれることもあるので、880円以内の差は「手数料差引？」としています。

=IF(D2="名義不明","要確認",IF(B2=H2,"一致",IF(AND(H2-B2>0,H2-B2<=880),"手数料差引？","金額違い")))

これで、「一致」以外の行だけを確認すればよくなります。

Excelの式だけでは難しいこと

ここまでの方法で、多くの入金は自動で判定できます。ただ、実際の業務では次のようなケースが出てきます。

・同じ取引先から、2か月分をまとめて振り込まれた（請求2件の合計と一致）

・同じ取引先の請求が複数あり、どの請求の入金かわからない

・名義の書き方が、取引先マスタと少しだけ違う（「(ｶ」が後ろに付くなど）

・期日を過ぎても入金がない請求を、一覧にしたい

件数が多い会社では、こうしたケースの確認に結局時間がかかります。

照合ツールの導入をお手伝いします

これらにも対応した照合ツールの導入を、ココナラで承っています。入金明細と請求一覧のCSVを入れると、「消込済み」「まとめて入金」「手数料差引の可能性」「金額違い」「名義不明」「期日超過」に色分けしたExcelができます。御社の銀行・会計ソフトのCSVに合わせてお作りします。