エクセル2010でカレンダー形式の配置表を作成すると連動して勤務表が作成されるようにと考えています。

カレンダー形式の配置表を作成すると連動して勤務表が作成されるようにと考えています。
理想としては添付画像の赤枠だけを編集すれば、別シートの各々の勤務表が連動して作成されるようにしたいです。色々調べながら作成し、毎月カレンダー形式の1週目1日のセルの位置が変わることによる勤務表の連動はできたのですが、２週目以降の連動がどうすればいいのかわからなくなりました。(見えにくいですが、IF、INDEX、MATCHを使用してみました)

併せて、配置表のカレンダー形式で5週目までの表記にしたいので、その月が6週目になった場合は1日の週の頭に残りの日にちがくるようにしたいです。例えば、2019/6の様に30日が6週目になった場合は1日の週の日曜に表示させたいのですがどうすれば良いでしょうか。(添付画像のように)

カレンダーは配置表の月を変更すれば、配置表・勤務表の日付けが自動で変わるようにしています。
各々の勤務表のシートは添付画像のように分けたままにしたいです。(残業時間などを自分で記入するため)

ややこしい質問で申し訳ありませんが、どなたか知恵をお貸しください。宜しくお願いします。

質問者からの補足コメント

• 添付画像見えにくいため補足しました。

  
    補足日時：2019/05/13 00:05

• 添付画像見えにくいため補足しました。

  
    補足日時：2019/05/13 00:05

• セルD5には　=IF(INDEX(配置表!$C$6:$I$11,1,MATCH('勤務表（Aさん）'!B5,配置表!$C$5:$I$5))="休日","/",IF(INDEX(配置表!$C$6:$I$11,1,MATCH('勤務表（Aさん）'!B5,配置表!$C$5:$I$5))="有休","有休","A"))　の式を入力しました。
  配置表を記入すれば、勤務表の勤務区分のD5～D20、J5～J19までが連動して入力されるようにしたいです。
  しかし、上記の式ではカレンダー形式の配置表で2週目以降になると、添付画像の勤務表5/5（配置表では2週目）からできなくなり、式がわかりません。
  
  言葉足らずで申し訳ございませんが、宜しくお願いします。

  
    補足日時：2019/05/13 14:27

• 6月の勤務表です。　○,×,△,当直＝A勤務、休日＝/、有休＝有休（すいません、A勤務の当直を追加
  しました）理想ではAさんの勤務表の勤務区分が
  
  1土　A
  2日　/
  3月　A
  4火　/
  5水　A
  
  になってほしいのですが、上記の式では2週目（6/2の日曜以降）からできなくなりました。

  
    補足日時：2019/05/13 15:40

• 回答ありがとうございます。提示して下さった式で解決いたしました。
  理解できていない関数が多数あり、勉強不足を痛感いたしました。
  レイアウトの件なのですが、元々は会社の既存の書式があり月末に勤務表を各々で手入力して提出していたものを自動でどうにかできないものかと考え作成するようになりました。なので、恐縮ですが出来ればこのレイアウトのまま作成したいと考えています。
  
  あとご提案いただきました作業シートのことで、もしお時間ございましたら回答お願いしたいのですが…
  添付画像の作業シートを別シートで作成いたしました。このような作業シートの場合、どうすれば良いでしょうか。作業シートの年日は配置表と連動し、日付は日付の数値です。

  
  No.4の回答に寄せられた補足コメントです。 補足日時：2019/05/14 12:00

No.4 ベストアンサー 回答者： fujillin 回答日時： 2019/05/13 17:24

No1です

補足を眺めて、なんとなくなさりたいことはわかってきましたが・・・

基本的には単純な検索・参照の処理のはずの内容なのに、なぜ難しくなっているかと言えば、大きな原因は表のレイアウトにあります。
多分、日にちに対して縦1列（または横1行）で増加するような表形式にしておけば関数式を作成するのもさほど難しくないものと思います。

ご質問のケースでは、見やすいように好きなレイアウトを作成しておいて、これを関数で対応させようとして、わけがわからなくなっているのではないかと見受けられます。

ご提示のような表形式で作業を行うにしても、基本的には1列（又は1行）レイアウトの表を「作業シート（実は元データ）」としえ作成しておき、それを基本にして、使いやすいレイアウトの表から参照するようにすれば、大分簡単になるはずと思います。
この「作業シート」は直接見ることはないでしょうから、レイアウトの換算が難しいような場合は、作業シート上に対象シートのレイアウトでの行番号や列番号を計算する作業列等を作成しておけば、レイアウトの換算は簡単にできるようになるものと想像します。

それを一発でレイアウト変換までこなそうとするので、わけがわからなくなりますし、仮にできたとしても関数式は長くなり、メンテナンス性も悪く、ほとんど理解のできないものになってしまいます。

