こんにちは。
エクセルで表を書いていてどうしても分からないことがあったので質問させていただきます。
エクセルを使って、添付画像にあるような表Aを作りました。
これはあくまで見本なので要素を適当に入れてみたものです。
(実際は、表の縦軸と横軸を含む複数の変数からなる式をC4に入れて、オートフィルをして表を作成しています)
ここで、表Aは条件付き書式である値以下(例の場合は250)のセルにオレンジの色がつくように設定しました。
次に同じ横軸・縦軸からなる表Bを作成しました。
緑の色は説明の便宜上、セルを普通に塗りつぶしただけです。
この表Bも見本なので、要素は適当に入れてあるだけですが、
実際は表Aとは別の縦軸・横軸を含む複数の変数からなる式に代入して作成しています。
表Aと表Bの縦軸・横軸の数や要素数は同じです。
ここで教えていただきたいのが、
表Aでオレンジ色がついた部分に該当する表Bの部分(緑色の部分)の最大値は求めることができるのか、ということです。
画像の例の場合で言うと、B18などの空いている適当なセルに、
表Aのオレンジの部分と同じ箇所に該当する表Bの緑の部分の最大値(今の例の場合では60)を表示させたいのです。
表Aのオレンジの範囲は、表Aを作成するのに使う式の縦軸・横軸以外の外部のパラメータの変更によって、毎回変わることになります。
こういった事はマクロ等で実現が可能となるのでしょうか?
エクセルをまともに使い出して日が浅く、こういった場合はどうすればいいのか分からず、質問させてもらいました。
何とか解決したいです。どんな情報でも構いません。
どなたか教えていただければ幸いです。よろしくお願いします。
No.5ベストアンサー
- 回答日時:
>=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
- 回答日時:
回答No2です。
範囲に色を塗ることでご質問は終わりかと思っていました。勘違いをしておりました。A10セルからD12セルの間で色の塗られた範囲での最大値を求めるのでしたら、次のような作業用の表を作って答えを求めるのが簡単でしょう。
例えば、回答No2の条件でA20セルに次の式を入力してD20セルまでオートフィルドラッグしたのちに下方にも2行オートフィルドラッグします。
=IF(A1>=10,A10,"")
これで表Bでの該当するセルの値が表示されますので最大値は例えばA25セルに次の式を入力すればよいでよう。
=MAX(A20:D22)
..
色々なやり方があり非常に参考になります。
まだ、きちんと理解が追いついていませんが、しっかり理解して使えるようになりたいです。
ご回答ありがとうございました!
No.3
- 回答日時:
条件付き書式は必要ないのかなあ とか思いつつ、
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
- 回答日時:
話を簡単にしましょう。
例えば表AがA1セルからD3セルにあって、そこで10以上の数値に色を付けるとしたら、A1からD3セルの範囲を指定してから条件付き書式の設定では、式として=A1>=10を入力しますね。
そこで表BがA10からD12の範囲にあって表Aと同じように色を付けるとしたらA10からD12を範囲として指定したのちに条件付き書式の設定では、式として=A1>=10を入力します。
つまりは表Aで設定した条件付き書式の式と表Bでの条件付き書式の式を同じにすればよいことになります。
ちゃんと表Bに自動的にセルの色がつくようになりました!
参照元の表の条件を同じにして、その条件を使う表の範囲をかえてやればいいのですね。
今まで条件付き書式は数式ではなく、「セルの値が…」の方ばかり使っていたのでこういう使い方は思いつきませんでした。
これで表Bにも表Aと同じ範囲に色を塗ることができます。
ご回答ありがとうございました!
No.1
- 回答日時:
条件が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)))
と入れればいいのでしょうか?
続けざまの質問で大変申し訳ありませんが、よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) セルに特定の色が出た時だけ、式を発動させたい 4 2022/06/17 10:32
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) 判定結果に応じて〇印(図形)をつけるマクロ 4 2022/10/30 11:22
- Excel(エクセル) エクセルの条件付き書式で*を使いたい 4 2022/05/13 16:49
- Excel(エクセル) エクセルで対象日に該当するデータがある場合に別表へ全対象者を表示させたい。 3 2023/07/12 09:48
- Excel(エクセル) エクセルで”入力シート”の文字書式の変更を”出力シート”で同じ文字書式で印刷したいです。VBA希望 4 2023/04/24 11:07
- Excel(エクセル) 【エクセル】COUNTIFの検索条件が可変する数字の場合の数式 1 2022/09/27 15:34
- Excel(エクセル) 「休日取得希望日」を申請したらシフト表に色付け 1 2022/04/17 14:22
- Visual Basic(VBA) Excel(VBA) 特定の条件に該当する行の値、書式を同じセルにコピ&ペーストしたいです 1 2022/05/21 18:18
- Excel(エクセル) エクセルの数式で教えてください。 5 2023/02/10 15:11
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelで年月日データから下二桁...
-
エクセルのセルが縦方向にのびる
-
エクセルのセルに「=A13」...
-
ワードの表の中に文字を入れる...
-
Excelで、セル内改行もそっくり...
-
エクセル 関数 セルの値が0...
-
エクセルの使い方について。 A...
-
【Excel】セルの中の文字の下の...
-
エクセルで特定のセルを表示の...
-
Excelの条件付き書式で、計算式...
-
Excelで来月以降の日付のセルを...
-
エクセルで文字の上に重ねがき...
-
横書きで縦の波線の書き方
-
concatenate関数で片方のセルの...
-
エクセルでセルに何も入力して...
-
Excelで土、日、祝日の色分けが...
-
セルA1とB1の数値が一致しな...
-
おねがいします>< エクセルで...
-
Excelで前ゼロを取る方法
-
エクセルで、文章の右端をそろ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelで年月日データから下二桁...
-
エクセルのセルに「=A13」...
-
ワードの表の中に文字を入れる...
-
Excelで、セル内改行もそっくり...
-
エクセルのセルが縦方向にのびる
-
エクセルで特定のセルを表示の...
-
エクセル 関数 セルの値が0...
-
Excelで来月以降の日付のセルを...
-
横書きで縦の波線の書き方
-
エクセルで文字の上に重ねがき...
-
エクセルでセルに何も入力して...
-
セルA1とB1の数値が一致しな...
-
Excelの条件付き書式で、計算式...
-
【Excel】セルの中の文字の下の...
-
Excel2007でセルに値があるはず...
-
勤務時間を10進法で合計を出したい
-
concatenate関数で片方のセルの...
-
Excelで前ゼロを取る方法
-
Excelで土、日、祝日の色分けが...
-
エクセルでシート保護してもフ...
おすすめ情報