Multiplizieren verschiedener Faktoren basierend auf dem Wert einer Zelle in Excel

Question

Ich versuche, eine Formel zu erstellen, mit der sich Folgendes berechnen lässt:

=< 10,000 x 1.00

10,0001-12,500 x 1.25

12,501 - 15,000 x 1.50

Beispiele:

Wenn die Zahl 8.520 ist (8.520 x 1,00) = 8.520

Wenn die Zahl 11.560 wäre (10.000 x 1,00) + (1.560 x 1,25) = 11.950

Wenn die Zahl 13.500 ist (10.000 x 1,00) + (2.500 x 1,25) + (1.000 x 1,50) = 14.625

Answer 1

Dies kann mit einem if erfolgen. Die Art und Weise, ein verschachteltes if zu erstellen, besteht darin, das zu tun, was bei verschiedenen Werten geschieht. Angenommen, die Datenspalte beginnt bei A2, dann

1. A2<10000. Ergebnis ist a2 + 0 oder A2
2. 10000 < A2 <12501 Ergebnis ist a2 +(.25 * (A2-10000) )
3. 12500 < A2 <15000 Ergebnis ist a2 +(.25* (a2-10000)) + (.25 *( A2-12500))

Um dies in eine einzelne Formel umzuwandeln, verwenden Sie einfach für jeden Fall ein if. Der Ablauf ist =if(test,true,false), wobei, wenn der Test wahr ist, der wahre Wert erhalten wird, andernfalls der falsche Wert. Die erste Lösung verwendet 3 ifs, ein falscher Test ergibt eine Null, ein wahrer ergibt die Antwort

• =WENN(A2<10001,A2,0)+WENN(UND(A2>10000,A2<12501),A2+(0,25*(A2-10000))+WENN(UND(A2>12500,A2<15001),A2+(0,25*(A2-10000))+(0,25*(A2-12500))))

Dies kann vereinfacht werden, indem man etwas Algebra anwendet, um

• =A2+WENN(A2>10000,(0,25*(A2-10000)),0)+WENN(A2>12500,(0,25*(A2-12500)))

Beide Formen können je nach Bedarf geändert werden, um mit Zahlen über 15.000 umzugehen.

Answer 2

Okay, es war etwas komplizierter als zunächst gedacht.

Erstellen Sie eine Tabelle wie die folgende:

Verwenden Sie dann die folgende Array-Formel:

=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))

Da es sich um eine Matrixformel handelt, muss sie beim Verlassen des Bearbeitungsmodus mit Strg-Umschalt-Eingabe statt mit der Eingabetaste bestätigt werden. Wenn dies richtig gemacht wird, wird Excel {}die Formel umschließen.

---

Indem wir auf der Formel von @fixer aufbauen und diese dynamisieren, erstellen wir eine Tabelle wie diese:

Wie Sie sehen, ist der Faktor jetzt die Erhöhung gegenüber dem vorherigen und nicht der tatsächliche Faktor.

Dann können wir diese Formel verwenden:

=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)))

Die Formel ist jetzt dynamisch, da sich der Verweis auf den Datensatz mit der Größe der Tabelle ändert. Sie INDEX(D:D,MATCH(1E+99,D:D))findet die letzte Zelle in der Spalte und legt diese als Umfang des Datensatzes fest.