やり方を教えて下さい。

Question

2400、2160、3528、3864、2800、1500、2016、6000、2688、3120　を
1万円ずつ３つに割り振りたいのですが、エクセルでできるかと思うのですが、
教えて頂けますか。
1万円を超えたとしても最小にしたいのです。
お願いします。

kagakusuki · Accepted Answer

>エクセルでできるかと思うのですが、教えて頂けますか。

　総当たりで行う方法です。
　まず、B1～K1のセル範囲にかけて、2400、2160、3528、3864、2800、1500、2016、6000、2688、3120の各金額を表す数値を入力して下さい。
　次に、3つに分けるのですから、L1～N1の横一列に並んだ3個のセルに、「Aさん」、「Bさん」、「Cさん」という具合に、割り振られる人（或いは団体等）の名前を、仮の名称でも構わないので、入力して下さい。
　次に、O1セルに「絶対偏差」、P1セルに「標準偏差」、Q1セルに「最高額-最低額」と入力して下さい。
　次に、A3セルに次の数式を入力して下さい。

=IF(ISNUMBER($A$2),IF(AND(ABS($A$2)=INT($A$2),$A$2+ROW()-ROW($A$2)<COLUMNS($L:$N)^COLUMNS($B:$K)),$A$2+ROW()-ROW($A$2),""),"")

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

=IF(ISNUMBER($A2),INDEX($L$1:$N$1,MOD(INT($A2/COLUMNS($L$1:$N$1)^(COLUMNS($B:B)-1)),COLUMNS($L$1:$N$1))+1),"")

　次に、B2セルをコピーして、C2～K2の範囲に貼り付けて下さい。
　次に、L2セルに次の数式を入力して下さい。

=IF(ISNUMBER($A2),SUMIF($B2:$K2,L$1,$B$1:$K$1),"")

　次に、L2セルをコピーして、M2～N2の範囲に貼り付けて下さい。
　次に、O2セルに次の数式を入力して下さい。

=IF(COUNTIF($L2:$N2,">0")<3,"",AVEDEV($L2:$N2))

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

=IF(COUNTIF($L2:$N2,">0")<3,"",STDEVA($L2:$N2))

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

=IF(COUNTIF($L2:$N2,">0")<3,"",MAX($L2:$N2)-MIN($L2:$N2))

　次に、B2～Q2の範囲をコピーして、B3～Q3の範囲に貼り付けて下さい。
　次に、A3～Q3の範囲をコピーして、A4～Q59050の範囲に貼り付けて下さい。
　次に、T1セルに「値」、S2セルに「絶対偏差の最小値」、S3セルに「標準偏差最小値」、S4セルに「『最高額－最低額』の最小値」と入力して下さい。
　次に、T2セルに次の数式を入力して下さい。

=IF(COUNT(INDEX($O$1:$Q$1,ROWS($S$2:$S2)):INDEX($O:$Q,ROWS($A:$A),ROWS($S$2:$S2))),MIN(INDEX($O$1:$Q$1,ROWS($S$2:$S2)):INDEX($O:$Q,ROWS($A:$A),ROWS($S$2:$S2))),"")

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

=IF(INDEX($B$1:$K$1,COLUMNS($U:U))="","",INDEX($B$1:$K$1,COLUMNS($U:U)))

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

=IF(ISNUMBER($T2),INDEX($B:$N,MATCH($T2,INDEX($O$1:$Q$1,ROWS($S$2:$S2)):INDEX($O:$Q,ROWS($A:$A),ROWS($S$2:$S2)),0)+ROW($O$1)-1,COLUMNS($U:U)),"")

　次に、T2～U2の範囲をコピーして、T3～U4の範囲に貼り付けて下さい。
　次に、U1～U4の範囲をコピーして、V1～AD4の範囲に貼り付けて下さい。
　次に、S6セルに次の数式を入力して下さい。

=INDEX($L$1:$N$1,ROWS($6:6))&""

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

