【Excel】最下行の値を表示する方法
- Excel2013を使用して、特定の条件を満たした最下行の値を表示する方法について教えてください。
- 具体的には、Sheet1にある表の中でA列が特定の値である最下行のN列の値をSheet2のA1セルに表示させたいです。
- 質問者さんの例では、A列が1234である最下行のN列の値(6,000)をSheet2のA1セルに表示したいということです。
- ベストアンサー
【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です。 よろしくお願いします。
- KOH3193
- お礼率92% (140/151)
- Excel(エクセル)
- 回答数7
- ありがとう数7
- みんなの回答 (7)
- 専門家の回答
質問者が選んだベストアンサー
>上記のような表が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で確定します。
その他の回答 (6)
- kagakusuki
- ベストアンサー率51% (2610/5101)
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)),""))
お礼
何度もご回答いただき、恐縮です…。 同じ結果を求めるのに、関数によっていろんな方法があるのだなと改めて感じた次第です。 お時間を割いていただき、ありがとうございました。
- kagakusuki
- ベストアンサー率51% (2610/5101)
作業列を使わずに、関数のみによって処理を行う方法です。 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関数の処理の重さが配列数式と同程度ということは知りませんでした。 丁寧に説明してくださり、ありがとうございました。
- kagakusuki
- ベストアンサー率51% (2610/5101)
もし、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列に入力されているデータが数値だけである場合には、次の様な方法もあります。 数値だけですので、こちらの方法でもできそうです。 時間を見つけて試してみたいと思います。 再度の回答ありがとうございました。
- kagakusuki
- ベストアンサー率51% (2610/5101)
今仮に、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)),"")
お礼
お礼が遅くなり申し訳ありません。 教えていただいた数式で、できました。 先の回答とはまた違った方法で、勉強になります。 回答ありがとうございました。
- tom04
- ベストアンサー率49% (2537/5117)
こんにちは! 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行程度と思われますので、作業列を使わないこちらの方法でも大丈夫そうです。 回答ありがとうございました!
- keithin
- ベストアンサー率66% (5278/7940)
=INDEX(Sheet1!N:N,MAX(IF(Sheet1!A1:A999=1234,ROW(Sheet1!A1:A999)))) として,「必ず」コントロールキーとシフトキーを押しながらEnterで入力します。
お礼
お礼が遅くなり申し訳ありません。 教えていただいた数式で希望通りできました。 配列数式…。以前、少し触れたことがあったものの使用する機会がなかったので、勉強になりました。 回答ありがとうございました。
関連するQ&A
- Excel2007で最下行のコピーについて
Excel2007で最下行のコピーについてなんですが、上手くいきません。そこで質問させて頂きます。 sheet3のセルO6、セルP6、セルQ6から下に向かって5000行目までに格子と関数が既に入ってます。 が、しかしコピーをしたい数値は今のところはO、P、Qの6行目にとどまっています。なので最下行はセルOPQの6行目になります。 sheet3のセルOPQの最下行数値をsheet4のセルABCの2行目にコピぺしたいです。 そこで作ってみました。基軸と言いますかsheet1を選択してから標準モジュールに Sub test() Worksheets("sheet3").Select Dim n As Long,t As Long n = Cells(Rows.Count,"O").End(xlUp).Row + 1 t = Cells(Rows.Count,"Q").End(xlUp).Row + 1 Range("O" & n, "Q" & t).Value.Select Selection = Worksheets("sheet4").Range("A2:C2").Value End Sub と書きましたがエラーになります。どの様すればよろしいですか?お願い致します。
- ベストアンサー
- Excel(エクセル)
- Excelにて複数条件を満たす行の特定のセルの値を返す関数
Excelにて複数条件を満たす行の特定のセルの値を返す関数を作りたいのですが、上手く行きません。 <Sheet1> A列 B列 C列 1 2008/6/1 ★ 320 2 2008/6/1 ☆ 300 : : : Sheet2のA1に、「A列が2008/6/1で、B列が☆の場合、C列の値を返す」という関数を入れたいのですが、どうも上手く行きません。 良い式がありましたら教えて下さいm(_ _)m
- 締切済み
- オフィス系ソフト
- 【エクセル】複数条件に該当する値をかえす方法
エクセルで表を作成していますが、どうしても上手くできません。 どなたかお分かりになりますでしょうか。 エクセル 列A 列B 列C ---------------------------------- 1 個人 新規 2000 (円) 2 法人 新規 3000 (円) 3 法人 新規 2500 (円) 4 個人 既存 1000 (円) 5 その他 新規 500 (円) 列A、列Bはそれぞれ固定値が入ります。 列Cはすべての値(定まっていない値)が入ります。 列A、列Bをそれぞれ条件として、該当する場合列Cの 値を別シートにかえしたいのです。 例)列Aが「個人」、列Bが「新規」の場合、 列Cの値をかえす 関数を使って =IF(AND(A1=個人,B1=新規),"C1","") とやってもエラーになってしまいます。 別シートには表を作成してあり、あるセルに列Cの 値のみ反映させたいのですが、うまくいきません。 同シート内でやっても上手くいかないので、数式そのものに 問題があると思っています。 上手く説明が出来ているか自信がありませんが、 解決できる方法はありますでしょうか。 どうぞ宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- 関数が入っている列で値のある最後のセルの値を返す
お尋ねします。 関数が入っている列で式の値が入っている一番下のセルの値を調べるにはどうしたら良いでしょうか? 例 A列 1 5 2 10 3 13 4 (式の値が無いのでゼロ) B1セルにA3セルの13と言う値を返したいのですが、列に式(SUMPRODUCT関数)が入っていて、実際はA4セルのゼロが返ってきます。 またシート1~シート5 まで上記と同じ式が入っていて、シート1のA列の最後の値「13」をシート6のB1セルに値を返す場合、INDIRECT関数を使った場合、どういう式になるでしょうか? ご回答よろしくお願いします。
- ベストアンサー
- Excel(エクセル)
- 【Excel】行と列で値を検索したい
こんにちは Sheet1に表があります。 Sheet2のA列と1行に値を入力したときに、 Sheet1の交わる値を表示したいのですが、 Sheet2のB2、C2、B3、C3の式を教えて下さい。 Excel2013です。 宜しくお願いいたします。
- 締切済み
- Excel(エクセル)
- エクセルでの関数を使った条件抽出方法について
エクセル初心者です。 エクセルでの関数を使った条件抽出方法についてどなたかご教授願います。 よろしくお願いします。 Sheet1の特定のセルに対して、Sheet2中にある条件に一致するセルの値を 表示させる方法がわかりません。 <具体的内容> 次のような2つのシートがあります。 Sheet1 ------------------------- A B C 1 2 2 費目1 3 費目2 4 費目3 ------------------------- Sheet2 ------------------------- A B C D 1 2 1 2 3 3 費目2 100 200 300 4 費目1 30 10 20 5 費目3 150 350 250 ------------------------- Sheet1のC1のセルと同じ数値をSheet2の2:2行から探して、 一致する数値に該当する列の3~4行目の値を、 Sheet1のA2:A3の費目とSheet2のA3:A5の費目とが 一致するようにして、Sheet1のB2:B3に表示したいです。 このときSheet1のB2~B3にはどのような関数式を入れたらよいですか? 上の場合だと、Sheet1のB2=10、B3=200、B4=350となってほしいです。 HLOOKUP、COLUMNなど組み合わせてみたのですが、 全くうまくいきません。簡単ではないのでしょうか? どなたか教えてください、よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- excel2003でデータをピックアップして表示
excel2003でデータをピックアップして表示 excelでA2からN300までの表があります。 M列(M3からM300まで数値)の値が1000以上のものだけ表のデータを表示したいのですがどうすればいいか。
- ベストアンサー
- オフィス系ソフト
- エクセルで別シートの値を取得したいです。
エクセルで質問があります。 別シートからセル値を取得したいです。 ただ、セル番地は行番号、列番号で指定し、 行、列番号はセルに入力してある値を使いたいです。 例 Aシート(値格納シート) B1セル値=1000 -------------- Bシート(値取得シート) A列=行番号入力 B列=列番号入力 C列=Aシートの取得関数 A2セル値=1 B2セル値=2 C2セル=1000(取得値) C2セルの関数に、A2とB2の値を使って Aシートのセル値を取得したいです。 よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- 【Excel】2つの値のどちらかを抽出する
A、Bのセルがあり、どちらのセルにも文字列が入っていたり、入っていなかったりとします。 そこでセルCに、A、Bどちらかの文字列を入れたいと考えたとき、どのような関数が考えられますでしょうか? 条件は以下のようにします。 (1)A,Bとも文字列か、空白のどちらか (2)Aの方が優先される。A,Bのどちらも値が入っている場合はA、Aが空白で、Bに値が入っている場合はBの値がCに反映される。 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- 複数行の最下行の範囲選択
こんにちは、マクロ初心者です。 A B C D E F G H I 1 あ あ あ あ あ あ 2 あ あ あ あ あ あ 3 あ あ あ 4 あ あ 5 あ あ 6 あ あ 7 あ あ 8 あ あ 9 あ 10 上記のような表があるとき セル"A"列から"I"列までの最下行にあたる行を検索し、 更に最下行までの範囲をコピーして、別シートにコピーさせたいのです。 (上記の例だと、セル"A1"~"I9"の範囲をコピーする) Range("A1", Range("I1").End(xlDown)).Select Selection.Offset(0, 0).Select 上記のコードでできると思ったのですが、なぜか不必要な範囲まで選択されてしまいます。 (下方向に数行余計に選択されてしまう) これではダメなのでしょうか? ちなみに、数式などは入っておらず、入力されていないセルは全て空白です。 A列だけの最下行を選択するというのは簡単に出来たのですが、このように複数の範囲を 選択する場合は、どのようにコードを書くべきでしょうか。 マクロのスキルが無い為困っています。 アドバイスをよろしくお願いいたします。
- ベストアンサー
- Visual Basic
お礼
お礼が遅くなり申し訳ありません。 教えていただいた数式で希望通りの結果を得られました。 質問投稿前にINDEX関数とMATCH関数の組み合わせで試行錯誤しましたので、MATCH関数での回答もいただけて、嬉しい限りです。 回答ありがとうございました。