ExcelでのVLOOKUP、パート2データベースなしでのVLOOKUPの使用
最近の記事では、Excelという関数を紹介しました。 VLOOKUP また、データベースからローカルワークシートのセルに情報を取得する方法について説明しました。その記事では、VLOOKUPには2つの用途があり、そのうちの1つだけがデータベースの照会を処理していると述べました。この記事は、VLOOKUPシリーズの第2回目と最後の回で、これとは別の、あまり知られていないVLOOKUP関数の使い方を調べます。.
まだ読んでいない場合は、最初のVLOOKUP記事を読んでください - この記事では、その記事で説明されている概念の多くが既に読者に知られていると想定します。.
データベースを扱う場合、VLOOKUPには、データベース内でどのデータレコードを見つけたいかを識別するための「一意の識別子」（製品コードや顧客IDなど）が渡されます。この一意の識別子 しなければならない データベースに存在しない場合、VLOOKUPはエラーを返します。この記事では、識別子がデータベースにまったく存在する必要がない場合のVLOOKUPの使い方を調べます。 VLOOKUPが、探しているデータを返すために「ほぼ十分で十分」というアプローチを採用できるかのようです。特定の状況では、これは まさに 私たちの必要なもの.
この記事では、実際の例、つまり一連の売上高に基づいて生成される手数料を計算する方法で説明します。この問題に対する唯一の合理的な解決策がVLOOKUPを使用することになるまで、非常に単純なシナリオから始めて、それを徐々に複雑にしていきます。ある架空の会社の初期のシナリオは、次のようになっています。営業担当者が特定の年に3万ドル以上の売上を生み出した場合、その売上に対して稼ぐ手数料は30％です。そうでなければ彼らの手数料はわずか20％です。これまでのところ、これはかなり単純なワークシートです。
このワークシートを使用するには、営業担当者はセルB1に売上高を入力し、セルB2の数式は受け取る権利のある正しい手数料率を計算し、セルB3で営業担当者の未払い手数料を計算します。 B1とB2の単純な乗算です.
セルB2には、このワークシートの唯一の興味深い部分、つまりどのコミッションレートを使用するかを決定するための式が含まれています。 以下 しきい値が30,000ドル、またはそれ 上に しきい値。この式は、以下のExcel関数を利用しています。 IF. IFに慣れていない読者のために、それはこのように動作します：
IF（条件、trueの場合は値、falseの場合は値）
どこ 調子 どちらかに評価される式です。 本当の または 偽. 上記の例では、 調子 式です B1
ご覧のとおり、売上高2万ドルを使用すると、セルB2の手数料率は20％になります。 40,000ドルの値を入力すると、異なる手数料率が得られます。
だから私たちのスプレッドシートは働いています.
もっと複雑にしましょう。 2番目のしきい値を導入しましょう。営業担当者が40,000ドルを超える収入を得た場合、そのコミッション率は40％に上がります。
実社会で理解するのは十分に簡単ですが、セルB2では式がより複雑になっています。式をよく見ると、元のIF関数の3番目のパラメータ（ falseの場合は値）は今やそれ自体でIF関数全体です。これは 入れ子関数 （関数内の関数）それはExcelでも完全に有効です（それでもうまくいきます！）が、読み、理解するのは難しいです。.
これがどのように、そしてなぜ機能するのかということには触れません。また、入れ子関数の微妙な違いについても調べません。これはVLOOKUPのチュートリアルであり、一般的なExcelのチュートリアルではありません.
とにかく、それは悪化します！ 50,000ドルを超える収入がある場合は50％のコミッションを受け取る権利があり、60,000ドルを超える収入がある場合は60％のコミッションを受け取る権利があると判断した場合はどうなりますか。?
今すぐセルB2の数式は、正しいものの、事実上読めなくなっています。関数が4レベルの深さにネストされている式を書く必要はありません。もっと簡単な方法があるはずです?
確かにあります。救助へのVLOOKUP!
ワークシートを少し再設計しましょう。私たちはすべて同じ数値を保ちますが、新しい方法でそれを整理します。 表形式 方法：
少し時間をかけて、新しいことを確認してください。 料金表 上記の一連のしきい値とまったく同じように機能します。.
概念的には、VLOOKUPを使用して営業担当者の売上高（B1から）を料金表で調べ、対応するコミッション率を返します。販売員が実際に次のような販売を作成した可能性があることに注意してください。 ではない 単価表の5つの値のうちの1つ（$ 0、$ 30,000、$ 40,000、$ 50,000、または$ 60,000）。彼らは34,988ドルの売り上げを上げたのかもしれません。 $ 34,988ではないことに注意することが重要です。 ではない レート表に表示されます。とにかくVLOOKUPが私たちの問題を解決できるかどうか見てみましょう…
セルB2（数式を配置する場所）を選択してから、VLOOKUP関数を挿入します。 式 タブ：
の 関数の引数 VLOOKUPのボックスが表示されます。引数（パラメータ）を一つずつ記入していきます。 参照値, この場合、セルB1からの売上合計です。カーソルを 参照値 フィールドをクリックし、セルB1を1回クリックします。
次に、このデータをルックアップするテーブルをVLOOKUPに指定する必要があります。この例では、もちろんレートテーブルです。カーソルを テーブル・アレイ フィールドをクリックし、レート表全体を強調表示します。 - 見出しを除く：
次に、テーブルのどの列に式から返される情報が含まれているかを指定する必要があります。この場合、テーブルの2列目にあるコミッションレートが欲しいので、次のように入力します。 2 に Col_index_num フィールド：
最後に値を入力します。 Range_lookup フィールド.
重要：VLOOKUPを使用する2つの方法を区別するのはこのフィールドの使用です。データベースでVLOOKUPを使用するには、この最後のパラメータ, Range_lookup, 常にに設定する必要があります FALSE, しかし、このVLOOKUPの他の使用法では、空白のままにするか、またはの値を入力する必要があります。 TRUE. VLOOKUPを使用するときは、この最後のパラメータに正しい選択をすることが重要です。.
明確にするために、次の値を入力します。 本当の の中に Range_lookup フィールド。これはデフォルト値なので、空白のままにしても構いません。
すべてのパラメーターを完成させました。我々は今クリックします OK ボタンをクリックすると、ExcelによってVLOOKUP式が作成されます。
いくつかの異なる売上合計金額を試してみると、数式が機能していることがわかります。.
結論
「データベース」バージョンのVLOOKUPでは、 Range_lookup パラメータは FALSE, 最初のパラメータに渡された値（参照値） しなければならない データベースに存在すること。言い換えれば、我々は完全に一致するものを探しています.
しかし、このVLOOKUPの他の用途では、必ずしも完全に一致するものを探しているわけではありません。この場合、「十分に近いことで十分です」。しかし、「十分に近い」とはどういう意味ですか？例を使用してみましょう。総売上34,988ドルのコミッションレートを検索すると、VLOOKUPの式から30％の値が返されます。これが正しい答えです。なぜ30％を含む表の行を選択したのですか？この場合、実際には、「ほぼ十分」とはどういう意味ですか？正確にしましょう。
いつ Range_lookup に設定されています TRUE （または省略）、VLOOKUPは列1を調べて一致します。 より大きくない最大値 の 参照値 パラメータ.
このシステムが機能するためには注意が必要です。, テーブルは1列目から昇順にソートする必要があります。!
VLOOKUPを使って練習したい場合は、この記事に示すサンプルファイルをここからダウンロードできます。.
