スプレッドシートを効率的に検索するための4つのExcelルックアップ関数

ほとんどの場合、検索マイクロソフトエクセルスプレッドシートはとても簡単です。行と列をスキャンするだけでは不十分な場合は、Ctrl + Fを使用して検索できます。非常に大きなスプレッドシートで作業している場合は、これら4つのルックアップ関数のいずれかを使用することで多くの時間を節約できます。

ルックアップを使用してExcelで検索する方法がわかれば、スプレッドシートのサイズに関係なく、いつでもExcelで何かを見つけることができます。

1.VLOOKUP関数

この関数を使用すると、列と値を指定でき、別の列の対応する行から値を返します（それが意味をなさない場合は、すぐに明らかになります）。これを行う可能性のある2つの例は、従業員の姓を従業員番号で検索するか、姓を指定して電話番号を検索することです。

関数の構文は次のとおりです。

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])

[参照値] あなたがすでに持っている情報の一部です。たとえば、都市がどの州にあるかを知る必要がある場合、それは都市の名前になります。
[テーブル・アレイ] 関数がルックアップ値と戻り値を検索するセルを指定できます。範囲を選択するときは、配列に含まれる最初の列がルックアップ値を含む列であることを確認してください。
[col_index_num] 戻り値を含む列の番号です。
[range_lookup] はオプションの引数であり、1または0を取ります。1を入力するか、この引数を省略すると、関数は入力した値または次に小さい数値を検索します。したがって、下の画像では、SATスコア652を探しているVLOOKUPは646を返します。これは、リスト内で652未満の最も近い数値であり、[range_lookup]のデフォルトは1であるためです。

これをどのように使用するかを見てみましょう。このスプレッドシートには、ID番号、姓名、都市、州、SATのスコアが含まれています。「冬」という名前の人のSATスコアを検索するとします。 VLOOKUPはそれを簡単にします。使用する式は次のとおりです。

=VLOOKUP('Winters', C2:F101, 4, 0)

SATスコアは姓の列から4番目の列であるため、4は列インデックス引数です。テキストを探すときは、[range_lookup]を0に設定することをお勧めします。それがないと、悪い結果を得る可能性があります。

結果は次のとおりです。

それは651を返しました。これは、92行目にあるKennedy Wintersという名前の学生のSATスコアです（上の挿入図に表示されています）。構文をすばやく入力するよりも、名前を探すためにスクロールする方がはるかに時間がかかります。

VLOOKUPに関する注記

VLOOKUPを使用するときは、覚えておくとよいことがいくつかあります。範囲の最初の列がルックアップ値を含む列であることを確認してください。最初の列にない場合、関数は誤った結果を返します。列が適切に編成されている場合、これは問題にはなりません。

また、VLOOKUPが返す値は1つだけであることに注意してください。ルックアップ値として「Georgia」を使用した場合、ジョージアからの最初の学生のスコアが返され、実際にジョージアからの学生が2人いることは示されません。

2.HLOOKUP関数

VLOOKUPが別の列で対応する値を見つけるのに対し、HLOOKUPは別の行で対応する値を見つけます。通常、適切な列見出しが見つかるまで列見出しをスキャンし、フィルターを使用して探しているものを見つけるのが最も簡単なため、HLOOKUPは、非常に大きなスプレッドシートがある場合、または時間で整理された値で作業している場合に最適です。。

関数の構文は次のとおりです。

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])

[参照値] あなたが知っていて、対応する値を見つけたい値です。
[テーブル・アレイ] 検索するセルです。
[row_index_num] 戻り値の取得元の行を指定します。
[range_lookup] はVLOOKUPの場合と同じです。可能な場合は空白のままにして最も近い値を取得するか、0を入力して完全一致のみを検索します。

このスプレッドシートには、各州の行と、2000〜 2014年のSATスコアが含まれています。 HLOOKUPを使用して、2013年のミネソタ州の平均スコアを見つけることができます。これを行う方法は次のとおりです。

=HLOOKUP(2013, A1:P51, 24)

下の画像でわかるように、スコアが返されます。

