実務で使えるExcel入門セミナー OFFSET関数とその使いどころ

シェアする

  • このエントリーをはてなブックマークに追加

OFFSET関数は便利な関数ですが、「どう使うか」が思いつきにくいところもあります。OFFSET関数の”使いどころ”を知るには、「関数の動き」をつかんでおくことがポイントです。

スポンサーリンク
自動サイズレスボンシブ

OFFSET関数の”動きのイメージ”をつかむ

OFFSET関数に限りませんが、関数を使いこなすには、”動きのイメージ”をとらえて理解しておくことがポイントです。


OFFSET関数は、動きを2つに分けておさえます。


1.基準になるセルからの移動


2.セル範囲の指定


この2つです。


OFFSET関数は複雑な動きをするため、1度に理解しようとすると、 
「何ができる関数なのか」がぼやけてしまい、「どう使うか」にまで発展させることができなくなってしまいます。


そうならないためには、関数の動きをはっきりとした形で理解する必要があります。それには、「動きを分けて捉える」のが有効です。


具体的に見てみましょう。

基準になるセルからの移動

「1.基準になるセルからの移動」は、まず、基準になるセルを決めて(ここでは、A1セル)、

スクリーンショット 2015 03 30 10 05 36


「基準になるセル」を移動させる距離を、「行」、「列」の順に指定します。


「基準になるセル」をA1セルから、3月1日の売上高が入力されているC5セルに移動させるとすると、

スクリーンショット 2015 03 30 10 17 50


A1セルを基準にすると、4行分縦に、2列分横に、移動させれば、C5セルにになります。


これが、「1.基準になるセルからの移動」の動きです。


なぜ、このような動きが必要とされるかというと、条件によって、集計範囲を変る必要がある場合に、

スクリーンショット 2015 03 30 10 30 15


いちいち集計範囲を入力するのではなく、「集計範囲の始点を変更する」ことで、対応できると効率的だからです。


次に、「2.セル範囲の指定」です。

セル範囲の指定

基準になるセルが決まったら、次は、セルの範囲が指定できます。


先ほどの売上高の表で、3月5日までを集計するとします。

スクリーンショット 2015 03 30 10 40 14


基準になるセルは「C4セル」なので、そこから


「縦に何行分」 
「横に何列分」


という形で、セルの範囲が指定できます。


3月1日から3月5日までの売上なら、「C5セル」から「C9セル」までですから、「縦に5行分」

スクリーンショット 2015 03 30 10 44 56


「横に1列分」で、

スクリーンショット 2015 03 30 10 49 15


3月1日から3月5日までのセル範囲を指定する(赤い枠)ことができます。

スクリーンショット 2015 03 30 10 51 36


これが、「2.セル範囲の指定」の動きです。


「2.セル範囲の指定」についても、「1.基準になるセルからの移動」と同様、条件によって、集計範囲を変える必要がある場合に、一から入力する必要がなくなり、効率的な範囲指定が可能になります。


動きをまとめると、

OFFSET関数の動きのイメージ まとめ

「1.基準になるセルからの移動」の動き

スクリーンショット 2015 03 30 10 57 29


「2.セル範囲の指定」の動き

スクリーンショット 2015 03 30 10 59 14


この2つの動きをイメージできるようにしておきます。

OFFSET関数の要素

”関数の動きのイメージ”をおさえたら、それに合わせて、入力内容を確認します。


OFFSET関数の要素は次の通りです。


①基準になるセル

②基準になるセルから縦への移動距離

③基準になるセルから横への移動距離

④セルの範囲指定(縦)

⑤セルの範囲指定(横)


この5つです。


先ほどの売上高の表で、3月1日から3月5日までを集計するとすると、

スクリーンショット 2015 03 30 11 09 09


=OFFSET(①A1, ②4, ③2, ④5, ⑤1)


このようになります。

OFFSET関数の使いどころ

OFFSET関数の動きが分かると、


集計範囲の始点が移動するケース

セル範囲が変動するケース


でOFFSET関数が使えることが分かります。


すぐに思いつくのは、先ほどの売上高の表で、 
日付を指定すると、その日の売上高を表示させる、あるいは、 
その日までの累計額を表示させたりするような場合。

スクリーンショット 2015 03 30 11 20 47
(リストから日付を選びます)


この日付の数値と連動させて、「1.基準になるセルからの移動」で、基準になるセルを移動させます。


上の例なら、1日の売上高が入力されている「C5セル」を基準にして、 
5日の「5」が指定されると、4つ分縦に移動するようにします。


つまり、「B2−1」(「B2」は日付を指定するセル。上の例では「5」が入力されている)と入力することになります。 
(なお、横に移動する必要は無いので、横への移動は”0”になります)


その日だけの売上高を表示するなら、集計範囲は1つのセルなので、「2.セル範囲の指定」では、「縦に1、横に1」になります。


まとめると、


=OFFSET(C5,B2-1,0,1,1)

こうなって、

スクリーンショット 2015 03 30 11 30 35


正しく表示されます。


これは、非常に簡単な例ですが、「視点の移動」「セル範囲が変動」に着目すると、OFFSET関数が使える場面に気づけるはずです。

まとめ

OFFSET関数は複雑な動きを、2つに分けておさえるのがポイントです。 
”関数の動きのイメージ”がはっきりすると、関数の「使いどころ」が分かるようになってきます。

<おまけ>
3月28日に福山雅治さんの「福山雅治のオールナイトニッポンサタデースペシャル“魂のラジオ”」が終了しました。 
放送終了の時間は深夜1時でしたが、3,000人の方がニッポン放送前にかけつけたそうです。 
福山さんのファンはもちろんですが、「福山さんのラジオ」のファンも多くいらっしゃったと推察します。 
一般に浸透している福山さんの人気とはまた違った、ラジオファンにとっての、「パーソナリティ 福山雅治」の存在の大きさが分かるエピソードです。 
残念ながら、私は行けなかったのですが。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする