Excelで按分計算をする場合は、総額、比率、比率の合計を使って計算します。 基本の式は「総額 × 各項目の比率 ÷ 比率の合計」です。
この記事では、Excelで総額を比率に応じて分ける方法、3:2:1で按分する具体例、ROUND関数を使った端数処理、合計額を元の総額と一致させる調整方法を解説します。 家事按分、期間按分、部門別配賦で使えるExcel式も紹介します。
すぐに計算したい方はこちら
- 按分計算ツール：総額を比率で分けたい場合
- 比率計算ツール：比率を割合・金額に変換したい場合
- 家事按分計算ツール：家賃・光熱費・通信費を事業割合で計算したい場合
- 期間按分計算ツール：日数・月数で費用を分けたい場合
Excelで按分計算する基本式
Excelで按分計算をする基本式は、次のとおりです。
按分額 = 総額 × 各項目の比率 ÷ 比率の合計
Excelでは、総額がB1、比率がB4:B6に入っている場合、1行目の按分額は次の式で計算できます。
=$B$1*B4/SUM($B$4:$B$6)この式を下の行にコピーすれば、各項目の按分額を計算できます。
3:2:1で按分計算するExcel例
たとえば、10,000円をA・B・Cに3:2:1で按分する場合、Excelでは次のように表を作ります。
| セル | 内容 | 入力例 |
|---|---|---|
| B1 | 総額 | 10000 |
| A4:A6 | 項目名 | A、B、C |
| B4:B6 | 比率 | 3、2、1 |
| C4:C6 | 按分率 | 比率 ÷ 比率合計 |
| D4:D6 | 按分額 | 総額 × 按分率 |
按分率を求める式
C4に次の式を入力します。
=B4/SUM($B$4:$B$6)C4をC6までコピーすると、A・B・Cそれぞれの按分率を計算できます。 表示形式をパーセントにすると、50%、33.33%、16.67%のように表示できます。
按分額を求める式
D4に次の式を入力します。
=$B$1*B4/SUM($B$4:$B$6)D4をD6までコピーすると、10,000円を3:2:1で分けた金額を計算できます。
| 項目 | 比率 | 按分率 | 按分額 |
|---|---|---|---|
| A | 3 | 50% | 5,000円 |
| B | 2 | 約33.33% | 約3,333円 |
| C | 1 | 約16.67% | 約1,667円 |
ROUND関数で端数を処理する方法
按分計算では、割り切れない金額が出ることがあります。 端数を処理したい場合は、ROUND関数を使います。
1円単位で四捨五入する
=ROUND($B$1*B4/SUM($B$4:$B$6),0)10円単位で四捨五入する
=ROUND($B$1*B4/SUM($B$4:$B$6),-1)100円単位で四捨五入する
=ROUND($B$1*B4/SUM($B$4:$B$6),-2)1000円単位で四捨五入する
=ROUND($B$1*B4/SUM($B$4:$B$6),-3)切り捨てたい場合はROUNDDOWN関数、切り上げたい場合はROUNDUP関数を使います。
=ROUNDDOWN($B$1*B4/SUM($B$4:$B$6),0)=ROUNDUP($B$1*B4/SUM($B$4:$B$6),0)按分後の合計額を総額と一致させる方法
各行で四捨五入すると、按分後の合計額が元の総額と一致しないことがあります。 たとえば、10,000円を3:2:1で按分して1円単位に丸めると、合計が10,000円からずれる場合があります。
その場合は、最後の項目で差額を調整する方法が簡単です。
最後の行で差額を調整する例
E4、E5には通常の丸め式を入れます。
=ROUND($B$1*B4/SUM($B$4:$B$6),0)最後の行であるE6には、総額から上の行の合計を引く式を入れます。
=$B$1-SUM(E4:E5)これにより、E4:E6の合計が必ずB1の総額と一致します。
最大金額の項目に差額を調整する方法
最後の項目ではなく、最も大きい金額の項目に差額を入れたい場合は、丸め後の金額に差額を加えます。
丸め後の金額がE4:E6にある場合、F4に次の式を入力してF6までコピーします。
=E4+IF(ROW(E4)=ROW(INDEX($E$4:$E$6,MATCH(MAX($E$4:$E$6),$E$4:$E$6,0))),$B$1-SUM($E$4:$E$6),0)この式では、E4:E6の中で最も大きい金額の行にだけ、総額との差額を加えます。 同額の最大値が複数ある場合は、最初に見つかった行に差額が入ります。
家事按分をExcelで計算する方法
家事按分では、家賃、光熱費、通信費などを、事業利用割合に応じて分けます。 基本式は次のとおりです。
経費計上候補額 = 支払額 × 事業利用割合
家賃を面積で按分するExcel式
支払額がB1、事業で使う面積がB4、自宅全体の面積がC4の場合、事業分は次の式で計算できます。
=$B$1*B4/C4たとえば、家賃120,000円、仕事部屋8㎡、自宅全体40㎡なら、次のように計算できます。
=120000*8/40結果は24,000円です。 家賃・光熱費・通信費の家事按分をフォームで計算したい場合は、家事按分計算ツールを使ってください。
期間按分をExcelで計算する方法
期間按分では、費用や売上を対象期間の日数・月数に応じて分けます。 基本式は次のとおりです。
対象期間分の金額 = 総額 × 対象期間 ÷ 全体期間
日数で期間按分するExcel式
総額がB1、対象日数がB4、全体日数がC4の場合、次の式で計算できます。
=$B$1*B4/C4たとえば、年額120,000円を365日のうち275日分で計算する場合は、次の式です。
=120000*275/365開始日と終了日から日数を計算する式
開始日がB4、終了日がC4の場合、開始日と終了日を含めて日数を数えるなら、次の式を使います。
=C4-B4+1終了日を含めない場合は、次の式です。
=C4-B4契約期間や対象期間をフォームで入力して計算したい場合は、期間按分計算ツールを使ってください。
日割り計算をExcelで行う方法
月額料金や家賃を日割り計算する場合は、次の式を使います。
日割り額 = 月額 × 対象日数 ÷ 分母日数
月額がB1、対象日数がB4、分母日数がC4の場合、Excel式は次のとおりです。
=$B$1*B4/C4たとえば、月額30,000円を10日分だけ計算し、分母日数を30日とする場合は、次の式です。
=30000*10/30結果は10,000円です。 月額料金や家賃の日割り計算をフォームで行いたい場合は、日割り計算ツールを使ってください。
部門別配賦をExcelで計算する方法
部門別配賦では、本社費、総務費、家賃、共通システム費などを、売上高・人数・面積などの基準で部門別に分けます。
基本式は次のとおりです。
配賦額 = 共通費 × 各部門の配賦基準値 ÷ 配賦基準値の合計
共通費がB1、各部門の配賦基準値がB4:B6の場合、D4には次の式を入れます。
=$B$1*B4/SUM($B$4:$B$6)売上高比で配賦するならB4:B6に各部門の売上高、人数比で配賦するなら各部門の人数、面積比で配賦するなら各部門の使用面積を入力します。
部門別配賦をフォームで計算したい場合は、部門別配賦計算ツールを使ってください。
Excelで按分計算するときのよくあるミス
比率の合計範囲を固定していない
式を下にコピーするとき、SUMの範囲を固定していないと、参照範囲がずれてしまいます。 そのため、次のようにドル記号を使って範囲を固定します。
=B4/SUM($B$4:$B$6)総額セルも、次のように固定します。
=$B$1*B4/SUM($B$4:$B$6)端数処理後の合計を確認していない
ROUND、ROUNDDOWN、ROUNDUPで各行を丸めると、合計額が元の総額と一致しないことがあります。 按分後は、必ず合計額を確認してください。
=SUM(E4:E6)合計が総額とずれる場合は、最後の行や最大金額の行で差額を調整します。
按分基準が実態に合っていない
Excelの式が正しくても、按分基準が実態に合っていなければ、計算結果の説明が難しくなります。 家賃なら面積、期間費用なら日数や月数、部門別配賦なら売上高・人数・面積など、目的に合った基準を選ぶことが大切です。
Excel按分計算のテンプレート例
以下のような表を作ると、基本的な按分計算を管理しやすくなります。
| 列 | 項目 | 入力・式 |
|---|---|---|
| A列 | 項目名 | A、B、Cなど |
| B列 | 比率・基準値 | 3、2、1など |
| C列 | 按分率 | =B4/SUM($B$4:$B$6) |
| D列 | 按分額 | =$B$1*B4/SUM($B$4:$B$6) |
| E列 | 丸め後金額 | =ROUND(D4,0) |
| F列 | 調整後金額 | 最後の行で差額調整 |
よくある質問
Excelで按分計算する式は何ですか？
基本式は「=総額*各項目の比率/SUM(比率範囲)」です。 たとえば総額がB1、比率がB4:B6にある場合は、次の式を使います。
=$B$1*B4/SUM($B$4:$B$6)Excelで3:2:1の按分計算をするには？
比率欄に3、2、1を入力し、各行で「総額 × 比率 ÷ 比率合計」を計算します。 総額がB1、比率がB4:B6なら、D4に次の式を入れて下にコピーします。
=$B$1*B4/SUM($B$4:$B$6)按分計算で端数を四捨五入するには？
ROUND関数を使います。 1円単位で四捨五入する場合は、次の式です。
=ROUND($B$1*B4/SUM($B$4:$B$6),0)按分後の合計が総額と合わない場合は？
丸め処理によって差額が出ている可能性があります。 最後の行に「総額 − それまでの合計」を入れると、合計額を総額と一致させられます。
=$B$1-SUM(E4:E5)家事按分もExcelで計算できますか？
はい。 支払額に事業利用割合を掛ければ計算できます。 たとえば家賃を面積で按分する場合は、「家賃 × 事業利用面積 ÷ 自宅全体面積」で計算します。
Googleスプレッドシートでも同じ式を使えますか？
基本的には同じ式を使えます。 SUM、ROUND、ROUNDDOWN、ROUNDUPなどの関数は、ExcelとGoogleスプレッドシートのどちらでも使えます。
まとめ
Excelで按分計算する基本式は「総額 × 各項目の比率 ÷ 比率の合計」です。 総額セルと比率合計の範囲を固定しておくと、式をコピーしても正しく計算できます。
端数を処理する場合はROUND関数、切り捨てならROUNDDOWN関数、切り上げならROUNDUP関数を使います。 ただし、各行で丸めると合計額が元の総額とずれることがあるため、最後の行や最大金額の行で差額を調整すると実務で使いやすくなります。
Excelで表を作るのが面倒な場合や、端数調整まで自動で行いたい場合は、以下の無料ツールを使ってください。
このページの内容は、Excelで按分計算を行うための一般的な解説です。 会計処理、税務申告、請求、契約処理などで使う場合は、契約書、社内ルール、税理士・会計士などの専門家の確認をおすすめします。
