【Excel】ランダムで良いのが見つかりません

質問

いつもお世話になっております。

エクセルで掃除の当番表を作成したく知識を借りたいです。

９人を毎月ランダムで当番決めしたいです。
図の”●”が当番担当者を示しています。
この”●”をランダムで入れたいのです。

参考にできそうなサイトも見つけられませんでした。

どなたか参考になりそうなサイトや方法わかる方いましたら
宜しくお願い致します。

回答No.3

KURUMITO · ベストアンサー

大分苦労しましたがやっと出来上がりました。次のようにすればよいでしょう。
毎月ごとに当番を組みなおしている場合には当番に偏りが出ることが考えられますので、また、ランダムなデータはシートにデータなどの入力などの操作が有った場合には組み合わせが変わってしまいます。そこで作業用のシート（シート２）に当番を決めるために必要なデータをランダム操作で並べたのちにそのシートをコピーして値として別のシート（シート３）に貼り付けます。それによってシート上に新たな入力が行われても組み合わせが変わらなくなりますのでその別のシートを元にお求めのシート（シート１）を作成することにします。
シート１ではA１セルに日にちと入力し、B１セルには８月１日からの日付にするのでしたら2012/8/1と入力します。
C1セルには次の式を入力して右横方向にドラッグコピーします。

=IF(B1="","",IF(MONTH(B1+1)=MONTH($B$1),B1+1,""))

これでその月の月末の日付までが表示されます。
次にB1からAF１セルを選択して右クリックして「セルの書式設定」から「表示形式」の「ユーザー定義」で d"日"とします。これで１日、２日・・のように表示されますね。
A２セルには曜日と入力し、B2セルには次の式を入力して右横方向にドラッグコピーします。

=IF(B1="","",TEXT(B1,"aaa"))

A3セルから下方には氏名を入力します。必ず入力してから次のシート操作に移ってください。氏名は暫定的な入力で構いません。
シート２ではA１セルには例えば2012/8/1のように日付を入力して右横方向にドラッグします。数か月分を表示するようにしても構いません。
A２セルには次の式を入力して横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNTA(Sheet1!$A$3:$A$100),"",IF(COLUMN(A1)=1,IF(ROW(A1)=1,"●",RAND()), IF(COLUMN(A1)<COUNTA(Sheet1!$A$3:$A$100),IF(COUNTIF($A2:OFFSET(A2,0,-1),"●")>0,"",IF(OFFSET(A2,0,-1)=MAX(OFFSET(A$2,0,-1):OFFSET(A$2,100,-1)),"●",RAND())),IF(COLUMN(A1)=COUNTA(Sheet1!$A$3:$A$100),IF(OR(OFFSET(A2,0,-1)="",OFFSET(A2,0,-1)="●"),RAND(),"●"), IF(MOD(COLUMN(A1)-1,COUNTA(Sheet1!$A$3:$A$98))+1=1,IF(OFFSET(A2,0,-1)=MAX(OFFSET(A$2,0,-1):OFFSET(A$2,100,-1)),"●",RAND()),IF(MOD(COLUMN(A1)-1,COUNTA(Sheet1!$A$3:$A$100))+1<COUNTA(Sheet1!$A$3:$A$100),IF(COUNTIF(OFFSET(A2,0,-MOD(COLUMN(A1)-1,COUNTA(Sheet1!$A$3:$A$100))):OFFSET(A2,0,-1),"●")>0,"",IF(AND(OFFSET(A2,0,-1)<>"",OFFSET(A2,0,-1)=MAX(OFFSET(A$2,0,-1):OFFSET(A$2,100,-1))),"●",RAND())),IF(MOD(COLUMN(A1)-1,COUNTA(Sheet1!$A$3:$A$100))+1=COUNTA(Sheet1!$A$3:$A$100),IF(OR(OFFSET(A2,0,-1)="",OFFSET(A2,0,-1)="●"),RAND(),"●"))))))))

その後にシート２全体を選択して「コピー」し、次にシート３を選択します。シート３のA１セルを選択したのちに「形式を選択して貼り付け」から「値」にチェックをして貼り付けます。
これによてランダムな配置が変化することのないシートが完成します。このシートを元にシート１の表を次の操作によって完成します。
シート１のB３セルには次の式を入力して右横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。

