[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『エクセルにて毎日変わる席順表を作りたい』（のい）
エクセルにて席順表を作成しようとしているのですが、
条件として
?@34人の人がいる
?A毎日違う人と隣になるようにする
?B同じ人と隣になるのは全員と隣になったあと
という条件があります。
エクセルで表示できればマクロだろうと関数だろうと構いません。
最終的には印刷して紙媒体で席順表として出力したいです。
あまり知識がないので方法が思い浮かばなないのです。
毎日違う人と隣になるようする、ということはボタンか何かを設置して押せば、別シートにその日の席順を書き込むようにするか、上記の手順を手動でやるか･･･ですよね
操作するのは誰になるか分からないのでなるべく操作を簡略化したい思いもあります。
マクロボタンなどを多用すべきなのでしょうか・・・
どなたか良い方法をご教授ください。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
条件として
・34人の人がいる
・毎日違う人と隣になるようにする
・同じ人と隣になるのは全員と隣になったあと
という条件があります
上記になります。
分かりづらいですがどなたかお願いします。
（のい） 2015/03/30(月) 22:07
（マナ ） 2015/03/30(月) 23:09
下記のように左上の 1 を固定にし、その他の部分をローテーションすれば、 出来るかと思います。
1-34 1-33 1-32 ↙ ↑ ↙ ↑ ↙ ↑ 2-33 34-32 33-31 ↓↑ ↓↑ ↓↑ 3-32 2-31 34-30 ↓↑ ↓↑ ↓↑ : : : ↓↑ ↓↑ ↓↑ 15-20 14-19 13-18 ↓↑ ↓↑ ↓↑ 16-19 15-18 14-17 ↓↑ ↓↑ ↓↑ 17-18 16-17 15-16 → → → （Mook） 2015/03/30(月) 23:31
もし、ランダムにやるとしたらまず34回分の隣になったことがある人リストを34人分作ることが一番最初にやるべきことだと思います。
（スズメ） 2015/03/30(月) 23:38
説明不測で申し訳ありません。企業内での席順になります。
社員同士のコミュニケーションの円滑化を図るため部署内全員と話せるようにしようというものです。
隣の席が異性でなければいけないという訳ではございません。
Mook様ありがとうございます。
似たような方法を上司に提案したところ規則性があれば次の日誰と座るか分かってしまっておもしろくないと棄却されてしまいまして・・・
提案していただいた身で失礼を承知で申し上げますが、完全にランダムで席順が入るようにはならないでしょうか？
（のい） 2015/03/30(月) 23:41
この３３通りの組み合わせを実施する順番を、 適当にばらければ、予測つきにくくはないですか。 これまた、何となくですが。
（マナ ） 2015/03/31(火) 00:14
私は2015年3月30日23時38分に34人分の隣になったことリストを作ると言いました。
その続きです。まず、A1〜A34に名前を書き、B1〜B34にrandbetween(0,1000000)と書きます。そのあと、B列を基準に降順にし、座席表の一番右上の席に数字の最も高い人を配置します。そして、２番目に数字の大きい人が隣にくるのですが、もし、座席表の一番右上の人が２番目に数字の大きい人と隣になったことがあれば、三番目に数字の大きい人が来ます。その人も隣になったことがあれば、四番目に数字の大きい人が来ます。この繰り返しで、隣に来る人が決まったら、座席表の一番右上の人の後ろに、まだ座席が決まっていない人のうちの数字が最も大きい人が来ます。そして、その人の隣の人を決めます。これでどうでしょうか？
（スズメ） 2015/03/31(火) 08:47
隣になる人が決まったとき、それによって残りの人が隣にくることのできる人がいなくなることのないように、もし、残りの人の中で、隣になることができる人がいなくなってしまう場合は、その時隣になる人が決まった人は再度決め直すシステムを導入しなければなりませんね。
（スズメ） 2015/03/31(火) 09:46
＞ この３３通りの組み合わせを実施する順番を、 ＞ 適当にばらければ、予測つきにくくはないですか。 ＞ これまた、何となくですが。
私も同感なんですけど、どうしてMookさんのシンプルなアイデアをベースに工夫しないのですかねぇ。。
33通りを規則正しく作る。
それら33通りの実施順をバラバラになるように決める。（33通りが全部終わるまで、この順番は固定化する）
各組合せの「各ペアの席」をランダムに配置する。
33通り終わったら、新しい実施順を決定して繰り返す。
なんかすごく簡単にいきそうな気がしますけど。。。
（半平太） 2015/03/31(火) 12:46
基本最初の答えをコード化したものに、マナさん、半平太さんのランダムに配置する というアイデアを足したものです。
上段がテーブル表、下段が上段をペアを変えずに順番と左右をランダムにシャッフルしたものです。
Sub Seating()
Dim seatingMaster(1 To 17, 1 To 2)
Dim seatingTable(1 To 17, 1 To 2)
Dim objSL
Set objSL = CreateObject("System.Collections.SortedList")
'// Initialize
Randomize
Range("A1").Resize(1, 100).EntireColumn.ColumnWidth = 3
Range("A1").Resize(40, 100).ClearContents
Dim r As Long
For r = 1 To 17
seatingMaster(r, 1) = r
seatingMaster(r, 2) = 35 - r
Next
Dim c As Long
For c = 0 To 32
'// Shuffle
objSL.Clear '// 修正
For r = 1 To 17
If Rnd() > 0.5 Then
objSL.Add Rnd(), seatingMaster(r, 1) & vbTab & seatingMaster(r, 2)
Else
objSL.Add Rnd(), seatingMaster(r, 2) & vbTab & seatingMaster(r, 1)
End If
Next
'// Preparation
For r = 1 To 17
seatingTable(r, 1) = Split(objSL.getByIndex(r - 1), vbTab)(0)
seatingTable(r, 2) = Split(objSL.getByIndex(r - 1), vbTab)(1)
Next
'// Output
Range("A1").Offset(0, c * 3).Value = "M【" & c + 1 & "】" 'Master List
Range("A2").Offset(0, c * 3).Resize(17, 2) = seatingMaster
Range("A20").Offset(0, c * 3).Value = "S【" & c + 1 & "】" 'Shuffle List
Range("A21").Offset(0, c * 3).Resize(17, 2) = seatingTable
'// Rotation
Dim rightTop As Long
Dim leftBottom As Long
rightTop = seatingMaster(1, 2)
leftBottom = seatingMaster(17, 1)
For r = 1 To 16
seatingMaster(18 - r, 1) = seatingMaster(17 - r, 1)
seatingMaster(r, 2) = seatingMaster(r + 1, 2)
Next
seatingMaster(17, 2) = leftBottom
seatingMaster(2, 1) = rightTop
Next
End Sub
スズメさんのアイデアは1回、2回だけならいけるかもしれませんが、 >同じ人と隣になるのは全員と隣になったあと という条件で破綻します。
これはこれまで同じペアにならないという組合せを全体の中で実現させないとならず、 全体の中でサブグループが出来てしまうと、３３のユニークなセットが出来ないため で、今回は乱数の使用が不向きなケースです。
でも >毎日席が変わるなんて、いちいち朝に座る位置を確認しなければならなくて面倒！、という社員の声が上がることに1票。 にもう一票。
（Mook） 2015/03/31(火) 13:15
ローテーションは普通偶数人数で行われます(もし奇数人数なら１人隣にならない人がでてくる)。Mookさんのやり方のように、左右の列が動くと、偶数番号の人は奇数の人と、奇数番号の人は偶数番号の人としか隣になりません(右の列の一番前が20、右の列の一番後ろが11、そのとなりが10、左の列の一番前が1の場合)。ですのでもしすべての人と当たるようにするには某番組のお見合い回転寿司のように、片方の列のみをうごかさなければなりません。
（スズメ） 2015/03/31(火) 13:45
あまり詳しく覚えていませんが、ナインティナインだったと思います。
（スズメ） 2015/03/31(火) 14:04
33 のテーブルの左右のペア561（33×17）の組合せには重複はないはずです。 Dictionary や COUNTIF 等で確認してみてください。
>最初のローテーションは含まれているのでしょうか？ の意味がわかりませんが、マクロの実行結果の上段の表が私の最初の回答のものです。
（Mook） 2015/03/31(火) 14:11
あちゃー > <! リストの初期化が抜けてた。
修正しました。
（Mook） 2015/03/31(火) 14:26
質問や皆さんの回答の流れを詳細に把握してはいないのですが
３３回のユニークな組み合わせができたとして、３４日目は、また、１からリセットでいいのでしょうか？ つまり、３４日目には昨日と全く同じ人とペアになることがＯＫなのか、やはり、その前の３２日間も含めて ユニークにならなければいけないのか、要件としてはどちらでしょうか？
（β） 2015/03/31(火) 15:14
βさんの言ったことを考慮すれば、 34C2 =561 (EXCELでは =COMBIN(34,2) ) ですので、今回の表のセットは多数の解のうちの 一つではなく、唯一の解のようですから、過去32日間を考慮すると33の表を順番に使うしか 方法は無いようです。
ただ、ランダムにすると組合せはともかく、座る席の場所には偏りが出ますね。 座る席も均等にというと、問題は更に複雑化しそうですが。
（Mook） 2015/03/31(火) 15:52
Mookさんにコード化していただいたものを元に無事上司の許可を取り付けることに成功しました。
ありがとうございました。
今回は臨時の新人講習会で使用する席順でして、各々にコミュニケーションを取ってほしいということで、毎日違う席順、及び被らないようにという要望を出させていただきました。
日程は全部で50日ほどあるので1週半くらいする予定になるのでしょうか。
1週したら計算しなおしたものをもう一度入れるだけの予定です。
VBAで出力されたシートとVLOOKUPを使ってVBAシートを更新するだけで良くなったのでこれからも使用できそうです。
自分自身、何故席順程度でここまでしなければならない感が否めませんでしたがエクセル使用法をまたひとつ知ることができてよかったです。
皆様ありがとうございました。
（のい） 2015/03/31(火) 18:17
ランダムに見えても、実際は最初の表のデータのままなので、記憶力が確かな人がいれば、 ２周目の自分の相手は1周目と同じことに気が付くでしょう。
50日限定であれば、1周目は１〜33の表を順番に、2周目は奇数番号の表を後ろからなどと すると、気が付く人は減るかもしれません。 （そうすると初日とと最終日は同じ相手ということになりますが。）
最初に、 >新人講習会で使用する席順でして、各々にコミュニケーションを取ってほしい という説明があれば、良かった気がします。
いずれにせよ、おもしろい内容の質問で、私も楽しませてもらいました。 （Mook） 2015/03/31(火) 19:09
seatingMaster(r, 2) = 35 - r を seatingMaster(r, 2) = ｒ+17
にしたものを使用すれば、気づかれにくいかも。
（マナ） 2015/03/31(火) 19:54
なるほど。 表の初期データを変えれば、簡単にバリエーションは作れますね。 その方が表の使う順序を変えるより、お手軽ですね。 （Mook） 2015/03/31(火) 20:10
A1： =Rand()
D7までコピー
A11： =IF(RANK(A1,$A1:$D1)=4,8,MOD(RANK($A1,$A$1:$A$7)+RANK(A1,$A1:$D1)-1,7)+1)
D17までコピー
E11： =MOD(RANK($A1,$A$1:$A$7)-RANK(A1,$A1:$D1),7)+1
H17までコピー
（日捲り熊五郎） 2015/03/31(火) 23:13
なぜなら現行のやり方は35日以降1周目と同じパターンなので、上司に見破られてしまえばあまり良くないからです。
（スズメ） 2015/03/31(火) 23:38
ちょっと説明を。
乱数で探そうとした場合、最初の数日は出せると思いますが、 日数が経つほど（過去の結果による制約が多くなればなるほど）結果の算出のための やり直しの計算処理が大変になるということです。
例えば、運よく（？）32日まで重複無い結果が出せたとしましょう。 この時、33日目で重複無い可能性は一通りの組み合わせになります。 34人から二人ずつ17ペアにする組合せは、34! ですから、約2.95＊10^38 です。 この内、条件を満たす組合せ（17組のペアによる並び）は2^17*17! ですから 約4.66*10^19です。 ということは、約 1/633京の確率（1京は１兆の1万倍）ということです。 （計算違ってたらすみません。）
これにたどり着くまでは、どれだけ計算をやり直しますか？ ということで、乱数が不向きと書きました。
>なぜなら現行のやり方は35日以降1周目と同じパターンなので、上司に見破られてしまえばあまり良くないからです。 に関してはマナさんが提案されたように、初期データの並びを変える、 テーブルの使用順序を変える、で簡単に改善ができます。
乱数で求めることが簡単な場合もありますが、条件が厳しい場合は不向きなことも 多いのです（アルゴリズム次第というのもありますが）。
（Mook） 2015/04/01(水) 00:06
只今日捲り熊五郎 さんの式を検証中。 （Mook） 2015/04/01(水) 00:26
お見事！
組み合わせを完全に乱数だけで求める力技であれば、これまでの説明の通りですが、 これは、組み合わせ自体は数式の中にアルゴリズムとして織り込んでいるので 無駄なパターン計算が一切必要ありませんね。
>アルゴリズム次第というのもありますが と書きましたが、これはエレガントな計算方法です。
>＞今回は乱数の使用が不向きなケースです。 >と言い切られているので、ちょっと頭の体操してみることにします。 ちょっと意図した用法とは異なってはいますが（言い訳・・・）、 先の発言は撤回します。
（Mook） 2015/04/01(水) 01:12
誤字訂正
（Mook） 2015/04/01(水) 09:17
1 人数は25人
2 10ﾍﾟｱ・5人はバラ
3 5人は毎週同じ人（月〜金）
4 10ペアは1ヶ月同じペアにならない（20日）
5 席は、2日連続で同じ席にならない
5は自分で席を決定するにしても、5人チームはメンバ固定、なおかつ20人は毎日違うペア、というところが自分の頭では考えきれず悩み倒しました。
どなたかご教授して頂ければ、と思います。
よろしくお願いします。
（メジロ） 2015/09/02(水) 09:37
テーマは同じですが、わかりにくくなると思いますので新規にトピを立ち上げられてはいかがですか？ その際には、このトピを
[[20150330215418]] 『エクセルにて毎日変わる席順表を作りたい』（のい）
このようにリンクを貼って参照できるようにしておき、かつ、組まれたＶＢＡコードをすべてコピペで アップされると、回答者さんたちにとってわかりやすいと思います。
（β） 2015/09/02(水) 09:46
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.