最適な組み合わせの自動計算

Question

お世話になります。
現在の業務で1回あたり20個の単位で作業を行っています。
しかし元の材料の数が毎回20個で割り切れる数量ではなく、その場合は16個又は15個の組み合わせを作って調整しています。
(16個or15個にしないと装置がエラーを起こして品質トラブルを起こすため)
調整の際に電卓を弾いて組み合わせを毎回作っているのですが、エクセルの関数又はマクロ機能を使って、例えば材料の数を入力すると20個、16個、15個のそれぞれの組み合わせ数を自動で計算する方法は可能でしょうか。
可能であれば、具体的な方法をご教授頂ければ幸いです。

Ogre7077 · Accepted Answer

マクロによる方法はすでに出ているようなので、数式による別案を。

-- A1 を左上に数表を記載

n ,20,16,15
00, 0 ← B2セル, 0, 0
01,-3, 1, 3
02,-3, 2, 2
03,-3, 3, 1
04,-3, 4, 0
05,-2, 0, 3
06,-2, 1, 2
07,-2, 2, 1
08,-2, 3, 0
09,-5, 4, 3
10,-1, 0, 2
11,-1, 1, 1
12,-1, 2, 0
13,-4, 3, 3
14,-4, 4, 2
15, 0, 0, 1
16, 0, 1, 0
17,-3, 2, 3
18,-3, 3, 2
19,-3, 4, 1 ← D21セル

-- G1 を左上に数式を記載

材料数, 421 ← H1セル
商, =FLOOR(H1/20,1) ← H2セル
余, =MOD(H1,20) ← H3セル
20枚組, =INDEX(B2:D21, H3+1, 1) + H2
16枚組, =INDEX(B2:D21, H3+1, 2)
15枚組, =INDEX(B2:D21, H3+1, 3)

-- No.5 の回答の補足について

> ①数表はどのように導けばいいのでしょうか。
努力と根性で手計算... だったのが大航海時代の数表です
https://ja.wikipedia.org/wiki/%E6%95%B0%E8%A1%A8
少数なら電卓で試行錯誤でもよいでしょうが、大量ならプログラムで算出... できるならそもそも数表はいりませんね

> ②FLOOR,MOD関数でそれぞれどの様な数値を導いているのでしょうか。
n = 20a+16b+15c とした場合、20枚組が基本なので先ずは a が最大になるように a = n/20 を小数点切り捨て(floor)で計算します。
a だけで解けなければ b,c で調整することになりますが、n/20 の余り(mod)である 0-19 の数だけを考慮すればよいので、予め算出した数表から 0-19 の数に対応する (a,b,c) を読み取り、先ほどの a を加算することで解が求まります。

> ③組み合わせ表=数表の認識で良いでしょうか。
そのとおりです。すみません書き間違えました。

-- 備考

数表を見ればわかるように、20枚の組がマイナスの場合は、材料数が少ないと解なしになります。最大で n=9 の場合の -5 なので、おおむね 20*5=100 枚以上の材料が常に必要となりますのでご注意ください。

tatsumaru77 · Answer

めぐみん_様
>No.13につきましては最初にロット数に対し２０枚で割り切れるか否かのIf判定をし商を２０枚の列に入れ、あとは無条件で１６枚と１５枚の列を０にされてはいかがでしょうか？

回答：ロット数＝４００１が与えられたとき、求める解は
20枚＝１９７　16枚＝１　15枚＝３　となります。
（２０＊１９７＋１６＊１＋１５＊３＝４００１）
その為、あなたが指摘される方法で最初に２０で割った商を20枚の列に入れると
20枚＝２００　となり　16枚＝０　15枚＝０となりますが、それでは
1枚余ってしまいます。（余ってはNGになります）
（２０＊２００＋１６＊０＋１５＊０≠４００１になってしまいます）
1枚も余らない組み合わせを求めて、かつ組み合わせが複数パターンあった場合は、２０枚の組みが1番多いものを採用するというのが、本件の条件になります。1枚も余らない組み合わせがない場合は、「組み合わせなし」が求める答えになります。例えば、ロット数＝２１の場合は、「組み合わせなし」になります。

