VBA。壁の間隔Ｘミリの中に、5種類の異なる巾の板を敷き詰め、Ｘ以下でＸに近い板の組み合わせを算出

エクセルVBA。A列1行目に板の名称ａ、2行目にb、3行目にｃ、4行目にｄ、5行目にｅが記入されています。Ｂ列にそれぞれの板の巾が記入されています。（ミリ表示）壁の間隔Ｘミリに敷く板の組合わせでＸに一番近い板の組み合わせを求め、その時の板の合計巾を提示するマクロを教えて下さい。

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

• 皆さんコメント遅くなりまして申し訳ありませんでした。皆さんのアドバイスを元にマクロを考えていました。まず同じ板は複数使えるようにします。銀鱗さんのアドバイスを元に皆さんのご意見を加えてゆきました。結論から言いますとマクロは完成しました。板の種類を5種にするとコードが長くなったので3種に少なくしてコードを書きました。でも、3種のコードで全てが理解できたので、何種でも対応できます。画像を添付したのですが、小さくて申し訳ありません。For～Nextは3種の板なので3回ネストさせました。1回のループは壁間距離を一番狭い巾で割った数を限度にループさせれば十分と踏んだのでその回数まで動かす事にしました。それで、3種の板の全ての組み合わせを出し、それぞれの板巾の合計を出し、ｼｰﾄに書き出し、並び替え「Match」関数を使って、壁間距離以下で一番近い板巾の合計を算出しました。このパターンが答えです。

  
    補足日時：2023/04/27 22:11

• 以下は記述したマクロです。　
  Sub 壁間に板を割付る()
  Dim X As Long
  Dim Wa As Long, Wb As Long, Wc As Long
  Dim N As Long
  Dim i As Long, i2 As Long, i3 As Long
  Dim Lr As Long, Lr2 As Long, Lr3 As Long
  Dim ii As Long
  
  X = Range("B1").Value '壁間距離
  Wa = Range("B4").Value
  Wb = Range("B5").Value
  Wc = Range("B6").Value
  
  N = WorksheetFunction.RoundUp(X / Wa, 0)

    補足日時：2023/04/27 22:13

• 続きです。
  For i = 0 To N
  For i2 = 0 To N
  For i3 = 0 To N
  Lr3 = Cells(Rows.Count, 5).End(xlUp).Row + 1
  Cells(Lr3, 5).Value = Wc & "x" & i3
  Cells(Lr3, 6).Value = Wc * i3
  Cells(Lr3, 3).Value = Wb & "x" & i2
  Cells(Lr3, 4).Value = Wb * i2
  Cells(Lr3, 1).Value = Wa & "x" & i
  Cells(Lr3, 2).Value = Wa * i
  Next
  Next
  Next

    補足日時：2023/04/27 22:14

• 続きです。
  '合計巾を算出する
  For ii = 9 To Lr3
  Cells(ii, 7) = Cells(ii, 2) + Cells(ii, 4) + Cells(ii, 6)
  Next
  
  'G列（合計巾）を昇順に並べ替える
  Range("A9:G10000").Sort Key1:=Range("G9"), Order1:=xlAscending
  
  End Sub
  もう一つマクロがあります。

    補足日時：2023/04/27 22:16

• 続きです。
  Sub 壁間距離以下で一番近い板巾の合計を算出する()
  
  Dim NearRow As Long
  Dim NearNo As Long
  Dim iii As Long
  Dim r As Long
  
  '検索範囲から近似値以下で最大値を検索（行数を返す）
  NearRow = WorksheetFunction.Match(Range("B1").Value, Range("G9:G100000"), 1) + 8 '8はタイトルがある行数
  NearNo = Cells(NearRow, 7).Value
  MsgBox NearNo

    補足日時：2023/04/27 22:18

• 続きです。
  r = 9
  For iii = 9 To 10000
  If Cells(iii, 7).Value = NearNo Then
  Cells(r, 9).Value = Cells(iii, 7).Offset(0, -6)
  Cells(r, 10).Value = Cells(iii, 7).Offset(0, -5)
  Cells(r, 11).Value = Cells(iii, 7).Offset(0, -4)
  Cells(r, 12).Value = Cells(iii, 7).Offset(0, -3)
  Cells(r, 13).Value = Cells(iii, 7).Offset(0, -2)
  Cells(r, 14).Value = Cells(iii, 7).Offset(0, -1)

    補足日時：2023/04/27 22:20

• Cells(r, 15).Value = Cells(iii, 7).Value
  r = r + 1
  End If
  Next
  
  End Sub
  
  ★以上です。皆様貴重なお時間を頂き、アドバイスいただきました本当にありがとう御座いました。

    補足日時：2023/04/27 22:22

No.2 ベストアンサー 回答者： 銀鱗 回答日時： 2023/04/23 23:56

んーと、マクロを書く前に、その問題を解くための手順は理解していますか？

要はマクロ以前の話は大丈夫かって話。

