エクセル セルの組み合わせ

質問

エクセル表の中で目的の数値になるセルの組み合わせを探したいです
Ａ列　　Ｂ列に
a 　210
b 　80
c 　403
d 　196
e 　221
f 　307
g 　325
h 　233
i 　237
j 　307
k 　487
L 　267
のようにあります。
これを組み合わせて９８０に近い組み合わせをいくつかさがしたいのですが、
どうしたらできるでしょうか。重複はできないのです。
ソルバーで挑戦しようと思いましたが、使い方がわかりません。
だれか教えてください。おねがいします。

回答No.4

Wendy02 · ベストアンサー

こんにちは。

残るは、以下のようになりますね。
たぶん、そのままで、何もしなくてもよいとは思いますが、私は、以下のように、[並べ替え]てしまい、範囲を狭めました。 $C$1:$C8 で、それぞれを設定しなおしました。理由は、もう、ソルバーでは求められないからです。並べ替えは、意味があります。

80
221
233
237
307
325
403
487

こんどは、ソルバーでは、ぴったりがどうやらないようですから、今度はオプション側で、まず、[公差]は、10% にして、[精度] と[収束] を、共に、0.1 とし、[単位の自動設定]をチェック

そうすると、
80　　1
221　　0
233　　1
237　　0.909307783
307　　0
325　　0
403　　0
487　　1
-------------------
1015.505945

これでは用が足りませんので、273 ... 1 を入れ、487 ...0 をにして、　403　...1にして、

80　　1
221　　0
233　　1
237　　1
307　　0
325　　0
403　　1
487　　0
-------------------
953

こんなことをしていたら、面倒でしょうがないなって思いましたので、途中から、マクロに切り替えてしまいました。良かったら、最初から、お使いください。

以下を、[標準モジュール]に貼り付けてください。

TargetNum の部分に数字を入れてください。
後は、表の通りです。

Const TargetNum As Long = 980
Const DATARANGE As String = "B1:B8"　'数値の範囲
Const CONTROLRANGE As String = "C1:C8" '結果の範囲
Dim m As Long　　　　　'母数
Dim n As Long　　　　　'抽出数
Dim Num() As Long　　　 '抽出したｎ個の数
Dim NumSave() As Long
Dim NearNum As Long
Dim arItems As Variant

Sub yCombin()
　　Dim i As Long, s As String

　　arItems = Range(DATARANGE).Value
　　m = UBound(arItems) - LBound(arItems) + 1
　　For n = 1 To m
　　　　ReDim Num(1 To n)
　　　　SeaarchNos 1, 1, 0
　　　　Erase Num
　　Next
　　　 Range(CONTROLRANGE).ClearContents
　　For i = 1 To UBound(NumSave)
　　　　 Range(CONTROLRANGE).Cells(NumSave(i)).Value = 1
　　Next
　　MsgBox "Finish!"
End Sub

Function SeaarchNos(x As Long, c As Long, t As Long) As Long
　　Dim i As Long, j As Long, tt As Long
　　j = x: tt = t
　　For x = j To m
　　　　Num(c) = x
　　　　tt = t + arItems(x, 1)
　　　　If c = n Then
　　　　　　If Abs(TargetNum - NearNum) > Abs(TargetNum - tt) Then
　　　　　　　　NearNum = tt
　　　　　　　　ReDim NumSave(1 To n)
　　　　　　　　For i = 1 To n
　　　　　　　　　　NumSave(i) = Num(i)
　　　　　　　　Next
　　　　　　End If
　　　　Else
　　　　　　SeaarchNos x + 1, c + 1, tt
　　　　End If
　　Next
End Function

お礼

お礼が遅くなり申し訳ありません。
今日出社し、早速してみたいと思います。
大変詳しく教えていただき助かりました。
ありがとうございました。

回答No.3

Wendy02

こんにちは。

>入力してＯＫをクリックすると、エラーがでます。
そうなんです。私は、なれてしまっているから、もう追加を押して、キャンセルをするのですが、ここがヘンですね。もう限界なのかなって思ったりします。古いまんまで、MS側は、ぜんぜん新しく直していませんしね。外部で作ったアドインは、ほとんどそうなんですね。

>あと変化させるセルがマイナス表示になるので、困ってます。

$C$1:$C$12 >=0
これが働いていませんね。もう一度、条件をみてください。

制約条件
--------------------------
$C$1:$C$12 <=1
$C$1:$C$12=整数
$C$1:$C$12 >=0
--------------------------
となっているはずです。

210
0
0
196
0
0
0
0
0
307
0
267

と出てくるはずです。少し、時間は掛かりますが、1分以内だと思います。

お礼

今日はじめて会員登録し、質問をしました。
回答への補足へ入力するのではなく、お礼に入力しなければいけませんね。お礼ですら・・・間違ってすみませんでした。
そしてありがとうございました。
もし時間がありましたら、上記の補足に書いたものの方法も教えていただけたら幸いです。

補足

詳しい解説本当にありがとうございます。
制約条件の入力ですが、追加を押してキャンセルするとうまくいきました。
いま９８０になる組み合わせが見つかった行は削除して、さらに残る数値で組み合わせをかんがえようと思うのですが、ちょうど９８０になる数値はないようで、９８０に近い組み合わせを探したいのですが、どのように制約条件を入力すればよろしいでしょうか？
最高でも９９０ぐらいで、下はいくつでもいいのですが・・・
何回も質問ありまして本当にすみません

回答No.2

Wendy02

こんにちは。

これは、解が見つかりますね。

B列　　C列　　D列
210　　　　　 =B1*C1　↓コピー
80
403
196
221
307
325
233
237
307
487
267
---------------------------
　　　　　　　　=SUM(D1:D12)

ソルバー：パラメータ設定

目的セル(E)：$D$13
目標値：　値(V)：980

変化させるセル
$C$1:$C$12

制約条件：
$C$1:$C$12=整数　（真ん中は、「区間」）
$C$1:$C$12 <=1
$C$1:$C$12 >=0

なお、うまく行かない場合は、オプションの公差を上げるとよいです。
公差：5　というのは、±5 %　という許容範囲の意味です。

余談：
ソルバーは、作成されてから、15年近く経っていて、それなりに不具合が報告されています。新しいものがほしくても、なかなか、Excel単体よりも値段が高いし、マクロで作るには、面倒だしっていうところです。

補足

詳しい回答ありがとうございます。
一度挑戦してみたのですが、制約条件入力のところで、
入力してＯＫをクリックすると、エラーがでます。
これが不具合のことでしょうか・・・
キャンセルをおすと一応表示はされるのですが・・・
あと変化させるセルがマイナス表示になるので、困ってます。
一応組み合わせて９８０に近ければいいのですが、むずかしいですね。

回答No.1

n-jun

組み合わせとその和をシートに書き出して、
＞９８０に近い
と言う条件で抽出するとか？
（例：９７０～９９０までとか）