エクセルでの条件付き書式の応用について教えて下さい

質問

こんにちは。
エクセルで表を書いていてどうしても分からないことがあったので質問させていただきます。

エクセルを使って、添付画像にあるような表Aを作りました。
これはあくまで見本なので要素を適当に入れてみたものです。
（実際は、表の縦軸と横軸を含む複数の変数からなる式をC4に入れて、オートフィルをして表を作成しています）
ここで、表Aは条件付き書式である値以下（例の場合は250）のセルにオレンジの色がつくように設定しました。

次に同じ横軸・縦軸からなる表Bを作成しました。
緑の色は説明の便宜上、セルを普通に塗りつぶしただけです。
この表Bも見本なので、要素は適当に入れてあるだけですが、
実際は表Aとは別の縦軸・横軸を含む複数の変数からなる式に代入して作成しています。
表Aと表Bの縦軸・横軸の数や要素数は同じです。

ここで教えていただきたいのが、
表Aでオレンジ色がついた部分に該当する表Bの部分（緑色の部分）の最大値は求めることができるのか、ということです。
画像の例の場合で言うと、B18などの空いている適当なセルに、
表Aのオレンジの部分と同じ箇所に該当する表Bの緑の部分の最大値（今の例の場合では60）を表示させたいのです。
表Aのオレンジの範囲は、表Aを作成するのに使う式の縦軸・横軸以外の外部のパラメータの変更によって、毎回変わることになります。

こういった事はマクロ等で実現が可能となるのでしょうか？
エクセルをまともに使い出して日が浅く、こういった場合はどうすればいいのか分からず、質問させてもらいました。
何とか解決したいです。どんな情報でも構いません。
どなたか教えていただければ幸いです。よろしくお願いします。

回答No.5

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

>=MAX(IF(C4:G8<=A4:A8,C12:G16))
>と入力すればよいのでしょうか？
良いと思うのですが求めたい結果と違ったのですか？

