こんにちは。
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),""))
> もし、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)),"")
お礼が遅くなり申し訳ありません。
教えていただいた数式で、できました。
先の回答とはまた違った方法で、勉強になります。
回答ありがとうございました。
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
お礼が遅くなり申し訳ありません。
教えていただいた数式で、できました。
実際に使用するデータは毎月増えるものの、最大で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ランキング
-
エクセルでの作業計算方法について
-
はがきについて。
-
エクセル 文字を増やしたい。
-
セルの内容表示が邪魔になる
-
Microsoft365に変えたのですが...
-
エクセルの計算
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
Excel ピボットテーブルで日付...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
Excelのチェックボックスの使い...
-
エクセル 白黒印刷で白線を印刷...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの条件付き書式につい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報