OFFSET関数について②

前回はOFFSET関数の基本構文と「範囲を返す」という考え方を学びました。今回は、その応用編 ── 動的な仕組みを作る OFFSET の実用パターン を3つ紹介します。

ケース①：追加されたら、自動で集計範囲が伸びる SUM
ケース②：入力するたびに勝手に伸びるドロップダウンリスト
ケース③：最新N件だけを取り出す「動く範囲」

どれも「OFFSET でしかきれいに書けない」というほどではありませんが、OFFSET を覚えると最短で書ける ──そんな仕事に登場する場面ばかりです。

ケース①：データが追加されると、合計範囲も自動で伸びる

売上記録のように、データが日々追加される表があるとします。

:::excel
A1=日付
B1=金額
A2=2026/5/1
B2=400
A3=2026/5/2
B3=350
A4=2026/5/3
B4=500
A5=2026/5/4
B5=300
A6=2026/5/5
B6=420
:::

「B列の金額を、データが追加されても自動で合計したい」というケース。

普通に書くなら、

=SUM(B2:B6)

ですが、これだと7行目に追加されたデータは集計されません。

ここで OFFSET の出番です。

=SUM(OFFSET(B2, 0, 0, COUNTA(B2:B1000), 1))

B2 を基準
ずれゼロ（B2 のまま開始）
高さは COUNTA(B2:B1000) で「B列のデータ数」を動的に取得
幅は1列

→ データが何行あろうと、入力されている分だけが集計対象 になります。

7行目、8行目、9行目とデータが増えても、数式を書き直す必要がありません。

テーブルでもいいのでは？

そう思った方は鋭いです。レッスン070でご紹介したテーブル化（Ctrl+T）でも、同じ「自動範囲拡張」が実現できます。むしろテーブルのほうが手軽で安全です。

OFFSET を使う場面は、テーブル化ができない・したくない場面 に限られます。たとえば、

既存資料のレイアウトを維持したい
セル結合や複雑な書式がある
受け取った他人のフォーマットを変えられない
ピボットテーブルの データソースとして範囲を動的に与えたい

特に ピボットテーブルや、グラフのデータ系列の範囲を動的にする ときに、OFFSET が活躍します。テーブルでは置き換えが効かない領域があるのです。

ケース②：勝手に伸びるドロップダウンリスト

レッスン015で データの入力規則（リスト） をご紹介しました。決まった選択肢から選ばせる、あの仕組みです。

普通は「リストの元の値」に 固定の範囲 を指定します（例：=$B$2:$B$6）。けれども、選択肢が増えていく可能性があるなら、範囲も自動で広げたい ですよね。

ここで、名前の定義（レッスン055）と OFFSET の組み合わせ が活躍します。

手順

数式タブ →「名前の定義」
名前：商品リスト
参照範囲：

=OFFSET($B$2, 0, 0, COUNTA($B$2:$B$1000), 1)

データの入力規則 → リスト → 「元の値」に：

=商品リスト

商品名を1つ追加するたびに、ドロップダウンリストの選択肢も自動で1つ増えます。

「リストのメンテナンスが面倒」と思っていた業務が楽になる定番パターンです。

社内マスタを共通化したい、商品リストを部署横断で使い回したい ── そんな現場で重宝します。

ケース③：最新N件を取り出す

「いちばん最近の3件の売上だけ見たい」というケース。

:::excel
A1=日付
B1=金額
A2=2026/5/1
B2=400
A3=2026/5/2
B3=350
A4=2026/5/3
B4=500
A5=2026/5/4
B5=300
A6=2026/5/5
B6=420
A7=2026/5/6
B7=380
A8=2026/5/7
B8=450
:::

データが追加されるたびに「最新3件」が更新されます。

ネストしているCOUNTA関数は列取得をします。

=OFFSET(B1, COUNTA(B:B) - 3, 0, 3, 1)

B1 を基準に
下に「B列の入力数 - 3」行ずらす（=末尾3件の手前まで進む）
高さ3行、幅1列

これで、常に「最新3件のB列範囲」を指す ことができます。SUM で囲えば最新3件の合計、AVERAGE で囲えば最新3件の平均が出ます。

=AVERAGE(OFFSET(B1, COUNTA(B:B) - 3, 0, 3, 1))

→ 直近3日の平均売上が 常に最新の状態で計算 されます。

「直近の動きだけウォッチしたい」KPI管理や、ダッシュボード作り で本当によく使うパターンです。

OFFSET の応用は「動的グラフ」にも効く

ちょっと先取りになりますが、レッスン083以降の グラフ でも、OFFSET は活躍します。

グラフの データ系列に、固定の範囲ではなく「名前の定義 + OFFSET」を指定 すると、データの追加に合わせて グラフの折れ線や棒が自動で伸びていく ── そんな表現も可能です。

「伸びるグラフ」── 報告用ダッシュボードで「お、これは何だか動くぞ」と相手の興味を引く効果もあります。

やってはいけないOFFSETの使い方

便利な OFFSET ですが、注意すべきパターンもあります。

① 巨大な範囲で全セルにOFFSETを置く

たとえば1万行ある表の全行に OFFSET を入れる、というのは 絶対に避けて ください。揮発性関数なので、ファイル全体が極端に重くなります。

② 普通の VLOOKUP で済む話に持ち出さない

「A列の値で検索して、B列を返したい」── これは XLOOKUP（レッスン051）や VLOOKUP（レッスン048）の仕事です。OFFSET でも書けますが、わざわざ OFFSET を使う理由はありません。

③ 入れ子を深くしすぎない

OFFSET の中に OFFSET、その中にさらに OFFSET ── このような 深いネスト は、本人以外には読めない数式になります。他の関数で代用できないか、いったん立ち止まる クセをつけましょう。

OFFSET の代替を考える

最近のバージョンの Excel（Microsoft 365）では、OFFSET の役割を より新しい関数で代用 できる場面が増えています。

テーブル + 構造化参照 で「自動拡張する範囲」が作れる
FILTER関数（レッスン059）で動的な抽出ができる
TAKE関数 / DROP関数 で「先頭N件・末尾N件」を切り出せる（Microsoft 365のみ）

「新しい関数で書ける場面は、できるだけそちらを優先する」── これが、揮発性関数とうまく付き合う一番の方法です。OFFSET は 「ここでしか書けない」場面に温存 しておく。これくらいのスタンスがちょうど良いです。

100Excelでは、TAKE関数とDROP関数は触れませんが、ここまで関数を見てくれば、引数ダイアログを見るだけで使えるかなと思います。ぜひ、確認してみてくださいね。

まとめ

データの 自動拡張集計 には =SUM(OFFSET(B2, 0, 0, COUNTA(B:B)-1, 1)) パターン
入力規則のリスト を伸ばすときは「名前の定義 × OFFSET」
最新N件を取るには OFFSET(基準, COUNTA(...)-N, 0, N, 1)
動的グラフ の元範囲にもOFFSETが活躍
揮発性ゆえに 使いどころを絞る のが上級者の流儀

次回からは、もう1つの強力な動的参照ツール ── INDIRECT関数 に進みます。

OFFSET が「位置の指差し」だとすれば、INDIRECT は「文字列でセルを呼び出す」関数。発想がまた違って、面白い世界が広がります。

ショートカット

※このレッスンに対応するショートカットはありません。