Excel でセルの値に基づいて異なる係数を乗算する

Question

次の値を計算する式を作成しようとしています。

=< 10,000 x 1.00

10,0001-12,500 x 1.25

12,501 - 15,000 x 1.50

例:

数字が8,520の場合（8,520 x 1.00）= 8,520

数字が11,560の場合（10,000 x 1.00）+（1,560 x 1.25）= 11,950

数字が13,500の場合（10,000 x 1.00）+（2,500 x 1.25）+（1,000 x 1.50）= 14,625

Answer 1

これはif文で行うことができます。ネストされたif文を構築する方法は、異なる値に対して何が起こるかを実行することです。A2から始まるデータの列を想定して、

1. A2<10000。結果はa2 + 0またはA2
2. 10000 < A2 <12501 結果はa2 +(.25 * (A2-10000) )
3. 12500 < A2 <15000 結果はa2 +(.25* (a2-10000)) + (.25 *( A2-12500))

これを単一の式に変換するには、各ケースにifを使用します。フローは=if(test,true,false)で、テストがtrueの場合はtrue値を取得し、そうでない場合はfalse値を取得します。最初のソリューションでは3つのifを使用し、falseテストの結果はゼロ、trueの結果は答えになります。

• =IF(A2<10001,A2,0)+IF(AND(A2>10000,A2<12501),A2+(0.25*(A2-10000))+IF(AND(A2>12500,A2<15001),A2+(0.25*(A2-10000))+(0.25*(A2-12500))))

これは代数を使って簡略化することができます

• =A2+IF(A2>10000,(0.25*(A2-10000)),0)+IF(A2>12500,(0.25*(A2-12500)))

どちらの形式も、15,000を超える数字をどのように処理するかに応じて変更できます。

Answer 2

そうですね、最初に考えていたよりも少し複雑でした。

次のようなテーブルを作成します。

次に、次の配列数式を使用します。

=SUM(IF(A2>$D$2:$D$4,IF(A2<$E$2:$E$4,A2-$D$2:$D$4,$E$2:$E$4-$D$2:$D$4)*$F$2:$F$4,0))

配列数式なので、編集モードを終了するときは Enter ではなく Ctrl + Shift + Enter で確定する必要があります。正しく実行すると、Excel によって{}数式が囲まれます。

---

@fixer の式を基にして式を動的にすると、次のようなテーブルが作成されます。

ご覧のとおり、係数は実際の係数ではなく、前回からの増加になっています。

次に、次の式を使用します。

=A2+SUMPRODUCT((A2>$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*MOD(A2,$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*$E$3:INDEX(E:E,MATCH(1E+99,D:D)))

数式は動的になり、テーブルが拡大または縮小すると、データセットへの参照も拡大または縮小します。INDEX(D:D,MATCH(1E+99,D:D))列の最後のセルが検索され、それがデータセットの範囲として設定されます。