| 関数 |
概要 |
用例 |
lookup
(vlookup,hlookup) |
ある範囲のなかで条件を満たすセルを調べるとき |
名簿と得点の表があったとして、〜さんの国語の点は？と調べるとき、または
商品コードから商品名を探すときなどに使います。 |
| match |
条件を満たすセルがどこにあるかを調べるとき |
表の中で〜さんの場所を調べ、その得点一覧を出力するときなどによく使います。その場合INDEX関数とセットで使います |
| 文字列検索関数群 |
検索用途として、その他の文字列関数を説明します。 |
search , find（文字列内の特定文字位置） , replace(置き換え)など |
lookup関数
lookupは調べ上げる関数です。
excelではある範囲のなかで条件を満たすセルを調べるときに使います。
=LOOKUP(検査値,検査範囲,対応範囲)
ルックアップ
[検索値:"検査範囲"の中からこの検索値を検索します(Aさん、商品名、商品コードなど)。
複数列ある場合左端の縦列が優先対象になるようです。また数値は昇順にしておく必要があるそうです。
検査範囲:検索対象範囲です("B3:D8"など)。
対応範囲:例えば"列位置"番目で かつ 検索範囲で見つかった行に対応するセルの値をVLOOKUP関す結果として取得します
※検索の型：TRUEはデフォルト値。TRUEで一致しなかった場合、検索値未満の最も大きい値が採用される。
以下の例は名前を変更すると合計点が自動的に計算される例です。
これは合計点の一つだけですが、データ元の全列について同じように作れば、
一覧が検索されるのと同じように表示できますね。
このように名前から点数を求めたり、その逆も可能です。そして商品名から商品コードを求めたりすることにも使われます。
vlookupとhlookupは vertical(垂直)のv、horizontal(水平)のhと覚えておくと忘れません。
=VLOOKUP(検索値,範囲,列位置,検索の型)
ブイ ルックアップ
[検索値:"範囲"の中の縦列の中からこの検索値を検索します(Aさん、商品名、商品コードなど)。
複数列ある場合左端の縦列が対象になるようです。
範囲:検索対象範囲です("B3:D8"など)。
列位置:"列位置"番目で かつ 検索範囲で見つかった行に対応するセルの値をVLOOKUP関す結果として取得します
検索の型:TRUE(近似の物を探す:あらかじめ検索範囲を昇順にしておくこと) or FALSE(完全一致:文字列の場合はこちら)]
※検索の型：TRUEはデフォルト値。TRUEで一致しなかった場合、検索値未満の最も大きい値が採用される。
実例はこちらを参考にしてください。
商品コード検索表を作る(レッツトライExcel)
match関数
何番目のデータかを調べるときは match関数を使います。
=MATCH（検査値,検査範囲,照合の型)
マッチ
[検査値:検査値を数値、文字列、論理値やセル参照で指定
検査範囲:検査値を含む連続したセル範囲を指定
照合の型:1、0、-1のいずれかの数値を指定
・0:完全一致
・1:(省略可能)検査値以下の最大の値が検索されます。データを昇順に並べ替えておく必要があります
・-1:検査値以上の最小の値が検索されます。データを降順に並べ替えておく必要があります
]
↑意図としては、合計(=H)が272(=B200)の人を探したい向けのサンプルです。
B200(=272)の値が、H185:H193の中で何行目かを match関数で検索しています。結果は 5行目でした。
それだけだと、使いづらいので、その行(=5)のデータを表示させています。
5行目1列目（相対値）は名前の列で Eさんです。同様に2列目は性別の列で男のパラメータでした。以下
英語は3列目、・・・計は7列目です。 INDEXの数式を使ってそれを表しています。
match関数を使ってもデータの番号のみしかわからないため、index関数などと組み合わせます。
第3引数の0は完全一致で検索する方法を指定します。そのため表はソートされている必要はありません。
検索値を超えないもっとも近い数字の場合 1,をその逆の場合 -1を照合値として指定する場合、元の表はソートされている必要があります。
また見つからない場合は N/Aエラーがでます。表示させたくない場合excelのオプションの設定かエラー処理をしてください。
実例はこちらを参考にしてください。
リストの過不足をチェック！(レッツトライExcel)
文字列検索関数群
| 関数 |
例 |
備考など |
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) = "みかん" |
特定の範囲を抜き出します。 |
その他の検索わざ *と?
*と?は 検索対象につけると任意の文字を表すメタ（抽象的な表現）文字になります。
?: 出席と欠席 両方にマッチさせるには、 "?席" と書きます。?はその位置に対する任意の文字を示します。
*: ご飯、お昼御飯 両方にマッチさせるには、 "*飯" と書きます。
類似リンク(検索系関数 実例サンプルあり)
・リストの過不足をチェック！(レッツトライExcel)
・宴会幹事向け出欠確認アンケートを作る(レッツトライExcel)
・マクロを使わずに重複行をサクッと削除するちょいわざ
・C列にあるそれぞれの単語が対象シートのセルのどこにあるか調べるちょいわざ(match)
・合計系の関数を極める！たった5つの関数を抑えれば大丈夫！活用図例付き(Excel関数使いこなし編)
・検索系の関数を極める！(Excel関数使いこなし編)
・ちょっと上ゆく関数を極める！(Excel関数使いこなし編)
|
|