エクセルで集計

質問

こんにちは
エクセルでの集計でよい方法があれば教えてください。

乗車定員が9人の車で、何組かの団体がいます。
同じ団体は同じ車に乗ることが絶対条件で、最小で何台の車が必要か台数が簡単に出せるような関数があれば教えてください。また、その組み合わせを表示するようにすることは可能でしょうか？
（別のセルに組み合わせを表示できるようにすることは）

（例）
4人　2人　5人　3人　2人　4人　5人　2人　4人　　合計31人　　台数

31÷9で4台で全員乗ることができますが同じ団体は分かれて乗車することになりかねないです。（この場合は分かれて乗ることにはなりませんが・・・）
もっと数が多い場合や毎回組み合わせを考えるのが面倒なので、
組み合わせや台数を関数で計算できればとても助かります。
こんなことは可能でしょうか？
宜しくお願いします。

回答No.3

okormazd · ベストアンサー

VBAエディタ画面で、ツール―参照設定
で、SOLVERにチェックを入れてSOLVERを使えるようにしてください。
添付図のピンク部分を入力し、黄色部分にデータを入れます。
図では降順に入力されていますが、コードで並べ替えますから、順番はどうでもいいです。
黄色部分の下は何も入力しないでください。

下記プログラムを実行してください。
添付図のような結果が得られるでしょう。

Sub solver3()

Dim r1 As Integer, r2 As Integer, c0 As Integer, c1 As Integer, c As Integer
Dim n As Integer, lmax As Single, nin As Integer
Dim rm As Integer, nrm As Integer, total As Single, r As Integer, col As Integer

c0 = 2
c1 = 3
r1 = 3
c = 4
n = 0
lmax = Cells(1, 2)

r2 = Cells(r1, 1).End(xlDown).Row

Range(Cells(r1, c - 1), Cells(r2 + 1, 256)).ClearContents
Range(Cells(r2 + 1, c1), Cells(r2 + 100, 256)).ClearContents

Range(Cells(r1, c - 3), Cells(r2, c - 2)).Sort Key1:=Cells(r1, c - 3), Order1:=xlDescending, _
Key2:=Cells(r1, c - 2), Order2:=xlDescending

Cells(r2 + 1, c - 2).FormulaR1C1 = "=SUM(R[" & -(r2 - 1) & "]C:R[-1]C)"
total = Cells(r2 + 1, c - 2)

Range(Cells(r1, c0), Cells(r2, c0)).Copy
Cells(r1, c1).PasteSpecial

SolverReset

While total > 0

Range(Cells(r1, c + 1), Cells(r2, c + 1)).FormulaR1C1 = "=RC1*RC[-1]"
Range(Cells(r1, c), Cells(r2, c)) = 1
Range(Cells(r2 + 1, c - 1), Cells(r2 + 1, c + 1)).FormulaR1C1 = "=SUM(R[" & -(r2 - 1) & "]C:R[-1]C)"

rm = r1
nrm = Cells(rm, c1)
While nrm = 0
rm = rm + 1
nrm = Cells(rm, c1)
Wend

SolverAdd CellRef:=Cells(rm, c), Relation:=3, FormulaText:="1"

SolverOk SetCell:=Cells(r2 + 1, c + 1), MaxMinVal:=1, ValueOf:=lmax, ByChange:=Range(Cells(r1, c), Cells(r2, c))
SolverAdd CellRef:=Range(Cells(r1, c), Cells(r2, c)), Relation:=1, FormulaText:=Range(Cells(r1, c1), Cells(r2, c1))
SolverAdd CellRef:=Range(Cells(r1, c), Cells(r2, c)), Relation:=3, FormulaText:="0"
SolverAdd CellRef:=Range(Cells(r1, c), Cells(r2, c)), Relation:=4, FormulaText:="整数"
SolverAdd CellRef:=Cells(r2 + 1, c + 1), Relation:=1, FormulaText:=Format(lmax)

SolverSolve userfinish:=True

For r = r1 To r2
Cells(r, c1) = Cells(r, c1) - Cells(r, c)
Next

Range(Cells(r2 + 1, c), Cells(r2 + 1, c + 1)).Copy
c = c + 2
Cells(r2 + 1, c).PasteSpecial

