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

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

赤コーナーと青コーナーの採点合計をラウンド毎に可変で算出するのですが、OFFSET関数をうまく活かしてSUM関数の合計範囲を可変にします。サンプルファイルでは”OFFSET関数サンプル”のシート名になります

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

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

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

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

 実は前回はOFFSET関数の引数を3つまでしか解説しませんでした

 が、実はもう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:幅)

高さを1に固定した上で、幅を➀で算出したMATCH関数の値により可変にします(参照は赤コーナーであれば、M3セル。青コーナーであればM4セルになります)

出来上がった数式は以下になります(赤コーナー分)

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

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

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

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

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

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

  

これで完成です

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

ではまた次回

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


にほんブログ村

 

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です