excelでの鶴亀算？

質問

工場で製品を作るときに使用する原料の計算をexcelを使って行いたい思っています。

原料13kg入り、原料16kg入り、原料17kg入りの3種類の袋があります。
これらの材料を使ってなるべく無駄がないように製品を作りたいと思っています。
たとえば製品を160kg作るときには、原料13kg入り x 6、原料16kg入り x 2、原料17kg入り x 2 を使用するとピッタリ160kgの製品を作ることができます。
製品は注文にあわせて作るので毎日作る量は違います。そのため毎日この何kg入りを何個必要とするのかを計算しなければなりません。
しかしこの計算を人間がやると時間がかかり間違えも起こるので、excel2002で行いたいと思ってます。
作る製品の量を入力すると各袋の数量が分かるようにするにはどのように行ったらいいのでしょうか。
どなたか教えてください。
よろしくお願いします。

回答No.6

kagakusuki

回答番号:ANo.2です。
　申し訳御座いません、せっかく補足して頂いたにも関わらず、関数の検討をしてみた処、結局、17kg入りが最優先で、16kg入りが最も後回しとなる数式となってしまいました。

　まず、余りが出る場合に対処するため、適当な列（ここでは仮にSheet2のA列を使用します）に、以下の様なリストを作成しておきます。

Sheet2のA1セルに　　余りが最小となる量 [kg]
Sheet2のA2セルに　　71
Sheet2のA3セルに　　58
Sheet2のA4セルに　　55
Sheet2のA5セルに　　45
Sheet2のA6セルに　　42
Sheet2のA7セルに　　39
Sheet2のA8セルに　　32
Sheet2のA9セルに　　29
Sheet2のA10セルに　　26
Sheet2のA11セルに　　16
Sheet2のA12セルに　　13

　次に、

Sheet1のA1セルに　　必要な製品の量 [kg]
Sheet1のB1セルに　　余りが最小となる原料の量 [kg]
Sheet1のC1セルに　　余る原料の量 [kg]
Sheet1のD1セルに　　13kg入りの個数
Sheet1のE1セルに　　16kg入りの個数
Sheet1のF1セルに　　17kg入りの個数
Sheet1のG1セルに　　合計 [kg]

と入力して下さい。
　次に、Sheet1のB2セルに次の数式を入力して下さい。

=IF(ISNUMBER($A2),IF(CEILING(CEILING($A2,1),17)<MOD(CEILING(CEILING($A2,1),17)-CEILING($A2,1),4)+FLOOR(CEILING(CEILING($A2,1),17)-CEILING($A2,1),4)/4,INDEX(Sheet2!$A:$A,MATCH($A2,Sheet2!$A$2:$A$12,-1)),CEILING($A2,1)),"")

　次に、Sheet1のC2セルに次の数式を入力して下さい。

=IF($B2="","",$B2-$A1)

　次に、Sheet1のD2セルに次の数式を入力して下さい。

=IF($B2="","",FLOOR(CEILING($B2,17)-$B2,4)/4)

　次に、Sheet1のE2セルに次の数式を入力して下さい。

=IF($B2="","",MOD(CEILING($B2,17)-$B2,4))

　次に、Sheet1のF2セルに次の数式を入力して下さい。

=IF($B2="","",CEILING($B2,17)/17-$E2-$F2)

　次に、Sheet1のG2セルに次の数式を入力して下さい。

=IF($B2="","",$D2*13+$E2*16+$F2*17)

　後は、Sheet1のA2セルに作る製品の量を入力するだけで、それに合わせた、各原料の袋ごとの必要数を求める事が出来ると思います。（C1とG1の数式は必ずしも必要という訳では御座いません）

回答No.5

hunt9999

すいません。No3の回答ですが、間違えがありました。
以下No4の回答と結合してモジュールに貼り付けてください。
相変わらず非効率で見た目も悪いですが、合計金額、それぞれの重量など機能が追加されてます。

'★結合箇所★結合箇所★結合箇所★結合箇所★結合箇所★

If Cells(1, 1) = Numbers Then

Cells(m, 2) = i
Cells(m, 3) = k
Cells(m, 4) = j
Cells(m, 5) = A価格 * i + B価格 * k + C価格 * j

m = m + 1
Indicater = True
End If

Next j
Next k
Next i

Cells(m, 5) = WorksheetFunction.Min(Range(Cells(11 + 5), Cells(m - 1, 5)))
Cells(m, 6) = "←最少価格"