などと、御託ばかり並べていても仕方がないので・・・
当方がきちんと内容を理解できているのかどうかはわかりませんが、勤務表のD5セルに

=IFERROR(HLOOKUP(VLOOKUP("Aさん",OFFSET(配置表!$B$1,MOD(INT((WEEKDAY(DATE(配置表!$C$3,配置表!$E$3,0))+B5-1)/7),5)*7+5,0,5,8),MOD(WEEKDAY(DATE(配置表!$C$3,配置表!$E$3,0))+B5-1,7)+2,0),{"○","×","△","当直","休日","有休";"A勤務","A勤務","A勤務","A勤務","/","有休"},2,0),"")

を入れて、コピペするとできそうな気がしますが……？

※ 上式は、配置表のB列から"Aさん"と一致する行を検索していますので、名前部分を実際の表に存在する名前に入れ替えておく必要があります。

No.3 回答者： mike_g 回答日時： 2019/05/13 15:04

補足日時：2019/05/13 14:27 に示された、D5 に入力したと豪語された式

=IF(INDEX(配置表!$C$6:$I$11,1,MATCH('勤務表（Aさん）'!B5,配置表!$C$5:$I$5))="休日","/",IF(INDEX(配置表!$C$6:$I$11,1,MATCH('勤務表（Aさん）'!B5,配置表!$C$5:$I$5))="有休","有休","A"))

ですが、前に示されていた配置表の $C$5:$I$5 の何処にも 休日、有休 等々は見当たらないので、私には貴方が何をやっているのかサッパリポンです。
お手数を掛けしましたが、此れ以上ご迷惑をお掛けするのは忍びないので、此処で失礼いたします。サヨナラ。

この回答へのお礼

すいません、折角確認していただけたのに月の違う添付画像を載せてしまいました。
一応、6月の勤務表を添付画像で補足させていただきます。
こちらこそ、お手数をお掛けしまして申し訳ございませんでした。

お礼日時：2019/05/13 15:26

No.2 回答者： mike_g 回答日時： 2019/05/13 10:54

補足日時：2019/05/13 00:05 の添付図に示された

セル D5 の数式、老眼の私のために、もう一度「補足]で
今度はテキストで書き写してください。貴方の添付図では読み取れません！
貴方自身はどう思いますか？

それから、
》 毎月カレンダー形式の1週目1日のセルの位置が変わることによる勤務表の連動はできた
とのことだけど、参考までに「配置表」の範囲 C5:I5 に入力された式を教えてください。

この回答へのお礼

ご指摘ありがとうございます。
添付画像では見にくいと思います。補足を追加しましたので確認していただけると有難いです。

配置表のC5:I5 に式は入力していません。入力規制で○,×,△,休日,○+夜,当直,有休が入力できるようにしています。
配置表で○,×,△,休日,○+夜,当直,有休を入力すると、勤務表の勤務区分が○,×,△,＝A勤務、休日＝/、有休＝有休で入力されるようにしたいからです。

お礼日時：2019/05/13 14:39

No.1 回答者： fujillin 回答日時： 2019/05/13 10:53

こんにちは

メインのご質問については、図が見えない上になさりたいことの内容もまったく把握できませんでした。
その代わりと言ってはなんですが、把握することができた
、
＞その月が6週目になった場合は1日の週の頭に残りの日にちがくるようにしたい
という方だけに回答させていただきます。


C3セルに年、E3セルに月がそれぞれ数値（＝シリアル値ではない）で入力されていると仮定しています。
日付表示のセル値がシリアル値が日付の数値のみなのか不明ですけれど、上記仮定に合わせて、ひとまず「日付の数値」と考えました。
ご提示の表のC5セルに

=IF(AND(WEEKDAY(DATE($C$3,$E$3,1))<COLUMN(A1)+ROW(A1),DATE($C$3,$E$3,COLUMN(A1)+ROW(A1)-WEEKDAY(DATE($C$3,$E$3,1)))<DATE($C$3,$E$3+1,1)),COLUMN(A1)+ROW(A1)-WEEKDAY(DATE($C$3,$E$3,1)),IF(DATE($C$3,$E$3,COLUMN(A36)+ROW(A36)-WEEKDAY(DATE($C$3,$E$3,1)))<DATE($C$3,$E$3+1,1),COLUMN(A36)+ROW(A36)-WEEKDAY(DATE($C$3,$E$3,1)),""))

の式を入れ、右方にI5セルまでフィルコピーし、さらに1週分をコピーして7行おきにペーストします。
もう少し簡単な式にできそうな気もしますが、すぐには思いつきませんでしたので…

この回答へのお礼

カレンダーの日付の件、解決しました。回答ありがとうございます。
fujillinさんの仮定された通り日付の数値で作成しておりましたので、すぐに解決することができました。

お礼日時：2019/05/13 15:11