ADDRESS関数の使い方｜検索した値のセル番地を出す方法

スポンサーリンク

「この金額、どのセルに入ってる?」

同僚にそう聞かれて、大きな表を上から目で追った経験はありませんか。

行数が増えるほど、番地探しは面倒になります。しかも手で控えた番地は、行を1本挿した瞬間に古くなってしまう。チェック表に書いた「B15」が、翌日には別のセルを指しているわけです。

ExcelのADDRESS関数は、この「セルの住所探し」を数式に任せるための関数。行番号と列番号を渡すと、$B$3 のような番地を返してくれます。MATCH関数と組み合わせれば、検索でヒットした値の番地を自動で表示できますよ。

この記事では、ADDRESS関数の使い方を基本構文と5つの引数から整理します。そのうえで「値から番地を出す」実務パターンへ進みましょう。最後は、エラーが出ないのに番地がズレる症状の直し方まで扱います。

  1. ADDRESS関数とは？行番号と列番号から「セル番地」を作る関数
    1. ADDRESS関数が返すのは番地そのもの（$B のような文字列）
    2. 何に使えるのか――位置をメモ・連絡・チェックに残す
    3. ADDRESSを使うべきか3秒で決まる判断フロー
  2. ADDRESS関数の基本構文と5つの引数
    1. 第1・第2引数（行番号・列番号）――まずはこれだけで動く
    2. 第3引数 abs_num 1〜4――コピーしたとき何を固定したいかで選ぶ
    3. 第4引数 A1形式とR1C1形式の違い
    4. 第5引数 シート名――スペース入りはクォートが自動で付く
    5. ADDRESS(0,3) が #VALUE! になる理由
  3. 検索でヒットした値のセル番地を出す（ADDRESS×MATCH）
    1. ステップ1 行だけ分かればいい場合
    2. ステップ2 行と列の両方を検索する場合
    3. ステップ3 表の左上がA1でないときの起点合わせ
    4. CELL(“address”, INDEX(…)) との使い分け早見表
  4. エラーは出ないのに番地がズレる3つの症状と直し方
    1. 症状1 MATCHの戻り値は相対位置――範囲を絞った瞬間に1行ズレる
    2. 症状2 シート名を渡し忘れて「今いるシート」の同じ番地を指す
    3. 症状3 COUNTAの最終行が見出し行・空白行の分だけズレる
    4. #REF! が出たときはADDRESS側ではなく参照側の問題
  5. ADDRESSで作った番地から値を取る（連携は最小限でいい）
    1. ADDRESS単体とINDIRECT併用の2行比較
    2. シート名を切り替えたいなら設計の本体はINDIRECT側
    3. Googleスプレッドシートでも引数の構成は同じ
  6. まとめ｜ADDRESS関数は「番地を出したいとき」に使う

ADDRESS関数とは？行番号と列番号から「セル番地」を作る関数

ExcelのADDRESS関数は、行番号と列番号を渡すとセル番地を返す関数です。読み方は「アドレス」。英語の address（住所）が語源で、セルの住所を文字で表す関数だと考えるとイメージしやすいですね。

たとえば =ADDRESS(3,2) と入力すると、$B$3 が返ります。2列目（B列）の3行目、という意味の番地。

指定する順番が「行 → 列」である点だけ気をつけてください。「B3」という普段の表記とは逆順なので、ここは最初につまずきやすいところ。

必須の引数は行番号と列番号の2つだけ。残り3つは省略できるので、まずはこの短い形から試すのがおすすめです。

ADDRESS関数が返すのは番地そのもの（$B のような文字列）

ADDRESS関数の戻り値は、セル参照を表すテキスト文字列です。B3セルに入っている値ではありません。この一点さえ押さえれば、あとの応用はすべて素直につながります。

「値が取れない」と感じるのは仕様どおりの動作で、故障でも数式ミスでもないんです。むしろ文字列で返ること自体が、この関数の使いどころ。

文字列だからこそ、そのまま画面に表示できます。& でメッセージに連結したり、コピーしてメールに貼ったりもできるわけです。

