エクセルで合計量が一定になるように計算表を作りたい

質問

xpでエクセル2000を使用しています。

社命により、
余ったピーナッツ（ピーナッツは例えです）を販売するため、
条件の違う複数の種類のピーナッツを混ぜて出荷することになりました。
その混合する際の各種類の割合を出さなければならず、
頭を抱えています。
エクセルの苦手な私にまかされてしまったため、
胃の痛い思いをしています。

与えられる数値
商品：ピーナッツ
リットル重：ピーナッツ1リットル当りの重さ
（例：1リットル＝500グラム）
千粒重：ピーナッツ1,000個の重さ
（例：1,000個＝１．２グラム）
有効率：食食べられるピーナッツの割合
（例：８０％）

ここからリットル重と千粒重、有効率の違うピーナッツを
3種類混ぜ合わせて、1リットルのミックスを作るときに、
各種類の食べられるピーナッツの個数が、
均等になるようにしたいと思います。
なお1種類のピーナッツについて、
一つのロットでは不足するため、
先入先出法で、
複数のロットを使用する場合もあります。
ロットによって、リットル粒、千粒重、有効率が異なります。

まず各種類ごとにリットル重を千粒重で割って、
これに有効率を掛けて、
1リットルあたりの食べられるピーナッツの数を出しました。

１リットル＝500グラム÷1.2グラム×1,000粒×80％＝333,333

次に１をこの数字で割りました。
１÷333,333＝0.0000003

これを3種類のそれぞれの値の合計値で割って、
それぞれの値の合計値に占める、各種類のこの数字の割合を出しました。

0.0000003÷（0.0000003＋2種類目の値＋3種類目の値）＝0.38552

この値が1リットルを作るときの種類ごとのリットル数になります。

これにリットル重をかけることで、
必要なグラム数を算出しました。

0.38552×500＝192.76グラム

1種類のピーナッツにつき１つのロットしかない場合には、
この数式で解決できるのですが、
1種類のピーナッツにつき、
複数ロットがある場合はどうしたらよいでしょう。

できれば上の与えられる数字を入力した際に、
自動的に計算できるような表が作れれば、
私の胃の痛みも少しは治まるのですが、
どなたかご協力いただけませんでしょうか。
説明不足の点ありましたらご指摘下さい。

回答No.7

kagakusuki

<回答番号:ANo.6からの続き>
　次に

C23=IF(A19="","",IF(C19=C8,C12-C15,VLOOKUP(C19,Sheet1!$F:$J,5,FALSE)))

D23=IF(A19="","",IF(D19=D8,D12-D15,VLOOKUP(D19,Sheet1!$K:$O,5,FALSE)))

と入力した後、B8セルをコピーして、C8セルとD8セルに貼り付けて下さい。
　次に、B12セルをコピーして、C12セルとD12セルに貼り付けて下さい。

　そして、A18～D27の範囲をコピーして、必要な回数だけ、１行ずつ空けて貼り付けて下さい。（必要な回数の目安の求め方は回答番号:ANo.5を参照の事）
次に、

F3="ロット"

F4="混合重量"

G3=IF(COUNTA($B$3:$D$4)=6,B$3,"")

G4=IF(G3="","",SUMIF(B$8:B$107,G3,B$15:B$114))

と入力して下さい。
　次に、G4セルをコピーしてG6セルに貼り付けて下さい。
　次にG3～G6の範囲をコピーして、H列とI列の同じ行に貼り付けて下さい。
　次に、

G5=IF(OR(G3=OFFSET(B8,ROWS($A$7:$A$17)*(COUNTIF($A:$A,"ロット")-1),),G3=""),"",INDEX(Sheet1!$A:$A,MATCH(G3,Sheet1!$A:$A,0)+1))

H5=IF(OR(H3=OFFSET(C8,ROWS($A$7:$A$17)*(COUNTIF($A:$A,"ロット")-1),),H3=""),"",INDEX(Sheet1!$F:$F,MATCH(H3,Sheet1!$F:$F,0)+1))

I5=IF(OR(I3=OFFSET(D8,ROWS($A$7:$A$17)*(COUNTIF($A:$A,"ロット")-1),),I3=""),"",INDEX(Sheet1!$K:$K,MATCH(I3,Sheet1!$K:$K,0)+1))

　次に、F5～I6の範囲をコピーして、その直下に連続して適当な回数（１種類のピーナッツにおけるロットの交換回数＋１）だけ貼り付けて下さい。

