エクセルでOFFSET関数を使う方法

OFFSET関数を使うと、Excelでセルやセル範囲を間接的に参照することができます。OFFSETを使うと、直接セルを参照するのではなく、開始する参照点を選んで、対象のセルへのアドレスを入力することができます。

このため、OFFSET関数は動的なExcelスプレッドシートで非常に有用です。OFFSET関数は単体でも使えますが、Excelの他の関数の中に入れ子にしてこそ、その真価が発揮されるのです。ここでは、3つの例を挙げながら、ExcelのOFFSETについて詳しく説明します。

ExcelのOFFSET関数とは？

OFFSET関数は、あるセルまたはセル範囲への参照を返します。OFFSETは5つのパラメータを取り、その構文は以下のとおりです。

=OFFSET(reference, 行数, 列数, 高さ, 幅)

OFFSETは、参照セルの位置を取り込み、そこから行数、列数だけ移動し、移動先のセルの参照を返します。

高さと幅のパラメータが1より大きい値に設定されると、その領域内のセルの範囲が返される。高さと幅は2つのオプションのパラメータである。空白のままにしておくと、OFFSETはそれらを1として読み、1つのセル参照を返します。

エクセルでOFFSET関数を使用する方法

エクセルでOFFSETを使うには、必要な3つのパラメータを入力する必要があります。参照セルを設定すると、OFFSETはそのセルから行数パラメータの数だけ下のセルに移動します。また、colsパラメータの数だけ右へ移動します。

それ以降、OFFSET は目的地セルへの参照を返します。高さと幅のパラメータも設定すると、OFFSET はセルの範囲への参照を返し、元の目的セルは左上のセルとなります。

紙面では複雑に聞こえるかもしれませんが、実際にやってみるととても分かりやすいですよ。それでは、OFFSET関数がどのように機能するか、いくつかの例で見てみましょう。

1.OFFSET 機能の例。1つのセルを参照する

まず始めに、OFFSET関数を単体で使用して、OFFSET関数がどのようにスプレッドシートを操作しているかを把握しましょう。この例では、Excelのスプレッドシートにたくさんの数字が表示されています。C4を参照するためにOFFSET関数を使用します。

OFFSET関数を素直に使うと、出力先のセルの内容が出力されます。ですから、数式を入力すれば、セルC4の内容、つまり数字の5が出力されるはずです。さっそく始めてみましょう。

数式を入力するセルを選択します。ここでは、セル G1 を使用します。

数式バーに次の数式を入力します：=OFFSET(A1, 3, 2) この数式は、OFFSET関数を呼び出して、セルA1の位置を取り込み、3セル分下に移動し、2セル分右に移動してセルC4に到達するものです。

Enterキーを押します。

Enterを押して数式が実行されると、セルには5が返されます。これはセルC4の数値なので、数式はうまくいきましたねOFFSETのようなExcelの数式をより理解するために、評価機能を使うこともできます。

2.OFFSET関数の例セルの範囲を参照する

高さと幅という2つのオプションのパラメータを使用すると、ExcelのOFFSET関数でセルの範囲を返すこともできます。それでは、前回の例題の続きを見てみましょう。

今回は、ExcelのOFFSETを使って、セルC4からD9を返します。前回と同様に、A1を開始の基準点として使用します。

数式を入力するセルを選択します。ここでは再びセル G1 を使用します。

数式バーに次の数式を入力します:=OFFSET(A1, 3, 2, 6, 2) この数式は前の数式とほぼ同様に機能しますが、目的のセル (C4) に達すると、目的のセルだけでなく 6 行 2 列を取り込むという点が異なります。C4は新しい範囲の最初のセルになります。

Enterキーを押します。

これで、数式を入力した場所にセル範囲C4からD9が表示されるはずです。出力は配列になります。

3.OFFSET関数の例複合的な数式

これ以前の2つの例は、OFFSETがExcelでどのように機能するかを見るためのウォームアップでした。OFFSETは通常、実用的な場面では他のExcel関数とネストして使用されます。それでは、新しい例で説明しましょう。

この例では、2017 年から 2021 年にかけてのサイドプロジェクトの所得を表示しています。上側には、2021年からの所定の年数における総収入を表示することになっているセルがあります。

ここでの目的は、ExcelのOFFSETを使用して動的なスプレッドシートを作成することです。合計の年数を変更すると、この値を計算する式も更新されるはずです。

この目標を達成するために、SUM関数と一緒にOFFSET関数を使用します。セルを直接参照するのではなく、OFFSETを使用するのです。こうすることで、SUMに組み込まれるセルの範囲を動的に取得することができます。

この例では、2021 年がセル H5 に、合計の年数がセル C1 に入っています。年数を含むセルでは、カスタム書式を使用しています。実際の値は年数で、「年」のテキストは接尾辞です。まず、合計を計算する数式を作成しましょう。ここではExcelのSUM関数を使用します。

合計を計算したいセルを選択します。

数式バーに、以下の数式を入力します：=SUM(OFFSET(H5,0, 0, 1, -C1)) この数式は、OFFSET と SUM で構成されています。最初のステップで、OFFSET はセル H5 に移動します。そこから 0 行 0 列で移動し、H5 から始まるセル範囲を返します。この選択範囲は高さが1セル、幅が-C1セルになります。つまり、OFFSET は H5 から始まり、C1 セルだけ左に移動する範囲を返します。

最後に、SUM関数で範囲内の数値を足し合わせて出力します。

Enterキーを押してください。