何に使えるのか――位置をメモ・連絡・チェックに残す

番地を文字列で取り出せると、こんな場面で効いてきます。

  • 「数字が合わないのはこのセル」と、番地つきで同僚に連絡する
  • 検算シートに「参照元はここ」という記録を残す
  • 在庫表の該当行を、担当者が目で探さずに開けるようにする
  • 可変の行・列から参照を組み立てる（MATCHと組み合わせる）

たとえば次の式は、番地を文章に混ぜて表示します。

="単価は " & ADDRESS(6,3) & " を見てください"

結果は 単価は $C$6 を見てください という一文になります。番地を手打ちして更新し忘れる、という事故がなくなりますよ。

ADDRESSを使うべきか3秒で決まる判断フロー

ADDRESSは万能の関数ではありません。使わない方が早い場面もあるので、先に分岐を整理しておきましょう。

やりたいこと選ぶ関数
番地の文字列がほしい（表示・記録・連絡）ADDRESS
セルの値そのものがほしい値そのものを取りたいならINDEX / XLOOKUP
シート名をセルで切り替えたいときINDIRECT（番地づくりにADDRESSを添える）
列番号をアルファベットで表示したいADDRESS（abs_num=4）＋SUBSTITUTE

値を取り出したいだけなら、ADDRESSを経由する必要はありません。値そのものを取りたいならINDEXとMATCHの組み合わせで完結します。XLOOKUP関数を使う手もありますが、対象は Microsoft 365 と Excel 2021 以降です。

参照先のシートをセルの入力で差し替えたい場合は、設計の本体がINDIRECT側になります。シート名をセルで切り替えたいときの組み方は専用記事にまとめました。ADDRESSは番地を作る担当として添えるだけ。

迷ったら「ほしいのは文字か、値か」で切り分けてみてください。文字ならADDRESSの出番です。

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

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

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

角カッコで囲んだ3つは省略できます。それぞれの役割を表で確認しましょう。

引数必須/省略可内容
行番号（row_num）必須セルの行番号。1以上の整数
列番号（column_num）必須セルの列番号。A=1、B=2、C=3…
参照の種類（abs_num）省略可絶対参照・相対参照を1〜4で指定。省略時は1
参照形式（a1）省略可TRUE=A1形式、FALSE=R1C1形式。省略時はTRUE
シート名（sheet_text）省略可番地の前に付けるシート名を文字列で指定

省略したときの既定値も押さえておきましょう。参照の種類は1、つまり行も列も $ が付いた完全な絶対参照になります。

第1・第2引数（行番号・列番号）――まずはこれだけで動く

まずは数字を2つ渡すだけの形から始めましょう。

=ADDRESS(3,2)     → $B$3
=ADDRESS(10,1)    → $A$10
=ADDRESS(77,300)  → $KN$77

列番号は数値で渡します。A列が1、B列が2、C列が3という並びですね。300列目のような大きな数でも、$KN$77 ときちんとアルファベットに変換してくれます。

自分がいるセルの番地を出したいなら、ROW関数とCOLUMN関数の出番です。

=ADDRESS(ROW(), COLUMN())

この式をF2セルに入れると、$F$2 が返ります。ROW()が2を、COLUMN()が6を返すからですね。列番号がぱっと出てこないときは、COLUMN関数で列番号を取得する方法が便利ですよ。

第3引数 abs_num 1〜4――コピーしたとき何を固定したいかで選ぶ

第3引数は、絶対参照と相対参照を切り替えます。絶対参照とは $ が付いた参照のことで、数式をコピーしても参照先が動きません。相対参照は $ なしで、コピーした方向にずれていきます。

=ADDRESS(3,2,□) の□に1〜4を入れた結果は次のとおりです。

abs_num行列=ADDRESS(3,2,n) の結果どんなときに選ぶか
1（省略時）絶対絶対$B$3コピーしても番地を動かしたくない
2絶対相対B$3横にコピーして列だけずらしたい
3相対絶対$B3縦にコピーして行だけずらしたい
4相対相対B3$ を消して文字として加工したい

