ADDRESS関数の使い方｜文字列が返る理由とINDIRECT連携で値を取る方法

スポンサーリンク

「ADDRESS関数を使ってみたら、値じゃなくて文字列が出てきた」
そんな経験はありませんか。

ExcelのADDRESS関数は、行番号と列番号から「セル番地」を作る関数です。
ただし返ってくるのは番地の「文字列」であって、そのセルの「値」ではありません。
ここでつまずく人がとても多いんです。

この記事では、ADDRESS関数の使い方を「文字列しか返らない理由」から整理します。
そのうえで、INDIRECT関数と組み合わせて値を取る方法を解説しますね。
さらにMATCHを加えた動的参照、最終行の値を取るパターン、INDEXとの使い分けまで進みます。
読み終わるころには、「いつADDRESSを使い、いつ使わないか」がはっきりわかりますよ。

ADDRESS関数とは？返るのは「文字列」であって「値」ではない

ExcelのADDRESS関数は、行番号と列番号を渡すとセル番地を返す関数です。
たとえば =ADDRESS(3,2) と入力すると、$B$3 が返ります。
B列（2列目）の3行目という意味の番地ですね。

ここで一番大事なポイントを最初に押さえましょう。
返ってくる $B$3 は、ただの文字列です。
B3セルに入っている値ではありません。

つまりADDRESS単体では、目的のセルの中身を取り出せないんです。
「ADDRESSを使ったのに値が取れない」と感じるのは、仕様どおりの動作です。
故障でも数式ミスでもありませんよ。

なお「ADDRESS」は「アドレス」と読みます。
英語の address（住所・所在地）が語源で、セルの「住所」を文字で表す関数だと考えるとイメージしやすいですね。

ADDRESS単体 vs INDIRECT(ADDRESS()) の2行比較

「文字列しか返らない」の正体を、次の表で直感的につかんでください。
B3セルに 1000 という値が入っている前提です。

数式返り値正体
=ADDRESS(3,2)$B$3セル番地の文字列
=INDIRECT(ADDRESS(3,2))1000B3セルの値

違いは1関数だけです。
ADDRESSの外側をINDIRECT関数で包むと、文字列がセル参照に変わり、値が取れます。

INDIRECT関数は「文字列で書かれた番地を、本物のセル参照として読み直す」関数です。
ADDRESSが番地を「書き」、INDIRECTがそれを「読む」。
この2段構えが、ADDRESS活用の基本形だと覚えてください。

ADDRESS関数でできること・できないこと

役割を整理しておきましょう。

  • できること：行番号・列番号から番地の文字列を組み立てる
  • できること：別シート名つきの番地を作る（後述）
  • できないこと：そのセルの値を直接取得する
  • できないこと：番地から逆に「行・列番号」を取り出す

値を取りたいならINDIRECT、番地を文字列のまま使いたいならADDRESS単体。
この線引きが、これ以降のすべてのパターンの土台になります。

ADDRESS関数の基本構文と5つの引数

ADDRESS関数の構文は次のとおりです。

=ADDRESS(行番号, 列番号, [参照の種類], [参照形式], [シート名])

必ず指定するのは「行番号」と「列番号」の2つだけです。
残り3つは省略できます。
それぞれの役割を表で確認しましょう。

引数必須/省略可内容
行番号必須セルの行番号（1以上の整数）
列番号必須セルの列番号（A=1、B=2…）
参照の種類（abs_num）省略可絶対参照／相対参照を1〜4で指定
参照形式（a1）省略可TRUE=A1形式、FALSE=R1C1形式
シート名省略可別シート参照時にシート名を文字列で指定

行番号・列番号だけ渡した場合、参照の種類は「1」になります。
つまり省略すると完全な絶対参照（行も列も $ 付き）です。

参照の種類（abs_num）1〜4の使い分け判断表

第3引数の abs_num は、絶対参照（$で固定）と相対参照（固定なし）を切り替えます。
絶対参照はコピーしても番地がずれない参照、相対参照はコピー方向にずれる参照ですね。

=ADDRESS(3,2,□) の□に1〜4を入れたときの出力は、次のとおり検証済みです。

