オフィスソフト

「Excel」での歩合計算--売上高によって変わる歩合率にVLOOKUP()関数で対応

歩合の計算は複雑だ。売り上げが一定の金額を超えると歩合率が上がるというケースを例に、「Excel」のVLOOKUP()関数を使用した解決策を紹介する。

 歩合給の仕事をしたことがある人なら、その計算がどれほど難しいかご存知のはずだ。一律の歩合なら、合計金額に関係なく同じ率で支払われる。たとえば、歩合率が2%の場合、合計金額が1000ドルであっても、100万ドルであっても、支払われるのはその2%だ。段階式の歩合では、成績が高ければ高いほど率が上がるため、(通常は)販売の促進につながる。

 本記事では、合計に基づいて歩合率が変わるシンプルな段階的システムを「Microsoft Excel」で構築していく。一定の基準を超えるごとに率が上がるシステムだ。ここでは歩合という用語を使っているが、ボーナスにも同じ構造を使用できる。これは、初めて段階的システムに取り組むときの例題として最適だ。頭痛がするほど難解ではないだろう。

 筆者は「Windows 10」の64ビット版システムで「Microsoft 365」を使用しているが、古いバージョンを使っても構わない。デモ用の.xlsxファイルをダウンロードして利用すると便利だ。この解決策はブラウザー版にも対応している。

最も単純な階層

 極めて単純な段階的階層は、会計用語で言うところの真の段階的システムとは大きく異なるかもしれないが、本稿ではそのようなものとして扱う。たとえば、合計が5万ドル以下の場合は、2%の歩合が支払われるとしよう。売り上げが5万ドルを超えると2.5%となり、8万ドル超の場合は3%となる。ここで注意したいのは、特定の基準を満たすと、その歩合率がすべての合計金額に適用されることだ。そのため、一部の人はこれを真の段階的システムとみなさない可能性がある。

 ビジネスルールは、歩合を支払う企業によって適用される。1つのルールがすべての企業の歩合に当てはまるということはない。難しいことではないが、歩合を設定するためのルールを知っておく必要がある。そのため、本記事で紹介するテクニックを柔軟に活用して、基準や歩合率が変わっても簡単に更新できるようにしてほしい。

 自社の階層システムを表す一連の独自の数式を考え出さなければならない、と思ってストレスを感じている人も、心配は無用だ。まずはそれらを書き出して、皆に理解してもらおう。この簡単なデモで適用する段階的システムのルールは、以下のとおりだ。

  • 5万ドル以下の月間合計に0.02を掛ける。5万ドルが1つ目の基準。
  • 8万ドル以下の月間合計に0.025を掛ける。8万ドルが2つ目の基準。
  • 8万ドルを超える月間合計に0.03を掛ける。3段階しかないため、3つ目の基準はない。

 このように分解すると、数式がはるかに分かりやすくなる。それでは始めよう。

階層のテーブルを作成する方法

 最初のアドバイスは、基準と歩合率の値を数式に入力しない、ということだ。代わりに、入力セルを作成して参照する。これなら、基準と歩合率が上がったときに(または、残念ながら下がってしまったときに)、シートを簡単に調整することができる。

 図Aに示すように、前のセクションの歩合率を反映する単純な階層テーブルを作成した(図Aのシートは意図的に単純なものにしている)。F1:F3の表示形式に「会計」を適用し、G1:G3に「数値」を適用する。歩合のテーブルは別のシートに作成してもいいが、ここでは、解決策と元のデータを一緒に見ることができるように、すべてを1つのシートで示している。次のステップでは、データと階層テーブルを組み合わせる数式を追加して、全担当者の毎月の歩合を返すようにする。階層テーブルの位置が少しずれているのはそのためだ。

図A

基準と歩合率の値には入力セルを使用する。
基準と歩合率の値には入力セルを使用する。

歩合計算の数式を作成する方法

 段階式の歩合の解決策を検索した人は、少し挫折して諦めたくなるかもしれない。本記事で紹介する方法は、検索で見つかるほとんどの解決策よりもはるかに簡単だ。それは、筆者が聡明だからではない。むしろ、ここで扱っているのはシンプルな問題だ。簡単に言うと、月間の合計金額と、階層テーブルのF列の最も近い基準値を比較して、G列の対応する率を返す数式があればいい。これにはVLOOKUP()関数を使う。後は、その率と月間合計を掛け算するだけだ。

「オフィスソフト」で読まれている記事

TechRepublic Japanで人気の記事

編集部オススメ

トレンドまるわかり![PR]

財務・経理
人事・労務
マーケ・営業
購買・調達
生産・製造
データ分析
コミュニケーション
通信・通話
文書・コンテンツ
PC・モバイル
PC
スマートフォン
タブレット
ウェアラブル
オフィスソフト
OCR
RPA
PCソフト
周辺機器
新興技術
ITインフラ
クラウドサービス
OS・ミドルウェア
開発
データベース
運用
セキュリティ

ホワイトペーパーランキング

  1. 5分でわかる、レポート作成の心得!成果至上主義のせっかちな上司も納得のレポートとは
  2. ノートPCは従来ながらの選び方ではダメ!新しい働き方にも対応する失敗しない選び方を徹底解説
  3. 問題だらけの現場指導を効率化!「人によって教え方が違う」を解消するためのマニュアル整備
  4. 緊急事態宣言解除後の利用率は低下 調査結果に見る「テレワーク」定着を阻む課題とその対応策
  5. たしか、あのデータは、こっちのアプリにあったはず…--業務改善のためのアプリ導入がストレスの原因に?

Follow TechRepublic Japan

このサイトでは、利用状況の把握や広告配信などのために、Cookieなどを使用してアクセスデータを取得・利用しています。 これ以降ページを遷移した場合、Cookieなどの設定や使用に同意したことになります。
Cookieなどの設定や使用の詳細、オプトアウトについては詳細をご覧ください。
[ 閉じる ]