関数について教えて！

質問

Excelの関数について教えてください。
大きな配列（例えば、B10:B20000）の中の最大値は、＝MAX(B10:B20000)　で求めることができますが、その最大値がB列の何行目にあるのかを知るための関数はどのようなものですか？　目で10行目から20000行目まで追うのが大変なので、簡単な関数で表示できれば、と思い質問します。なお、マクロを使えば可能なことは承知していますが、一緒に仕事をしている仲間がマクロを取扱えないので、できれば関数で表現したいのです。

回答No.1

noname#99913 · ベストアンサー

次の式を試してください。

=MATCH(MAX(B10:B20000),B10:B20000,0)+9

MATCH関数は、探す数の、配列内の相対的な位置を返します。ですので、行数を求める場合、B10からですので9を足します。

お礼

kozirou54様
適切かつ迅速な回答まことにありがとうございました。
早速、小さな配列を作って試してみました。正しく表示されました。
2週間前から悩んでいたのですが、おかげさまで解決しましたので、明日は仲間に披露できます。本当にありがとうございました。

回答No.4

bbit68

条件付き書式を使ってはどうでしょうか？
上位1位の書式を赤にするとか・・・。

回答No.3

imogasi

普通はMATCH関数を使います。違う関数利用は無いかと考えて
例　A2:A8に数が有るとする。
=SUMPRODUCT((A1:A8=MAX($A$1:$A$8))*ROW(A1:A8))
いずれも複数行に最高値が有る場合は適当な値が返らない。
ーー
MATCH関数も複数行に最高値が有る場合は１番目しか捕らえられない。
ーー
配列数式で（データ数２００００行が心配だが出来ると思う）
=LARGE(IF(A2:A9=MAX($A$1:$A$8),ROW(A2:A9),""),1)
と入れて　SHIFT+CTRL+ENTER
複数行に最高値が有る場合は、上から２番目
=LARGE(IF(A2:A8=MAX($A$2:$A$8),ROW(A2:A8),""),2)
と入れて　SHIFT+CTRL+ENTER
例データ　A2:A8
12
3
86
23
86　＜－－８６の上から２番目
86
で
６ （行目）

お礼

imogasi様
ご親切な回答ありがとうございました。
SUMPRODUCT関数の場合、最高値が1個の時は正解が得られましたが、2個の時は、両者の行数の積になりました。今回、複数の最高値の可能性が高いため、この方法は残念ながら使えそうもありません。
Large関数の場合は、計算結果が　#VALUE!　になってしまい、巧く計算できませんでした。ご指示あった、Control+Shift+Enter　で3個のキーを同時に押しても何の反応もありませんでした。私のExcelのバージョンが古いのでしょうか？
ちなみに、私のバージョンは、Excel 2000 (9.0.2812)　です。
ご親切に感謝します。

回答No.2

Wendy02

こんにちは。

=MATCH(MAX(B10:B20000),B:B,0)

>仲間がマクロを取扱えない
私もマクロ中心ですが、マクロでは、サブルーチンにする代わりにこういう処理が出てきます。

なお、数式をみてお分かりではないのかもしれまんせが、これは「配列」を扱っておりません。
配列では、このような数式が出来ません。数式で、配列は、セルの値があるなしに関わらず、指定したセルに対して検索や計算をしますが、セルの範囲では、その値があるところまでしか検索や計算をしません。つまり、B:Bにしても、最終行が、1万行目までしかなければ、1万行目までしか検索していません。

お礼

Wendy02様
適切かつ迅速な回答まことにありがとうございました。
早速、小さな配列を作って試してみました。正しく表示されました。
2週間前から悩んでいたのですが、おかげさまで解決しましたので、明日は仲間に披露できます。本当にありがとうございました。