私たちは最近、最も便利な Excel 関数の XNUMX つに記事を捧げました。 VPR また、必要な情報をデータベースからワークシート セルに抽出するために使用する方法を示しました。 また、関数には XNUMX つの使用例があることも述べました。 VPR そのうちの XNUMX つだけがデータベース クエリを処理します。 この記事では、関数を使用するあまり知られていない別の方法を学習します。 VPR Excelで。
まだこれを行っていない場合は、関数に関する最後の記事を必ずお読みください。 VPR以下のすべての情報は、最初の記事で説明した原則に既に精通していることを前提としているためです。
データベース、関数を操作する場合 VPR 一意の識別子が渡されます。これは、検索する情報を識別するために使用されます (製品コードや顧客識別番号など)。 この一意のコードはデータベースに存在する必要があります。 VPR エラーを報告します。 この記事では、この関数の使用方法について説明します。 VPRIDがデータベースにまったく存在しない場合。 あたかも関数 VPR おおよそのモードに切り替え、何かを見つけたいときに提供するデータを選択します。 特定の状況では、これがまさに必要です。
人生からの例。 タスクを設定します
この記事を実際の例で説明しましょう。幅広い販売指標に基づいてコミッションを計算します。 非常に単純なオプションから始めて、問題に対する唯一の合理的な解決策が関数を使用することになるまで、徐々に複雑にします。 VPR. 架空のタスクの最初のシナリオは次のとおりです。営業担当者が 30000 年間に $30 を超える売り上げを上げた場合、彼の手数料は 20% です。 それ以外の場合、手数料はわずか XNUMX% です。 それを表の形にしましょう：
売り手はセル B1 に販売データを入力し、セル B2 の式によって、売り手が期待できる正しい手数料率が決定されます。 次に、結果のレートがセル B3 で使用され、売り手が受け取る必要がある合計コミッションが計算されます (単純にセル B1 と B2 を乗算します)。
表の最も興味深い部分は、セル B2 に含まれています。これは、手数料率を決定するための式です。 この数式には、次の Excel 関数が含まれています。 IF （もしも）。 この機能に慣れていない読者のために、その仕組みを説明します。
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
調子 次のいずれかの値を取る関数の引数です 真のコード (TRUE)、または 間違った情報 （間違い）。 上記の例では、式 B1
B1 が B5 より小さいというのは本当ですか?
または、次のように別の言い方をすることもできます。
年間の総売上高が基準値を下回っているというのは本当ですか?
この質問に答えるなら はい (TRUE) の場合、関数は戻ります true の場合の値 (TRUE の場合の値)。 この場合、これはセル B6 の値になります。つまり、総売上がしきい値を下回った場合の手数料率です。 質問に答えると NO (FALSE) を返します false の場合の値 (FALSE の場合の値)。 この場合、これはセル B7 の値です。つまり、総売上がしきい値を超えた場合の手数料率です。
ご覧のとおり、合計売上高が $20000 の場合、セル B2 の手数料率は 20% になります。 $40000 の値を入力すると、手数料率は 30% 変化します。
これが私たちのテーブルの仕組みです。
タスクを複雑にします
物事をもう少し難しくしましょう。 別のしきい値を設定してみましょう: 売り手の収益が $40000 を超える場合、手数料率は 40% に増加します。
すべてが単純明快に見えますが、セル B2 の式は明らかに複雑になっています。 式をよく見ると、関数の XNUMX 番目の引数が IF （IF）は別の本格的な機能になりました IF （もしも）。 この構造は、関数の入れ子と呼ばれます。 Excel は喜んでこれらの構造を許可し、機能しますが、読みにくく、理解するのがはるかに困難です。
技術的な詳細 (なぜ、どのように機能するのか) については掘り下げません。また、ネストされた関数を記述する際のニュアンスについては触れません。 結局のところ、これは機能に特化した記事です VPR、Excel の完全なガイドではありません。
いずれにせよ、式はさらに複雑になります。 50 ドル以上の売上を上げた売り手に対して 50000% の手数料率の別のオプションを導入するとどうなるでしょうか。 また、誰かが 60000 ドル以上を販売した場合、60% のコミッションを支払うのでしょうか?
これで、セル B2 の数式は、エラーがなくても、まったく読み取れなくなりました。 プロジェクトで 4 レベルのネスティングを持つ式を使用したいという方は少ないと思います。 もっと簡単な方法があるに違いない!?
そして、そのような方法があります！ 関数は私たちを助けます VPR.
VLOOKUP関数を適用して問題を解決します
テーブルのデザインを少し変えてみましょう。 すべて同じフィールドとデータを保持しますが、それらを新しい、よりコンパクトな方法で配置します。
少し時間を取って、新しいテーブルを確認してください 料金表 前のしきい値テーブルと同じデータが含まれます。
主なアイデアは、関数を使用することです VPR 表に従って希望の関税率を決定する 料金表 販売数量によります。 売り手は、表の 34988 つのしきい値のいずれにも等しくない金額で商品を販売できることに注意してください。 たとえば、彼は XNUMX ドルで売ることができますが、そのような金額はありません。 関数がどのように機能するか見てみましょう VPR そんな状況に対応できます。
VLOOKUP 関数の挿入
セル B2 (数式を挿入する場所) を選択し、検索します。 VLOOKUP (VLOOKUP) Excel 関数ライブラリ: 式 (数式) > 関数ライブラリ (関数ライブラリ) > ルックアップとリファレンス (参照と配列)。
ダイアログボックスが表示されます 関数の引数 (関数の引数)。 から始めて、引数の値をXNUMXつずつ入力します 参照値 （参照値）。 この例では、セル B1 の合計売上高です。 フィールドにカーソルを置きます 参照値 (Lookup_value) をクリックし、セル B1 を選択します。
次に、関数を指定する必要があります VPRデータを探す場所。 この例では、これはテーブルです 料金表. フィールドにカーソルを置きます テーブル・アレイ (テーブル) をクリックし、テーブル全体を選択します 料金表ヘッダー以外。
次に、式を使用してデータを抽出する列を指定する必要があります。 表の XNUMX 列目にある手数料率に関心があります。 したがって、引数については Col_index_num (Column_number) 値 2 を入力します。
そして最後に、最後の引数を紹介します – 範囲検索 (Interval_lookup)。
重要： 関数を適用する XNUMX つの方法の違いを生むのは、この引数の使用です。 VPR. データベースを操作する場合、引数 範囲検索 (range_lookup) には常に値が必要です 間違った情報 (FALSE) 完全一致を検索します。 機能の使用について VPR、このフィールドを空白のままにするか、値を入力する必要があります 真のコード （真実）。 このオプションを正しく選択することは非常に重要です。
分かりやすくご紹介します 真のコード (TRUE) フィールドで 範囲検索 (Interval_lookup)。 ただし、フィールドを空白のままにしてもエラーにはなりません。 真のコード はそのデフォルト値です:
すべてのパラメーターを入力しました。 今、私たちは押します OK、Excel は関数を使用して数式を作成します VPR.
総売上高に対していくつかの異なる値を試してみると、式が正しく機能することを確認できます。
まとめ
機能が VPR データベースで動作、引数 範囲検索 (range_lookup) を受け入れる必要があります 間違った情報 （間違い）。 そして、次のように入力された値 参照値 (Lookup_value) はデータベースに存在する必要があります。 つまり、完全一致を探しています。
この記事で取り上げた例では、完全に一致させる必要はありません。 これは、関数が VPR 目的の結果を返すには、近似モードに切り替える必要があります。
例： 売上高が 34988 ドルの販売員のコミッション計算に使用するレートを決定したいと考えています。 関数 VPR は 30% という値を返しますが、これはまったく正しい値です。 しかし、式が 30% や 20% ではなく、正確に 40% を含む行を選択したのはなぜでしょうか? 近似検索 とはどういう意味ですか? はっきりさせましょう。
議論が 範囲検索 (interval_lookup) に値があります 真のコード (TRUE) または省略、関数 VPR 最初の列を反復処理し、ルックアップ値を超えない最大の値を選択します。
重要なポイント： このスキームを機能させるには、テーブルの最初の列を昇順で並べ替える必要があります。