1. Home » 
2. Excel講座の総目次 » 
3. Excel講座 Q&A(Tips) » 
4. 掃除当番表を作成したい

掃除当番表を作成したい：Excel Q&A

作成:2015/3/5

掃除当番表を作成する　　　　topへ

• 当サイトの掲示板にて掃除当番表を作成したいとの質問がありました。
  質問には曜日ごとに休暇の人を除外する必要があるようなのですが、この処理は複雑になるのでここでは機械的に当番を割り振る方法までとします。
• ここでは、担当する人は20名で、1階、2階、3階の各階を一人で当番することにします。また、土日なども稼働する職場とします。
• 下表のように、当番を割り振るための数式を作成します。
  掃除当番の完成図
  • 
• 使用した計算式(数式)
  • B3セルに　=INDEX($G$3:$G$22,IF(MOD(COLUMN(A1)+(TEXT($A3,"d")-1)*3,20)=0,20,MOD(COLUMN(A1)+(TEXT($A3,"d")-1)*3,20)))　と入力します。
    この数式をD3セルまでオートフィルでコピーし、さらにB3:D3セルを下方向へB32:D32までオートフィルでコピーしています。
  • まず表示する当番の人のリストはG3:G22に書き出してあります。
    このリストから、順番に一人ずつ取り出していますので、
    =INDEX($G$3:$G$22,順番)
    といった数式でよさそうです。
  • 順番が　横方向(列方向)へ1,2,3　となり、次の行では4,5,6、更に次の行では7,8,9・・・　といった具合に変化すれば良いと考えました。
    この順番を求めるために、横方向(列方向)は1,2,3　という順番なので　COLUMN(A1)　として、横方向へコピーすると、COLUMN(B1)、COLUMN(C1)となり、1,2,3が得られます。
  • 行方向はここでは日付の日にちを使うことにしました、具体的には　4月1日の1日の1を利用しようというわけです。
    TEXT(A3,"d")　で下方向へコピーすると、1,2,3・・・と連番が得られます。
    • 月日ではなく、行番号で作成することもできます。そのときはROW(A1)　とします。
    2行目以降ではでは4、7、10・・・といった3の倍数を作成すれよいと考えました。
    COLUMN(A1)+(TEXT($A3,"d")-1)*3　とすることで順番が作成することができます。
    順番は20まできたら、次は1へ戻る必要がありますので、20で割った時の余りをつかえば、0〜19の連番が得られます。
    20で割り切れるときは0となるので、この0のときは20とします。
    IF(MOD(COLUMN(A1)+(TEXT($A3,"d")-1)*3,20)=0,20,MOD(COLUMN(A1)+(TEXT($A3,"d")-1)*3,20))
    としました。
    • 当番になる人が10人であれば、1〜10の繰り返しにするため、
      IF(MOD(COLUMN(A1)+(TEXT($A3,"d")-1)*3,10)=0,10,MOD(COLUMN(A1)+(TEXT($A3,"d")-1)*3,10))
      となります。
  • IF関数の部分だけの実行例が下図です。1〜20の連番が作成できたことが分かります。
    
  • 最初の　INDEX関数の部分と組み合わせて、
    =INDEX($G$3:$G$22,IF(MOD(COLUMN(A1)+(TEXT($A3,"d")-1)*3,20)=0,20,MOD(COLUMN(A1)+(TEXT($A3,"d")-1)*3,20)))
    として完成としました。
    

• 下図のようなケースでは?

  • 実際の質問では下図のように、横に日付が並んでいるものでした。
    
  • 上記とは列と行の関係が逆になっているのですから、数式でこの関係を逆にするだけです。
    B3セルに　=INDEX($K$2:$K$21,IF(MOD(ROW(A1)+(TEXT(B$2,"d")-1)*3,20)=0,20,MOD(ROW(A1)+(TEXT(B$2,"d")-1)*3,20)))
    と、入力して、B6セルまでオートフィルでコピーします。B3:B6をオートフィルでH3:H6までコピーして1週目は完成です。
    • =IF(MOD(ROW(A1)+(TEXT(B$2,"d")-1)*3,20)=0,20,MOD(ROW(A1)+(TEXT(B$2,"d")-1)*3,20))で下図のような連番を作成することができました。
      
  • 2週目は当番の始まりが2からになるので、
    B8セルは　=INDEX($K$2:$K$21,IF(MOD(ROW(A2)+(TEXT(B$2,"d")-1)*3,20)=0,20,MOD(ROW(A2)+(TEXT(B$2,"d")-1)*3,20)))　としました。
    3週目は始まりが、3からになるので
    B13セルは　=INDEX($K$2:$K$21,IF(MOD(ROW(A3)+(TEXT(B$2,"d")-1)*3,20)=0,20,MOD(ROW(A3)+(TEXT(B$2,"d")-1)*3,20)))　としました。
    4週目は始まりが、4からになるので
    B18セルは　=INDEX($K$2:$K$21,IF(MOD(ROW(A4)+(TEXT(B$2,"d")-1)*3,20)=0,20,MOD(ROW(A4)+(TEXT(B$2,"d")-1)*3,20)))　としました。
  • なお、この週初めの開始数値は人数が異なると異なりますので、調整する必要があります。
    例えば15名で回すとすれば　数式は　=IF(MOD(ROW(A1)+(TEXT(B$2,"d")-1)*3,15)=0,15,MOD(ROW(A1)+(TEXT(B$2,"d")-1)*3,15))　となります。
    第2週目は　=IF(MOD(ROW(A7)+(TEXT(B$2,"d")-1)*3,15)=0,15,MOD(ROW(A7)+(TEXT(B$2,"d")-1)*3,15))　と7始まりにする必要があります。
    

スポンサードリンク

PageViewCounter

Since2006/2/27