abs_num行列ADDRESS(3,2,n) の出力主な使いどころ
1（省略時）絶対絶対$B$3番地を固定したいとき
2絶対相対B$3横方向コピーで行だけ固定
3相対絶対$B3縦方向コピーで列だけ固定
4相対相対B3列名変換など $ を消したいとき

判断軸はシンプルです。
「コピーしたときに、行と列のどちらをずらしたくないか」で選びます。

実務で意外と出番が多いのが abs_num=4（相対参照）です。
$ が一切付かない B3 形式が返るので、後で文字を加工しやすいんですね。
列番号をアルファベットに変換する場面で活躍します（後述）。

参照形式（A1形式 vs R1C1形式）

第4引数の a1 は、番地の表記スタイルを決めます。

a1形式ADDRESS(3,4,1,a1) の出力
TRUE（省略時）A1形式$D$3
FALSER1C1形式R3C4

R1C1形式は「行3・列4」のように番号で表す書き方です。
主にVBA・マクロ開発で使われます。
普通のワークシートではA1形式を使うので、この引数は省略でかまいません。

なお、INDIRECTと組み合わせてR1C1形式を使う場合は注意が必要です。
=INDIRECT(ADDRESS(3,4,1,FALSE), FALSE) のように、INDIRECT側の第2引数もFALSEにそろえてください。
片方だけR1C1にすると参照がかみ合わず、エラーになります。

別シート・別ブックの指定方法

第5引数にシート名を渡すと、シート名つきの番地が返ります。

=ADDRESS(1, 1, 1, TRUE, "1月")

この結果は '1月'!$A$1 です。
シート名が '1月' とシングルクォートで囲まれている点に注目してください。
ADDRESS関数は、必要に応じてクォートを自動で付けてくれます。

別ブックを参照したいときは、シート名を "[Book2.xlsx]Sheet1" の形式で渡します。
ただしADDRESSはあくまで「文字列」を作るだけです。
この文字列から実際に値を取るには、やはりINDIRECTが必要になります。

ADDRESS だけでは値を取れない――INDIRECT との連携が必須

ここからが本題です。
ADDRESSが作った番地の文字列を、INDIRECTで「値」に変えていきます。

INDIRECT(ADDRESS()) の基本パターン

セルA1に行番号「5」、B1に列番号「3」が入っているとします。
この2つを使ってC5セルの値を取りたい、という場面です。

=INDIRECT(ADDRESS(A1, B1))

処理は3ステップで進みます。

  1. ADDRESS(5, 3) が $C$5 という文字列を作る
  2. INDIRECT("$C$5") がその文字列をセル参照に読み直す
  3. C5セルの値が返る

A1やB1の数字を書き換えるだけで、参照先がその場で切り替わります。
番地を計算で組み立てられるのが、この組み合わせの強みですね。

シート名を動的に切り替える実務パターン

INDIRECT＋ADDRESSが本当に光るのは、シート名を変数化したいときです。
月別シート（1月、2月…）から、選んだ月のデータを取りたい場面を考えましょう。
セルC1にシート名「1月」、A1に行番号、B1に列番号が入っているとします。

=INDIRECT(ADDRESS(A1, B1, 1, TRUE, C1))

C1を「2月」「3月」と変えるだけで、取得先シートが丸ごと切り替わります。
ADDRESSが '2月'!$C$5 のような番地を作り、INDIRECTが値を取ってくる流れです。

これは通常の参照では作りにくい仕組みです。
セルの値でシートを切り替えられる点が、後述するINDEXとの決定的な違いになります。

NOTE

INDIRECTは「揮発性関数」です。
揮発性とは、シート上で何か変更があるたびに再計算される性質のこと。
そのため数千セル単位で多用すると、ブックが重くなります。
同一シート内の単純な値取得なら、後述のINDEXを使うほうが軽快ですよ。
INDIRECTの揮発性についてはINDIRECT関数の記事で詳しく解説しています。

MATCH+ADDRESS+INDIRECT で行を動的に特定する