判断軸はシンプル。「コピーしたときに、行と列のどちらを固定したいか」で選べば迷いません。

実務で出番が多いのは1と4です。1は番地をそのまま記録したいとき、4は返ってきた文字を加工したいときに使います。

abs_num=4 は列名を取り出すワザの土台にもなります。列番号をアルファベットに変換する方法は、別解法も含めて専用記事で解説していますよ。

第4引数 A1形式とR1C1形式の違い

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

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

R1C1形式とは、行も列も番号で表す書き方のこと。「R3C4」なら3行目・4列目という意味ですね。VBAやマクロの開発でよく使われる表記です。

ワークシートの標準表示はA1形式なので、この引数はほぼ省略でかまいません。R1C1形式をINDIRECTに渡すときだけ、INDIRECT側の第2引数もFALSEにそろえてください。片方だけFALSEにすると、参照がかみ合わずエラーになります。

第5引数 シート名――スペース入りはクォートが自動で付く

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

=ADDRESS(1,1,1,TRUE,"Sheet2")        → Sheet2!$A$1
=ADDRESS(2,3,1,FALSE,"EXCEL SHEET")  → 'EXCEL SHEET'!R2C3

下の例に注目してください。シート名にスペースが含まれる場合、ADDRESSはシングルクォートを自動で付けてくれます。Microsoft公式ドキュメントの使用例でも、この挙動が示されています。

一方、スペースのない Sheet2 にクォートは付きません。ハイフンや括弧を含むシート名は少し注意。ADDRESSの結果を一度セルに出して、クォートの有無を目で確かめておくと安心です。

別のブックを指したい場合は、シート名を "[Book2.xlsx]Sheet1" の形式で渡します。公式の使用例では '[Book1]Sheet1'!R2C3 と返っており、この形でもクォートは自動で付きます。

ADDRESS(0,3) が #VALUE! になる理由

ADDRESS関数が返すエラーは基本的に1種類、#VALUE! だけです。行番号・列番号に1未満の数値や、数値として読めない文字列を渡したときに発生します。

数式結果原因
=ADDRESS(0,3)#VALUE!行番号が0（1以上が必要）
=ADDRESS(3,0)#VALUE!列番号が0
=ADDRESS(3,"B")#VALUE!列番号に文字列を渡している

手入力で0を書く人はいませんよね。このエラーが出るのは、引数を数式で組み立てているときがほとんどです。

たとえば行番号を計算で求めたとしましょう。引き算の結果が0以下になった瞬間に #VALUE! へ変わります。エラーが出たら、まずADDRESSの中身を別セルに出して数値を確認しましょう。

検索が空振りした場合はMATCHが #N/A を返し、それがADDRESSにも伝わります。表示を整えたいならIFERRORで包むのが手軽。

=IFERROR(ADDRESS(MATCH(F2,A:A,0),1), "見つかりません")

検索でヒットした値のセル番地を出す（ADDRESS×MATCH）

ここからが本題です。「この商品の5月の数字って、どのセル?」を数式に答えさせましょう。

例として、次のような売上表を使います。A列に商品名、1行目に月の見出しが並んだ、よくある形ですね。

行A列B列C列D列
1商品名4月5月6月
2ボールペン12,00011,50013,200
3ノート8,4009,1008,800
4クリップ3,2003,0503,400
5ファイル6,7007,2006,900
6付箋4,5005,3004,800
7ホチキス2,9003,1002,750

データは13行目まで続いている前提です。検索用の入力欄として、F2に商品名、F3に月を入れておきましょう。

ステップ1 行だけ分かればいい場合

まずは「その商品が何行目にあるか」を番地で出します。F2には「付箋」と入力してある状態です。

=ADDRESS(MATCH(F2, A:A, 0), 1)

結果は $A$6 になります。MATCH関数がA列から「付箋」を探し、6を返します。その6を受け取ったADDRESSが、6行1列目の番地を組み立てるわけです。

