エクセル カレンダーでシフト表を作りたいです

先日、昔の投稿でこのような解答を拝見しました！
すごいと思い作成したのですが、私はシフト担当の名前の上下にもう一行ずつ挿入したいです。
この式で作った表に行を挿入するとエラーになるのですが、どこを変えればうまくいくのかわからないので分かる方に教えていただきたいです。
よろしくお願いいたします！


↓の画像で上側がSheet1で各日のシフトを入力し、下側のSheet2に表示させるとします。

まずSheet1の日付をシリアル値で操作します。
C1セルに西暦年・E1セルに月の数値をそれぞれ入力します。
D2セル（セルの表示形式はユーザー定義から　d　としておく）に
=IF(MONTH(DATE($C1,$E1,COLUMN(A1)))=$E1,DATE($C1,$E1,COLUMN(A1)),"")
という数式

D3セルに
=IF(D2="","",TEXT(D2,"aaa"))
という数式を入れ、D2・D3セルを範囲指定　→　D3セルのフィルハンドルで月末（31日）までの
AH列までコピー！
これでC1・E1セルの数値を入れ替えるだけで横1列のカレンダーができます。

次にSheet2のC1セルに
=DATE(Sheet1!C1,Sheet1!E1,1)
という数式を入れ、セルの表示形式はユーザー定義から
m月
としておきます。（このC1セルは今後手を加えません）
A4セル（セルの表示形式はユーザー定義から　d　としておく）に
=IF(MONTH($C$1-WEEKDAY($C$1)+COLUMN(A1)+7*(ROW(A2)/2-1))=Sheet1!$E$1,$C$1-WEEKDAY($C$1)+COLUMN(A1)+7*(ROW(A2)/2-1),"")

A5セルに
=IF(A4="","",INDEX(Sheet1!$D$4:$AH$100,MATCH($A$1,Sheet1!$C$4:$C$100,0),MATCH(A4,Sheet1!$D$2:$AH$2,0))&"")
という数式を入れ、A4・A5セルを範囲指定　→　A5セルのフィルハンドルで「土」のG列までフィル＆コピー
そのまま（2行が選択されている状態）で下へ2行ずつフィル＆コピー！

これでSheet2のA1セルを入れ替えるだけでSheet1のデータが日付毎に表示されます。

※　このままではセルの左上にエラーチェックオプションのマークが表示され
目障りだと思いますので、
ファイル　→　オプション　→　数式　→　エラーチェックの項目の
「バックグラウンドでエラーチェックを行う」のチェックを外しOK

※　Sheet1のC1・E1を変えるだけでSheet2のカレンダーは連動するようにしています。

こんな感じではどうでしょうか？m(_ _)m

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

• https://oshiete.goo.ne.jp/qa/8982126.html
  
  こちらです！

  No.1の回答に寄せられた補足コメントです。 補足日時：2020/01/12 00:32

• 解答ありがとうございます！
  挑戦してみましたが、エラーが発生して
  行を挿入することで、偶数の週が表示されなくなってしまいました。難しいですね…

  No.2の回答に寄せられた補足コメントです。 補足日時：2020/01/12 00:34

• 回答ありがとうございます！
  こちらは別の方の質問を引用しました！
  私はこれをみて表を作ってみたのですが。
  このカレンダーの日にちの下に表示されているシフトを私は出勤する方の名前で作成しました。
  その名前の上と、下に別の内容も打ち込みたいので行を追加したかったのですが、行を挿入することにより、偶数の週が表示されなくなってしまいます。
  
  理想は日にちが1日だとすると
  
  1日
  空白
  シフトの人の名前
  空白
  
  としたいです

  No.4の回答に寄せられた補足コメントです。 補足日時：2020/01/14 23:17

• その言葉を知りませんでした！
  ちなみにAです！
  Aが最終的に作りたい表です！

  No.5の回答に寄せられた補足コメントです。 補足日時：2020/01/15 20:04

• ありがとうございます！！
  すごく嬉しいです！四行にすることには成功しました！
  この表の日付の下に以前質問している方のようにシフトをだすとしたら式は少し変わりますか？
  前に質問している方と同じ形式でさらに
  今回教えていただいたように行をプラスで二列増やした表を作りたいです。
  知識もないくせに、要望が多く申し訳ありませんが分かれば教えていただきたいです。

  No.6の回答に寄せられた補足コメントです。 補足日時：2020/01/22 21:05

