数字データ表で、条件を満たす場合にサイン表示

質問

以下のような内容を関数や条件付き書式で実現したいのですが、どのようにすればいいでしょうか。

添付画像のようにAB列のデータとDE列のデータがあります。
表のデータ内で調べたい条件はG列とH列に記載した内容です。
A列の中でデータを１行目から順に見ていき、最初に545以上になったセルにピンク色を付けたいです。そして、B列は535以下になったセルに水色を付けたいです。ただし、B列の場合は検索範囲は１行目からではなくて、A列で一番最初に545以上になったA7より下の８行目以降の範囲だけで検索したいです。そして、B8からB15の中で一番最初に535以下になるのはB11なので、ここに水色を付けたいです。
そして、このGH列の条件を満たした場合はB17にTRUEと表示したいです。

また、DF列のデータは上記と同じ条件での検索ですが、D7が545以上に最初になるセルですが、E8からE15までの範囲には535以下になるセルがありません。
このようなケースではE17にFALSEと表示したいです。

以上のような事がやりたいのですが、どのような方法が適していますか。
よろしくお願いします。

回答No.10

kagakusuki

回答No.8です。
　別法として、回答No.8の条件付き書式で設定する数式を、

=ROW()=MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)

の代わりに、

=AND(COUNT($G$2,A1)=2,A1>=$G$2,COUNTIF(A$1:A1,">="&$G$2)=1)

にする事も出来ますし、

=ROW()=MATCH(SMALL(INDIRECT("R"&MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)+1&"C:R"&ROW(B$15)&"C",FALSE),COUNTIF(INDIRECT("R"&MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)+1&"C:R"&ROW(B$15)&"C",FALSE),"<="&$H$2)),INDIRECT("R"&MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)+1&"C:R"&ROW(B$15)&"C",FALSE),0)+MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)*(MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)<ROW(B$15))

の代わりに、

=AND(COUNT($H$2,B1)=2,B1<=$H$2,COUNTIF(INDIRECT("R"&MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)+1&"C:RC",FALSE),"<="&$H$2)=1,ROW()>MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0))

にする事も出来ます。

お礼

先ほど、回答No8で教えていただいた方法だとうまくいかないケースがあると補足コメントに記載しましたが、こちらの方法なら問題なく出来ました。

ありがとうございました。

回答No.9

bunjii

＞教えていただいた方法は、B列の検索対象範囲がB７～B15となりますが、
質問の要件を一部見落としていたようです。
大変失礼致しました。

>教えていただいたセルの対象範囲をどのように直せばB８～B15が対象になりますか。
少々面倒な数式になります。
幾つかの論理数式を加算しますのでN関数を省略します。
J1、K1、L1、M1の数式はそのままで良いでしょう。
J2=(AND(A2>=G$2,MAX(J$1:J1)=0))+(SUM(J$1:J1)>0)+(MAX(J$1:J1))
K2=(AND(MAX(J$1:J2)>1,B2<=H$2,MAX(K$1:K1)=0))+MAX(K$1:K1)+(MAX(K$1:K1)>0)
L2=(AND(D2>=G$2,MAX(L$1:L1)=0))+(SUM(L$1:L1)>0)+(MAX(L$1:L1))
M2=(AND(MAX(L$1:L2)>1,E2<=H$2,MAX(M$1:M1)=0))+MAX(M$1:M1)+(MAX(M$1:M1)>0)

>最初のMAX関数は＝や＞なども付いていませんが、どのような条件を表現しているのでしょうか。
論理演算では数値の「0」がFALSEで「0以外の値」がTRUEを返しますので「>0」を省略しても同じ結果が得られます。
厳密には「<>0」や「>0」を付加した方が良いかも知れません。

尚、条件付き書式での数式も誤りがありましたので訂正します。
=J1=1 → =$J1=1
=K1=1 → =$K1=1

お礼

bunjii　様

丁寧な解説をしていただきありがとうございました。
教えていただいたやり方で無事に出来ました。

ありがとうございました。

回答No.8

kagakusuki

