メインコンテンツへスキップ
見出し画像

経理 入金 Excel関数|手数料が引かれた入金に、式2本で印と差額を付ける

    請求額より数百円少ない入金は、振込手数料が引かれていることがあります。入金シートの横に式を2本足すと、その入金に「手数料差引」の印と差額が付きます。ダミーの入金29件では5件でした。

    29件の入金のうち、請求額より少し少ない5件(青い枠)に印と差額が付く。点線の4件は「要確認」
    29件の入金のうち、請求額より少し少ない5件(青い枠)に印と差額が付く。点線の4件は「要確認」

    やること

    同じブックに「請求」シート(C列 取引先・D列 請求額)と「入金」シート(C列 取引先・D列 入金額)がある前提です。入金の取引先は、振込名義を名寄せ表で自社の取引先名に直した列で、名寄せ表に無い名義は「未登録」にしてあります。

    1. 入金シートの H1 に「手数料とみなす上限」、I1 に上限の金額を入れる(例は1000)
    2. E1 に「判定」、F1 に「差額」と書き、E2 に判定の式、F2 に差額の式を入れる

    E2: =IF(C2="未登録","未登録",IF(COUNTIFS(請求!$C:$C,C2,請求!$D:$D,D2)>0,"一致",IF(COUNTIFS(請求!$C:$C,C2,請求!$D:$D,">"&D2,請求!$D:$D,"<="&D2+$I$1)>0,"手数料差引","要確認")))
    F2: =IF(E2="手数料差引",MINIFS(請求!$D:$D,請求!$C:$C,C2,請求!$D:$D,">"&D2)-D2,"")

    3. E2 と F2 を、入金の最後の行まで下へコピーする

    結果

    29件は「一致」18件・「手数料差引」5件・「要確認」4件・「未登録」2件に分かれ、手数料差引5件の差額はダミーに仕込んだ手数料と全部同じでした。「要確認」の4件は、2件の請求をまとめて払った入金2件(取引先C・M)と、1件の請求を2回に分けて払った入金2件(取引先H)です。

    気をつける所

    • まとめ入金から手数料が引かれた入金(取引先M)は「要確認」に残ります。請求1件ずつと比べる式だからです。

    • 二重入金は2件とも「一致」になります。取引先ごとの合計の差は、SUMIFS で差額を出す方法(下のカード)で見てください。

    • MINIFS は Microsoft 365・Excel 2024・2021 と、Windows 版の Excel 2019 で使えます(Microsoft サポート「MINIFS 関数」)。Mac の Excel 2019 は対応の一覧にありません。

    • 上限の1000円は例です。自社の取引で引かれる額に合わせて変えてください。

    次の1歩

    請求書と入金明細を突合する手順は、こちらです。

    印を付けたあと、Claude Code と Excel の関数で請求1件ずつ突合したい方向けです。

    取引先ごとの請求と入金の差額を、SUMIFS で一度に出したい方向けです。

    検証の記録

    • 検証した日: 2026-10-25

    • ダミーデータ: 請求30件・入金29件(すべて架空)

    • 式の結果: 式を入れたブックを LibreOffice で計算し、python で出した判定と差額が58セルすべて一致

    • Excel 本体では確認していません(LibreOffice で計算)

    • 表の画面は LibreOffice の描画です

    • 出所: Microsoft サポート「MINIFS 関数」https://support.microsoft.com/ja-jp/office/minifs-関数-6ca1ddaa-079b-4e74-80cc-72eef32e6599 (2026-10-25 に確認)

    本文の一部と一部のイメージ画像は生成AIで作成し、手順は実際に動かして確認しています。

     
     
    経理・総務の月次作業を、AIで短くする手順を書いています。公開するのは、実際に動かして確かめたものだけ。所要時間と、うまくいかなかった所も残します。貼って使える文面とテンプレートつき。

    あなたへのおすすめ