　以上で準備は完了です。
　使用方法や注意事項は、回答番号:ANo.4～5と同じです。

回答No.6

kagakusuki

前の回答では流石に数式が長過ぎましたので、シェイプアップ版も作ってみました。
　まず、Sheet1は変更ありません。
　そして、一部重複する部分もありますが、Sheet2の各セルには、以下の数式を入力して下さい。

A3="開始ロット"

A4="残りの重量"

A9="リットル重"

A10="千粒重"

A11="有効率"

A12="ロット重"

A13="可食千粒数"

A14="可食千粒体積"

A15="混合重量"

A16="体積小計"

B1=Sheet1!$A$1

C1=Sheet1!$F$1

D1=Sheet1!$K$1

B9=IF(B8="","",VLOOKUP(B8,Sheet1!$A:$E,2,FALSE))

C9=IF(C8="","",VLOOKUP(C8,Sheet1!$J:$J,2,FALSE))

D9=IF(D8="","",VLOOKUP(D8,Sheet1!$K:$O,2,FALSE))

B10=IF(B8="","",VLOOKUP(B8,Sheet1!$A:$E,3,FALSE))

C10=IF(C8="","",VLOOKUP(C8,Sheet1!$J:$J,3,FALSE))

D10=IF(D8="","",VLOOKUP(D8,Sheet1!$K:$O,3,FALSE))

B11=IF(B8="","",VLOOKUP(B8,Sheet1!$A:$E,4,FALSE))

C11=IF(C8="","",VLOOKUP(C8,Sheet1!$J:$J,4,FALSE))

D11=IF(D8="","",VLOOKUP(D8,Sheet1!$K:$O,4,FALSE))

B13=IF(COUNT(B10:B12)=3,B12*(B11/100)/B10,"")

B14=IF(COUNT(B9:B11)=3,B10/B9*B11/100),"")