'組み合わせなかった場合
If Not Indicater Then

For i = 0 To 100
For k = 0 To 100
For j = 0 To 100
Numbers = 原料A * i + 原料B * k + 原料C * j

If Not indicater2 Then
If Numbers > Cells(1, 1).Value Then
TempValue = (Numbers - Cells(1, 1).Value)

Cells(2, 8) = "一致する値がなかったので近い値"

Cells(3, 8) = "k"
Cells(3, 9) = "j"
Cells(3, 10) = "i"

Cells(4, 8) = i
Cells(4, 9) = k
Cells(4, 10) = j
Cells(4, 11) = A価格 * i + B価格 * k + C価格 * j
Cells(4, 12) = "←最少価格"

indicater2 = True
End If
End If

If indicater2 Then

If Numbers > Cells(1, 1).Value And TempValue > (Numbers - Cells(1, 1).Value) Then

TempValue = (Numbers - Cells(1, 1).Value)

Cells(4, 8) = i
Cells(4, 9) = k
Cells(4, 10) = j
Cells(4, 11) = A価格 * i + B価格 * k + C価格 * j

End If

End If

Next j
Next k
Next i

End If

Exit Sub

err1: MsgBox "数値が入力されてない箇所があります。" & Chr(13) & _
"数値を入力してください"

End Sub

お礼

できました。
ありがとうございました。
しかしこんなに難しいことだったのですね。『組み合わせなかった場合』は正直理解できていません。
道理で考えても分からないわけです。

本当にありがとうございました。

回答No.4

hunt9999

Sub RevisedTuruKame()

'製品の単位重さ
Dim 原料A As Long
Dim 原料B As Long
Dim 原料C As Long

'１袋あたりの価格
Dim A価格 As Long
Dim B価格 As Long
Dim C価格 As Long

'変数　答え一致した場合
Dim i As Long
Dim k As Long
Dim j As Long
Dim Numbers As Long
Dim Indicater As Boolean
Dim m As Long
m = 11 '組み合わせの表示を何行から始めるか

'変数　答え一致しない場合
Dim TempValue As Long
Dim indicater2 As Boolean

'入力箇所
Dim EnteredRng As Range
Dim ARng As Range
Set EnteredRng = Range("A1,B3: D3, B7:D7")

Cells(2, 1) = "↑合計重量(kg)"

Cells(2, 2) = "原料A(kg)"
Cells(2, 3) = "原料B(kg)"
Cells(2, 4) = "原料C(kg)"

Cells(6, 2) = "A価格(円/袋)"
Cells(6, 3) = "B価格(円/袋)"
Cells(6, 4) = "C価格(円/袋)"

Cells(m - 1, 2) = "A袋の数(袋)"
Cells(m - 1, 3) = "B袋の数(袋)"
Cells(m - 1, 4) = "C袋の数(袋)"
Cells(m - 1, 5) = "合計金額(円)"

With Range("A1,B2: D3, B6:D7,B10:E10")

.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous

.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter

With .Font
.Name = "ＭＳ Ｐゴシック"
.Size = 12
End With

End With

Columns(1).AutoFit
Columns(2).AutoFit
Columns(3).AutoFit
Columns(4).AutoFit
Columns(5).AutoFit

'必要な値が入力されているか-------------------
For Each ARng In EnteredRng
If ARng = "" Then

GoTo err1

End If
Next ARng
'------------------------------------------

原料A = Cells(3, 2)
原料B = Cells(3, 3)
原料C = Cells(3, 4)

A価格 = Cells(7, 2)
B価格 = Cells(7, 3)
C価格 = Cells(7, 4)

For i = 0 To 100
For k = 0 To 100
For j = 0 To 100
Numbers = 原料A * i + 原料B * k + 原料C * j

'★結合箇所★結合箇所★結合箇所★結合箇所★結合箇所★

回答No.3

hunt9999

VBAで出来ると思います。
即興で作成したのでかなり非効率です。
これをモジュールに貼り付けてから。
作成したい量を、A1に入力してください。
１行目の２列目から４列目にＡ1を満たす組み合わせ結果がでます。
組み合わせがなかった場合は５列目から７列目にその値に一番近い組み合わせが表示されます。
このマクロは13kg、16kg、17kgしか対応してません。
対応させたければ、ただ、変数を３つ設ければいいだけです。
単価が違う場合は得られた組み合わせの結果からエクセル関数で計算するか、変数をまた１つさらに設けてそれらを比較して一番値の小さい（価格が安い）のを結果として表示させればよいです。

