![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
こんにちは。
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ベストアンサー
- 回答日時:
>上記のような表が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関数での回答もいただけて、嬉しい限りです。
回答ありがとうございました。
No.7
- 回答日時:
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)),""))
何度もご回答いただき、恐縮です…。
同じ結果を求めるのに、関数によっていろんな方法があるのだなと改めて感じた次第です。
お時間を割いていただき、ありがとうございました。
No.5
- 回答日時:
作業列を使わずに、関数のみによって処理を行う方法です。
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関数の処理の重さが配列数式と同程度ということは知りませんでした。
丁寧に説明してくださり、ありがとうございました。
No.4
- 回答日時:
もし、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),""))
![「【Excel】条件を満たした最下行の値を」の回答画像4](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/0/1235215_5497e699e7813/M.jpg)
> もし、Sheet1のn列に入力されているデータが数値だけである場合には、次の様な方法もあります。
数値だけですので、こちらの方法でもできそうです。
時間を見つけて試してみたいと思います。
再度の回答ありがとうございました。
No.3
- 回答日時:
今仮に、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)),"")
![「【Excel】条件を満たした最下行の値を」の回答画像3](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/f/1235215_5497eb9058bb9/M.jpg)
お礼が遅くなり申し訳ありません。
教えていただいた数式で、できました。
先の回答とはまた違った方法で、勉強になります。
回答ありがとうございました。
No.2
- 回答日時:
こんにちは!
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
![「【Excel】条件を満たした最下行の値を」の回答画像2](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/2/667667_5497e699309f0/M.jpg)
お礼が遅くなり申し訳ありません。
教えていただいた数式で、できました。
実際に使用するデータは毎月増えるものの、最大で2,000行程度と思われますので、作業列を使わないこちらの方法でも大丈夫そうです。
回答ありがとうございました!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Visual Basic(VBA) if関数とifs関数は組み合わせることはできますか。 セルA1が「A」のとき「向日葵」と表示。 セル 4 2023/02/02 20:48
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) vba 隣のセルに値がある行だけ関数をコピー&ペーストしたい A1 100001 A2 100002 1 2023/01/28 14:29
- Excel(エクセル) 【Excel関数】値が合致するセルの隣のセルを表示させたい 8 2022/10/12 17:44
- Excel(エクセル) Excelにの以下の設定方法について教えてください! C列にデータ入力の設定をしています。(出、入を 3 2022/06/22 01:33
- その他(プログラミング・Web制作) python文字化けエラーが発生しているようです 3 2022/04/13 19:41
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/15 08:30
- Excel(エクセル) 関数を用いて表示したセルの内容を、見えている形でコピーする方法 2 2022/09/14 16:36
- Excel(エクセル) エクセルの数式で教えてください。 5 2023/02/10 15:11
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
条件に一致する最終行の値をエクセル関数で抽出する方法
Excel(エクセル)
-
Excelで、条件と一致する最後のセルを検索したい
Excel(エクセル)
-
エクセルで空白セルを含む列の最終行の値を取得する式を教えてください
Excel(エクセル)
-
-
4
Excel関数で、範囲内の最後のセルを得る方法
Excel(エクセル)
-
5
条件付き書式で最終行に線を引きたい
Excel(エクセル)
-
6
VBA データ(特定値)のある最終行を取得したい
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】for next構文について
-
エクセルで表
-
PDFの請求明細をエクセルにしたい
-
職場の人から聞かれており、こ...
-
ユーザー定義関数をアドイン登...
-
下記マクロでMsgBox "空白です...
-
エクセルでバーコード作成し使...
-
在庫管理表に使うエクセルの関...
-
Excel関数-文字列で自動作成さ...
-
エクセルの関数について教えて...
-
Excelデータをコピペして、ペー...
-
Excelで50個のセルに同じ文字を...
-
Excel 2019 は、SPILL機能があ...
-
Microsoft Officeの中古は信用...
-
エクセルの表で1年間の曜日を...
-
エクセルで会社の従業員のデー...
-
スプレッドシート、Excelでの数...
-
[オートフィルタ]で抽出された...
-
エクセルでB列でフィルターをか...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報