![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
こんにちは。
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も見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで 自動的に◯や数字を...
-
エクセルでファイルの最終更新...
-
シフト表をエクセルで作るとき...
-
Excel 2019 [オプション]の[リボンのユ...
-
Excelに詳しい方! B列が「日...
-
Excelファイルが開けません
-
excel2013 MonthDays 関数が使...
-
スプレッドシートの関数につい...
-
【マクロ】2回実行したら、エ...
-
特定の文字列を含む、住所を抽...
-
EXCELの散布図で日付が1900年に...
-
エクセルのツールバーから数値...
-
Excelで表を作ったところに文字...
-
祝日と土曜、日曜の合計をカウ...
-
Excelについて
-
【マクロ】名前を保存する際に...
-
Excel分数の表示について
-
エクセルでCtrl+Tでテーブルの...
-
マイクロソフトのPADを使ってい...
-
【EXCEL】画像の黄色部分の抽出...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報