• ベストアンサー

グループ最終行のデータを別シートに抽出をしたい

エクセル2013使用しています。 シート4、A列に(1~10)グループ番号がついたデータがランダム入っています。 シート1、H3にグループ番号1を選択時、シート4の該当するグループ、 I列の最終データのみを、シート1 I3に抽出したい。 シート1、H3グループ番号は選択できるようにしています。 関数を用いてする、方法を 教えてください 添付写真;シート4 グループ番号1の最終データはAAA110となり、シート1 I3セルにAAA110を表示させたい 説明不足で申し訳ありません!!

質問者が選んだベストアンサー

  • ベストアンサー
  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.2

 今仮に、Sheet4のA2~A9の範囲にグループ番号が入力されていて、Sheet5のA列を作業列として使用して、Sheet1のH3セルに入力されているグループ番号に該当する「Sheet4のI列の値」を、Sheet1のI3セルに表示させるものとします。  まず、Sheet5のA2セルに次の関数を入力して下さい。 =IF(INDEX(Sheet4!$A:$A,ROW())="","",IF(COUNTIF(Sheet4!$A$1:INDEX(Sheet4!$A:$A,ROW()),INDEX(Sheet4!$A:$A,ROW()))=COUNTIF(Sheet4!$A:$A,INDEX(Sheet4!$A:$A,ROW())),INDEX(Sheet4!$A:$A,ROW()),""))  次に、Sheet5のA2セルをコピーして、Sheet5のA3~A9のセル範囲に貼り付けて下さい。  その上で、Sheet1のI3セルには次の関数を入力して下さい。 =IF(AND($H3<>"",COUNTIF(Sheet5!$A:$A,$H3)),INDEX(Sheet4!$I:$I,MATCH($H3,Sheet5!$A:$A,0)),"")  尚、もしも作業列を使用したくない場合には、Sheet5は使用せずに、Sheet1のI3セルに入力する関数を次の様にして下さい。 =IF(AND($H3<>"",COUNTIF(Sheet5!$A:$A,$H3)),INDEX(Sheet4!$I:$I,SUMPRODUCT(MAX((Sheet4!$A$2:$A$9=$H3)*ROW(Sheet4!$A$2:$A$9)))),"")  又、もしSheet4の最終行が何行目になるのか不明な場合には、Sheet1のI3セルに入力する関数を次の様な関数として下さい。(但し、Excel2007以前のバージョンのExcelではこの関数を使えません) =IF(AND($H3<>"",COUNTIF(Sheet5!$A:$A,$H3)),INDEX(Sheet4!$I:$I,SUMPRODUCT(MAX((Sheet4!$A:$A=$H3)*ROW(Sheet4!$A:$A)))),"")  又、もしSheet4のA列に入力されているグループ番号が必ず数値データとなっている事が保障されている場合には、Sheet1のI3セルに入力する関数を次の様な関数とする事も出来ます。 =IF(AND($H3<>"",COUNTIF(Sheet5!$A:$A,$H3)),INDEX(Sheet4!$I:$I,SUMPRODUCT(MAX((Sheet4!$A$1:INDEX(Sheet4!$A:$A,MATCH(9E+307,Sheet4!$A:$A))=$H3)*ROW(Sheet4!$A$1:INDEX(Sheet4!$A:$A,MATCH(9E+307,Sheet4!$A:$A)))))),"")  但し、SUMPRODUCT関数を使った方法や、(今回は提示しておりませんが)配列式を使用した方法では、元データの行数が多くなれば多くなる程処理が重くなりますので、元データの行数が万単位になる様な場合には作業列を用いる方法を使った方が実用的です。

okamoto27
質問者

お礼

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

すると、全ての回答が全文表示されます。

その他の回答 (1)

  • intin
  • ベストアンサー率33% (8/24)
回答No.1

参考URLのような感じで、 作業列を作る方法はどうですか? vlookup関数を使う前提であれば、 例えば表全体を右にずらすと、元のA列がB列になります。 その状態でA2列に =if(countif(B2:$B$65535,B2)=1,B2,"") と入れて、下にコピー。 あとは、 A列をキーにしてvlookupを使えばいいと思います。 先ほどの例だと、元のI列はJ列に動きますから、 A列から数えると10列目になります。 あとは、 sheet1のI3セルに、 =vlookup(H3,sheet4!$A$2:$J$65535,10,false) と入れれば出るのではないでしょうか?

参考URL:
http://www.excelspeedup.com/vlookupsaisyuu/
すると、全ての回答が全文表示されます。

関連するQ&A