【Excel】条件を満たした最下行の値を表示

こんにちは。

Excel2013を使用しています。

《Sheet1》
　　　A　　　　　B　　　　　C　　　　・・・・・・　　　　N
1　　1234　　　○　　　　01/01　　　　　　　　　　2,000
2　　1234　　　○　　　　01/30　　　　　　　　　　5,000
3　　2345　　　△　　　　01/05　　　　　　　　　　1,000
4　　3456　　　□　　　　01/20　　　　　　　　　　3,000
5　　3456　　　□　　　　01/25　　　　　　　　　　4,000
6　　1234　　　○　　　　02/10　　　　　　　　　　6,000
7　　3456　　　□　　　　02/15　　　　　　　　　　5,000


上記のような表がSheet1にある場合、A列が1234(数値)である最下行のN列の値をSheet2のA1セルに表示させたいのですが、関数で可能でしょうか？
この場合はSheet2のA1セルに表示される値は6,000、同様にA列が2345(数値)である最下行のN列の値の場合は1,000です。

よろしくお願いします。

No.6 ベストアンサー 回答者： bunjii 回答日時： 2014/01/24 20:58

>上記のような表がSheet1にある場合、A列が1234(数値)である最下行のN列の値をSheet2のA1セルに表示させたいのですが、関数で可能でしょうか？

可能です。
検索値が検索対象範囲に複数ある時はVLOOKUPで検索すると最初に見つかった行を返しますので目的に合いません。
検索対象のキー列（A列）が昇順または降順でソートされていればMATCH関数で目的の行番号を検出できます。
しかし、ランダムに配置されていますのでIF関数を使ってキー列の前処理を行います。
IF(Sheet1!A:A=C2,ROW(A$1:A$10000),"") のように配列を返すように設定すればMAX関数で一致する行番号の最大値が引き出せます。
また、IF(Sheet1!A:A=C2,C2,"") のようにすればMATCH関数で同じ結果が得られます。
得られた行番号を使ってSheet1!のN列から目的の値を取り出せます。

=INDEX(Sheet1!N$1:N$10000,MATCH(C2,IF(Sheet1!A$1:A$10000=C2,C2,"")))

=INDEX(Sheet1!N$1:N$10000,MAX(IF(Sheet1!A$1:A$10000=C2,ROW(A$1:A$10000),"")))

最大行番号は必要に応じて変更してください。
過大にすると再計算のときに応答が遅くなります。

Excelの関数で返り値を配列にするには数式バーに入力カーソルが有る状態でCtrl+Shift+Enterで確定しなければなりません。
今回の数式はIF関数の返り値を配列とする必要があるためCtrl+Shift+Enterで確定します。

この回答へのお礼

お礼が遅くなり申し訳ありません。

教えていただいた数式で希望通りの結果を得られました。
質問投稿前にINDEX関数とMATCH関数の組み合わせで試行錯誤しましたので、MATCH関数での回答もいただけて、嬉しい限りです。

回答ありがとうございました。

お礼日時：2014/01/27 14:34

No.7 回答者： kagakusuki 回答日時： 2014/01/25 00:26

　SUMPRODUCT関数を使用した回答No.5とはまた別の方法です。

Sheet2のA1セルに次の関数を入力しますと、Sheet1のA列が1234(数値)である最下行のN列の値が、Sheet2のA1セルに表示されます。

=IF(COUNTIF(Sheet1!$A:$A,1234),INDEX(Sheet1!$N:$N,SUMPRODUCT(ISNUMBER(ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A))))*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))-ROW(Sheet1!$A$1)+1),1234)<COUNTIF(Sheet1!$A:$A,1234)))+ROW(Sheet1!$A$1)),"")


　因みに、Sheet1のA列において、「Sheet2のA2セルに入力されている値」と同じ値が入力されている最下行のN列の値を、Sheet2のB2セルに表示させる場合には、Sheet2のB2セルには次の関数を入力します。

=IF($A2="","",IF(COUNTIF(Sheet1!$A:$A,$A2),INDEX(Sheet1!$N:$N,SUMPRODUCT(ISNUMBER(ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A))))*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))-ROW(Sheet1!$A$1)+1),$A2)<COUNTIF(Sheet1!$A:$A,$A2)))+ROW(Sheet1!$A$1)),""))

この回答へのお礼

何度もご回答いただき、恐縮です…。

同じ結果を求めるのに、関数によっていろんな方法があるのだなと改めて感じた次第です。

お時間を割いていただき、ありがとうございました。

お礼日時：2014/01/27 11:14

No.5 回答者： kagakusuki 回答日時： 2014/01/24 18:47

　作業列を使わずに、関数のみによって処理を行う方法です。

Sheet2のA1セルに次の関数を入力しますと、Sheet1のA列が1234(数値)である最下行のN列の値が、Sheet2のA1セルに表示されます。

=IF(COUNTIF(Sheet1!$A:$A,1234),INDEX(Sheet1!$N:$N,SUMPRODUCT(MAX((Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A))=1234)*ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))))),"")


　因みに、Sheet1のA列において、「Sheet2のA2セルに入力されている値」と同じ値が入力されている最下行のN列の値を、Sheet2のB2セルに表示させる場合には、Sheet2のB2セルには次の関数を入力します。

=IF(ISNUMBER($A2),IF(COUNTIF(Sheet1!$A:$A,$A2),INDEX(Sheet1!$N:$N,SUMPRODUCT(MAX((Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A))=$A2)*ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$A:$A)))))),""),"")


