記事の詳細

こんにちは。火曜日担当の田中です。

急激に寒くなりましたね。

昼間はまだ暑いので半袖でいけますが、さすがに夜は寒いですね(^^

体調不良にはお気をつけて。。。

 

今日はExcelです。

管理業務等していると、やはりExcelが大活躍します。

業務には欠かせませんよね(^^

そして、よく使うVLOOKUP!!

Excelが使えるようになってきたなぁ・・って感じた人が最初に立ち止まる壁がVLOOKUP!!

他のシートにある情報を参照したりと、とても便利な関数なんですけど、

自分この関数嫌いなんです!!

複数の関数を使ったりとかしたいのですが、結構な頻度でエラーになります。

分解して、どこまで合ってるか調べると、VLOOKUPのところでエラーになっている事がよくあります!!

なので…

私はVLOOKUPの代わりにこれらの関数をよく使います。

 

INDEX関数とMATCH関数

 

この2つの関数を使って、参照したいデータを引っ張ってくるようにしています。

「VLOOKUPはわからんがこっちなら」って思ってもらえるのが狙いだったりしますwww

初心者の方にもわかりやすいように説明していますので、是非読んでみてください(^^♪

 

「INDEXとMATCH関数」はどんな時に使う関数なのか?

参照と言われてもどのような場面で利用すればいいのかが思い浮かばないと思います。

なので、

前回参加者かどうかの確認

とか

社員のデータから、特定の情報を引っ張ってきて資料を作成する

とか

複数のシートにまたがっているデータを一つのシートにまとめる

とか

データを1つ1つ手動で入力するのが面倒な時に活躍する関数

だと思っています。

 

今回は

イベント関係をやっているので、統計を出す為にイベントの前回参加者を調べたりします。

それを例に使い方の説明をします。

 

INDEX関数とMATCH関数を使って前回参加者を探し出せ!

下記シートにはとあるイベントに参加した人の情報があります。
2015年の参加者と2016年の参加者です。

※この表にいる登場人物と実在する人物とはなんら関係はございません。

2016-10-25_17h55_082016-10-25_17h55_55

 

ではこの「なにかのらんきんぐ2016」で前回参加者を探したいと思います。

2016-10-25_18h07_18

 

まずは先頭のアリスが2015年の参加者に含まれているのかを調べます。

=INDEX(‘2015年参加者’!B$4:F$13,MATCH(‘2016年参加者’!B4,’2015年参加者’!C$4:C$13,0),2)

という式を入れています。

すると、前回参加者のところに「アリス」が出力されます。

2016-10-25_18h14_50

 

2015年の表にはアリスが2番目におりますので、2016年の表にアリスが表示されています。

2016-10-25_18h15_44

 

あとはいつも通りに数式をアンジェラからアナまでコピーします。

2016-10-25_18h18_51

2016-10-25_18h20_34

これで前回参加者は自分の名前で表示をされています。
そして「#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関数

これを説明するのは中々難しいですね…

指定された行と列が交差する場所にあるセルを参照します。

例えば、

2016-10-25_18h20_34

 

この表に=INDEX(B4:F13,3,4)と入れたとしますと、

バレーボールという値が出力されます。

まずB4:F13のところでデータの抽出するべき範囲を指定しています。

そして、次に「3」で行を指定しています。

最後に列を「4」で指定しています。

図で表すとこんな感じです。

2016-10-25_18h53_42

 

MATCH関数

指定したセルの範囲を検索してその項目の位置を上から数えて返します。

ここでは

INDEX関数で必要な「行」を特定するのに使います。

 

=MATCH(‘2016年参加者’!B4,’2015年参加者’!C4:C13,0)

2016年の参加者であるアリスが2015年でも参加しているのかを調べるので、

まず、2016年のアリスのセルを選択します。

2016-10-25_19h19_00

そして、続いて2015年の名前リストの中にアリスがいるか探します。

2016-10-25_19h15_44

 

3番目に指定している「0」ですが、

「完全一致」するもの。

です。

※完全一致なので、半角、全角のスペースを含む含まないも検知しますので、事前に置換等をして、スペースを調節しておいてください。

2016-10-25_19h26_53

アリスは2番目にありますので、2という数字が表示されます。

という感じで、他のシートから、データを参照して引っ張ってきています(^^♪

 

前回参加者を”〇”等で表示したい!

確かに、INDEXとMATCH関数を使うだけですと、該当する値を出力するだけなので、わかりづらいかもしれません。

しかし、IF関数を使う事で、

「前回参加者は〇」

等で表示するようにすることができます。

2016-10-25_18h24_43

=IF(B4=INDEX(‘2015年参加者’!B$4:F$13,MATCH(‘2016年参加者’!B4,’2015年参加者’!C$4:C$13,0),2),”〇”,””)

これで少しは見やすくなると思います。

まだ「#N/A」のエラーが邪魔ですね。

これは外し方がありますが、これは下記のブログにて記載しています。…(^^)/

Excelで関数を使っていると出会うエラー。 でも、消したい!非表示にしたい!。このエラー!!と思うことがあると思います。今回使う関数IFERRORでそれをやってみたいと...

いかがでしたでしょうか。

INDEX関数やMATCH関数って単体でいつ使うんだ…(;゚Д゚)

と思っていましたが、こういう使い方もあるんですね。

でわでわ。

 

Thank you for reading my blog.

See you next time.

Have a nice time.

【PR】相模原市産業会館パソコン教室

相模原市産業会館パソコン教室では「アットホーム」な雰囲気でパソコンを楽しく分かるまで学ぶことができます。

bana-pasoken-blog

この記事であなたの課題を解決することができましたか?

疑問点があったり、解決できなかったことがありましたら、お気軽にご相談してください。


ほかの投稿も読んでみませんか?

  1. アバター
    • まこと
    • 2018年 5月 31日

    非常に参考になりましたが、コピペするとINDEXとMATCHの参照範囲が変わってしまいます・・・・なので、サンプルのようになりません。

  2. インクループ株式会社
    • インクループ株式会社
    • 2018年 6月 01日

    まこと 様

    はじめまして。
    ブログをお読み頂きありがとうございます。
    記事を書きましたインクループ株式会社の田中と申します。
    返信が遅くなりまして申し訳ございません。

    「コピペをすると参照範囲が変わってしまう」とありますが、数式内のセルに「$」をつけて絶対参照にしていますでしょうか?
    今回の場合、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/

    以上です。

  1. この記事へのトラックバックはありません。

相模原のお店やサービスをどんどん紹介!

行ってみよう相模原 相模原市のお店限定

飲食店

ページ上部へ戻る