Excel Master Microsoft

【データ検索】に関する関数|マスター関数(Excel/エクセル)_第4弾!

エクセル関数の勉強
サブロー
Excel/エクセル関数初心者必読 !!第4弾は、リスト等の範囲からデータを検索する関数[検索/行列関数]について説明します。本講座でExcel/エクセル関数のスキルをアップしていきましょう !!
↓本講座のINDEXは下記です↓
マスター関数(Excel/エクセル)-ALL INDEX
検索/行列関数の中で、下記の6種類を順番に紹介していきます!使用例については、(個人的に)使用頻度が高いもののみ付け加えています。
モグゾー
関数名 説明 分類
HLOOKUP 指定したテーブルまたは配列の先頭行で特定の値を検索し、指定した列と同じ行にある値を返す 検索/行列
LOOKUP 1行または1列のみのセル範囲、または配列に含まれる値を返す。この関数は旧バージョンとの互換性を維持するためのもの 検索/行列
MATCH 指定された照合の種類に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返す 検索/行列
VLOOKUP 指定された範囲の1列目で特定の値を検索し、指定した列と同じ行にある値を返す。テーブルは昇順で並び替えておく必要があります 検索/行列
XLOOKUP 範囲または配列を検索し、見つかった最初の一致に対応する項目を返す。一致が存在しない場合、XLOOKUPは最も近い(概算)一致を返すことができる。 検索/行列
XMATCH 配列またはセル範囲内の項目の相対的な位置を返す 検索/行列

【HLOOKUP】関数

①個人的使用頻度:

②読み方:エイチルックアップ

③分類:検索/行列関数

  ④書式:HLOOKUP(検索値,参照範囲,行番号[,検索の型])

●引数1:検索値

⇨検索したい値またはセル参照を指定する。

 文字列を指定する場合は、『"』で囲む必要がある。(例:"りんご")

※検索値は全角文字と半角文字は区別して検索しますが、英字の大文字と小文字は区別しない

●引数2:参照範囲

⇨参照したい一覧表の範囲を指定する。

●引数3:行番号

⇨引数2の「参照範囲」の上から数えた行の番号を指定する。

 (参照範囲の一番上端行を"1"として、下方向に順番に数えた数を指定)

●引数4:検索の型(省略可)

⇨下記2パターン(4種類)の型がある

FALSE:検索値と完全一致。もしない場合は、『#N/A』が返る

0:検索値と完全一致。もしない場合は、『#N/A』が返る("FALSE"と一緒)

TRUE:近似値(検索値未満の最大値)

1:近似値(検索値未満の最大値)("TRUE"と一緒)

※省略した場合は、"TRUE"、"1"と同じ型となる。

●関数説明:指定したテーブル、または配列の先頭行で特定の値を検索し、指定した列と同じ行にある値を返す

[参考] H:Horizontal(水平(横方向))を意味する

⑤使用例:

HLOOKUP関数の使用例

【LOOKUP】関数-ベクトル形式

①個人的使用頻度:

②読み方:ルックアップ

③分類:検索/行列関数

  ④書式:LOOKUP(検索値,検索範囲,対応範囲)

●引数1:検索値

⇨検索値を数値、セル番号で指定する

●引数2:検索範囲

⇨検索する範囲を1行または1列で指定する

●引数3:対応範囲

⇨検索範囲で見つかった値と同じ位置の値を取り出す 

●関数説明:1行または1列のみのセル範囲、または配列に含まれる値を返す。(旧バージョンとの互換性を維持用)

 

【LOOKUP】関数-配列形式

①個人的使用頻度:

②読み方:ルックアップ

③分類:検索/行列関数

  ④書式:LOOKUP(検索値,配列)

●引数1:検索値

⇨検索値を数値、文字列、論理値、セル番号等で指定する

●引数2:配列

⇨別の位置にある表から検索範囲をセル範囲で指定する。

☆セル範囲の行数が列数より多い、または同じ数の場合、範囲の先頭列が検索の対象

☆セル範囲の列数が行列より多い場合、範囲の先頭行が検索の対象

●関数説明:1行または1列のみのセル範囲、または配列に含まれる値を返す。(旧バージョンとの互換性を維持用)

 

【MATCH】関数

①個人的使用頻度:

②読み方:マッチ

③分類:検索/行列関数

  ④書式:MATCH(検索値,検索範囲,検索方法)

●引数1:検索値

⇨検索範囲内で検索したい値を指定する。

 検査値には、数値、文字列、論理値、セル参照が指定可能。

●引数2:検索範囲

⇨検索値が検索するセル範囲を1行、もしくは1列で指定する。

 検索範囲は配列に対するセル参照も指定可能。

 

●引数3:検索方法(省略可)

⇨検索値を検索する方法を下記のように指定する。

