画像: iStock/AndreyPopov
コミッション制の仕事をしたことがあれば、計算がどれほど難しいかご存知でしょう。定額コミッションは、合計金額に関係なく同じ利率で支払われます。例えば、利率が2%の場合、合計金額が1,000ドルでも1,000,000ドルでも、2%の報酬となります。段階制コミッションは（通常）売上増加につながります。なぜなら、パフォーマンスが良ければ高いほど、利率も高くなるからです。
この記事では、Microsoft Excel で、合計金額に応じてレートが変動するシンプルな階層型システムを構築します。マイルストーンごとにレートが上昇します。ここでは「コミッション」という用語を使用していますが、ボーナスにも同じ構造を使用できます。階層型システムに取り組む際の最適な出発点であり、アセトアミノフェンは必要ありません。
参照:すべてのユーザーが習得すべき Excel のヒント 60 選(TechRepublic)
Windows 10 64ビットシステムでMicrosoft 365を使用していますが、以前のバージョンでもご利用いただけます。ご参考までに、デモ用の.xlsxファイルをダウンロードしていただけます。このソリューションはWeb版と互換性があります。
最も単純な階層
最もシンプルな階層構造は、会計上は真の階層構造とは言えないかもしれませんが、ここではそう扱います。例えば、50,000ドルまでの合計金額に対して2%の税率が適用されるとします。50,000ドルを超える売上高には2.5%、80,000ドルを超える売上高には3%が適用されます。ここで重要なのは、基準額に達した時点で、すべての合計金額に対して税率が適用されるということです。そのため、これを真の階層構造システムと見なさない人もいるかもしれません。
参照：Windows 10：音声認識とディクテーションの音声コマンド一覧（無料PDF）（TechRepublic）
ビジネスルールは、コミッションを支払う企業によって適用されます。すべての企業に適用される単一のルールは存在しません。難しいことではありませんが、設定するにはルールを理解しておく必要があります。そのため、マイルストーンやパーセンテージの値が変更された場合に簡単に更新できるよう、動的に作業することをお勧めします。
組織の階層構造を表す独自の表現を思いつくのが大変だと感じている方もご安心ください。まずは言葉で書き出すことで、全員が同じ認識を持つことができます。この簡単なデモンストレーションでは、階層構造のルールを以下のように適用します。
- 月間合計が 50,000 ドル未満の場合は 0.02 が掛けられ、50,000 ドルが最初のマイルストーンとなります。
- 月間合計が 80,000 ドル未満の場合は 0.025 が掛けられ、80,000 ドルが 2 番目のマイルストーンとなります。
- 月間合計が 80,000 ドルを超える場合は 0.03 倍になります。階層が 3 つしかないため、3 番目のマイルストーンはありません。
このように分解してみると、表現がずっと分かりやすくなります。それでは始めましょう。
階層テーブルの作成方法
まず最初にアドバイスしたいのは、マイルストーン値やパーセンテージ値を数式に入力しないことです。代わりに、入力セルを作成して参照してください。そうすれば、マイルストーン値やパーセンテージ値が上昇（あるいは残念ながら下降）した場合でも、シートを簡単に調整できます。
参照：2021年のテクノロジー予算：CXO向けガイド（無料PDF）（TechRepublic）
図Aに示すように、前のセクションで示したレートを反映したシンプルな階層テーブルを作成しました。（図Aのシートは意図的にシンプルにしています。）会計書式をF1:F3に、数値書式をG1:G3に適用します。コミッションテーブルは別のシートに作成することもできますが、ここではソリューションと元のデータを一緒に確認できるように、すべてを1つのシートにまとめています。次のステップでは、データと階層テーブルを結合する式を追加して、全従業員の月間コミッションを返します。階層テーブルが少しオフセットされているのはそのためです。
図A
手数料を計算する式の作成方法
段階制コミッションのソリューションを探すと、行き詰まって諦めたくなるかもしれません。しかし、私たちのソリューションは、他の多くのソリューションよりもはるかにシンプルです。これは私が優れているからではなく、むしろ単純な問題に取り組んでいるからです。簡単に言うと、月間合計と、段階制テーブルのF列にある最も近いマイルストーン値を比較し、対応するレートをG列に返す式が必要です。VLOOKUP()関数を使えば、この計算が可能です。そして、そのレートに月間合計を掛け合わせるだけです。
まず、新しいテーブルが必要です。図Bは、データも式も入っていない、対応するTableオブジェクトを示しています。このTableオブジェクトをコピーし、データを削除します。次の式をG7（コミッションテーブル）に入力し、残りのデータ範囲（G7:H15）にコピーします。
=VLOOKUP(C7,$F$1:$G$3,2)*C7
図 Cに示すように、コミッションは魔法のようにコミッション テーブルに入力されます。
図B
図C
式を詳しく見ていきましょう。まず、VLOOKUP() 関数は、売上テーブル（C7）の月間合計金額 52,008 ドルを比較します。この関数の性質上、完全一致を探す必要がなく、元の値よりも大きい値が見つかるまでリストを評価し続けます。この場合、その値は 80,000 ドル - 80000 ドル > 52008 ドルです。引数 2 は、F 列のマイルストーンから 1 列分だけ移動し、その値 0.025 を返すように関数に指示しています。式の最後の部分では、0.025 に 52,008 ドルを掛けて 1,300.20 ドルを返します。
さて、これを式として分解してみましょう。
=VLOOKUP(52008,$F$1:$G$3,2)*C7=
=VLOOKUP(52008,{0,0.02;50000,0.025;80000,0.03},2)*C7=
=0.025*52008=
=1300.20
参照: Excel でシート ビューを使用してより柔軟な共同作業を行う方法(TechRepublic)
Excelテーブルで手数料を変更する方法
マイルストーンとパーセンテージは入力セルなので、図Dに示すように、Tierテーブルの値を変更するだけで、すべてのコミッションを素早く更新できます。レートが少し上がりましたね！1つまたはすべての値を変更するだけで、支払うべきコミッションをすぐに更新できます。これらの値を変更する際は、F列のマイルストーンの値が昇順になっている必要があることに注意してください。また、両方のテーブルオブジェクトで新しい行が自動的に挿入されます。
図D
乞うご期待
この解決策は、想像以上にシンプルです。複雑で入り組んだ表現を想像していた方は、きっと驚かれることでしょう。ただし、この簡単な解決策は、段階的コミッションシステムというテーマのほんの表面に触れているに過ぎません。次の記事では、もう少し難しい内容にしていきます。
