OFFSETの使い方|基準セルから指定した位置のセル範囲を求める

Excelの一覧

Excel(エクセル)のOFFSETの使い方|基準セルから指定した位置のセル範囲を求める

使用関数:OFFSET、MATCH、SUM、COUNTA

OFFSET関数とは?

OFFSETの「SET」には「特定の場所に配置する」という意味があり、「OFF」は「離れた位置」を示します。Excel関数のOFFSET(オフセット)には基準点から指定された距離を移動するという意味合いがあります。OFFSET関数は基準となるセル範囲から指定した列数・行数を移動したセル範囲を返します。

OFFSET関数の引数と書式

「数式」→「関数ライブラリ」→「検索/行列」からOFFSETを選択します。
数式ライブラリの検索/行列からOFFSETを選択
MATCH関数の引数は5つ。「参照」「行数」「列数」「高さ」「幅」です。
「参照」は求める位置の基準となるセル、またはセル範囲です。
「行数」「列数」は「参照」から行方向・列方向にどれだけ移動させるかを指定します。
「高さ」「幅」は第一引数~第三引数によって得られた位置からの範囲のサイズを指定します。

書式の構成はこうなります。
OFFSET関数の書式の構成

OFFSET関数の使い方

簡単な使用例を挙げて、OFFSET関数の設定方法を説明します。
「地球」というセルを起点に、行方向へ「3」列方向へ「2」離れた天体を取得します。
指定距離にあるセルをOFFSET関数で求める使用例

第一引数「参照」

「参照」には移動の基準となるセルを指定します。セル範囲を指定する場合でも移動距離の起点は範囲の左上のセルになります。
「参照」に指定したセルは位置を示し、その中の入力値は意味を持ちません。空白セルを指定することもできます。

この例では地球と入力されたセルを「参照」にクリックで指定しますが、地球という文字列を削除しても、セル自体を移動させてもOFFSETの結果に影響しません。ここで指定したのはあくまで「A1=A列の1行目」という位置情報です。
OFFSET関数で「参照」に基準となるセルを指定

第二引数「行数」と第三引数「列数」

第二引数「行数」には、「参照」に指定したセル(またはセル範囲)を「0」として、行方向(下方向)に「3」移動させる指定をします。この例では「参照」に指定したセルが「A1」なので、「4行」が求める位置になります。
第三引数「列数」には、「参照」に指定したセル(またはセル範囲)を「0」として、列方向(右方向)に「2」移動させる指定をします。この例では「参照」に指定したセルが「A1」なので、「C列」が求める位置になります。
「行数」と「列数」で求められる位置は「C4」セルということですね。
OFFSET関数の「行数」「列数」を指定

「行数」「列数」をマイナス(-)で指定する

OFFSET関数の「行数」と「列数」にマイナス(-)の数値を指定すると、行数では上方向へ、列数では左方向への距離を指定できます。
OFFSET関数の「行数」と「列数」の正数・負数の指定

第四引数「高さ」と第五引数「幅」

第四引数「高さ」には、「参照・行数・列数」で求めたセル(またはセル範囲の左上セル)を「1」として、参照する行方向の範囲を指定します。
第五引数「幅」には、「参照・行数・列数」で求めたセル(またはセル範囲の左上セル)を「1」として、参照する列方向の範囲を指定します。

この2つの引数は省略することができます。高さと幅を省略した場合は「参照」で指定した範囲と縦横同数の範囲を指定したと見なされます。
OFFSET関数で「高さ」「幅」を省略

この使用例では1セルだけを参照したいので、「高さ」と「幅」両方に「1」と指定するか、もしくは省略します。
「地球」から行方向へ「3」列方向へ「2」離れた天体がOFFSET関数で取得できました。
基準から行方向へ「3」列方向へ「2」離れたセルの値をOFFSET関数で取得できた

「高さ」「幅」をセル範囲で取得する

セル一つだけでなく、セル範囲を取得してみましょう。
下図の例では「太陽」セルを基準として「行数=1、列数=0」で得た相対位置のセルから「縦に2、横に2」のセル範囲を参照しています。
OFFSET関数で「行数=1、列数=0」で得た相対位置のセルから「縦に2、横に2」のセル範囲を参照
「縦に2、横に2」のセル範囲が取得できました。
「縦に2、横に2」のセル範囲を取得

「高さ」「幅」をマイナス(-)で指定する(スピル)

Excel2016まではOFFSET関数の「高さ」と「幅」には正数しか指定できませんでした。マイナス(-)を指定するとエラーが返されたはずです。
Excel2019~及び365でスピルが使える環境では、OFFSET関数の「高さ」と「幅」にマイナス(-)の数値を指定できるようになっています。高さでは上方向へ、幅では左方向への範囲を指定できます。
行数・列数の指定とは異なり、「参照・行数・列数」で求めた相対位置を「1」または「-1」として範囲を指定します。
OFFSETの数式を「=OFFSET(A1,3,3,-3,4)」とした場合、相対位置を含めてへ3セル、へ4セルが返されます。数式を「=OFFSET(A1,3,3,4,-2)」とした場合、相対位置を含めてへ4セル、へ2セルが返されます。
OFFSET関数の「高さ」と「幅」を正数・負数で指定

OFFSET関数の便利な応用

OFFSETは他の関数と組み合わせて使われることが多く、参照範囲を可変にさせたい時などにとても重宝する関数です。

OFFSETとCOUNTA関数を組み合わせてプルダウンリストを可変にする

OFFSETとCOUNTA関数を組み合わせて、項目を追加・削除しても自動で修正される可変のプルダウンリストを作成する解説記事です。

https://www.tschoolbank.com/tb-articles/excel-addressbook-data-list/

