2つの異なるデータ群からデータが合致していないキーワードまたは数字を抽出する場合 大量のデータの中から、複数の関連した項目をもったデータを抽出するときにVLOOKUP関数は力を発揮します。 関数の引数で数式を作成する場合• 図１ 図１の場合、[ 1 ]の列に商品番号を入力すると、[ 2 ]の商品一覧から商品名と単価を検索し、[ 3 ]にて商品名と売上の計算結果を表示しています。 文字列として保存されている場合、誤った値や想定外の値が返されることがあります。
16COLUMN関数を使うと、右方向へコピーして値が1増加するので「2」を返すように「COLUMN B1 」とします。 商品一覧表をテーブルに変換して、 テーブル名を 商品コード としました。
以上をまとめると次のようになります。
C4セルに数式が入力できたら、下方向へ数式をコピーします。
0を入力すると｢検索値｣と完全に一致するデータのみを探し出します。
セル範囲 H4:J6 に『 商品一覧表』と名前を定義します。
本連載では、今回から数回にわたり、XLOOKUP関数の使用方法と特徴について解説していきます。
アスタリスクは任意の一連の文字列に相当します。
左から2列目や3列目を検索することはできません。
例えば商品番号と商品の情報が一覧となった表があった場合、商品番号を指定しただけで商品の情報を取り出し表示することが可能です。 つまり、VLOOKUP関数は、データを検索し、そのデータに該当した行の指定列からデータを取り出す関数なのです。 これらも、ぜひご覧ください！ 【関連記事】• 多くの場合、VLOOKUP関数では 完全に一致する値を検索することが多いため、4つめの引数は 「FALSE」にすると覚えてしまっていいでしょう。
15［ 検索値］に一致するデータが見つからないとき、「TRUE」では［ 検索値］を超えない最大値を該当のデータとします。 Excel VLOOKUP関数で別シートを参照する方法 VLOOKUP関数でデータを検索す際に、検索を行いたい検索範囲がExcelの別シートにある場合、別シートを参照することで検索範囲として指定することが可能です。
検索値が含まれるセル範囲。
セル範囲 H4:J6 に『 商品一覧表』と名前を定義します。
今回は、参照したい表が複数あって、「 ある条件のときはA表から、別の条件のときはB表からデータを取り出す」というように、条件によって取り出し元の表を切り替える、といったことを考えます。
その理由は、「」の「」で紹介したように、このサンプルでは、商品Noを「4」といった数値ではなく、「0004」といった文字列として扱っているからです。 に｢商品一覧表｣のナンバーを入力すると、自動的に対応する商品名が表示されるようにしたいとします。
4その後、形式を選択して貼り付けの「値」でデータを置き換えます。
「250」という値は A5：A9 には存在しないため、それを超えない近しい値である「200」を使って結果が返ります。
新しいテーブルにフィールドを追加するには、手順 3 で収集した情報を使用して、最初の空の列に VLOOKUP 数式を入力します。
【説明】• 在庫管理で商品番号と商品の情報が一覧となっている場合、商品番号を検索してその商品の商品名や価格を取り出す場合• 関連する参照テーブル名。
これは［ 範囲］で指定した表のうち、 取り出したい値がある列が左から何列目にあるかを意味します。 構文 VLOOKUP 検索値, 範囲, 列番号, [検索の型] 次に例を示します。
6左端列を並べ替えるか、または FALSE を使用して、完全一致を検索してください。 検索は、指定した表の「左端列」で行われます。
実際に探したい検索値 セル A2 と、それを TRUE で探索した結果 セル B2 が等しくないことがわかります。
が入力されていれば 商品一覧表を用いて商品名を検索する｣という指示を入力しています。
Excel2007以降は、[数式]タブの[名前の定義]を実行します。
例えば、商品IDから商品の単価を取り出したり、顧客名から住所を取り出したりなど、よく行う処理が VLOOKUP関数だけで解決できます。 ここでは単価を取り出したいので、［ 列番号］に「3」と指定します。
5D4:E4 セルを選択し、オートフィルでD6:E6セルまで数式をコピーして完成です。 VLOOKUP関数でできること VLOOKUP関数はExcelで表形式のデータを検索する場合に、最もよく使われる関数の1つなので、ぜひ覚えておこう。
列見出しの位置を テーブルの見出し行のどこにあるかを MATCH関数で求めています。
［ 列番号］に指定した「2」は、［ 範囲］の表のうち、左から2列目を意味します。
VLOOKUP関数の列位置を 2 とか 3 と指定していますが、列見出しの「商品名」「単価」を利用する方法もあります。
3第3引数と第4引数を指定する 「,」を入力して［ 列番号］の「2」と［ 検索の型］の「FALSE」を指定します。 更に、C4にも【リスト】で【1，2，3，4，5，6】としておけば、マウス操作のみで料金が表示されます。
［ 検索の型］が「FALSE」の場合は「200」、「TRUE」の場合は「150」が取得されました。
ただし、VLOOKUP関数は引数が多いこともあり、いざ使おうと思うと、なかなか思い出せないものでもある。
特に最後にスペースが含まれる場合は気づきにくいです。
Excel2007以降、Excel2013以降ではIFERROR関数やIFNA関数が使えて簡潔な数式で処理ができるようになりました。 今回は完全に一致する値がない場合は「完全一致なし」という文字列を表示しましたが、これはあくまでも例です。 MATCH関数で求めた列位置の一人分の料金を出します。
14セル範囲 G4:J6 に『 評価一覧』と名前を定義すると、• 【問題】VLOOKUP関数とMATCH関数を使って運賃を求めなさい B C D E 2 運賃表 3 東京 名古屋 大阪 4 鹿児島 50,000 40,000 35,000 5 宮崎 45,000 35,000 30,000 6 熊本 40,000 30,000 25,000 7 福岡 30,000 20,000 15,000 8 9 到着地 名古屋 10 出発地 熊本 11 運賃は 30,000 解答例• （範囲を絶対参照にするのはC4セルの数式をC5,C6セルにコピーする時値が変化しないようにするためです。
その表は、同じシート上でも別シート上でも、別ブック上でも構わない。
検索範囲 … 検索値 データ が含まれるセルの範囲を入力します。
検索方法は、完全一致を利用したいため、「FALSE」を指定しました。