• ここまでは無事に完成しました！！
  本当にありがとうございます！
  もうひとつできればやりたいことがあり、自分で調べたり本を買ったりしてみたのですが分からなかったので聞きたいです。
  もしかしたらそんなことはできないのかもしれませんが、このシフト担当者の名前の下の二列には(早出)だったら(掃除)。(遅出)、(鍵閉め)。などを入力予定です。早出と打てばその下に掃除とでるようにする関数はありますか？
  先生のようにたくさん補足で聞いてしまい申し訳ありません。

  No.7の回答に寄せられた補足コメントです。 補足日時：2020/01/24 23:11

No.1 回答者： mike_g 回答日時： 2020/01/09 22:15

その「昔の投稿」のURLを教えてください。

No.2 回答者： fujillin 回答日時： 2020/01/10 10:54

こんにちは

関数式の内容はちゃんと見てませんが、ざっと見るところ、行、列の位置を計算で求めて参照する位置を決めるような仕組みになっているため、「行の挿入」などの際のエクセルの自動修正が効かない形式になっているものと想像します。

最終的にどのような位置関係になさりたいのかよくわかりませんが、位置を計算する仕組みを理解して、それに合わせた修正をすれば、問題なく対応できるはずです。
とは言え、ここでそれをご質問なさっていることから推測すると、式の意味がわからないか、あるいは、式を理解する気が無いのかどちらかの可能性が高そうなので、計算式はいじらずに、レイアウトを変えても同じような使い勝手にできる別の方法を提案しておくことにします。

まず、現状のシートはそのまま（＝オリジナルシートとします）にしておきます。
1）質問者様が求めるシートの体裁になるように、新しいシート（＝新シート）の各セルにオリジナルシートへの参照を設定します。（これで、見た目は目的の形になるはずです）
2）年月のセル（あるいは担当者名など）は新シート内で指定したいでしょうから、これらのセルだけは新シートを生かして、オリジナルシート側が新シートを参照するように変更します。（他にも同様の内容があれば同じように設定します）
3）以上で、新シート上にレイアウト・使い勝手ともに、お望みの通りのシートができるはずと思います。

最後に、オリジナルシートを非表示にして出来上がりです。

No.3 回答者： fujillin 回答日時： 2020/01/12 00:56

No2です。

＞行を挿入することで、偶数の週が表示されなくなってしまいました。
＞難しいですね
どうやら、内容を勘違いなさっているようです。

私の回答では「行を挿入する」行為は発生しません。
作りたい結果の形式を直接作成する方法であって、作成してから行を挿入するのではありません。
とは言っても、単純な参照設定だけで構築できれば、行を挿入しても問題ないはずと想像します。
だたし、操作可能なのは新シートのみで、オリジナルシートは操作できないことはかわりありません。

No.4 回答者： mike_g 回答日時： 2020/01/12 11:31

確認させてください。

》 シフト担当の名前の上下にもう一行ずつ挿入したい
貴方が示した「こんな感じ」の表には「担当の名前」が見当たらないけど、ひょっとして範囲 A3:A10 の部分が該当するのでしょうか？
「上下にもう一行ずつ挿入」する部分は、列範囲 C:AH 内の 4、5、6、…、11行目のことでしょうか？つまり、A、B、C、…さんの行範囲を、それぞれ、4:6、7:9、10:12 にすると？名前はそれぞれ 5、8、11行目の真ン中に配置すると？
それから、「こんな感じ」の右下の七曜表は不要なンですか？

No.5 回答者： mike_g 回答日時： 2020/01/15 14:05

》 カレンダーの日にちの下に表示されている

》 シフトを私は出勤する方の名前で作成…
此処で「カレンダー」と仰っているのは、次のドッチかと訊いているンですよッ！
Ａ．添付図“手前”(範囲 A:G)の“七曜表”(この言葉知ってます？)
Ｂ．添付図“向こう側”の横長のもの(C列以右)

No.6 回答者： tom04 回答日時： 2020/01/21 15:04

こんにちは！

横からお邪魔します。
お示しのサイトは当方が投稿したものだと思います。

結局1日当たり4行（日付以外に3行が空白）のカレンダーを作成したい！というコトでしょうかね？

そうであれば、一例です。

