• ベストアンサー

関数について教えて！

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

質問者が選んだベストアンサー

  • ベストアンサー
noname#99913
noname#99913
回答No.1

次の式を試してください。 =MATCH(MAX(B10:B20000),B10:B20000,0)+9 MATCH関数は、探す数の、配列内の相対的な位置を返します。ですので、行数を求める場合、B10からですので9を足します。

Simonm
質問者

お礼

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

その他の回答 (3)

  • bbit68
  • ベストアンサー率62% (5/8)
回答No.4

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

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.3

普通は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 で ６ （行目）

Simonm
質問者

お礼

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

  • Wendy02
  • ベストアンサー率57% (3570/6232)
回答No.2

こんにちは。 =MATCH(MAX(B10:B20000),B:B,0) >仲間がマクロを取扱えない 私もマクロ中心ですが、マクロでは、サブルーチンにする代わりにこういう処理が出てきます。 なお、数式をみてお分かりではないのかもしれまんせが、これは「配列」を扱っておりません。 配列では、このような数式が出来ません。数式で、配列は、セルの値があるなしに関わらず、指定したセルに対して検索や計算をしますが、セルの範囲では、その値があるところまでしか検索や計算をしません。つまり、B:Bにしても、最終行が、1万行目までしかなければ、1万行目までしか検索していません。

Simonm
質問者

