Excelで一つ下のセルを参照する方法｜数式でずれない書き方【実例つき】

Excelで「一つ下のセルの値」を横に並べたくて =A3 と打ち、下方向にコピーしたら——参照が全部ずれてぐちゃぐちゃになった。そんな経験はないでしょうか。「常に一つ下を見る」という関係を数式そのものに持たせるには、単純な直接参照だけでは足りません。

この記事を読むと、OFFSET と INDIRECT を使って「行を挿入・削除しても、下にコピーしてもずれない」動的な参照を作れるようになります。先に結論だけ言うと、移動量を数値で柔軟に指定したいなら OFFSET、セル番地やシート名を文字列で組み立てたいなら INDIRECT です。この違いを、一つの表を最後まで使い回しながら確かめていきましょう。

Excelで一つ下のセルを参照する方法｜数式でずれない書き方【実例つき】のサンプル ▲ Excel サンプル

まず「=A3」で足りるか自問する

今回は「日別の売上を入力した表で、各行の隣に翌日の売上を並べたい」というシナリオで進めます。

行	A列（日付）	B列（売上）	C列（翌日の売上を表示したい）
2	6/1	10,000	←ここに 6/2 の売上を出したい
3	6/2	12,000	←ここに 6/3 の売上
4	6/3	9,000	…

C2 に翌日の売上（B3）を出すだけなら、次で十分です。

=B3

固定の一箇所だけならこれがベストです。まずは「単純参照で足りないか？」を自問する——これが乱用を防ぐ第一歩です。ただしこの式を C3 C4 へコピーすると参照は素直にずれてくれる一方、行を途中に挿入するとロジックが崩れます。「常に一行下」という意味を数式に持たせたいときに、次の二つが効いてきます。

OFFSET関数で一つ下のセルを参照する

OFFSET は「基準セルから、指定した行数・列数だけ移動した先のセル」を返します。

=OFFSET(基準, 行の移動量, 列の移動量)

一つ下なら行の移動量を 1、列の移動量を 0。C2 に入れるなら次の通りです。

=OFFSET(B2, 1, 0)

「B2 を基準に1行下・0列横」つまり B3（12,000）が返ります。

OFFSETが便利な理由

強みは、移動量を数式で指定できる点です。E1 に「何行下を見るか」を入れておけば——

=OFFSET(B2, E1, 0)

E1 を 1 にすれば翌日、2 にすれば翌々日、と値を変えるだけで参照先が動きます。ドロップダウンと組み合わせれば簡易検索ツールにもなります。

注意点と代替

OFFSET は「揮発性関数」で、シートを操作するたびに再計算されます。数万行で多用するとファイルが重くなるので使いどころは絞りましょう。動作を軽くしたいなら、非揮発性の INDEX で代用できます。

=INDEX(B:B, ROW()+1)

同じ「一つ下」を、再計算の負荷を抑えて実現できます。

INDIRECT関数で一つ下のセルを参照する

INDIRECT は「文字列で表したセル番地」を実際の参照に変換します。

=INDIRECT("B3")

これで B3 が返りますが、固定では意味がありません。行番号を計算して文字列を組み立てます。C2 に入れるなら——

=INDIRECT("B" & (ROW()+1))

ROW() は数式が入っているセルの行番号を返します。C2 なら 2 なので ROW()+1 は 3。"B" とつないで "B3" を作り、その値を参照します。下にコピーしても、それぞれの行を基準に常に「一つ下」を見てくれるのがポイントです。

シート切り替えとよくあるエラー

INDIRECT はシート名を変数として扱えます。F1 にシート名を入れておけば——

=INDIRECT("'" & F1 & "'!B3")

F1 を変えるだけで参照シートを切り替えられ、複数シートの同じ位置を集計するときに重宝します。

ここで詰まりやすいのが #REF! エラーです。多くはシート名やセル番地の文字列が実在しないことが原因です。特にシート名に空白や記号（6月集計 など）が含まれる場合は、上の式のように名前を '（シングルクォート）で囲むと解決します。エラーが出たら、まず組み立てた文字列が正しい番地になっているかを確認しましょう。