現在位置は
です

発言小町

新しいトピを作成
本文です

エクセルで複数のOR条件をマーキングするには?

ロロ
2018年2月27日 11:22

エクセルに詳しい方、ご教示願います。

概算時と精算時の差が一定以上のセルをマーキングしたいです。

例)概算時(A):★=200,000/☆=3,000,000/◆=450,000
精算時(B):★=1,500,000/☆=1,200,000/◆=400,000
(B)-(A):★=1,300,000/☆=-1,800,000/◆=-50,000

上記の条件だった場合、(A)と(B)の差が、1,000,000以上、つまり、★と☆のセルをマーキングしたいのです。
条件付き書式で、片方の条件(指定の値より大きい/小さい)であれば簡単に出来るのですが、(B)-(A)が1,000,000以上、または、-1,000,000以下、と言う2つの異なる条件のどちらかを満たすセルを抽出する関数がわかりません。
絶対値を使えばいいかなぁとも思いましたが上手く行かず。
全部同じマーキングで良いのですが欲を言えば、増えた時と減った時で色分け出来れば更にいいです。

どんな関数を使えば上手く行くでしょうか?

ユーザーID:5510178994  


お気に入り登録数:31
  • 携帯に送る
  • このエントリーをはてなブックマークに追加
古いレス順
レス数:22本

タイトル 投稿者 更新時間
条件付書式を2重に
たか
2018年2月27日 14:17

タイトル通りですが、条件付書式を2種類設定すれば良いのではないでしょうか。
2つの条件で違う書式の方が良いとのことですので、絶対値を使うよりも断然簡単でしょう。

「セルを選択」→「プラスの条件付書式を設定」→(セル選択はそのまま)→「マイナスの条件付書式を設定」
です。

ユーザーID:7107401748
条件付き書式を2つ作る
事務員
2018年2月27日 15:13

× 1つの条件付き書式にいくつかのルールを作る
○ 2つの条件付き書式を作る

A1セルに設定する場合
1つめの条件付き書式のルールを"A1>1,000,000"、書式を青
2つ目の条件付き書式のルールを"A1<-1,000,000"、書式を赤

あとは必要なセルに書式のみコピーでよろしいかと。

関数は必要ありませんよ〜

ユーザーID:8308613164
オフィス
ささ
2018年2月27日 15:51

オフィスいくつですか?
余程古いオフィスを使い続けてたりしなければ、ひとつのセルに条件付書式はひとつまでとは決まっていないので、片方ずつならできると言うあなたはもう既に対応できます。

例えばA3が計算結果のセルだとして、計算結果のセルに「条件付書式」-「ルールの管理」-「新規ルール追加」から、ひとつは「A3<=-1000000」で書式で好きなセル色、また新規ルール追加して「A3>=1000000」で別のセル色にすればいいです。
セルに「=A1-A2」とか入れれば勝手に代わります。
残りのセルにはそこをコピーして張り付ければいいです。

ユーザーID:4609770213
一つの条件でやろうとせずに
あやき
2018年2月27日 16:40

=OR((B)ー(A)>1000000,(B)ー(A)<-1000000)

ですが色も分けたいなら
同じ範囲で
(B)ー(A)>1000000で条件を指定して、条件追加で
(B)ー(A)<-1000000で違う色で条件指定したらいいと思います。

ユーザーID:1695188704
条件付き書式を追加 しては
ヒカリ
2018年2月27日 19:59

条件付き書式を追加 しては
セルの値が「次の値より大きい」 書式設定 100万以上

追加(A)

セルの値が「次の値より小さい」 書式設定 -100万以下

ユーザーID:9949975760
教えてくれる人はたくさんいると思いますが私も。
エクセラー
2018年2月28日 0:37

条件付き書式に辿り着いたのなら、もう少しです。おしい!
画像が貼れないので、下記説明分からなかったら条件付き書式をググるなどして調べてみてください。

前提条件:主様の例が、C6〜F8セルに以下のように入っている。

概算(A) |200000 | 3000000|450000
清算(B) |1500000| 1200000|400000
(B)-(A) |1300000|-1800000|-50000

以下手順。
1.D6〜F8を選択
2.ホームタブ→条件付き書式→新しいルール、を選択。
3.「数式を使用して〜」を選択
4.「次の数式を満たす場合に〜」の下の枠を選択し、カーソル点滅状態にする。
5.4の枠に「=D$8>1000000」と入力
6.「書式(F)」を選択
7.塗りつぶしタブ→好きな色を選択→OK、を選択
8.新しい書式ルール画面に戻るので、OKを選択

これでD列が選んだ色で塗られます。