ここで大事な前提が1つ。MATCH関数が返すのは「範囲内の相対位置」であって、シート上の行番号ではありません。

上の式でうまくいくのは、検索範囲に A:A（A列全体）を指定しているからです。A列全体なら1番目＝1行目なので、相対位置と行番号がぴったり一致します。範囲を途中から始めた瞬間にこの一致は崩れるので、後述の症状1で詳しく扱いますね。

ステップ2 行と列の両方を検索する場合

次は列も検索して、表の中の1点を特定します。F3には「5月」と入力しておきましょう。

=ADDRESS(MATCH(F2, A:A, 0), MATCH(F3, 1:1, 0))

結果は $C$6 です。分解すると次の3ステップになります。

  1. MATCH(F2, A:A, 0) がA列で「付箋」を探して6を返す
  2. MATCH(F3, 1:1, 0) が1行目で「5月」を探して3を返す
  3. ADDRESS(6, 3) が6行3列目の番地 $C$6 を作る

1:1 は1行目全体を指す書き方。見出しが1行目にあるので、こちらも相対位置と列番号が一致します。F2とF3を書き換えれば、番地はその場で切り替わりますよ。

ステップ3 表の左上がA1でないときの起点合わせ

実務の表は、A1から始まっているとは限りません。上にタイトル行があったり、左に余白列があったりしますよね。

同じ表をB3から貼り直したケースで考えましょう。B3が「商品名」、C3からE3に月の見出し、データはB4からB15に入っています。検索欄はH2（商品名）とH3（月）とします。

=ADDRESS(MATCH(H2,B4:B15,0)+ROW(B4)-1, MATCH(H3,C3:E3,0)+COLUMN(C3)-1)

結果は $D$8 です。計算の中身を追いかけてみましょう。

  • 行: MATCH(H2,B4:B15,0) は5を返す（付箋はB4から数えて5番目）
  • 行の補正: 5 + ROW(B4) - 1 = 5 + 4 - 1 = 8
  • 列: MATCH(H3,C3:E3,0) は2を返す（5月はC3から数えて2番目）
  • 列の補正: 2 + COLUMN(C3) - 1 = 2 + 3 - 1 = 4（D列）

覚え方は「範囲の先頭セルの行番号を足して、1を引く」だけ。列も同じ理屈で、COLUMN関数に置き換えれば補正できます。

この補正を身につけておくと、表の位置が変わっても式が壊れません。汎用性がぐっと上がるので、ぜひ手を動かして確かめてみてください。

CELL(“address”, INDEX(…)) との使い分け早見表

番地を出す関数はもう1つあります。CELL関数の検査の型 "address" です。こちらは既存のセル参照から番地を読み取る関数で、指定した範囲の左上隅のセル参照を文字列で返します。

MATCHで求めた番号を直接渡せない点が、ADDRESSとの大きな違い。いったんINDEX関数でセル参照に変えてから渡す必要があります。

=CELL("address", INDEX(A1:D13, MATCH(F2,A:A,0), MATCH(F3,1:1,0)))

2つの関数の性格を並べておきましょう。

比べる点ADDRESSCELL(“address”, 対象範囲)
渡すもの行番号・列番号（数値）セル参照（範囲の左上を読む）
MATCHの結果を直接渡せるか渡せる渡せない（INDEXなどで参照に変える）
$ の付き方を選べるかabs_numで1〜4の4通り選ぶための引数がない
シート名を自分で付けられるか第5引数で付けられる付けるための引数がない
引数の省略行番号・列番号は省略不可対象範囲を省略すると計算時点で選択中のセルが対象
向いている場面これから指す番地を組み立てるすでにある参照の番地を読み取る

Microsoftは対象範囲について「参照を含めることをお勧めします」と明記しています。省略すると、そのときアクティブなセルの情報が返ってしまうためですね。

INDEXに渡す番号も「範囲の中で何番目か」である点は共通です。範囲の起点をA1にそろえておくと、ステップ3のような補正を考えずに済みます。

検査の型は "address" 以外にもたくさんあります。書式やファイル名まで取得できるので、CELL関数の検査の型12種もあわせて眺めてみてください。

