[Excel] OFFSET関数でセル範囲を参照する

今回は、Excel(エクセル)のOFFSET関数を使ってセル範囲を参照してみます。数式でセルを絶対参照したとしても、行を挿入・削除したりすると参照範囲が変わってしまう場合がありますが、OFFSET関数ならば行を挿入・削除しても変わらない完全な絶対参照が可能です。

OFFSET関数の特徴: セル範囲を絶対参照できる

指定したセル範囲を参照するOFFSET関数は、他の関数と組み合わせて活用するタイプの関数です。行の挿入・削除などの操作にかかわらず、常に指定したセル範囲を参照することができます。今回は、SUM関数で直接セル範囲を指定して合計する方法と、OFFSET関数と組み合わせてセル範囲を指定する方法を比較していきます。

SUM関数でセル範囲を絶対参照した場合

今回の例では、B2～D4まで9つのセルの値を合計する数式を入力していきます。はじめに、SUM関数でセル範囲を参照してみましょう。数式を入力するセル(画面の例ではA1)を選択して、数式バーに「=SUM(」と入力(図1)したら、「$B$2:$D$4」と「$」と付けた絶対参照でセル範囲を指定します(図2)。[Enter]キーを押して数式を確定させると、B2～D4の9つのセルの合計値が表示されます(図3)。

• (図1)A1セルを選択して数式バーに「=SUM(」と入力します

• (図2)「=SUM($B$2:$D$4)」と入力して絶対参照でB2～D4セルを指定します

• (図3)B2～D4の9つのセルの合計値が表示されます

SUM関数は行の挿入や削除に対応できない

しかし、この状態で2行目と3行目の間に行を挿入してみると、どう変化するでしょう？ (図4)。A1セルをクリックして数式を確認してみると、参照範囲がB2～D5の12のセルに変更されてしまっています(図5)。この状態で挿入した3行目に数値を入力すると、12のセルの合計が算出され、当初の「B2～D4の9つのセルの合計」という条件が崩れてしまいます(図6)。

• (図4)2行目と3行目の間に行を挿入してみます

• (図5)A1セルの数式が「=SUM($B$2:$D$5)」になり、セル範囲が変わってしまいました

• (図6)3行目に数値を入力すると、当初の条件とは異なり12のセルの合計が表示されてしまいます

行を削除する場合も同様です。たとえば4行目を選択して削除してみると(図7)、A1に設定した数式が「=SUM($B$2:$D$3)」に変わってしまいます(図8)。ここから4行目に再度数値を入力しても、B2～D3の6つのセルの合計値が表示されてしまい、こちらも「B2～D4の9つのセルの合計」という条件が崩れています(図9)。このように、絶対参照でセル範囲を指定しても、行の挿入・削除などを行うことで参照範囲が変わってしまいます。

• (図7)4行目を右クリックして[削除]を選択します

• (図8)A1セルの数式が「=SUM($B$2:$D$3)」に変更されてしまいます

• (図9)4行目に数値を入力しても、B2～D3の6つのセルの合計が表示されます

OFFSET関数の基本数式は、OFFSET(基準のセル, 行数, 列数, 高さ, 幅)

シートの編集を行っても、セルの指定範囲を変えたくないという場合は、OFFSET関数を利用しましょう。先ほどと同じくA1セルを選択したら、数式バーに「=SUM(OFFSET(」と入力します(図10)。続けて「A1,1,1」と入力して基準となるセル(A1)と、そこから1行目(,1)、1列目(,1)となるB2セルを指定します(図11)。さらにB2セルから高さ(指定したい行範囲)と幅(指定したい列範囲)を指定し、「=SUM(OFFSET(A1,1,1,3,3))」という数式を完成させます。

OFFSET関数で参照しているのは、A1セルから1行目、1列目にあるB2セルから3行、3列のセル範囲で、B2～D4の9つのセルになります(図12)。[Enter]キーを押して確定させると、図3と同様にB2～D4のセルの合計値が表示されます(図13)。

• (図10)A1セルを選択して数式バーに「=SUM(OFFSET(」と入力します

• (図11)「=SUM(OFFSET(A1,1,1」と続けて基準のセル(A1)とそこからの行数(1)、列数(1)と指定します

• (図12)さらに指定したい行範囲(3)と列範囲(3)を指定し「=SUM(OFFSET(A1,1,1,3,3))」という数式を完成させます

• (図13)B2～D4の9つのセルの合計値が表示されます

OFFSET関数なら、行の挿入や削除にも自動対応

この状態で、2行目と3行目の間に行を挿入してみましょう(図14)。図5のときとは異なり、行を挿入しても数式は変更されません(図15)。挿入した行に数値を入力すれば、図6とは異なり、正しく「B2～D4の9つのセルの合計」を求めることができます(図16)。

• (図14)2行目と3行目の間に行を挿入してみます

• (図15)OFFSET関数を用いた場合は、行を挿入しても数式は変更されません

• (図16)挿入した行に数値を入力すれば、正しくB2～D4の9つのセルの合計値が表示されます