【Excel : match関数】リストにあるデータのセル位置を検索

Excel関数・VBA
match関数
スポンサーリンク
スポンサーリンク

はじめに

どうも、nekoshan気ままブログです。

ブログを始めて日が浅いのでとにかく書きやすい記事から書いてきました。

今まではゲームの記事や観光の記事を書いていましたが、

10記事を超えたので、ExcelやVBAの記事を書いていくことができそうです。

 

ExcelやVBAは自宅での家計簿や一般企業、はたまた病院でも業務のツールとして

幅広く用いられています。

そのため、例として「あああ」とか「みかん」とかテキトーな文字をセルに入れて記事を書く

のはやめて、すぐにでも使い方が分かるように書きたいと思います。

 

私は独学でまったくの初心者からExcel関数とVBAをある程度まで習得することができました。

セミナーやお金のかかることは一つもしていません。全てネットからの情報で賄いました。

それで一つ分かったとこがあります。

初心者でもExcelでこういうことがしたいという明確な目的がある人には

すぐに回答は見つかるもんだということです。

初心者だった私が独学でネットを見ながら学んだので、

初心者の目線で他の初心者さんへ関数を紹介することができるかなと思っています。

 

Excelなどの関数を調べる人は、本当に困っていたり

こういうことができたらもっと仕事が効率良くいくのになと思って

ブログを訪れると思いますので(私もそうでした)、

ですので、こちらも少しでも役に立つ情報を差し上げたいと思っています。

 

Excelの環境について

今回使用した Office のバージョンは

・Microsoft Office Home and Business Premium で

・Microsoft® Excel® for Office 365 MSO

です

Excel関数に関してはあまりバージョンの差はないかと思います。

match関数とは

関数が初めての人はよくわからないと思いますが、

読んで字の如く match ( マッチ ) です。一致という意味です(笑)

よくそのままマッチするとか使いますよね。

Excelでも同様に同じ意味で用いられます。

目的の文字があるセルを探した時に、一定の範囲に探したい文字があった場合、

一致(マッチ)したとなり、答えを返します。

この答えというのは後述しますので、今は気にしなくて大丈夫です。

つまり、

任意の範囲内で、探したい文字と同じ文字を見つけてくれる関数です。

また、この関数は基本的に単独で使うことはあまりなく、他の関数と組み合わせて使う

ことが多いと思われます。

実際に私も単独で使ったことはなく組み合わせて使っています。

よく一緒に組み合わせるのは、index関数や、sumproduct関数です。

関数を試し打ちしてみる

関数を打つ時はまず 半角で =(イコール) と入力します。

そしてその後に 半角で match と打ちます。

そうすると下の画像のようになります。