省略:検索値以下の最大の値が検索される(”1"と同様)

0:検索値に完全一致する値が検索される

1:検索値以下の最大の値が検索される("省略"と同様)

-1:検索地以上の最小値が検索される

※検索値が発見されなかった場合は、『#N/A』となる。

●関数説明:指定された照合の種類に従って検査範囲内を検索し、

      検査値と一致する要素の、配列内での相対的な位置を表す数値を返す

⑤使用例:

MATCH関数の使用方法1

MATCH関数の使用方法2

 

【VLOOKUP】関数

①個人的使用頻度:

②読み方:ブイルックアップ

③分類:検索/行列関数

  ④書式:VLOOKUP(検索値,検索範囲,列番号,検索の型)

●引数1:検索値

⇨検索範囲の左列で検索したいデータを指定する。

●引数2:検索範囲

⇨検索したい範囲をセル範囲、または配列で指定する。

●引数3:列番号

⇨範囲内で戻り値として取得したいデータは入力されている列番号を、左端を1として何番目にあるかを指定する。

●引数4:検索方法(省略可)

⇨検索値を検索する方法を下記のように指定する。

TRUE:近似値(検索値未満の最大値)を範囲の中から探す。

              範囲の左端の列にあるデータを昇順で並び替えておかないと正しい結果が返ってこない。

    省略した場合、"1"を指定した場合も同様。

FALSE:検索値と完全一致するデータのみを範囲の中から探す。

   「FALSE」の場合はデータの並び替えはない。

    "0"を指定した場合も同様。

一致するデータがない場合は「#N/A」を結果として返す。

●関数説明:指定された範囲の1列目で特定の値を検索し、指定した列と同じ行にある値を返す。

(テーブルは昇順で並び替えておく必要がある)

⑤使用例:

VLOOKUP関数の使用例1

VLOOKUP関数の使用例2

【XLOOKUP】関数

①個人的使用頻度:

②読み方:エックスルックアップ

③分類:検索/行列関数

  ④書式:XLOOKUP(検索値,検索範囲,戻り範囲[,一致モード][,検索モード])

●引数1:検索値

⇨検索の対象とするセルを指定する。

●引数2:検索範囲

⇨検索の対象とするセルやセルの範囲を指定する。

●引数3:戻り範囲

⇨検索の結果として返すセルやセルの範囲を指定する。

●引数4:一致モード(省略可)

⇨一致の種類を指定する。

0:検索値と完全一致するデータのみを範囲の中から探す。(省略した場合も同様)

-1:検索値と完全一致、または次に小さい項目を返す。

1:検索値と完全一致、または次に大きい項目を返す。

2:ワイルドカード文字と一致する項目を返す。

●引数5:検索モード(省略可)

⇨使用する検索順序を指定する。

1:先頭から末尾へ検索する。(省略した場合も同様)

-1:末尾から先頭へ逆方向に検索する

2:昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行する。

※昇順に並んでいないと意図しない検索結果が返ってくるので使用には注意が必要!!!

-2:降順で並べ替えられた検索範囲を使用してバイナリ検索を実行する。

※降順に並んでいないと意図しない検索結果が返ってくるので使用には注意が必要!!!

※一致するデータがない場合は「#N/A」が結果として返す。 

●関数説明:範囲または配列を検索し、見つかった最初の一致に対応する項目を返す。

     一致が存在しない場合、XLOOKUPは最も近い(概算)一致を返すことができる。

~バイナリー検索~
検索対象がソートされている場合に適用できる高速な検索手法のこと。検索の開始は、検索範囲の真ん中に位置するデータと比べ、もし一致すれば検索完了。もし小さければ目的のデータは前半にあり、大きければ後半にある。これをデータが無くなるまで繰り返し、検索対象を徐々に絞って検索速度を上げる手法。

⑤使用例:

XLOOKUP関数の使用例

【XMATCH】関数

①個人的使用頻度:

②読み方:エックスマッチ

③分類:検索/行列関数

  ④書式:XMATCH(検索値,検索範囲,[一致モード][,検索モード])

●引数1:検索値

⇨参照する値、セルを指定する。

●引数2:検索範囲

⇨検索する配列または範囲を指定する。

●引数3:一致モード(省略可)

⇨一致の種類を指定する。

0:検索値と完全一致するデータのみを範囲の中から探す。(省略した場合も同様)

   一致するデータがない場合は「#N/A」が結果として返す      

-1:検索値と完全一致するデータのみを範囲の中から探す。

   一致するデータがない場合は次の小さい項目を返す。

1:検索値と完全一致するデータのみを範囲の中から探す。

   一致するデータがない場合は次の大きい項目を返す。

2:ワイルドカード文字との一致する項目を返す。

●引数4:検索モード(省略可)

⇨使用する検索モードを指定する。

1:先頭の項目から検索を実行する。      

-1:末尾の項目から逆方向に検索を実行する

2:昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行する。

-2:降順で並べ替えられた検索範囲を使用してバイナリ検索を実行する。

●関数説明:配列またはセル範囲内の項目の相対的な位置を返す

⑤使用例:

   5-1:完全一致パターン(縦方向)

XMATCH関数の使用例1

   5-2:完全一致パターン(横方向)

XMATCH関数の使用例2

   5-3:完全一致、または次に小さいパターン

XMATCH関数の使用例3

   5-4:完全一致、または次に大きいパターン

XMATCH関数の使用例4

サブロー
それでは、今回はここまで。最後までお読みいただきありがとうございました!
2020年8月24日公開 | 2020年8月26日更新

~INDEX一覧~

エクセル関数の勉強 1

サブローエクセル/Excel関数の一覧にしています。本ブログ内で記事にしているものは、講座の列にリンク付けを随時行います(^o^) ※使用頻度のランクに関しては、個人的な主観でランク付けをしています。 ...

2

サブロー危険物取扱者試験 乙4種対策講座の目次(INDEX)を作りました。 随時更新していくので、よろしくお願いします。 【目次/INDEX】 講座ID分類内容リンク 1共通#1『危険物取扱者試験』の ...

3

サブローQC(品質管理)検定に関する説明が増やして行く予定なのでリンクに飛ぶような目次(INDEX)を作りました。 随時更新していくので、よろしくお願いします。 モグゾー対象級を記載していますが、あく ...

4

サブローExcel VBAに関する説明を今後増やして行く予定なので、各リンクに飛ぶような目次(INDEX)を作りました。 随時更新していくので、よろしくお願いします。 目次(INDEX) 講座No内容 ...

-Excel Master, Microsoft
-, , , , , ,

Copyright© サブログ , 2020 All Rights Reserved Powered by AFFINGER5.