別にマクロも作業列も配列計算も必要ありません。

　次に、以下の様な操作を行い、A1:A15のセル範囲に条件付き書式を設定して下さい。

A1セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=ROW()=MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にあるピンク色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄に入力されているセル範囲の設定を

$A$1:$A$15

に変更（カーソルとマウスの左ボタンによる範囲選択が使えます）
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　次に、以下の様な操作を行い、B1:B15のセル範囲にそれぞれ条件付き書式を設定して下さい。

B1セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=ROW()=MATCH(SMALL(INDIRECT("R"&MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)+1&"C:R"&ROW(B$15)&"C",FALSE),COUNTIF(INDIRECT("R"&MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)+1&"C:R"&ROW(B$15)&"C",FALSE),"<="&$H$2)),INDIRECT("R"&MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)+1&"C:R"&ROW(B$15)&"C",FALSE),0)+MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)*(MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)<ROW(B$15))

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある水色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄に入力されているセル範囲の設定を

$B$1:$B$15

に変更（カーソルとマウスの左ボタンによる範囲選択が使えます）
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　次に、A1:B1のセル範囲をコピーして、D1:E15のセル範囲に"書式のみ貼り付け"て下さい。
　次に、B17セルに次の関数を入力して下さい。

=ISNUMBER(1/COUNTIF(INDEX(B:B,MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)+1):B$15,"<="&$H$2)/(MATCH(LARGE(A:A,COUNTIF(A:A,">="&$G$2)),A:A,0)<ROW(B$15)))

　次に、B17セルをコピーして、E17セルに貼り付けて下さい。

　以上です。

補足

ご回答ありがとうございます。

丁寧に操作の流れを記載していただきありがとうございました。

教えていただいた方法を実行してみましたが、G２の条件が545だとうまくいくのですが、G２を他の数字に変更するとうまく出来ません。

例えば、条件をG２が543、H2が540に変更すると、A5がピンクに、B7が水色になります。
しかし、B６が539なので、すでにH2の条件を満たしているため、ここが水色になるようにしたいです。

どこを修正すればよろしいでしょうか。

回答No.7

imogasi

#2回答の補足に関して
メッセージボックスの表示は、１歩１歩、要所で確認するために、このコーナーの回答では
私は多用しています。
本質問の私の回答では、第1列において、第1条件の初めて５４５以上になった「セルの行番号」をメッセージボックスで表示して、自分で、ここまではコードが間違いなさそうだ、ととりあえず自分で納得するものです。なくても良いものですし、自分が納得したらそのコード行を抹消しても良い。
ーー
あとで思い付いた、VBAでなぜやった方がよいか？について補足
プログラムや関数を知らない、小学生でもこの問題をやらすと、A列の上の行から１セルづつ、５４５以上でないかと下方向に調べていくと思います。
その後はB列に視点を移して１セルずつ判定していくでしょう。
そのやり方そのものをVBAコード化したものが本件です。
だから人間の本性に沿った、思いつきやすい方法と思います。
ーー
またこれをユーザー関数化して、あたかもエクセルの関数のようにするのも、多少手を加えるとできます（略）。そうすると=HANTEI(545,453）のような式をセルに入れることになります。５４５などは引数といいます。この値は使うつど自由に指定するようにできます。
しかし大原則として、エクセル関数では、直接セルの書式を左右することは、仕組み上、MSは応じていません。しかし条件付き書式で条件指定に関数を使うことは多用されます。

回答No.6

bunjii

回答No.3の別解として作業テーブルを使わない方法を提示します。
A1:A15の条件付き書式で使用する数式を次のようにします。
=ROW()=MIN(INDEX((A$1:A$15>=G$2)*ROW(A$1:A$15)+(A$1:A$15<G$2)*16,0))
B1:B15の条件付き書式で使用する数式を次のようにします。
=ROW()=MIN(INDEX((B$1:B$15<=H$2)*ROW(B$1:B$15)+(B$1:B$15>H$2)*16+(ROW(B$1:B$15)<=MIN(INDEX((A$1:A$15>=G$2)*ROW(A$1:A$15)+(A$1:A$15<G$2)*16,0)))*16,0))
同様にD1:D15とE1:E15の条件付き書式の数式も其々次のように設定します。
D列
=ROW()=MIN(INDEX((D$1:D$15>=G$2)*ROW(D$1:D$15)+(D$1:D$15<G$2)*16,0))
E列
=ROW()=MIN(INDEX((E$1:E$15<=H$2)*ROW(E$1:E$15)+(E$1:E$15>H$2)*16+(ROW(E$1:E$15)<=MIN(INDEX((D$1:D$15>=G$2)*ROW(D$1:D$15)+(D$1:D$15<G$2)*16,0)))*16,0))

B17については次の数式で結果を得られます。
=MIN(INDEX((B$1:B$15<=H$2)*ROW(B$1:B$15)+(B$1:B$15>H$2)*16+(ROW(B$1:B$15)<=MIN(INDEX((A$1:A$15>=G$2)*ROW(A$1:A$15)+(A$1:A$15<G$2)*16,0)))*16,0))<16
D17も同様に次ぐの数式で良いでしょう。
=MIN(INDEX((E$1:E$15<=H$2)*ROW(E$1:E$15)+(E$1:E$15>H$2)*16+(ROW(E$1:E$15)<=MIN(INDEX((D$1:D$15>=G$2)*ROW(D$1:D$15)+(D$1:D$15<G$2)*16,0)))*16,0))<16

これらは配列計算で論理演算を基本とした正非の判定です。
一般的に複数条件の論理演算ではOR関数やAND関数を使いますがOR=加算、AND=乗算と言う論理がありますので配列内の論理演算では加算や乗算を使うことで数式を簡素化できます。
今回の回答ではINDEX関数の第1引数（範囲）の中で配列の要素を複数条件の論理演算で処理し、その結果を配列のままMIN関数へ渡すために行番号を0に指定しています。
INDEX関数の性質では行番号や列番号を0にするか無指定にすると配列の値を戻しますので他の関数の前処理用に使うことができます。
作業用のテーブルを使えないときは数式が複雑で長くなりますが利用価値はあると思いますので知識として記憶されると良いでしょう。

お礼

ご回答ありがとうございます。

新しく教えていただいた、作業用テーブルを使わないこちらの方法の方が少ないスペースで出来るので、ありがたいです。

数式の意味も丁寧に記載していただきありがとうございました。とても分かりやすかったです。

回答No.5

tsubu-yuki

私ならどうするかなぁ、で考えてみました。
おそらく、作業列をどこかに用意して使うことになるでしょう。
作業列を煩わしく思わないのであれば、基本の関数で充分です。
というわけで、Ａ：Ｂ列のデータに対してＪ：Ｋ列に作業用の列を用意しました。

作業用のＪ列は、
> 最初に545以上になったセルに
とのことなので、
　Ｊ１セル：=IF(A1>=$G$2,ROW(),"")
という式を作って、行方向にコピーしてみます。
単純に、Ａ１がＧ２以上なら行番号を、未満なら空白を返す式です。

続いて作業用のＧ列は
> A列で一番最初に545以上になったA7より下の８行目以降の範囲
> 535以下になったセルに
とのことなので、
　Ｋ１セル：=IF(AND(SUM($J$1:J1),B1<=$H$2),ROW(),"")
として、行方向にコピーです。
中身は「Ｊ１からＪ列の自分の行までの合計が０以外、
　　　　かつ、Ｂ列がＨ２セル以下」を満たすときに行番号を返す式です。
下準備完了です。

Ａ列およびＢ列に条件付き書式を設定します。
それぞれ、作業列が対応するように、条件付き書式の「数式を使用して」
　Ａ１セル：=ROW(A1)=MIN(J$1:J$15)　　　（塗りつぶし：ピンク）
　Ｂ１セル：=ROW(B1)=MIN(K$1:K$15)　　　（塗りつぶし：水色）
を設定し、まとめてコピー、必要な範囲に書式を貼り付けてやります。

Ｂ１７セルにＴＲＵＥ／ＦＡＬＳＥを表示する件、結論から先に。
　Ｂ１７セル：=AND(COUNT(J1:J15)<>0,COUNT(K1:K15)<>0)
という式を入れてやります。
特に難しくなく、「Ｊ列もＫ列も数値の数が０でない」ならＴＲＵＥを、
「どちらか一方でも数値が入っていない」ならＦＡＬＳＥを返します。

Ｄ：Ｅ列に関しても、同様に設定してやります。
　※並んでいるなら、コピー貼り付けで問題ないです。

範囲等はおそらくフェイクだと思いますので、
範囲などの参照先は実際の環境に合わせて組み替えましょう。

補足

ご回答ありがとうございます。

数式の意味も丁寧に記載していただきありがとうございました。

あと、私の説明が分かりにくかったため、一点だけ補足を記載いたします。

教えていただいた方法は、B列の検索対象範囲がB７～B15となりますが、希望としてはA列でヒットした行の１段下になる８行目のB８～B15の範囲だけを検索対象としたいです。

教えていただいたセルの対象範囲をどのように直せばB８～B15が対象になりますか。

回答No.4

skp026

複雑な条件は、式を使いますが、
今回の要望は式では、できる気がしないですし、
条件付き書式で対応できる範囲外です。

理由は、
「GH列の条件を満たした場合はB17にTRUEと表示」
「このようなケースではE17にFALSEと表示」
これらの点です。
B17をC17、E17をF17と変えるなら、
それぞれの列で判定してtrueとfalseを表示する、
そのような式で可能です。

「8行目以降～」については、ROWとINDIRECTでできます。
INDIRECTの例。
http://www.excel.studio-kazu.jp/func/indirect.html
ROWは、ROW()でセル自身の行数が取得できますから、
INDIRECTと組み合わせて8行前のセル値を取得します。

どうしてもtrue、falseの列を変えたくないなら、
ExcelVBA(マクロ)で値を書き換えることになります。
(私はデータ値の書き換えには賛成はできませんが、、、)

現実的には、取り消し線や太字など、
それっぽい表現で乗り切ることもあるようです。

回答No.3

bunjii

>以上のような事がやりたいのですが、どのような方法が適していますか。
比較の論理が面倒なので作業用のテーブルを使う方が良いでしょう。
J1:M15にチェック用テーブルを作成します。
J1=N(A1>=G$2)
J2=N(AND(A2>=G$2,MAX(J$1:J1)=0))
J2セルを下へJ15までコピーします。
K1=N(AND(MAX(J$1:J1),B1<=H$2))
K2=N(AND(MAX(J$1:J2),B2<=H$2,MAX(K$1:K1)=0))
K2セルを下へK15までコピーします。
L1=N(D1>=G$2)
L2=N(AND(D2>=G$2,MAX(L$1:L1)=0))
L2セルを下へL15までコピーします。
M1=N(AND(MAX(L$1:L1),E1<=H$2))
M2=N(AND(MAX(L$1:L2),E2<=H$2,MAX(M$1:M1)=0))
M2セルを下へM15までコピーします。
A1:A15を選択して条件付き書式で数式を使ってセルの書式を設定します。
数式は次のように設定します。
=J1=1
塗りつぶしの色はピンクとします。
B1:B15を選択して同様に数式を使う条件付き書式を設定します。
=K1=1
塗りつぶしの色を水色にします。
D列とE列もA列とB列に準じて条件付き書式でセルの塗りつぶしを設定します。
B17 セルへは次の数式を設定します。
=SUM(J1:J15)>0
E17セルへは次の数式を設定します。
=SUM(M1:M15)>0
以上で完了です。

お礼

ご回答ありがとうございます。

ご丁寧に教えていただきありがとうございました。教えていただいた方法で出来ました。
MAX関数は使ったことが無かったのですが、このように使うものなのですね。
また、SUM関数はこれまで合計の計算にしか使用した事がなかったのですが、このような式でTRUEとFALSEが表示できるとは知りませんでした。
大変、勉強になりました。

ありがとうございました。

補足

私の説明が分かりにくかったため、補足です。

教えていただいた方法は、B列の検索対象範囲がB７～B15となりますが、希望としてはA列でヒットした行の１段下になる８行目のB８～B15の範囲だけを検索対象としたいです。

教えていただいたセルの対象範囲をどのように直せばB８～B15が対象になりますか。

あと、作業用テーブルの関数の意味について質問です。

K2=N(AND(MAX(J$1:J2),B2<=H$2,MAX(K$1:K1)=0))

N関数の中にある三つの条件をすべて満たす時が１で、違う場合に０が入力されるようですが、MAX(J$1:J2)という条件はどのような意味なのでしょうか。後半のMAXは＝0がついているので、最大値が0の場合という意味だと理解できたのですが、最初のMAX関数は＝や＞なども付いていませんが、どのような条件を表現しているのでしょうか。

お手数をかけして申し訳ありませんが、よろしくお願いします。

回答No.2

imogasi

取り消すまえの質問で考えたが
下記のようなことでしょう。
判定条件など、こちらで勝手に設定したが、下記＞８０は＞５４５、＞１５０は＜５３５に修正しすればよいのかな。
色も適当に好みのものにかえてください。
Interior.ColorIndex = ｘｘの部分。
Googleで「ＶBA　ＣｏｌｏｒＩｎｄｅｘ」で照会のこと。
ーー
標準モジュールに
Sub test01()
'--第１条件判定
For i = 2 To 10
If Cells(i, "B") > 80 Then
Cells(1, "B") = i
Cells(i, "B").Interior.ColorIndex = 6
GoTo p1
End If
Next i
'--第２条件判定
p1:
MsgBox i
For j = i To 10 ' 第I条件充足の行から下へ判定
If Cells(j, "C") > 150 Then
Cells(1, "C") = j
Cells(j, "C").Interior.ColorIndex = 8
Exit Sub
End If
Next j
End Sub
ーー
質問の表現の仕方に注文あり
＞AB列のデータ
ー＞Ａ列のデータとB列のデータについて　とか
ー＞A列、B列について
と書くべき。
AB列のように書くと、その２文字の列記号があれば、その１列を指してしまう。
ーー
なぜVBAを使うのを勧めるか？
上記のように、各列について思った通りをIF文で判定すれば仕舞です。
関数でやるとなると、
「A列で指定した値を超える最初のセルを見つける」、という問題がたまに質問されるが、
WEBで調べればわかる。Googleで
「エクセル　関数　指定した値を超える最初のセル」
http://okwave.jp/qa/q5229028.html　など
回答は複雑で、わかりにくいものになる。配列数式など使ったものさえある。
だから避けたい。

補足

ご回答ありがとうございます。

教えていただいたVBAの内容を修正したのが以下です。

Sub test01()
'--第１条件判定
For i = 1 To 10
If Cells(i, "A") > 545 Then
Cells(1, "A") = i
Cells(i, "A").Interior.ColorIndex = 6
GoTo p1
End If
Next i
'--第２条件判定
p1:
MsgBox i
For J = i To 10 ' 第I条件充足の行から下へ判定
If Cells(J, "B") > 535 Then
Cells(1, "B") = J
Cells(J, "B").Interior.ColorIndex = 8
Exit Sub
End If
Next J
End Sub

直したのは"B"を”A"に、"C"を"B"に。
あと、80を545、150を535にしました。

それで実行してみたら、メッセージウインドウが起動して「11」という数字が表示されました。
これは条件が合致した場合に「11」と表示がされるような記述なのでしょうか。ちなみに、セルに色は付きませんでした。

何か他に直すべき所がありますか？

回答No.1

okwtun

一般的な「条件付き書式」はすべてを対象としてしまうので
私であればマクロで対応します。
それほど複雑な条件では無いので５分もあれば完成すると思いますが。

補足

ご回答ありがとうございます。

マクロで条件付き書式を作成する方法が分からないのですが、何か参考になるページをご存知でしたら教えてください。

よろしくお願いします。