【いちからExcel】データ範囲の中で対象の値がある行のデータを取得する方法

　Excelを用いて、データ範囲の中で対象の値がある行のデータを取得する方法について紹介します。対象の値がある行番号が分っても、わざわざスクロールや検索機能を用いてコピーペーストするのはかなりの手間です。また、VLOOKUP関数では検索行をわざわざ左端に持ってくる必要があります。
　ここでは、XLOOKUP関数、MATCH関数、INDIRECT関数、OFFSET関数を用いる3つの方法を紹介します。なお、Excel2021を用いていますので、それ以前のバージョンですとXLOOKUP関数は対応していないようです。ご注意ください。

3つの抽出方法

　たとえば、加速度・速度・変位や応力成分データなど、時刻毎に書かれた大量のデータの中から”ある最大値”が発生する時刻の他の成分を抜き出すことを考えます。ExcelVBAを使うことも一つの手ですが、定型的なデータですと、Excelシート上に抽出式を書き込んだ表を作成しておけば、それをシート上の同じ位置にコピーすることで、簡単にデータを抜き出すことが出来ます。そのような場合を想定した方法を3つご紹介します。

• Excel上でデータを抽出する方法
  1. XLOOKUP関数を用いる方法
  2. MATCH関数＋INDIRECT関数
  3. MATCH関数＋OFFSET関数

XLOOKUP関数を用いた例　

　この例は、A列～D列に時間、加速度、速度、変位の時刻歴データが16384個書かれたシートから、加速度、速度、変位が最大および最小になる時刻の各データを抽出した例です。各成分の最大値あるいは最小値は、シート上に着色された位置でMAX関数あるいはMIN関数を使って抽出しています。各最大値およぶ最小値が発生する行の他の成分は、XLOOLUP関数を用いて抽出しています。下図には3行目と5行目に書かれた数式を例として示しています。

MATCH関数＋INDIRECT関数

　各成分の最大値あるいは最小値をG3～G8に抽出し、その値が存在する行番号をMATCH関数を用いて抽出しています。行番号が分れば、列と行番号を用いて抽出する位置を指定することができるので、INDIRECT関数で値を抽出しています。下図には3行目と5行目に書かれた数式を例として示しています。

　なお、下図のように、INDIRECT関数の行番号に直接MATCH関数を書き込むことも可能です。

MATCH関数＋OFFSET関数

　この例では、OFFSET関数のオフセット行数に、直接MATCH関数を書き込むことでデータを抜き出しています。それぞれ1行目からのオフセット数をMATCH関数で指定しますので、MATCH関数の検索範囲は2行目からとしています。下図に3行目と5行目に書かれた数式を例として示しています。

　それでは、各関数の使用方法を以下に示します。

XLOOKUP関数

　VLOOKUP（縦方向の検索）関数やHLOOKUP（横方向の検索）関数を使っている方は多いと思います。たとえば、VLOOKUPでは検索範囲を最初の列（一番左）とする必要がありますが、XLOOKUP関数は検索範囲と抽出範囲（戻り範囲）を指定できる点にあり、戻り列が検索列の右側でも左側でも関係なく抽出できる特徴があります。また、縦方向でも横方向でも検索ができます。office365やoffice2021以降のバージョンで使用することが可能です。

MATCH関数

　指定範囲で項目を検索し、その範囲内の項目の相対的な位置を返します。

INDIRECT関数

　指定されるセル位置の値を参照します。たとえば、＝INDIRECT(“C6“)や＝INDIRECT(”C”＆”6”)とするとセルC6を参照します。

OFFSET関数

　セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。

以上、データ範囲の中で対象の値がある行のデータを取得する方法について紹介しました。お役に立てば幸いです。