Excel(エクセル)のADDRESS関数の使い方|行番号・列番号からセル参照の文字列を返す
使用関数:ADDRESS、INDIRECT、COUNTA、MATCH
ADDRESS(アドレス)関数とは?
ADDRESSは「宛先」「住所」を意味する英語ですが、ADDRESS関数は参照したセルの住所「セル番地」を文字列で返す関数です。別ブック、別シートの参照もシート名を指定してスムーズに実行できます。
通常Excelでセルを参照する時、目当てのセルをクリックすると表示される列番号(アルファベット)・行番号(数字)を組み合わせた「セル番地」はセルの位置情報です。ADDRESS関数はこれを文字列に作り変えて渡します。多くはINDIRECT関数やCOUNTA関数との組み合わせで使用されます。
ADDRESS関数の引数と書式
「数式」→「関数ライブラリ」→「検索/行列」からADDRESSを選択します。
書式の構成はこうなります。
引数が多いように感じるかもしれませんが、必須なのは「行番号」「列番号」だけ。後は省略可能です。別シートを参照する時は最後の引数「シート名」を指定します。
第一引数「行番号」 第二引数「列番号」
シートの左上端のセルを1行目1列目として行方向・列方向に数える番号を数値、またはセル参照で指定します。
例えば、「行番号」に4、「列番号」に3を指定すると、
「$C$4」と絶対参照のセル番地が文字列で返ります。
第三引数「参照の種類」
「参照の種類」は返るセル番地を「絶対参照」「複合参照(絶対列参照・絶対行参照)」「相対参照」のどれにするかを1~4までの番号で指定します。
この引数を省略した場合は「絶対参照」で返ります。
指定する番号 | 参照の種類 | A1の表示 | |
---|---|---|---|
1(または省略) | 絶対参照 | ▶行・列ともに固定 | $A$1 |
2 | 複合参照(絶対行参照) | ▶行だけ固定、列は可変 | A$1 |
3 | 複合参照(絶対列参照) | ▶列だけ固定、行は可変 | $A1 |
4 | 相対参照 | ▶行・列ともに可変 | A1 |
絶対参照・複合参照・相対参照を徹底的に分かりやすく解説しています。

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

第五引数「シート名」(別ブック・別シートの参照では必須)
一つのシート内で数式を完結させるなら省略します。別ブック、別シートの参照セルを取得する場合に、そのシート名を入力します。
例えば、行・列番号どちらも1にして”“[book1]Sheet1”“のように入力すれば、[book1]Sheet1!$A$1とブック名・シート名付きで表示されます。
同じブックの別シートを参照するなら、指定は“Sheet1”、戻り値はSheet1!$A$1です。
ADDRESS関数と他の関数を組み合わせて別シートを参照
ADDRESS関数で求めた参照セルに入力されている値を表示させるためにはINDIRECT関数に入れ子(ネスト)する必要があります。
下図の例では、別シート「会員名簿」の末尾に新規メンバーを追加する度に新規会員の氏名が自動で入れ替わるようにADDRESS関数とMATCH関数、COUNTA関数、INDIRECT関数を組み合わせて実装します。
COUNTA関数は範囲の中にデータが幾つあるかを数えます。別シート「会員名簿」の氏名の列範囲を指定することで末尾の「行番号」を取得してADDRESS関数に渡します。
数式: COUNTA(会員名簿!B:B)
ADDRESS関数の「列番号」には数値で「2」としてもいいですが、列の項目を増やしても対応できるようにMATCH関数で「氏名」の位置を指定しておきます。
MATCH関数の「検査値」に氏名のセル、「検査範囲」に別シート「会員名簿」の列見出しの行範囲を絶対参照で指定しています。
数式: MATCH($B$3,会員名簿!$1:$1)
ADDRESS関数の最後の引数「シート名」に手入力で「会員名簿」と打ち込み、Tabキーを押して文字列指定にします。
数式: ADDRESS(COUNTA(会員名簿!B:B),MATCH($B$3,会員名簿!$1:$1),,,”会員名簿”)
組み立てたADDRESS関数をINDIRECT関数の参照文字列に入れ子して完了です。
最終的な数式: =INDIRECT(ADDRESS(COUNTA(会員名簿!B:B),MATCH($B$3,会員名簿!$1:$1),,,”会員名簿”))
別シートの会員名簿の末尾にある氏名が抽出できました。新規会員のデータが追加されれば、自動で氏名が入れ替わります。
ADDRESS関数と組み合わせることが多い関数の使い方の詳細記事です。分かりやすくまとめていますのでご一読下さい。


ADDRESS関数で別シートを参照した時のエラー
ADDRESS関数で別シートの参照セルを取得する際に起こるエラー#REF!はシート名の指定ミスが原因かもしれません。
通常の数式で別シートの名前を指定する場合は「!」を付けますが、ADDRESS関数の「シート名」ではシート名を文字列で指定します。「!」は付けないで、シートの名前だけを「“」で括って入力するのが決まりです。
別ブックのシート名を指定するときはブック名を[ ]で囲み、その後にシート名を入れます。
ADDRESS関数の第五引数「シート名」に別ブックを指定・・・→ “[ブック名]シート名“
ADDRESS関数の第五引数「シート名」に別シートを指定・・・→ “シート名“
- ADDRESS関数は行番号・列番号で参照したセルの住所「セル番地」を文字列で返す関数です
- ADDRESS関数の「参照の種類」には参照セルの表示を「絶対参照」「複合参照(絶対列参照・絶対行参照)」「相対参照」のどれにするか1~4までの番号で指定します
- ADDRESS関数「参照形式」にはシートの参照形式がR1C1形式(行数字・列数字)になっている場合でのみ論理値のFALSE(または0)を指定します
- ADDRESS関数「シート名」は別ブック、別シートの参照セルを取得する場合に指定します
- ADDRESS関数はINDIRECT関数を筆頭に、COUNTA関数、MATCH関数など他の関数と組み合わせることで利用の範囲が広がります
- ADDRESS関数で別シートを参照する場合、シート名の指定でエラー(#REF!)になるケースが多いので注意しましょう