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

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

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

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

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

概要

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

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

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

*****Sponsered Link***** ************************

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へ


にほんブログ村

 

 

*****Sponsered Link***** ************************

コメントを残す

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