Excel2007 曜日によって別のセルの色を変え

質問

いつもお世話になり有難うございます。

昨日質問させていただきましたが、質問の仕方が下手糞なため、もう一度よく整理して再質問させていただきます。

なお、前回忙しい中回答していただいた皆様には、私の頭がアホなためこのようなことになり申し訳ありません。何卒お許しください。

昨日（http://okwave.jp/qa/q8082698.html?by=datetime&order=ASC）にて皆様に回答いただき、自分なりに頑張ってみたのですが、
画像にある通りA列に日付、B列に氏名、D9:AE9は時間で、その下にそれぞれ働く時間の箇所に色を塗っています。（赤が勤務、黄が休憩）
シフトを組む際に、曜日によって営業時間が異なるのですが、営業する時間帯をD9:AE9の箇所に色を塗りたいのです。（画像では10時から21時までが営業）
（営業時間は月曜～土曜が10時から21時、日曜祝日が10時から20時、祝前日が10時から21時）

管理用シートには別表で2013/1/1～2013/12/31までの表があり、「営業時間判定」という列に月曜～土曜と祝前日の10時から21時までの営業の日には「1」、日曜祝日の10時から20時までの営業の日に「0」と入力した表を作成しました。

具体的に頭の中で考えているイメージですが、
●A9の日付と管理用シートにある別表の日付を紐付けし、
●管理用シートの別表にある「営業時間判定」が「1」の場合は画像のF9:AA9のセルに色を付け、
●同じく「0」だった場合はF9:Y9のセルに色を付ける
という手順が簡単ではないかと思うのですが、このような場合はどのようにすればいいのでしょうか？

恐れ入りますがよろしくお願いいたします。

回答No.4

MarcoRossiItaly · ベストアンサー

言い忘れましたが、INDIRECT 関数が読み取っている「管理用シート!$A2:$B366」という文字列で特定されるセル範囲に「名前」というものを登録し、その名前で条件式を書くという方法もあります。この方法のメリットは、短い日本語などの言葉と簡単な書式で数式が書け、管理しやすくなる上、行列番号のミスによる参照エラーもしにくくなることです。設定の方法は、前回のご質問で皆さんが回答されているとおりです。

＞……マクロ（？）を使用して選択した場所にこの雛形の表を貼り付けするようにしています。

このご質問で作られた表を、ということでしょうか？

そうであれば、マクロのコードにおいて貼り付け方法がどうなっているかにもよりますが、表のセル範囲のプロパティ全てを丸ごとコピペしているのだとすれば、マクロの実行前に No.3 の条件式をちょっとだけ書き換えておくことによって、実行後にコピー先でも意図したとおりに条件付き書式が設定されているようにできるかもしれませんね。

AB9
　=vlookup(A9,indirect("管理用シート!$A2:$B366"),2,)　　……最初の「$」を削除

このセルをコピーして AC9 に貼り付け後、AC9 の 21（時）をクリア。条件付き書式の「ルールの管理」で見ると、AC9 には次式が適用されています。

　=vlookup(B9,indirect("管理用シート!$A2:$B366"),2,)

これが AB9 での式と同一のものになるよう修正。つまり「B9」→「A9」と書き換え。その後、マクロによるコピーを実行します。丸ごとのコピーなら、これでうまく行くかと。

あるいは、元の表の条件は修正せずに、マクロによる貼り付けの後で手動で修正するという方針なら、次のような。例えば、AB100 セルに表を貼り付けると、日付も AB100 に入力されている状況になるが、条件式の VLOOKUP 関数は「$」の効果で A100 を参照しています。それを条件付き書式の「ルールの管理」にて、次式に修正。

AB9:AC9
　=vlookup($AB100,indirect("管理用シート!$A2:$B366"),2,)

上の 2 案のほか、マクロでコピーする際に条件付き書式を併せて設定し直すことももちろん可能ですが、具体的なコードを見ないと有益なアドバイスは難しいです。それについて質問される場合、このご質問は締め切って、新たなご質問としてコードとともに投稿されるようしてください。

この言っている意味も分からないという場合は、まずは Excel のマクロがどんなものか、検索などでさわりだけでもお調べになることをお勧めします。

お礼

教えていただいた通りにやってうまくいくことができました。
マクロは今回初めてやったので、まだまだわからないことだらけですが、今回教えていただいたことも含めて自分の身になればと思います。

このたびは有難うございました。丁寧な回答に深く感謝します。

回答No.5

keithin

＞日曜が祝前日だった場合はうまくいかないのではないかと思い

日曜と祝前日のどっちが優先なのか、ご相談では何も説明が無いことに留意してください。
余談ですが振替休日の扱いとかも同様ですね。

勿論そういう「説明を忘れていた」細かいことを、ご自分で考えて仕上げていくのがあなたの仕事です。回答者は、別にパーフェクトにアナタのヤリタイを解決するのが仕事じゃありませんから。

＞別表を作成して再質問させていただきました。