実務では「検索値から該当行を探し、その値を取る」パターンが頻出します。
MATCH関数を加えると、これが実現できます。
MATCHは「検索値が範囲の何番目にあるか」を番号で返す関数です。

3関数の役割分担は次のとおりです。

関数役割
MATCH検索値が何行目かを返す
ADDRESS行番号・列番号から番地を作る
INDIRECT番地の文字列から値を取る

月次集計表から当月データを抽出する

A列に月名（1月〜12月）、B列に売上が入った集計表があるとします。
セルD1に「3月」と入れて、その売上を取り出してみましょう。

=INDIRECT(ADDRESS(MATCH(D1, A:A, 0), 2))

流れを分解します。

  1. MATCH(D1, A:A, 0) がA列で「3月」の行番号を返す（仮に4行目なら 4）
  2. ADDRESS(4, 2) が $B$4 を作る
  3. INDIRECT("$B$4") がB4の売上値を返す

D1を「4月」に変えれば、参照行が自動でずれて値が切り替わります。
「探す→番地を作る→値を取る」の3ステップだと考えれば、難しくありませんね。

MAX+MATCH+ADDRESS+INDIRECT で最終行の値を常に取得する

「リストの一番下に追加した最新データを、いつも自動で表示したい」。
そんなときは、最終行を計算で特定するパターンが便利です。

まず、データが連続している（途中に空白がない）場合はCOUNTAが使えます。
B列の最終行の値を取る数式はこうです。

=INDIRECT(ADDRESS(COUNTA(B:B), 2))

COUNTA(B:B) がB列の入力済みセル数を数え、それがそのまま最終行番号になります。
ただし見出し行を含めてカウントするので、データの行数と最終行番号がずれないか確認してください。

途中に空白行が混じる場合は、MAXとIFで「値が入っている最後の行」を探します。
こちらは配列数式です。

=INDIRECT(ADDRESS(MAX(IF(B:B<>"", ROW(B:B))), 2))

仕組みを分解しましょう。

  1. IF(B:B<>"", ROW(B:B)) が、値のある行だけ行番号を返す
  2. MAX(...) がその中で一番大きい行番号、つまり最終行を取り出す
  3. ADDRESS(最終行, 2) が番地を作り、INDIRECT が値を取る

この数式は配列として処理されます。
スピル対応のExcel（Microsoft 365など）ではそのまま確定できます。
古いバージョンでは Ctrl+Shift+Enter で確定してください。

新しい行を追加するたびに、表示が自動で最新値に追従します。
最終行を手で書き換える手間がなくなりますよ。

ADDRESS が不要なケース――INDEX で十分な場面を整理する

ここで正直にお伝えします。
ADDRESS＋INDIRECTは便利ですが、「使わなくていい場面」も多いんです。
多くの解説はこの組み合わせを推しますが、過剰になりやすい点は知っておくべきです。

同一シート内の値取得は INDEX+MATCH が正解

「行番号と列番号から値を取る」だけなら、INDEX関数で完結します。
先ほどの月次集計の例は、INDEX＋MATCHでこう書けます。

=INDEX(B:B, MATCH(D1, A:A, 0))

INDIRECT＋ADDRESS版と結果は同じです。
しかし違いは小さくありません。

観点INDEX+MATCHINDIRECT+ADDRESS
処理速度高速（参考値：約0.001秒）低速（参考値：約5.2秒）
揮発性なし（必要時だけ再計算）あり（変更のたび再計算）
数式の読みやすさシンプルやや複雑
別シートの動的切り替え不可可能

速度の参考値には大きな差があります（およそ5,000倍）。
測定環境による参考値ですが、傾向としてINDEXが軽いのは確かです。
同一シート内のデータ取得なら、迷わずINDEX＋MATCHを選んでください。

ADDRESS+INDIRECT が必要な唯一の場面（シート名の動的切り替え）

ではADDRESS＋INDIRECTの出番はどこか。
答えはほぼ1つ、「シート名をセルの値で切り替えたいとき」です。

INDEX関数は、参照範囲のシートを変数で差し替えられません。
「1月」シートと「2月」シートを、セルの入力で切り替える――これはINDEXには無理です。
ここだけはINDIRECT＋ADDRESSの独壇場になります。