L11セルに =MAX(IF(C4:G8<=A4:A8,C12:G16))
[Ctrl]+[Shift] +[Enter] で確定({}で囲まれる

L13セル 横
=INDEX(C11:G11,SUMPRODUCT((C12:G16=L11)*{1,2,3,4,5}))
L14セル 縦
=INDEX(B12:B16,MATCH(L11,INDEX(C12:G16,,MATCH(L12,C11:G11,0)),0))
ただし、条件として
最大値に重複がないこと
C11:G11 に重複がないこと
B12:B16 に重複がないこと

もし最大値が重複する場合は、どのように表示するかを決めること

# あと、新たな質問は新たにスレを立て、
# このページをリンクさせておいたほうが良いと思う

お礼

二度目のご回答ありがとうございます。
=MAX(IF(C4:G8<=A4:A8,C12:G16))
の式で最大値を得ることができました！ありがとうございます。

ただ、最大値のセルのある表の位置を取得しようとして、
CoalTar様の見本のようにやってみたらできたのですが、
自分がいざ実際に使ってみたい表で試したところ、うまくいきませんでした。
横軸が0.5刻みで0～90までの180個、縦軸は0.5刻みで0～157個あったので、

=INDEX(C11:G11,SUMPRODUCT((C12:G16=L11)*{1,2,3,4,5}))

の式の表と軸の範囲を変更した後に、{1,2,3,4,5}の部分を{1,2,3…180}としてみましたが、
#N/Aと表示されてしまい、軸要素の取得ができませんでした。

ただ、これ以上は本来の質問内容と異なると思いますので、
問題点を整理した後にCoaltar様のご指摘通り、新しく質問という形をとらせていただきます。
度重なる質問にもお答え下さり、本当にありがとうございました。

回答No.4

KURUMITO

回答Ｎｏ２です。範囲に色を塗ることでご質問は終わりかと思っていました。勘違いをしておりました。
Ａ10セルからＤ12セルの間で色の塗られた範囲での最大値を求めるのでしたら、次のような作業用の表を作って答えを求めるのが簡単でしょう。
例えば、回答Ｎｏ2の条件でＡ20セルに次の式を入力してＤ20セルまでオートフィルドラッグしたのちに下方にも2行オートフィルドラッグします。

=IF(A1>=10,A10,"")

これで表Ｂでの該当するセルの値が表示されますので最大値は例えばＡ25セルに次の式を入力すればよいでよう。

=MAX(A20:D22)
..

お礼

色々なやり方があり非常に参考になります。
まだ、きちんと理解が追いついていませんが、しっかり理解して使えるようになりたいです。
ご回答ありがとうございました！

回答No.3

某HN クロメート(Chromate)（@CoalTar）

条件付き書式は必要ないのかなあ とか思いつつ、

K3セルに
=IF(D3<=250,D12)
右へ下へオートフィル(N8セルまで)
求める値は =MAX(K3:N8)

ちなみに作業用の表Cを作らない場合は
=MAX(IF(D3:G8<=250,D12:G17))
[Ctrl]+[Shift] +[Enter] で確定、配列数式です({}で囲まれる)

添付画像参照

補足

ご回答ありがとうございます。
確かに色をつけなくても、表Bの表Aに該当する範囲の最大値が分かればいいので、
条件付き書式は必ずしも必要ではないかもしれませんね。
そちらの方は#2の回答者様の方法で出来ましたのでこれを使っています。

IFで作業用の表を作って一段階間に表を挟んでやると凄く分かりやすくなりますね！
確かに条件を満たさないセルをFALSEにしておいて、MAXで最大値を求めてやれば簡単ですね。

こちらのやり方の場合、#1の回答者様への補足にもあるような、

>>今の条件だと表A全体に対して一つの条件(250以下の値)を設けていますが、
>>表Aの各行に対して条件を設けた場合、
>>（つまり、C4～G4の行は250以下、C5～G5の行は248以下…など）

といった形で条件を付けた際に、私の例の画像のエクセルに各行ごとの条件値をA4からA8に入れた場合、
CoalTar様の作業用の表を用いる方法では、作業用表の左上のセルに入れる文を
=IF(C4<=$A4,C12)
としてやって、これをオートフィルして表Aの各行に対する条件を考慮した作業用表を作り、
その表に対してMAX関数を使うと、表Aの行ごとに条件を設けた場合でもちゃんと最大値が求まると思うのですが、
作業用の表を作らない場合では、最大値を求めたいセルに
=MAX(IF(C4:G8<=A4:A8,C12:G16))
と入力すればよいのでしょうか？

また、#1の補足にもありますが、CoalTar様の方法で最大値を求めた場合、
表Bの最大値のあるセルに対応する横軸・縦軸の値を表示させたい場合はどうすればよいでしょうか。
（最大値のある座標E14を表示するのではなく、E14の場所に対応する表Bの横軸の値3と縦軸の値3という値です）
続けての質問になって申し訳ありませんが、よろしくお願いします。

回答No.2

KURUMITO

話を簡単にしましょう。
例えば表ＡがＡ1セルからＤ3セルにあって、そこで10以上の数値に色を付けるとしたら、Ａ1からＤ3セルの範囲を指定してから条件付き書式の設定では、式として=A1>=10を入力しますね。
そこで表ＢがＡ10からＤ12の範囲にあって表Ａと同じように色を付けるとしたらＡ10からＤ12を範囲として指定したのちに条件付き書式の設定では、式として=A1>=10を入力します。
つまりは表Ａで設定した条件付き書式の式と表Ｂでの条件付き書式の式を同じにすればよいことになります。

お礼

ちゃんと表Bに自動的にセルの色がつくようになりました！
参照元の表の条件を同じにして、その条件を使う表の範囲をかえてやればいいのですね。
今まで条件付き書式は数式ではなく、「セルの値が…」の方ばかり使っていたのでこういう使い方は思いつきませんでした。

これで表Bにも表Aと同じ範囲に色を塗ることができます。
ご回答ありがとうございました！

回答No.1

mshr1962

条件が250以下として最大値でいいなら
=SUMPRODUCT(MAX((C4:G8<=250)*(C12:G16)))

最小値の場合は
=SUMPRODUCT(MIN(1000^(C4:G8<=250)*(C12:G16+1)))-1
※1000は集計範囲C12:G16の最大値より大きければOK
※+1,-1は最小値が0の場合の補正用これがないとC12の0が対象の値になる為

平均値だと
EXCEL2007以降なら
=AVERAGEIF(C4:G8,"<=250",C12:G16)
EXCEL2003以前では
=SUMIF(C4:G8,"<=250",C12:G16)/COUNTIF(C4:G8,"<=250")

補足

おお、これです。ありがとうございます！
SUMPRODUCT関数は初めて見ました。MAX関数と組み合わせるとこんなことが出来るのですね。

続けての質問で申し訳ないのですが、このやり方で表Bの最大値を求めた場合に、
表Bの最大値のセルの場所を特定するために、表Bの縦軸・横軸の値をセルに表示させたい場合はどうすればいいでしょうか。

また、今の条件だと表A全体に対して一つの条件(250以下の値)を設けていますが、
表Aの各行に対して条件を設けた場合、
（つまり、C4～G4の行は250以下、C5～G5の行は248以下…など）
この場合に各行ごとの条件値をA4からA8に入れたとすると、
最大値を表示したいセルに
=SUMPRODUCT(MAX((C4:G8<=$A4)*(C12:G16)))
と入れればいいのでしょうか？

続けざまの質問で大変申し訳ありませんが、よろしくお願いします。