次に  ( 「半角の左片カッコ」 を入力すると下の画像のようになります。

さて、表示が変わりました。

表示された MATCH(検査値, 検査範囲, [照合の種類]) の意味については次の項目で説明します。

書いたものは「Delete」キーか「Back Space」キーで一旦全て消してください。

match関数の中身について

検索値、検査範囲、照合の種類 とは何なのかを簡潔に説明します。

  検査値: 検索したい値 のこと

 検査範囲: 検査をする範囲 のこと

照合の種類: 検索したい文字と完全に一致するの文字を検索するかそうでないかを決める

これについてはピンとこないと思いますので、実際の例を見ながら説明していきます。

準備

私自身臨床検査技師の資格を取りたいため、その関連の教科書や医学の本がたくさんあります。

それらの本と値段、が一覧にされたリストがあるとします。
 ※だいたい、本の名前が長いので省略して書きます

企業でもなんでも住所や取引先、商品などをリスト化したExcelがあると思いますので、

それに適応できます。

 

実際に使うことを想定して作っていこうと思います。

下の画像の表を使っていきます。

match関数でしたいこと

  • 書物のリストから「ゲノム工学の基礎」が書いてあるセルの位置を探す

です。

リストの行が少ないので、3行目にあるじゃねーかというのはわかっていますが、

何千行の中から探すのはめんどくさいですよね?

では、はじめます

どこかにこれらの書物を入力するセルを決めます。

今回は近くのセルにしますが、別のシートに決めても大丈夫です。

検索する書物の欄には探したい書物を入力するようになります。

プルダウンという機能を使うことで入力を省くこともできます。

次に探した結果を表示するセルを決めます。そしてここに match関数を入れることになります。

結果を表示するセルも任意で好きなセルの場所で構いません。

今回は検索する書物の下に置きます。
(見やすくするためD列のセル幅を狭くし、E列のセル幅を広くしました)

まだ、関数は何も入れておりません。

が、上の画像の赤枠(関数挿入欄)に関数を入れていきます。

始めのうちはこの欄に入れたほうが良いと思います。

セルに直接書くこともできますが慣れていないと書いてる途中で選んだセル番地がズレたり、

エラーが出てびっくりしてしまうと思いますので。

さて関数を入れていきます。

試し打ちした要領で入れていきますと、それと同じ画面になると思います。

そして、検査値を入れます

ここには検索したい値を入力するのですが、先ほど入力欄は作りましたので

そのセル ( 隠れて見えませんがE3セル ) をマウスを使って選択します。

または、「 E3 」と入力しても大丈夫です。

検索する書物を入力するセルが破線で囲ってあればOKです。

セルを選択したら , 「半角のカンマ」 を入力します。

半角のカンマを入力すると、先程の破線が実線に代わり、

match関数の項目が検査値から次の、検査範囲 に移ったのが分かると思います(赤枠)。

カンマを入力したその流れでそのまま次に検査範囲を入れていきます。

検査範囲は、書物の中から当然探しますので、書物の書かれているセルの範囲を

マウスを使ってクリックしてドラッグしながら選択をします。

B1セルの「書物」と書かれたセルは範囲に入れなくて大丈夫です。

「書物」ではありません、それは見出しですから。

つまり、

「細胞診」から「基礎有機化学」までをドラッグすればいいわけです。

セルB2 から セルB11 までです。

ここでも「 B2:B11 」と入力しても大丈夫です。


「:」コロン

連続した複数範囲を指定したい時はこのマークを使います。

選択する位置までドラッグしたらドラッグするのをやめて、半角のカンマを入力します。

そうすると下の画像のようになると思います(画面を切り替えたら表示は消えるかもしれません)。

次は 照合の種類ですが、

今回は 
書物のリストから「ゲノム工学の基礎」が書いてあるセルの位置を探す

であり、検索する書物に入力する文字はぴったり一致する文字を入力しますので

「完全一致」を表す 0 を入力します(上の画像の赤枠)。

0 を入力したら ) 「半角の右片カッコ」を入力して関数自体は出来上がりです。

このような数式が完成すると思います。

=MATCH(E3,B2:B11,0)

そして「Enter」キーを押すと、下の画像になると思います。

関数の結果が、「 #N/A 」と出ています(上の画像の赤枠)。

これは、数式の中で参照するが対象が見つからないというエラーです。

参照する対象というのは、この場合、検索する書物を入力するセルということです

そこに何も入力していないので、結果的に答えが出ませんでしたというエラーです。

ですので、検索する書物のセルに、検索する書物を入力すれば答えが出ます。

検索する書物は「ゲノム工学の基礎」ですね。

これを入力(青枠)すると下の画像になります。

結果がきちんと出てくれました。

答えは  です(赤枠)。

は? と思った人、いますよね? 絶対

ですが、これがmatch関数で出せる答えです。

この  の解釈は 検査範囲の中で3つ目に検索した書物があります

これを相対位置といいます。

という解釈になります。

検査範囲は「細胞診」から「基礎有機化学」ですので

細胞診は1行目ですよね? そうすると「ゲノム工学の基礎は」3行目ですよね?

だから、match関数の答えは  なんです。

これ、書物などのリストが何千行あった場合はかあなり楽になると思いませんか?

次はお遊びですけど、親切ですね

「行目にあります」というのを隣のセルに書いておけばいいのです(笑)

関数が入っているセルの中に収めたいと思った人もいると思います。