エラーは出ないのに番地がズレる3つの症状と直し方

#VALUE! や #REF! は、画面に出た時点で「間違っているよ」と教えてくれます。本当に怖いのは、正しい形の番地が返るのに中身が違うケース。

数式は動いている、結果もそれらしい、でも指しているセルが1つ違う。ここでは実務で起きやすい3症状を、気づき方とセットで押さえましょう。

症状1 MATCHの戻り値は相対位置――範囲を絞った瞬間に1行ズレる

一番多いのがこれです。ステップ1の式は A:A で動いていました。ところが実務では「列全体だと重い」と考えて、範囲を A2:A13 に絞りたくなりますよね。

その瞬間、番地は静かに1行ズレます。

数式返る番地実際の位置
=ADDRESS(MATCH(F2,A:A,0),1)$A$6正しい
=ADDRESS(MATCH(F2,A2:A13,0),1)$A$51行上を指す

原因はMATCHの仕様です。A2:A13 の中ではA2が1番目なので、A6は5番目。

MATCHは5を返し、ADDRESSはそれを5行目として扱います。しかもエラーは出ません。

気づき方は2つあります。返ってきた番地のセルを実際に見て、検索値と中身が一致するか確かめる方法。もう1つは、MATCHだけを別セルに出して行番号と見比べる方法です。

見出し行を1行外したなら、ズレは必ず1行分。除外した行数がそのままズレ幅になると覚えておきましょう。

直し方は起点を足し戻すだけ。

=ADDRESS(MATCH(F2,A2:A13,0)+ROW(A2)-1, 1)

5 + 2 - 1 で6になり、$A$6 が返ります。行を挿入して表の位置が動いても、ROW(A2) が一緒にずれるので補正も自動で追いつきますよ。

症状2 シート名を渡し忘れて「今いるシート」の同じ番地を指す

「東京支店」シートのC6を取りたくて、集計シートに次の式を書いたとしましょう。

=INDIRECT(ADDRESS(6,3))

エラーは出ません。でも返ってくるのは、集計シートのC6の値です。

ADDRESSが作ったのは $C$6 という番地だけで、シート名が入っていません。だからINDIRECTは、数式のあるシートを見に行きます。

両方のシートに似た数字が並んでいると、間違いに気づくのは月末の照合作業だったりします。

気づき方はシンプル。ADDRESSの部分だけを別セルに出してみましょう。東京支店!$C$6 のようにシート名が付いていれば正解です。

数式バーでADDRESSの部分を選択し、F9キーを押す方法もあります。その場で結果を確かめられますよ（Escキーで元に戻ります）。

直し方は第5引数にシート名を渡すだけです。H1にシート名を入力しておけば、参照先を後から切り替えられます。

=INDIRECT(ADDRESS(6,3,1,TRUE,$H$1))

症状3 COUNTAの最終行が見出し行・空白行の分だけズレる

「表の一番下の値を、いつも自動で表示したい」。そんなときに使われるのが、COUNTA関数で最終行を数える方法です。

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

B1が見出しで、B2からB13までデータが埋まっていれば結果は $B$13。件数13とB列の最終行13が、たまたま一致しているわけです。

この一致には条件があります。1行目から最終行まで、B列に空白セルが1つもないこと。裏を返せば、空白が1つ混じるだけで番地は上へズレます。

B列の状態COUNTAの結果返る番地実際の最終行
B1〜B13がすべて入力済み13$B$13B13（正しい）
B7が未入力12$B$12B13（1行上を指す）
B1が空でB2〜B13に入力12$B$12B13（1行上を指す）

気づき方は、データを1行足してみることです。番地が1つ下にずれれば正常。動かなかったり飛んだりしたら、空白セルを疑いましょう。

空白が混じる表では、MAXとIFで「値が入っている最後の行」を直接探す方法が確実です。

=ADDRESS(MAX(IF(B1:B1000<>"", ROW(B1:B1000))), 2)

