photo:ぱくたそ

こんにちは、釈和(シャカズ)です。

今回はExcel(エクセル)の基本、
OFFSET関数のご紹介です。

少々分かりづらいこの関数。

でも、ちょっと理解を深めるととっても便利で役立つ、
そんな知って得する関数のひとつであります。

今回はOFSET関数を解説してご紹介します。

スポンサードリンク

 

OFFSET関数のキホン

OFFSET関数はとても便利な関数ですが、
正直、少々理解しづらいところがあります。

それでもその仕組みを少し理解できれば、
これほど便利で役立つ関数はないと言えるほど。

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

 

OFFSET関数の構文

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

=OFFSET(参照、行数、列数、高さ、幅)

OFFSETの引数(参照、行数、列数、高さ、幅)は3種類になります。

 

(OFFSET関数3つの引数)

1.基準となるセルを指定する引数➡「参照」

2.基準セルを動かす位置を指定する引数➡「行数」,「列数」

3.基準セルの大きさを指定する引数➡「高さ」,「幅」

 

下の図で解説します。

左の「男性俳優人気ベスト5」の表の中から、
4位となる俳優の「出身地」と「俳優名」をOFFSET関数を使って指定してみました。

 

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

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

 

基準と基準から動かす位置(行数と列数)と大きさ(高さと幅)の、
OFFSET関数の引数の意味するところを知ると理解しやすくなります。

 

タマタマ

OFFSET関数の引数は3種類と知るとちょっと分かってきたニャー!


はこ君はこ君

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

OFFSET関数の高さと幅をもう一歩踏み込んで理解しよう

OFFSET関数の「高さ」と「幅」については、もう少し解説してみます。

そこで、OFFSET関数とSUM関数を組み合わせでその特徴を説明します。

OFFSET関数の「高さ」と「幅」は”大きさ”でした。

 

下の図の赤い枠の範囲をOFFSET関数の高さと幅で指定しています。

指定した範囲の数値をSUM関数で合計するというわけです。

 

OFFSET関数ではB4セルを参照(基準値)とし1行目の1列目の移動したセル(C5)から、
さらに高さを3行から幅を2列で範囲(C5:D7)を指定します。

OFFSET関数で指定した範囲(C5:D7)を、
SUM関数で合計するので結果は「23」となります。

はこ君はこ君

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


タマタマ

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

 

大きさが変わるるリストから表引きする

このOFFSET関数はどんな時に使うと便利なのか、
簡単な例をご紹介して解説します。

ここでは、VLOOKUP関数OFFSET関数、そしてCOUNTA関数が登場、
その組み合わせを利用します。

はじめは複雑に感じるかもしれませんが、
順を立てて関数を組み合わせていけばスムーズに使うことができます。

 

VLOOKUP関数で生じる問題をOFFSET関数で解決!

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

 

F3に入力した関数は=VLOOKUP(F3,B2:d7,3,FALSELSE)という式が入力されています。

構文は=VLOOKUP(検索値,範囲,列番号,検索方法)です。

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

 

F3にリストの順位番号4を入力すると、
これがコードとなりB3;D7の範囲一番左の列から同じ番号のを見つけます。

さらに、行にあるデータの左端から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関数で解決する方法

元のVLOOKUP関数の式にある範囲が変化する点が問題でした。

つまり、

=VLOOKUP(F3,B3:D7,3,FALSE)

=VLOOKUP(F3,B3:D8,3,FALSE)

=VLOOKUP(F3,B3:D9,3,FALSE)

とリストが追加されると行数が変化することになります。

この可変するデータをいちいち修正しなければならず問題です、
この問題の範囲指定を下の図のようにOFFSET関数置き換えて利用するというわけです。

その際、COUNTA関数も組み合わせます。

 

OFFSET関数の構文は、=OFFSET(参照行数列数高さ) でした。

参照は基準セルになりますからB3。

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

 

さて、次に必要な引数の“高さ”が可変するので、
この可変するデータの数を数える=COUNTA関数を使います。

=COUNTA関数は、「指定した範囲内の、空白でないセルの個数を返します」。

 

