エクセルの関数を教えてください

質問

どういう関数を使えばよいのか分からずに困っています。ご存知の方教えて頂けないでしょうか。
ある列（例えばＡ３～Ａ５００）に入力されている数字の中で最大の数字を探す時には『ＭＡＸ（Ａ３：Ａ５００）』としますが、その列には平成２９年度（２９で始まる５ケタの数字）のものと平成３０年度（３０で始まる５ケタの数字）のものが混在しており、ＭＡＸ関数を使うと必ず平成３０年度のものが出てきます。
しかし、その中の２９年度のもの（２９で始まる５ケタの数字）で最大の数字を探したいのですが、その方法が分かりません。
どういう関数、または方法があるのでしょうか？
ご存知の方は教えて頂けないでしょうか？
よろしくお願いします。

回答No.2

bunjii · ベストアンサー

下記の数式を試してください。
=MAX(INDEX((A3:A500<30000)*A3:A500,0))
MAX関数の内側のINDEX関数は対象範囲から30で始まる値を0に置き換えるための配列値の前処理です。
しかし、回答No.1の配列数式のようにShift+Ctrl+Enterの同時打鍵の必要は無くEnterキーのみで確定できます。

お礼

ご解答頂きありがとうございました。
ＩＮＤＥＸと組み合わせると良かったのですね！
ＭＡＸもＩＮＤＥＸも知ってましたが、組み合わせるとこうなるなんて夢にも思いませんでした！
作業列を作らず、出来るだけシンプルな数式（関数）でしたいな、と思っていましたので、bunjiiさんのご解答を見た時、『これだ！』と思いました。
すごく嬉しかったです！
明日会社に行ってから早速組み入れたいと思います。
ご解答頂き、本当にありがとうございました。

回答No.4

imogasi

MAXIF関数は無いようです。
今は（２００７以後）MAXIFS関数があります。それを使うのがよいと思う。
しかし
https://dekiru.net/article/14292/
にあるように、３６５ユーザー以外では使えないようです。＃NAMEエラー。
ーーー
作業列を使ってよい場合
B列に　IF(LEFT(A2,2)="30","",MID(A2,3,5)*1)　
のような式を入れて、先頭３０のついたデータは空白化して、B列でMAXをかけるのはどう？
仕事で忙しいとき、こういうところへ質問して、３－４日かけるより、能率的だと思うがな。
ーー
配列数式を使う場合
A列は数字が入っている場合　例2902365
=MAX(IF(A2:A9<2999999,A2:A9,""))　と入れてSHIFT+CTRL+ENTERキー同時押し。
で求まる。
文字列の場合は式が変わる。
=MAX(IF($A$3:$A$9<"2999999",($A$3:$A$9)*1,""))
と入れてSHIFT+CTRL+ENTERキー同時押し。

お礼

お忙しいところご回答いただきありがとうございました。
エクセルのバージョンは書いてないわ、こんな夜中に「助けてください！」みたいに仕事のことを質問してくるくわで、『こいつ何やってんのかな』と思われたかもしれませんね。
すみませんでした。
実は家のＰＣから質問させていただきました。
私も数日は掛かるかな？と思っていたのですが、こんなにも早く４件もご解答を寄せて頂き、本当に感謝です！
imogasiさんのご解答にあるように作業列を作れば出来るのは分かっていたのですが、そうしたくなかったので質問させていただきました。
幸い作業列を作らない方法も分かりましたので、そちらを使わせて頂こうと思っています。
また、「配列数式」もなじみが薄いため、保守的で心配性の私としては普通の数式がありがたいです。
ご解答頂き本当にありがとうございました。

回答No.3

mt2015

Excelが2016以降でしたらMAXIFS関数が使えます。
=MAXIFS(A3:A300,A3:A300,"<30000")

Excelのバージョンによって使える関数が増えていますので、質問の際にはExcelのバージョンを書いてください。

お礼

ご解答頂きありがとうございました。
そうですね。エクセルのバージョンを書いてませんでした。済みません。
エクセルのバージョンは２０１０です。
ですので、ＭＡＸＩＦＳという関数は見当たりませんでした。
２０１０になって（だと思いますが・・・）ＣＯＵＮＴＩＦＳやＳＵＭＩＦＳを見つけた時には「そうだよ！これが欲しかったんだよ！」って思いましたが、ＭＡＸＩＦＳも良いですね！
何だか、エクセルの関数の方からどんどん私のような素人に近づいて来てくれているようで非常に有難いです。
ごちゃごちゃ書いてしまって済みません。
ご解答頂き、本当にありがとうございました。

回答No.1

mdmp2

答えを出すセルを選択して数式バーにつぎのように入力して、shift+ctrl+enter します。
=MAX(IF((A3:A500)<30000,(A3:A500)))
すると、数式がつぎのように変わります。
{=MAX(IF((A3:A500)<30000,(A3:A500)))}

｛｝でくくられます。これは配列式であることを表しています。
A3～A500 の数字の中で、30000 以下の数字の最大値が得られます。

初めから{=MAX(......................)} と入力してもダメみたいです。

お礼

こんなに早くご解答頂き、本当にありがとうございました。
質問しておきながら、ご解答をいただいた時には私は眠っておりました。
もし私が起きていて、mdmp2さんのご解答をリアルタイムで読んでいたら、もしかしたらベストアンサーを付けて解答は締め切っていたかもしれません。
質問をした後、ＭＡＸ関数に関する他の方の質問や、それへの解答をいくつも見ていたのですが、その時に「配列式」というのを知り、「（配列式は）何か難しそうだけど、でも、仕方ないかな・・・」と思っていましたので、採用させて頂いていたかもしれません。
そう考えると、私にとっては丸一日見られなかったことが返って良かったのですが、でも、こんなに早くご解答頂いていたのにmdmp2さん（他の皆さんにもそうですが）にはお礼が遅くなってしまい、申し訳ありませんでした。
早速ご解答頂き、本当にありがとうございました！