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

質問

http://okwave.jp/qa/q8796983.html
こちらの質問の続きです。

出席データからカレンダーを作成することができました。
ただ、出席日に「出」マークの表示だけだと、
該当する生徒の「そもそも通うべき曜日」がわからないので、
カレンダーsheetの日～土（A4～G4)に、「そもそも通うべき曜日」を色塗り（カレンダーsheetをパッとみて各生徒の「そもそも通うべき曜日」がわかる方法）したいと考えています。

添付図の、参考.xlsx:1に、データsheetがあります。
通うべき曜日が、L～R列に表示されており、
月＝１、火＝２、水＝３、木＝４、金＝5、土＝６、日＝７　と表記しています。

鈴木さやか　のカレンダーの「水 (D4) 」を色塗り（もしくはほかの方法）したいです。
伊藤裕であれば、「木」「土」に色塗り・・・ということです。

上記のようなことは可能でしょうか？
よろしくお願いいたします。

回答No.3

tom04 · ベストアンサー

続けてお邪魔します。

＞同姓同名の生徒が数名いる事に気づき・・・
実は最初の質問（↓のURL）の時、気になっていたのですが敢えて書かずにいました。

http://okwave.jp/qa/q8793806.html

「名前」と「クラス」で判断したい！というコトであれば↓の画像のように
最初のSheet1も「クラス」の列を作る必要がありますよね。
画像のようにSheet1（←出席日を入力するSheet）を変更し、Sheet2も「土」の上のセルに「クラス」を入力するとします。

一番簡単なのはSheet3に作業用の列を設ける方法だと思います。
画像ではSheet3のT列としています（実際は使用していない列にしてください）
T2セルに
=IF(OR(A2="",H2=""),"",A2&H2)
という数式を入れフィルハンドルでずぃ～～～！っと下へコピーしておきます。

今回は「名前定義」の方法にしてみました。
Sheet3のT列すべてを範囲指定　→　「名前」という名前定義
Sheet3のL～R列すべてを範囲指定　→　「週表」という名前定義
Sheet3のL1～R1セルを範囲指定　→　「曜日」という名前定義
としています。
※　同じ名前で別範囲の名前定義はできませんので、前回別列を「名前」と定義している場合は
メニュー　→　数式　→　名前の管理　→　「名前」という名前を一旦削除して新たに名前定義してください。

そしてSheet2のカレンダーの数式も当然変わってきます。
日付のセルはいままで通りでOK
「出」のセルのA6セルが
=IF(COUNTIFS(Sheet1!$A:$A,$B$3,Sheet1!$B:$B,$G$3,Sheet1!$C:$C,A5),"出","")
となります。
これを2行ずつフィルハンドルでコピー！

次にカレンダーの色を付けたいセルすべてを範囲指定　→　条件付き書式　→　・・・　→　数式欄に
=INDEX(週表,MATCH($B$3&$G$3,名前,0),MATCH(A$4,曜日,0))>0
という数式を入れ　書式から「黄色」を選択しています。
これで画像のような感じになります。

※　このカレンダーを別セルにコピー＆ペーストする場合は
「日付」セルの数式変更はすでに説明している通り
「出」のセルの数式変更は
B3・G3・A5　の三つのセルの番地を変更します。
「条件付き書式」の数式変更は
B3・G3・A4　の三つのセルとなります。
以上コツコツとカレンダーの数だけ手作業で編集し直してみてください。

※　余計なお世話かもしれませんが・・・
大学の学籍番号のように個人用のシリアルナンバー的なものがあれば
同姓同名は一切気にする必要がありませんね。
なおかつ、VLOOKUP関数などでそのナンバーを入力するだけで「名前」「クラス」等の表示が可能になりますので、
わざわざ二つのセルの編集をしなくても良いことになります。
学籍番号的なものがない場合は「クラス名＋出席番号」などで独自で作成しても良いと思います。
すなわち画像のSheet3の作業列的なものを1列作っておき、
Sheet1の「出席用」のSheetにも利用すれば数式そのものがもっと簡単になります。

※　ここまで来ると「祝日」の表示方法とか、「出席すべき日」に出席していない場合セルを別の色付け・・・等々
もっと、あぁ～～～んなコトや・・・こぉ～～～んなコトがやりたい！
という欲求が出てくると思いますが

とりあえずこの程度で・・・m(_ _)m

お礼

tom02さま

いろいろと本当に有難う御座いました。
今回に関しては、全カレンダーの変更が大変そうで（200近く既に作成済みだったため…）実用はあきらめてしまいました。
（簡易データで試したところ、実行する事ができました！ご教示いただき有難う御座いました！）

この度は本当にお世話になりました。
できたデータの利用を続けていくにあたって、また問題が生じた場合は、新しい板で質問させていただきたいと思います。

本当に有難う御座いました！

回答No.2

tom04