同じように、
 9.上記1〜4を操作。
10.4の枠に「=D$8<-1000000」と入力
11.上記6〜8を操作。(塗りつぶす色を変える。)
で、E列が2色目の色で塗られます。

やるだけならここまでですが、一応後学のためのヒントを。
上記手順11まで終わったら、

条件付き書式→ルールの管理、を選択してみてください。
作った2つのルールが確認できます。

(a)「適用先」について
これが手順1で選んだ範囲です。
なので、手順1で適用範囲を間違えた場合は、ここで修正すればよいです。

今回の例で行くと、「=D$8>1000000」の条件を「=$D$6:$F$8」のセルで発動させる
の意味になります。

ヒント:「$」は固定の意。分からないときは「絶対参照」でググってください。
条件を「=$D$8>1000000」に変えてみると分かるかも。

字数が足りないので続く。

ユーザーID:1484273180
続き
エクセラー
2018年2月28日 1:00

(b)▼▲ボタンについて
これを押下すると、作った条件が上下に入れ替わります。

条件は上にあるほど優先度が高いです。

今回の例では、塗られる場所が被ることがありませんが、被る場合
上の条件の方が優先されて塗られます。

例えば、
今は、手順5の「=D$8>1000000」の方が下にあると思います。
これを「=$D$8>1000000」に変えると、本来はC〜F列がすべて下の条件の書式の色で塗られますが、
E列は上の条件が勝つため、E列だけ上の条件の書式で塗られます。

※条件を変えたあとは、「適用」ボタンを押下するのをお忘れなく。

このくらい理解しておけば、困ることはほぼないと思います。
では、頑張ってください。

ユーザーID:1484273180
んー携帯からの書き込み
あのこ
2018年2月28日 1:52

簡単すぎて迷いました!A−Bの答えのセルがそれ以上でよいのではないのでしょうか?不当数でしたっけ?それでよいのでは?違っていたらすみません!

ユーザーID:4494833873
えっと
事務
2018年2月28日 2:07

IF関数でネストするとか?

エクセル最近は勉強してないのでだいぶ忘れてしまいました。

ユーザーID:5304857380
条件付書式で数式を使う
あらら
2018年2月28日 3:26

まず、B-Aの計算された結果のセルは簡単ですね。

条件付書式を2つ作ればいいだけです。

B-Aの答えが1,000,000より大きいなら青。

B-Aの答えが-1,000,000より小さいなら赤。

という風に2つの条件付書式を作ればOKです。




その元となる2つの数。

例えば★でいうと200,000と1,500,000にも色を付けたい場合、条件式書式に数式を入れればいいんですよ。


セルA1・・・200,000
セルA2・・・1,500,000
セルA3・・・B-Aの結果の1,300,000

としましょう。

「数式を利用して〜・・・」という項目があるのでそれを使います。

まず一つ目の200,000、つまりセルA1に書式をつけます。

A1の条件付書式で「A3が1,000,000以上なら青」、という数式を入れます。

=A3>=1000000

これが式、色はお好きに。「>=」が以上という意味。

数式を入れるときA3をマウスでクリックすると、勝手に$がついてしまうので、それは削除しておきましょう。

あとでコピペで書式を他のセルに貼り付けたとき、$があると困ります。

同じく、マイナスの場合は、A1に「A3が-1,000,000以下なら赤」という数式を入れます。

=A3<=1000000

同じく$は消しておきましょう。「<=」が以下という意味。




200,000に、「A3が1,000,000以上のとき青」、と「A3が-1,000,000以下のとき赤」の2つを設定するんです。

1,500,000にも、「A3が1,000,000以上のとき青」、と「A3が-1,000,000以下のとき赤」の2つを設定するんです。

これで概算時も清算時もB-Aも、3つとも色をつけました。

あとは、コピペして他のセルに書式だけ貼り付ければOK。

ユーザーID:2233776082
出来ました〜
事務
2018年2月28日 9:46

IF関数のネストでは無理でした。すみません。

トピ主さんのレス通りに見出しから列A1から当て嵌めて表にした場合、

1まず、B-Aの結果を別の行(行3)に作ります。

2B1:B2を選択。

3条件付き書式の「数式を使用して、書式設定するセルを決定」を選択。

4「次の数式を満たす場合に値を書式設定」に

 =OR($B$3>=1000000,$B$3<=-1000000)

と記入。

塗りつぶしの色など書式を設定。

5これでB列はOKなので、同じ要領でC列の条件対書式を設定。(ちょっと手間)

ユーザーID:5304857380
思いつき
惟任
2018年2月28日 12:36

