Excelで差額をつけた計算式

質問

歓送迎会の幹事の一人になってしまったので、
Excelで金額などの計算シートをつくってくれ！
と言われました。
それぞれ会費に男女差額をつけなければならないと
言われてしまったのですが、(金額差はその時々で変わるらしいです)

A1セルに総参加人数
A2セルに男子参加人数
A3セルに女子参加人数
A4セルに男女差額分（その時々で入力する値が違うとして）
B4セルにはその時かかる飲み会の合計金額
が入力されているとした場合、
A2とA3セルの横に男女それぞれ、差額分をつけた
一人当たりの会費を出す場合の計算式はどのようにしたらいいでしょうか？

まだまだExcel勉強中でわからないことだらけで
うまく説明もできてなくて申し訳ありませんが、
よろしくお願いします。

回答No.7

noname#204879 · ベストアンサー

A　　　　　 B　　　　　　　　　　　C
1　　　55　←総参加人数
2　　　35　←男子参加人数
3　　　20　←女子参加人数
4　　\500　　　　\123,456　←左は差額、右は宴会合計額
5　\2,430　←男子会費
6　\1,930　←女子会費
7　　　　　　　　\123,650　←徴収合計額
8　　　　　　　　　　\194　←余り額

A1: =SUM(A2:A3)
A5: =CEILING((B4+A4*A3)/A1,10)
A6: =A5-A4
B7: =A5*A2+A6*A3
B8: =B7-B4

お礼

皆様ご回答ありがとうございます！
ヘタな説明だったのに、皆様がわかりやすく説明してくださったおかげで無事、幹事の仕事を終えることができました。
お礼が遅くなってしまい申し訳ありません。
また、この場をお借りして皆様にお礼申し上げます。
個々にお礼できずすみません；
ありがとうございました！！

回答No.6

zap35

私も同じような計算良くやりました。ゴールシークを使ってみませんか。まさにこのような目的で使う機能です。

考え方は次で良いでしょうか。
お店は男女関係ないので、「人数×平均単価＝支払総額」になる。これを男女の差額をつけて按分する。

各セルに以下の値と式を入れます（「セル：」の部分は入力する必要はありません。式以外は数字を入れます）
a1： = a2 + a3 、ｂ１：平均単価、ｃ１：= a1 * b1 　（a1は総人数、c1は支払総額とする）

a2：男性人数、b2: = c3 ＋ a4、c2： = a2 * b2

a3:女性人数、b3：　空白、c3: = a3 * b3

a4：つけたい差額金額　、　c4：　=SUM( c2 : c3 )

これで準備はＯＫ。「ツール」→「ゴールシーク」を選び
　数値目標セル：　c4
　目標値：　　　　　ｃ１の支払総額を手入力
　変化させるセル：　b3

これでＯＫボタンをクリックすれば自動で計算してくれます。
端数が出たり、たまに収束違えられない場合もありますが大体はこれで済みます

回答No.5

blindcrow

【続き】No.4です。

これをセル番地に置き換えて、女子の会費となる「Ａ３」セルに計算式を入力します。

=(B4-A2*A4)/A1

次は男子ですが、女子の会費に差額分を足したものが男子の会費になりますので、差額を含めた男子の会費を「Ｂ」とすると、

(会費総合計－男子参加人数×差額)÷総参加人数＋差額＝Ｂ

これをセル番地に置き換えて、男子の会費となる「Ａ２」セルに計算式を入力します。

=(B4-A2*A4)/A1+A4

これで男女ともの会費が割り出せます。
計算式はN0.1さんの書いているもので正解ですね。

エクセルを前にすると、セル番地や計算式でけっこう複雑に考えがちです。目的と計算対照をしっかり把握しないと、本来の目的とかけはなれてしまいますので、一度自分なりに組み立ててからエクセルに応用することを心がけて下さい。

回答No.4

blindcrow

こんにちは。

基本的にエクセルうんぬんより、数学的な割り出し方が求められるものですね。
計算式を一度紙に書いてみると分かりやすいです。

まず割り出すのは、差額を省いた一人頭の会費です。この場合、女子の会費ということになります。
基本となる差額を省いた会費を「Ａ」とします。

そうすると計算式は、

会費総合計＝男子参加人数×（Ａ＋差額）＋女子参加人数×Ａ

となります。これを組み替えると、

会費総合計＝男子参加人数×Ａ＋男子参加人数×差額＋女子参加人数×Ａ
　↓
会費総合計－男子参加人数×差額＝男子参加人数×Ａ＋＋女子参加人数×Ａ
　↓
会費総合計－男子参加人数×差額＝総参加人数×Ａ
　↓
(会費総合計－男子参加人数×差額)÷総参加人数＝Ａ

ということになります。要するに女子の会費は、会費総合計から男子の参加人数に差額をかけた分を引いた金額を、女子の参加人数で割ったものになるということですね。

回答No.3

mshr1962

女子の支払額をXとすると
B4=A2*(X+A4)+A3*X=(A2+A3)X+A2*A4
女子=(B4-A2*A4)/(A2+A3)
男子=(B4-A2*A4)/(A2+A3)+A4
で求めることができます。もっとも計算上10円単位とか100円単位ですよね。
10円単位で女子分を切り上げとして
女子=ROUNDUP((B4-A2*A4)/(A2+A3),-1)
男子=ROUNDOWN((B4-A3*女子)/A2,-1)
幹事が調整する差額=A3*女子-A2*男子-B4　（最大 A2*10円のマイナス）
※幹事が調整額を払いたくない場合は男子の計算をROUNDUPで行ってください。

回答No.2

imogasi

>Excel勉強中でわからないことだらけで
そんなエクセルのことではなくて、それ以前に、文章で、男女の会費計算方法が、質問には説明されてないのでは。
文章力と、他人（回答志向者）への想像（何のデータが必要か・思いやり）の欠如ですよ。
計算式は四則演算で済むのではないですか。であれば普通質問するまでもない。計算方法は質問者の勝手に決めることで、エクセルの出番などは、電卓の延長でしょう。
男女は総人数比率で総費用を按分するとか。例　男子：女子＝２：１
男女総額で例えば２万円の差をつけるのか。
男子や女子の中では差はないのか（例　部長は２倍とか）
色々想像はできるが、情報が質問の中にない。
＞A4セルに男女差額分（その時々で入力する値が違うとして）
一律（全員）の差額か。

回答No.1

lune_ec

男子の会費=(B4-A2*A4)/A1+A4
　女子の会費=(B4-A2*A4)/A1

でいかがでしょう。
ただし、端数処理はしていませんのでそこはお好みでどうぞ。