=IF(OR(B$1="",$A3=""),"",IF(INDEX(Sheet3!$A$2:$XX$100,ROW(A1),MATCH(B$1,Sheet3!$1:$1,0))="●","●",""))

シート３の１行目に表示されている日付に基づいた組み合わせが表示されることになります。
同じ組み合わせのままで良ければ、シート３にシート１に合致する日付が無くともシート３の１行目の日付を手動で変えることによって対応することができます。

お礼

こんな質問に熱くご回答してくださり
ありがとうございました！
とても助かりました。

回答No.2

kagakusuki

当番日そのものを完全にランダムにしますと、当番となる日数が人によって異なって来てしまいますので、掃除当番を行う順番をランダムに決まる様にされては如何でしょうか？（この方法でも月の変わり目で、当番を行う日数に差が生じてしまう恐れがありますが）

　今仮に、Sheet2のA列とB列を作業列として使用して、Sheet1に当番表を作成するものとします。
　まず、Sheet2のA5セルに次の数式を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",RAND())

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

=IF(ISNUMBER($A5),RANK($A5,$A:$A),"")

　次に、Sheet2のA5～B5の範囲をコピーして、同じ列の6行目以下に貼り付けて下さい。

　次に、Sheet1の
B1セルに　　年
D1セルに　　月掃除当番表
A3セルに　　日付
A4セルに　　曜日
と入力して下さい。
　次に、Sheet1のB3セルに次の数式を入力して下さい。

=IF(ISNUMBER(($A$1&"/"&$C$1&"/"&COLUMNS($B:B))+0),IF(MONTH(($A$1&"/"&$C$1&"/"&COLUMNS($B:B))+0)=$C$1,($A$1&"/"&$C$1&"/"&COLUMNS($B:B))+0,""),"")

　次に、Sheet1のB3セルをコピーして、Sheet1のB3～AF4の範囲に貼り付けて下さい。
　次に、以下の操作を行って下さい。

Sheet1のB3セルにカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

d

と入力
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
Sheet1のB4セルにカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

aaa

と入力
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

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

=IF(AND(INDEX($3:$3,COLUMN())<>"",MOD(COLUMN()-COLUMN($B$3),COUNT(Sheet2!$B:$B))+1=INDEX(Sheet2!$B:$B,ROW())),"●","")

　次に、Sheet1のB5セルをコピーして、Sheet1のC5～AF5の範囲に貼り付けて下さい。
　次に、Sheet1のB5～AF5の範囲をコピーして、同じ列の6行目以下に貼り付けて下さい。

　後は、Sheet1のA5以下に各当番担当者の名前を入力してから、A1セルに西暦年、C1セルに月を入力しますと、当番準がランダムに決められた当番表が表示されます。

　尚、この方法では、何処かのセルに入力を行ったり、ファイルを開き直したり、F9キーを押したりする度に当番表が自動的に書き換わってしまいます。
　これを防ぐためには、Excelの「計算方法の設定」を[手動]とされた上で、[ブックの保存前に再計算を行う]という箇所のチェックを外して下さい。
　そして、「計算方法の設定」を[手動]とした場合において、表を変更する等のために再計算を行うためには、F9キーを押せば再計算が行われます。

お礼

お礼が遅くなり申し訳ありません！
とても丁寧にご回答してくださり
ありがとうございました。
この手順でやっていったら出来ました！
本当にありがとうございました。

回答No.1

9056-9046

9人で31日を掃除交番するとなると
1人3回その他4人が4回になるようですね。

こういった交番は斜め交番で
右上がりとかその逆でよく設定しますよね。

そんな単純だと駄目でしょうかね？

その他参考までに
一つ●箇所をコピーして●を入れたい
セル一つ一つをCtrl+クリックして
最後に貼付けすれば一度で全部埋められますよ。

そういったことではない！？かな？

お礼

そういった方法をとるのも一つの手でした。
私の質問内容が不十分で申し訳ありませんでした。

ご回答ありがとうございます！