☰

この料金体系の式を Excel で作成するにはどうすればいいですか?

tag-icon • tag-icon microsoft-excel worksheet-function
この料金体系の式を Excel で作成するにはどうすればいいですか?

値を持つセルがありますA1 = 3,500,000。

この 350 万ドルにかかる手数料を次の式に従って計算します。

最初の 100 万ドルに対しては 7% プラス。2 番目の 100 万ドルに対しては 5% プラス。3 番目の 100 万ドルに対しては 3% プラス。それ以降は 2% です。

理想的には、このすべてを 1 つのセルで実行したいので、これを行うためのテーブル全体またはシートを使用するのではなく、1 つのセルに 1 つの数式を使用します。

ありがとう！

答え1

確かに、1 つのセル内の 1 つの数式でこの問題を解決することも可能ですが、数式を簡単に理解して簡単に変更したい場合は、表の方がきれいになります。

--

シングルセルソリューション

単一セル ソリューションの場合、値が A1 にある場合、次の数式で段階的な料金が計算されます。

= A1 * 0.07 + MAX(A1-1000000,0) * -0.02 + MAX(A1-2000000,0) * -0.02 + MAX(A1-3000000,0) * -0.01

合計値に 0.07 を掛け、各ティアごとに新しいティアと以前のティアの料金の差額を差し引きます (これを行う方法は多数ありますが、ここでは 1 つの例を示しました)。

--

シンプルなテーブルソリューション

単純なテーブル ソリューションの場合は、次の操作を実行できます。

ここに画像の説明を入力してください

各層の値を計算する式:

E2: =MIN(C2,$A$1)

E3: =MIN(C3,$A$1)-SUM($E$2:E2)

E4: =MIN(C4,$A$1)-SUM($E$2:E3)

E5: =MAX($A$1-C4,0)

合計料金を計算する式:

D6: =SUMPRODUCT(D2:D5,E2:E5)

この設定は動的です。しきい値や手数料を変更しても、計算式は機能します。

将来的に追加のティアが必要になる可能性がある場合は、XML テーブルが推奨されます。4 つのティアのみを使用するが、それらのティアのしきい値と料金が変更される場合は、上記のシンプルなテーブルで十分です。

答え2

これには、適切に構造化された参照テーブルを使用できます (非常に類似した税金計算の問題にも使用できます)。

あなたのコミッションテーブル

  • 列1 = 売上の基本金額
  • 列2 = 各層の割合
  • 列3 = そのティアの前に獲得したコミッションの額

下の表に
G1: 0
G2: =(E3-E2)*F2+G2
必要事項を記入してください

そして、データを入力するとA1

=(A1-VLOOKUP(A1,commissionTbl,1))*VLOOKUP(A1,commissionTbl,2)+VLOOKUP(A1,commissionTbl,3)

ここに画像の説明を入力してください

コミッション構造が変更された場合、数式を編集するよりもはるかに簡単にテーブルを編集できることに注意してください。

答え3

3つの可能性を示したいと思います。

ここに画像の説明を入力してください

  1. ティア 1、ティア 2、ティア 3、ティア 4 の値を個別に計算し、最後に合計を算出します。

このためには、セル内の数式を確認してください。

E2: =IF(D2<=1000000, D2*0.07, 1000000*0.07)

F2: =IF(D2>1000000, IF(D2<=2000000, (D21-1000000)*0.05, 1000000*0.05), 0)

G2: =IF(D2>2000000, IF(D2<=3000000, (D2-2000000)*0.03, 1000000*0.03), 0)

H2: =IF(D2>3000000, (D2-3000000)*0.02, 0)

そして最後の1つは私2: =SUM(E2:H2)

2. もう 1 つは、 の単一式ソリューションですI5。

セル I5:

=IF(D2<=1000000, D2*0.07, IF(D2<=2000000, 1000000*0.07 + (D2-1000000)*0.05, IF(D2<=3000000, 1000000*0.07 + 1000000*0.05 + (D2-2000000)*0.03, 1000000*0.07 + 1000000*0.05 + 1000000*0.03 + (D2-3000000)*0.02)))

3. 最後は、I7LET 関数が使用されている Excel の上位バージョン用の数式です。

セル I7:

=LET( amount, D2,
        tier1, MIN(amount, 1000000),
        tier2, MAX(0, MIN(amount, 2000000) - 1000000),
        tier3, MAX(0, MIN(amount, 3000000) - 2000000),
        tier4, MAX(0, amount - 3000000),
        tier1*0.07 + tier2*0.05 + tier3*0.03 + tier4*0.02)

注意 必要に応じてセル参照を調整します。

関連情報