ADDRESS関数の使い方|行番号・列番号からセル参照の文字列を返す

ADDRESS関数の使い方

Excel(エクセル)のADDRESS関数の使い方|行番号・列番号からセル参照の文字列を返す

使用関数:ADDRESS、INDIRECT、COUNTA、MATCH

ADDRESS(アドレス)関数とは?

ADDRESSは「宛先」「住所」を意味する英語ですが、ADDRESS関数は参照したセルの住所「セル番地」を文字列で返す関数です。別ブック、別シートの参照もシート名を指定してスムーズに実行できます。

通常Excelでセルを参照する時、目当てのセルをクリックすると表示される列番号(アルファベット)・行番号(数字)を組み合わせた「セル番地」はセルの位置情報です。ADDRESS関数はこれを文字列に作り変えて渡します。多くはINDIRECT関数やCOUNTA関数との組み合わせで使用されます。

ADDRESS関数の引数と書式

「数式」→「関数ライブラリ」→「検索/行列」からADDRESSを選択します。
数式ライブラリの検索/行列からADDRESSを選択
書式の構成はこうなります。
ADDRESS関数の書式の構成
引数が多いように感じるかもしれませんが、必須なのは「行番号」「列番号」だけ。後は省略可能です。別シートを参照する時は最後の引数「シート名」を指定します。

第一引数「行番号」 第二引数「列番号」

シートの左上端のセルを1行目1列目として行方向・列方向に数える番号を数値、またはセル参照で指定します。
例えば、「行番号」に4、「列番号」に3を指定すると、
ADDRESS関数の「行番号」に4、「列番号」に3を指定
$C$4」と絶対参照のセル番地が文字列で返ります。
ADDRESS関数「行番号」「列番号」の指定で絶対参照のセル番地が文字列で返る

第三引数「参照の種類」

参照の種類」は返るセル番地を「絶対参照」「複合参照(絶対列参照・絶対行参照)」「相対参照」のどれにするかを1~4までの番号で指定します。
ADDRESS関数の参照の種類に絶対参照・複合参照・相対参照を1~4までの番号で指定
この引数を省略した場合は「絶対参照」で返ります。

指定する番号参照の種類A1の表示
1(または省略)絶対参照▶行・列ともに固定$A$1
2複合参照(絶対行参照)▶行だけ固定、列は可変A$1
3複合参照(絶対列参照)▶列だけ固定、行は可変$A1
4相対参照▶行・列ともに可変A1

絶対参照・複合参照・相対参照を徹底的に分かりやすく解説しています。

絶対参照・相対参照・複合参照を徹底的にわかりやすく
Excel|絶対参照・相対参照・複合参照を徹底的にわかりやすく Excelを操作していると必ずぶつかるのが「絶対参照」と「相対参照」。イメージはうっすら把握できるんだけど、こういう時はこっち!っていう確信がイマイチ持てない…そんな悩ま

第四引数「参照形式」(R1C1形式のみ)

引数「参照形式」はシートの参照形式がA1形式(列アルファベット・行数字)でなく、R1C1形式(行数字・列数字)になっている場合でのみ論理値の「FALSE」または「0」を指定します。
ADDRESSの「参照形式」に参照形式がR1C1形式の場合FALSEか0を指定
A1形式では「TRUE」か「1」を指定してもいいですが、普通は省略します。

参照形式(A1、R1C1)の詳しい解説記事です。

R1C1参照形式の表示方法と役立て方・A1との相違点
Excelで規定のA1参照形式をR1C1参照形式に切替える方法と、この2つの形式の相違点を詳しく解説します。R1C1形式では、数式の修正箇所が見える利点があります。

第五引数「シート名」(別ブック・別シートの参照では必須)

一つのシート内で数式を完結させるなら省略します。別ブック、別シートの参照セルを取得する場合に、そのシート名を入力します。
例えば、行・列番号どちらも1にして”“[book1]Sheet1”“のように入力すれば、[book1]Sheet1!$A$1とブック名・シート名付きで表示されます。
ADDRESS関数の「シート名」を指定すると参照セルのセル番地がブック名・シート名付きで表示される
同じブックの別シートを参照するなら、指定は“Sheet1”、戻り値はSheet1!$A$1です。

ADDRESS関数と他の関数を組み合わせて別シートを参照

ADDRESS関数で求めた参照セルに入力されている値を表示させるためにはINDIRECT関数に入れ子(ネスト)する必要があります。
下図の例では、別シート「会員名簿」の末尾に新規メンバーを追加する度に新規会員の氏名が自動で入れ替わるようにADDRESS関数とMATCH関数、COUNTA関数、INDIRECT関数を組み合わせて実装します。
ADDRESS関数とCOUNTA関数、INDIRECT関数を組み合わせて別シートからデータを取得する使用例