SUM関数とOFFSETとMATCH関数を組み合わせて可変の参照範囲を自動集計

日々の売上表で特定の期間の施術ごとの売上合計をSUM関数・OFFSET関数・MATCH関数の組み合わせで自動表示させます。
特定期間の項目別の売上合計をSUM関数・OFFSET関数・MATCH関数の組み合わせで自動表示

OFFSET関数の「行数」「列数」に入れるMATCH関数

OFFSET関数の移動距離を指示する「行数」と「列数」にそれぞれMATCH関数を入れます。
OFFSETの「参照」には「A1」を指定します。
OFFSETの「参照」には「A1」を指定

  • 「行数」に入るのは基準セルから開始日までの距離です。
    MATCH関数の「検査値」には「G3(開始日)」、「検査範囲」には表の「日付」のデータ範囲、「照合の種類」には完全一致の「0」を入れます。
    数式は「MATCH(G3,$A$2:$A$22,0)」。表の日付の欄から開始日を探し、一番上から何番目にあるかを求めました。
    OFFSET関数の「行数」に入れるMATCH関数の組み立て
  • 「列数」に入るのは基準セルから項目(G5セルに入れた施術名)までの距離です。
    MATCH関数の「検査値」には「G5(カット)」、「検査範囲」には表の列項目の範囲、「照合の種類」には完全一致の「0」を入れます。
    数式は「MATCH(G5,$B$1:$E$1,0)」。表の列項目からカットを探し、範囲の左から何番目にあるかを求めました。
    OFFSET関数の「列数」に入れるMATCH関数の組み立て
  • OFFSET関数の「行数・列数」にMATCH関数で得た数値が入り、返される範囲の左上の位置(開始日と同じ行で隣の列にある金額のセル)が決まりました。
    OFFSET関数の「行数・列数」に入ったMATCH関数で返される範囲の左上の位置が決まった
  • 上記のセルから終了日と同じ行までが集計範囲です。この値を求めるのはOFFSET関数の「高さ」で、「終了日-開始日+1」で求められます。終了日-開始日では最終日の金額セルが含まれないので「+1」で補正します。「幅」は「1」で「参照」と同じなので省略します。
    OFFSET関数の「高さ」に終了日-開始日+1を指定
  • OFFSETの数式です。OFFSET(A1,MATCH(G3,$A$2:$A$22,0),MATCH(G5,$B$1:$E$1,0),H3-G3+1)。これをSUM関数に入れます。
    OFFSETの数式をSUM関数に入れ子

    最終的な数式は =SUM(OFFSET(A1,MATCH(G3,$A$2:$A$22,0),MATCH(G5,$B$1:$E$1,0),H3-G3+1)) となります。

  • 集計される範囲は可変になっていて、日付・項目名を入れ替えると自動で金額の値に反映されます。
    日付、項目名を入れ替えると、自動で金額の集計値に反映

OFFSET関数とVLOOKUP・SUMIF関数の組み合わせは要注意

OFFSET関数は便利な関数ですが、使い方によっては動作が重くなります。
OFFSETは揮発性関数の一つで、シート内のセルを書き換えたり、ブックを開く度に再計算が行われます。これによってOFFSETが組み込まれたシートの動作は重くなるため、更に重いVLOOKUPやSUMIFと組み合わせるとデータ量次第では作業の遅延につながります。

OFFSET関数の多用でシートの動作が重いと感じたら、OFFSETと似た働きをするINDEX関数に置き換えるなどして対処しましょう。

INDEX関数の詳しい使い方はこちらから。

INDEX関数の使い方|指定した行と列の交差セルを返す
ExcelのINDEX関数の使い方を初心者にも分かりやすく解説。「配列形式」「セル参照形式」の組み立て、引数の省略とエラーの対処法、INDEX関数とMATCH関数を組み合わせて複数条件でデータを抽出する使用例、INDEX関数とROW・COL...

OFFSET関数とINDEX関数の違いについて

OFFSET関数は基準となるセル範囲からシフトした相対位置を求めますが、INDEX関数は配列の中の行番号・列番号が交差した位置を求めます。
文章だとイメージしにくくても、図にすると初心者の方でも分かりやすいと思います。
OFFSET関数とINDEX関数の仕組みの違い
求める値へのアクセス方法は違っても、同じ結果を導きやすいことが分かりますね。

もう一つ、ポイントとなる違いは、行方向・列方向への移動距離の数え方です。
OFFSETは基準となるセルを「0」とし、右方向・下方向へ「1・2・3・・・」、左方向・上方向へ「-1・-2・-3・・・」と数えます。
対するINDEXは配列の左上セルを「1」とし、右方向・下方向へ「2・3・4・・・」と数えます。マイナス(-)は配列の外に出るので#VALUE!エラーになります。
OFFSET関数とINDEX関数の行・列方向への移動距離の数え方の違い

INDEXは書式もシンプルで直感的に使える関数です。OFFSETは便利ですが、頻繁に再計算されるのがネック。多用は避けましょう。

OFFSET関数のまとめ
  • OFFSET関数は基準セルからの距離を行数・列数で指定し、その位置にあるセル範囲を参照します
  • OFFSET関数の行数・列数は基準セルを「0」として数えます。マイナス(-)の指定もできます。
  • OFFSET関数はMATCH関数・SUM・SUMIF関数・COUNTA関数と組み合わせ、参照範囲を可変にしてプルダウンリストを作るなど様々な応用が効きます
  • OFFSETは便利な関数ですが、性質上動作が重い傾向があり、使い方に注意が必要です
  • OFFSETの代替にはINDEX関数が最適です。OFFSETとINDEXの違いをよく把握した上で上手に使い分けましょう
タイトルとURLをコピーしました