total = Cells(r2 + 1, c1)

SolverReset

n = n + 1

Wend
Cells(r2, c) = "総車数"
Cells(r2 + 1, c) = n
Cells(r2, c + 1) = "総人数"
nin = 0
For col = c1 + 2 To c - 1 Step 2
nin = nin + Cells(r2 + 1, col)
Next
Cells(r2 + 1, c + 1) = nin
Cells(r2 + 1, 1) = "計"

End Sub

お礼

同じ表を作成して、VBAのプログラムをコピーして実行してみました。
しかし、「ソルバー:内部エラーまたはメモリ不足です」という表示がでて計算結果が下のようになりました。

乗車定員9　　　　　　　　　　　　　1号車
人数　　組数　　　作業列　　　組　　　　人
計　　　　1　 　　　　　　　　　　　　#VALUE!
5　　　　　2　　　　　2　　　　1　　　　　5
4　　　　3　　　　　　3　　　　　1　　　　4
3　　　　1　　　　　　1　　　　　1　　　　3
2　　　　3　　　　　　3　　　　　1　　　　2
　　　　　9　　　　　　　9　　　　　5　　　　#VALUE!
（ちょっとずれてしまいましたが・・・）

２号車以降は何も出力されませんでした。
どこがおかしいのでしょうか？
ちなみにPCのスペックは、
ウィンドウズXP
メモリは500MBでエクセル2003です。

それから、これはR1C1参照形式でしかできないのでしょうか？
試しに変更してみましたがやっぱりうまくできませんでした。

補足

丁寧なお返事ありがとうございます。

質問ですが、
この画像の表を新規のシートに作ってからVBAをコピーすればいいのですか？
また、プログラムを挿入するのはどのプロジェクトまたはモジュールにコピーすればいいのでしょうか？

回答No.5

okormazd

>ついでで申し訳ないんですが、

「黄色部分の下は何も入力しないでください。」
というのは、何人でも何組でも定員が何人でも対応できるようにしているためです。
必要な人数と組数をA列とB列に入れればいいです。

お礼

確かにできました。
本当にすごいですね、感動しました。
ありがとうございました。

VBAの本で初歩的なレベルから読むのにいい本をご存知でしたら、
ぜひ教えてもらえませんか？

回答No.4

okormazd

#2,#3です。
#3で、
「黄色部分の下は何も入力しないでください。」
と書いていますが。
「計」とか入力したんじゃないですか。

お礼

まさにそのとおりでした。
おみそれいたしました・・・。
すごいですね、こんなことができるなんて。ありがとうございました。

ついでで申し訳ないんですが、
A列の人数を1から9人まで増やして、組数もそれに対応して同じだけ増やすにはどこのパラメーターを変更すればいいのでしょうか？

回答No.2

okormazd

理論的に完全かどうかはわかりませんが、ExcelのSolverを使えば十分実用になるものができます。手動でもできますが、操作が面倒なのと、ここで説明するのが面倒なので、VBAでやることになりますが、VBAでも大丈夫ですか。

補足

お返事ありがとうございます。

ExcelのSolverは聞いたことがありますが使ったことはありません。
少し勉強してみます。
VBAは初歩的なものなら少しは分かりますがあまり自信はありません。
でも、ぜひ良かったら教えてください。

回答No.1

Be_DaMa

ご質問内容は、「ビンパッキング問題」といわれているものですね。
求め方は何通りも考案されていますので、まず、どういった方法で車の最小台数を
求めるかを考える必要があると思います。
しかし、あらゆる場合の最小数を効率的に見つけることができるような万能な方法はありません。
簡単な方法の例
団体を空いている人数の少ない車に乗せる→乗れないときは
次に空いている人数の少ない車に乗せる・・・・→
乗せる車がないときは新しい車に乗せる

これらを関数を使って計算するのは、私は無理なように思います。

参考URL：

http://ja.wikipedia.org/wiki/%E3%83%93%E3%83%B3%E3%83%91%E3%83%83%E3%82%AD%E3%83%B3%E3%82%B0%E5%95%8F%E9%A1%8C

お礼

早速お返事ありがとうございます。
「ビンパッキング問題」ですか。

調べてみます。ありがとうございました。