COUNTA関数は範囲の中にデータが幾つあるかを数えます。別シート「会員名簿」の氏名の列範囲を指定することで末尾の「行番号」を取得してADDRESS関数に渡します。
ADDRESS関数の「行番号」にCOUNTA関数を入れ子して列範囲のデータ数を指定
数式: COUNTA(会員名簿!B:B)

ADDRESS関数の「列番号」には数値で「2」としてもいいですが、列の項目を増やしても対応できるようにMATCH関数で「氏名」の位置を指定しておきます。
MATCH関数の「検査値」に氏名のセル、「検査範囲」に別シート「会員名簿」の列見出しの行範囲を絶対参照で指定しています。
ADDRESS関数の「列番号」にMATCH関数を入れ子して「氏名」の位置を指定
数式: MATCH($B$3,会員名簿!$1:$1)

ADDRESS関数の最後の引数「シート名」に手入力で「会員名簿」と打ち込み、Tabキーを押して文字列指定にします。
ADDRESS関数「シート名」に手入力でシート名を指定
数式: ADDRESS(COUNTA(会員名簿!B:B),MATCH($B$3,会員名簿!$1:$1),,,”会員名簿”)

組み立てたADDRESS関数をINDIRECT関数の参照文字列に入れ子して完了です。
ADDRESS関数をINDIRECT関数の参照文字列に入れ子
最終的な数式: =INDIRECT(ADDRESS(COUNTA(会員名簿!B:B),MATCH($B$3,会員名簿!$1:$1),,,”会員名簿”))

別シートの会員名簿の末尾にある氏名が抽出できました。新規会員のデータが追加されれば、自動で氏名が入れ替わります。
ADDRESS関数とMATCH・COUNTA・INDIRECT関数の組み合わせで会員名簿の末尾にある氏名を抽出

ADDRESS関数と組み合わせることが多い関数の使い方の詳細記事です。分かりやすくまとめていますのでご一読下さい。

INDIRECT関数の使い方|文字列のセル番号やシート名で値を参照する
INDIRECT関数の使い方を初心者にも分かりやすく解説。参照文字列で別シートのデータを反映させるのに&を使った指定、別ブック、別シート、列、範囲に定義した名前の指定、INDIRECTとVLOOKUP・ADDRESS・SUM関数を組み合わせた使い方など。
MATCH関数の使い方|検索して条件に合うセルの位置を求める
MATCH関数の使い方を初心者にも分かりやすく解説。MATCH関数で部分一致のあいまい検索をする方法、MATCH関数とINDEX関数やOFFSET関数を組み合わせてデータを抽出する使用例、MATCH関数で複数の検索値を指定する方法、エラーの対処法など。

ADDRESS関数で別シートを参照した時のエラー

ADDRESS関数で別シートの参照セルを取得する際に起こるエラー#REF!はシート名の指定ミスが原因かもしれません。

通常の数式で別シートの名前を指定する場合は「!」を付けますが、ADDRESS関数の「シート名」ではシート名を文字列で指定します。!」は付けないで、シートの名前だけを「」で括って入力するのが決まりです。
別ブックのシート名を指定するときはブック名を[ ]で囲み、その後にシート名を入れます。

ADDRESS関数の第五引数「シート名」に別ブックを指定・・・→ “[ブック名]シート名
ADDRESS関数の第五引数「シート名」に別シートを指定・・・→ シート名

ADDRESS関数のまとめ
  • ADDRESS関数は行番号・列番号で参照したセルの住所「セル番地」を文字列で返す関数です
  • ADDRESS関数の「参照の種類」には参照セルの表示を「絶対参照」「複合参照(絶対列参照・絶対行参照)」「相対参照」のどれにするか1~4までの番号で指定します
  • ADDRESS関数「参照形式」にはシートの参照形式がR1C1形式(行数字・列数字)になっている場合でのみ論理値のFALSE(または0)を指定します
  • ADDRESS関数「シート名」は別ブック、別シートの参照セルを取得する場合に指定します
  • ADDRESS関数はINDIRECT関数を筆頭に、COUNTA関数、MATCH関数など他の関数と組み合わせることで利用の範囲が広がります
  • ADDRESS関数で別シートを参照する場合、シート名の指定でエラー(#REF!)になるケースが多いので注意しましょう
タイトルとURLをコピーしました