OFFSET関数の使い方：行と列で指定した位置のデータを取り出す：Excel関数

OFFSET関数は、基準となるセルから、行と列で指定した位置（セル）のデータを取り出す関数です。

OFFSET関数は、一般的には他の関数と組み合わせて使うことが多い関数ですが、多少理解しにくい関数の一つなので、ここで使い方をしっかり覚えてください。使い方さえしっかり覚えてしまえば便利に使うことができます。

OFFSET関数は指定したセルの値を取り出す

対応バージョン：Excel365 2019 2016 2013 2010

OFFSET関数の書式です。

=OFFSET(参照,行数,列数,高さ,幅)
※カッコ内の「参照,行数,列数,高さ,幅」を引数といいます。
※引数〔参照〕〔高さ〕〔幅〕を使うことでセル範囲の大きさを指定できる。

引数の説明

• 参照：　基準とするセルを指定
• 行数：　〔参照〕に指定したセルから上下に移動する行数を指定。プラスの数値で下方向、マイナスの数値で上方向へ移動する行数を指定
• 列数：　〔参照〕に指定したセルから左右に移動する列数を指定。プラスの数値で右方向、マイナスの数値で左方向へ移動する列数を指定
• 高さ：　セル範囲を指定するときの行数を指定。セル範囲の行数
• 幅：　セル範囲を指定するときの列数を指定。セル範囲の列数

OFFSET関数の使い方

まず、実際のサンプルで関数のイメージを掴んでいきます。

OFFSET関数の基本的な使い方

G2セルに以下の関数を入力しています。

=OFFSET(A1,3,3)

引数〔参照〕は基準となるセルを指定しますので、ここでは「A1セル」を指定。

この「A1セル」から3行下で3列右の位置にあるセルの内容を求める指定なので、結果は「D4セルの内容の19」が求められます。

引数〔行数〕も〔列数〕も、基準となるセルの真下や真横を「1」とするので、3行下は「A4セル」で、そこから3列右は「D4セル」になります。

これがOFFSET関数の基本的な使い方ですが、OFFSET関数は他の関数と組み合わせて使うことが多い関数なので、以下ではSUM関数と組み合わせる例をご紹介します。

合計する場合はSUM関数：Excel関数

OFFSET関数とSUM関数で合計を求める

G4セルには以下の関数を入力しています。

＝SUM(OFFSET(B1,0,0,6,1))

SUM関数の引数にOFFSET関数を指定します。

まず、OFFSET関数でSUM関数で合計する範囲を求めます。引数〔参照〕〔高さ〕〔幅〕を指定することでセル範囲を指定できます。

OFFSET関数では、B1セルを基準として、そこから下に「0」右に「0」の位置、つまり、B1セル自体から〔高さ〕が「6」行、〔幅〕が「1」列を合計の範囲と設定しています。

次に、SUM関数でOFFSET関数で求めた合計範囲の合計を算出し結果を返すという流れです。

OFFSET関数に複数の関数を組み合わせて使う

前述したようにOFFSET関数は単独で使われることが少ない関数ですが、他の関数と組み合わせることで非常に便利な関数となります。

そんな例をご紹介します。

以下の表は、商品別に売上金額を記録した単純なものです。商品点数も少ないですから売上合計を計算するのも簡単な作業です。売上金額が入力されているセル範囲をSUM関数で合計するだけですから。

しかし、この表には新たな商品が追加されていくことも考えられます。商品が追加されていけば、そのたびに関数を修正しなければ使えませんので、面倒な作業となってしまいます。

そこで、商品が追加されるごとに関数を修正しないように、COUNT関数を使って入力されているデータの件数を数え、それをOFFSET関数の引数に使う方法を解説します。

SUM関数/OFFSET関数/COUNT関数を組み合わせる

G1セルには以下の関数を入力しています。

＝SUM(OFFSET(C2,0,0,COUNT(C:C),1))

少し複雑になりますが、それぞれのやっていることは単純です。

１．まず関数式の一番内側の「COUNT(C:C)」は、数値（日付や時刻を含む）の個数を数えて結果を返します。文字や空白は数えません。

COUNT関数の引数の「C:C」は、C列の先頭から最後尾までを指定する意味ですが、文字列や空白は対象外となるので先頭行の「見出し文字」は無視され、また、空白の行も無視され、数値（この場合は売上金額）が入力されているセルの個数だけ数えて結果を返します。

これで、有効なデータが入力されているデータの件数が求まりました。サンプルの場合なら「5」となります。

COUNT関数・COUNTA関数で数値の数を数える/COUNTBLANK関数で空白の数を数える：Excel関数

２．次に、OFFSET関数でデータが入力されている範囲を選択します。

OFFSET(C2,0,0,COUNT(C:C),1)

パラメータの説明

1. 〔参照〕：C2セルを基準にする（売上金額の先頭）
2. 〔行数〕：C2セルから行は移動しないので「0」
3. 〔列数〕：C2セルから列は移動しないので「0」
4. 〔高さ〕：COUNT関数の戻り値を指定。この場合なら「5」
5. 〔幅〕：セルの選択範囲の幅はC列の1列だけなので「1」

ここまでで、サンプル画像の「C2セルからC6セル」までが選択されました。

３．最後にSUM関数で選択されたセル範囲の合計を求めています。

まとめ

OFFSET関数は単独で使う場面が少ない関数です。

しかし、今回のサンプルのように、選択すべき範囲が固定されていなく常に変動するような場合に役立つ関数です。後からデータを追加しても自動で最新のデータ件数が求められますので、都度、関数式を変更する必要もなく、実際の運用で役立つこと間違いないでしょう。

OFFSET関数は、多少理解しにくい面もありますが、1ステップずつ組み立てて行けば上手く使えるようになります。

OFFSET関数と同じ分類の関数

OFFSET関数と同じ「検索/行列」系の関数は以下のページで探せます。