　尚、SUMPRODUCT関数は配列数式ではありませんが、配列数式と同様に処理が重くなりやすい関数です。
　ですから、上記の関数は「配列数式を使って処理が重くなる事が、大きな問題とはならない」という場合において、一々、「Shift＋Ctrlキーを押しながらEnterキーで確定」という操作が必要になる配列数式を使うくらいなら、処理の重さは配列数式と同程度ではあるものの、「Shift＋Ctrlキーを押しながらEnterキーで確定」という操作を必要としない、関数を使って結果を表示させるための方法の1つであるとお考え下さい。
（尤も、配列数式でも、「Shift＋Ctrlキーを押しながらEnterキーで確定」という操作を必要とせずに、結果が表示される様にする方法は存在しますが）

この回答へのお礼

何度もご回答いただき、ありがとうございます。

SUMPRODUCT関数は今回実際に使用するデータの中で、他の集計のために使っている関数のひとつでもあります。
SUMPRODUCT関数の処理の重さが配列数式と同程度ということは知りませんでした。

丁寧に説明してくださり、ありがとうございました。

お礼日時：2014/01/27 11:01

No.4 回答者： kagakusuki 回答日時： 2014/01/24 17:57

　もし、Sheet1のn列に入力されているデータが数値だけである場合には、次の様な方法もあります。

　今仮に、Sheet3のA列を作業列として使用するものとします。
　まず、Sheet3のA1セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(COUNTIF(INDEX(Sheet1!$A:$A,ROW()+1):INDEX(Sheet1!$A:$A,ROWS(Sheet1!$A:$A)),INDEX(Sheet1!$A:$A,ROW())),"",INDEX(Sheet1!$N:$N,ROW())))

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。
　その上で、Sheet2のA1セルに次の関数を入力しますと、Sheet1のA列が1234(数値)である最下行のN列の値が、Sheet2のA1セルに表示されます。

=IF(COUNTIF(Sheet1!$A:$A,1234),SUMIF(Sheet1!$A:$A,1234,Sheet3!$A:$A),"")


　因みに、Sheet1のA列において、「Sheet2のA2セルに入力されている値」と同じ値が入力されている最下行のN列の値を、Sheet2のB2セルに表示させる場合には、Sheet2のB2セルには次の関数を入力します。

=IF($A2="","",IF(COUNTIF(Sheet1!$A:$A,$A2),SUMIF(Sheet1!$A:$A,$A2,Sheet3!$A:$A),""))

この回答へのお礼

＞ もし、Sheet1のn列に入力されているデータが数値だけである場合には、次の様な方法もあります。

数値だけですので、こちらの方法でもできそうです。
時間を見つけて試してみたいと思います。

再度の回答ありがとうございました。

お礼日時：2014/01/27 10:47

No.3 回答者： kagakusuki 回答日時： 2014/01/24 15:07

　今仮に、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA1セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(COUNTIF(INDEX(Sheet1!$A:$A,ROW()+1):INDEX(Sheet1!$A:$A,ROWS(Sheet1!$A:$A)),INDEX(Sheet1!$A:$A,ROW())),"",INDEX(Sheet1!$A:$A,ROW())))

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。
　その上で、Sheet2のA1セルに次の関数を入力しますと、Sheet1のA列が1234(数値)である最下行のN列の値が、Sheet2のA1セルに表示されます。

=IF(COUNTIF(Sheet3!$A:$A,1234),INDEX(Sheet1!$N:$N,MATCH(1234,Sheet3!$A:$A,0)),"")


　因みに、Sheet1のA列において、「Sheet2のA2セルに入力されている値」と同じ値が入力されている最下行のN列の値を、Sheet2のB2セルに表示させる場合には、Sheet2のB2セルには次の関数を入力します。

=IF(COUNTIF(Sheet3!$A:$A,$A2),INDEX(Sheet1!$N:$N,MATCH($A2,Sheet3!$A:$A,0)),"")

この回答へのお礼

お礼が遅くなり申し訳ありません。

教えていただいた数式で、できました。
先の回答とはまた違った方法で、勉強になります。

回答ありがとうございました。

お礼日時：2014/01/27 10:42

No.2 回答者： tom04 回答日時： 2014/01/24 14:54

こんにちは！

A列だけが検索対象で、他の列は無視してよいのですよね？
一例です。

↓の画像で右側がSheet2で、Sheet2のC2セルに検索値を入力するとします。

Sheet2のA1セルに
=IF(C2="","",INDEX(Sheet1!N1:N1000,MAX(IF(Sheet1!A1:A1000=C2,ROW(A1:A1000)))))

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のA1セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

※　とりあえず1000行目まで対応できる数式にしていますが、
データ量が極端に多い場合はおススメしません（3000行程度であれば問題ないと思います）
それ以上のデータがある場合、作業用の列を設ける等して対処した方が良いと思います。m(_ _)m

この回答へのお礼

お礼が遅くなり申し訳ありません。

教えていただいた数式で、できました。
実際に使用するデータは毎月増えるものの、最大で2,000行程度と思われますので、作業列を使わないこちらの方法でも大丈夫そうです。

回答ありがとうございました！

お礼日時：2014/01/27 14:30

No.1 回答者： keithin 回答日時： 2014/01/24 14:43

=INDEX(Sheet1!N:N,MAX(IF(Sheet1!A1:A999=1234,ROW(Sheet1!A1:A999))))

として，「必ず」コントロールキーとシフトキーを押しながらＥｎｔｅｒで入力します。

この回答へのお礼

お礼が遅くなり申し訳ありません。

教えていただいた数式で希望通りできました。
配列数式…。以前、少し触れたことがあったものの使用する機会がなかったので、勉強になりました。

回答ありがとうございました。

お礼日時：2014/01/27 10:34