こんにちは
エクセルでの集計でよい方法があれば教えてください。
乗車定員が9人の車で、何組かの団体がいます。
同じ団体は同じ車に乗ることが絶対条件で、最小で何台の車が必要か台数が簡単に出せるような関数があれば教えてください。また、その組み合わせを表示するようにすることは可能でしょうか?
(別のセルに組み合わせを表示できるようにすることは)
(例)
4人 2人 5人 3人 2人 4人 5人 2人 4人 合計31人 台数
31÷9で4台で全員乗ることができますが同じ団体は分かれて乗車することになりかねないです。(この場合は分かれて乗ることにはなりませんが・・・)
もっと数が多い場合や毎回組み合わせを考えるのが面倒なので、
組み合わせや台数を関数で計算できればとても助かります。
こんなことは可能でしょうか?
宜しくお願いします。
No.3ベストアンサー
- 回答日時:
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をコピーすればいいのですか?
また、プログラムを挿入するのはどのプロジェクトまたはモジュールにコピーすればいいのでしょうか?
同じ表を作成して、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!
(ちょっとずれてしまいましたが・・・)
2号車以降は何も出力されませんでした。
どこがおかしいのでしょうか?
ちなみにPCのスペックは、
ウィンドウズXP
メモリは500MBでエクセル2003です。
それから、これはR1C1参照形式でしかできないのでしょうか?
試しに変更してみましたがやっぱりうまくできませんでした。
No.4
- 回答日時:
#2,#3です。
#3で、
「黄色部分の下は何も入力しないでください。」
と書いていますが。
「計」とか入力したんじゃないですか。
まさにそのとおりでした。
おみそれいたしました・・・。
すごいですね、こんなことができるなんて。ありがとうございました。
ついでで申し訳ないんですが、
A列の人数を1から9人まで増やして、組数もそれに対応して同じだけ増やすにはどこのパラメーターを変更すればいいのでしょうか?
No.1
- 回答日時:
ご質問内容は、「ビンパッキング問題」といわれているものですね。
求め方は何通りも考案されていますので、まず、どういった方法で車の最小台数を
求めるかを考える必要があると思います。
しかし、あらゆる場合の最小数を効率的に見つけることができるような万能な方法はありません。
簡単な方法の例
団体を空いている人数の少ない車に乗せる→乗れないときは
次に空いている人数の少ない車に乗せる・・・・→
乗せる車がないときは新しい車に乗せる
これらを関数を使って計算するのは、私は無理なように思います。
参考URL:http://ja.wikipedia.org/wiki/%E3%83%93%E3%83%B3% …
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(悩み相談・人生相談) 常識がないと思うのは私だけでしょうか。 友人3人とランチに行きます。 とても人気で予約が取れないくら 7 2022/12/25 11:55
- 計算機科学 2500円の人と2000円の人の集計 2 2023/08/06 07:18
- 統計学 お酒に強い人の割合について 2 2022/09/10 18:42
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
- その他(家族・家庭) どのようにするのが良いでしょうか? 来月、車で片道1時間半ほどのアウトレットモールに行く計画をしてい 6 2023/07/24 20:16
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- 電車・路線・地下鉄 マニアに質問。JR電車内で車掌に乗り越し精算、乗車区間変更をお願いする時の事ですが・・・ 2 2022/10/02 20:58
- Excel(エクセル) 図書カードの分配 7 2023/05/09 15:57
- その他(悩み相談・人生相談) 代行タクシー(タクシー代行)について 1 2023/06/25 16:35
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBAでグループごとソートす...
-
電気回路の閉路方程式の問題
-
力率80%の根拠
-
「電流を印加する」という表現...
-
オペアンプ/反転増幅器/頭打ち
-
モーターの電流値が上がるのは...
-
パルスとレベルについて
-
400V 3相4線式について...
-
ACアダプタについて教えてくだ...
-
立ち上がり電圧について。たと...
-
アプリ life360 について、 ネ...
-
ブレーカー容量の余裕について
-
以下の条件で短絡保護回路を製...
-
ヒューズは何故劣化するのですか?
-
直流の消費電力/消費電流
-
質問です。3Aって何wですか? ...
-
DフリップフロップからJKフリッ...
-
直流電流から交流電流への換算...
-
DC24Vにて容量計算がわかりませ...
-
三相三線式のS相は接地されてい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXCEL VBA 行の値を累計したい...
-
剛体の力学の問題 正三角形
-
球殻状のコンデンサの電界を求...
-
統計学(離散型確率変数)の問...
-
材料力学の問題ですが
-
4次方程式の固有値の求め方
-
トラックへの荷物の積みかたを...
-
iPad Airの購入について
-
電気回路 Y-Δ変換について
-
電気回路の閉路方程式の問題
-
材料力学について質問です。 問...
-
エクセル 指定した複数の文字の...
-
θ=(C1/r^2)+C2•••(8) 定数C1,C2...
-
2つのシートを重複検索したあと...
-
ブタジエン(M1)/アクリロニトリ...
-
作った表から一覧表を作成をしたい
-
流体力学の公式証明が解らない
-
抵抗値計算
-
対称・反対称な波動関数
-
(3)(4)がわからないです。 (1) ...
おすすめ情報