エクセルのワークシート上で使える関数のひとつに、OFFSET関数があります。
任意のセルから〇行、△列分だけ移動したセルを参照する関数で、複数シートのデータを取りまとめる、なんてケースで使えるととても便利です。
そこで本記事ででは、「OFFSET関数で別シートを参照する方法と、他関数との組合せ」について解説します。
１．OFFSET関数の基本的な使い方
こんにちは。エクセルマン・ブリーダーのしもむぎ(@re_znd13)です。
まずはOFFSET関数の基本的な使い方を見てみましょう。
OFFSET関数の構文は以下の通りです。
OFFSET(参照セル,行方向のオフセット量,列方向のオフセット量)
参照セルを始点として、行方向、列方向に指定したオフセット量だけ移動したセルを参照する関数です。
例えば、参照セルをA1として、行方向に3、列方向に4というオフセット量を指定すると、E4セルの値を返す、というわけです。
下の画像のような九九の表でいえば、A1から3行4列動いた先のE4セルの値「20」が返されているのがわかりますね。
２．別シートのセルを参照する方法
多くの関数と同じように、OFFSET関数でも別シートのセルを参照することができます。
ここで、以下のようなケースを考えてみましょう。
Sheet1には九九の表が入力されています。
Sheet2に、九九の表の任意の答えを返すようなOFFSET関数の式を入力してみます。
別シートを参照する際、「’シート名’!セル名」となりますので、
＝OFFSET('Sheet1'!A1,3,4)と入力すると、Sheet1のA1を始点として、3行4列だけ移動したセルの値「20」が返されます。
３．他関数との組合せ
３．１ MATCH関数との組合せ
前章までは、オフセット量を固定値にしたケースで説明をしました。
実際に使用する際には、オフセット量が不明であったり、バラバラであったりするんですね。
そこでこの章では、よりフレキシブルに活用する方法を紹介します。
OFFSET関数と相性がよくてオススメなのは、MATCH関数です。
前章の例で、Sheet1の九九の表から、かけられる数、かける数を別セルに入力しておき、それらを参照して九九の答えを出力する、というケースを考えてみます。
MATCH関数の役割は、かけられる数やかける数が何番目のセルにあるか探すことです。
かけられる数はA列の1行目～10行目にありますので、A1:A10というセル範囲から、指定した数値が何行目にあるか、というのを探すのです。
このときのMATCH関数の式は、以下のように立てます。
=MATCH(かけられる数, セル範囲, 完全一致)
〇行目にあるとした場合、オフセット量つまり移動する量は、〇－１となるので、注意が必要です。
画像では、かけられる数「7」をL2セルへ、かける数「8」をL3セルへ入力し、MATCH関数で特定しつつ、L1へ以下のような式を入力して求めました。
=OFFSET(A1,MATCH(L2,A$1:A$9,0)-1,MATCH(L3,A$1:I$1,0)-1)
３．２ INDIRECT関数との組合せ
よりフレキシブルさが必要なケースを想定して、INDIRECT関数との組合せを紹介します。
INDIRECT関数との組合せは、複数のシートにデータがあり、ひとつのシートにまとめたいという場合に活躍します。
今回は2018年、2019年、2020年というシートに、お米の取れ高が入力されている事例を作ってみました。
5つの土地（田んぼ）に対して、広さ、取れ高、精米量がまとめられていますが、年ごとにその項目順や土地順が変わってしまっていることがわかるでしょうか。
担当者が違う場合にこういったことが起こりやすいですよね。
こんな場合には、OFFSET関数とINDIRECT関数、MATCH関数を組み合わせると、たくさんのシートがあっても対応が可能です。
項目というシートを作成し、項目ごと（広さ、取れ高、精米量）のデータ収集を行うことを考えてみましょう。
INDIRECT関数を使って、別シートの参照セルを変数化します。
また、MATCH関数とINDIRECT関数の組合せで、オフセット量も変数化します。
=OFFSET(INDIRECT("'"&C$1&"'!A1"),
MATCH($B3,INDIRECT("'"&C$1&"'!B1"):INDIRECT("'"&C$1&"'!B10"),0)-1,
MATCH(C$2,INDIRECT("'"&C$1&"'!A2"):INDIRECT("'"&C$1&"'!G2"),0)-1)
画像のように、取れ高のデータ、精米量のデータ、広さのデータを分けて抽出することができました。
４．まとめ
本記事では、「OFFSET関数で別シートを参照する方法と、他関数との組合せ」について解説しましたが、いかがでしたか？
最後の例は少しゴリゴリ感が否めませんが、マクロ（VBA）にどうしても抵抗がある、という場合には活用してみてください。
