エクセルで合計量が一定になるように計算表を作りたい
xpでエクセル2000を使用しています。
社命により、
余ったピーナッツ(ピーナッツは例えです)を販売するため、
条件の違う複数の種類のピーナッツを混ぜて出荷することになりました。
その混合する際の各種類の割合を出さなければならず、
頭を抱えています。
エクセルの苦手な私にまかされてしまったため、
胃の痛い思いをしています。
与えられる数値
商品:ピーナッツ
リットル重:ピーナッツ1リットル当りの重さ
(例:1リットル=500グラム)
千粒重:ピーナッツ1,000個の重さ
(例:1,000個=1.2グラム)
有効率:食食べられるピーナッツの割合
(例:80%)
ここからリットル重と千粒重、有効率の違うピーナッツを
3種類混ぜ合わせて、1リットルのミックスを作るときに、
各種類の食べられるピーナッツの個数が、
均等になるようにしたいと思います。
なお1種類のピーナッツについて、
一つのロットでは不足するため、
先入先出法で、
複数のロットを使用する場合もあります。
ロットによって、リットル粒、千粒重、有効率が異なります。
まず各種類ごとにリットル重を千粒重で割って、
これに有効率を掛けて、
1リットルあたりの食べられるピーナッツの数を出しました。
1リットル=500グラム÷1.2グラム×1,000粒×80%=333,333
次に1をこの数字で割りました。
1÷333,333=0.0000003
これを3種類のそれぞれの値の合計値で割って、
それぞれの値の合計値に占める、各種類のこの数字の割合を出しました。
0.0000003÷(0.0000003+2種類目の値+3種類目の値)=0.38552
この値が1リットルを作るときの種類ごとのリットル数になります。
これにリットル重をかけることで、
必要なグラム数を算出しました。
0.38552×500=192.76グラム
1種類のピーナッツにつき1つのロットしかない場合には、
この数式で解決できるのですが、
1種類のピーナッツにつき、
複数ロットがある場合はどうしたらよいでしょう。
できれば上の与えられる数字を入力した際に、
自動的に計算できるような表が作れれば、
私の胃の痛みも少しは治まるのですが、
どなたかご協力いただけませんでしょうか。
説明不足の点ありましたらご指摘下さい。
お礼
回答ありがとうございます。 なかなか難しいですね。