Sub TuruKame()
'変数　答え一致した場合
Dim i As Long
Dim k As Long
Dim j As Long
Dim Numbers As Long
Dim Indicater As Long
Dim m As Long
m = 1 '組み合わせの表示を何行から始めるか

'変数　答え一致しない場合
Dim TempValue As Long
Dim indicater2 As Long

For i = 0 To 100
For k = 0 To 100
For j = 0 To 100
Numbers = 13 * i + 16 * k + 17 * j

If Cells(1, 1) = Numbers Then

Cells(m, 2) = i
Cells(m, 3) = k
Cells(m, 4) = j
m = m + 1
Indicater = True
End If

Next j
Next k
Next i

If Not Indicater Then

For i = 0 To 100
For k = 0 To 100
For j = 0 To 100
Numbers = 13 * i + 16 * k + 17 * j

If Not Indicater Then
If (Numbers - Cells(1, 1).Value) > 0 Then
TempValue = (Numbers - Cells(1, 1).Value)
indicater2 = True
End If
If TempValue > (Numbers - Cells(1, 1).Value) Then

TempValue = (Numbers - Cells(1, 1).Value)

Cells(1, 5) = "一致する値がなかったので近い値"
Cells(2, 5) = i
Cells(2, 6) = k
Cells(2, 7) = j

End If

End If

Next j
Next k
Next i

End If

End Sub

回答No.2

kagakusuki

例えば製品を160kg作るときには、原料16kg入り×10でも良い事から判る様に、その計算は数学では一律に求める事は出来ません。
　そのため、それだけの情報ではExcelで計算する事が出来ません。
　ですから、各袋の種類に対して、使用する際の優先順位を決めて頂けないでしょうか。
　つまり、例えば「原料1kg当たりのコストが、13kg入りが最も高くつき、17kg入りが最も安く済むため、17kg入りをなるべく多くして、13kg入りはなるべく少なくしたい。」といった優先順位を示して頂けないでしょうか。
　無論、原料の優先順位はコスト以外にも、安全性、供給の安定性、環境に対する配慮、等、色々な要因に影響されると思いますが、何故その様な優先順位になっているのかという理由を説明して頂く必要は御座いませんから、全ての要因を考慮した結果としての、優先順位だけを教えて頂きたいのです。
　尚、追加情報を投稿される際には、ログインを済まされてから、この回答が掲載されているページを開き、この回答が掲載されている箇所の近くにある「補足する」と記されている部分をクリックして下さい。（補足の入力欄が現れます）

補足

回答ありがとうございます。
原料に優先順位をつけるとしたら、最優先が17kg入り、次が16kg入り、最後が13kg入りになると思います。
よろしくお願いします。

回答No.1

keithin

とりあえずシチメンドクサイ算数の式を考案しなくても手っ取り早くエクセルが答えを教えてくれる機能として，ツールメニューの「ソルバー」というのを使うことができます。

添付図：
A1:C1に13,16,17を記入
A2:C2はそれぞれの数量を入れる欄として，今は空欄にしておく

D2に
=SUMPRODUCT(A1:C1,A2:C2)
と記入
D3に目的値の
160
を記入
D4に
=D3-D4
を記入

D4セルを選んでソルバーを開始，
　目標値は　最小値　にマーク
　変化させるセルは　A2:C2　を設定
　制約条件の追加をクリック，
　　A2:C2を選んで　「区間」と見えているのを選択して追加
　　A2:C2を選んで　「>=」「０」　として追加
　　D4を選んで　「>=」「０」として追加
　　キャンセルでダイアログを閉じて
あとは実行します。

D3で目的の数量を160キロからたとえば170キロに変更したら，そのまままたソルバーを立ち上げてただ実行すれば改めて答えを出してくれます。
「ツールメニューからソルバーを選んで実行する」だけだと，どこかに手順をメモ書きで書いておけば誰にでも使えます。

＃同じ160kg集めるにも，たとえば2,2,6袋でもできますが，0,10,0袋でも出来ます。
　ソルバーは１つしか答えをだしてくれませんので，たとえばそこに更に「袋ごとの単価が少しずつ違う」みたいな条件を加えて計算式と制約条件を継ぎ足していけば，より合理的な結果が得られます。
そういった試行錯誤まで行いたい時は，少しソルバーの操作に慣れた人が操作をする必要も出てくるかもしれません。