B15=IF(COUNT($B9:$D12)=12,IF(SUM($B14:$14)*MIN($B13:$D13)<1,MIN($B13:$D13),1/SUM($B14:$D14)*B10/(B11/100),"")

　次に、B3～D3の範囲をコピーして、B7～D7の範囲とG2～I2の範囲に貼り付けて下さい。
　次に、B13～B15の範囲をコピーして、C13～C15の範囲とD13～D15の範囲に貼り付けて下さい。
　次に、A7～D16の範囲をコピーして、A18～D27の範囲に貼り付けて下さい。
　次に、
A8=IF(COUNTA($B$3:$D$4)=6,"ロット","")

A19=IF(B16<1,"ロット","")

B8=IF($A8="","",B$3)

B12=IF(B11="","",B$4)

B16=IF(COUNT(B9:D12)=12,IF(SUM(B14:D14)*MIN(B13:D13)<1,(SUM(B14:D14)*MIN(B13:D13),1),"")

B27=IF(COUNT(B20:D23)=12,IF(SUM(B25:D25)*MIN(B24:D24)<1-B16,(SUM(B25:D25)*MIN(B24:D24)+B16,1),"")

B19=IF(A19="","",INDEX(Sheet1!$A:$A,MATCH(B8,Sheet1!$A:$A,0)+(B13=MIN($B13:$D13))))
C19=IF(A19="","",INDEX(Sheet1!$F:$F,MATCH(C8,Sheet1!$F:$F,0)+(C13=MIN($B13:$D13))))
D19=IF(A19="","",INDEX(Sheet1!$K:$K,MATCH(D8,Sheet1!$K:$K,0)+(D13=MIN($B13:$D13))))
B23=IF(A19="","",IF(B19=B8,B12-B15,VLOOKUP(B19,Sheet1!$A:$E,5,FALSE)))

と入力して下さい。
（次の回答へ続きます）

回答No.5

kagakusuki

回答番号:ANo.4の続きです。
B19=IF($A19="","",INDEX((Sheet1!$A$3:$A$99,Sheet1!$F$3:$F$99,Sheet1!$K$3:$K$99),MATCH(B8,IF(COLUMNS($B:B)=1,Sheet1!$A$3:$A$99,IF(COLUMNS($B:B)=2,Sheet1!$F$3:$F$99,Sheet1!$K$3:$K$99)),0)+(B13=MIN($B13:$D13),,COLUMNS($B:B)))

B23=IF(B22="","",IF(B19=B8,B12-B15,INDEX((Sheet1!$B$3:$E$99,Sheet1!$G$3:$J$99,Sheet1!$L$3:$O$99),MATCH(OFFSET(B23,-MATCH($A23,$A$9:$A$12,0),),IF(COLUMNS($B:B)=1,Sheet1!$A$3:$A$99,IF(COLUMNS($B:B)=2,Sheet1!$F$3:$F$99,Sheet1!$K$3:$K$99)),0),MATCH($A23,Sheet1!$B$2:$E$2,0),COLUMNS($B:B))))

と入力した後、B8～B15の範囲をコピーして、C列とD列の同じ行に貼り付けて下さい。
　同様に、B19～B26の範囲をコピーして、C列とD列の同じ行に貼り付けて下さい。
　そして、A18～D27の範囲をコピーして、必要な回数だけ、１行ずつ空けて貼り付けて下さい。
　この場合の必要な回数の目安は、次の計算式で求められます。
必要な回数＝(INT((1/3)リットル÷最も体積の少ないロットの体積)＋2)×3

　次に、

F3="ロット"

F4="混合重量"

G3=IF(COUNT($B$3:$D$4)=6,B$3,"")

G5=IF(OR(G3=OFFSET(B$8,ROWS($A$7:$A$17)*(COUNTIF($A:$A,"ロット")-1),),G3=""),"",INDEX((Sheet1!$A$3:$A$99,Sheet1!$F$3:$F$99,Sheet1!$K$3:$K$99),MATCH(G3,IF(COLUMNS($G:G)=1,Sheet1!$A$3:$A$99,IF(COLUMNS($G:G)=2,Sheet1!$F$3:$F$99,Sheet1!$K$3:$K$99)),0)+1,,COLUMNS($G:G)))

G4=IF(G3="","",SUMIF(B$8:B$107,G3,B$15:B$114))

と入力して下さい。
　【注意】G4の数式における、$107と$114は、それぞれ最も下の行にあるロット欄と混合重量欄の行数（の一例）です。
　この行数はコピーしたA7～D16の範囲を貼り付ける回数によって異なりますが（この例では８回貼り付けた場合に相当）、貼り付け回数が９回以上にならない限りは、$107と$114としたままでも、動作上は問題ありません。
　次に、G4セルをコピーしてG6セルに貼り付けて下さい。
　次に、F3～F4の範囲をコピーしてF5～F6の範囲に貼り付け、G3～G6の範囲をコピーしてH列とI列の同じ行に貼り付けて下さい。
　次に、F5～I6の範囲をコピーして、その直下に連続して適当な回数（１種類のピーナッツにおけるロットの交換回数＋１）だけ貼り付けて下さい。

　以上で準備は完了です。
　後は、Sheet2のB2～D2セルに、ミックスを作り始める時に使用する、各種類毎のロットの名称を入力し、B3～D3セルに、それらのロットに残っているピーナッツの重量を入力して下さい。
　すると、G～I列に使用されるロットの名称と、ミックスを作るために必要となる、各ロット毎のピーナッツの重量が表示されます。

　尚、上記の数式は、コピー&ペーストをし易くするために、長い式となっておりますが、コピー&ペーストに依らずに数式を入力する場合は、もっと短い数式でも同じ事が出来ます。

回答No.4

kagakusuki

回答番号:ANo.2、3です。
　今仮に、Sheet1にはロットのデータが入力されていて、Sheet2で計算処理を行うものとします。
　尚、この回答におけるエクセル表では、混ぜる重量を自動計算する際にSUMIF関数を使用しているため、各数値データの中に、ロット番号と同じ数値が存在した場合、誤った重量が表示されてしまいます。
　ですから、このバグが現れない様にするために、ロットの名称が数字のみで構成されている場合には、数値データと区別するために、「No.」等の何らかの文字を付け加えて、文字列データとして扱われる形式に変更して下さい。
　Sheet1のA1セルには番目のピーナッツの種類名（仮にＡとします）、F1セルには番目の種類名（仮にＢとします）、K1セルには番目の種類名（仮にＣとします）が入力されていて、A列にはＡのロット名、B列にはＡのリットル重、C列にはＡの千粒重、D列にはＡの有効率、E列にはＡのロットの重量、F列にはＢのロット名、・・・・・・O列にはＣのロットの重量が入力されるものします。
　まず、Sheet1のA2セルには"ロット"、次に順次右に向かって、"リットル重"、"千粒重"、"有効率"、"ロット重"の順番で繰り返し、O2セルまで入力して下さい。
　次に、3行目には1番目のロットのデータ、4行目には2番目のロットのデータ、5行目には3番目のロットのデータ・・・・・、99行目には97番目のロットのデータという具合にロットのデータを入力して下さい。

　次にSheet2に移りまして、

A3="開始ロット"

A4="残りの重量"

A9="リットル重"

A10="千粒重"

A11="有効率"

A12="ロット重"

A13="可食千粒数"

A14="可食千粒体積"

A15="混合重量"

A16="体積小計"

B1=Sheet1!$A$1

C1=Sheet1!$F$1

D1=Sheet1!$K$1

B9=IF(B8="","",INDEX((Sheet1!$B$3:$E$99,Sheet1!$G$3:$J$99,Sheet1!$L$3:$O$99),MATCH(OFFSET(B9,-MATCH($A9,$A$9:$A$12,0),),IF(COLUMNS($B:B)=1,Sheet1!$A$3:$A$99,IF(COLUMNS($B:B)=2,Sheet1!$F$3:$F$99,Sheet1!$K$3:$K$99)),0),MATCH($A9,Sheet1!$B$2:$E$2,0),COLUMNS($B:B)))

B13=IF(COUNT(B10:B12)=3,B12*(B11/100)/B10,"")

B14=IF(COUNT(B9:B11)=3,B10/B9*B11/100),"")

B15=IF(COUNT($B9:$D12)=12,IF(SUM($B14:$14)*MIN($B13:$D13)<1,MIN($B13:$D13),1/SUM($B14:$D14)*B10/(B11/100),"")

と入力してから、B9セルをコピーして、B10、B11、B12に貼り付けて下さい。
　次に、B3～D3の範囲をコピーして、B7～D7の範囲とG2～I2の範囲に貼り付けて下さい。
　次に、A7～D16の範囲をコピーして、A18～D27の範囲に貼り付けて下さい。
　次に、

A8=IF(COUNTA($B$3:$D$4)=6,"ロット","")

A19=IF(B16<1,"ロット","")

B16=IF(COUNT(B9:D12)=12,IF(SUM(B14:D14)*MIN(B13:D13)<1,(SUM(B14:D14)*MIN(B13:D13),1),"")

B27=IF(COUNT(B20:D23)=12,IF(SUM(B25:D25)*MIN(B24:D24)<1-B16,(SUM(B25:D25)*MIN(B24:D24)+B16,1),"")

B8=IF($A8="","",B$3)

B12=IF(B11="","",B$4)

と入力して下さい。

　まだ途中ですが、回答欄の制限文字数をオーバーしたため、これ以上書き込む事が出来ません。
　本来は分割して回答する事は、このサイトのルールに違反する行為なのですが、仕方がありませんので、残りは次の回答に記す事にさせて頂きます。

回答No.3

kagakusuki

回答番号:ANo.2です。

>1種類のピーナッツにつき、
複数ロットがある場合はどうしたらよいでしょう。

　考え方としては、各種類毎のロットに残っているピーナッツの中に、食べられるピーナッツが何粒あるのかを、計算で求めます。

食べられる粒の数（千粒単位）＝ロットに残っているピーナッツの重量×有効率÷千粒重

　すると、食べられる粒が最も少ないロットの中には、食べられる粒数が幾つあるのかが判明します。
　今仮に、その粒数をｎ×1000個とします。
　食べられる粒を千粒含んだピーナッツの体積を、仮に可食千粒体積と呼ぶ事にしますと、可食千粒体積は、

可食千粒体積＝千粒重÷(リットル重×有効率)

になりますから、食べられる粒をｎ×1000粒含んだピーナッツの体積は、

可食千粒体積×ｎ[リットル]

になります。
　又、食べられる粒をｎ×1000粒含んだピーナッツの重量は、
千粒重×ｎ÷有効率

になります。
　さて、Ａ、Ｂ、Ｃ、各種類のピーナッツについて、食べられる粒をｎ×1000粒含む重量ずつロットから取り出して、混ぜ合わせた場合の体積は、

Ａの可食千粒体積×ｎ＋Ｂの可食千粒体積×ｎ＋Ｃの可食千粒体積×ｎ

になります。
　この値が１リットルに満たない、例えば0.3リットルだった場合には、0.7リットル不足しますので、食べられる粒が最も少なかったロットを交換して、異なる割合で混ぜ合わせたミックスを作って、0.7リットルを満たします。
　この時、ロットを変更しない２種類のピーナッツに関しては、ロットの重量から、0.3リットル分のミックスを作る際に、消費したピーナッツの重量を差し引いた値を、残量とします。
　もし、ロットの残量が不足して、0.7リットルのミックスを作る事が出来ない場合には、先程の0.3リットルのミックスを作る場合と同様の手順で、食べられる粒が最も少ないロットに合わせて、ミックスを作る場合の体積を求めて、0.7リットルから差し引いて、不足する体積を求める計算を、総体積が１リットル以上になるまで繰り返します。
　そして最後に、ロットの残量が足りていて、総体積が１リットルを超える場合には、

必要な食べられる粒数＝不足する体積÷(Ａの可食千粒体積＋Ｂの可食千粒体積＋Ｃの可食千粒体積)

という数式で、必要な食べられる粒数を求めます。
　最後に、ロット毎に使用したピーナッツの重量を合計すると、混ぜるべきピーナッツの重量を求める事が出来ます。

　尚、考え方を記すだけで長くなってしまいましたので、（ルール違反になるかも知れませんが）エクセルの数式に関しては、次の回答で記す事に致します。

お礼

kagakusuki様

詳細にわたるご説明ありがとうございます。
明日からの急な出張の準備でいまどたばたしておりまして、
あとでゆっくり試してみたいと思います。
本当にありがとうございます。

回答No.2

kagakusuki

更に胃が痛くなる様な話で心苦しいのですが、気になる点がありますので、指摘させて頂きます。
　大粒のピーナッツ500mlと小粒のピーナッツ500mlを混ぜると、大粒のピーナッツの粒と粒の間の隙間に小粒のピーナッツが入り込んで隙間を埋めるため、混ぜた後の体積は１lよりも少なくなります。（隙間を埋め尽くした後に、余った小粒の体積が、大粒の体積に加わると考えると判り易いかと思います）
　例えば、大豆500mlに20mlのゴマを加えても、ゴマが大豆の隙間に入り込むため、体積は500mlのままになると思います。
　ピーナッツ以外でも、アルコール（分子が大きい）と水（分子が小さい）を混ぜた場合でも、体積は単純に合計した値よりも小さくなります。
　この事を計算に含めなくとも良いのでしょうか？

　それから、何故

0.0000003÷（0.0000003＋2種類目の値＋3種類目の値）＝0.38552

という数式になるのかが、良く判りませんでした。

>次に１をこの数字で割りました。
１÷333,333＝0.0000003

の部分の１が、1lの事だとしますと、0.0000003は「食べられるピーナッツ１個あたりの体積」となりますから、

1[l]÷（0.0000003＋2種類目の値＋3種類目の値）＝ミックス1l中に含まれる1種類あたりの食べられるピーナッツの個数

という数式に変更して、

必要なグラム数＝千粒重×ミックス1l中に含まれる1種類あたりの食べられるピーナッツの個数÷(1000×有効率)

だと思うのですが、如何でしょうか。

　尚、「～種類目のピーナッツ」という表現では長くなるため、ピーナッツの種類を仮にＡ、Ｂ、Ｃとして、上記の数式をまとめますと、

Ａの必要なグラム数＝Ａの千粒重/(((Ａの千粒重/(Ａのリットル重×Ａの有効率))＋(Ｂの千粒重/(Ｂのリットル重×Ｂの有効率))＋(Ｃの千粒重/(Ｃのリットル重×Ｃの有効率)))×Ａの有効率)

になると思います。

　後、ロットの変わり目を跨いでミックスを作る場合の計算式のアイデアを思い付き、現在のところエクセルで自動計算させためのセルと数式の組み合わせを試作中ですから、もう暫くお待ち下さい。

回答No.1

Cupper

んーと、Excelの質問ではなくて、計算方法の質問でしょうか？

質問文には書いていないけれど計算方法は決まっているのでしたら、それを書きましょう。
文章にすることで Excelなどの計算ソフトでの処理方法が明確になりますので
意外と文章にしている時点でやり方を自身で思いつくかもしれません。

（まあ、自分だったらゴールシーク機能を使ってみようと思いますけど…）

補足

ご回答ありがとうございます。
計算方法というのがこれでよいのか分かりませんが、
要は合計１リットルという量が決まっているときに、
この３つの種類のピーナッツをミックスしたときに、
３種類の食べられるピーナッツの量が均等になるように、
それぞれの種類の必要量を求めたいと言うことなのですが、
わかりますでしょうか？
千粒重、リットル重、有効率は必ず与えられます。