できますが、本筋から外れるため別記事で紹介します。

match関数についての基本はここまででです!

次はmatch関数を使う上で知っておかないといけない注意事項を二つ紹介します。

それが

注意事項

表がシートの一番上から始まっていない場合

これを考える必要があります。

見て下さい?表は1行目を含めて作成されていますね?

思い出してください?

match関数の答えは検査範囲の中で何番目(もしくは何行目)にあるかを出してくれる関数です。

つまり、表の始まりが途中だったとしても永遠に「ゲノム工学の基礎」は 3 行目です。

これが match関数で注意しなければいけないことです。

相対位置で結果を出すということです。

確認しましょう。

シートの一番上に2行分追加して

3 のままですね。

関数挿入欄を見てみました。

なんか検査する位置変わってますよね?

先程選択した時の範囲は B2からB11 であったはずですが、B4からB13 になっています。

検索する書物のセルも2つ下に行って E5 になっています(まぁ、これはいいでしょう)。

このままでは正確な行位置が分からなくなってしまいますので対処が必要です。

絶対参照にしても無駄です(絶対参照が分からない方はスルーで構いません!)

方法① シートの一番上のセルから検査範囲に含める

一つの手段として有効です。

しかし、これはシートの一番上の行に新しい行が追加されないことが前提の手段となります。

2行目や3行目などのその他の行に追加される分には問題ありません。

B4から始まっていた範囲をB1からに変更をする手段です

方法は関数挿入欄をクリックして、B4の4 を消して 1 に書き換えるでもいいですし、

範囲選択中の赤い枠の端っこにマウスのカーソルを合わせて上に引き延ばすでもいいです。

カーソルが両矢印になったらドラッグします。

こうすることで、シートの一番上から検索してくれるため、何行目かが分かるようになります。

答えとしては6行目になりますね。

これもB1からB13までの相対位置を答えとして返しているのです。

方法② match関数の解に足し算をする

2つ目の方法が、表の空いている行数を足してあげることで、正確な行位置を出す方法です。

ただ、これにも制約はありまして、

表より上の行が増えないことが前提となります。

今上にある行は3行分ありますね

ですので、

=MATCH(E5,B4:B13,0) +3

としてやるわけです。

そうすることで答えが何行目を指しているかが出るようになります。

シートの上から数えて、6番目に「ゲノム工学の基礎」がきていることが分かると思います

意外と簡単な対処方法です。

同じ文字が範囲内に含まれている場合

検査範囲にもし「ゲノム工学の基礎」が2か所にあった場合はどうなるでしょうか

答えは

3 です。

これもmatch関数で注意しなくてはいけないことです。

同じ文字が複数、同一範囲内にある場合は、

最初に一致した値の場所(行)しか答えとして返してくれないということです。

これを回避する場合や、両方とも答えさせることも可能です

しかし、今回は量が多くなるため、別記事にて紹介したいと思います。

まとめ

全て読んできたは方はわかると思います

match関数とは

任意の範囲内で、探したい文字と同じ文字を見つけてくれる関数です。

match関数を構成する要素は

  検索値: 検索したい値 のこと

 検査範囲: 検査をする範囲 のこと

照合の種類: 検索したい文字と完全に一致するの文字を検索するかそうでないかを決める

注意事項

・返す値は範囲指定した中での相対位置を返す
・範囲内に同じ文字列がある場合は検索順に早いものしか答えてして返してくれない


今回はmatch関数と注意事項について紹介しました。

この関数でも他に書きたいことができましたので、随時更新していきたいと思います!

また、他の関数との組み合わせなども随時紹介していこうと思います!

 

長々と読んでいただきありがとうございます。

いかがだったでしょうか、初心者の方が理解してくだされば成功です。

 

何か聞きたいこと・相談、わからない等があればコメント下さい、対応させていただきます!

こちらの勉強にもなるので、私の分からないことも調べてお答えさせていただきます!

Excel関数・VBAPC関連
スポンサーリンク
シェアする
nekoshanをフォローする
スポンサーリンク
nekoshan気ままブログ

コメント