IF(B1:B1000<>"", ROW(B1:B1000)) は、値のある行だけ行番号を返します。MAXがその中から最大値、つまり最終行を取り出す流れ。空白がどこにあっても正しく拾ってくれます。

対応バージョン: この式は配列数式（範囲を丸ごと計算する数式）です。Microsoft 365 と Excel 2021 以降は動的配列に対応しています。そのまま Enter キーで確定して大丈夫です。Excel 2019 以前では Ctrl+Shift+Enter で確定してください。

範囲は B1:B1000 のように必要な広さに絞るのがコツ。列全体を対象にすると計算量が増え、ブックが重くなります。

#REF! が出たときはADDRESS側ではなく参照側の問題

ADDRESS関数は #REF! を返しません。文字列を組み立てるだけの関数なので、存在しないセルを指す文字列でも平気で作ってしまいます。

#REF! が出るのは、その文字列を受け取ったINDIRECTが参照に変換できなかったときです。シート名のスペルミス、削除済みのシート、クォートの付け忘れあたりが典型ですね。

つまり犯人はADDRESSの外側にいます。原因の切り分け手順は#REF!・循環参照・参照ずれの症状別診断にまとめました。そちらを見ながら1つずつ潰していきましょう。

ADDRESSで作った番地から値を取る（連携は最小限でいい）

「番地じゃなくて値がほしい」という場面もありますよね。その場合はINDIRECT関数を1つかぶせます。

ADDRESS単体とINDIRECT併用の2行比較

B3セルに 1000 という値が入っている前提で、2つの式を見比べてみましょう。

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

違いは外側の1関数だけ。INDIRECTは「文字列で書かれた番地を、本物のセル参照として読み直す」関数です。

ADDRESSが番地を書き、INDIRECTがそれを読む。この2段構えが連携の基本形になります。

ただし、なんでもINDIRECTで包む必要はありません。INDIRECTは揮発性関数（シートに変更があるたび再計算が走る性質）です。多用するとブックの動作が重くなります。

同一シート内で値を取るだけなら、INDEXとMATCHの組み合わせの方が軽快ですよ。

シート名を切り替えたいなら設計の本体はINDIRECT側

この組み合わせが本当に光るのは、シート名を変数にしたいときです。C1にシート名、A1に行番号、B1に列番号を入れておきます。

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

C1を書き換えるだけで、取得先のシートが丸ごと切り替わります。ADDRESSは番地を組み立てる部品にすぎません。設計の本体はINDIRECT側にあると考えてください。

シート名の管理方法や #REF! の防ぎ方は、専用記事で詳しく解説しています。INDIRECT関数で別シートを参照する書き方もあわせてどうぞ。

Googleスプレッドシートでも引数の構成は同じ

ADDRESS関数はGoogleスプレッドシートにもあり、5つの引数の構成は同じです。行番号と列番号から番地の文字列を受け取る、という基本の考え方もそのまま通用します。

Sheetsで使いたい方はスプレッドシート版のADDRESS関数を参照してください。入力の流れはそちらにまとめています。

まとめ｜ADDRESS関数は「番地を出したいとき」に使う

ADDRESS関数は、行番号と列番号からセル番地を組み立てる関数です。返るのは番地の文字列なので、表示・記録・連絡にそのまま使えます。

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

  • =ADDRESS(3,2) は $B$3 を返す。渡す順番は「行 → 列」
  • abs_num 1〜4は「コピーしたときに何を固定したいか」で選ぶ
  • MATCHと組み合わせれば、検索でヒットした値の番地を自動表示できる
  • MATCHの戻り値は範囲内の相対位置。範囲を絞ったら + ROW(先頭セル) - 1 で補正する
  • 番地の形は正しいのに中身が違うときは、範囲の起点・シート名・空白セルの3点を疑う
  • 値そのものがほしいならINDEX、シート名を切り替えたいならINDIRECTに任せる

「この値、どのセルにある?」という問いに数式で答えられると、確認作業の手戻りがぐっと減ります。まずはステップ1の1行から、手元の表で試してみてくださいね。

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