ExcelのOFFSET関数の使い方を徹底解説【MATCH関数との組み合わせ・動的範囲の作成】

今回は、ExcelのOFFSET関数について、実務でそのまま使える3つの事例を交えてご紹介します。

まず関数の構文を解説し、その後、OFFSET関数を実際の業務の問題解決にどう活かせるのかを具体的に見ていきましょう。

OFFSET関数とは

OFFSET関数は、基準となるセル(参照セル)または範囲(参照範囲)から、指定した行数・列数だけ離れた位置にあるセル(ターゲットセル)や範囲(ターゲット範囲)への参照を返す関数です。

下の図は、OFFSET関数を使ってセル(左側)または範囲(右側)への参照を取得する仕組みを示したものです。

ターゲットセルと参照セルの関係が直感的にイメージできるはずです。

緑色で強調表示されたセルがターゲットセル、黄色のセル群がターゲット範囲、青色のセルが参照セルに該当します。

図1

OFFSET関数の構文

OFFSET関数の構文は次の通りです。
OFFSET(参照, 行数, 列数, [高さ], [幅])

参照(Reference)	必須。オフセットの起点となるセルまたはセル範囲を指定します。セル範囲を指定する場合は、隣接したセルである必要がある点に注意してください。
行数(Rows)	必須。参照セル(または参照範囲の左上端のセル)から上下に何行ずらすかを指定します。正の数も負の数も指定可能です。図1の左側の例で、関数を「=OFFSET(C3, -1, -1)」に変更すると、ターゲットセルはB2になります。B2はC3の一つ上の行にあるためです。
列数(Cols)	必須。参照セル(または参照範囲の左上端のセル)から左右に何列ずらすかを指定します。行数と同様に、正負どちらの値も使えます。たとえば、参照セルをB4、ターゲットセルをC3とすると、答えは「=OFFSET(B4, -1, 1)」です。C3はB4より一つ右の列にあるため、列数は正の値になります。
高さ(Height)	省略可能。ターゲットが範囲の場合にのみ使用します。ターゲット範囲に含まれる行数を指定し、必ず正の数である必要があります。図1の右側の例では、ターゲット範囲が2行あるため、高さは「2」を設定しています。
幅(Width)	省略可能。こちらもターゲットが範囲の場合にのみ使用します(図1の右側を参照)。ターゲット範囲に含まれる列数を指定し、正の数である必要があります。

それでは、OFFSET関数を実務の課題解決に活用する方法を見ていきましょう。

ケース1:OFFSET関数+MATCH関数の組み合わせで右から左への検索を実現

VLOOKUP関数では「左から右」への検索しかできないことはよく知られています。検索値は必ず表範囲の先頭列に配置しなければなりません。

新しい検索値を追加したい場合には表全体を一列右へずらす必要があり、別の列を検索値にしたい場合にはデータ構造自体を変更しなければなりません。

しかし、OFFSET関数とMATCH関数を組み合わせれば、VLOOKUP関数のこの制限を解消できます。

MATCH関数とは何か、そしてOFFSET関数とどう組み合わせて検索を行うのかを見てみましょう。

MATCH関数は、指定した値をセル範囲の中から探し、その範囲内での相対的な位置を返す関数です。

図2.1の範囲B3:B8(国別・年別の売上データ)を例に挙げます。

数式「=MATCH("USA", B3:B8, 0)」は、USAが範囲内の最初の項目であるため1を返します(セルB10・C10を参照)。

同様に、範囲C2:F2に対する「=MATCH(2015, C2:F2, 0)」は、2015が範囲内の3番目の項目であるため3を返します(セルB11・C11を参照)。

ここでOFFSET関数に戻りましょう。参照セルをB2、ターゲットセルをE3とした場合、OFFSETの数式はどう書けばよいでしょうか?

E3はB2の1行下、3列右にあります。

したがって、数式は「=OFFSET(B2, 1, 3)」となります。赤色の数字に注目してください。MATCH関数が返した数字と一致していることに気づきますか?

これこそが「OFFSET関数とMATCH関数を組み合わせる方法」の答えです。MATCH関数は、OFFSET関数の第2引数または第3引数として使うことができます(セルC13を参照)。

セルC14は、同じデータをVLOOKUP関数で取得する例です。

VLOOKUP関数を使う前に、「2015年の売上が表範囲B2:F8の4列目にあること」を把握しておく必要があります。

つまり、VLOOKUP関数ではデータ構造を十分に理解していなければならないのです。これもVLOOKUP関数の制限の一つです。しかし、MATCH関数をOFFSET関数の引数として使えば、列番号を知らなくても済みます。

列数が多い表では、この方法が非常に役立ちます。

図2.1

続いて、もう少し複雑な例を見てみましょう。

会社名・担当者名・メールアドレスを含む表があるとします。「担当者名から会社名を取得したい」「メールアドレスから担当者名を取得したい」という場合、どうすればよいのでしょうか?

図2.2では、範囲B5:E8に会社情報が入力されています。セルC2とセルB3に値を入力すれば、赤枠内の数式によって、担当者名から会社名を取得できます。

範囲D2:E4は、既知のメールアドレスから担当者名を取得する方法を示しています。

まとめると、この2つの例は「右から左への検索が可能であり、検索値を最右列に置く必要がない」ことを示しています。表内のどの列に検索値があっても問題ありません。

図2.2

ケース2:OFFSET関数+COUNT関数の組み合わせで計算を自動化

列に新しい数値を追加するたびに計算を自動更新する方法を紹介する前に、まず「列の最後の数値を自動的に取得する方法」から始めましょう。

下の図は人事部門の入力データを示しています。C列の最後の数値を取得したい場合、OFFSET関数を使うと数式は「=OFFSET(C2, 9, 0)」となります。

この数式から、9という数字が鍵であることが分かります。この数字さえ自動的に求められれば、列の最後の数値を自動的に特定できます。

9は、C列に含まれる数値セルの個数にほかなりません。

COUNT関数をご存じなら、この関数が範囲内の数値セルの個数を数えるものであることがお分かりでしょう。

たとえば「=COUNT(C3:C11)」は、C3からC11までの数値セルの個数をカウントします。

今回のように列全体の数値の個数を知りたい場合は、C列の全行を含む「C:C」のような参照を使います。

セルG4とH4を見てください。「=COUNT(C:C)」が返す値は、まさに9です。

そこで、先ほどのOFFSET関数の「9」をCOUNT(C:C)に置き換えると、新しい数式「=OFFSET(C2, COUNT(C:C), 0)」(セルH5)が得られます。

この数式が返す値は87000で、まさにC列の最後の数値です。

次に自動計算に進みましょう。C列のすべての数値の合計を求めたいとします。

SUM関数とOFFSET関数を組み合わせると、数式は「=SUM(OFFSET(C2, 1, 0, 9, 1))」となります。

9は範囲C3:C11の行数であり、同時にC列の数値セルの総数でもあります。

したがって、数式は「=SUM(OFFSET(C2, 1, 0, COUNT(C:C), 1))」という形で書き換えられます。

セルG10とH10を見ると、9人の給与合計は$521,700です。

ここでセルC12に$34,000などの数値を入力すると、セルG5とG10の値はそれぞれ$34,000と$555,700に自動的に変わります。

セルG5やG10の数式を更新する必要がない――これこそが「自動化」です。

COUNT関数を使う際は注意が必要です。COUNT関数は数値が含まれるセルの個数しか返しません。

たとえば「=COUNT(B:B)」は、B列に数値セルが存在しないため、9ではなく0を返します(セルG3・H3を参照)。

D列には数値セルが10個あり、「=COUNT(D:D)」も10を返します。

しかし、C列と同じ要領でD列の最後の数値を取得しようとすると、結果は0になってしまいます(セルG8・H8を参照)。

明らかに0は期待した結果ではありません。何が問題なのでしょうか? 実は、セルD13はセルD2から10行ではなく11行離れているのです。

これはセルG7の数式「=OFFSET(D2, COUNT(D:D) + 1, 0)」でも確認できます。

まとめると、COUNT関数とOFFSET関数を組み合わせて計算を自動化するには、数値が連続して隣接している必要があります。

図3

ケース3:OFFSET関数で動的範囲(ダイナミックレンジ)を作成する

会社の月別販売数量をグラフ化したいとします。図4.1は現在のデータと、それをもとに作成したグラフです。

毎月、最新月の販売数量がC列の最後の数値の下に追加されていきます。

グラフを簡単に自動更新する方法はあるのでしょうか?

グラフを自動更新する鍵は、OFFSET関数を使って「販売数量」列の動的な範囲名を作成することです。

この動的範囲により、新しいデータが入力されると、販売数量データ全体が自動的に範囲に含まれるようになります。

図4.1

動的範囲を作成するには、リボンの「数式」タブをクリックし、「名前の管理」または「名前の定義」を選択します。

「名前の定義」をクリックすると、下記のような「新しい名前」ダイアログボックスが表示されます。

「名前の管理」を選んだ場合は、さらに「新規」をクリックして同じダイアログボックスを開きます。

図4.2

「名前」ボックスには動的範囲の名前を入力します。「参照範囲」ボックスには、C列に入力された販売数量をもとに動的な値の範囲を生成するOFFSET数式「=OFFSET(Figure4!$C$2, 1, 0, COUNT(!$C:$C), 1)」を入力します。

デフォルトでは、名前はブック全体に適用され、ブック内で一意である必要があります。

しかし今回は、適用範囲を特定のシートに限定したいので、「スコープ」ボックスで「Figure4」を選択します。「OK」をクリックすると、動的範囲の作成は完了です。

以降、新しいデータが入力されるたびに、すべての売上データが自動的に範囲に含まれます。

次に、グラフ上の任意の場所を右クリックし、「データの選択」を選びます。

図4.3

表示された「データソースの選択」画面で、系列1(Series1)を選択し、「編集」をクリックします。

図4.4

そして、図4.5のように「=Figure4!Units」と入力します。

図4.5

最後に試してみましょう。セルC13に「11」と入力すると、グラフが変化し、値11が反映されているのが分かります。

新しいデータを追加すると、グラフは自動的に更新されます。

図4.6

関連記事

• ExcelのOFFSET関数の使い方と実例集

作業ファイルのダウンロード

この記事で使用した作業ファイルは、下のリンクからダウンロードできます。

Excel-Offset-Function.rar