＞またFor文の最初（a)のスタート値は int(ロット数/20）を超える事はないかと考えますがいかがでしょうか？

回答：ご指摘のようにa=200から開始しているので、4000が指定されても問題ありませんが、NO12の場合は、a=100から開始していました、この場合は、問題があるので、修正しました。（ロット数の上限＝４０００が前提です）

めぐみん_ · Answer

tatsumaru77 様

御助言ありがとうございました。

No.13につきましては最初にロット数に対し２０枚で割り切れるか否かのIf判定をし商を２０枚の列に入れ、あとは無条件で１６枚と１５枚の列を０にされてはいかがでしょうか？

またFor文の最初（a)のスタート値は int(ロット数/20）を超える事はないかと考えますがいかがでしょうか？
未検証ですので違っていたらすいません。

めぐみん_ · Answer

数学に疎いと言われてますが、『実作業の手順（電卓での計算）』で書かれては宜しかったのでは？
或いは例題数を増やすか。

どの道初級レベルなジジィには難題のようですね。

tatsumaru77 · Answer

たびたび、申し訳ありません。
No12でのマクロに誤りがありました。
ロット数＝４０００の時、
20枚＝２００、16枚＝０、１５枚＝０　が最適解ですが、
20枚＝１００、16枚＝９５、１５枚＝３２　となっていました。

20枚＝２００、16枚＝０、１５枚＝０　が表示されるように修正しました。
尚、ロット数が４０００以内であれば、最適解が表示されますが、
それより多い場合は、結果が表示されても最適解ではない可能性があります。従って、運用上の上限は４０００としてください。

-------------------------------------------------------
Option Explicit
Public Sub 組み合わせ数計算()
    Dim lot_count As Long
    Dim ws1 As Worksheet
    Dim wrow As Long
    Set ws1 = Worksheets("Sheet1")
    lot_count = ws1.Range("B2").Value
    If lot_count < 15 Then
        MsgBox ("ロット数が小さすぎます")
        Exit Sub
    End If
    Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim key As Long
    For a = 200 To 0 Step -1
        For b = 200 To 0 Step -1
            For c = 200 To 0 Step -1
                key = 20 * a + 16 * b + 15 * c
                If key = lot_count Then
                    ws1.Range("B5").Value = a
                    ws1.Range("C5").Value = b
                    ws1.Range("D5").Value = c
                    MsgBox ("設定完了")
                    Exit Sub
                End If
            Next
        Next
    Next
    MsgBox ("該当組み合わせなし")
End Sub

tatsumaru77 · Answer

No10です。
申し訳ありません。前回のは破棄してください。
余分な処理が入っていたので削除しました。
------------------------------------------
Option Explicit
Public Sub 組み合わせ数計算()
    Dim lot_count As Long
    Dim ws1 As Worksheet
    Dim wrow As Long
    Set ws1 = Worksheets("Sheet1")
    lot_count = ws1.Range("B2").Value
    If lot_count < 15 Then
        MsgBox ("ロット数が小さすぎます")
        Exit Sub
    End If
    Dim dicT As Object
    Set dicT = CreateObject("Scripting.Dictionary")
    Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim key As Long
    For a = 100 To 0 Step -1
        For b = 100 To 0 Step -1
            For c = 100 To 0 Step -1
                key = 20 * a + 16 * b + 15 * c
                If dicT.exists(key) = False Then
                    dicT(key) = Array(a, b, c)
                End If
            Next
        Next
    Next
    If dicT.exists(lot_count) = True Then
        ws1.Range("B5").Value = dicT(lot_count)(0)
        ws1.Range("C5").Value = dicT(lot_count)(1)
        ws1.Range("D5").Value = dicT(lot_count)(2)
        MsgBox ("設定完了")
    Else
        MsgBox ("該当組み合わせなし")
    End If
End Sub

tatsumaru77 · Answer

No10です。
追伸：ロット数＝４０００迄はまちがいなく処理できます。
（実際は５１００になります）

tatsumaru77 · Answer

以下のマクロを標準モジュールに登録してください。
使用上の中です。
シート名は"Sheet1"を使用します。
B2のセルにロット数を入力して、マクロを実行してください。（黄色のセル）
B5、C5、D5のセルに結果が表示されます。（青色のセル）
詳細は添付図を参照ください。不明点があれば補足してください。
--------------------------------------------------------
Option Explicit
Public Sub 組み合わせ数計算()
    Dim lot_count As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim maxrow As Long
    Dim wrow As Long
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    maxrow = ws2.Cells(Rows.count, 1).End(xlUp).Row    'A列 最大行取得
    lot_count = ws1.Range("B2").Value
    If lot_count < 15 Then
        MsgBox ("ロット数が小さすぎます")
        Exit Sub
    End If
    Dim dicT As Object
    Set dicT = CreateObject("Scripting.Dictionary")
    Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim key As Long
    For a = 100 To 0 Step -1
        For b = 100 To 0 Step -1
            For c = 100 To 0 Step -1
                key = 20 * a + 16 * b + 15 * c
                If dicT.exists(key) = False Then
                    dicT(key) = Array(a, b, c)
                End If
            Next
        Next
    Next
    If dicT.exists(lot_count) = True Then
        ws1.Range("B5").Value = dicT(lot_count)(0)
        ws1.Range("C5").Value = dicT(lot_count)(1)
        ws1.Range("D5").Value = dicT(lot_count)(2)
        MsgBox ("設定完了")
    Else
        MsgBox ("該当組み合わせなし")
    End If
End Sub

tatsumaru77 · Answer

No6です。補足ありがとうございました。
４０００以内であれば、ひと手間かかりそうですが、何とかなりそうです。
（但し、瞬時に結果がわかるようなものは無理かと思います）
明日までには回答できるかと思いますので、しばらくお待ちください。

tatsumaru77 · Answer

めぐみん_様
＞・２０枚毎纏めた数
＞・１６枚毎纏めた数
＞・１５枚毎纏めた数
＞と思っての金種表でしたけど、思い違いでしょうか？

横からすみません。

今回の案件は、数学的には、ロット数＝Nが与えられたとき、
20*x + 16*y + 15*z = N
が成立する、x,y,zの組み合わせを求めよ。
と理解しています。（もし、違っていたら、質問者の方ご指摘ください）
但し、
N:1以上の整数
x,y,z:0以上の整数とする。

最適な組み合わせの自動計算

マクロによる方法はすでに出ているようなので、数式による別案を。

めぐみん_様

tatsumaru77 様

数学に疎いと言われてますが、『実作業の手順（電卓での計算）』で書かれては宜しかったのでは？

たびたび、申し訳ありません。

No10です。

No10です。

以下のマクロを標準モジュールに登録してください。

No6です。

めぐみん_様

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング