[[20120924211107]]　『期間内に入っている月のセルに色付けしたい』（yui）　ページの最後に飛ぶ

『期間内に入っている月のセルに色付けしたい』（yui）

現在、エクセルで作業員の予算管理を扱う表を作成しているのですが、自分がやりたいことがどうやって実現できるかわかりません。
調べたのですが、上手くいかないため、お力をお貸し頂けますと本当に助かります。
宜しくお願い致します。
エクセルは2003、ＸＰ使用です。

作業員の予算管理表を作成しています。
一つのシートの中で、いくつか項目がありますが、
セル「Ｅ５」と「Ｆ５」が結合して作業期間の開始日が入力されています。
また、「Ｅ６」と「Ｆ６」も結合セルとなっており作業期間の終了日が入力されています。
開始日は2012/7/4、終了日は2012/10/4と入力されているとします。
期間は月をまたぐ時もありますし、またがない時もあります。

その下に各作業員の名簿があり、各月の勤務予定時間と予算金額などが入っています。
詳しくは、7月はセル「Ｅ７」と「Ｆ７」が結合して項目名「2012/07」と入力され、Ｅ８セルには「時間」と項目名が、Ｆ８の項目には「金額」と項目名が入っています。

その各下「Ｅ９」セルから「Ｅ１６」までには各作業員ごとに時間数を手動で入力します。
「Ｆ９」セルから「Ｆ１６」へは金額が入りますが、ここは式が入っているので時間を入れると自動で計算され表示される項目となります。

これらが右隣りに月ごとに2012/08、2012/09、2012/10、2012/2012/11、2012/12、2013/01、～2013/06と列AB列まで1年分入っています。

やりたいことは、「Ｅ５」と「Ｆ５」に入っている作業開始日2012/7/4と、「Ｅ６」と「Ｆ６」2012/10/4の作業終了日までの期間に属する月について7月、8月、9月、10月と各作業員の時間と金額セルに色付けをしたいのです。（項目名へは色づけは除きます。例えば、7月はＥ９からＦ１６までと、Ｆ９からＦ１６まで。
8月はＧ９からＧ１６までとＨ９からＨ１６までという風にです。

作業期間は変わるので、変わった場合にその期間に属するセルに色がつくようにしたいのです。
担当者の入力位置の間違えを防止するためにしたいのですが、条件付き書式で出来るのでは？と思ったのですが、作業開始日と終了日は2012/7/4というような表示で、下の月の表示は2012/07という文字列の表示となり、そもそも表示形式も違うし、色づけしたいのはその2012/07の下の時間や金額欄の別のセルなのでどうしていいかわからなくなりました。

条件付き書式にこだわらず、こうやれば出来るというのがあれば勿論それでもかまいません。
また、何かこの表の作成方法に問題があり、改善すれば可能とういことでもいいのでお助け頂けますと本当に助かります。

どうぞ宜しくお願い致します。

図

  A  B  C   D |   E   F   |  G   H   IJ    ～AB列まで
5 作業開始日  | 2012/7/4  |      　　　　　←ここの開始日から
______________|___________|______________   
6 作業終了日  | 2012/10/4 |        |　　　　←終了日までの期間
___________________________________|____
7　　　　　　 |  2012/07  |2012/08 | 2012/09 
___________________________________|____            
8             |時間|金額  |時間|金額 |時間|金額
______________|____|_____|_______________
9  鈴木        20    200　　　　　　　　　　←7、8、9、10月にE9から色付け
__________________________________________　　下は16行目までです
10 山田        30    500
__________________________________________
11　佐藤       10    100

12
13
14
15
16 吉田　　　20 400

 状況説明が完璧でびっくり！
 お手本にしてほしいような質問です。

 7行目が文字列やとのことですけど、シリアル値でも文字列でも
 どっちでもいける方法で説明します。

 ■条件付き書式

 E9:E16を選択して、条件付き書式（セルの値が→数式が、に変更）

 =AND(TEXT(E$7,"yyyymm")>=TEXT($E$5,"yyyymm"),TEXT(E$7,"yyyymm")<=TEXT($E$6,"yyyymm"))
　
「書式」から色を設定

 次にF9:F16を選択して、条件付き書式（セルの値が→数式が、に変更）

 =AND(TEXT(E$7,"yyyymm")>=TEXT($E$5,"yyyymm"),TEXT(E$7,"yyyymm")<=TEXT($E$6,"yyyymm"))

 同じ数式やねんけど、E列とF列を別々に設定すること（E列もF列もE7を参照させるため）
　
