エクセルで、2個以上の費目の合計金額の消費税額を、元の費目の消費税額に按分する方法を教えてください。具体的には、費目A 60円 費目B 50円 合計110円 で消費税額5円ですが、その5円を元の費目の金額を判断して3円、2円と割り振る方法です。(合計金額の消費税ありきで、各費目の消費税は、1円調整額の矛盾があってもかまいません)

一番納得できる回答の方に、300ポイント進呈。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2006/02/07 17:16:44
  • 終了:--

回答(7件)

id:vinaka No.1

vinaka回答回数232ベストアンサー獲得回数12006/02/07 17:28:26

ポイント20pt

単純な回答ですが、

費目A60円 で5%=3円

費目B50円 で5%=2.5円

とはどうでしょうか?

ただ、0.**円という単位も出てきてしまうので、以下のようにするのはどうでしょうか

A行→費目A

B行→費目B

C行→合計【費目A+費目B】

D行→費目Aの消費税

E行→費目Bの消費税


D行は(D1の場合)

=rounddown(A1*0.05,0)

E行は(E1の場合)

=rounddoen(B1*0.05,0)

にしてあげるのはどうでしょうか?

URLはダミーです。

http://www.zest-com.com/

妻 浮気 調査 探偵 興信所 at zest-com.com

もしくは

費目Aの消費税は

=費目の合計の消費税*(費目A/費目A+費目B)

にすると費目Aの消費税は出ますが。

小数点以下が出ないようにするのには

rounddownなどのround関数を使用するといいと思います。

id:iwasi4

早速ありがとうございます。例がよくなかったですかね。費目A 55円ないしは50円とすると、めんどくさくなりますね。できるなら、対象金額のセルを見て、割り振りをしたいということです。

(エクセルの初心者なので、バカダナァと思うところはズバッとご指摘くださいー。すみません)

2006/02/07 17:42:17
id:kimizu No.2

kimizu回答回数726ベストアンサー獲得回数212006/02/07 17:40:31

ポイント50pt

TRUNC(XXX,0)

を使えば簡単です。この関数は、小数点以下を切り捨てます。

C1とC2に単価を入れます。

合計(C3)には、SUM(C1:C2)などの合計関数を使って合計値を求める。

税込み合計(C4)には、TRUNC(C3*1.05,0)で代入します。

その横に、消費税額のみ(D4)をTRUNC(C3*0.05,0)で代入します。


個々の消費税額ですが、

D1 =TRUNC((C1/C3)*D4),0)

D2 =TRUNC((C2/C3)*D4),0)

のように、


「単価/合計*消費税額」の整数部分


を出せば、単価の消費税額が求められますよ。

id:iwasi4

おっぉ、なんとなくわかってきたぞ。

これでいいのかな。いいような気が・・・

でも回答閲覧続行で・・・

2006/02/07 17:45:26
id:Asuca No.3

Asuca回答回数281ベストアンサー獲得回数42006/02/07 18:05:59

ポイント20pt

最初から1つづつ計算したら駄目ですか?


A1のセル[60]  B1のセル[=rounddown(A1*0.05,0)]

A2のセル[50]  B2のセル[=rounddown(A2*0.05,0)]

A3のセル[=SUM(A1:A2)] B3のセル[=SUM(B1:B2)] C3のセル[=rounddown(A3*0.05,0)]


B3のセルとC3のセルの比較で差分も出ます

id:iwasi4

差分をB1、B2に自動的に還元したいのです。A1A2の金額の大小に応じて。つづく!

2006/02/08 00:59:24
id:hidesh No.4

hidesh回答回数43ベストアンサー獲得回数02006/02/07 19:01:53

ポイント10pt

http://ame.dip.jp/upload/1139/306153.xls

�A�b�v���[�_�����߂���

upしました。

こういうことでしょうか?

id:iwasi4

リンク先、みたいのですが、

http://ame.x0.com/upload.htmlに、リダイレクトされてしまいみれません。

2006/02/08 01:03:34
id:Baku7770 No.5

Baku7770回答回数2832ベストアンサー獲得回数1812006/02/07 19:04:09

ポイント50pt

 次のようなやり方で如何でしょうか?


 一般には、

金額×消費税率=消費税額(1円未満は切り捨て、四捨五入、切り上げのいずれか)

としておいて、金額最大の費目について

総額に対する消費税-その他の費目に対する消費税額

とします。


 例であれば、最大の費目はAの60円ですので

総額に対する消費税5円

Bの消費税2円(切り捨てです)

他に費目はありませんから、

Aの消費税額=総額に対する消費税額-その他の費目に対する消費税額の合計

      =5円-2円=3円

といった具合です。


 この計算方法のいいところは総額に対する消費税額と総消費税額が必ず一致することです。

id:iwasi4

ふむふむ。よいです。ね。

できれば、金額最大の費目について

総額に対する消費税−その他の費目に対する消費税額

の実際の式の記述が欲しい!サンプルでよいので。

2006/02/08 01:06:27
id:aki73ix No.6

aki73ix回答回数5224ベストアンサー獲得回数272006/02/07 19:23:18

EXCELで12個までの費用の合計金額の元の費用の消費税を按分するブックを作ってみました


Macroで実行するともっとスマートなのですが、セルだけで作るとこんな感じです


解説すると、まず、粗い消費税を元の消費税から費目の金額の割合で計算し、切り捨てた誤差の大きいものから、重み付けをして、その後差分を上乗せ、上乗せした時に、順位が同じものがあった場合、オーバーしてしまうので、上乗せした中で順位が低いものを上から順番に差が0になるまで引き算して元に戻すというものです

1円調整額の矛盾が生じないのが特徴ですが

同じ値段でも下の品目が優先的に振られます

id:iwasi4

おぉ!キター!良いかも!良いかも!

サンクスです。

?でも合計の消費税額違ってませんか?

合計額4000円として、消費税190円と出るし・・・

・・・・・・・・あれ・・わからん

続行で!

2006/02/08 08:57:47
id:aki73ix No.7

aki73ix回答回数5224ベストアンサー獲得回数272006/02/08 09:54:28

ポイント300pt

6番目の回答者です


> ?でも合計の消費税額違ってませんか?

合計額4000円として、消費税190円と出るし・・・

税込みで4000円の場合 3810円+消費税190円(3810*0.05=190.5)なのでOKですね


そう言われてみれば、税込みで110円(定価100円)でも、税抜きで110円(定価110円)でも消費税は5円ですね


税込み計算:110-110/1.05=5.23円

税抜き計算:110*0.05=5.5円


もしも、税抜きで入れたい場合はBセルの消費税のところを =TRUNC(B1-B1/1.05)から =TRUNC(B1*0.05)に変えてやれば 4000円で 200円の消費税になり、後は変更の必要はありません


一応税抜き計算もできるシートを追加したものをアップします

同様に、消費税の重み付けをして、誤差計算するので、このセルだけ変更すれば、消費税を四捨五入で計算したり、切り上げにしたりしても、税率が変わっても対応可能です

id:iwasi4

はい。その後自己解決していました。

今までのところ、これが一番理想に近いものです。後問題は、こういった費目ごとの調整が、階層的に行われて(4階層ほど)いるので、(※つまり1回目の調整の結果(各費目に配分された消費税)が、今度は親になり、さらにその構成要素にさかのぼっていく、というところです。

まぁ、これは何とかなるかならないか・・・

ならないようであれば、またトピ立てて、質問するようにします。

aki73ixさん大変ありがとうございます。

2006/02/09 09:23:17

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません