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

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

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

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

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

関数を理解するための一番の近道は”手を動かす”ことです

ところで、

読者の中には「MATCH関数とINDEX関数の組み合わせはVLOOKUP関数と似ている」と感じた方もいらっしゃると思います

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

では、

 下の画像にある表から、選手の名前に応じて学年から出場回数を抽出する関数を作成していきましょう!!

18

INDEX関数とMATCH関数は下の画像の黄色の印を付けた箇所に入力をします

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

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

24

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

今度は列を追加してみましょう!

25

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

なぜなら、VLOOKUP関数の列参照は整数で設定しているからです

対して、INDEX関数とMATCH関数は、あくまで参照する値に応じて検索が行えます

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

この”違い”を理解した上で、場面に応じてMATCH関数とINDEX関数の組み合わせてとVLOOKUP関数を使い分けていきましょう!

次回OFFSET関数について解説します


にほんブログ村

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

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

OFFSET関数はある指定範囲を起点とし、指定した行数もしくは列数を移動して参照する関数です

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

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

このダッシュボードでは、ラウンドの指定に応じて採点表から指定のラウンドまでの採点・合計を表示しています

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

引数

関数を作成するのに必要な引数は次の3つ/引数1~3です

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

アイコン

OFFSET関数 100.44 KB 16 downloads

...

演習

まずはサンプルファイルの演習シートにて、OFFSET関数で左の表から木村選手の学年を求めてみましょう       

 *但し、参照はA1セルにします

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

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

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

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

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

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

次は仕上げとして、鈴木選手の出場回数もOFFSET関数で求めましょう!

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

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

<まとめ>

今回はOFFSET関数・単体の使い方について解説しました

この関数では、まずは起点を作成するところがポイントです

OFFSET関数では、なかなか使い道が見えてこないと思います

ところが、

 この関数は記事の冒頭で述べたように、他の関数と組み合わせると絶大な効果を発揮します

では次回は他の関数との組み合わせ方法について解説します

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

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村