=COUNTA(B:B)とすると空白でないセルは「6」になります。

項目の「順位」は必要なデータ数ではないので「-1」とします。

つまり、=COUNTA(B:B)-1とし、「5」が行数となります。

 

列数は変わりませんので「3」のままです。

 

(まとめ)

=VLOOKUP(F3,B3:D7,3,FALSE)

↓ 可変する範囲をOFSET関数とCOUNTA関数の組み合わせで用意します。

 

=OFFSET(B3,0,0,COUNTA(B:B)-1,3)

➡B3セルを基準に行数と列数を”0”で固定し、
5行3列の範囲を指定するという意味になります。

 

↓ VLOOKUP関数の可変範囲をOFFSET関数で置換えます。

=VLOOKUP(F3,OFFSET(B3,0,0,COUNTA(B+B)-1,3),3,FALSE)

 

こうして、OFFSET関数はこうした可変するデータの表引きに対応でき、
利用する場面も多く便利で役立つ関数となるわけであります。

タマタマ

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


はこ君はこ君

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

 

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

EXCELでよく使う書式設定には、
データの入力規則」という設定があります。

入力値の種類を設定したり、
入力を補助する機能を利用したりよく使います。

入力の補助機能のひとつに「リスト」がありますが、
このリストの範囲もデータが追加されて可変する場合があります。

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

 

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

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

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

すると、下の図のように「データの入力規則」のウィザードが立ち上がります。

「設定」タブを選択し「入力値の種類」にあるドリルダウンをクリック、
メニューから「リスト」を選択します。

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

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

「OK」をクリックして設定が完了します。

リストを設定したセルB3を選択すると▼のドリルダウンマークが表示されます。

▼をクリックするとリストのメンバーがリストとして表示されます。

入力したい名前をクリックすれば、
セルに選択した名前が入力されます。

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

 

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

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

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

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

ここにOFFSET関数を入力します。

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

OKをクリックして設定完了です。

リストのメンバーが追加されても追加したデータがリストに表示されていきます。

いちいち範囲設定をし直すことが必要ないので、
とても便利で役立ちます。

 

私の感想

可変するデータには”OFFSET関数”と覚えたものです。

冒頭からこの関数はとても便利な関数ですが、
正直、少々理解しづらいとお伝えしました。

それもそのはず、式の構文がちょっとややこしいからです。

 

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

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

理解しづらい部分が逆にフレキシブルに指定ができると考えると、
なるほどいろいろなケースで使えると関数の魅力を実感するわけであります。(^<^)

 

(管理人からのおススメ広告)

水素は壊れやすいので体の健康にいいと分かっても取り入れるのが難しい。
リタライフはお風呂でタップリ水素を体に取り入れることができる点がおススメですよ。

 

今回のまとめ

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

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

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

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

 

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

最終的には、

=VLOOKUP(検索値、範囲、列番号、検索方法)範囲

=OFFSET(参照、行数、列数、高さ、幅)に

OFFSET関数の高さ

=COUNTA(値-1) ・・・個々の値は範囲になります

で置換ました。

 

つまり、

=VLOOKUP(検索値、OFFSET(参照、行数、列数、COUNTA(値-1)、幅)、列番号、検索方法)

と、組み合わせの構文となり、

 

=VLOOKUP(F3,OFFSET(B3,0,0,COUNTA(B+B)-1,3),3,FALSE)

という式になりました。

 

 

また、入力規則リスト設定では

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

とOFFSET関数とCOUNTA関数の組み合わせで、
可変するリストに対応すべくOFFSET関数もご紹介しました。

 

理解しづらい点もありますが、
構文のどの引数をどんな関数に置き換えたらいいかと分けて考えること。

ややこしい分フレキシブルに指定ができる点が今回の関数の魅力でもありました。

実際に役立つ便利な関数でありましたから、
ぜひ、実際に使って活用して頂きたいと思います。

 

今回はOFFSET関数の解説をご紹介させて頂きました。

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

 

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

Microsoft Officeランキング

スポンサードリンク