どうもマサヤです！
「データ追加されたらセル範囲を自動で広げたい」
「プルダウンリストを自動更新したい」
「ピボットのデータソースを自動で広げたい」
その悩み、OFFSET関数を使えば解決できます！
でも、OFFSET関数は解りづらい！といった声を周りから聞きます。
理解できてもどうやって使いこなせばいいかピンと来ない。
この問題を解決できるように、まずはOFFSETの基本を紹介します。
その後、OFFSETの様々な使い方を紹介していきますよ！
使えるようになれば効率が上がることは間違いありません！
OFFSET関数 基本の使い方
まずはOFFSET関数の基本を説明します！
関数の説明
セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。また、返されるセル参照の行数と列数を指定することもできます。
Microsoft OFFSET関数 説明より
指定した行・列だけ移動してセルもしくはセル範囲を結果として返してくれます。
セル範囲についても高さや幅を指定するで、自由に変更することが出来ます。
OFFSET関数がよく使われるのはセル範囲を自由に変更できるからです。
書式
OFFSET(①基準, ②行数, ③列数, ④[高さ], ⑤[幅])
①基準 ： どのセルから始めるかを指定します。
②行数 ： ①基準のセルから移動したい行数を指定します。
③列数 ： ①基準のセルから移動したい列数を指定します。
④高さ ： 省略可能。移動したセルから参照するセルの高（行）さを指定します。
⑤幅 ： 省略可能。移動したセルから参照するセルの幅（列）を指定します。
OFFSET関数の具体的な説明
下記の数式だった場合、OFFSET関数はどのセル範囲を参照するかを見てみましょう。
基準セルがA1となっているので黄色のセルが開始地点となります。
そこから3行移動してA3（緑色セル）へ、3列移動してD4（赤色セル）へと参照セルが移動します。
ここからはセル範囲の拡張です。
高さが4なのでセル範囲はD4:D7となり、幅が3なのでD4:F7となります。
よって、返されるセル範囲はD4:D7（水色セル）となります。
高さと幅は参照セルを含めます。
↑の数式を例にあげるとD4から高さが4指定された場合、D8ではなくD7となります。
要するにカウント方法として、４（1）・5（2）・6（3）・7（4）と数えます。
動画でOFFSET関数の基本を説明【動画】
よりイメージできるように動画で動きを確認してみましょう。
動画の最後にOFFSET関数にて返されるセル範囲を、B2（ピンク色セル）に表示させてます。
※実際は高さと幅を指定したOFFSET関数を実行すると #VALUE! エラーとなります。
動画では解りやすくするためセル範囲を表示させているだけなので注意しましょう。
では、次からは実際の使い方を紹介していきます！
OFFSET関数の使いこなす！ 応用や便利な使い方
いよいよOFFSET関数の応用、便利な使い方を紹介します！
データ追加に合わせて、セル範囲を自動で広げる
まずは動画をどうぞ！
この動画では、売上の合計金額を計算しています。
最初は数式をSUM（B3:B7）としていましたが、B8にデータ追加されると正しく計算できなくなっています。
もちろんSUM（B3:B8）と変更すれば正しく計算できますが、データが追加される度に手動で変更するとなると手間ですよね。
そこで、OFFSET関数が出てきます！
SUM関数内のセル範囲を、OFFSET関数に変えることで計算できるようになります。
高さをCOUNTA関数でカウントすることで、データ追加されても自動でセル範囲が変わってくれます。
プルダウンリストを自動で更新させる
プルダウンリストに追加したデータを自動で反映させる方法を紹介します！
とは言っても、先ほどの応用なのでちょっとした違いがあるぐらいです。
COUNTA関数を組み合わせたOFFSET関数をデータの入力規則に入れるだけです。
詳しくは↓をご覧ください。
プルダウンの基本の作り方から自動反映方法の詳細が載ってます！
ピボットのデータソースを自動で広げる
ピボットテーブルで集計している場合、データが追加されるとデータソースを手動で広げる必要があります。
これも面倒なのでOFFSET関数を使って解決してしまいましょう！
名前の定義を使うことで自動反映を実現しています。
◆ 手順 ◆
名前の定義
① 数式 ⇒ 名前の定義をクリック。
② 名前は適当に入力。※動画ではセル範囲としています。
③ 参照範囲に =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) と入力。
※セル指定は絶対参照（$マーク）を使いましょう。
データソースの変更
④ ピボットテーブルツール ⇒ オプション ⇒ データソースの変更をクリック。
⑤ テーブル/範囲に セル範囲 と入力。
⑥ 最後に更新を押せば反映します。
データ追加後に更新だけは必要です。
ブックを開いた時に更新で問題ないのであれば以下方法で可能です。
ピボットテーブル オプション] ⇒ データタブ ⇒ ファイルを開くときにデータを更新するにチェック。
まとめ
OFFSET関数とは参照セルを返してくれる関数です。
セル範囲を可変的に扱うことができます。
COUNTAと組み合わせるとデータが追加される度にセル範囲を伸縮します。
他にもMATCH関数と組み合わせることで、VLOOKUP関数ではエラーになる探査もできます。
汎用性の高い関数です。
ぜひマスターして使いこなしてくださいね。
間違いなくミスが少なり効率もあがりますよ！
コメント