シフト表 パターン入力について

エクセルでシフト表を作成しています。
勤務者リスト、パターンリストがあり、F4・Ｆ7に担当者を入力した場合1日の担当者に自動的に
入力され以下もオートフィル等を使用し一覧を作成したいです。
担当者リスト、固定で山田→佐藤→田中の順で繰り返しです。
パターンリストも同様に、A→B→Cの順です。

関数を使うべきなのかマクロを使うのか手がかりを検索してみましたがどうしてもわかりません。
（マクロはそこまで詳しくありません）
お手数をおかけ致しますがどなたか、知恵をお貸しください。

No.6 ベストアンサー 回答者： tom04 回答日時： 2017/11/22 20:27

No.5です。

D列のエラー処理をしていませんでした。

D3セルの数式を
=IFERROR(IF(C3="","",INDEX(J$4:J$10,IF(MOD(MATCH(F$7,J$4:J$10,0)+COUNTIF(D$2:D2,"?*"),COUNTA(J$4:J$10))-1=0,COUNTA(J$4:J$10),MOD(MATCH(F$7,J$4:J$10,0)+COUNTIF(D$2:D2,"?*")-1,COUNTA(J$4:J$10))))),"")

にしてください。m(_ _)m

この回答へのお礼

書き込みありがとうございます。
すごい！！の一言です。可動しました。
取り急ぎ関数式をコピペして、使用させて頂きました。
ゆっくり解読させて頂きます。できるかな･･･

書き込みして頂いた皆さん本当にありがとうございました。
またよろしくお願いします。

関数の得意な皆さん一体どんな仕事をなさってるのか、すご過ぎて
そんな疑問さえもちました。

お礼日時：2017/11/24 11:33

No.5 回答者： tom04 回答日時： 2017/11/22 20:11

こんばんは！

土日・祝日の扱いがある場合、シリアル値で処理した方が簡単だと思います。
一例です。

とりあえず担当者、パターンともH・J列の4～10行目までの範囲を対象としています。
↓の画像のようにSheet2に祝日データを作成しておきます。（B列にシリアル値が入っている）

Sheet1のA1セルに「西暦年」、C1セルに表示したい「月」の数値を入力するとします。

A3セルの表示形式はユーザー定義から　d　としておき
=IF(MONTH(DATE(A$1,C$1,ROW(A1)))=C$1,DATE(A$1,C$1,ROW(A1)),"")

B3セルは
=TEXT(A3,"aaa")

C3セル
=IFERROR(IF(OR(F$4="",WEEKDAY(A3,2)>5,COUNTIF(Sheet2!B:B,A3)),"",INDEX(H$4:H$10,IF(MOD(MATCH(F$4,H$4:H$10,0)+COUNTIF(C$2:C2,"?*"),COUNTA(H$4:H$10))-1=0,COUNTA(H$4:H$10),MOD(MATCH(F$4,H$4:H$10,0)+COUNTIF(C$2:C2,"?*")-1,COUNTA(H$4:H$10))))),"")

D3セル
=IF(C3="","",INDEX(J$4:J$10,IF(MOD(MATCH(F$7,J$4:J$10,0)+COUNTIF(D$2:D2,"?*"),COUNTA(J$4:J$10))-1=0,COUNTA(J$4:J$10),MOD(MATCH(F$7,J$4:J$10,0)+COUNTIF(D$2:D2,"?*")-1,COUNTA(J$4:J$10)))))

という数式をそれぞれ入れ、A3～D3セルを範囲指定　→　D3セルのフィルハンドルで月末の33行目までフィル＆コピー！

これでF4・F7セルのデータを入れ替えれば
1日目はF4・F7から始まるデータが表示されると思います。m(_ _)m

No.4 回答者： fujillin 回答日時： 2017/11/22 18:12

#3です。

投稿してから気づきました。

G2、H2の値は一度しか参照していないので、D、E列の式に直接組み込んでしまえば、G2、H2の集計欄は不要でしたね。
申し訳ありませんでした。

No.3 回答者： fujillin 回答日時： 2017/11/22 17:58

こんにちは

横からですが、関数派に一票です。