今回実現したように、別表で１と０を記録して条件付き書式に反映するのは、簡単な良い方法です。
次のステップとして言えば、そうやってご自分の「正しい１の条件」や「０の条件」のそれぞれの数式を構築できれば、それを（その考え方／ロジックを）直接条件付き書式の条件として利用する事も出来るようになります。

＃余談ついでに
＞自分自身だいぶ頭の中を整理できてきたので、なんとなく理解できました

なるほど。それでご質問に何も回答していない回答が、一番役に立ったワケですね。回答する側からすれば随分とやる気を無くさせる結果だったので、こちらのご相談もスルーしようかなぁと思いましたけど。

お礼

「＃余談ついでに」について、こういう言い方は良くないかもしれませんが、いただいた回答の最初の部分も含め、まるで質問者が「悪」であるかのような言い方はどうかと思います。
回答者様のようにこのサイトの常連の方はごくわずかで、ほとんどの方がたまにしか利用していないと思います。これでは質問する気も無くなってしまうと思います。
私も問題を解決したくて質問させていただきましたが、こんなことならやる気が無くなった時点で回答していただかなくても結構です。
「回答者は、別にパーフェクトにアナタのヤリタイを解決するのが仕事じゃありませんから。」こんなことは誰だって承知しています。

ですが質問した側として、私のスキルがないために、下手な説明になってしまい申し訳ありませんでした。
Excelの考え方などもわかり、正直言って質問+αの収穫もありました。
ただ、いただいた回答の中にある「次のステップ」には私はまだまだですが。

今後もお世話になる機会があるかもしれませんが、その時は何卒お手柔らかにお願いいたします。この度は有難うございました。

回答No.3

MarcoRossiItaly

ああ、たいへん失礼しました。No.2 ですが、No.1 さんのおっしゃるとおりです。INDIRECT 関数を使えば条件付き書式でも、他シートへの参照ができたのでした。

その場合、No.2 の 4. の式は、次のとおりになりますね。

=vlookup($A9,indirect("管理用シート!$A2:$B366"),2,)=1
あるいは
=vlookup($A9,indirect("管理用シート!$A2:$B366"),2,)

※「=0」だと必ず付けないといけませんが、0 以外の数の場合は、なくても大丈夫です。

間違えてしまったので、ベストアンサーは辞退します。

補足

回答有難うございます。
No.1さんとほぼ同じですが、やってみたところとりあえずその表に関してはうまくいきました。
ただ、http://okwave.jp/qa/q8063467.htmlで質問していますが別のシート（管理用シート）にシフト表の雛形を作成し、さらに月ごとにシートを作成し、マクロ（？）を使用して選択した場所にこの雛形の表を貼り付けするようにしています。この場合、貼り付けた表では思うように行かないのですが、この場合はどうすればよろしいのでしょうか？

回答No.2

MarcoRossiItaly

前回のご質問に回答した者です。「管理用シート」というのができ上がったのですね。良かったです。

なるほど 10 行目以下ではなく、項目の並んでいる 9 行目を青く塗るのですね。今、気付きました。

それには条件付き書式を使いますが、他シートへの参照をしようとすると、ダイアログの終了時に、それはできないとのエラーメッセージが出ます。「管理用シート」の表をシフト表と同じシート上にコピペし、それを参照してください。

1.
F9:AA9 のセル範囲を選択。

2.
普通のセルの書式として、好きな色を塗る。

3.
AB9:AC9 のセル範囲を選択。（← F 列からではないので注意）

4.
ホームタブ「条件付き書式＞新しいルール＞数式を使用して…」とクリック。ボックスに次の条件式を入力。

=vlookup($A9,$AG2:$AH366,2,)=1

5.
同じダイアログ内の「書式」ボタンから、好きな色を塗る。

回答No.1

keithin

昨日も回答した通りですが。

あなたがヤリタイこと
＞「1」の場合は画像のF9:AA9のセルに色を付け、
＞同じく「0」だった場合はF9:Y9のセルに色を付ける

Ｆ９からＹ９は、１だろうが０だろうが関係なく色が付くので、そのまま色を塗ってしまって構いません。
Ｚ９とＡＡ９は、１の時にだけ色が付くので、昨日皆さんが教えてくれた通りに条件付き書式で色を付けます。

手順：
管理用シートの具体的なレイアウトが説明されていないので、Ａ列に日付、Ｂ列に１かゼロを記入する事にします

Ｚ９とＡＡ９を選ぶ
条件付き書式で新しいルールを開始する
数式を使用して…を選び
=VLOOKUP($A$9,INDIRECT("管理用シート!$A:$B"),2,FALSE)
と記入、書式ボタンでセルの色を塗らせる。

補足

有難うございます。
自分自身だいぶ頭の中を整理できてきたので、なんとなく理解できました。(前日の回答）
ただ、日曜が祝前日だった場合はうまくいかないのではないかと思い、別表を作成して再質問させていただきました。
まずはご支持通りやってみます。