タグ別アーカイブ: 名前管理

関数をマスターする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へ


にほんブログ村

関数をマスターする7~MATCH関数&INDEX関数2~

今回は前回に引き続き、MATCH関数とINDEX関数に取り組みます

名前管理の機能を使ってより実践的な方法に取り組んでみましょう!

もし、分かりづらい部分があれば下記のサンプルファイルをダウンロードして自分で触って動かしてみましょう!”手を動かす”のが一番の近道です

ところで

MATCH関数とINDEX関数の組み合わせがどう”VLOOKUP関数”と違うかという点も合わせて解説していきます(VLOOKUP関数は行方向に検索しますが、列方向には検索できませんので、その点は省略させて頂きます)

では

以下、サンプルファイルの表から選手の名前に応じて学年から出場回数を抽出する関数を作成していきましょう!!

18

1.まずは表の見出しに対応した名前をつけよう!

選手~出場回数まで、エクセル画面上の名前ボックスで見出しそのままに名前をつけましょう!

GIF9

ここで名前を付ける理由を1つだけ解説します

もし、名前を付けない場合は、選手の学年を抽出する関数はどんな風になるかというと以下のようになります

=INDEX(C$2:C$11,MATCH(C$13,B$2:B$11,0))

一方、名前をつけると下記のようにセル範囲の部分が置き換わります

=INDEX(学年,MATCH($C$13,選手,0))

名前を付けたほうが一目見ただけで意味が分かりますよね

”Cの列は何だっけ?”などと表を参照する必要はありません

では、以下にそのまま”=INDEX(学年,MATCH($C$13,選手,0))”と記入しましょう

20

2.関数を下まで(出場回数まで)コピーして、関数の名前を修正

関数をコピーした後、関数の名前を抽出対象にあわせて修正しましょう

21

22

もし、1.の段階で名前を付けていなかったら、セルの列や行位置などを修正する必要があった(もしくは1.の段階で絶対参照を設定しておく必要があった)ことを考えると、名前を付けることが省力化につながることが良く分かると思います

3.表に列や行を追加してみましょう!

VLOOKUP関数でも同じように名前をつけて表から抽出することができます

23

ここで、表に行データを追加してみましょう!

24

VLOOKUP関数でも追加に対応できています

今度は取り合えず列だけ追加してみましょう!

25

するとVLOOKUP関数の列参照が狂ってしまいます・・・

この点もMATCH関数とINDEX関数の組み合わせとVLOOKUP関数の大きな違いです

 

”違い”を理解した上で表の性質や抽出データの活用方法など、場面に応じてMATCH関数とINDEX関数の組み合わせてとVLOOKUP関数を使い分けていきましょう!