祝日の計算は面倒なので、少し変更させていただいていますが、以下の仮定と変更を行っています。
・B列の曜日はDate値を書式aaaで曜日表示している仮定
・C列に祝日の欄を追加し、祝日には祝印等を入力すると設定
・G、H列の担当、パターンは上詰めで記入されている
　（とりあえず、30人、30パターンまでとしています）
・土日祝日を除いた日に、担当者、パターンともリストの初めから順に繰り返すものと仮定
（前月の続きなどで途中からスタートする場合は、式中に初期値の項を加えることで対応可能ですね）
※ 担当者、パターンは同じ数でなくても対応可能なように考えています。


◇作成方法
1）C列に祝日の欄を追加（C列でなくても良いですが、とりあえずC列としました）
　（空白以外であれば祝日と判定します）
2）G列に担当者リスト、H列にパターンリストとし3行目から上詰めで入力
3）G2に以下の式をいれ、H2にフィルコピー
　=COUNTIF(G3:G33,"<>")
　（それぞれの数をカウント。とりあえず30種類まで）
4）D2に以下の式を入れ、E列および下方にフィルコピー
=IF(AND(WEEKDAY($B2,2)<6, $C2=""),INDEX(G$3:G$33,MOD(SUMPRODUCT((WEEKDAY(A$2:$B2,2)<6)*1,(B$2:$C2="")*1)-1,G$2)+1),"")


※ 曜日の赤色表示や枠線の表示などを自動化なさりたい場合は、条件付き書式を用いれば可能と思います。
※ 祝日判定を別途関数化なさっていたりするのなら、それを利用してC列に空白以外の表示となるようにすれば、そのまま利用可能だと思います。

この回答へのお礼

投稿ありがとうございます。
ご丁寧に関数式をご入力ありがとうございました。
祝の欄をあえていれるということで大変勉強になりました。
関数も分かり安かったのでなんとなく解読できました。
こちらのパターンも勉強し実用させて頂きます。
今回はベストアンサーをつけず大変恐縮ですが
またぜひご指導お願いします。

お礼日時：2017/11/24 11:02

No.2 回答者： yokomaya 回答日時： 2017/11/22 16:28

場所了解しました。

祝日をどう扱うかのルールが必要ですが、その前に

まずこの表、年月がありませんけど年月が決まれば
曜日は決まります。例えばa1に年、b1に月を入力するとすれば
a3の式は=DATE(a1,b1,1)
A4は＝a3+1
b3の式は=a1
表示形式を”aaa"
A4とB3を下方向にオートフィルすれば年月だけ入力すれば
曜日が自動的に変わります。
この事が曜日を得るために必要となるので
作業列で
=WEEKDAY(A3+1)>2
これで平日にのみTRUEが出るので
それをカウントして３で割ればという事です。
上記式にMATCHで祝日を織り込む事も
考えられます。

この回答へのお礼

再度書き込みありがとうございます。
曜日日付、曜日については関数を使い解決しておりまして
問題は、担当者とパターンを自動的に返すことで
やはり教えて頂いた様に、作業シートしかないのかなとただいま行っております。
ただ利用する人が初心者のため（私以外の人間）その作業をできるかというと無理があり、マクロのボタンまたは、当初の説明のようにセルにスタートさせる名前とパターンをF4・F7のみ入力で自動的に入るように
に簡易的にならないかと思っています。

2つを併せ、作業シートの工程をマクロにしてみようかなと苦戦中です。

お礼日時：2017/11/22 16:56

No.1 回答者： yokomaya 回答日時： 2017/11/22 14:31

僕なら関数ですね。

土日を除いた日数を
作業列で出してそれを
３人なら３で割った余りを用います。
問題はお休みの１６日が何でしょう？
祝日ないしは定休日ですか？

また人毎にパターンが異なるシフトってどんなものか
この問題には直接関係ないけど聞いてみたいです。

この回答へのお礼

回答者様ありがとうございました。
16日は祝日です。

固定の3人でABCと決まった場所をローテーションで掃除するというの意味のシフト表です。

お礼日時：2017/11/22 15:24