判断はこの一言で済みます。
「シート名を動的に変えたい？」がYesならINDIRECT＋ADDRESS、NoならINDEX。
迷ったらまずINDEXを試す、と覚えておくと失敗しませんよ。

列番号をアルファベットに変換する（abs_num=4 の使いどころ）

最後に、ADDRESSならではの便利ワザを紹介します。
「列番号5をアルファベットの『E』で表示したい」といった変換です。
ここで abs_num=4（相対参照）が効いてきます。

=SUBSTITUTE(ADDRESS(1, 5, 4), "1", "")

この数式は E を返します。
仕組みは2ステップです。

  1. ADDRESS(1, 5, 4) が E1（$なしの相対参照）を作る
  2. SUBSTITUTE(..., "1", "") が「1」を消し、列名 E だけが残る

第1引数を「1」に固定しているのがコツです。
行番号を1にしておけば、消すべき数字が必ず行番号の「1」だけになります。
列名のアルファベットには数字が含まれないので、SUBSTITUTEで「1」を消せば列名がきれいに残るわけです。

2桁の列にも対応します。
列番号28なら、ADDRESS(1, 28, 4) が AB1 を作り、結果は AB です。

=SUBSTITUTE(ADDRESS(1, 28, 4), "1", "")

今いるセルの列名を知りたいときは、COLUMN関数と組み合わせます。

=SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "")

列番号からアルファベットへの変換は、ほかの方法もあります。
列番号をアルファベットに変換する方法で、別アプローチも含めて詳しく解説していますよ。

なお、Googleスプレッドシートでも同じ5引数構文でADDRESSが使えます。
Sheetsで使いたい方はスプレッドシートのADDRESS関数を参照してください。

よくあるエラーと対処法

ADDRESSとINDIRECT連携で出やすいエラーを2つ整理します。

#VALUE!エラーは、ADDRESSの引数自体に問題があるサインです。

原因対処
行番号・列番号が0以下1以上の整数を指定する
行番号・列番号が数値でない文字列が混入していないか確認する
abs_numが1〜4以外1〜4のいずれかを指定する

たとえば =ADDRESS(0, 3) は #VALUE! になります。
行番号は必ず1以上にしてください。

#REF!エラーは、INDIRECTと組み合わせたときに出ます。
ADDRESS単体は正しくても、参照先が見つからないと発生します。
主な原因は2つです。

  • 存在しないシート名を参照している（スペルミス・削除済みシート）
  • シート名にスペースや記号があり、クォートで囲めていない

ADDRESSの第5引数でシート名を渡せば、クォートは自動で付きます。
一方、自分で文字列結合して番地を作っている場合は要注意です。
クォートを手で付け忘れると #REF! になります。
エラー時はまず「シート名が本当に存在するか」を確認してくださいね。

まとめ

ADDRESS関数は、行番号と列番号から「セル番地の文字列」を作る関数です。
返るのは文字列であって値ではない、という一点を押さえれば迷いません。

この記事の要点をおさらいします。

  • =ADDRESS(3,2) は $B$3、つまり番地の文字列を返す
  • 値を取るには INDIRECT(ADDRESS(...)) で文字列を参照に変える
  • abs_num 1〜4は「コピー方向で何を固定したいか」で選ぶ
  • MATCHを足すと検索値から動的に値を取れる
  • MAX+IFを足せば、空白混じりでも最終行の値を取得できる
  • 同一シート内ならINDEX＋MATCHが高速で正解
  • ADDRESS＋INDIRECTの出番は「シート名の動的切り替え」にほぼ限られる
  • abs_num=4 とSUBSTITUTEで列番号をアルファベットに変換できる

ADDRESSは単体ではなく、INDIRECT関数やMATCH関数と組み合わせてこそ真価を発揮します。
ただし、なんでもADDRESS＋INDIRECTにせず、同一シートならINDEX関数を優先する。
この使い分けができれば、あなたの数式は速くて読みやすいものになりますよ。

スポンサーリンク
タイトルとURLをコピーしました