続：excel日付データからカレンダーを作成・色塗

質問

先日下記で回答をいただきました。
http://okwave.jp/qa/q8793806.html

ベストアンサーの方のsheet2でできたカレンダーを並べて表示⇒
一枚の紙に６～８名分ぐらいのカレンダーを表示させて印刷して見たい場合、
どの様にすればよいでしょうか？
生徒は５０名以上おります。

単純に上記URLのカレンダーを、出来上がったカレンダーの数列横や、数列下にコピペすると
正しいカレンダー（日付）や出席マークが表示されず困っています…。

回答No.2

tom04 · ベストアンサー

No.1です。

＞例えば一番上に１行挿入したい場合・・・
すなわち前回アップした画像の1行目が2行目に移動した場合と解釈すれば良い訳ですね？

というコトは前回のSheet1の1行目を挿入すると
Sheet2のA2セルは
=Sheet1!B2
C2セルは
=MONTH(A2)
E2セルは
=A2-DAY(A2)+1
となっていると思います（相対参照にしているため）
これに関しては手を加える必要はないと思います。

ただカレンダーの数式のA2部分がA3と1行ずれるためにデタラメな表示になります。
最初の「日」（A5セル？もしくはA6セル？）の数式を
=IF(MONTH($E$2-WEEKDAY($E$2)+COLUMN(A1)+7*(ROW(A2)/2-1))=$C$2,$E$2-WEEKDAY($E$2)+COLUMN(A1)+7*(ROW(A2)/2-1),"")
のようにしてみてください。

※　前回の画像の「Sep」となっているセルを参照し、Row関数だけ気を付けます。
下にずれれば相対参照のRow関数も変化しますので、
Row(A3)/2-1
となっているところを
Row(A2)/2-1
のように変更してやる必要があります。
ただ、Column関数に関してはそのままA2となっていても問題ありません。
（相対参照の場合、行挿入するとRow関数には影響しますが、Column関数は影響しません。）

「出」の数式も「検索条件」を「氏名」を入力するセル（絶対参照）
に変更する必要があると思います。

今度はどうでしょうか？m(_ _)m

お礼

tom04さま

本当にありがとうございます！
非常に、わかりやすいご説明で、すんなりと変更することができました!!

このカレンダーから、そこまで手間でもないため手作業でもいいのですが、可能であればやりたい事があり
画像を添付したいため質問板を新たに作りました。
お時間あればご一読いただけないでしょうか…
可不可の検討だけでも結構です、よろしくお願いいたします。
http://oshiete.goo.ne.jp/qa/8799172.html
（画像が小さくなってしまいました…見にくくて申し訳ありません）

よろしくお願いいたします。

回答No.1

tom04

こんばんは！
前回回答した者です。

＞一枚の紙に６～８名分ぐらいのカレンダーを表示させて印刷して見たい場合・・・
というコトですので、↓の画像の右側Sheet（Sheet2）のような表が3～4段作成するとします。
今回はSheet1の作業列は、なしにしてみました。
複合参照等でそのままコピー＆ペーストできないかやってみたのですが、難しそうなので
各カレンダーごとの数式に手を加えもう一度フィル＆コピーしなければなりませんが、
一例です。
Sheet2の配置も変えてみました。
画像のように各カレンダーの上に表示したい「氏名」を入力するようにしています。

おそらく1ヵ月毎のカレンダーになると思いますので、
Sheet2のA1セル（セルの表示形式はユーザー定義から　yyyy　としておきます）に
=IF(Sheet1!B2="","",Sheet1!B2)
C1セルに
=MONTH(A1)
E1セル（セルの表示形式はユーザー定義から　mmm　としています）に
=A1-DAY(A1)+1
という数式を入れておきます。
ここまでは前回同様です。

そしてA5セル（セルの表示形式はユーザー定義から　d　としておく）に
=IF(MONTH($E$1-WEEKDAY($E$1)+COLUMN(A1)+7*(ROW(A2)/2-1))=$C$1,$E$1-WEEKDAY($E$1)+COLUMN(A1)+7*(ROW(A2)/2-1),"")
A6セルに
=IF(COUNTIFS(Sheet1!$A:$A,$B$3,Sheet1!$B:$B,A5),"出","")
という数式を入れA5・A6セルを範囲指定　→　A6セルのフィルハンドルで「土」の列までコピー
そのまま（2行が選択された状態で）下へ2行ずつ6週分コピー！
（1ヶ月が6週ある月があるため）

これで画像では「●●」さんのカレンダーが作成できます。
ここからちょっと手間がかかります。
A3～G16セルを範囲指定　→　右クリック　→　コピー　→　I3セルに貼り付け
この状態ではめちゃくちゃな表示になりますので、
A5セルを選択　→　数式バーの数式をドラッグ　→　右クリック　→　コピー　→　Escキーを押下　→　I5セルを選択　→　貼り付け　→　Enter
I6セルが選択されていると思いますので、数式バー内の数式の
＞=IF(COUNTIFS(Sheet1!$A:$A,$B$3,Sheet1!$B:$B,I5),"出","")
の「$B$3」を編集し直して
＞=IF(COUNTIFS(Sheet1!$A:$A,$J$3,Sheet1!$B:$B,I5),"出","")
のようにカレンダー上の「検索したい氏名」のセル番地｛（絶対参照）←画像ではJ3セルになります。｝にします。
この二つのセルを2行ずつもう一度フィル＆コピーします。

同様にA3～G16セルを範囲指定　→　コピー　→　A18セルに貼り付け
ここでもA5セルの数式バー内の数式をドラッグ　→　右クリック　→　コピー　→　Escキー　→　A20セルに貼り付け　→　Enter
A21セルが選択されていて、数式が
＞=IF(COUNTIFS(Sheet1!$A:$A,$B$3,Sheet1!$B:$B,A20),"出","")
となっているところを、「検索したい氏名」のセル番地　B3　を　B18　に訂正し
＞=IF(COUNTIFS(Sheet1!$A:$A,$B$18,Sheet1!$B:$B,A20),"出","")
として、これも列・行方向に2行ずつコピーします。

この操作をカレンダーの数だけやる必要があります。
これでSheet2の検索したい氏名だけを入れ替えればその人用のカレンダーが作成されます。

※　今回はSheet2のA1（西暦年）とC1（月）のセルに手を加える必要はありません。
Sheet1のB1セルが変更されれば自動でSheet1の月のカレンダーとなります。

以上、長々と書きましたが結論として
各カレンダーの最初の日曜の日付セルはすべて同じ数式が入り
その下のセルは「検索氏名」のセル番地（絶対参照にする）という手間が必要になります。

参考になりますかね？m(_ _)m

補足

tom04さま

またしてもご回答いただきまして、本当にありがとうございます!!
当方の質問がわかりにくいのに対し、非常にわかりやすく、ご教示いただいた通りにすれば
カレンダーはいくらでも表示することができました。本当に感謝しております。

理解力がなく大変恐縮ですが、例えば一番上に１行挿入したい場合、
このまま行を挿入してしまうとやはり式が崩れカレンダーが日付が狂ってしまうのですが
この場合現在のA5セルの式をどのように変更する必要がありますか？

今うまくデータが出ているのでこのまま使えばお手間もかけず済むのですが、
どうしても1行下にずらす必要が出てきまして…ヘッダーではなくデータ内に表示したいのです。
こちらもお知恵をお貸しいただければ幸いです。よろしくお願いいたします。。。