[[20130626103005]]　『セルに自動で色をつける日程表』（柏木）　ページの最後に飛ぶ

『セルに自動で色をつける日程表』（柏木）

 エクセルは2007です。

 数式と条件付き書式で受付日と納期日を入力するとセルが自動で塗られる日程表を途中までネットに知識を借りて作っていたのですが、詰まりました。

 A1に西暦年、B1に月入力しています。そしてこの式はJ5に入力されています。
 =IF(MONTH(DATE($A$1,$B$1,COLUMN(A1)))=$B$1,DATE($A$1,$B$1,COLUMN(A1)),"")

 曜日が自動で出るようにした計算式です。これはJ6に入力されています。
 (実は曜日はいらないのだが消していいのか分からない・・・)
 =IF(J5="","",TEXT(J5,"aaa"))

 上記二つはオートフィルで右にひと月分コピーしてあります。

 H8に受付日、I8に納期日を入力しています。
 =OR(AND(J$5<>"",J$5=$H8),AND(J$5<>"",J$5=$I8))

 受付日の次の日を入力しています。
 (私は受付日と納期日の間を塗りたいだけなのだがやり方が分からない)
 =AND(J$5<>"",J$5>=$G8,J$5<=$I8)

 上記二つは条件付き書式で指定しています。

 これを同じシート内に４カ月分連続で表示したいです。

 ＿＿＿｜＿＿＿｜＿６月＿｜＿７月＿｜＿８月＿｜＿９月＿｜
 受付日｜納期日｜ここのセルが塗られる＿＿＿＿＿＿＿＿＿｜
 ＿〃＿｜＿〃＿｜どんどん続いていく＿＿＿＿＿＿＿＿＿＿｜
 ＿＿＿｜＿＿＿｜＿＿＿＿＿＿＿＿

 他に足りないところがあればご指摘願います。
 宜しくお願いします。

 んー良くわかってないけど
 受付日から納期日のセルが塗りつぶされたい
 ってことかな？
 J8から塗りつぶしたい最終セルまでを範囲選択して、条件付き書式に
 =AND(J$5>=$H8,J$5<=$I8)
 でいかがでしょう？
 違うかな？
 （稲葉）

 ちょっと衝突気味ですが。。。

 現状がどうなっているのかは良く分かりましたが
 ＞詰まりました。
 のどの部分が詰まっているのかが、よくわからないです。

 ＞=OR(AND(J$5<>"",J$5=$H8),AND(J$5<>"",J$5=$I8))
 これは、どのセルの条件付き書式の式ですか？

 ＞受付日の次の日を入力しています。
 G8セルに次の日を入力して、その下の数式を条件付き書式で使ってるのですか？

 G列に日付を入れずに、受付日の次の日から色を付けたい　と言う事なら
 =AND(J$5<>"",J$5>=$G8,J$5<=$I8)
 　              ↓
 =AND(J$5<>"",J$5>$H8,J$5<=$I8)
 の変更で良いと思いますが。。。

 具体的に、
 　H8が○月○日　I8が△月△日になっているとき
   ◆セル～◎セルに色を付けたいが
 　今の設定だと×××になる。
 と書いてもらえると、伝わりやすいかもしれません。

 カレンダー表示は
 J5=IF(J5="","",IF(DAY(J5)=1,TEXT(J5,"m月"),""))
 J6=IF(TEXT(DATE($A$1,$B$1,COLUMN(A1)),"yyyymm")<=TEXT(DATE($A$1,$B$1+3,1),"yyyymm"),DATE($A$1,$B$1,COLUMN(A1)),"")
 をオートフィルで右に4カ月分コピー。

 曜日は、特に使ってないので　柏木さんが不要なら
 無くても問題ないと思います。

 (HANA)

 >実は曜日はいらないのだが
 一般に"何月何日何曜日"といいますが、月日がわかれば曜日も決まるので冗長なように思われます。
 しかしこれは、日付を確実に伝達するためであり、また個人の予定においてはウィークデイと土日では違ってきますし、会社の業務でも週初めあるいは週末などで気合いの入れ方も違ってくると思います。

 >稲葉 様 
 出来れば受付日と納期日、そしてその間は別の色がいいです。

 受＿＿＿＿＿＿＿＿納
 ■□□□□□□□□●

 ■はグレー、□は水色、●は青　みたいな感じで色分けされると嬉しいです。
 因みに動作確認をしたら最初に作ってあった６月は塗りつぶせたのですが、
 その後同じように作った７月に関しては塗りつぶせませんでした・・・。

 >HANA 様
 詰まっている部分は４カ月分を連続して作れない、というところです。

 ＞=OR(AND(J$5<>"",J$5=$H8),AND(J$5<>"",J$5=$I8))
 これは塗りつぶしたいセルに対しての条件付き書式です。

 ＞G8セルに次の日を入力して、その下の数式を条件付き書式で使ってるのですか？
 はい、その通りです。こちらは書いて頂いた計算式で求めていたものになりました。

 実はカレンダー表示が上手くいきませんでした。
 J5に計算式を入力したら循環参照と出てそのまま続けたところ、４カ月分作っていた最初２カ月分にまるまる色が付いてしまいました。
 受付日・納期日を入力するとその行の色は消えますが、セルが指定した日付を塗ることもしませんでした。

 毎回条件は変わるのですが一例として・・・
 H8を6月10日、I8を6月20日にしS8からAC8に色を付ける。
 一番最初に質問した時の計算式だとこの作業は出来ます。
 ですが、
 H9を7月10日、I9を7月20日にしAW8からBG8に色を付けようとすると、色が付かないのです。

 >??? 様
 曜日も大事ですが期間を大事にするものを作っています。
 実を言うと日にちも非表示にしなるべくすっきりとした見た目にしたいのです。

 とりあえず、新しいシートで
 A1セルに年を入力
 B1セルに月を入力

 J5セルに =IF(TEXT(DATE($A$1,$B$1,COLUMN(A1)),"yyyymm")<=TEXT(DATE($A$1,$B$1+3,1),"yyyymm"),DATE($A$1,$B$1,COLUMN(A1)),"")
 　を入れて、４か月分右にフィルドラッグ

 J8セルの条件付き書式として
 　=AND(J$5<>"",$H8=J$5)・・・・・グレー
 　=AND(J$5<>"",$I8=J$5)・・・・・青
   =AND($H8<J$5,J$5<$I8)・・・・・水色
 を設定して、他の範囲にも広げてみて下さい。

 日にちは、文字色を背景色と同じにする　とかで見えなくするのでも良いですか？

 全然遠い所に作って　そこを参照する様にしても良いかもしれませんが。。。

 (HANA)

 >HANA 様
 上手くいきました！
 ありがとうございました！！

 日にちは文字を白にして弄ってしまわないように非表示にしました。

 あと一つだけ聞きたいのですが、
 この形式の日程表は、月を跨いだとき(今回の場合7月になったら)、一番最初の月(今回は6月)をクリアして切り取り最後の列に貼り付けるなどは可能でしょうか？
 同じシートで延々と繰り返す日程表になってます。

 ｜＿6月＿｜＿7月＿｜＿8月＿｜＿9月＿｜
 月を跨ぐと
 ｜＿7月＿｜＿8月＿｜＿9月＿｜＿10月＿|
 どんどんずれていく

 そうか、日にちの行は非表示にすれば良いんですね！！
 じゃぁ、開いた時にびっくりするので　文字色は戻しておいた方が良いかもですね。
 何も見えないから、何も入力されてないと思って　Delete　されても面倒なので。

 カレンダーの表示は、A1,B1セルに入力した年月によって決まっていますので
 月が変わった時は、そこの入力を変更すれば良いと思いますが。。。
 A1に =YEAR(TODAY())
 B1に =MONTH(TODAY())
 でも良いんじゃないでしょうか？

 (HANA)

 色は付けて念のために注意書きもしておきました！

 >A1に =YEAR(TODAY())
 >B1に =MONTH(TODAY())
 こちらで上手くいきました。

 しかし切り取って貼り付けは上手くいきませんでした・・・
 変な所に水色と青が出現してしまいました。
 月を跨ぐデータがあるため(EX:6/25～7/10など)上手くいかないのかも？
 しれません

 (柏木)

 ＞しかし切り取って貼り付けは上手くいきませんでした・・・
 えっと・・・・そうですね。

 7月と表示されている所は、7月と入っているのではなく
 A1,B1セルに入力されている値と、セルの位置から 算出されているだけなので。

 月を跨いでいるかどうかは、関係ないです。

 原因の一つは、数式が「A1年B1月の4カ月後までの日付なら表示する」って設定になっているので
 どうしても、A1,B1セルの値を表示させたい最初の年月に変えないといけないです。
 　　↓が、その条件を付けている所
 　　TEXT(DATE($A$1,$B$1,COLUMN(A1)),"yyyymm")<=TEXT(DATE($A$1,$B$1+3,1),"yyyymm")

 もう一つは、数式内で　COLUMN(A1) ってところがあると思います。
 カレンダーの始まりの日の数式(J列の数式)が　COLUMN(A1) ってなってないといけないので
 単純に切り取って後ろに回して、後ろが前にシフトしてきたのではダメなんです。
 　　何回か出てきますが　↓の所
 　　DATE($A$1,$B$1,COLUMN(A1))
     J列が COLUMN(A1) その隣のK列が COLUMN(B1)・・・ってなってないといけない。

 切り取って後ろに回した時、J列の数式は COLUMN(A1) ってなってないですよね？

 これに関しては対策はとれるのですが、、、、
 一つ目の問題がクリアできないので
 やっぱり、A1,B1セルの値を書き換えないといけないです。

 ちなみに、A1,B1セルの位置も　非表示にする行に持って行っても問題ないので
 必要であれば変更してみて下さい。

 (HANA)

 >HANA 様
 私が考えていたのは、
 新たに9月の後ろにひと月分の表を作成
 7月の日にちの数式を
 =IF(TEXT(DATE($A$1,$B$1,COLUMN(A1)),"yyyymm")<=TEXT(DATE($A$1,$B$1+3,1),"yyyymm"),DATE($A$1,$B$1,COLUMN(A1)),"")
 に書き換え、またオートフィルでコピーのやり直し。
 その後6月の表を削除みたいな感じで考えていたのですが、手間もかかるしあんまり頭の良くない考えだと思ってます・・・。

 因みに対策と問題点を教えて下さると有難いです。

 (柏木)

 パソコン使用のタイムリミットが来てしまったので
 また明日きます。お手数おかけします(._.)
 (柏木)

 J5:=(A1&"/"&B1&"/"&1)*1
 あるいは　　直接　j5に "2013/6" or "6/1"と入力 この場合　A1: =YEAR(J5)　B1: =MONTH(j5)　
 K5:J5+1
 DY5まで右へコピー。
 DZ5:=IF(DY5="","",IF(DAY(DY5+1)=1,"2",DY5+1))
 EB5まで右へコピー。

 済みません。
 一旦書き込みしたのですが、ちょっと違っている様だったので消してしまいました。

 いまいち、どういった事を聞かれているのか分からないです。

 今の仕組みのポイントは
 　【A1,B1セルの値を書き換えるだけで】、4か月分のカレンダーが自動で表示される
 って所です。

 ですから、その仕組みを残そうと思うと
 【A1,B1セルの値を変える事】は必須に成ってしまいます。

 その仕組みを変えるのなら、どの様な仕組みにしたいのか
 から考え直さないといけないです。

 それにしても、一月分をシフトさせる必要は無いと思いますが。
 もちろん「どうしてもそうしたい」と言うのなら　不可能だ　と言う訳ではありません。

 拘らない方が良いと思いますよ？

 たとえば、以下の様な場合では　どの様に考えますか？

 A1セルに 1 を入力します。
 A2セルに =A1+1 の式を入れて　A30まで下方向にフィルドラッグします。

 今は、A1セルを先頭に 1～の連番が表示されていると思いますが
 15からの連番にしたい場合
 　[１]．A1セルの「1」を「15」に書き換える。
 　[２]．15と成っているからA30までをコピーして、A1セルから貼り付ける。
 でしたら、やるのは[１]の方ですよね？

 [２]をやったのでは、エラーに成ってしまいますし。。。

 A1セルに「1」が入っているから、A15セルに「15」と表示されているのです。
 A15セルの式をA1セルに持って行っても、A1セルは「15」にはならないですよね？

 もしも
 ＞因みに対策と問題点を教えて下さると有難いです。
 が、その上で
 ＞＞これに関しては対策はとれるのですが、、、、
 の「対策」の対応として書かれたのでしたら
 それを書くことに意味は無いです。

 問題点は、前回書いた様に　２点あって
 そのうちの一つは対策はあるが　もう一つの問題がクリア出来ないからです。

 現在の仕組みを残す方向で考えたとしますね？
 6月分のカレンダー部分を切り取って、後ろに回します。
 7/1のセルがJ列に来ますが、
 ＞7月の日にちの数式を ～～ に書き換え、またオートフィルでコピー
 すると、結局 6月分のカレンダーが表示されていた時に入っていた式と
 同じ式が入る　＝　やっぱり6月分のカレンダーから表示される
 って事に成ります。

 ですから、この考え方
 　結局 J列の式を　=IF(TEXT(DATE($A$1,$B$1,COLUMN(A1)),"yyyymm")<=TEXT(DATE($A$1,$B$1+3,1),"yyyymm"),DATE($A$1,$B$1,COLUMN(A1)),"")
 　に変えて4か月分フィルドラッグする
 のでは 7月始まりのカレンダーは作れない
 って事になります。

 仕組みを変える・式を変える
 等するのでしたら、また話は変わって来ますが。

 ただ、最初にも書きましたが　どういった事を聞かれているのかわからないので
 色々書いてはみましたが「そんな事を聞いてるんじゃないのに！！」って場合は
 もう少し、何をどうしたいのか　教えてもらえたらと思います。

 (HANA)

 >HANA 様
 お返事遅れました。ちょっと私が頭が悪いのでHANAさんが仰っていることが
 イマイチ理解できないのですが・・・。
 私がやりたいことをまとめてみました。

 ・日付入力で自動でセルの色が変わる日程表が作りたい(4カ月分)
 　4カ月なのは仕事上長い期間を設けられることがあるからです。
 ・7月になったら6月のカレンダーは消去したい
 　7月になったら6月のデータは不要です。
 　ですが月を跨いでいるデータは(6/25～7/10)6月部分は消去してもいいが、
 　7月部分は残したいです。
 ・7月になったらまた4カ月の日程表にしたい
 　9月の後ろに10月を追加したいです。

 こんな感じです。
 今までのものを無視しても構わないです。

 (柏木)

 えっと。。。

 ＞＞A1に =YEAR(TODAY())
 ＞＞B1に =MONTH(TODAY())
 ＞こちらで上手くいきました。

 って書いてあるので、うまくいったのだと思っていましたが
 何が問題なのですか？

 具体的に
 　○○にならないからこれではだめ
 と書いてもらえますか？

 TODAY() は、コンピュータに設定されている日付なので
 検証のために
 A1=YEAR(A2)
 B1=MONTH(A2)
 として、A2セルの日付を色々替えて確認してみて下さい。

 A2セルの日付が6月なら、6月始まりのカレンダー
 A2セルの日付が7月なら、7月始まりのカレンダーに変わるはずですが。

 ＞日付入力で自動でセルの色が変わる日程表が作りたい(4カ月分)
 条件付き書式で、セルの色が変わるだけの表ですよね？

 ＞7月になったら6月のデータは不要です。
 この「データ」っていうのは、どのセル範囲のどんなデータの事ですか？

 (HANA)

 >HANA 様
 YEARとMONTHを使うのはやめました。
 自分で色々と検証してみると、月を変えれば自動的に6月の日程表は消えるのですね。
 私の検証不足でした。

 お手数おかけしました。あらかた解決致しました。
 本当にありがとうございました！

 (柏木)

 ＞月を変えれば自動的に6月の日程表は消えるのですね。
 そうなんですよ
 ＞＞カレンダーの表示は、A1,B1セルに入力した年月によって決まっていますので
 ＞＞月が変わった時は、そこの入力を変更すれば良いと思いますが。。。
 って書いてたんですが。ＴＴ

 年が変わった時は、A1セルの方も変更して下さいね。
 でないと、違う年のカレンダーが表示されてしまいますので。

 まぁ、難しい問題じゃなくてよかったです！！

 (HANA)

コメント返信：

[ 一覧(最新更新順) ]

[[20130626103005]] 『セルに自動で色をつける日程表』（柏木） ページの最後に飛ぶ

[[20130626103005]]　『セルに自動で色をつける日程表』（柏木）　ページの最後に飛ぶ