を使用してExcelで手数料を計算する方法要点：IF、VLOOKUP、またはXLOOKUP関数を使用して、基本的な階層化計画とレート表のためにExcelで手数料を計算する方法を学びます。＜8377＞＜9816＞スキルレベル：中級＜8377＞＜8526＞ビデオチュートリアル＜2366＞＜900＞＜6605＞Youtubeで見る
Excelで手数料を計算するのは非常に難しい作業です。 これは、レート表の各階層の手数料を計算するために複数のIFステートメントを使用しようとした場合に特に当てはまります。
この記事では、このプロセスをはるかに簡単にするためにVLOOKUP関数を使用する方法を説明します。 秘密は、最も近い一致を見つけるために、vlookupの最後の引数をTRUEに設定することです。最後の引数をTRUEに設定する方法の詳細については、VLOOKUPを使用して最も近い一致を見つける方法に関する私の記事をチェックしてください。
更新: この投稿は、新しいXLOOKUP関数で手数料を計算するための手順を含むように更新されました。 ここにXLOOKUPの記事&ビデオがあります。
ダウンロードファイル
フォローするファイルをダウンロードします。
手数料の計算-IF対VLOOKUP対XLOOKUP。xlsx(31.5KB)
Vlookupで手数料を計算する
単純な手数料プランでは、通常、各レベルの売上高でのペイアウト率 営業担当者がより多くの販売を行うように、彼/彼女のペイアウト率は、一般的に増加します。
ペイアウト率は、フラットな金額、収益の割合、クォータの割合などである可能性があります。 VLOOKUPの仕事は、レートテーブルで担当者の販売金額を見つけ、対応するペイアウト率を返すことです。
この例では、コミッションプランは次のようになります:
- Rep販売$0-$50,000て得5%
- Rep販売$51,000-$100,000て得7%
- Rep販売$100,001-$150,000て得10%
- Rep販売してドルを超え150,001いを獲得15%
の職まで一定の売上高ます。 彼/彼女は達成された販売のレベルに応じて一定のペイアウト率を獲得します。
vlookup式を使用して、特定の販売金額（ルックアップ値）のペイアウト率を計算できます。 これを機能させるには、vlookupの最後の引数をTRUEに設定する必要があります。
最後の引数をTRUEに設定すると、vlookupは、ルックアップ量以下のルックアップ値に最も近い一致を検索します。 これは基本的に、2つの数値（層）の範囲の間の値を見つけることを可能にします。これが、VLOOKUPの最後の引数の名前がrange_lookupである理由です。 この引数がTRUEの場合、VLOOKUPは層最小列の値の範囲を調べて、完全一致またはルックアップ値より小さい値を検索します。
レートテーブルの設定方法
このレートテーブルをExcelに置くときは、ルックアップ範囲のティア最小値をリストするだけです。 再びVLOOKUPは、ルックアップ値以下の「最も近い一致」を検索します。 ルックアップ値よりも大きい値が見つかった場合は、前の行を返します。
この手数料レートテーブルの例では、ルックアップ範囲の最初の行はゼロである必要があります。 これは、営業担当者が潜在的にsales0の売上を持つ可能性があり、ルックアップ値がゼロになるためです。 ルックアップ値（売上金額）が負の数であった場合、vlookupはエラーを返します。
これを知って、すべての可能なルックアップ値のレートテーブルを設定することが重要です。
売上金額が参照範囲の最後の行より大きい場合、vlookupは最後の行を返します。 たとえば、担当者がsales175,000の販売を行った場合、vlookupは15％を返します。
手数料を計算してドルの値を返す
ペイアウトは、パーセンテージではなくドルの値として返すこともできます。 この設定では、支払いは定額になります。
これは、売上金額が階層内にあるかどうかにかかわらず、支払いが同じになることを意味します。 以下の例では、販売金額がsales55,000または9 95,000の場合、支払いはpayout1,000になります。
支払いはスライドスケールではありません。 これは、各層のための定額料金です。
スライドスケール計算を探している場合は、SUMPRODUCTを使用した階層レート構造による手数料の計算に関する私の記事を参照してください。
手数料の計算XLOOKUP
この計算には新しいXLOOKUP関数を使用することもできます。 最も近い一致を見つけるときは、VLOOKUPと非常によく似ています。
Xlookupの利点
XLOOKUPには主に二つの違いと利点があります:
- XLOOKUPでは、ルックアップ配列を指定し、配列を別々の範囲として返します。 これは上の画像のB4：B7とD4：D7です。 列BとDの間に行を挿入または削除でき、数式は引き続き機能するため、これはVLOOKUPよりも利点です。
VLOOKUPのテーブル配列内に列を追加/削除するときは、列インデックス番号を手動で変更するか、式を使用して動的に計算する必要があります。 - XLOOKUPは、一致モードに完全一致または次の小さい/大きい項目を使用するときにデータをソートする必要はありません。 XLOOKUPは実際に次の小さい/大きい項目を探し、その行/列の結果を返します。
VLOOKUPでは、最後の引数がTRUEのときにデータをソートする必要があります。 通常、これらのタイプのテーブルのデータをソートする必要がありますが、テーブルの下部に新しいレート階層を配置するだけで、XLOOKUPが機能することを知って
XLOOKUPの欠点
XLOOKUPの主な欠点は互換性です。 あなたとあなたのファイルのすべてのユーザーの両方がXLOOKUPを持つMicrosoft/Office365のバージョンにある必要があります。 これは、古いバージョンのExcelでは下位互換性がないか、使用できません。
したがって、VLOOKUPはこのシナリオではうまく機能し、ネストされたIF式よりもはるかに簡単に記述できます。
ネストされたIF式を避ける
手数料を計算する一般的なアプローチは、IF文を使用することです。 このようなレート表を使用すると、複数のIF文を記述する必要があります。 基本的には、テーブル内の各層（行）に対して1つのIF文を記述する必要があります。
その後、すべてのIF文を1つの長くて醜い式に結合する必要があります。 これらはネストされたIF文と呼ばれます。
ネストされたIfステートメントは、読みやすく理解するのが難しく、Excelの計算が遅くなる可能性があるため、可能な限りネストされたIfを避けるようにしています。 ブック内に何千ものネストされたIF式がある場合、計算時間が遅くなる可能性があります。
IF式は維持するのがはるかに困難です。 テーブルから行が追加/削除されるときに変更が必要です。 VLOOKUPおよびXLOOKUPでは、この種の保守は必要ありません。
IFを使用する場合は、レートテーブルへの各セル参照を絶対参照にするようにしてください（キーボードのF4）。 それ以外の場合は、数式をコピーするときに誤った結果が表示されます。
vlookupを使用すると、ネストされたIF式を使用するよりもはるかに簡単できれいです。 この例でわかるように、vlookupを使用すると、1つの式を使用して、特定の販売金額の手数料ペイアウト率を計算できます。
その他のリソース
- VLOOKUPを使用して最も近い一致を見つける方法–最後の引数がTRUEに等しい
- SUMPRODUCT
- Vlookupで累積階層手数料を計算する方法Starbucks
- VLOOKUP&Match–A Dynamic Duo
- Vlookup
- VLOOKUP&Match-A Dynamic Duo
- VLOOKUP&MATCH-A Dynamic Duo
- VLOOKUP&MATCH-A Dynamic Duo
- If関数の説明：excelでIF文の式を書く方法
最も近い一致でVlookupを使用するにはどうすればよいですか？
最も近い一致手法を使用したVLOOKUPは、税括弧の計算、価格の一致などにも使用できます。 この手法を使用して何を計算しますか？