ExcelでVLOOKUPを使用する方法

Excel にデータを含む大きなスプレッドシートがあり、そこから特定の情報をフィルター処理して抽出する簡単な方法が必要になったことはありませんか? Excel で VLOOKUP を使用する方法を習得すれば、強力な Excel 関数を 1 つだけ使用してこのルックアップを実行できます。

Excel の VLOOKUP 関数は、多数のパラメーターを持ち、複数の使用方法があるため、多くの人を怖がらせます。この記事では、Excel で VLOOKUP を使用するすべての方法と、この関数が非常に強力な理由について説明します。

Excel の VLOOKUP パラメータ

=VLOOKUP( と入力し始めると、 Excel の任意のセルに入力すると、使用可能なすべての関数パラメーターを示すポップアップが表示されます。

これらの各パラメーターとその意味を調べてみましょう。

• lookup_value :スプレッドシートから探している値
• table_array :検索したいシート内のセル範囲
• col_index_num :結果を取得する列
• [range_lookup] :一致モード (TRUE =おおよそ、FALSE =正確)

これら 4 つのパラメーターを使用すると、非常に大きなデータセット内のデータに対して、さまざまな便利な検索を実行できます。

簡単な VLOOKUP Excel の例

VLOOKUP は、あなたが学習した基本的な Excel 関数の 1 つではありません。そのため、簡単な例を見てみましょう。

次の例では、米国の学校の SAT スコアの大規模なスプレッドシートを使用します。このスプレッドシートには、450 を超える学校と、読解、数学、および作文の個々の SAT スコアが含まれています。自由にダウンロードして、フォローしてください。データをプルする外部接続があるため、ファイルを開くときに警告が表示されますが、安全です。

興味のある学校を見つけるために、このような大規模なデータセットを検索するのは非常に時間がかかります。

代わりに、テーブルの横にある空白のセルに簡単なフォームを作成できます。この検索を行うには、学校用に 1 つのフィールドを作成し、読解、数学、作文の点数用に 3 つの追加フィールドを作成します。

次に、Excel で VLOOKUP 関数を使用して、これら 3 つのフィールドを機能させる必要があります。 リーディングで フィールドで、次のように VLOOKUP 関数を作成します:

<オール>

タイプ =VLOOKUP(

School フィールドを選択します。この例では I2 です .カンマを入力してください。

検索するデータを含むセル範囲全体を選択します。カンマを入力してください。

範囲を選択すると、検索に使用している列 (この場合は学校名の列) から開始し、データを含む他のすべての列と行を選択できます。

注意 注:Excel の VLOOKUP 関数は、検索列の右側のセルのみを検索できます。この例では、学校名の列は、検索しているデータの左側にある必要があります。

4. 次に、リーディング スコアを取得するには、選択した列の左端から 3 番目の列を選択する必要があります。 3 と入力してください カンマをもう 1 つ入力してください。
5. 最後に FALSE と入力します 完全一致の場合、) で関数を閉じます .

最終的な VLOOKUP 関数は次のようになります:

=VLOOKUP(I2,B2:G461,3,FALSE)

最初に Enter キーを押して関数を終了すると、[読み方] フィールドに #N/A が含まれていることに気付くでしょう。 .

これは、School フィールドが空白で、VLOOKUP 関数が検索するものが何もないためです。ただし、調べたい高校の名前を入力すると、その行のリーディング スコアの正しい結果が表示されます。

大文字と小文字を区別する VLOOKUP に対処する方法

データセットに記載されているのと同じケースで学校名を入力しないと、結果が表示されないことに気付くかもしれません。

これは、VLOOKUP 関数が大文字と小文字を区別するためです。これは、特に非常に大きなデータセットの場合、検索対象の列が大文字の使用方法と一致しない場合に厄介です。

これを回避するには、結果を検索する前に、検索対象を強制的に小文字に切り替えることができます。これを行うには、検索している列の横に新しい列を作成します。関数を入力してください:

=TRIM(LOWER(B2))

これにより、学校名が小文字になり、名前の左側または右側にある余分な文字 (スペース) が削除されます。

Shift キーを押したまま、マウス カーソルを最初のセルの右下隅に置き、2 本の水平線に変更します。マウスをダブルクリックして、列全体を自動入力します。

最後に、VLOOKUP はこれらのセルのテキストではなく数式を使用しようとするため、すべてを値のみに変換する必要があります。これを行うには、列全体をコピーし、最初のセルを右クリックして、値のみを貼り付けます。

すべてのデータがこの新しい列でクリーンアップされたので、B2 ではなく C2 でルックアップ範囲を開始することで、以前の列の代わりにこの新しい列を使用するように Excel の VLOOKUP 関数を少し変更します。

=VLOOKUP(I2,C2:G461,3,FALSE)

検索を常に小文字で入力すると、常に適切な検索結果が得られることがわかります。

これは、VLOOKUP で大文字と小文字が区別されるという事実を克服するための便利な Excel のヒントです。

VLOOKUP 近似一致

この記事の最初のセクションで説明した完全一致の LOOKUP の例は非常に簡単ですが、近似一致はもう少し複雑です。

近似一致は、数値範囲を検索する場合に最適です。これを正しく行うには、検索範囲を適切にソートする必要があります。この最も良い例は、数字の等級に対応する文字の等級を検索する VLOOKUP 関数です。

教師が生徒の宿題の年間成績の長いリストを持っており、最終的な平均値の列がある場合、その最終成績に対応する文字の成績が自動的に表示されると便利です。

これは VLOOKUP 関数で可能です。必要なのは、数値スコア範囲ごとに適切な文字グレードを含む右側のルックアップ テーブルだけです。

ここで、VLOOKUP 関数と近似一致を使用して、正しい数値範囲に対応する適切な文字グレードを見つけることができます。

この VLOOKUP 関数では:

• lookup_value :F2、最終平均成績
• table_array :I2:J8、レター グレード ルックアップ範囲
• index_column :2、ルックアップ テーブルの 2 番目の列
• [range_lookup] :TRUE、近似一致

G2 で VLOOKUP 関数を終了して Enter キーを押すと、前のセクションで説明したのと同じ方法で残りのセルに入力できます。すべての文字の等級が適切に入力されていることがわかります。

Excel の VLOOKUP 関数は、割り当てられた文字スコアの成績範囲の下限から次の文字スコアの範囲の上限までを検索することに注意してください。

したがって、「C」は下位範囲 (75) に割り当てられた文字である必要があり、B はそれ自体の文字範囲の下部 (最小) に割り当てられます。 VLOOKUP は、60 (D) の結果を、60 から 75 の間の最も近い近似値として「検索」します。

Excel の VLOOKUP は非常に強力な機能で、長い間使用されてきました。また、Excel ブック内の任意の場所で一致する値を見つけるのにも役立ちます。

ただし、毎月の Office 365 サブスクリプションを持っている Microsoft ユーザーは、新しい XLOOKUP 関数にアクセスできるようになったことに注意してください。この関数には、より多くのパラメーターと追加の柔軟性があります。半年ごとのサブスクリプションをお持ちのユーザーは、2020 年 7 月に更新が公開されるまで待つ必要があります。