団体旅行の号車振り分けをしたい。

質問

はじめまして。私は今度、大きなツアーの責任者に抜擢されまして、いろいろな団体に所属なさっている参加者、約1000名をまとめて20台ほどのバスに詰め込んで、目的地まで連れて行くための、号車の振り分けを作成しなければならなくなりました。

しかし、その参加者が、いろいろな団体から集められているために、団体ごとにまとめてくれという同乗希望が相次ぎ、振り分けが複雑化してしまいました。また同乗希望が、次々に後から追加で申請される状況で、バスの定員などもあるので、振り分けをいちいち作り変えなければならず、別の本来の仕事に集中できずに困っています。
また、それぞれのバスの男女の数もできるだけ半々にしろとのことで、できたらエクセルに打ち込んである参加者名簿から、こういった振り分けをコンピューターにしてもらえたらと思った次第です。

行うことは、定員50名のバスに、男女を約25名ずつ（定員は完璧に埋まらなくてもよい、男女比も1～2名のズレがでてもよい、とのこと）
同乗希望が出ている人達は同じバスに乗せる（同乗希望の出ていない、1人で参加するという参加者は、単独にせず、同じく1人で参加するという人達と一緒に乗せたい(バス内で、浮いてしまうのを防ぐため)）
です。
また、各団体配布用振り分け表を、同じくエクセルで作ったので、そちらに結果が反映されるともっとうれしいです

このような作業をエクセルのマクロを使えば行なえるかなと思ったのですが、そっち方面は知識がほとんど無く、やってみたはいいものの、皆目見当もつかずお手上げでした。どなたかお詳しいかたで、そのようなプログラムを書ける方がいたらぜひともお力をお借りしたい所存です。宜しくお願いいたします。

回答No.2

papayuka · ベストアンサー

#1です。

> 要求レベルが高かったですかね・・・

文章だけでしかやり取り出来ない場所では特にそうです。
データをどんな状態保持しているのかで処理が大きく異なりますから。

> 同乗希望・男女関係なく、ただ号車を振り分けてくれるだけのプログラム

これはリストの上から50名ずつ同一番号を打っていくだけに思いますけど、そう言う事ですか？
先の表と同じだとしたら、こんな感じ？

Sub Test()
Dim i As Long, Cnt As Integer, Car As Integer
Cnt = 1: Car = 1
With ActiveSheet
　For i = 2 To .Range("A65536").End(xlUp).Row
　　　.Range("D" & i).Value = Format(Car, "0#号車")
　　　Cnt = Cnt + 1
　　　If Cnt > 50 Then
　　　　 Car = Car + 1: Cnt = 1
　　　End If
　Next i
End With
End Sub

回答No.1

papayuka

要求レベル全てにVBAで対応可能か判断出来ませんし、仮にVBAで実現出来たとしても相当複雑な処理が必要だと感じます。例外的な処理もだいぶありそうで、人的作業は必須な気もします。実際のデータ構成が不明なこういった場のやりとりで出来るような事でも無いでしょう。

例えば↓こんなもんでも作業軽減にはなるでしょうか？

A1:D7に下記のリストがあるとする。

氏名　　性別　　団体名　　号車
山田　　男　　　×商事　　1号車
佐藤　　女　　　×商事　　1号車
佐伯　　女　　　○商店　　2号車
鈴木　　男　　　○商店　　2号車
田中　　男　　　△産業　　3号車
井上　　男　　　△産業　　3号車

F1:H4を下記のようにして

号車　　男　　女
1号車　 G2
2号車
3号車

G2 に =SUMPRODUCT(($B$2:$B$7=G$1)*($D$2:$D$7=$F2))
と入れて、残りのセルにコピーすると下記のような結果が出ます。

号車　　男　　女
1号車　　1　　1
2号車　　1　　1
3号車　　2　　0

この表を睨みながら、リストの号車欄を適宜修正する。

補足

要求レベルが高かったですかね・・・

同乗希望・男女関係なく、ただ号車を振り分けてくれるだけのプログラムだとどんな風に作ればよいでしょうか？
最低それさえ自動化できたら、あとは自力でやれそうな気がします。
男女は名簿を分けてそれぞれで分けて作業すればできそうですし。
今ある名簿はpapayukaさんの例と同じ作りで、最終的に出力する表には、氏名と団体名の枠だけです。