↓の画像のような配置とします。
A1セルに西暦年・C1セルに表示したい月の数値をそれぞれ入力するとします。
尚、F1セルは=DATE(A1,C1,1)
という数式を入れその月の初日のシリアル値にしておき、表示形式は「mmm」としています。

A4セル（セルの表示形式はユーザー定義から　d　としています）に
=IF(MONTH($F$1-WEEKDAY($F$1)+COLUMN(A1)+7*(ROW(A4)/4-1))=$C$1,$F$1-WEEKDAY($F$1)+COLUMN(A1)+7*(ROW(A4)/4-1),"")

という数式を入れA4～A7セルを範囲指定　→　A7セルのフィルハンドルで右へコピー１
そのまま（4行が選択されている状態で）下へ4行ずつフィル＆コピー！

これで画像のような感じになります。

※　月によっては6週ある場合がありますので、6週分フィル＆コピーしておいた方が良いかもしれませんね。

※　「シフトの人の名前」に関しては手を付けていません。m(_ _)m

No.7 回答者： tom04 回答日時： 2020/01/22 21:41

No.6です。

＞この表の日付の下に以前質問している方のようにシフトをだすとしたら・・・

結局No.6のようなレイアウトでシリアル値の下の行に「シフト表の氏名」を表示したい！というコトでしょうか？

もちろんできますが、シフト表がどのようになっているか？によって変わってきます。
質問内容をこちらで解釈すると、シフト表には横長のカレンダーとその日のシフト名だけの表なのでしょうかね？
（前回当方が回答したシフト名？の部分に「氏名」が入っている）

↓の画像のような配置にしてみました。
Sheet1がシフト用のシートにしています。（今回は縦型にしてみました）
Sheet1のA4セル（セルの表示形式はユーザー定義から　d　としておく）に
=IF(MONTH(DATE(A$1,C$1,ROW(A1)))=C$1,DATE(A$1,C$1,ROW(A1)),"")

隣りのB4セルに
=TEXT(A4,"aaa")

として、A4・B4セルを範囲指定　→　31日までの34行目までフィル＆コピー！

C列は手入力で「氏名」を入力してください。

そしてSheet2の方は前回のやり方と一緒ですが、
Sheet1のA1セルを
=Sheet1!A1
C1セルを
=Sheet1!C1
としています。
これでSheet1の方の年・月のセルを入力すればSheet2に反映されます。

Sheet2のA4セルは前回の数式、
A5セルに
=IFERROR(VLOOKUP(A4,Sheet1!$A:$C,3,0)&"","")

という数式を入れ前回同様、行ずつのフィル＆コピーをしてみてください。

※　今回はシフト名は一人だけとしましたが、
Sheet1のD・E列を利用すれば複数名を表示できます。m(_ _)m

No.8 ベストアンサー 回答者： tom04 回答日時： 2020/01/25 00:03

No.6・7です。

＞シフト担当者の名前の下の二列には(早出)だったら(掃除)。(遅出)、(鍵閉め)。などを入力予定です。

すなわち前回のアップしたSheet2の方の操作になるのですね。
日付の下の行（3行目）に「早出」とか「遅出」を入力すると4行目に「掃除」や「鍵閉め」などが表示されれば良い！という解釈で・・・

いちいち入力するのも大変でしょうから（数式が入っているセルに入力すると数式が消えてしまう）
↓の画像のように出勤形態によって何の仕事があるか？という表を作成してはどうでしょうか？
そして、直接入力するのではな、入力規則のリストを使ってみてはどうでしょう。

↓の画像で日付の行（A4セル）と「氏名」（A5セル）は前回の数式のままです。

次にA6セルを選択　→　データ　→　データの入力規則　→　入力値の欄で「リスト」を選択し
画像の配置では　I2・I3　を選択してください。
（もちろんI・J列のデータはもっと増えても構いません。データがあるだけをリスト候補に選択します）

A7セルに
=IFERROR(VLOOKUP(A6,$I:$J,2,0),"")

という数式を入れます。

後は今まで通り4行ずつフィル＆コピー！

これでなんとかお望みの形にならにでしょうか。m(_ _)m

この回答へのお礼

本当にありがとうございました！
ここまで理想のものができて、そもそも直接質問したいくらいだったので見つけて教えていただけて感激です。たくさんの方が助けてくださいましたが、最後まで協力してくださったのでベストアンサーとさせていただきます！

お礼日時：2020/01/25 21:47