内容
関数に関するチュートリアルの第 XNUMX 部では、 VPR (VLOOKUP) Excel で、すべての力を導くのに役立ついくつかの例を分析します。 VPR 最も野心的な Excel タスクを解決します。 例では、この機能がどのように機能するかについての基本的な知識が既にあることを前提としています。 そうでない場合は、構文と基本的な使用法を説明するこのチュートリアルの最初の部分から始めることに興味があるかもしれません。 VPR. それでは始めましょう。
複数の条件で Excel を検索する
演算 VPR in Excel は、データベース内の特定の値を検索するための非常に強力なツールです。 ただし、重大な制限があります。その構文では、XNUMX つの値しか検索できません。 複数の条件で検索したい場合は？ 以下に解決策があります。
例 1: 2 つの異なる基準による検索
注文のリストがあり、検索したいとします。 商品の数量 (数量)、XNUMX つの基準に基づく – クライアント名 (顧客) → 製品名 （製品）。 以下の表からわかるように、各バイヤーがいくつかの種類の商品を注文したという事実によって、問題は複雑になります。
通常の機能 VPR 指定されたルックアップ値に一致する最初の値を返すため、このシナリオでは機能しません。 例えば、商品の数量を知りたい場合 お菓子'購入者が注文した ジェレミーヒル、次の式を書きます。
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– この式は結果を返します 15製品に対応 いちご、ブロッコリー、これは一致する最初の値であるためです。
簡単な回避策があります。必要なすべての基準を組み合わせる追加の列を作成します。 この例では、これらは列です クライアント名 (顧客) → 製品名 （製品）。 マージされた列は常に検索範囲の一番左の列でなければならないことを忘れないでください。 VPR 値を探すときに検索します。
したがって、テーブルに補助列を追加し、次の数式をすべてのセルにコピーします。 = B2＆C2. 文字列を読みやすくしたい場合は、結合した値をスペースで区切ることができます。 =B2&» «&C2. その後、次の式を使用できます。
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
セルはどこですか B1 引数の連結値を含む 参照値 (lookup_value) および 4 - 口論 col_index_num (column_number)、つまり、取得するデータを含む列の番号。
例 2: テーブルが別のシートに表示されている XNUMX つの基準による VLOOKUP
別のシートまたは別の Excel ワークブックにある XNUMX 番目のテーブル (ルックアップ テーブル) からデータを追加してメイン テーブル (メイン テーブル) を更新する必要がある場合は、挿入する数式で目的の値を直接収集できます。メインテーブルへ。
前の例のように、結合された値を含むルックアップ テーブルに補助列が必要になります。 この列は、検索範囲の左端の列である必要があります。
したがって、式 VPR このようになる可能性があります：
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
ここで、列 B と列 C には、それぞれ顧客名と製品名が含まれており、リンク 注文!$A&$2:$D$2 別のシートで検索するテーブルを定義します。
数式を読みやすくするために、ビュー範囲に名前を付けると、数式がより単純になります。
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
数式が機能するには、表示しているテーブルの左端の列の値を、検索条件とまったく同じ方法で組み合わせる必要があります。 上の図では、関数の最初の引数で行う必要があるのと同じ方法で、u2bu2band の間にスペースを入れて値を結合しました (BXNUMXbuXNUMXand CXNUMX)。
忘れないでください！ 演算 VPR 255 文字に制限されているため、255 文字を超える値は検索できません。 これを念頭に置いて、目的の値の長さがこの制限を超えないようにしてください。
補助列を追加することは、最も洗練された方法ではなく、常に受け入れられるとは限らないことに同意します。 ヘルパー列なしで同じことを行うことができますが、関数を組み合わせたより複雑な式が必要になります。 INDEX (INDEX) と MATCH (より露出)。
VLOOKUPを使用して2番目、3番目などの値を抽出します
あなたはすでにそれを知っています VPR 一致する値を 2 つだけ返すことができます。より正確には、最初に見つかった値です。 しかし、この値が表示された配列で数回繰り返され、それらの 3 番目または XNUMX 番目を抽出したい場合はどうなるでしょうか? すべての値の場合はどうなりますか? 問題は複雑に見えますが、解決策は存在します。
テーブルの 2 つの列に顧客の名前 (Customer Name) が含まれ、もう 3 つの列に顧客が購入した製品 (Product) が含まれているとします。 ある顧客が購入した 4 番目、XNUMX 番目、XNUMX 番目の商品を探してみましょう。
最も簡単な方法は、列の前に補助列を追加することです 顧客名 各名前の繰り返し番号を使用して顧客名を入力します。たとえば、 ジョン・ドウ1, ジョン・ドウ2 関数を使用してナンバリングでトリックを行います COUNTIF (COUNTIF)、顧客名が列 B にある場合:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
その後、通常の機能を使用できます VPR必要な注文を見つける。 例えば：
- もう完成させ、ワークスペースに掲示しましたか? 2位 お客様から注文された商品 ダン・ブラウン:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - もう完成させ、ワークスペースに掲示しましたか? 3位 お客様から注文された商品 ダン・ブラウン:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
実際、次の図に示すように、テキストの代わりに参照値としてセル参照を入力できます。
あなたが探しているだけなら 2-e 繰り返し、より複雑な式を作成することにより、補助列なしでそれを行うことができます:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
この式では：
- $ F $ 2 – 購入者の名前を含むセル (変更されていません。注意してください – リンクは絶対です);
- $B$ - 桁 顧客名;
- Table4 – あなたのテーブル (この場所は通常の範囲にすることもできます);
- $ C16 – テーブルまたは範囲の最後のセル。
この数式は、XNUMX 番目に一致する値のみを検索します。 残りの繰り返しを抽出する必要がある場合は、前のソリューションを使用してください。
すべての一致のリストが必要な場合 – 関数 VPR 一度に XNUMX つの値 (期間) しか返さないため、これはヘルパーではありません。 でもエクセルには機能がある INDEX （INDEX）、このタスクに簡単に対処できます。 そのような式がどのように見えるかは、次の例で学びます。
目的の値のすべての繰り返しを取得します
上記のように VPR スキャンした範囲からすべての重複値を抽出することはできません。 これを行うには、次のようないくつかの Excel 関数で構成される、もう少し複雑な数式が必要です。 INDEX （索引）、 小さい (小) と 行 （ライン）
たとえば、次の数式は、範囲 B2:B2 内のセル F16 の値のすべての繰り返しを検索し、列 C の同じ行から結果を返します。
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
この配列数式を複数の隣接するセル (セルなど) に入力します。 F4:F8下の図に示すように。 セルの数は、検索された値の最大反復回数以上である必要があります。 クリックすることを忘れないでください Ctrl + Shift + Enter配列数式を正しく入力します。
それがどのように機能するかを理解することに興味がある場合は、式の詳細を少し掘り下げてみましょう。
一部1：
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – セル F2 の値を範囲 B2:B16 の各値と比較します。 一致が見つかった場合、式 文字列(C2:C16)-1 対応する行の番号を返します (値 -1 ヘッダー行を含めないようにすることができます)。 一致するものがない場合、関数 IF (IF) は空の文字列を返します。
関数の結果 IF (IF) そのような水平配列があるでしょう: {1,"",3,"",5,"","","","","","",12,"","",""}
一部2：
ROW()-3
СТРОКА()-3
ここで関数 行 (LINE) は追加のカウンターとして機能します。 数式はセル F4:F9 にコピーされるため、数値を減算します。 3 関数の結果から値を取得する 1 セル内 F4 (4 行目、3 を引く) を取得します。 2 セル内 F5 (5 行目、3 を引く) など。
一部3：
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
演算 小さい (小) リターン 能 データ配列の最小値。 私たちの場合、どの位置 (最小から) を返すかは、関数によって決定されます。 行 (LINE) (パート 2 を参照)。 つまり、セルの場合 F4 function SMALL({配列},1) 収益 1位 (最小の) 配列要素、つまり 1. セル用 F5 収益 2位 配列内の最小要素、つまり 3, etc.
一部4：
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
演算 INDEX (INDEX) は単に配列内の特定のセルの値を返します C2：C16. セル用 F4 function 索引($C$2:$C$16) 戻ります いちご、ブロッコリー F5 function 索引($C$2:$C$16) 戻ります お菓子' などがあります。
一部5：
IFERROR()
ЕСЛИОШИБКА()
最後に、式を関数内に入れます IFエラー (IFERROR)、エラー メッセージに満足する可能性が低いため ＃で (#N/A) 数式がコピーされるセルの数が、表示されている範囲内の重複する値の数よりも少ない場合。
既知の行と列による１D検索
Excel で XNUMXD 検索を実行するには、既知の行番号と列番号で値を検索する必要があります。 つまり、特定の行と列の交点でセル値を抽出しています。
それでは、表に戻り、関数を使用して式を書きましょう VPR、XNUMX 月に販売されたレモンの価格に関する情報を検索します。
３Ｄ検索を行う方法はいくつかあります。 オプションを確認して、最適なオプションを選択してください。
VLOOKUP および MATCH 関数
たくさんの関数を使用できます VPR (VLOOKUP) と もっと露出 (MATCH) フィールドの交点で値を見つける 製品名 （文字列）と 月 (列) 問題の配列:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
上記の式は通常の関数です VPR、セル A2 から A9 の値「Lemons」の完全一致を検索します。 しかし、XNUMX 月の売り上げがどの列にあるかわからないため、関数の XNUMX 番目の引数に列番号を設定することはできません。 VPR. 代わりに、関数が使用されます もっと露出この列を定義します。
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
人間の言葉に翻訳すると、この式は次のことを意味します。
- 文字「Mar」を探しています – 引数 参照値 （参照値）;
- A1 から I1 までのセルを調べる – 引数 ルックアップ配列 (ルックアップ配列);
- 完全一致を返す – 引数 マッチタイプ (マッチタイプ)。
使い方 0 XNUMX 番目の引数では、関数を指定します もっと露出 探している値と正確に一致する最初の値を探します。 これは次の値に相当します。 間違った情報 (FALSE) 第 XNUMX 引数 VPR.
これは、XNUMX 次元検索または双方向検索とも呼ばれる、Excel で双方向検索式を作成する方法です。
SUMPRODUCT関数
演算 SUMPRODUCT (SUMPRODUCT) は、選択した配列の積の合計を返します。
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
INDEXおよびMATCH関数
次の記事でこれらの関数について詳しく説明しますので、今のところ、次の式をコピーするだけでかまいません。
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
名前付き範囲と交差演算子
これらの複雑な Excel 数式にあまり興味がない場合は、次の視覚的で記憶に残る方法が気に入るかもしれません。
- テーブルを選択し、タブを開きます 式 (数式) をクリックし、 選択から作成 (選択から作成)。
- チェックボックスをオンにします 上段 （上の行）および 左の列 (左側の列)。 Microsoft Excel は、スプレッドシートの一番上の行と左の列の値から範囲に名前を割り当てます。 数式を作成せずに、これらの名前を直接使用して検索できるようになりました。
- 空のセルに次のように書きます =行名列名、たとえば次のように：
=レモンズマー
…またはその逆:
=マルレモン
行名と列名はスペースで区切る必要があることに注意してください。この場合、交差演算子のように機能します。
名前を入力すると、数式を入力するときと同様に、一致する名前のリストを含むツールチップが Microsoft Excel に表示されます。
- イベント 入力します そして結果を確認
一般に、上記のどの方法を選択しても、XNUMX 次元検索の結果は同じになります。
XNUMX つの数式で複数の VLOOKUP を使用する
メイン テーブルとルックアップ テーブルに共通の列が XNUMX つもなく、通常の関数を使用できない場合があります。 VPR. ただし、関心のある情報が含まれていない別のテーブルがありますが、メイン テーブルとルックアップ テーブルと共通の列があります。
次の例を見てみましょう。 列のあるメインテーブルがあります SKU (新規)、別のテーブルからの対応する価格を含む列を追加します。 さらに、2 つのルックアップ テーブルがあります。 最初のもの (ルックアップ テーブル 1) には、更新された数値が含まれています SKU (新規) と製品名、および 2 番目 (ルックアップ テーブル XNUMX) – 製品名と古い番号 SKU (旧).
XNUMX 番目のルックアップ テーブルの価格をメイン テーブルに追加するには、double と呼ばれるアクションを実行する必要があります。 VPR またはネストされた VPR.
- 関数を書く VPR、テーブル内の製品名を検索します ルックアップ テーブル 1 SKU、目的の値として:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)ここに 新しい_SKU – 名前付き範囲 $A:$B テーブルで ルックアップ テーブル 1, 2 – これは、商品の名前を含む列 B です (上の図を参照)。
- テーブルから価格を挿入する数式を作成する ルックアップ テーブル 2 よく知られている製品名に基づいています。 これを行うには、以前に作成した数式を新しい関数のルックアップ値として貼り付けます VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)ここに 価格 – 名前付き範囲 $ A：$ C テーブルで ルックアップ テーブル 2, 3 価格を含む列 C です。
以下の図は、作成した式によって返された結果を示しています。
VLOOKUP および INDIRECT を使用した異なるテーブルからのデータの動的置換
まず、「異なるテーブルからのデータの動的置換」という表現の意味を明確にして、お互いを正しく理解できるようにしましょう。
同じ形式のデータを含む複数のシートが存在する場合があり、特定のセルに入力された値に応じて、特定のシートから必要な情報を抽出する必要があります。 これは例で説明した方がわかりやすいと思います。
いくつかの地域の同じ製品と同じ形式の売上レポートがあるとします。 特定の地域の売上高を知りたい場合:
そのようなレポートが XNUMX つしかない場合は、非常に単純な式と関数を使用できます。 VPR и IF (IF) 検索するレポートを選択するには:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
どこ：
- $D$2 製品の名前を含むセルです。 ここでは絶対参照を使用して、数式を他のセルにコピーするときにルックアップ値が変更されないように注意してください。
- $ D3 地域名のセルです。 同じ列の他のセルに数式をコピーする予定であるため、絶対列参照と相対行参照を使用しています。
- FL_サルes и CA_営業 – 対応する販売レポートを含むテーブル (または名前付き範囲) の名前。 もちろん、通常のシート名とセル範囲参照を使用できます。たとえば、 「FLシート」!$A$3:$B$10ですが、名前付き範囲の方がはるかに便利です。
ただし、そのようなテーブルが多数ある場合、関数は IF 最善の解決策ではありません。 代わりに、関数を使用できます 間接 (INDIRECT) を使用して、目的の検索範囲を返します。
おそらくご存知のように、関数 間接 は、テキスト文字列で指定されたリンクを返すために使用されます。これはまさに今必要なものです。 したがって、上記の式の式を大胆に関数に置き換えます IF 関数とリンクする 間接. 組み合わせはこちら VPR и 間接 うまく機能します：
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
どこ：
- $D$2 – これは製品名のセルです。絶対リンクのため変更されていません。
- $ D3 地域名の最初の部分を含むセルです。 この例では、これは FL.
- _販売 – すべての名前付き範囲またはテーブルの名前の共通部分。 セル D3 の値と組み合わせると、必要な範囲の完全修飾名が形成されます。 以下は、この機能を初めて使用する方向けの詳細です。 間接.
INDIRECT と VLOOKUP の仕組み
まず、関数の構文を思い出させてください 間接 (間接):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
最初の引数には、セル参照 (A1 または R1C1 スタイル)、範囲名、またはテキスト文字列を指定できます。 XNUMX 番目の引数は、最初の引数に含まれるリンクのスタイルを決定します。
- A1引数が 真のコード (TRUE) または指定なし。
- R1C1、もし FAS E （間違い）。
私たちの場合、リンクにはスタイルがあります A1であるため、XNUMX 番目の引数を省略して最初の引数に集中できます。
それでは、販売レポートに戻りましょう。 ご存じのとおり、各レポートは個別のシートにある個別のテーブルです。 数式が正しく機能するには、テーブル (または範囲) に名前を付ける必要があり、すべての名前に共通部分が含まれている必要があります。 たとえば、次のようにします。 CA_営業, FL_売上, TX_セールス 等々。 ご覧のとおり、すべての名前に「_Sales」が含まれています。
演算 間接 列 D の値とテキスト文字列「_Sales」を結び付けて、 VPR 検索するテーブル。 セル D3 に値「FL」が含まれている場合、数式はテーブルを検索します。 FL_売上、「CA」の場合 - 表内 CA_営業 などがあります。
関数の結果 VPR и 間接 次のようになります。
データが別の Excel ブックにある場合は、名前付き範囲の前にブックの名前を追加する必要があります。次に例を示します。
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
関数が 間接 別のワークブックを参照しているため、そのワークブックが開いている必要があります。 閉じている場合、関数はエラーを報告します。 #REF！ (#SSYL!)。