Excelで値が入っているセルの位置を取得する方法【総集編】

ExcelのMATCH関数で特定の範囲内での相対的なセルの位置を取得する方法や、ADDRESS関数で指定した行と列のセルのアドレスを取得する方法をご紹介します。値が入力されている最終行のセルの位置を取得する方法も解説しているので、ぜひ参考にしてみてください。

MATCH関数でセルの位置を取得する方法

MATCH関数を使うと、指定した範囲における検索値の行番号または列番号を取得可能です。これらの番号はINDEX関数と組み合わせて、特定のセルの位置の値を取得するのに使用されます。

MATCH関数の使い方

MATCH関数 は検索値と一致するセルを検索し、検査範囲内での相対的な位置を返します。

例えば MATCH(2,A2:A6,0) なら、検査範囲から 2 と一致するセルを検索します。検査範囲内での 2 の相対的な位置は先頭から 2 番目なので、返り値は 2 です。

なお照合の種類には、完全一致を検索する 0 を指定してください。

同様に MATCH(“商品名”,A1:D1,0) なら検査範囲から 商品名 と一致するセルを検索します。検査範囲内での 商品名 の相対的な位置は先頭から 3 番目なので、返り値は 3 です。

検査値に 文字列 を直接指定する場合は、例のように二重引用符で囲ってください。

INDEX関数でセルの値を取得する方法

INDEX関数は、指定した行番号と列番号がクロスする位置の値を返します。

INDEX(A2:D6,G2,G3) なら、返り値は参照内で2行目と3列目がクロスする位置の りんご です。

MATCH(F2,A1:D1,0) のように、MATCH関数の検索値をセル参照にしてみてください。

IDの部分を 4 にすると、ID4の商品名(トマト)を自動的に抽出可能です。商品名の部分を 分類 にすると、ID 2 の分類(果物)が抽出されます。

ADDRESS関数でセルの位置を取得する方法

行番号の取得にはROW関数を、列番号の取得にはCOLUMN関数を使用します。またこれらの番号を活用すれば、ADDRESS関数でセルのアドレスを取得することも可能です。

ROW関数で行番号を取得する方法

ROW関数は、引数として指定した参照の行番号を返します。

セルを縦方向の範囲として指定した場合の返り値は、先頭の行番号です。参照を省略すると、ROW関数が入力されているセルの行番号を返します。

COLUMN関数で列番号を取得する方法

COLUMN関数は、引数として指定した参照の列番号を返します。

セルを横方向の範囲として指定した場合の返り値は、先頭の列番号です。参照を省略すると、COLUMN関数が入力されているセルの列番号を返します。

ADDRESS関数でセルの位置を取得する方法

ADDRESS関数は、指定した行・列に対応するセル参照を文字列で返します。参照の種類は以下の通りです。省略すると、 1 の絶対参照と認識されます。

• 1：行と列の絶対参照（例: $A$1）
• 2：行は絶対参照で、列は相対参照（例: A$1）
• 3：行は相対参照で、列は絶対参照（例: $A1）
• 4：行と列の相対参照（例: A1）

セルの最終行の位置を取得する方法

空白を含む列から、値が入力されている最終行の位置を取得する方法をご紹介します。最終行の行番号を取得すれば、INDEX関数で最終行の値も簡単に抽出可能です。

空白を含むセルの最終行を取得する方法

空白を含む列の最終行の位置を取得する数式は MAX((A:A<>””)*ROW(A:A)) です。難しそうに見えて実は簡単な内容ですので、解説もチェックしてみてください。

1. A列が空白か、空白でないかの真偽判定を行います。
2. ROW関数で、A列のそれぞれの行番号を取得します。
3. 真偽判定の結果とA列の行番号を乗算し、MAX関数でその最大値を取得します。

まず A:A<>”” は、「A列が空白でない」と同義です。

例のようにA列が空白でないなら TRUE(真) を、空白なら FALSE(偽) を返します。

次にROW関数は、すべてのA列の行番号を返します。

(A:A<>””)*ROW(A:A) は、真偽判定の結果とA列の行番号を乗算です。

TRUEは 1 、FALSE は 0 なので、TRUE(A列が空白でない) の場合のみ 0以上の数値を返します。

MAX関数は一連の数値のうち、最大の数値を返します。

つまり MAX((A:A<>””)*ROW(A:A)) なら、2つの乗算結果の最大値です。最大値となるのはA列が空白でないセルの、最終行になります。

セルの最終行の値を取得する方法

INDEX関数の行番号に、MAX関数で取得した最終行を指定してください。

例えば INDEX(A:A,H2) なら、A列の5行目の値(CCC)を取得します。これらをひとつの数式にまとめると、=INDEX(A:A,MAX((A:A<>””)*ROW(A:A))) になります。