No.1です。
たびたびごめんなさい。
投稿後気になったのですが、
アップされている画像を拝見するとおそらくExcel2010だと思われますので
前回の数式で大丈夫だとは思いますが、
Excel2007などの場合は条件付き書式の数式で直接別Sheetは参照できなかったと思います。
（手元にExcel2007がないので検証はできません）

その場合は各々の範囲を「名前定義」して数式を使います。

No.1の画像通りの配置として
まず、Sheet2のA列すべてを範囲指定　→　名前ボックス（数式バーの左側にA1とセル番地が表示されているところ）
のA1を消去　→　仮に　名前　と入力　→　Enter
これでSheet2のA列すべてが「名前」と名前定義されました。
同様にL～R列すべてを範囲指定　→　名前ボックスに仮に　週表　としてEnter
（L～R列が「週表」と名前定義）
最後にL1～R1を範囲指定　→　名前ボックスに　曜日　としてEnter
（L1～R1が「曜日」と名前定義）
このように必要範囲すべてに名前定義しておきます。
（「名前定義」の名前はご自身で判りやすい名前にしてください）

そしてSheet1のカレンダー内の色を付けたいセルを範囲指定　→　条件付き書式　→　・・・中略・・・　→　数式欄に
=INDEX(週表,MATCH($B$3,名前,0),MATCH(A$4,曜日,0))>0
という数式を入れ　→　書式　→　塗りつぶしから好みの色を選択しOK

これで前回と同様の結果になるはずです。

※　Excel2010でもこのようにすればSheet名を気にする必要はなく、そのままの数式で大丈夫です。

数式内のB3とA4の部分だけは前回の説明どおり、カレンダーの配置ごとに編集し直します。
何度も失礼しました。m(_ _)m

補足

tom04さま

お礼が遅くなり大変申し訳ありません。
アドバイスいただいた通りに試したところ、思うように色塗りをすることができました！
本当に有難う御座います…。

生徒数がかなり多く昨夜コツコツとカレンダー数をこなしていた所で、
同姓同名の生徒が数名いる事に気づき、苦戦している所です。

カレンダー上の、「土」の上に、生徒のクラス名（A1等です）を入力しておき、名前とクラスが一致しないと色塗りされないようにする関数はどうなりますでしょうか。

今のところ同姓同名・クラスも同じというパターンがでてきていないので、上記ができれば解決できると推測しますが、最後までやってみないと他に問題が出てくるかわからない状況です…。

回答No.1

tom04

こんにちは！

↓の画像のように「そもそも通うべき曜日」がSheet2の月～日のL～R列に数値が入っているとします。
＞月＝１、火＝２、水＝３、木＝４、金＝5、土＝６、日＝７　と表記しています。
とありますが、実際は何らかの数値が入っていれば対応できるようにしてみました。

↓の画像でSheet1（カレンダーSheet）の
A4～G4を範囲指定　→　条件付き書式　→　新しいルール　→　数式を使用して　→　数式欄に
=INDEX(Sheet2!$L:$R,MATCH($B$3,Sheet2!$A:$A,0),MATCH(A4,Sheet2!$L$1:$R$1,0))>0
とし　→　書式　→　塗りつぶし　→　「黄色」を選択しています。

※　画像はカレンダーの列すべてに色がついていますが、
日～土のカレンダーすべてを範囲指定　→　条件付き書式　→　・・・中略・・・　→　数式欄に
=INDEX(Sheet2!$L:$R,MATCH($B$3,Sheet2!$A:$A,0),MATCH(A$4,Sheet2!$L$1:$R$1,0))>0
という数式をいれ　→　塗りつぶしから「黄色」を選択しています。
（色は好みの色にしてください）

二つの数式で　A4セル　の部分の「$」マークがあるか、ないかでこのように変えるコトができます。
質問では曜日だけの行のようですので、曜日の4行目の７列だけを範囲指定し、
どちらの数式でも大丈夫です。（範囲指定したセルのみ条件付き書式が設定されますので・・・）
（前者の数式ではカレンダーの行すべてに色を付けるという訳にはいきません。）

1Sheetにカレンダーが複数並んである場合は
条件付き書式の数式内の検索氏名のセル（B3セルの部分）と
各カレンダーの「日」が入っているセル（A4セル）の二つのセル番地だけを変える必要があります。

※　Sheet2の部分は実際のSheet名にしてください。m(_ _)m

補足

情報が不足していました。こちらの補足を正としてください。

(1)カレンダーsheetのG2(土の2セル上）にクラス名を表示しています。
(2)データsheetのH列にクラス名を入力しています。

カレンダーsheet上の　生徒氏名　と　土の2セル上にある　クラス名　がデータsheetのA列の姓名と、H列のクラス名が一致すれば、「そもそも通わなければならない曜日」に色塗り、をしたいです。

宜しくお願い致します。