今回はExcelの「OFFSET関数」の使い方を解説します。少々分かりづらいこの関数ですが、増減など稼働するデータの範囲指定に対応する関数なので、他の関数との組み合わせでその威力を発揮します。ちょっと理解を深めるととっても便利で役立つ、そんな知って得する関数のひとつであります。offset関数の基本はもちろん、よく使う「SUM関数」、「VLOOKUP関数」、「COUNTA関数」との組み合わせを例に、データの入力規則での応用なども、できるだけ分かりやすくご紹介します。少しでもご参考になればうれしく思います。

スポンサードリンク

 

OFFSET関数のキホン

OFFSET関数はとても便利な関数ですが、正直、少々理解しづらいところがあります。それでもその仕組みを少し理解できれば、これほど便利で役立つ関数はないと言えるほど。

まずは、OFFSET関数がどんな関数であるかを解説します。

 

OFFSET関数の構文

OFFSET関数の構文は次の通り。

OFFSET関数は指定した基準から、指定した位置(行数と列数)の大きさ(高さと幅)の範囲を返してくれます

なんのこっちゃ?(笑)

 

下の図で解説します。

左の「男性俳優人気ベスト5」の表の中から、4位となる俳優の「出身地」と「俳優名」をOFFSET関数を使って指定してみます。これで、OFFSET関数の引数の意味するところが分かります。

4位の出身地「大阪府」は基準セルC4から4行目の1列目にある高さ1の幅1のセルと指定になります。➡「=OFFSET(C4,4,1,1,1)」

4位の俳優名「菅田 将睴」は、基準セルC4から4行目の2列目にある高さ1の幅1のセルと指定になります。➡ =OFFSET(C4,4,2,1,1)

タマタマ

OFFSET関数の引数は3種類、基準セルと移動先のセルとその大きさ(範囲)と知るとちょっと分かってきたニャー!


はこ君はこ君

高さと幅が1だから一つのセルというわけじゃな!

タマタマ

でも、これって何に使えるんだろう!??

OFFSET関数をSUM関数と組合わせて理解を深めましょう

下の図の赤い枠の範囲をOFFSET関数の高さと幅で指定し、指定した数値をSUM関数で合計します。今回は基準のセルを”B4″とします。

B4セルを基準セルとし1行目の1列目の移動したセル(C5)から、さらに高さを3行から幅を2列で範囲(C5:D7)が指定されます。OFFSET関数で指定した範囲(C5:D7)を、SUM関数で合計するので結果は「23」となります。

➡=SUM(OFFSET(B4,1,1,3,2))

タマタマ

基準のセルはどのセルでも指定できるんだ、それにしてもまだOFFSET関数を使う理由が分からない??


はこ君はこ君

つまりじゃな、大きさ(範囲)が変化する場合に使えるのじゃ!


タマタマ

大きさが変化するって??

大きさが変わるリストからVLOOKUP関数で表引きする

この“OFFSET関数”はどんな時に使うと便利なのか、簡単な例をご紹介して解説します。ここでは組み合わせ関数として、“VLOOKUP関数”“COUNTA関数”が登場、それらの組み合わせを解説します。可変する範囲指定に対応するためです。

順を立てて関数を組み合わせていけば理解しやすく式ができます。(^^)

 

VLOOKUP関数で生じる問題!

下図にありますようなリストから、VLOOKUP関数で表引きして利用することはよくあります。

G3に入力した関数は=VLOOKUP(F3,B2:D7,3,FALSE)という式が入力されています。構文は=VLOOKUP(検索値,範囲,列番号,検索方法)です。

※検索方法の”FALSE”は省略、もしくは”0”でもOKです。

 

下図F3にリストの順位番号4を入力すると、これがコードとなりB3;D7の範囲の一番左の列から同じ番号の4を見つけます。さらに、その行にあるデータの左端から3列目の「3」を指定しましたので、3列目にある俳優の名前「菅田将暉」を表引きして表示となります。

このようによく利用する”VLOOKUP関数”ですが、厄介な問題を抱えることもあります。

 

上記の左リストが常に変わらず一定であればいいのですが、どんどんデータが追加されて変化する場合、つまりは指定した範囲の行数が増えていきます。この場合いちいちVLOOKUP関数の範囲指定を変更しなければなりません。

=VLOOKUP(f3.B3:D7,3,FALSE)

=VLOOKUP(f3.B3:D8,3,FALSE)

=VLOOKUP(f3.B3:D9,3,FALSE)

といった具合。。

この変化するリストの範囲指定するときこそ、OFFSET関数の出番となるわけです。

はこ君はこ君

増えていく範囲をOFFSET関数で自動化するんじゃな!


タマタマ

難しそう!?


はこ君はこ君

関数の引数を他の関数に順に置換えていけばいいのじゃぞ!!

可変する範囲をOFFSET関数でCOUNTA関数との組合せで解決する

あらためて下図で解説します。

“=VLOOKUP(F3,B3:D7,3,FALSE)”の指定した範囲データが増えて変化するわけですから、範囲指定をいちいち修正しなければならないはずでした。この問題の変化する範囲指定を下図のように”OFFSET関数”に置き換えて利用するというわけです。