ミネソタンの2013年の平均スコアは1014でした。2013は数字であり、文字列ではないため、引用符で囲まれていないことに注意してください。また、24はミネソタが24列目にあることから来ています。

これが Excelで加重平均を計算する方法。

HLOOKUPに関する注意事項

VLOOKUPと同様に、ルックアップ値はテーブル配列の最初の行にある必要があります。通常、ルックアップ値に列タイトルを使用するため、これがHLOOKUPで問題になることはめったにありません。 HLOOKUPも単一の値のみを返します。

3-4。 INDEX関数とMATCH関数

INDEXとMATCHは2つの異なる関数ですが、一緒に使用すると、大きなスプレッドシートの検索を大幅に高速化できます。どちらの機能にも欠点がありますが、それらを組み合わせることで、両方の長所を生かしていきます。

ただし、最初に、両方の関数の構文は次のとおりです。

=INDEX([array], [row_number], [column_number])

[配列] 検索する配列です。
[row_number] と [column_number] 検索を絞り込むために使用できます（これについては後で説明します）。

=MATCH([lookup_value], [lookup_array], [match_type])

[参照値] 文字列または数値を使用できる検索語です。
[lookup_array] MicrosoftExcelが検索語を検索する配列です。
[match_type] 1、0、または-1のオプションの引数です。 1は、検索語以下の最大値を返します。 0は正確な用語のみを返し、-1は検索用語以上の最小値を返します。

これら2つの関数をどのように一緒に使用するかが明確でない可能性があるため、ここで説明します。 MATCHは検索語を受け取り、セル参照を返します。下の画像では、列Fで値646を検索すると、MATCHが4を返すことがわかります。

一方、INDEXは逆のことを行います。つまり、セル参照を受け取り、その中の値を返します。ここで、City列の6番目のセルを返すように指示されると、INDEXは行6の値である「Anchorage」を返すことがわかります。

これから行うことは、MATCHがセル参照を返し、INDEXがその参照を使用してセル内の値を検索するように、2つを組み合わせることです。ウォーターズという姓の学生がいたことを思い出し、この学生のスコアが何であったかを確認したいとします。使用する式は次のとおりです。

クロックウォッチドッグタイムアウトウィンドウズ10インストール

=INDEX(F:F, MATCH('Waters', C:C, 0))

ここでは、一致タイプが0に設定されていることがわかります。あなたが文字列を探しているとき、それはあなたが使いたいものです。その関数を実行すると、次のようになります。

挿入図からわかるように、オーウェンウォーターズは1720を獲得しました。これは、関数を実行したときに表示される数値です。これは、数列を見るだけではそれほど役に立たないように思われるかもしれませんが、50回実行する必要がある場合にどれだけの時間を節約できるか想像してみてください。大規模なデータベーススプレッドシート数百の列が含まれていました！

Excel検索を始めましょう

MicrosoftExcelにはたくさんあります非常に強力な機能データを操作するためのものであり、上記の4つは表面を傷つけるだけです。それらの使い方を学ぶことはあなたの人生をはるかに楽にするでしょう。

本当にMicrosoftExcelをマスターしたい場合は、EssentialExcelのチートシートを手元に置いておくことで本当にメリットがあります。

画像クレジット：Cico / シャッターストック

共有共有つぶやき Eメール キヤノン対ニコン：どちらのカメラブランドが優れていますか？

キヤノンとニコンは、カメラ業界で最も有名な2社です。しかし、どのブランドがより良いカメラとレンズのラインナップを提供していますか？

次を読む 関連トピック

生産性
スプレッドシート
マイクロソフトエクセル
検索のコツ

著者について イアン・バックリー（216件の記事が公開されました）

Ian Buckleyは、ドイツのベルリンに住むフリーランスのジャーナリスト、ミュージシャン、パフォーマー、ビデオプロデューサーです。マッドサイエンティストになることを期待して、執筆中やステージ上にいないときは、DIYの電子機器やコードをいじっています。

イアン・バックリーのその他の作品

ニュースレターを購読する

ニュースレターに参加して、技術的なヒント、レビュー、無料の電子書籍、限定セールを入手してください。

購読するにはここをクリックしてください