怠け者や本を読む時間がない人は、ビデオをご覧ください。 詳細とニュアンスは以下のテキストにあります。
問題の定式化
したがって、XNUMX つのテーブルがあります。 注文表 и 価格表:
タスクは、後でコストを計算できるように、製品の名前に焦点を当てて、価格表の価格を注文表に自動的に代入することです。
ソリューション
Excel関数セットで、カテゴリの下に 参照と配列 （ルックアップとリファレンス） 機能があります VPR （VLOOKUP）.この関数は、指定された表 (価格表) の左端の列を上から下に移動して、指定された値 (この例では「Apples」という単語) を探し、それが見つかったら、隣接するセルの内容を表示します。 (23 ルーブル) .図式的に、この関数の操作を表すことができます。
関数をさらに使いやすくするために、一度に 3 つのことを行います。価格表のセル範囲に独自の名前を付けます。 これを行うには、「ヘッダー」(G19: HXNUMX) を除く価格表のすべてのセルを選択し、メニューから選択します。 挿入 – 名前 – 割り当て (挿入 — 名前 — 定義) またはプレス CTRL + F3 のような任意の名前 (スペースなし) を入力します。 価格…今後、この名前を使用して価格表にリンクできます。
次に、関数を使用します VPR…入力するセル（D3）を選択し、タブを開く 数式 – 関数の挿入 (数式 — 関数の挿入)… カテゴリ内 参照と配列 (検索と参照) 関数を見つける VPR （VLOOKUP） を押し OK… 関数の引数を入力するためのウィンドウが表示されます。
それらを順番に埋めます：
- 希望値 （参照値） – 関数が価格表の左端の列で検索する製品の名前。 この例では、セル B3 の「Apples」という単語です。
- 表 （テーブル・アレイ） –希望する値uXNUMXbuXNUMXbaが取得されるテーブル、つまり価格表。 参考までに、先ほど挙げた独自の名前「Price」を使用します。 名前を付けていない場合は、テーブルを選択するだけでかまいませんが、 ボタンを押すことを忘れないでください F4ドル記号でリンクを固定するそうしないと、数式を列 D3:D30 の残りのセルにコピーするときに下にスライドするためです。
- 列番号 (列インデックス番号) – 価格値を取得する価格表の列のシリアル番号 (文字ではありません!)。 名前が記載された価格リストの最初の列には 1 の番号が付けられているため、2 の番号が付けられた列の価格が必要です。
- 間隔ルックアップ (範囲検索) – このフィールドに入力できる値は XNUMX つだけです: FALSE または TRUE:
- 値が入力されている場合 0 or 横たわっている （NS）、実際には、これは検索のみが許可されることを意味します 完全一致つまり、関数が価格表の注文テーブルで指定された非標準アイテムを見つけられない場合 (たとえば、「ココナッツ」が入力された場合)、#N/A (データなし) エラーが生成されます。
- 値が入力されている場合 1 or TRUE （TRUE）、これは、正確なものではなく検索を許可することを意味しますが、 近似一致、つまり「coconut」の場合、関数は「coconut」にできるだけ近い名前の製品を見つけようとし、この名前の価格を返します。 ほとんどの場合、このようなおおよその置換は、実際にあった間違った製品の値を置換することで、ユーザーを欺くことができます! したがって、ほとんどの実際のビジネス上の問題では、近似検索は許可しないのが最善です。 例外は、テキストではなく数値を検索する場合です。たとえば、ステップ ディスカウントを計算する場合です。
すべての！ 押すことは残っています OK 入力した関数を列全体にコピーします。
# N / A エラーとその抑制
演算 VPR （VLOOKUP） #N/A エラーを返す (#N/A) もし：
- 完全一致検索有効 (引数 インターバル ビュー = 0) で、目的の名前が含まれていない 表.
- 大まかな検索が含まれています (インターバル ビュー = 1 表検索が行われている は、名前の昇順でソートされていません。
- 名前の必要な値が由来するセルの形式 (たとえば、この場合は B3) と、テーブルの最初の列 (F3: F19) のセルの形式が異なります (たとえば、数値とテキスト）。 このケースは、テキスト名の代わりに数値コード (口座番号、識別子、日付など) を使用する場合に特に一般的です。 この場合、関数を使用できます Ч и TEXT データ形式を変換します。 次のようになります。
=VLOOKUP(TEXT(B3),価格,0)
これについて詳しくは、こちらをご覧ください。
- コードにスペースまたは非表示の非表示文字 (改行など) が含まれているため、関数は必要な値を見つけることができません。 この場合、テキスト関数を使用できます TRIM （トリム） и PRINT（掃除） それらを削除するには：
=VLOOKUP(TRIMSPACES(CLEAN(B3)),価格,0)
=VLOOKUP(TRIM(CLEAN(B3));価格;0)
エラーメッセージを抑制するには ＃N / A (#N/A) 関数が完全に一致するものを見つけられない場合は、関数を使用できます IFエラー （IFERROR）…たとえば、この構造は VLOOKUP によって生成されたエラーをインターセプトし、それらをゼロに置き換えます。
= IFERROR (VLOOKUP (B3, 価格, 2, 0), 0)
= IFERROR (VLOOKUP (B3; 価格; 2; 0); 0)
PS
365 つの値ではなく、セット全体を一度に抽出する必要がある場合 (複数の異なる値がある場合)、配列数式でシャーマニゼーションを行う必要があります。 または、Office XNUMX の新しい XLOOKUP 機能を使用します。
- VLOOKUP 関数の改良版 (VLOOKUP 2)。
- VLOOKUP 関数を使用して、ステップ (範囲) 割引をすばやく計算します。
- INDEX 関数と MATCH 関数を使用して「左 VLOOKUP」を作成する方法
- VLOOKUP 関数を使用して、フォームにリストのデータを入力する方法
- 最初の値ではなく、テーブルからすべての値を一度に引き出す方法
- PLEX アドオンの VLOOKUP2 および VLOOKUP3 関数