5種類の中からの組み合わせを全部引っ張り出してそこから目的の値に近い物を順番に並べるというものになります。
(´・ω・`) 数学の基礎。中学の数学レベルの話だよ。

・・・
マクロじゃなくて、普通に表にしてみるといい。
横方向に5種類の名称。
その下に使うか使わないかの表示。
横に使った板の合計の厚さ。

　a　b　c　d　e　厚さ
　0　0　0　0　1　=(aの厚さ)×0 + …(bの厚さ)×0 + (eの厚さ)×1
　0　0　0　1　0　=(aの厚さ)×0 + …(bの厚さ)×1 + (eの厚さ)×0
　0　0　0　1　1　=(aの厚さ)×0 + …(bの厚さ)×1 + (eの厚さ)×1
　：
　1　1　1　0　1　=(aの厚さ)×1 + …(bの厚さ)×0 + (eの厚さ)×1
　1　1　1　1　0　=(aの厚さ)×1 + …(bの厚さ)×1 + (eの厚さ)×0
　1　1　1　1　1　=(aの厚さ)×1 + …(bの厚さ)×1 + (eの厚さ)×1

こんなのを作るだけだ。（前の回答者さんが言ってる5ビットの2進数）
あとは「厚さ」を昇順に並べてVLOOKUP関数などを使って一番近い値を探すだけ。

こういった考え方ができなきゃ、自分で問題を解決することはできませんよ。

なお、ここに書いたことは回答者に著作権が発生するので勝手にこの回答の一部を切り取って質問文に貼り付けて「このやり方のマクロを教えてください」というのはアウト。
訴えちゃうよ。マジで♪
（利用規約に投稿内容の著作に関する記述がありますので確認してください）

・・・
ということでマクロにしなくても解き方を理解していれば余裕で問題を解けるって話。
（エクセルを知らない人に使わせるとかそういった事情は考慮していない）

No.6 回答者： fujillin 回答日時： 2023/04/24 16:51

No5です。

連投失礼（直後に気が付きました）

＞D2セルに　= X(目的の寸法） - D1
は
　　= D1 - X(目的の寸法）
が、正しいですね。
訂正しておきます。
（どちらでもできますけれど、以降の説明と整合していなかった）

No.5 回答者： fujillin 回答日時： 2023/04/24 16:47

こんにちは

雰囲気からすると、それぞれの板は複数枚使用可（0枚を含む）で、「一番近い」とは言っても、合計がXをオーバーするようにして一番小さい組み合わせではないかと思うのだけれど・・
その辺のところがまったく記載されていないし、回答者がいろいろ書いていても質問者からの反応はないみたいだし、マクロを作成することが目的なのか、解を得ることが目的なのかもわからないですね。


◇ 解を得ることが目的の場合
マクロを使わなくてもエクセルの機能で解を得られます。
　C列に枚数（とりあえず1を入力）
　D1セルに=SUMPRODUCT(B1:B5,C1:C5)を入力（＝合計幅）
　D2セルに　= X(目的の寸法） - D1 を入力（合計値と目的値の差）
のようなものを作っておいて、

エクセルの「ソルバー」の機能で、
　・目標をD2セルが　正で最小値になるように設定
　・変数をC列として、値が正の整数（0を含む）で条件を設定
として、実行すれば解が求められます。
（解が複数存在する場合にどれが返されるかは初期値によります）
※ ソルバーがわからない場合は、検索すれば使い方の説明はたくさん見つかるでしょう。


◇ マクロを作るのが目的の場合
No2様がご指摘のように、解を得るための手順を考えましょう。
（他人に丸投げでは、「作る」ことにはなりません）

まず、1種類の材（幅wとする）でXの幅を埋めるには
　n = Roundup(X/ｗ,0) 枚の板が必要になります。
この時の余分な幅は
　X - n * w で求められます。
これを、これまでの最小値と比較して、小さかったらその枚数を記憶するようにしておきます。

この材料を0～n-1枚迄使って、残りの幅を次の違う材料で埋めたらどうなるかを順次計算してゆくことで、全体の最小値を求めることが可能になります。
つまり、上記のようなサブプロシージャを作成して、再帰的に呼び出せば計算できるってことです。

同じ値になる複数解が存在する場合に、全部を抜き出したいのなら、上記の記録する際に「同じ値の場合は追加で記録する」というようにしておけば可能です。


一方で、実務でこのようなものを求めるケースを考えてみると、単純に、最小値だけで決定してはいないのではないかという気がしますけれど・・
「できるだけ各種を同じ数に近くしたい」とか「できるだけ1,2種類で済ませてしまいたい」とか、他の思惑が合って、1ｍｍや2ｍｍ程度の差なら最小値にはこだわらないってことの方が多そうな気がしますけれど。

No.4 回答者： 初心者ですが何か。 回答日時： 2023/04/24 14:03

例題を挙げておいたら良かったのでは？

例えば
・a:２枚、c:１枚
・b:１枚、e:３枚
使うケースがあるみたいな？
よくあるナップザック問題なのでしょうけど、条件が不明確ですと厳しそう？

No.3 回答者： tatsumaru77 回答日時： 2023/04/24 08:53

同じ板を何回使用しても良いですか。

それとも使用できるのは1回限りですか。
例えば、aを５回使用するのは、良いのでしょうか。それともaは１回だけしか使用できないのでしょうか。

No.1 回答者： osamuy 回答日時： 2023/04/23 23:37

0から31までの数値を二進数で表すと下記のようになりますが、この5ビットのそれぞれが、5枚の板の組み合わせを表していると考えて、1となってる板の値の和を計算して、Xとの差が一番小さいのを選べば答えになります。

――という感じでマクロを組んでみては。

0=00000
1=00001
2=00010
3=00011
4=00100
5=00101
6=00110
7=00111
8=01000
9=01001
10=01010
11=01011
12=01100
13=01101
14=01110
15=01111
16=10000
17=10001
18=10010
19=10011
20=10100
21=10101
22=10110
23=10111
24=11000
25=11001
26=11010
27=11011
28=11100
29=11101
30=11110
31=11111