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

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

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

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

OFFSET関数

1 ファイル 100.44 KB

ダウンロード

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

概要

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

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

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

２．OFFSET関数で１．で算出した値に応じて合計範囲を算出する

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

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

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

３．２．の関数に名前をつけてSUM関数の中に組み込む

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

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

演習

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

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

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

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

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

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

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

　
　　=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:参照・起点/赤コーナーなのでM４セル

b:行数

c:列数

d:高さ

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

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

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

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

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

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

これで完成です

＜まとめ＞

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

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

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

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

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

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

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

ではまた次回！

にほんブログ村