記事の詳細
初心者でも簡単!!エクセルでVLOOKUPの代わりにINDEXとMATCH関数を使い別シートのデータ参照をする方法
こんにちは。火曜日担当の田中です。
急激に寒くなりましたね。
昼間はまだ暑いので半袖でいけますが、さすがに夜は寒いですね(^^
体調不良にはお気をつけて。。。
今日はExcelです。
管理業務等していると、やはりExcelが大活躍します。
業務には欠かせませんよね(^^
そして、よく使うVLOOKUP!!
Excelが使えるようになってきたなぁ・・って感じた人が最初に立ち止まる壁がVLOOKUP!!
他のシートにある情報を参照したりと、とても便利な関数なんですけど、
自分この関数嫌いなんです!!
複数の関数を使ったりとかしたいのですが、結構な頻度でエラーになります。
分解して、どこまで合ってるか調べると、VLOOKUPのところでエラーになっている事がよくあります!!
なので…
私はVLOOKUPの代わりにこれらの関数をよく使います。
INDEX関数とMATCH関数
この2つの関数を使って、参照したいデータを引っ張ってくるようにしています。
「VLOOKUPはわからんがこっちなら」って思ってもらえるのが狙いだったりしますwww
初心者の方にもわかりやすいように説明していますので、是非読んでみてください(^^♪
今回のブログの見出しはコチラです!
「INDEXとMATCH関数」はどんな時に使う関数なのか?
参照と言われてもどのような場面で利用すればいいのかが思い浮かばないと思います。
なので、
前回参加者かどうかの確認
とか
社員のデータから、特定の情報を引っ張ってきて資料を作成する
とか
複数のシートにまたがっているデータを一つのシートにまとめる
とか
データを1つ1つ手動で入力するのが面倒な時に活躍する関数
だと思っています。
今回は
イベント関係をやっているので、統計を出す為にイベントの前回参加者を調べたりします。
それを例に使い方の説明をします。
INDEX関数とMATCH関数を使って前回参加者を探し出せ!
下記シートにはとあるイベントに参加した人の情報があります。
2015年の参加者と2016年の参加者です。
※この表にいる登場人物と実在する人物とはなんら関係はございません。
ではこの「なにかのらんきんぐ2016」で前回参加者を探したいと思います。
まずは先頭のアリスが2015年の参加者に含まれているのかを調べます。
=INDEX(‘2015年参加者’!B$4:F$13,MATCH(‘2016年参加者’!B4,’2015年参加者’!C$4:C$13,0),2)
という式を入れています。
すると、前回参加者のところに「アリス」が出力されます。
2015年の表にはアリスが2番目におりますので、2016年の表にアリスが表示されています。
あとはいつも通りに数式をアンジェラからアナまでコピーします。
これで前回参加者は自分の名前で表示をされています。
そして「#N/A」のとなっている方は今回初めての参加者ということになりますね。
=INDEX(‘2015年参加者’!B$4:F$13,MATCH(‘2016年参加者’!B4,’2015年参加者’!C$4:C$13,0),2)でやっている事。
ではここから、やっている事の解説をしていきたいと思います。
使用している関数は下の2つ。
- INDEX関数
- MATCH関数
難しく見えますが、簡単にいうと
かくれんぼ
です。
INDEXで場所を指定します。
ここからここまでね。
この公園から外出ちゃだめだからね。
そして、Matchで、隅々まで探していきます。
どこかな~どこかな~?
いたら
〇〇ちゃんみ~っけ!!
って感じです。
ご丁寧にいる場所を細かく数値で
ここから南に2メートル!!
(上から下に向かって数えるので…)
と教えてくれます。
いない人(該当しない人)はエラーで返ってきます。
ねぇねぇ、どこ~?
見つからないものを永遠に続けることでしょう…
INDEX関数
これを説明するのは中々難しいですね…
指定された行と列が交差する場所にあるセルを参照します。
例えば、
この表に=INDEX(B4:F13,3,4)と入れたとしますと、
バレーボールという値が出力されます。
まずB4:F13のところでデータの抽出するべき範囲を指定しています。
そして、次に「3」で行を指定しています。
最後に列を「4」で指定しています。
図で表すとこんな感じです。
MATCH関数
指定したセルの範囲を検索してその項目の位置を上から数えて返します。
ここでは
INDEX関数で必要な「行」を特定するのに使います。
=MATCH(‘2016年参加者’!B4,’2015年参加者’!C4:C13,0)
2016年の参加者であるアリスが2015年でも参加しているのかを調べるので、
まず、2016年のアリスのセルを選択します。
そして、続いて2015年の名前リストの中にアリスがいるか探します。
3番目に指定している「0」ですが、
「完全一致」するもの。
です。
※完全一致なので、半角、全角のスペースを含む含まないも検知しますので、事前に置換等をして、スペースを調節しておいてください。
アリスは2番目にありますので、2という数字が表示されます。
という感じで、他のシートから、データを参照して引っ張ってきています(^^♪
前回参加者を”〇”等で表示したい!
確かに、INDEXとMATCH関数を使うだけですと、該当する値を出力するだけなので、わかりづらいかもしれません。
しかし、IF関数を使う事で、
「前回参加者は〇」
等で表示するようにすることができます。
=IF(B4=INDEX(‘2015年参加者’!B$4:F$13,MATCH(‘2016年参加者’!B4,’2015年参加者’!C$4:C$13,0),2),”〇”,””)
これで少しは見やすくなると思います。
まだ「#N/A」のエラーが邪魔ですね。
これは外し方がありますが、これは下記のブログにて記載しています。…(^^)/
いかがでしたでしょうか。
INDEX関数やMATCH関数って単体でいつ使うんだ…(;゚Д゚)
と思っていましたが、こういう使い方もあるんですね。
でわでわ。
Thank you for reading my blog.
See you next time.
Have a nice time.
【PR】相模原市産業会館パソコン教室
相模原市産業会館パソコン教室では「アットホーム」な雰囲気でパソコンを楽しく分かるまで学ぶことができます。
コメント
この記事へのトラックバックはありません。
非常に参考になりましたが、コピペするとINDEXとMATCHの参照範囲が変わってしまいます・・・・なので、サンプルのようになりません。
まこと 様
はじめまして。
ブログをお読み頂きありがとうございます。
記事を書きましたインクループ株式会社の田中と申します。
返信が遅くなりまして申し訳ございません。
「コピペをすると参照範囲が変わってしまう」とありますが、数式内のセルに「$」をつけて絶対参照にしていますでしょうか?
今回の場合、INDEX関数のところで’2015年参加者’!B$4:F$13としており、B4のセルとF13のセルを計算する場所がずれないように絶対参照をしています。
他にもMatch関数のところでも計算する場所がずれないようにしているので、恐らくセルを絶対参照にする事で解決できるかと思います。
絶対参照のことはこちらの記事に記載していますので合わせて参照下さいませ。
https://incloop.com/1%E5%88%86%E7%90%86%E8%A7%A3%EF%BC%81%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB%E6%95%B0%E5%BC%8F%E3%81%AE%E4%B8%AD%E3%81%AB%E3%81%82%E3%82%8B%E3%83%9E%E3%83%BC%E3%82%AF%E3%81%AE%E6%84%8F%E5%91%B3%E3%81%A8/
以上です。