一番上の入り口へ > excel > セルの値や何番目のセルかを表の中から検索したい時ベストな関数の使い分けはこれ!

セルの値や何番目のセルかを表の中から検索したい時ベストな関数の使い分けはこれ!

--


個人的には match関数がお気に入りです。最初はわかりにくいかもしれませんが、 使用実例もリンクしていますので、参考にどうぞ。
検索関数は、大きく以下の3カテゴリーを抑えておけば大体のケースに対応できると思います。
関数 対象範囲 概要 用例
lookup
vlookup,hlookup
複数のセル あるセル選択範囲のなかで一致または近いセルの値を取得するとき。ただし、データは昇順にしておくという手間がかかる。 名簿と得点の表があったとして、〜さんの国語の点は?と調べるとき、または 商品コードから商品名を探すときなどに使います。
match 複数のセル 指定したセル範囲内のどこに同じ値のセルがあるかを調べるとき 表の中で〜さんの場所を調べ、その得点一覧を出力するときなどによく使います。一致した場所だけでなく、一致した場所の値がなんなのか知りたい時はINDEX関数とセットで使います。 対象のデータはランダムな並びでも問題ないため、事前にデータを整理する必要はありません lookup関数のように昇順だけではなく、降順にも対応可能です。
search , find , replace , substitute , mid
(文字列検索関数群)
単一セル内の文字 文字の一部を置き換えたり、文字が含まれているかなどセルの文字列を操作したい時 検索用途として、その他の文字列関数を説明します。
search , find(文字列内の特定文字位置) , replace(置き換え)など

lookup関数

=LOOKUP(検査値,検査範囲,対応範囲)
 ルックアップ

[検索値:"検査範囲"の中からこの検索値を検索します(Aさん、商品名、商品コードなど)。

検査範囲:チェック対象となる検索対象範囲です("B3:D8"など)。複数列ある場合左端の縦列が優先対象になるようです。また数値は昇順にしておく必要があるそうです。

対応範囲:こちらの範囲は、「検索結果を返す値」を「抜き出す範囲」です。例えば"列位置"番目で かつ 検索範囲で見つかった行に対応するセルの値をVLOOKUP関す結果として取得します。下図の例で言うと検査範囲(赤の範囲)で上から7つ目で見つかったため、式の結果として対応範囲の上から7つ目を返しています。 ] 結果:Lookupできたセルの値かそれより辞書順で小さいものが値として返る(昇順前提)

lookupは調べ上げる意味です。
excelではあるセル範囲内で条件を満たすセルを調べるときに使います。細かく言うとセルの近い値が取得できます。
例えば数字が100個並んでいた時に、その100個の中のどこの間に、この数字が入って行けてその直前の数字は何かを教えてくれます。

具体例

例を示します。下段の表において名前を入力すると、上段の表から検索し合計点が自動的に計算される例です。
(上段がデータ全体の表で、下段がlookup関数を使って対象の値を検索する表です。)

LOOKUP(@検査値:B196="Gさん"Aという名前を,B検査範囲=「B185:B193」:名前列の行から同じ行(上から7つ目/下から3つ目)にある,C対応範囲=「H185:H193」:合計列の値(下から3つ目)を出力(=296))
セルの値が大きくてすみません。。。

この例は計(合計点)の列一つだけですが、データ元の全列(性別、英語、数学、、、)について同じように作れば、
一覧が検索されるのと同じように表示できますね。



このように名前から点数を求めたり、その逆として点数から名前を求めることも可能です。またこのlookup関数たちは、よく商品名から商品コードを求めたりすることにも使われます。

lookupの実例はこちらを参考にしてください。 商品コード検索表を作る(レッツトライExcel)

特徴のまとめ

  • 昇順にしておく必要がある
  • 一致しない場合もエラーは返らない
  • 一列、または1行の指定のみ可能


vlookup(hlookup)

今度はlookup関数と似たような、vlookupとhlookupの形についても説明します。
lookup関数と少し違うだけです。

vもhもほぼ同じなので、vlookup関数で説明します。

=VLOOKUP(検索値,範囲,列位置,検索の型)
 ブイ ルックアップ

[・検索値:"範囲"の中の縦列の中からこの検索値を検索します(Aさん、商品名、商品コードなど)。 複数列ある場合左端の縦列が対象になるようです。

範囲:検索対象範囲です("B3:D8"など)。

列位置:"列位置"番目で かつ 検索範囲で見つかった行に対応するセルの値をVLOOKUP関す結果として取得します

検索の型:TRUE(近似の物を探す:あらかじめ検索範囲を昇順にしておくこと) or FALSE(完全一致:文字列の場合はこちら)]

検索の型:デフォルト値はTRUE。TRUE指定をすると一致しなかった場合、検索値未満の最も大きい値が採用されます。

hlookupも方向が違うだけでそれ以外は同じです。 vlookupとhlookupは vertical(垂直)のv、horizontal(水平)のhと覚えておくと忘れません。


lookup,match関数の違い

lookup,match関数の違いと、昇順の影響

match関数

lookup関数は値自体が取得できますが、もう少し柔軟に使いたいようなときは何番目のデータかを調べたくなります。その時はlookup関数ではなく、match関数を使います。
例えば3番目に検索がヒットした時に、lookup関数だとそのセルの値を持ってきてしまいますが、Cさんではなく、その"3"という数字を使ってデータを整理表現したい時にmatch関数が使いたくなります。

=MATCH(検査値,検査範囲,照合の型)
マッチ