=IF(COLUMNS($U:U)>COUNTIF($U$4:$AD$4,$S6),"",INDEX($U$1:$AD$1,SUMPRODUCT(ISNUMBER(COLUMN($U$1:$AD$1))*(COUNTIF(OFFSET($U$4,,,1,COLUMN($U$1:$AD$1)-COLUMN($U$1)+1),$S6)<COLUMNS($U:U)))+1))

　次に、U6セルをコピーして、T6～AD6の範囲に貼り付けて下さい。
　次に、S6～AD6の範囲をコピーして、S7～AD8の範囲に貼り付けて下さい。

　これで、T6～AD8の範囲に割り振りの組み合わせが自動的に表示されます。
　尚、金額が変わる際には、B1～K1に入力されている金額を変更して下さい。

CoalTar · Answer

No1,2です。金額の高いほうから足していき、10,000円超えたときの冊数-1はすべての人の最低購入数(2冊)ということはわかるのですが、計算に反映できなかった。。。しかし、もうちょっと改良してみました少なくとも誰かが何らかの本を1冊は購入するのでC2セルは1(の人)で固定で良いことになるよって、人数の(冊数-1)乗の試行となる(今回の場合19,683) 1. ツール - オプション |計算方法| [レ]反復計算 15000 2. A1セル 0 (引き金) 3. C1:L1セルに対象の金額 4. C4セル =COUNT(C1:L1) 冊数 5. E4セル =A4^(C4-1) 試行回数 6. G4セル無料分の金額 7. I4セル =IF(SUM(C1:L1)<=G4*A4,"計算不要","要計算") 　計算の要/不要 8. B5:F5セル連番(各人の番号) 9. A2セル =IF(A1=0,0,IF(A2=E4,E4,A2+1)) 　試行回数に達したら止める 10. D2:L2セルまで 1,2,・・・(人数) を書き換えていきます　=IF($B2="",MOD(INT($A2/$A$4^(COLUMN(A2)-1)),$A$4)+1, 　 MOD(INT($B2/$A$4^(COLUMN(A2)-1)),$A$4)+1) 　右へオートフィル 11. A6セル各人の番号が一つ以上入っているかの条件判断　=AND(COUNTIF(D2:L2,C5:INDEX(B5:F5,A4))) [Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる 12. B6セル G4セルに入力した金額より多くかかった分の金額　=IF($A6>0,MAX(0,SUMIF($C2:$L2,B5,$C$1:$L$1)-$G$4),99999) 　右へオートフィル 13. G6セル最大5人分の差額の合計　=IF(A1<>0,SUM(B6:F6),99999) 14. H6セル今までの最小値の比較　=IF(A1=0,99999,MIN(G6,H6)) 15. I6セル最小値を出したときの番号　=IF(A1=0,0,IF(H6

MarcoRossiItaly · Answer

No.7 です。続報です。

最適解 176 円となる組み合わせは、やはり No.7 で言ったとおり、次の 1 通りしか存在しませんね。

「6000、2400、1500」、「3864、3528、2688」、「3120、2800、2160、2016」

No.7 のマクロでは 3 個と 3 個を列挙し、その結果、 176 円となる順列の数は COUNTIF 関数により 72 通りと分かりましたが、

(3・2・1) × (3・2・1) × 2 = 72

※「× 2」となるのは D ～ F の 3 列と G ～ I の 3 列を丸ごと入れ替えた順列が列挙されているため

というふうに計算で求めることができ、 COUNTIF での結果と一致します。つまり組み合わせとしては、上の 1 通りしかないと結論できます。

MarcoRossiItaly · Answer

力ずくで解いたら、「 176 円」が最適解（ 1 万円を超えた部分の金額の 3 人分の合計が最小となる解）となりました。

易しくない数学です。したがって当然、これをパッと解ける Excel の機能というのは、用意されていません。 Excel はただの計算機です。何らかの関係式がある場合は、偏微分やらソルバーで最適解を求められるかもしれませんが、整数問題でもあり、簡単ではありません。

とりあえず分かることは、小さいほうから 5 つ合計すると次のとおりとなるので、No.3・4さんの計算結果と比較して分かるとおり、最適解とはなりません。

1,500 + 2,016 + 2,160 + 2,400 + 2,688 = 10,764
10,764 - 10,000 = 764

ですから最適となるのは、 1 人当たりの購入数が 4 つ以下である場合であるはずです。つまり 3 人の購入数の組み合わせとして考えられるのは、「 4 つ、 4 つ、 2 つ」または「 4 つ、 3 つ、 3 つ」の 2 パターンしかあり得ないこととなります。前者は 10C4 × 6C4 × 2C2 = 3,150 通り、後者は 10C4 × 6C3 × 3C3 = 4,200 通りの組み合わせがあると分かります。

個数の組み合わせはこの 2 パターンしかないので、結局は 10 個の金額から合わせて 6 個を選び取るときの順列の数だけ計算すればよいこととなります。残りの 4 つは、自動的に決まってしまいますね。

本当は組み合わせを求められたらいいんですが、私は大して VBA が得意ではないので、順列にします。組み合わせを求めたかったら、詳しい方に別途、お尋ねください。順列でも一応、 10P6 = 151,200 行だけで済みます。ワークシートの行数はExcel2003 では 65,536 ですが、 2007 では 1,048,576 あるので、 2007 で順列を書き出してみてください。

下のほうに貼った何の工夫もないコードを使えば、私のパソコンだと 1.5 分ほどの時間がかかりましたが、一応できました。内容は、 B1:B10 の範囲に記入してある 10 個の金額を写し取って D1:I151200 に記入していくというものです。

マクロによる処理の後で、次式を入力しました。

B11　=SUM(B1:B10)　　…… 10 個の合計額
B14　=MIN(P:Q)　　……最適解である金額（ 176 円）
B15　=MATCH(B$14,P:P,)　　…… P 列で「 176 円」が最初に現れる行番号
B16　=MATCH(B$14,Q:Q,)　　…… Q 列で「 176 円」が最初に現れる行番号
B17　=COUNTIF(Q:Q,B14)　　…… P 列に存在する「 176 円」の行数
K1　=MAX(,SUM(D1:G1)-10^4)　　…… D ～ G の 4 列の合計から 10,000 を差し引いた額（ 10,000 以下の場合は 0 ）
L1　=MAX(,SUM(H1:I1)-10^4)　　…… H ～ I の 2 列の合計から 10,000 を差し引いた額（ 10,000 以下の場合は 0 ）
M1　=MAX(,SUM(D1:F1)-10^4)　　…… D ～ F の 3 列の合計から 10,000 を差し引いた額（ 10,000 以下の場合は 0 ）
N1　=MAX(,SUM(G1:I1)-10^4)　　…… G ～ I の 3 列の合計から 10,000 を差し引いた額（ 10,000 以下の場合は 0 ）
O1　=MAX(,B$11-SUM(D1:I1)-10^4)　　……残り一人の金額から 10,000 を差し引いた額
P1　=SUM(K1:L1,O1)　　……三人の 10,000 を上回った額の合計
Q1　=SUM(M1:N1,O1)　　……三人の 10,000 を上回った額の合計

添付図のとおり、「 176 円」が現れるのは順列の数で 72 通りです。ただ組み合わせとしては、何通りがこれに含まれているのか、確認していません。その気になれば簡単に該当の行のみ抜き出せますが。

「6000、2400、1500」、「3864、3528、2688」、「3120、2800、2160、2016」などが含まれるようです。これは、No.2さんやNo.4さんが示されているのと同じ組み合わせですね。きっと、これのみになるのかな？とにかく合計は、「 176 円」でオッケーです。

ご質問の金額の個数はたまたま、今回の方法でできる限界に近いでした。もうちょっと数が増えると、お手上げという感じです。

Sub write_down_permutations()

Dim price As Variant
price = Range("b1:b10")

Dim i1, i2, i3, i4, i5, i6 As Integer
Dim j As Long

For i1 = 1 To 10
For i2 = 1 To 10
If i2 <> i1 Then
For i3 = 1 To 10
If i3 <> i1 And i3 <> i2 Then
For i4 = 1 To 10
If i4 <> i1 And i4 <> i2 And i4 <> i3 Then
For i5 = 1 To 10
If i5 <> i1 And i5 <> i2 And i5 <> i3 And i5 <> i4 Then
For i6 = 1 To 10
If i6 <> i1 And i6 <> i2 And i6 <> i3 And i6 <> i4 And i6 <> i5 Then
j = j + 1
Cells(j, 4) = price(i1, 1)
Cells(j, 5) = price(i2, 1)
Cells(j, 6) = price(i3, 1)
Cells(j, 7) = price(i4, 1)
Cells(j, 8) = price(i5, 1)
Cells(j, 9) = price(i6, 1)
End If
Next i6
End If
Next i5
End If
Next i4
End If
Next i3
End If
Next i2
Next i1

End Sub

kagakusuki · Answer

回答:No.5です。
　書き忘れておりましたが゛、A2セルには数値の0を入力して下さい。

okormazd · Answer

#3です。
探したら、別にありました。

3120+2800+2160+2016=10096
3864+3528+2688=10080
6000+2400+1500=9900

ですかね。

okormazd · Answer

ソルパーを繰り返し使えばそれらしいことはできますが、VBAになります。
10000円を超えるのは最小なら何件超えてもいいのですかね。
それなら、

6000+2160+2016=10176
3864+3120+2800=9784
3528+2688+2400+1500=10116

というのがありますが。

CoalTar · Answer

こういう問題は総当たりしかないと思います。 10冊すべて購入すること各人10,000以上の金額のみ合計する。 3人の場合で各人1冊以上購入する。として、計算は3＾10-1=59048回行うとします (計算回数は何も購入しない人も計算対象です) で、VBAなのかもしれませんが、得意ではないので反復計算で行ってみます 1. ツール - オプション |計算方法| [レ]反復計算 10000 2. A1セル 0 (引き金に使います) 3. C1:L1セルに対象の金額 4. A2セル =IF(A1<>0,A2+1,0) 5. C2セル:L2セルまで 1,2,3を書き換えていきます　=IF($B2="",MOD(INT($A2/3^(COLUMN(A2)-1)),3)+1,MOD(INT($B2/3^(COLUMN(A2)-1)),3)+1) 　右へオートフィル 6. A6セル 1,2,3が一つ以上入っているかの条件判断　=PRODUCT(COUNTIF(C2:L2,{1,2,3})) 7. B6セル 10000円以上かかった分の金額　=IF($A6>0,MAX(0,SUMIF($C2:$L2,B5,$C$1:$L$1)-10000),99999) 　右へオートフィル 8. E6セル 3人の合計　=IF(A1<>0,SUM(B6:D6),99999) 9. F6セル今までの最小値の比較　=IF(A1=0,99999,MIN(E6,F6)) 10. G6セル最小値を出したときの番号　=IF(A1=0,0,IF(F6

CoalTar · Answer

＞2400、2160、3528、3864、2800、1500、2016、6000、2688、3120　を
何かの番号ですか？

＞1万円ずつ３つに割り振りたいのですが
その番号を持つ人に ダブらないように3人に各1万円を与える？

＞1万円を超えたとしても最小にしたいのです。
3万円なので超えているし・・・？
最初のナンバーは金額で、小さいほうから足していけば1万超えるか超えないかが分かる。

規則を詳しく説明し、サンプルを数個あげて説明してください。

やり方を教えて下さい。

>エクセルでできるかと思うのですが、教えて頂けますか。

No1,2です。

No.7 です。

力ずくで解いたら、「 176 円」が最適解（ 1 万円を超えた部分の金額の 3 人分の合計が最小となる解）となりました。

回答:No.5です。

#3です。

ソルパーを繰り返し使えばそれらしいことはできますが、VBAになります。

こういう問題は総当たりしかないと思います。

＞2400、2160、3528、3864、2800、1500、2016、6000、2688、3120 を

この回答への補足

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

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

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

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

　回答:No.5です。

＞2400、2160、3528、3864、2800、1500、2016、6000、2688、3120　を