この際、”OFFSET関数”の引数にある”高さ”をさらに”COUNTA関数”を利用して組み合わせていきます。

 

OFFSET関数の構文は、=OFFSET(参照、行数、列数、高さ、幅) でした。参照は基準セルになりますからここでは”B3″を指定します。

この”B3セル”は動きませんから行数と列数はどちらも”0″に設定して固定します。

 

COUNTA関数は、=COUNTA(値)という構文で、範囲内の空白でないセルの個数を返してくれます。この場合の値は列範囲になります。=COUNTA(B:B)とするとB列のなかから空白でないセルの個数を返してくれます。ここでは、順位、1、2、3、4、5ですから空白ではないセルの個数は「6」となります。なので「-1」を入れて指定する範囲の行数「5」を導きます。

こうして、VLOOKUP関数の変化する範囲をOFFSET関数でCOUNTA関数を組み合わせて指定しました。

 

タマタマ

VLOOKUP関数の範囲をOFFSET関数、さらにCOUNTA関数が登場するから、正直、難しい・・。


はこ君はこ君

式の構文をしっかり把握してこそ組み合わせができるのじゃな!

 

データの入力規則でも使えるOFFSET関数

EXCELの書式設定には、データの入力規則」という設定があります。入力値の種類を設定したり入力を補助する機能を利用したりよく使います。入力の補助機能のひとつに「リスト」がありますが、
このリストの範囲もデータが追加されて可変する場合があります。

この問題もVLOOKUP関数と同様にOFFSET関数を使って問題を解決することができます。

 

データの入力規則のリスト設定とは

B3セルに「俳優名リスト」を参照して俳優名を入力する際、実際に入力するよりもあらかじめ「リスト」を用意しその中から選べる方がスピーディ。

「データ」タブ>>>「データツール」グループ>>>「データの入力規則」の順に選択。

すると、下の図のように「データの入力規則」のウィザードが立ち上がります。「設定」タブを選択し「入力値の種類」にあるドリルダウンをクリック、メニューから「リスト」を選択します。

チェックボックスにチェックを確認。

「元の値」の空欄をクリックし、リストの範囲をマウスでセルをドラッグし選択します。「OK」をクリックして設定が完了します。

リストを設定したセルB3を選択すると▼のドリルダウンマークが表示されます。▼をクリックするとリストのメンバーがリストとして表示されます。入力したい名前をクリックすれば、セルに選択した名前が入力されます。

文字を間違えることもなく、素早く入力を終えるのでとても便利です。

 

リスト設定の可変するデータの問題もOFFSET関数で解決する方法

VLOOKUP関数のところでご紹介しましたとおりリストが常に固定されていればいいのですが、データが追加されるといちいちリスト設定の指定範囲をし直す必要があり面倒です。

そこでOFFSET関数を使ってこの問題を解決します。

 

データの入力規則のダイアログボックスで、「元の値」の枠にリストとなる元のデータ範囲をしていします。ここにOFFSET関数を入力し、OKをクリックして設定完了です。

=OFFSET(E3,0,0,COUNTA(E:E)-1,1)

 

タマタマ

リストのメンバーが追加されても自動的に追加したデータがリストに表示されるから便利だニャー!!


はこ君はこ君

可変データにはOFFSET関数とCOUNTA関数の組合せじゃ!!

私の感想

冒頭から”OFFSET関数”はとても便利な関数ですが、正直、少々理解しづらいとお伝えしました。それもそのはず、式の構文がちょっとややこしいからです。

ややこしい構文の上に、VLOOKUP関数、OFFSET関数、そしてCOUNTA関数と組み合わせるのですから理解に苦しむのも当然。

 

それでも、構文に含まれるどの引数をどんな関数で置換えるのか、設計図を見ながら考えてみると理解が深まるように思えます。

これらの関数を使ってみると、関数の組合せをより理解できるようになります。(^<^)

 

 

今回のまとめ

今回登場の関数の構文は次の通り。

➡指定した範囲の1列目で特定の値を検索し指定した列と同じ行にある値を返します。
=VLOOKUP関数(検索値、範囲、列番号、検索方法)

➡指定した参照から指定した行数、列数の範囲への参照を返します。
=OFFSET(参照、行数、列数、高さ、幅)

➡範囲内の空白でないセルの個数を返します
=COUNTA(値1、値2、・・・)

OFFSET関数はVLOOKUP関数やリスト設定など、大きさが変わる可変データから表引きする時などに役立つことを解説してみました。

 

理解しづらい点もありますが、各関数の構文の理解と構文のどの引数をどんな関数に置き換えたらいいかと分けて考えることがポイント。ややこしい分フレキシブルに指定ができる点が今回の関数の魅力でもありました。実際に役立つ便利な関数でありましたから、ぜひ、実際に使って活用して頂きたいと思います。

いかがでしたでしょうか?

 

最後まで読んでくださりありがとうございます。人気ブログランキングに参加中。こちらクリックして頂けましたらうれしく思います。
↓↓↓

Microsoft Officeランキング

スポンサードリンク