【iOS版アプリ】不具合のお知らせ

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

質問者からの補足コメント

  • 皆様、早速の回答ありがとうございます。
    質問内容に関して補足致します。
    元の材料数が仮に420枚とすれば、20枚の組み合わせが21組ですが、421枚となると…
    20枚:18組、16枚:1組、15枚:3組の組み合わせに振り分けて作業します。
    元の材料数は前工程の作業で確定され、その単位ごとに流さなければならないため繰り越し及び
    前工程でキリの良い数量に調整して貰うようなことはできません。

    「最適な組み合わせの自動計算」の補足画像1
      補足日時:2021/09/19 16:26
  • ありがとうございます。
    仰る通り、20個組が基本です。
    回答頂いた内容ですが、うまく理解できていません。
    初歩的なことで申し訳ありませんが質問です。
    ①数表はどのように導けばいいのでしょうか。
    ②FLOOR,MOD関数でそれぞれどの様な数値を導いているのでしょうか。
    ③組み合わせ表=数表の認識で良いでしょうか。

    No.5の回答に寄せられた補足コメントです。 補足日時:2021/09/20 06:59
  • 回答ありがとうございます。
    以下回答致します。
    A1.最大でも4,000個以内です。
    A2.①(20枚の組が一番多い組み合わせが理想)
    A3.問題ありません。

    1,000個を超えると厳しいということなので、難しそうですね...

    No.6の回答に寄せられた補足コメントです。 補足日時:2021/09/20 10:15
  • お恥ずかしながら数学には疎いのですが、記載の数式で間違いないと思います。

    No.8の回答に寄せられた補足コメントです。 補足日時:2021/09/20 10:19
gooドクター

A 回答 (17件中1~10件)

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



-- 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 枚以上の材料が常に必要となりますのでご注意ください。
    • good
    • 0
この回答へのお礼

回答頂いた内容で最適な組み合わせ数が自動で算出できるようになりました!
ありがとうございました。

お礼日時:2021/09/20 15:47

めぐみん_様


>No.13につきましては最初にロット数に対し20枚で割り切れるか否かのIf判定をし商を20枚の列に入れ、あとは無条件で16枚と15枚の列を0にされてはいかがでしょうか?

回答:ロット数=4001が与えられたとき、求める解は
20枚=197 16枚=1 15枚=3 となります。
(20*197+16*1+15*3=4001)
その為、あなたが指摘される方法で最初に20で割った商を20枚の列に入れると
20枚=200 となり 16枚=0 15枚=0となりますが、それでは
1枚余ってしまいます。(余ってはNGになります)
(20*200+16*0+15*0≠4001になってしまいます)
1枚も余らない組み合わせを求めて、かつ組み合わせが複数パターンあった場合は、20枚の組みが1番多いものを採用するというのが、本件の条件になります。1枚も余らない組み合わせがない場合は、「組み合わせなし」が求める答えになります。例えば、ロット数=21の場合は、「組み合わせなし」になります。

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

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

tatsumaru77 様



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

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

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

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


或いは例題数を増やすか。

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

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


No12でのマクロに誤りがありました。
ロット数=4000の時、
20枚=200、16枚=0、15枚=0 が最適解ですが、
20枚=100、16枚=95、15枚=32 となっていました。

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

-------------------------------------------------------
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
「最適な組み合わせの自動計算」の回答画像13
    • good
    • 0
この回答へのお礼

回答頂いた内容で最適な組み合わせ数が自動で算出できるようになりました!
4,000個以上のロットは、おそらくこの先も無いと思います。
ありがとうございました。

お礼日時:2021/09/20 15:50

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
    • good
    • 0

No10です。


追伸:ロット数=4000迄はまちがいなく処理できます。
(実際は5100になります)
    • good
    • 0

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


使用上の中です。
シート名は"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
「最適な組み合わせの自動計算」の回答画像10
    • good
    • 0

No6です。

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

めぐみん_様


>・20枚毎纏めた数
>・16枚毎纏めた数
>・15枚毎纏めた数
>と思っての金種表でしたけど、思い違いでしょうか?


横からすみません。

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

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング