[[20121106174307]]　　『入金を確認したらセル文字の色が変わる設定にしたい』(beee) 　ページの最後に飛ぶ

　『入金を確認したらセル文字の色が変わる設定にしたい』(beee)
　
　色々と「条件付書式」を検索すると近いものは出てくるのですが
　やっているうちに混乱してしまいました。

　よくある質問とは思うのですが

　A1 会社名
　B1 入金予定日
　C1 入金日
　
　とセルがあり、

　①　入金日まではC1には特に何も表示させず空欄、
　②　B1の入金予定日を過ぎたらC1に「未入金」と緑文字などで表示させる
　③　B1の入金予定日を5日過ぎたら「未入金」を赤で表示させる
　④　入金を確認したらC1に日付(3/14形式設定あり)を入力し、
　　　入力されたら白セルに黒文字表示にする。

　の設定がしたいです。
　
　エクセルは何年もやっていますが基礎がないので
　初心者並みに分からない事が多々あります。
　特に数式、関数は本当にダメで分からないです。
　
　数式等教えていただけると嬉しいです。
　よろしくお願いいたします。

　(beee)

Excel2003までのバージョンでの手順

C1には「未入金」を最初から入力しておき、書式設定でフォントを白にしておく。
入力規則で
条件1　数式が　=ISNUMBER($C1)　　　　　　　　　　　(4)の条件
条件2　数式が　=AND(ISTEXT($C1),TODAY()>=$B1+5) (3)の条件
条件3　数式が　=AND(ISTEXT($C1),TODAY()>=$B1+3)　　(2)の条件

として、それぞれの条件で書式設定。
当然のことながら、入金日を入力したらC1の「未入金」の文字列データはなくなります。
使い回しする時は再度「未入金」の文字列を入力する必要があります。
なお、Excel2007、2010でしたら条件によって特定の文字列を表示させることが出来たと思います。
（みやほりん）

C列の式を値で上書きするのは運用ミスが起こりやすい事と、背景色変化だけでも十分と思うので、別案。

条件1 「数式」「=AND(C1="",B1<TODAY()-4)」の場合、パターンを薄赤
条件2 「数式」「=AND(C1="",B1<TODAY())」の場合、パターンを薄緑

(???)

　みやほりん様、???さま
　どうもありがとうございます。

　双方とも試し、ちゃんとできました!!
　感謝です!!

　ところでこれを設定しなくてはならない同形式のbookがいくつかあるのですが
　C1で設定した内容を下にコピーすると
　C2C3,C4・・・の条件設定は
　C1と全く同じものがコピーされてしまいます。
　C1の書式を下の行に簡単にコピーする方法があったら是非教えてください。

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

　現在、C1セルには???さんの
　条件1 「数式」「=AND(C1="",B1<TODAY()-4)」の場合、パターンを薄赤
　条件2 「数式」「=AND(C1="",B1<TODAY())」の場合、パターンを薄緑
　が設定されています。

　(beee)

セルのコピペでも変わるはずですが、C1セルだけコピペ後、セル右下の小さな■をドラッグして下に引っ張ってコピーでも駄目ですか？
$C$1 のように、$を付けると固定になってしまうので、数字の前の$は付けないよう。
(???)

 エクセルは 2007 か 2010 でしたよね？

 範囲の先頭セルに設定されている数式が表示されるので
 【同じものがコピーされていると思っているだけ】
 なんじゃないですか？

 B列のセルの値をかえても変化がなく
 B1セルの値を変えた時だけすべて同じ様に変化する

 って事なら、同じ設定がされていると思えますが。。。

 (HANA)

 HANAさん、ありがとうございます。
 エクセル2010です。

 ドロッグアンドドロップ??
 セルをアクティブにして
 右下の「+」をクリックしたまま下に引っ張ってコピーをしているのですが
 未入力セルに色がついたりするので見てみると
 一番最初に設定した「C1」の内容、

 実際設定しているセルには
 条件1 「数式」「=AND(D6="",B6<TODAY()-4)」 の場合、パターンを薄赤 
 条件2 「数式」「=AND(D6="",B6<TODAY())」 の場合、パターンを薄緑

ですが、

　下のセルにコピーした設定は本来
　条件1 「数式」「=AND(D7="",B7<TODAY()-4)」
　条件2 「数式」「=AND(D7="",B7<TODAY())」

  ・・・となって欲しいところ、

　条件1 「数式」「=AND(D6="",B6<TODAY()-4)」の場合、パターンを薄赤
　条件2 「数式」「=AND(D6="",B6<TODAY())」の場合、パターンを薄緑
　とそのままコピーされてしまいます。

　ちょっと前にコピーの件質問していますが
　全然わかりません・・。

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

　（ｂｅｅｅ）

 そうなんですよね。
 ここはこれまでと同じだった方がわかりやすかったと思いますが。。。

 今までは、
 　アクティブセルに設定されている数式
 が表示されていましたが、2010は
 　適用先範囲の先頭に設定されている数式
 が表示される様になっています。

 違いが分かりますか？

 たとえば、 
 　A1セルに =A1=0　条件で書式を設定。
 　そのセルをコピーして、A1:A10セルの範囲に貼付。

 2003までの場合
 　A2セルをアクティブにして、条件付き書式の設定を開くと
 　数式は　=A2=0　と表示されていました。
 2010の場合
 　A2セルをアクティブにしても =A1=0 と表示されます。
 　適用先に　=$A$1:$A$10　のセル番地が表示されると思いますが
 　先頭セル・・・A1セル・・・に設定した数式がそのまま表示されるのです。

 思った結果にならないなら、別の問題があるのではないかと思います。

 ちなみに、B,D両方共入力が無い場合、色がつく様な書式になっていると思います。
 条件式をどこかのセルに書き出して　結果を確認してみて下さい。

 (HANA)

 HANAさん、???さん　間が空いてしまってすみません。

 やはり、■のところを下にコピーするとHANAさんがおっしゃるように一番最初に設定したコピー元の
 セルの内容がコピーされます。
 ちまちま1個ずつ直していたら何かのきっかけでまた最初の、
 コピー元のデータに戻ってしまいます。
 これは原因もわからず、何度やっても同じです。

 不毛です。

 コピーしたものをいったん全部クリアして、1セルずつ設定していけばできますが・・・。

 なんとかつらーっとコピーする方法はないでしょうか。

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

 (beee)

　上の補足です。
　一旦全部クリアして1セルずつ設定していけば・・・と書いたのですが、
　一番上のセルの情報をクリアしたらコピーしてあった(ちまちま一個ずつ直した)他のセルの情報も
　全部消えていました。

　いったい何が起きたのでしょうか。

　　(beee)

 現在の案件から少し外れますが、以下の事を確認して
 どの様になるのか、そちらの状況を教えていただきたいのですが。

 新しいブックを用意して下さい。
 シート1のA2セルに　赤　と入力してください。
 その隣のB2セルに　条件付き書式の設定で
 　数式が　=A2="赤"　　　セルの色を赤
 の設定をして下さい。

 ここで確認ですが、B2セルの色が赤になりますよね？・・・・・・・・（１）

 B2セルをB1セルにフィルコピーして下さい。
 　フィルコピーと言うのは、B2セルをアクティブにすると少し太めの枠で囲われますが
 　その右下にある四角い部分をクリックして、ドラッグする事です。
 　＞右下の「+」をクリックしたまま下に引っ張ってコピー
 　これの事です。
 B1セルの色は赤に変わりますか？・・・・・・・・・・・・・・・・・（２）

 A1セルに　赤　を入力した場合、B1セルの色はどうなりますか？・・・（３）

 B2セルの条件付き書式の数式を確認するとどうなっていますか？・・・（４）

 A2セルの文字を削除した場合、B2セルの色はどうなりますか？・・・・（５）

 B2セルの条件付き書式の数式を確認した時、=A1="赤" と表示されていたとしても
 B2セルの色は A1セルのデータではなく、A2セルのデータによって　変化すると思います。
 A1:A2セルの値を色々替えて、納得いくまで確認して下さい。

 現在思った結果にならないのは、条件付き書式のコピーの方法に問題があるのではなく
 設定している条件の方に問題があると思います。

 (HANA)

HANAさん

ありがとうございます。

やってみました。

 ここで確認ですが、B2セルの色が赤になりますよね？・・・・・・・・（１）　はい

 B2セルをB1セルにフィルコピーして下さい。
 　フィルコピーと言うのは、B2セルをアクティブにすると少し太めの枠で囲われますが
 　その右下にある四角い部分をクリックして、ドラッグする事です。
 　＞右下の「+」をクリックしたまま下に引っ張ってコピー
 　これの事です。
 B1セルの色は赤に変わりますか？・・・・・・・・・・・・・・・・・（２）　いいえ

 A1セルに　赤　を入力した場合、B1セルの色はどうなりますか？・・・（３）　赤に変わりました

 B2セルの条件付き書式の数式を確認するとどうなっていますか？・・・（４）　=A1="赤"

 A2セルの文字を削除した場合、B2セルの色はどうなりますか？・・・・（５）　消えます

となりました。
なるほど。と思うのですが、(4)の所、
「条件付き書式」で表示されている式が自分の思惑と違うのに反映される。
と言うのがどうも分かりません。
この辺が私の基礎のなさだと思います。
いずれゆっくり勉強しよう・・でここまで来てしまっています。

また、コピーしたセルの数値を納得いくように変えたとしても、
他のコピーした任意のセルの条件付き書式を削除すると
コピーし、手直ししたすべての内容が消えてしまいます。
これでかなりの時間を費やしてしまい正直へこんでます。

コピー元の「B2」を条件式を削除して全部消えるなら分かるのですが
前はこうではなかったように思い、混乱している一因でもあります。

率直にお伺いします。

　A1 会社名
　B1 入金予定日
　C1 入金日
　
　とセルがあり、

　①　入金日まではC1には特に何も表示させず空欄、
　②　B1の入金予定日を過ぎたらC1に「未入金」と緑文字などで表示させる
　③　B1の入金予定日を5日過ぎたら「未入金」を赤で表示させる
　④　入金を確認したらC1に日付(3/14形式設定あり)を入力し、
　　　入力されたら白セルに黒文字表示にする。
　⑤　これらを設定したセルを一気にコピーして目的の表を仕上げる。

この条件式を教えていただけたら嬉しいです。
「学校」なのに短絡的で申し訳ないですが助けてください。
どうぞよろしくお願いいたします。

(beee)

C1に予め「未入金」を入力しておく。
C1の条件付き書式を以下にする。
「数式」「=AND(C1="未入金",B1<TODAY()-4)」「文字色赤」
「数式」「=AND(C1="未入金",B1<TODAY())」「文字色緑」
「セルの値」「次の値に等しい」「未入金」「文字色白」

(???)

 ＞「条件付き書式」で表示されている式が自分の思惑と違うのに反映される。 
 ＞と言うのがどうも分かりません。

 その説明も、既に書いているんですけどね？

 ＞＞2010の場合
 ＞＞　A2セルをアクティブにしても =A1=0 と表示されます。
 ＞＞
 ＞＞　適用先に　=$A$1:$A$10　のセル番地が表示されると思いますが
 ＞＞　先頭セル・・・A1セル・・・に設定した数式がそのまま表示されるのです。

 最初 B2セルに設定した時は、適用先が =$B$2 になっていたと思います。
 ですから、条件の数式は先頭セルに設定されている　=A2="赤"　が表示されます。

 フィルドラッグした後は、適用先が =$B$1:$B$2 になったと思います。
 ですから、【どのセルをアクティブにして確認しても】
 　　　　　　　　　　　先頭セルに設定されている =A1="赤"　が表示されます。

 思った結果にならない時に
 　設定した手順が悪いのか
 　設定した内容が悪いのか
 見極める必要があると思います。

 思った結果になるセルと、ならないセルがありますよね？
 『A2セルに赤』の確認で、内容の方に問題がありそうだと目星がついたと思います。
 うまくいかない所は、各セルがどのようになっているのか説明をしておかれるのが良いと思います。

 そちらで設定した時の手順を細かく書いて下さった様に。
 　B列が○○でC列が××の時は、◇◇になって希望通りだが
 　B列が△△でC列が□□の時は、◎◎になって希望と違う

 データの情報を正確に伝える事が、思った結果を得るための近道です。

 (HANA)

???さん、HANAさん、ありがとうございます。
本日外勤でパソコンを触れないと思います。
また少し間が空いてしまいますがかならずやってみます。
引き続きご指導ください。

(beee)

HANAさん、???さん間が空いてしまってすみません。

HANAさん、

＞＞2010の場合

 ＞＞　A2セルをアクティブにしても =A1=0 と表示されます。
 ＞＞
 ＞＞　適用先に　=$A$1:$A$10　のセル番地が表示されると思いますが
 ＞＞　先頭セル・・・A1セル・・・に設定した数式がそのまま表示されるのです。

の件、理解しました。
うまくいかないのはこのせいかと思っていたのですみませんでした。

もう一度やってみて、見直してみました。
あと、「条件を満たす場合は停止」がよくわからなかったのでこれも設定してみたのですが
やはりよくわかりませんでした。
これも教えていただけたら嬉しいです。

ダミーで作ったシートで
Bには入金予定日、
Cに書式

条件1 「数式」「=AND(C4="",B4<TODAY())」の場合、パターンを赤
条件2 「数式」「=AND(C4="",B4<TODAY()-4)」の場合、パターンを緑
「条件を満たす場合は停止」にチェック
設定範囲　$C$4:$C$18

条件1 「数式」「=AND(D4="",B4<TODAY()-4)」の場合、パターンを緑
条件2 「数式」「=AND(D4="",B4<TODAY())」の場合、パターンを赤
「条件を満たす場合は停止」にチェック
設定範囲　$D$4:$D$18

条件1 「数式」「=AND(E4="",B4<TODAY())」の場合、パターンを赤
条件2 「数式」「=AND(E4="",B4<TODAY()-4)」の場合、パターンを緑
「条件を満たす場合は停止」チェックをはずす
設定範囲　$E$4:$E$18

条件1 「数式」「=AND(F4="",B4<TODAY()-4)」の場合、パターンを緑
条件2 「数式」「=AND(F4="",B4<TODAY())」の場合、パターンを赤
「条件を満たす場合は停止」チェックをはずす
設定範囲　$F$4:$F$18

(TODAY・・・２９日として)

すると下記のように表示されます。

手順についてはC,D列の表示順を変えてやってみましたが正しく表示されません。
本日が２９日なので本来Gのように表示したいのですが・・・。

何が問題でしょうか。
またG列のように表示するにはどのように設定したらよいでしょうか。

      B          C        D        E        F　　　　　　　G
 B4  11/16      赤       緑       赤       緑              赤
 B5  11/17      赤       緑       赤       緑              赤	
 B6  11/27      赤       赤       赤       赤              赤		
 B7  11/28      赤       赤       赤       赤              赤
 B8  11/29      無       無       無       無              緑		
 B9  11/30      無       無       無       無              緑		
 B10 12/1       無       無       無       無              緑	 	
 B11 12/2       無                                         緑		
 B12 12/3       無                                         緑		
 B13 12/4       無                                         無		
 B14 12/5       無                                         無		
 B15 12/6       無				
 B16 12/7       無	　			
 B17 12/8       無				
 B18 12/9       無

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

★何度やっても表示がずれてしまいます。
C4赤、D4緑、E4赤、F4緑・・・です。

(beee)

 あれ？
 今日が 11/29 の予定ですよね？

 で、最初のご説明の色だと

　②　B1の入金予定日を過ぎたら・・・・・緑
　③　B1の入金予定日を5日過ぎたら・・・・赤
　④　入金を確認したら・・・・・・・・・黒

 なので、
  今日の5日前 11/24以前・・・・・・・赤
　それ以降の今日迄 11/25～11/29・・・緑
 じゃないんですか？

 G列の色だと
 　今日を過ぎたら　赤
 　今日を含めて未来の5日間を　緑
 ですよね？    ~~~~

 すると、赤くする方は　B4<TODAY() の様に今日の日付とB列の日付を比べれば良いと思いますが
 　　　　緑にする方は　TODAY()+5 した日付とB列の日付を比べないといけないですよね？

 H4に
 　=AND(C4="",B4<TODAY())
 I4に
 　=AND(C4="",B4<TODAY()+5)
 の式を入れて下にフィルドラッグして、セルに表示されている結果を確認してみて下さい。

 H4:H7 と I4:I12 が「TRUE」になりますか？

 (HANA)

HANAさん

早速のレス、感謝です!!
なんか表がちゃんと表示されています。
魔法???

表示の色の件、肝心な所を書き忘れていました。
経理に言われて
入金予定日が過ぎていて入金のないものは赤、

その後の５日間は緑に、

以降は何も表示しない、
入金日が入力されたらその日付は白地に黒文字(自動)表示。です。

電話応対や雑用をしながらなので自分でもこんがらがっています。

やってみました。

H4とI4、おっしゃる範囲がTRUEとなりました。

と言うことは・・・

条件1　=AND(C4="",B4<TODAY()) 赤
条件2　=AND(C4="",B4<TODAY()+5)　緑

と設定してやれば・・・。

・・・出来ました!!
さらに下にドラッグしてコピーして・・・
出来ました!!

本チャンの表に設定してみましたが自分の希望通りに表示されました。
もう私泣けてくるぐらい頭悪いです。

これで間違いないかと思うのですがいまいち自信が・・・。
私の設定の仕方で大丈夫でしょうか?

(beee)

 表は、ずれていたので　勝手に直しました。
 最初に半角スペースを入れて
 後の開けたい部分を半角スペースで調整すると
 入力は面倒ですが　割ときれいに表示できると思います。

 エクセルから張り付けた場合等は、見た目で確認出来ない Tab が入っていたりするので
 スペースで調整してもなかなかうまくいかない事もあると思います。

 ＞その後の５日間は緑に 
 こんな感じの　値の比較は混乱しやすいので
 最後に表を載せていただいた様に　実際に書き出して考えてみるのが良いと思います。
 ＞＞TODAY()+5 した日付とB列の日付を比べないといけないですよね？
 ここの理屈は分かってもらえましたか？

 書式の設定に関しては、上手く表示出来ているなら　良いと思います。

 一つ問題としては、B列もC列も入力が無い場合に　セルに色がついてしまいます。
 「書式を多目にコピーしておくことはないよ♪」
 と言う事であれば問題ないと思いますが。

 ＞「条件を満たす場合は停止」
 これにチェックを付けておくと、これまでのバージョンでの設定と同じ様に
 　上から順に評価され、条件を満たした時点でその書式が適用され　それ以降の条件は無効
 です。

 このチェックを外しておくと、次の設定も評価され　条件を満たせば適用されます。

 今回はどちらもセル色を変更する設定だったので違いが分かりにくいですが
 たとえば　上側の設定(条件1)を「セル色赤」
 　　　　　下側の設定(条件2)を「文字色緑」
 にしたとします。

 H,I列に式を入れて確認してもらいましたが
 H4:H7 と I4:I7 はどちらも TRUE になっていますね。

 チェックがついていたら　条件1が満たされた時点で次の評価はされませんので
 C4:C7 はセル色が赤になるだけです。

 チェックを外していたら　続いて条件2も評価されますので
 C4:C7 は　更に　文字色も緑になります。

 D4セルに
 　●　を入力。
 　条件付き書式の設定で
 　　条件1 =B4<TODAY()　　セル色赤
 　　条件2 =B4<TODAY()+5　文字色緑
 　の設定をして
 D18までフィルドラッグした後
 チェックを付け替えて結果の違いを確認してみて下さい。

 (HANA)

HANAさん

どうもありがとうございます!!

こんがらがってぐしゃぐしゃでしたが
いつも霞がかかってスルーしていた部分なので勉強になりました。

＞その後の５日間は緑に

 こんな感じの　値の比較は混乱しやすいので
 最後に表を載せていただいた様に　実際に書き出して考えてみるのが良いと思います。
 ＞＞TODAY()+5 した日付とB列の日付を比べないといけないですよね？
 ここの理屈は分かってもらえましたか？

これは比べて、一目でわかりました。
こうしなかったらまだ混乱していたと思います。

表の整え方、「条件を満たす場合は停止」など、
会社にあるマニュアルには書いてなく、
検索するにも次々と雑用が降ってきてちょっとしたことが
出来ずにおりました。

実際にやってみると違いが分かります。
細かい表や拙い私の説明等おつきあいいただき
本当に助かりました。

分からないことはまだまだあるのでまたお世話になることがあったら
どうぞよろしくお願いいたします。

感謝です!!
ありがとうございました。

(beee)

コメント返信：

[ 一覧(最新更新順) ]

[[20121106174307]] 『入金を確認したらセル文字の色が変わる設定にしたい』(beee) ページの最後に飛ぶ

[[20121106174307]]　　『入金を確認したらセル文字の色が変わる設定にしたい』(beee) 　ページの最後に飛ぶ