エクセルで最大値を求める

質問

B列の最大値を求めたいのですが、条件が一つあります。
A列の値が１０以下の場合はカウントしないようにしたいのです。

例
A　　B
６　　２０
６　　５８
１２　６
１１　３
１　　１００

この場合求めたい最大値は６になります。
これを既存の関数かユーザー定義関数でやりたいのですが、やり方を教えてください。

回答No.6

end-u · ベストアンサー

ちなみに？

>｛｝の表現をユーザー定義関数で書く..
『表現』？
何がやりたいのですか？

最大値は単独の答えでしょうから、
ユーザー定義関数では単独の値を返すようにコードを書けば良いだけです。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
配列である必要はありません。

ユーザー定義関数のコード内で{}と同じ考え方で処理したいという意味なら
配列変数を使えば良いです。
>｛｝でくくってもエラーになりました
コードを見ないと直しようもありません。

ユーザー定義関数の引数に配列を与えたいならユーザー定義関数をセルに入力して
確定する時に[Ctrl]+[Shift]+[Enter]です。

セルに配列数式をセットする[Ctrl]+[Shift]+[Enter]自体をマクロにするなら
Subプロシージャです。FormulaArrayプロパティを使います。

回答No.5

end-u

例示のデータで
=MAX(IF(A1:A5>10,B1:B5,""))
と入れてあるとします。

数式バーの中でマウスを使って
『　A1:A5>10　』
だけを選択して[F9]キーを押してみてください。

{FALSE;FALSE;TRUE;TRUE;FALSE}
と表示されます。
確認できたら[Esc]キーで解除してください。

同様に
『　B1:B5　』は
{20;58;6;3;100}
と表示されます。

『　IF(A1:A5>10,B1:B5,"")　』は
{"";"";6;3;""}
です。

これが配列です。
この{"";"";6;3;""}のMAXを取り出せばいいわけです。
紹介したページの関連ページ
http://pc.nikkeibp.co.jp/pc21/special/hr/hr1.shtml
>配列は数式の計算の中で一時的に使われる“作業用セル”みたいなものなんです

この配列を使った数式から答えを取り出す時は
>配列数式を確定するときは、『Enter』キーだけじゃなく、『Ctrl』キーと『Shift』キーを押しながら、『Enter』キーを押して確定するんです。
>そうすると数式が { } で囲まれて、配列数式だってことがわかるようになっているんです
{=MAX(IF(A1:A5>10,B1:B5,""))}

補足

ありがとうございます。
ちなみに｛｝の表現をユーザー定義関数で書く場合にはどうしたらよいでしょうか。｛｝でくくってもエラーになりました

回答No.4

end-u

掛けずに、普通にMAX(IFの配列数式を使うと良いです。
http://pc.nikkeibp.co.jp/pc21/special/hr/hr3.shtml

補足

=MAX(IF(A1:A5>=10,B1:B5,""))

と入れてみましたが＃VALUE！とでてうまくできません。

回答No.3

emaxemax

No1です。
よく考えてみたら、先ほどの回答は対象に必ず正（プラス）の数がある場合のみ有効でした。
必ず正の数があるかどうかわからない場合はかなり厄介な計算になります。

また、ついでなので最大値だけでなく最小値も求めてみました。
整理すると以下のとおりです。

●A列のセルが10を超える行のB列の最大値

・対象（A列のセルが10を超える行のB列）に必ず正の数がある場合
=SUMPRODUCT(MAX((A1:A5>10)*(B1:B5)))

・対象に必ず正の数があるとは限らない場合
=SUMPRODUCT(LARGE((A1:A5>10)*(B1:B5),(SUMPRODUCT((A1:A5>10)*(B1:B5>0))=0)*COUNTIF(A1:A5,"<=10")+1))

●A列のセルが10を超える行のB列の最小値

・対象に必ず負（マイナス）の数がある場合
=SUMPRODUCT(MIN((A1:A5>10)*(B1:B5)))

・対象に必ず負の数があるとは限らない場合
=SUMPRODUCT(SMALL((A1:A5>10)*(B1:B5),(SUMPRODUCT((A1:A5>10)*(B1:B5<0))=0)*COUNTIF(A1:A5,"<=10")+1))

回答No.2

imogasi

(1)MAXIF関数は無い
Googleででも「EXCEL　MAXIF」で照会すれば、多数の記事がでて、本質問の答えになっている。
（２）配列数式
（３）SUMPRODUCTで配列数式使用を回避
（４）作業列に条件以外の行は0をだし、その列でMAXをとる。
（５）ユーザー関数
１行ずつ読んで条件以外は読み飛ばし、条件に該当する行のもののみ、「以前の行での最大値」と比べ大きければ
置き換えるコードを作るだけ。
（６）フィルタ操作のオプション指定を使って、A列で「１０　より大きい」を表示させておいて
A,B列以外のセルで　=SUBTOTAL(4,$B$2:$B$100)　　第１引数の４がMAXを指定
ーー
単一関数で出来るのでSUMPRODUCTが普通の回答かな。

回答No.1

emaxemax

=SUMPRODUCT(MAX((A1:A5>10)*(B1:B5)))
では？