「書式」から色を設定

 E9:F16の2列を選択し、AB列までオートフィルして条件付き書式の設定終了
　
　
 7行目は文字列やなく、シリアル値にしておいた方が、E7だけの入力で済むのでラク。
 G7（G7とH7の結合セル）に　=EDATE(E7,1)　を入れて、表示形式をユーザー定義で　yyyy/mm
 AB列までオートフィル
　
 ただしEDATE関数は、2003の場合、ツール→アドイン→分析ツールにチェックしとかんとエラーになります。
 分析ツールが使えんという場合は、G7 =DATE(YEAR(E7),MONTH(E7)+1,1)　にする。
　
　
 7行目は文字列やないとあかんという事情があるんやったら
 条件付き書式の数式はこれでもオッケー

 =AND(TEXT($E$5,"yyyy/mm")<=E$7,TEXT($E$6,"yyyy/mm")>=E$7)

 ただし7行目が yyyy/mm の形の文字列やとして。
　
　
 ■入力規則

 条件付き書式以外では、入力規則で、期間外の場合は入力できないようにすることも可能。

 E9:E16を選択して、データ→入力規則

 入力値の種類：ユーザー設定
 数式　　　　：（上の条件付き書式と同じ数式）

 金額の列には数式が入ってるってことやから、F列には設定せず
 E9:F16の2列を選択してオートフィルでコピー

 もちろん条件付き書式と入力規則の両方を設定してもかまいません。　
　
　
 ■蛇足
　
 ただし表のつくりとしては、入力する列と数式が入ってる列（時間と金額）が交互に繰り返されるのが難点。
 間違って数式が消されるんちゃう？ という心配は無用なんかな。
 それはシートの保護で対応済？

 （よみびとしらず）

 (補足)

 7行目は文字列やないとあかんていう場合でも、入力はE7だけ、G列以降は数式でもいけます。

 E7に文字列で入力（2012/07みたいに）

 分析ツールが使えるんやったら

 G7: =TEXT(EDATE(E7,1),"yyyy/mm")

 使えんのやったら

 G7: =TEXT(DATE(YEAR(E7),MONTH(E7)+1,1),"yyyy/mm")

 AB列までオートフィル
　
　
 なんかごちゃごちゃ書きすぎてカオス状態。
 7行目は文字列でええやんという気がしてきました。

 7行目が文字列限定やったら、上に書いたことをくり返しますけど
 条件付き書式でも入力規則でも、数式はこれでええです。

 =AND(TEXT($E$5,"yyyy/mm")<=E$7,TEXT($E$6,"yyyy/mm")>=E$7)

 （よみびとしらず）

 混迷の度が深まってまいりましたが
 7行目が文字列やとしたら、これでもオッケー、かも

 =AND(DATE(YEAR(E$7),MONTH(E$7)+1,0)>=$E$5,E$7*1<=$E$6)

 シリアル値でもこれでええ、かも。
 ただし7行目を月初の日付にしてるんやったらの話ですけど。
 シリアル値限定ならE$7*1の *1 は不要。

 （よみびとしらず）

（よみびとしらず）さん

いろいろ方法を教えていただきまして、本当にありがとうございました。

まずは、一番最初に教えて頂きました、シリアル値でも文字列でもいけるという方法で設定してみましたが希望通りに色付表示され感動でした！

また、シリアル値への変換方法も教えていただいてありがとうございました。
EDATE関数については全く知りませんでしたのでとても勉強になりました。
これから作業をする上でシリアル値にした方が都合が良ければ教えて頂いたようにしていきたいです。

〉条件付き書式以外では、入力規則で、期間外の場合は入力できないようにすることも可能。

そういう入力規則もあったのですね。
知らなかったので、これは使える！と思い早速設定させて頂きました。
ありがとうございます。

〉ただし表のつくりとしては、入力する列と数式が入ってる列（時間と金額）が交互に繰り返されるのが難点。
〉間違って数式が消されるんちゃう？という心配は無用なんかな。
〉それはシートの保護で対応済？

そうですよね・・。
周囲に数式が他にもたくさん使用されているので私も心配でした。
おっしゃるように、表が完成しましたら運用前にシートの保護で必要項目以外は入力出来ないようにしてしまおうと思っておりました。
入力出来る部分のみ色づけ等の処置を施して。
その一環として質問させて頂いたので解決しまして本当に助かりました。

客観的に見てもやはり表のつくりが問題ですかね。
う～ん、時間と金額でそれぞれ集めようか、それとも上下にしようかと未だに悩むところです。

他人が見て、わかりやすい、使いやすい運用って難しいですね。
ご指摘ありがとうございました。

ベースを教えて頂いたので、表形式を変更しても使用出来そうですね。

本当にありがとうございました。
また、何かございましたら宜しくお願い致します。

(yui)

コメント返信：

[ 一覧(最新更新順) ]

[[20120924211107]] 『期間内に入っている月のセルに色付けしたい』（yui） ページの最後に飛ぶ

[[20120924211107]]　『期間内に入っている月のセルに色付けしたい』（yui）　ページの最後に飛ぶ