ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.012

参照式の参照セルが移動しても固定されるようにしたい 《INDIRECT》

2003/2007/2010/2013

セルを参照している数式(参照数式)の番地を固定したい、というお問い合わせです。

セルに例えば「=A2」という参照数式があったとして、参照されているセルA2の内容をセルB4に移動すると、「=B4」といった具合に、参照数式も移動した番地へと移動し、同じ内容を参照するようにしてくれるのです。

参照元を移動すると

ん?では、「=$A$2」とすればいいんでは・・・と思った方、一度やってみてください。
番地を固定した参照数式であれ、同じように、参照元を移動すると参照数式も番地を移動してくれます。

もちろん、これが既定となっている以上、Excelにおいてはこの方が便利なケースが圧倒的に多いわけです。
ではどうすれば、参照元を移動しても位置が固定されるような参照式が作成できるのでしょうか。

そのような場合はINDIRECT関数(関数の分類: 検索/行列)を使ってみてください。
INDIRECT関数は、普段の参照式の「=」を省いた参照文字列を引数に指定します。

例えば「=A2」であれば、「=INDIRECT("A2")」という具合です。

INDIRECT関数

INDIRECT関数の引数は、セル参照でない文字列ですから、引数にしているセルが移動したとしても数式の設定内容は変わりません。
ただし、上図のように参照元が空白の場合は「0」を返しますので、空白としたい場合は、次のようにIF関数などを使って工夫してください。

  =IF(INDIRECT("A2")=0,"",INDIRECT("A2"))

ヘルプmemo

<INDIRECT関数 (検索/行列関数)>

指定される文字列への参照を返します。
 数式: =INDIRECT(参照文字列, 参照形式)
 引数:
  参照文字列 …文字列でセル参照を指定
  参照形式 …TRUEまたはFALSEを指定
      TRUEまたは省略 …引数[参照文字列]をA1形式で指定する場合
      FALSE …引数[参照文字列]をR1C1形式で指定する場合
 ヒント:
  A1形式の参照は「A2」といった通常のセル参照、R1C1形式はセルA2の場合
  「R2C1」という行(R)と列(C)を数値で指定する参照です。

関連ヘルプ

新規メールが開くハイパーリンクをまとめて設定するには 《HYPERLINK》
重複データを調べる 《COUNTIF》
設定されているリンクを保ちながら、行と列を入れ替えて表を貼り付けたい 《配列数式/TRANSPOSE》
VLOOKUP関数の結果がエラー値の場合には空白で表示させる 《IFERROR・IF・ISERROR》
0を抜いた最小値を調べるには 《MIN・SMALL・COUNTIF・IF》
3つ以上ある範囲に条件分岐するには(多分岐) 《IF》
データに連番をふる、同じデータには同じ番号 《INDEX・MATCH・COUNTIF・MAX》
規則性がない空白セルを除いた行に連番をふるには 《IF・COUNTA・COUNTIFS》
複数条件を満たすデータの件数を求めるには 《COUNTIF・COUNTIFS・SUMPRODUCT》
奇数行の合計、偶数行の合計、n行おきの合計を求める 《配列数式/IF・SUM・MOD》
時刻を5分単位で切り上げまたは切り捨てて表示するには 《CEILING・FLOOR・ROUNDUP》
VLOOKUP関数で、複数テーブルから検索したい 《VLOOKUP・アンパサンド》
2つのデータを比較し、他方にない(両方にある)データを探す 《VLOOKUP・COUNTIF》
OR条件に当てはまるデータの合計を求める 《SUMIF・SUMIFS・SUMPRODUCT・DSUM》
日付を表す8桁の数値データを、6桁の和暦にしたい 《DATE・TEXT》
twitter hatena line pocket