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

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

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

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

アイコン

OFFSET関数 100.44 KB 15 downloads

...

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

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へ


にほんブログ村

 

 

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

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

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

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

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

関数を作成するのに必要な引数は取り急ぎ以下の3つで説明していきます

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

アイコン

OFFSET関数 100.44 KB 15 downloads

...

まずはサンプルファイルの演習シートでまずはOFFSET関数で木村選手の学年を求めてみましょう(但し、参照はA1セルにします)

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

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

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

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

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

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

次は仕上げとして鈴木選手の出場回数を求めましょう!

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

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

今回はここまでです、では次回お会いしましょう!

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


にほんブログ村