お礼

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

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • Excelにて列の一番下にある数値を表示する関数

    質問させていただきます。 エクセルの関数で「○列に入力されている数値の中で常に一番下にある数値を表示する」関数などありますでしょうか？ 例えば B列に数値を入力していき、一番下？最終行？にある数値を自動で表示させたいです。下の例では「80」をとあるセルに表示させたいです。 　A　　B　　C　　D　　・・・・・ １　　100 ２　　 99 ３　　150 ４　　 80 ５ ６ ・ ・ =MAXという関数は見つけることはできたのですが・・・ =MAXの関数では、最大値しか表示できないので・・・ 色々と調べてみたのですが、調べ方が悪いのか探し出せませんでした。 お手数ですがよろしくお願いします。

  • エクセル関数初心者です

    課題の資料に使うための、交通量を調べエクセル関数を使用して整理しています。 関数初心者で、参考書などで調べたり、こちらで似た質問を見つけて、参考にさせていただいたのですが、上手くいかないので、助けてくださいいただけたらと思います。 具体的に、下表の様に 　　 　A列　B列 日付　車台数/台 1行目　1日　　5　 2行目　2日　　4　 3行目　3日　　8　 4行目　4日　　2　 5行目　5日　　4　 の様に、３ヶ月分、車の交通台数を調査して表を作成しました。このデータの中で、B列の最大値にあたる日付を、別のセルに表示したいのですが、=INDEX(A1:A5,MAX(B1:B5))という式ではダメでしょうか？ やってみた所、本当は「3日」と表示されてほしいのに、まったく違う日付が出てしまいます。 最小値に対応する日付も求めたいのですが、こちらもMAXをMINに変えただけではだめでした。 本当に初心者で、検討違いの式を使っているのかもしれないのですが、 どなたか簡単な方法を教えてください。 どうぞよろしくお願いします。

  • エクセル関数で、ある検索条件で最大値を表示するには

    マクロの知識がないため、関数での対応方法を教えてください。 No.　A　B 1　　 1　0 2　　 1　1 3　　 1　2 4　　 1　3 5　　 2　0 6　　 2　1 7　　 3　0 8　　 3　1 9　　 3　2 ・・・ 上記データでA列が同じ数字なら、B列の最大値をC列に表示したいです。 この場合、A1～A4は1なので、B1～B4の最大値3をC1～C4に表示　と言う具合です。 これが1000行以上あります。 良い方法はございませんか？

  • エクセルと関数について

    エクセルと関数について 売店チケット 件数 料金 性別 6000 2 6000 男 6000 0 6000 女 上記のような表をエクセルで作成しました。 左からＡ列　売店チケット　Ｂ列　件数　Ｃ列　料金　Ｄ列　性別です。 売店チケットは3000円単位で、6000円だと2件になります。 でも、手打ち入力なので、ミスすることも多く、Ｂ列の0件はエラーで、正しくは2件なのです。 そこで質問なのですが、関数で もし、Ａ列に金額が入っていれば（ゼロ以外）、Ｂ列の0は数字上 おかしいので、エラーを表示するような数式を作りたいです. 例えば、Ｅ列の1行目に「エラーですよ」的な表示はまとめてできますか？ Ｂ列は1行目から3000千行目位までデータが入っているとします。 売店チケットに金額の表示があるのに、Ｂ列が一件でもゼロだと間違いなのです。 うまく表現できていないかもしれませんが、よろしくお願いします。

  • 二次元配列が、勝手に一次元配列になってしまう

    Excelのマクロで、二次元配列を格納したバリアント型を戻り値とする関数を作ったのですが、… 二次元型配列のひとつの要素数が1の時、受け取ったバリアント型変数は、一次元配列になっています。 列ベクトルなら、それもありかな～と思うのですが、なんで、行ベクトルまで、一次元配列にするんだよ～って、困っています。 シートから関数を呼んだ場合は、列ベクトルは列ベクトル、行ベクトルは行ベクトルとして、表示されるので、マクロの中で関数を呼び出した場合も、行列の情報を保持できる方法があるんじゃないかと思ったのですが。 どなたか、ご教示頂けるとありがたいです。 よろしくお願いします。

  • エクセルの配列関数の制限について

    エクセルの配列関数の制限について Ｖ列にＸ列・Ｙ列・Ｚ列・ＡＡ列・ＡＢ列・ＡＣ列・ＡＥ列・ＡＧ列・Ｐ列を参照した配列関数を 下のように入力しています。 {=SUM((Y$1:Y$500=F7)*(Z$1:Z$500=H7)*(AA$1:AA$500)*(P7="○"),(AB$1:AB$500=F7) *(AC$1:AC$500=H7)*(AE$1:AE$500=H7)*(AG$1:AG$500)*(P7="×"))} このＹ列・Ｚ列・ＡＡ列・ＡＢ列・ＡＣ列・ＡＥ列・ＡＧ列は、ぞれぞれ200行くらいしか 文字が入力されていない場合には、配列関数の結果がうまく表示されました。 これらのセルの200行以降から300行・400行と項目を増やしていったところ、 配列関数の結果がうまく表示されないセルが出てきました。 これは、配列関数の参照するセルに制限があるということでしょうか？

  • Excel 関数　

    Excelの関数の使い方についての質問です。 以下のようなファイルを作成しています。 ************************* 　日 　支出 1日 　\2000 2日 　\1000 3日 　\5000 4日 　\600 　・　　　・ 　・　　　・ 　・　　　・ ************************* "支出"という列の中から最大値（最高支出金額）を見つけ、その最大値を返した日付け（＝最も支出金額が高かった日）をあいているセルに表示（"日"という列から日付をひっぱってくる）させるにはどのような関数（またはマクロ）を組めばいいのでしょうか？？

  • エクセルの関数について教えてください

    先日もコチラで質問させていただき エクセルでの表作りを１から見直しております。 さて、次のような事が出来るのか、出来ればどのような関数（数式？）を使えばいいでしょうか？ ●maxとかminの関数で最大・最小値を引数にしますが、この最大・最小値を出した日付を引数にする事は出来るのでしょうか？ ちなみに表はベーシックな表にしています。１番上の行に見出しを右方向に、１番左の列に下に向かって日付を 以上、よろしくお願い致します。

  • エクセル関数

    A列　B列 エクセル初心者です。B列●の右に直前の空白のセルの数を表示する □　　　　　　　関数はありますか。これが2000行あるので困っています。式の意味 □　　　　　　　も教えていただけますか。 ●　　2 □ ● 1

  • エクセル関数　教えて下さい！

    エクセルでMAX関数で最大値を出した結果に、それがいったいどれなのかを合わせて 表示させたいときはどうしたらいいのでしょうか？初心者なもので、お分かりなられる方よろしくお願い致します。（例えばMaxが8で、それと同時にｄってことを表示させたいのでが。） a 5 b 4 c 6 d 8 e 1

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する