Generische Formel
=MAX(0,MIN(A1,1))
Zusammenfassung
Um einen Prozentwert so zu begrenzen, dass er zwischen 0% und 100% liegt, können Sie eine Formel verwenden, die auf den Funktionen MIN und MAX basiert. In dem gezeigten Beispiel lautet die kopierte Formel in C5:
=MAX(0,MIN(B5,1))
Das Ergebnis ist, dass negative Werte auf Null gesetzt werden, Werte über 1 auf 1 begrenzt werden und Werte zwischen 0 und 1 nicht beeinflusst werden.
Hinweis: Alle Werte sind im Prozentzahlformat formatiert.
Erläuterung
Um dieses Problem zu verstehen, stellen Sie sicher, dass Sie wissen, wie die Formatierung von Prozentzahlen funktioniert. Kurz gesagt, Prozentsätze sind Dezimalwerte: 0,1 ist 10%, 0,2 ist 20% und so weiter. Die als Prozentsatz formatierte Zahl 1 beträgt 100%. Mehr zu Zahlenformaten hier.
Das Ziel dieses Beispiels ist es, eingehende Prozentwerte so zu begrenzen, dass sie innerhalb eines oberen und unteren Schwellenwerts liegen. Negative Werte und Werte über 100% sind nicht zulässig, daher muss das Endergebnis eine Zahl zwischen Null und 1 (einschließlich 0-100%) sein.
Obwohl die IF-Funktion zur Lösung dieses Problems verwendet werden kann (siehe unten), ist das Ergebnis etwas länger und redundant. Stattdessen wird im gezeigten Beispiel eine Kombination der Funktionen MIN und MAX in einer sehr kompakten Formel verwendet:
=MAX(0,MIN(B5,1))
Dies ist ein Beispiel für die Verschachtelung - die MIN-Funktion ist in der MAX-Funktion verschachtelt. Das Verschachteln ist ein Schlüsselbaustein für fortgeschrittenere Formeln.
Die MIN-Funktion wird von innen nach außen verwendet, um eingehende Werte wie folgt auf 1 zu begrenzen:
MIN(B5,1) // get smaller value
Übersetzung: Geben Sie den kleineren Wert von B5 und 1 zurück. Für jeden Wert über 1 wird der Wert in B5 zurückgegeben. Im Beispiel enthält B5 -5% (-0,05), sodass MIN -0,05 zurückgibt. Dieses Ergebnis wird direkt an die MAX-Funktion zurückgegeben:
=MAX(0,-0.05) // get larger value
Hier sehen wir, wie die Formel ihre Arbeit macht. Da Null größer (größer) als -0,05 ist, gibt MAX als Endergebnis Null zurück. Der ursprüngliche Wert wird verworfen.
IF-Funktion
Wie oben erwähnt, kann die IF-Funktion auch verwendet werden, um dieses Problem zu lösen. Dazu benötigen wir zwei separate IF-Funktionen. Eine ZF erzwingt negative Werte auf Null:
IF(B5<0,0,B5) // cap at zero
Die zweite ZF begrenzt größere Werte auf 1:
=IF(B5>1,1,B5) // cap at 1
Wenn wir die erste IF in die zweite verschachteln, haben wir die endgültige Formel:
=IF(B5>1,1,IF(B5<0,0,B5))
Dies ist ein Beispiel für eine verschachtelte IF. Es gibt genau das gleiche Ergebnis wie die obige MIN- und MAX-Formel zurück, ist jedoch etwas komplexer und redundanter. Beachten Sie beispielsweise, dass der Verweis auf B5 dreimal getrennt erfolgt.
Fazit: Wenn Sie eine Auswahl basierend auf kleineren oder größeren Werten treffen müssen, können die Funktionen MIN und MAX eine clevere und elegante Möglichkeit sein, eine Formel einfach zu halten.
MEDIAN-Funktion
OK, jetzt, da wir über das Verschachteln und über die Eleganz von MIN mit MAX gesprochen haben, sollte ich erwähnen, dass es möglich ist, dieses Problem ohne Verschachtelung mit der MEDIAN-Funktion zu lösen. Die generische Version der Formel sieht folgendermaßen aus:
=MEDIAN(0,1,A1)
Dies funktioniert, weil die MEDIAN-Funktion den Median (mittlere Zahl) in einer Gruppe von Zahlen zurückgibt. Wenn ein Wert negativ ist, wird Null zur mittleren Zahl. Wenn eine Zahl größer als 1 ist, wird 1 zur mittleren Zahl. Klug!
Beachten Sie jedoch, dass MEDIAN die mittlere Zahl nur zurückgibt, wenn die Gesamtzahl der Werte ungerade ist. Wenn die Anzahl der Werte gerade ist, gibt MEDIAN den Durchschnitt der beiden Zahlen in der Mitte zurück. Wenn die Zielzelle (A1) leer ist, gibt MEDIAN den Durchschnitt von 1 und Null zurück, der 0,5 oder 50% beträgt, wenn er als Prozentsatz formatiert wird.
