下請への支払いの控除をエクセルで計算する｜列の作り方と式の例
協力会社が多いと、支払いの控除はエクセルの支払一覧でまとめて計算することが多くなります。式を一度作れば楽になりますが、端数処理の関数と率を掛ける列を間違えると、全行の金額がずれたまま気づかないこともあります。
このページでは、支払一覧の列の作り方と式の例を整理します。式を作ったら、何件かを上の計算ツールで確かめると安心です。
外注請求の相殺計算
1 件分の請求額と控除の率を入れると、差し引いたあとの支払額を表示します。エクセルの式の結果と突き合わせるのに使えます。
入力した値はこの画面の中だけで計算し、どこにも送信しません。
列の作り方
支払一覧は、1 行に 1 件の請求を置き、次の列を並べると式が書きやすくなります。
| 列 | 中身 |
|---|---|
| A 外注先 | 協力会社の名前 |
| B 請求額(税抜) | 請求書の税抜金額 |
| C 消費税 | B に税率を掛けて丸めた額 |
| D 請求額(税込) | B + C |
| E 協力会費 | 率を掛ける金額 × 率を丸めた額 |
| F 安全協力費 | 同じく率を掛けて丸めた額 |
| G その他の控除 | 定額で差し引くもの |
| H 支払額 | D − E − F − G |
率は表の上の方のセルに 1 か所だけ置き、各行の式から参照すると、率が変わったときに 1 か所直すだけで済みます。
式の例
端数を切り捨てる場合、式の例は次のとおりです(2 行目の場合。率は J1 と J2 に置いた例)。
- C2(消費税):
=ROUNDDOWN(B2*10/100,0) - D2(請求額・税込):
=B2+C2 - E2(協力会費・税抜に掛ける場合):
=ROUNDDOWN(B2*$J$1,0) - F2(安全協力費):
=ROUNDDOWN(B2*$J$2,0) - H2(支払額):
=D2-E2-F2-G2
四捨五入なら ROUND、切り上げなら ROUNDUP に置き換えます。税込に率を掛ける取り決めなら、E2 と F2 の B2 を D2 に置き換えます。
式の確かめ方
- 支払一覧から 3 件ほど選ぶ(金額の大きいもの、端数の出やすいもの)
- 上の計算ツールに請求額と率を入れ、端数処理を支払一覧と同じにする
- 計算ツールの支払額と、支払一覧の H 列を比べる
- 1 件でも違えば、式の参照先と端数処理の関数を見直す
率を変えた月や、列を足した月は、この確かめを必ず行うようにすると、全行がずれたまま振込データを作る事故を防げます。
よくある間違い
- 表示形式だけで丸めている: セルの表示が整数でも、中の値に小数が残っていると、合計が 1 円ずれます。ROUNDDOWN などの関数で丸めます
- 率のセルが相対参照になっている: 式を下にコピーすると参照がずれます。$ を付けた絶対参照にします
- 行を足したときに式をコピーし忘れる: 支払額の列が空のまま振込データに進まないよう、合計行で件数も数えておきます
よくある質問
端数処理はどの関数を使えばよいですか？
切り捨ては ROUNDDOWN、四捨五入は ROUND、切り上げは ROUNDUP です。社内で決めた方法に合わせてください。
INT 関数で切り捨ててもよいですか？
金額が 0 以上なら同じ結果になりますが、マイナスの金額では結果が変わります。返品などでマイナスが出る表では ROUNDDOWN を使うと安心です。
支払一覧から振込データも作れますか？
支払額の列と振込先の情報を揃えておけば、銀行の総合振込の取り込み形式に合わせて並べ替えることができます。形式は利用している銀行の案内で確かめてください。
まとめ
支払一覧の控除の計算は、率を 1 か所に置く、関数で丸める、何件かを計算ツールで確かめるの 3 つを守れば、全行のずれを防げます。式を変えた月は、上の計算ツールで結果を確かめてから支払いに進みましょう。