特定の数値に色やマークで表示したい

質問

単に色分けであれば、条件付書式で出来たのですが・・・
A列>B列　赤
A列<B列　青
A列=B列　青　ここ迄は条件付書式でOKなのですが、
更に
B列に
100 赤
100 青(連続する2回目以降）
0.00 赤
0.00 赤
0.00 青（連続する3回目以降）

問題はB列には、見た目には100と見えて、「100」に対しての色分けの式を考えていましたが・・・関数が入っている為エラーになるし・・・
どのようにしてB列の色分け又はマークでの表示の仕方を教えてください。

回答No.11

vista2446 · ベストアンサー

今夜は、お仕事のため、あまり時間がありません。なので、推測で数式を書きますので、ご容赦ください。
カタチにはなると思いますので、今夜はこれで社長の目をごまかし、週末を楽しんでください(笑)

E列に文字が無く、色付きセルなのでしょうから、関数に利用することができません（VBAを除く）なので、「赤」「青」の値の入った列を設けます。例として『H列』に設けます。
H4セルに
=IF(OR(AND(B4=100,B3=100,B2=100),AND(B4=0,B3=0)),"青",IF(OR(A4<B4,B4=100,B4=0),"赤",IF(A4>=B4,"青","")))
と入力し、オートフィルでコピーします。H2にコピーするとエラーになりますので、このセルには関数を入れず、手入力で「赤」か「青」を入力してください。ちなみに、この数式はSTEP1の条件付き書式の関数です。

F列の関数はさっきのH列を参照します。F2セルに
=IF(AND(C2="",D2=""),"",IF(H2="赤",IF(23<D2,"○",IF(23>D2,"△","")),IF(H2="青",IF(23<C2,"○",IF(23>C2,"△","")))))
と入力します。ただ、C、D列が「23」だったときの補足がありませんでしたので、設定してませんので必要なら不等式を変更してください。

明日のお昼ごろまで、ココに来られそうにありません。なんとか急場を凌いでくださいね！これで上手くいくことを祈ります。。。

お礼

ありがとうございます・・・
お仕事のお邪魔をしてすみません・・・
ちょうど、社長と打ち合わせをしていて、「見て直ぐ分かるのは色で区別されている方だけど・・・もし、話の通り（セルの色では、関数が成立しないかも？色では無く、文字やマークでは駄目なんですか？って推測を話していたところでした。）なのであれば、文字でもいいよ～～～ん！」って感じの話をして・・・その式を考えていたところでした。

ちなみに、「２３」は「○」の仲間の様なので、
=IF(AND(C2="",D2=""),"",IF(H2="赤",IF(23<=D2,"○",IF(23>D2,"△","")),IF(H2="青",IF(23<=C2,"○",IF(23>C2,"△","")))))
で良いのかしら？　トライしてみます！

とりあえず、教えて頂いた内容でがんばります！（力こぶ）
vista2446さんも　お仕事（本業）頑張ってください！！
＊補足が生じたら・・・補足送っておきます。
いろいろ　ほんとうにありがとうございます。
後　一息です。がんばりま～～～す（にひぃ）

回答No.12

vista2446

こんにちわ。お昼を過ぎて補足が無いことから、無事、結果を出されたんだと思います。それとも、STEP3まで進んでるのかしら？
mim-mimさんの頑張りが報われて、マッタリと週末を過ごされているんでしょうね♪
数式の変更は、結果良ければそれでヨシ！だと思うし、H列が邪魔だなぁと思えば、列を選択して右クリックから非表示にしてしまえば見えなくなります。それにしても、かなり頭を悩ませて、頑張り抜いて、ココに辿り着かれたんだと思います。ホントにおつかれさまでした。

補足

先週は本当に本当にありがとうございました。
何とか、社長「OK!」が出て・・・ホッと安心して・・・
それをテンプレートとして、他のシートも直し始めていた矢先・・・
日曜にPCを起動させたのですが・・・起動せず・・・
（週末は殆ど持って帰って、作業することも多いので・・）

丸一日つぶし・・・　今日会社で見てもらっていたのですが・・・
どうも、すぐには直らない無い様で・・・　とりあえず、違うPCを使用して仕事の再開です。
半日つぶれました・・・
しかし、血と涙の結晶のシートが・・・壊れたPCの中・・・・
途方にくれました・・・
テンプレートも直したシートも・・・

幸い、社長に送っていたシートが有ったので、初めからの作業では無くなったので・・・
一安心（涙）
テンプレートに直して、それからそれぞれのシートを直す・・・・
こらからがんばりま～～～～す。

本当に本当にありがとうございました。
PCの故障さえなければ　更にHappyな週明けでしたが・・・
こんなもんですね・・・人生って・・・

ちなみに、複雑な（私には）式を入れてありますが、それを見えなく又は式が入っているセルにエラー表示「#DIV/0!」ではなく
空白（じゃない方がいいかな・・・？）とか「ー」とかマーク表示をしたいのですが、
あの式を消さずに出来るのかが心配で・・・
私個人としては、「#DIV/0!」は表示されていて欲しくない・・・
今まではそう、作成してきましたが・・・
今回のように、複雑な式に当てはめるのが。。。怖くて・・
手をつけずにいます・・・

一応・・・お礼＆状況をお知らせするついで（苦笑い）に再質問させて頂きました。
何か、良いアドバイスが有るようでしたら、また　お願いします。（ペコリ）

回答No.10

vista2446

設問に不備があります。以下を補足してください。
１．E列はA、B列から求めた値で「赤」「青」と表記されているのか
２．E列が「赤」でも「青」でも「23<C列=○」「23>C列=△」と読み取れるが、そういう設問なのか
３．文章と例の配列に矛盾がある（E列「赤」の行）
４．STEP1を忘れ、STEP2の設問では、F2に入る数式は、
=IF(C2="","",IF(23<C2,"○","△"))　
　［C2が空白ならF2は空白、23を超える値なら○、23以下なら△］
となります。

なにか、大事なことをお忘れではありませんか？

補足

大変失礼しました。　改めて読み返すと間違いばかりで・・・

大切な事は、色を分けた事であり、今回はその色を利用しての第２スッテプでした。

ご指摘の
１）は、完全に私の表現不足です。
正しくは
Ｅ列が「青」の場合、「Ｃ列」を参照して「２３」より大きければ「○」小さければ「△」
Ｅ列が「赤」の場合、「Ｄ列」を参照して「２３」より大きければ「○」小さければ「△」
＊「青」＝「Ｃ列」　「赤」＝「Ｄ列」参照の事でした。
上記がその他の疑問点をほぼ全てをカバーする事と思います。

「○」「△」を表示させる場所（列）ですが、それは、「Ｆ列」がベストですが、それが難しければ、色が消えなければ、「Ｅ列」でも結構です。＊簡単な方で構いません。

罫線の件ですが、条件付書式の中で勝手（？）に見えない（消える）様に設定がされていた事に気が付きました。それぞれの書式で罫線を入れたところ、解消されました。
また、「100２連続」とかの文字も無事に消すことが出来ました。
ありがとうございます。

今日は、外での会議が多く・・・戻ってきて早速、私には厄介な「エクセル」との戦いが始まります。
どうも・・今日中の仕上げらしいのです・・・前任者は体調を崩して・・期限や内容も良く分からず、「はい！これ！」って笑顔（苦笑い）を添えて社長から渡されたもので・・・
渡す時に期限や第２ステップがある事を知らせて欲しかった・・・
と、愚痴っても仕方がありません！
mim-mim必死でがんばります！
ご指導お願いします。

現状は、文字を消した事、罫線を表示できた事・・・そこまでです。
すみません・・・進んでなくて・・・
お力お貸しください。（ぺこり）　再補足が有る場合は、即刻お知らせください。確認しご連絡させて頂きます。

回答No.9

vista2446

雷ですか～　自然の「美」と「驚異」ですね～
外の「雷鳴」と事務所内の「悲鳴」では、一緒に働いてる方の「罵声」が輪唱されますよね(爆)

本題。条件付き書式でセル色を設定なさってるんでしたら、文字を消しても無問題。背景色と罫線色の配色で罫線が、見づらくなることはよくあります。なので、色の組み合わせで対処しましょう。次のSTEPということですが、セルの書式を反映させてなにかをエクセル君に何かしてもらおうとすると、VBAさんのお出ましを願うことになりそうですが、σ(´ρ` )は残念なことにVBが書けません(泣)
一応、しゃちょーさんのご注文をお知らせください。私のスキルアップにもなるかも。。。

補足

お疲れ様です。。。コーヒーブレイクが・・・ディナータイム（お弁当）になってしまいました。

書式というか・・・色で判別したいらしい。
例題として対象列をＣ列にしていましたが、それは、忘れ違う列にします。
Ａ列Ｂ列は変わりません。（それの最異を色別にしたかっただけのようです。第一ステップ偏）
新たに
Ｃ列（上昇）　Ｄ列（下降）　Ｅ列（ＡＢを色分けしたもの）　　Ｆ列
６　　　　　　９５　　　　　　赤
５１　　　　　７　　　　　　　赤
１９　　　　　５２　　　　　　赤
５８　　　　　２１　　　　　　青
８４　　　　　２４　　　　　　青
２０　　　　　４２　　　　　　青

上記の様になっているとします。
＊Ｅ列が「赤」の時Ｃ列を参照し「２３」の数値より上の数字なら○
下の数字なら△
＊Ｅ列が「青」の時も上記内容と同じ
○△のマーク表示をＥ列に入れても、またはＦ列に入れてもかまいません（簡単なほうでお願いします。）
なので、
Ｃ列　　　　　Ｄ列　　　　　Ｅ列（ＡＢを色分けしたもの）　　Ｆ列
６　　　　　　９５　　　　　　赤　　　○
５１　　　　　７　　　　　　　赤　　　△
１９　　　　　５２　　　　　　赤　　　○
５８　　　　　２１　　　　　　青　　　○
８４　　　　　２４　　　　　　青　　　○
２０　　　　　４２　　　　　　青　　　△

上記の様になる事を依頼してきたようです。。。

お分かりいただけましたでしょうか？？？

雷も納まり小雨になったら、今日は帰ろうと思っています。。。
せめて・・・後１時間で帰りたい・・・

さぁ～　気合入れなおして！がんばるぞ～～～
まずは、色の調整と文字を消します。

○△の件・・・よろしくおねがいします・・・

回答No.8

vista2446

３）A列>と＝B列　「青」と４）A列<B列　「赤」で矛盾が・・・
A列　　　B列　　　　C列
55.45　　0.00　　 「赤」
0.00　　　0.00 　　「赤」
45.98　　79.99　　「青」　　　　←「赤」？
100.00　　100.00　 「赤」
45.66　　100.00　 「青」＊2回目だから
55.56　　55.56　　「青」
87.98　　0.00　　 「赤」
0.00　　　0.00　　 「赤」
0.00　　　0.00　　 「青」＊3回目だから
59.99　　100.00　 「赤」
66.89　　34.55　　「赤」　　　　←「青」？

回答No.7

vista2446

お疲れさまです。今宵は飲みに出かけませんので、仕切り直してじっくり考えましょう。(笑)
ところで、連夜の残業になっちゃうんでしょうか？食事はキチンと取ってくださいね～

1)　B列=100　「赤」　
2)　B列=0.00　「赤」（連続2回まで）
3)　B列=100　連続2回目から　「青」
4)　B列=0.00　連続3回目から　「青」
5)　A列>=B列　「青」
6)　A列<B列　「赤」
※C列にzap35さんが提示されたIF関数が組み込まれている。
これらの条件と、この順序で優先順位としてよろしいでしょうか？今頃ですが、この条件付き書式は、どの列に設定するんでしょうか？

なんか、チャットみたいになってワクワクしてきました～（不謹慎）　mim-mimさんが早く帰宅できますよーに祈りますわ。。。

あ、
＞では、A列＞B列　「青」が含まれてないように思うのですが・・・
#5の回答で「C列のフォントカラーを「青」に変更しておきます」とあらかじめフォント色を青にしたため、「A列>B列」「A列=B列」は条件に含みませんでした。条件3を付け加えたときに「A列>=B列」とするべきでしたね～＼(__ )　

あっ！こちらにもコーヒーを１つ、お願いできますか？(笑)

補足

コーヒーで良いなら・・・　１つと言わず・・・どんな仕事を投げ打っても、お届けに参ります。（コーヒー（お茶）出しには自信（？）評判良いんですよ！これが・・・　社内外から・・・
しかし、外は光まくり轟音が凄いです・・・
さっきは、余りの雷の大きさに「きゃ～～～～～～～～！」っと言って耳を塞ぐ私がうるさかったと・・・言われてしまいました・・・
なんか、ほんとチャットいてるみたいです。（にひぃ）

本題ですが、うっかりさんの私はうっかりセルを「青」に設定する事を忘れていたのでした。
罫線の事が無ければ、９９％ＯＫ！です。
どうも、赤が続く＆青の場合は文字（１００連続）が表示されていると罫線が見えない。　青が続く場合には罫線は見えます。
この件は、保留（？）でなんと！なんと！更なる追い討ちが・・・
社長に見せた所「罫線はそんなに重要じゃない、では、次のスッテップに移ろう・・・それと、この文字要らないかな？」
新たな指示が発令されました。
第２スッテプ
１）文字を外す。
＊上記は式から外せば良いんですよね？

次を説明するのが又これ大変かも・・・（汗）
色分けをする事が第１スッテップで・・その色を利用して第２スッテップへ・・・
２）はちょっと頭を整理して、再度連絡させていただきます。
まずは、１）の文字を外すのは、計算式から外せば良いですか？？

あぁ～私も自分のコーヒー淹れてこよっと！
一緒にコーヒーブレイクしましょ（笑い）

回答No.6

vista2446

先程、ご質問文からずっと読み返していたのですが、途中で条件に変更があったようですね・・・よく読んでませんでした。
【質問文】
A列>B列　赤
A列<B列　青
A列=B列　青
【#4の補足】
A列<B列　赤
A列>B列　青
A列=B列　青

>A列>B列とA列＝B列は「青」と言う条件が反映されず・・・「赤」で表示されてしまいます。
#2の補足から条件を設定しましたので、A列>B列は赤で表示させてます。A列<B列が青ではなく赤で表示させるんですね。条件2の不等号を変更してください。
A列=B列　青には条件指定をしてないので、青で表示されてますが。。。ただ、A、B列が１００でも連続2回までの赤表示を優先させるんですよね？

>条件付書式を下へコピーしていくと　罫線は消えて（見えなくなってしまうのですか？）しまうのでしょうか？
セルの書式設定で罫線を指定しているにもかかわらず、消えてしまってるんですね。初めて聞く症状ですね～（汗）
とりあえず、セルの書式設定で罫線をクリア後、再度設定してもダメですかね？

再度、すべての条件と優先順位を提示していただけないですか？
現時点でのC4セルの条件付き書式の数式は、
条件1　青　=OR(AND(B4=100,B3=100,B2=100),AND(B4=0,B3=0))
条件2　赤　=OR(A4<B4,B4=100,B4=0)
条件3　青　=A4=B4

補足

あとがとうございます（ペコリペコリ）
月末で銀行行ったり・・・雑務（苦笑い）をしながらなので・・・なかなか進まず・・・　ご返答本当にありがとうございます。

仕切り直させて頂ます。（間違っていた部分も有り）
１）B列に100　「赤」　
　　B列に100が連続して2回め以降の場合　「青」
２）B列に0.00が連続して2回まで　「赤」
　　B列に0.00が連続して3回目以降は　「青」
３）A列>と＝B列　「青」
４）A列<B列　「赤」

なので、例えば
A列　　　B列　　　　　C列
55.45　　＊　0.00　　「赤」
0.00　　 ＊　0.00 「赤」
45.98　<　79.99　　　「青」
100　　　＊　100　　　「赤」
45.66　　＊　100　　　　「青」＊2回目だから
55.56　＝　55.56　　　　「青」
87.98　　＊　0.00　　　　　「赤」
0.00　　　＊　0.00 「赤」
0.00　　　＊　0.00　　　　　　「青」＊3回目だから
59.99　　　＊　100　　　　　「赤」
66.89　>　　34.55　　　　　　「赤」

こんな数字がランダムに並んでいています。（数字の配置がバラバラですみません）

IF関数で連続する式は前の方に教えて頂いた式を入れてあり、連続されたらセル上に「0.00３連続」と表示されるようになってます。
=IF(AND(A3=0,A2=0,I1=0),"0.00３連続",IF(AND(A3=100,A2=100),"100２連続",""))

上記の式を踏ませて、１）～４）の条件をクリアする方法を教えてください・・・

尚、
＞現時点でのC4セルの条件付き書式の数式は、
条件1　青　=OR(AND(B4=100,B3=100,B2=100),AND(B4=0,B3=0))
条件2　赤　=OR(A4<B4,B4=100,B4=0)
条件3　青　=A4=B4
では、A列＞B列　「青」が含まれてないように思うのですが・・・

罫線の件は、教えて頂いた通りにやってみます。
あぁ～　今日は終電だけは・・・避けたい！
ぶ～～～～！上司から「お茶飲みたい！」コールがかかりました。。。
お茶出して・・エクセルに取り組みます。。。
「お茶が先か～　エクセルが先か～～～～（ゲンコツ）！」（うふっ
心の叫び）

また、報告します。　この説明で理解していただけたでしょうか？？？
よろしくお願いします。　mim-mimでした。

回答No.5

vista2446

#2です。昨夜は大変だったんですね。私も昨夜は遅くまで飲み歩いてて、今日は二日酔いのため、頭痛がひどいです。(笑)
補足の例にある「C列の赤、青を条件に合わせて色付けを設定する」という条件付き書式を考えてみました。
C列のフォントカラーを「青」に変更しておきます。条件付き書式の数式は、C4に設定した書式です。
条件１．=OR(AND(B4=100,B3=100,B2=100),AND(B4=0,B3=0))　フォント色・青
条件２．=OR(A4>B4,B4=100,B4=0)　フォント色・赤
C4セルの書式をC列の他の行へ書式のみコピーしてみたところ、補足の色になったという次第です。
酔いがまだ残ってるみたいなので、mim-mimさまの意に沿っているのかわかりませんが、参考になれば幸いです。

補足

早速のご返答ありがとうございます（ぺこり）

教えて頂いた条件で作成し・・・確認したところ、

A列>B列とA列＝B列は「青」と言う条件が反映されず・・・
「赤」で表示されてしまいます。

また、初歩的かもしれませんが、条件付書式を下へコピーしていくと　罫線は消えて（見えなくなってしまうのですか？）しまうのでしょうか？
セルの書式設定上は罫線は指示してありますが・・・

何度もすみません・・・
二日酔いのところ（プチ笑い）

えっと、私の説明わかりましたかね～～～～　説明下手ですみません・・・
ご返答おまちしてま～～～～～す！

回答No.4

vista2446

お疲れ様です。#2です。補足を拝見させていただきました。たくさんの条件で、難解な書式を組むんですね。まだ条件がよく理解できてませんが、ORやANDが使えれば一気に解決するのにな～と思い、なにげに条件にORを書いてみましたところ、見事に反映され補足の書式に設定できました。以前は確かにORやANDは無視されてたんですけど。。。バージョンは2003です。Excelがこわれてしまったんでしょうか・・・

補足

お疲れ様です。ご連絡ありがとうございます。
昨日の段階でクリアされた事は、

教えて頂いた式を入れて
１）100が連続して２回目以降と0.00が連続して３回目以降に「印」を付け区別できる事。
通常に条件付書式を利用して
２）条件付書式で　A列=>B列　「青」と　A列<B列　「赤」での色分け

上記２点はクリアされたのですが、問題が発生！
B列にある１回目の１００が「赤」で表示されたいのですが、A列<=B列である場合「青」の条件付書式が反映されます。＊0.00も同じような状況。

A列に影響を受けないようにB列の100や0.00を色又は印で区別する事は可能ですか？

私自身が関数と言うものを、良く理解していない・・・こんな風に式を作りたい！と思っても、それをどの関数に当てはめて良いのか？　久しぶりに頭をフル回転させたのですが。。。どうも空回り（？）の様で・・・　アドバイスを頂ける方に対して、もっと分かりやすく説明を出来るようにすることも、とても大切な事だと痛感しました。

>ORやANDが使えれば一気に解決するのにな～と思い、なにげに条件にORを書いてみましたところ、見事に反映され補足の書式に設定できました。
どんな式ですか？教えてください。

予断ですが・・・結局最終での帰宅になり・・・夢にまで数字や計算式が追いかけてきました（笑い）　本日は午前中他の仕事を先に終わらせ、午後から・・・エンドレス？？出来るまで、この仕事に取り組む次第です。

回答No.3

zap35

＃０１です
100が連続している１番目と２番目の行は「A列=B列　青」という条件と矛盾していますね。条件通りなら「青」のはずです。つまり100や0.00が出てきたら、先の条件とは別ルールになるのですか。それは質問や補足のどこを見たら分かるのでしょう？

＞ご理解いただけましたか？？
わかんないですよ。100または0.00という特定の数字に意味があるならはっきり提示してください。特別なルールがあるのは0.00と100だけですか？

もし「100が3回連続したら青」または「0.00が2回連続したら青」というような場合は条件付き書式ではできません。それは条件付き書式にはAND、ORのような複合条件を記述できないからです

記号でよいならC3セルに
=IF(AND(B3=100,B2=100,B1=100),"100 ３連続",IF(AND(B3=0,B2=0),"0.00 ２連続",""))
のような式を入れて下方向にコピーする手もありますが…

なかなか帰宅できずにお困りかもしれませんが、よりベターな回答を期待するなら質問を再度整理していただけませんか

お礼

ありがとうございました。
教えていただいた式を入力し条件付書式でも色分けが出来て、やった～完成！と思いきや・・・
説明不足の上塗り状態で・・・
100が３回以上連続と0.00が２回以上連続した部分の式を入れそれは、上手く行きました。（ありがとうございます）
しかし、、
100が2回まで0.00が1回の部分を当初「赤」で表示させようとしていたのですが、今の状態であると100が2回まで0.00が1回の部分が「青」で表示されてしまいます。（A列>=0.00とA列<1=00の場合「青」）＊条件付書式が優先されるのか？
お礼のはずが・・・更なるお願いになってしまいましたが、
＊100が2回まで0.00が1回の部分を「赤」で表示させるには、更にどうするか教えてください。
説明が不足でしたら、それも含めご返答願います。

補足

すみません・・　なんかパニクッてしまい・・・
えっと、再度指示の内容の確認をしたところ、

優先順位としては、ご指摘の部分

＞ご理解いただけましたか？？
わかんないですよ。100または0.00という特定の数字に意味があるならはっきり提示してください。特別なルールがあるのは0.00と100だけですか？

この2点がポイントです。
ご指摘頂いたとおり、「100が3回連続したら青」または「0.00が2回連続したら青」がという部分が　重要な様です。

IF関数を教えて頂きましたが、条件付書式を設定していても、（条件付書式とは別に）式を入れても大丈夫なんですか？

教えていただいた式を入れ　更に通常の条件付書式で<=>は作成する。って事ですよね？

どう表現して良いのか・・・　zap35さんのおっしゃる通りです。。。
式を入れて・・・・トライしてみます。

回答No.2

vista2446

Excelのご質問でしょうか？Excelの条件付き書式は3つの条件まで書式を設定できます。3つを越える条件を設定したい場合は、数式を利用します。B列のセルに関数が組み込まれていても、その関数がエラーにならない限り、必ず値が返ってきます。どのような関数を組んでいるのかわかりませんが、落ち着いて考えてみましょう。また、優先順位も考慮して条件付き書式を設定してください。それにしても「連続する2回目以降」とはどういう意味なんでしょう？

補足

ありがとうございます
補足をさせていただきます。
例えばB列に=(SUM(E16:E17)/SUM(D16:D17)*100)の式が入っていて
A列　　　　　B列　　　　　C列
44.83　　　　59.09　　　　青　　
62.62　　　　0.00 　　 赤
58.76 　　 39.87　　　　赤
11.36 　　 0.00　　 　　赤　
0.00 　　0.00　　　　 青
55.78 　　 55.78　　　　青
67.55 　　 100.00　　　 赤
45.73 　　 76.77　　　　青
100.00 　　 100.00　 　　赤
100.00 　　 100.00　　　 赤
100.00 　　 100.00 　　 青
の様な状態にしたいのです。　
優先順位としては、100や0.00での色又はマーク付け
その後通常の色分け・・・なんですが・・・
ご理解いただけましたか？？
更なるアドバイス願います。。。
社長から「まだ出来ないの？」って言われ・・・出来なきゃ帰れそうにもありません・・・（とほほ）