ExcelのOFFSET関数の使い方をお探しですね。
広告
ExcelのOFFSET関数を使いこなそう！基準セルからずらして範囲を取得する方法
ExcelのOFFSET関数って、最初はちょっととっつきにくいかもしれません。
でも、使い方が分かってくると「こんなこともできるんだ!」と驚くくらい便利な関数なんです。
OFFSET関数は、基準にしたセルから「下に○行」「右に○列」という感じで移動して、移動先のセルや範囲を取ってこられる関数です。
たとえば、A1セルを起点にして「1行下、0列右」と指定すればA2セルを参照できますし、「0行下、1列右」と指定すればB1セルを参照できます。
普通の関数は計算結果として数字や文字を返すことが多いですよね。
でもOFFSET関数は「セルそのもの」や「セルの範囲」を返すのが特徴です。
だから、SUM関数やAVERAGE関数みたいに範囲を受け取る関数と組み合わせると、すごく実用的に使えるようになります。
OFFSET関数の基本的な書き方
OFFSET関数の基本の形は、こんな感じです。
“`excel
=OFFSET(基準, 行数, 列数, [高さ], [幅])
“`
それぞれの意味を見ていきましょう。
**基準**は、スタート地点となるセルです。
ここから移動を始めます。
**行数**は、上下にどれだけ動くかを指定します。
プラスの数字なら下に、マイナスの数字なら上に動きます。
**列数**は、左右にどれだけ動くかです。
プラスなら右に、マイナスなら左に動きます。
**高さ**と**幅**は省略してもOKです。
移動した先を起点に、縦何行×横何列の範囲を取ってくるかを決めます。
単独のセルだけ参照したいなら、書かなくても大丈夫です。
簡単な例で見てみよう
たとえば、A1セルから1行下のA2セルを取得したいときは、こう書きます。
“`excel
=OFFSET(A1,1,0)
“`
A1セルから右に1列移動してB1セルを取得したいときは、こうです。
“`excel
=OFFSET(A1,0,1)
“`
さらに、A1セルを基準にして、動かずに縦3行・横2列の範囲(つまりA1:B3)を取得したいときは、こんな感じ。
“`excel
=OFFSET(A1,0,0,3,2)
“`
ただし、OFFSET関数は「他の関数に渡す範囲を作る関数」と考えた方が分かりやすいです。
セルに単独で入力しても、期待通りに表示されないことがあるんです。
新しいExcel(365や2021以降)なら範囲が展開されることもありますが、基本的には他の関数と組み合わせて使うものだと思ってください。
実際にセルをずらしてみよう
OFFSET関数を使うときは、まず「どのセルを基準にするか」を決めます。
そして、そこから目的のセルまで何行・何列ずれているかを数えればOKです。
たとえば、B2セルを基準にして、D5セルを参照したいとします。
B2からD5へ行くには、下に3行、右に2列動けばいいですね。
だから、数式はこうなります。
“`excel
=OFFSET(B2,3,2)
“`
Excelの表を見ながら「下に何行か」「右に何列か」を数えれば、目的のセルにたどり着けます。
上や左に動きたいときは、マイナスの数字を使います。
たとえば、C3セルを基準に1行上のC2セルを参照するなら行数に「-1」、1列左のB3セルを参照するなら列数に「-1」を指定します。
範囲を取得する方法
範囲を取ってきたいときは、「高さ」と「幅」を使います。
たとえば、B2セルから下に1行、右に2列移動したD3セルを起点に、5行×3列の範囲を取得したい場合は、こう書きます。
“`excel
=OFFSET(B2,1,2,5,3)
“`
この式では、まずB2からD3へ移動して、そこから縦5行・横3列の範囲を参照しています。
「移動先のセルを決める」ことと「移動先からどれくらいの大きさを取るか」を分けて考えると、分かりやすくなりますよ。
別シートのセルも参照できる
別シートのセルを基準にすることもできます。
たとえば、Sheet2のC3セルから1行下を参照する場合は、こんな感じです。
“`excel
=OFFSET(Sheet2!C3,1,0)
“`
シート名の後に「!」を付けてセルを指定すれば、別シートのセルを基準にできます。
月別データを別シートに分けている場合なんかでも使えますね。
基準セルは固定した方がいい場合も
OFFSET関数を使うときに大事なのが、基準セルを固定するかどうかです。
数式をコピーして使う場合、基準セルが相対参照のままだと、コピー先に合わせて基準位置もずれちゃいます。
基準セルを動かしたくないときは、`$B$2`みたいに絶対参照にしましょう。
“`excel
=OFFSET($B$2,3,2)
“`
こうしておけば、数式を下や右にコピーしても基準セルが変わりません。
OFFSET関数は位置関係を指定する関数なので、基準がずれると結果も大きく変わっちゃいます。
思った値が返ってこないときは、まず基準セルが正しい場所を指しているか確認してみてください。
他の関数と組み合わせて便利に使おう
OFFSET関数が本当に力を発揮するのは、他の関数と組み合わせたときです。
SUM関数との組み合わせ
SUM関数と組み合わせると、合計する範囲を自由に変えられます。
たとえば、B2:B11に売上データが並んでいて、E1セルに「何行分を合計するか」を入力するとします。
B2から始まる範囲を、E1セルの数だけ縦に広げて合計するなら、こう書けます。
“`excel
=SUM(OFFSET(B2,0,0,E1,1))
“`
この式では、B2セルを基準に動かず、E1セルに入力された行数分だけ高さを変えています。
E1に「3」と入力すればB2:B4の合計、「5」と入力すればB2:B6の合計になります。
「売上ランキングの上位何件を合計する」とか「直近何か月分を集計する」といった場面で、すごく便利です。
MATCH関数との組み合わせ
MATCH関数と組み合わせると、表の見出しやキー項目から目的の位置を探して、その結果をOFFSET関数の行数・列数に渡せます。
たとえば、縦に商品名、横に月名が並ぶ売上表で、指定した商品と月の交差する値を取得したい場合はこんな感じです。
“`excel
=OFFSET(B2,MATCH(E2,B3:B10,0),MATCH(F2,C2:H2,0))
“`
この例では、B2セルを表の左上の基準位置にして、E2セルに入力した商品名がB3:B10の何番目にあるかを行方向の移動量に、F2セルに入力した月名がC2:H2の何番目にあるかを列方向の移動量にしています。
交差表から値を取り出す場面で、とても役立ちますよ。
COUNTA関数との組み合わせ
COUNTA関数と組み合わせると、入力済みデータの数に応じて参照範囲を自動で広げられます。
たとえば、B列に名前の一覧があって、B1が見出し、B2以降にデータが入力される場合、入力済みの名前だけを範囲として参照するには、こう指定できます。
“`excel
=OFFSET(B2,0,0,COUNTA(B:B)-1,1)
“`
COUNTA関数は、空白じゃないセルの数を数える関数です。
この例では、B列全体の入力数から見出し分の1を引いて、名前が入力されている行数だけを高さにしています。
入力規則のリストにこの考え方を使えば、新しい名前を追加したときにプルダウンの選択肢を自動で増やすこともできます。
ただし、途中に空白セルがあると正しく数えられない場合があるので、一覧表は空白行を作らず連続して入力するのが基本です。
VLOOKUP関数の弱点をカバー
OFFSET関数は、VLOOKUP関数では取りにくい「検索列より左側の値」を取得したいときにも使えます。
VLOOKUP関数は検索範囲の左端列をキーにする必要があるので、キー項目の左にある単価や分類を返すのが苦手なんです。
こんなとき、MATCH関数で検索値の行位置を探して、OFFSET関数で左方向に移動すれば、検索値より左にあるデータも取得できます。
ただし、最近のExcelではXLOOKUP関数やINDEX関数とMATCH関数の組み合わせでも同じことができます。
OFFSET関数は便利ですが、数式の読みやすさも考えて使い分けるのが大切です。
注意点とエラーを防ぐコツ
行数と列数を間違えないように
OFFSET関数でよくあるミスが、行数と列数の方向を間違えることです。
**行数は縦方向**の移動で、プラスなら下、マイナスなら上。
**列数は横方向**の移動で、プラスなら右、マイナスなら左。
たとえば、右に移動したいのに行数に数値を入れると、下方向のセルを参照しちゃいます。
「行は上下、列は左右」と覚えておきましょう。
エラーに気をつけよう
移動先がワークシートの外に出る場合は`#REF!`エラーになります。
たとえば、A1セルを基準にして1行上へ移動する指定は、Excel上に存在しないセルを参照しようとするのでエラーになります。
また、行数・列数・高さ・幅に文字列など不適切な値を指定すると、`#VALUE!`エラーになることがあります。
高さや幅に0以下の値を指定した場合も、エラーにつながりやすいので、基本的には正の整数を指定しましょう。
大量に使うと重くなることも
OFFSET関数は「揮発性関数」と呼ばれるタイプの関数です。
これは、シート内で変更があるたびに再計算されやすい関数のこと。
少数の数式で使う分には問題ないんですが、大量のセルにOFFSET関数を入れると、ブックの動作が重くなる場合があります。
数千、数万行規模のデータで多用する場合は、Excelテーブル、INDEX関数、XLOOKUP関数、FILTER関数などで代替できないか検討してみてください。
特に、可変範囲を作る目的でOFFSET関数を使う場合は、Excelの「テーブル」機能も有力な選択肢です。
テーブル化しておけば、データを追加したときに参照範囲が自動で拡張されて、数式も読みやすくなります。
とはいえ、入力規則のリストや名前の定義、動的な集計範囲の作成など、OFFSET関数ならではの柔軟さが活きる場面もたくさんあります。
大事なのは、何でもOFFSET関数で解決しようとするんじゃなくて、「基準からずらして範囲を作りたい」という目的がはっきりしている場面で使うことです。
まずは簡単な式から試してみよう
OFFSET関数を正しく使うには、いきなり複雑な数式を作らないことも大切です。
まずは`=OFFSET(A1,1,0)`みたいな単純な式で、どのセルが返るかを確認してみましょう。
次に高さと幅を加えて範囲を取得して、最後にSUM関数やMATCH関数と組み合わせると、数式の意味を追いやすくなります。
複雑な表で使う場合は、基準セルを表の左上や見出しの交点など、数えやすい場所に置くとミスを防げますよ。
まとめ
OFFSET関数は、基準セルから指定した行数・列数だけずらしたセルや範囲を取得できる、とても便利な関数です。
最初は分かりにくく感じるかもしれませんが、SUM関数で可変集計を作ったり、MATCH関数で表から値を取り出したり、COUNTA関数で入力済みの範囲だけを参照したりと、実務で役立つ使い道がたくさんあります。
行数・列数の方向、エラー条件、再計算の負荷に注意しながら使えば、Excelでの集計や参照をもっと柔軟に設計できるようになりますよ。
ぜひ試してみてください!
広告
