タグ別アーカイブ: OFFSET関数

関数をマスターする7~OFFSET関数~

今回、次回OFFSET関数に取り組みます

OFFSET関数はある指定範囲を起点とし、指定した行数もしくは列数を移動して参照する関数です

この関数は単体だと地味な機能しかありませんが、他の関数と組み合わせると高度な事ができるようになります

次回ではOFFSET関数とSUM関数、そしてMATCH関数と組み合わせて以下のようなダッシュボードを作るところまで解説します

このダッシュボードでは、ラウンドの指定に応じて採点表から指定のラウンドまでの採点・合計を表示しています

今回はOFFSET関数の単体での機能を確認しておきましょう!

引数

関数を作成するのに必要な引数は次の3つ/引数1~3です

=OFFSET(引数1:参照,引数2:行数,引数3:列数)

アイコン

OFFSET関数 100.44 KB 16 downloads

...

演習

まずはサンプルファイルの演習シートにて、OFFSET関数で左の表から木村選手の学年を求めてみましょう       

 *但し、参照はA1セルにします

まず引数1:参照を設定しましょう

上の画像ではみずらいですが、絶対参照でA1セルを参照するようにしました

次に引数2の行数を設定しましょう

木村選手の行はA1セルの1つ下なので1を入力します

最後は引数3を設定して完成です

学年の列はA1セルから2列目ですので、2列目を設定します

次は仕上げとして、鈴木選手の出場回数もOFFSET関数で求めましょう!

参照値は上と同様にA1セルです

行数は3、列数は4にします!

<まとめ>

今回はOFFSET関数・単体の使い方について解説しました

この関数では、まずは起点を作成するところがポイントです

OFFSET関数では、なかなか使い道が見えてこないと思います

ところが、

 この関数は記事の冒頭で述べたように、他の関数と組み合わせると絶大な効果を発揮します

では次回は他の関数との組み合わせ方法について解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

関数をマスターする8~OFFSET関数~

今回はOFFSET関数を他の関数や機能と組み合わせ、ダッシュボードの中で実践的に使ってみましょう

 赤コーナーと青コーナーの採点合計をラウンド毎に可変で算出するのですが、OFFSET関数をうまく活かしてSUM関数の合計範囲を可変にします

サンプルファイルでは”OFFSET関数サンプル”のシート名になります

アイコン

OFFSET関数 100.44 KB 16 downloads

...

まずは何故、可変で算出できるのか、仕組みの概要を解説します

概要

1.MATCH関数を使用して表から該当ラウンドの位置を算出する

 例えばボードに示されたラウンドが4Rならば4を算出するようにします

MATCH関数を使用して、上の画像の画像の「1R~10R」が入力された範囲を参照して4Rの位置を抽出します

2.OFFSET関数で1.で算出した値に応じて合計範囲を算出する

 実は前回はOFFSET関数の引数を3つまでしか解説しませんでしたが、実はもう2つあります

4つ目の引数は「範囲の高さ」、5つ目の引数は「範囲の幅」になります

この2つの引数を使って可変で範囲を設定します

3.2.の関数に名前をつけてSUM関数の中に組み込む

名前機能を利用することで複雑な関数の組み合わせ式をシンプルに示すことができます

以上の3つを踏まえて実際に作業をしていきましょう!

演習

➀MATCH関数で位置を算出する

 検索値はボードがあるF2セル、検索範囲は採点表でラウンドが記載されているM2からV2までの範囲になります(採点は前ラウンドまでなので-1で調整します)

 =MATCH($F$2,$M$2:$V$2,0)

②OFFSET関数で合計範囲を算出す

前回、解説したOFFSET関数の引数は3つでした  

=OFFSET(引数1:参照,引数2:行数,引数3:列数)

実は前述のようにもう2つ追加できます

 
  =OFFSET(引数1:参照,引数2:行数,引数3:列数, _

    引数4:高さ、引数5:幅)

今回の場合は高さをに固定した上で、幅をで算出したMATCH関数の値により可変にします

これまでの内容を組み込んだ数式は以下になります(赤コーナー分)

=OFFSET(a:OFFSET関数サンプル!$M$4,b:0,c:0,d:1, e:MATCH(OFFSET関数サンプル!$F$2,OFFSET関数サンプル!$M$2:$V$2,0)-1)

a:参照・起点/赤コーナーなのでM4セル

b:行数

c:列数

d:高さ

e:合計範囲の幅⇒MATCH関数でボードのラウンドより算出(4Rがボードに示されていれば3になります)

③②の関数に名前をつけた上でSUM関数に組み込む

では数式タブから名前管理をクリックして②の数式に名前をつけましょう!

数式は下の画像の参照範囲のところにコピーしてしまいましょう!

(下の例では赤コーナー、青コーナーと名前をつけています)

名前をつけたら下の画像のようにSUM関数に組み込みましょう!

  

これで完成です

<まとめ>

今回は、OFFSET関数と他の関数を組み合わせて合計範囲を可変にして計算する方法を解説しました

 ポイントは2つあり、まず1つ目はOFFSET関数の4つ目と5つ目の引数をうまくMATCH関数と組み合わせることです

OFFSET関数の4つ目の引数は高さですので、今回のように合計範囲が行方向の場合には1で固定して考えて構いません

5つ目の引数は幅ですので、こちらはMATCH関数と組み合わせて可変にします

2つめはOFFSET関数を名前で設定してSUM関数に組み込む点です

このように複雑な関数を名前で組み込むことで、数式の可読性を向上させることができます

 範囲を可変にする技術はダッシュボードではよく使うので、実際に手を動かしてみてしっかり身につけておきましょう!

ではまた次回

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村