[検査値:検査値を数値、文字列、論理値やセル参照で指定
検査範囲:検査値を含む連続したセル範囲を指定
照合の型:1、0、-1のいずれかの数値を指定
   ・0:完全一致
   ・1:(省略可能)検査値以下の最大の値が検索されます。データを昇順に並べ替えておく必要があります
  ・-1:検査値以上の最小の値が検索されます。データを降順に並べ替えておく必要があります
] 結果:指定した内容と同じセルの相対位置を返す。

具体例

上記と同じデータの表を使って,今度はmatch関数を説明します。


下図↓は、合計(=H)が272(=B200)の人を探したい時のサンプルです。
B200(=272)の値が、H185:H193の中で何行目かを match関数で検索しています。結果は 5行目でした(図は"何列目?"て書いてますけど、、)

match関数を使ってもデータの番号のみしかわからないため、index関数などと組み合わせます。表の5行目、1列目からは "E"が取得出来ました(図の最下部)。

またmatch関数で値が見つからなかった場合は N/Aエラーがでます。表示させたくない場合excelのオプションの設定かエラー処理をしてください。または iserror関数とif関数を組み合わせてエラーの時は空にすると見栄えはマシになります。

1列だけだと、使いづらいので、その行(=5)のデータを全て(名前、性別、英語、数学、、、)表示させています[図の下側の濃い緑色の部分]。
5行目1列目(相対値)は名前の列で Eさんです[図の左下の数式部分]。同様に2列目は性別の列で男のパラメータでした。以下 英語は3列目、・・・計は7列目です。 INDEXの数式を使ってそれを表しています。


match関数は このようなとき 第3引数は0をよく使います。
第3引数の0は完全一致で検索する方法を指定します。そのため表はソートされている必要はありません。なので lookup関数より使いやすいと思います。
照合の値として、"検索値を超えないもっとも近い数字の場合1"を, "その逆の場合 -1"を照合値として指定する場合、元の表はソートされている必要があります。

活用実例はこちらを参考にしてください。 リストの過不足をチェック!(レッツトライExcel)
ちょっとかっこ良く柔軟にすっきりと参照先シートを文字で指定する ちょいわざ




おすすめの関数である理由は

私は lookup関数系は使わず、ほぼ100% match関数を使います。その理由は「ランダムな列で検索できる」こと、「場所の番号がわかる」こと、「その場所の番号を使ってさらに柔軟に処理できる」ことだからです。完全一致の欠点についても、*という文字を使えるためそれほど制約を感じません。

複数の文字列に一致しているかを検索するには、 その条件文列を追加して、そこを対象にすれば良いと思います。
例えば、 APPLE と ORANGE を含む列を検索したいときは、 1つずつ分解して、 APPLEが含まれているかの列と、ORANGEが含まれているかの列を作ります。 AND条件であれば、両方○の時、OKとするような列もさらに作ります。OR条件であれば、どちらかが含まれたらOKとなる列を追加して、そこを条件対象の範囲とすれば、同じようにsumif関数が使えると思います。

文字列検索関数群

lookup/matchの他にも、検索系の関数としてたまに使うものがありますので紹介します。
先ほどの関数は、セル範囲が対象だったのが、これら↓は主にセルの値自体の検索になります。
関数 備考など
search
/find
=SEARCH("区","港区")= 3
=SEARCH("e","statement",6)= 7
前半の文字が、後半の文字に含まれているかを調べる関数
半角の疑問符 (?) または半角のアスタリスク (*) をワイルドカード文字として使用することができます。
ワイルドカード文字の疑問符は任意の 1 文字を表し、アスタリスクは任意の文字列を表します
検索文字列が見つからないと、エラー値 #VALUE! が返されます。
replace =REPLACE("mother",SEARCH("other","mother"),5,"onday")
motherのotherを ondayに置き換えた結果を取得する例
文字に対して、指定範囲の文字を特定の文字と置き換えます。
substitute =SUBSTITUTE(A2, "売上", "原価")
A2セルの売上を原価に置換した結果を取得する例
セルの文字に対して特定の文字を置き換えます。
mid =MID("夏みかん",2,3) = "みかん" 特定の範囲を抜き出します。

その他の検索技 : "*"と"?"文字

*と?は 検索対象につけると任意の文字を表すメタ文字(抽象的な文字の概念表現)になります。


?: ?はその位置に対する任意の1文字を示します。例えば出席と欠席 両方にマッチさせる表記は、 "?席" となります。
*: ?と違い1文字だけでなく複数文字に対応する場合に使います。例えば、ご飯、お昼御飯 両方にマッチさせるには、 "*飯" と書きます。

?と*文字の使用例としてcount/sum系の例ですが、こちらを参考にしてください
表からその特定のセルだけ合計したい時に使える ちょいわざ


類似リンク(検索系関数 実例サンプルあり)

リストの過不足をチェック!(レッツトライExcel)
match関数を使った実例

今まさに合計したいときベストな関数とやり方はこれたった5つの関数を抑えれば大丈夫!
活用図例付き(Excel関数使いこなし編)


セルの値や何番目のセルかを表の中から検索したい時ベストな関数の使い分けはこれ!(Excel関数使いこなし編)

宴会幹事向け出欠確認アンケートを作る(レッツトライExcel)

マクロを使わずに重複行をサクッと削除するちょいわざ

C列にあるそれぞれの単語が対象シートのセルの
どこにあるか調べるちょいわざ(match)


ちょっと上ゆく関数を極める!(Excel関数使いこなし編)



上に戻る↑

一番上の入り口へ > excel >セルの値や何番目のセルかを表の中から検索したい時ベストな関数の使い分けはこれ!