マーキングしたいセルの考え方を逆にしたらどうですか?

条件付書式で-1,000,000〜1,000,000の範囲内をマーキングさせる。
これなら条件は1つです。

これだと色分けは出来ないですが。

ユーザーID:4307493220
単純に
Pearl
2018年2月28日 13:28

概算がA1、精算がB1にあるとします。

1条件付き書式→新しいルール→数式を使用して、書式設定するセルを決定
 で
 =$B1-$A1>=1000000
 と入力し、書式を指定。

2設定完了後、続けてもう一度同じ操作をして
 -1,000,000以下の条件付き書式を設定。


一度にやろうとせず、2回に分けてやればわりと単純な考え方でできそうですが、如何でしょうか。

なお、$が余計に設定されていたり、""で勝手にくくられて文字列扱いになっていたりするので
上手くいかない場合は「ルールの管理」で確認して下さい。

ユーザーID:4086024447
関数ではありませんが
rere
2018年3月1日 23:25

同じセルに条件付き書式を2つ設定できます。
「指定の値より大きい」条件付き書式と「指定の値より小さい」条件付き書式を設定し、それぞれ違う色にすることで、色分けもできます。

ユーザーID:3450720892
excel関数
mmmm
2018年3月2日 11:21

a1に概算、b1に精算時のデータを入れたとして、
差が1,000,000以下なら変化なし、差が1,000,000以上なら*を印字するなら
=if(a1-b1>=1000000,"*",if(a1-b1=<-1000000,"*"," "))
という関数になります。

ユーザーID:1620360587
出来ました。有難うございました。
ロロ(トピ主)
2018年3月2日 16:11

トピ主のロロです。
使っているのは、office365 Excel2016です。
通常業務では会社独自のシステムを使っているので、エクセルを使用するのは比較表を作成する必要がある時くらいしかありません。
恥ずかしながら[条件付き書式]機能に気付いたのもつい最近でした。

条件付き書式>セルの強調表示ルールから「指定値より大きい」「指定値より小さい」を選んで希望通りの表が出来ました。
条件付き書式で複数の条件を選んだら後から選んだ条件で上書きされると思っていました。
なので、複数の条件の場合は新しいルールを関数で作成しなければならないと。

ご教示下さった皆様、有難うございました。

ユーザーID:5510178994
わ〜 皆さんすごい
事務
2018年3月2日 21:11

もっと簡単な設定があったのですね!思いつきませんでした。
大変勉強になりました。トピ主さん良いトピをありがとうございます。


やはり少し離れるとダメなものですね。。自分のレベルの低さを痛感していまいました。
最近おざなりで仕事をしていたなぁ、、と反省しきりです。
またぼちぼちエクセルの勉強を再開しようと思います。

ユーザーID:5304857380
間違い訂正
エクセラー
2018年3月3日 0:38

すみません。

以上、以下の条件だと
「=D$8>=1000000」
「=D$8<=-1000000」
ですね。(イコールが抜けてました。)

例題の数値では問題ありませんが、事務様のレス見てハッとしました。


続きの方のレスにて

本来はC〜F列がすべて下の条件の書式の色で塗られますが、 ←×
本来はD〜F列がすべて下の条件の書式の色で塗られますが、 ←〇


混乱させてしまい、申し訳ありません。

ユーザーID:5183430341
出来ました!有難うございました。
ロロ(トピ主)
2018年3月5日 15:21

トピ主のロロです。
先日もコメントしたのですが反映されていないようなので改めまして。

レスを下さった皆様、有難うございます。
色々、勉強になりました。
使っているのはOffice365 Excel2016です。

条件付き書式は1つしか出来ない(後から作成した条件に上書きされる)と思っていました。
精算と概算の差が大きい数値のセルを目立たせたかったのですが、rere様の方法で無事、抽出することが出来ました。

有難うございました。

ユーザーID:5510178994
事務様、ロロ様へ
エクセラー
2018年3月5日 22:29

TO 事務様

>自分のレベルの低さを痛感していまいました。

そんなことないと思います。

私は、事務様のやり方をみて、おお!このやり方があったか!
と目から鱗でした。

ゴールにたどり着く道は1つじゃないので、事務様のも立派な解だと思います。


TO ロロ様

解決されたようなので、もう見てないかもしれませんが。
ロロ様のやり方教えていただけないでしょうか?
書かれていることをやると、

〇:意図通りに塗られる、×:意図と異なる色で塗られる、△:塗られない

△、×、△
〇、×、△
〇、〇、△

になりません?

どうやってできるのでしょう?

ユーザーID:1484273180
 
現在位置は
です