Formuła ogólna
=MAX(0,MIN(A1,1))
Podsumowanie
Aby ograniczyć wartość procentową, tak aby mieściła się w zakresie od 0% do 100%, możesz użyć formuły opartej na funkcjach MIN i MAX. W pokazanym przykładzie formuła w C5, skopiowana w dół, to:
=MAX(0,MIN(B5,1))
W rezultacie wartości ujemne są wymuszane na zero, wartości powyżej 1 są ograniczone do 1, a wartości od 0 do 1 pozostają niezmienione.
Uwaga: wszystkie wartości sformatowane przy użyciu formatu liczb procentowych.
Wyjaśnienie
Aby zrozumieć ten problem, upewnij się, że rozumiesz, jak działa formatowanie liczb procentowych. Krótko mówiąc, procenty to wartości dziesiętne: 0,1 to 10%, 0,2 to 20% i tak dalej. Liczba 1 sformatowana jako wartość procentowa to 100%. Więcej o formatach liczb tutaj.
Celem tego przykładu jest ograniczenie przychodzących wartości procentowych, tak aby mieściły się w górnym i dolnym progu. Wartości ujemne i wartości powyżej 100% są niedozwolone, więc wynik końcowy musi być liczbą od zera do 1 (0–100%) włącznie.
Chociaż do rozwiązania tego problemu można użyć funkcji JEŻELI (patrz poniżej), wynik będzie nieco dłuższy i zbędny. Zamiast tego pokazany przykład wykorzystuje kombinację funkcji MIN i MAX w bardzo zwartej formule:
=MAX(0,MIN(B5,1))
To jest przykład zagnieżdżenia - funkcja MIN jest zagnieżdżona wewnątrz funkcji MAX. Zagnieżdżanie jest kluczowym elementem konstrukcyjnym bardziej zaawansowanych formuł.
Działając od wewnątrz, funkcja MIN służy do ograniczania przychodzących wartości do 1 w następujący sposób:
MIN(B5,1) // get smaller value
Tłumaczenie: zwróć mniejsze z B5 i 1. Dla każdej wartości powyżej 1 zwracana jest wartość z B5. W tym przykładzie B5 zawiera -5% (-0,05), więc MIN zwraca -0,05. Ten wynik jest zwracany bezpośrednio do funkcji MAX:
=MAX(0,-0.05) // get larger value
Tutaj widzimy, że formuła działa. Ponieważ zero jest większe (większe) niż -0,05, MAX zwraca zero jako wynik końcowy. Oryginalna wartość jest odrzucana.
Funkcja JEŻELI
Jak wspomniano powyżej, do rozwiązania tego problemu można również użyć funkcji JEŻELI. Aby to zrobić, potrzebujemy dwóch oddzielnych funkcji JEŻELI. Jedna JEŻELI wymusza ujemne wartości zera:
IF(B5<0,0,B5) // cap at zero
Drugi IF ogranicza większe wartości do 1:
=IF(B5>1,1,B5) // cap at 1
Kiedy zagnieżdżamy pierwszy IF wewnątrz drugiego, otrzymujemy ostateczną formułę:
=IF(B5>1,1,IF(B5<0,0,B5))
To jest przykład zagnieżdżonego IF. Zwraca dokładnie ten sam wynik, co powyższa formuła MIN i MAX, ale jest nieco bardziej złożony i zbędny. Zauważ na przykład, że odniesienie do B5 występuje trzy razy.
Podsumowując - kiedy musisz dokonać wyboru na podstawie mniejszych lub większych wartości, funkcje MIN i MAX mogą być sprytnym i eleganckim sposobem na utrzymanie prostego wzoru.
Funkcja MEDIANA
OK, teraz, gdy rozmawialiśmy o zagnieżdżaniu i rozmawialiśmy o elegancji MIN z MAX, powinienem wspomnieć, że możliwe jest rozwiązanie tego problemu bez żadnego zagnieżdżania za pomocą funkcji MEDIAN. Ogólna wersja formuły wygląda następująco:
=MEDIAN(0,1,A1)
To działa, ponieważ funkcja MEDIANA zwraca medianę (środkową liczbę) w grupie liczb. Gdy wartość jest ujemna, zero staje się liczbą środkową. Gdy liczba jest większa niż 1, 1 staje się liczbą środkową. Sprytny!
Należy jednak pamiętać, że MEDIAN zwraca środkową liczbę tylko wtedy, gdy całkowita liczba wartości jest nieparzysta. Jeśli liczba wartości jest parzysta, MEDIANA zwraca średnią z dwóch liczb w środku. W konsekwencji, jeśli komórka docelowa (A1) jest pusta, MEDIAN zwróci średnią z 1 i zera, która wynosi 0,5 lub 50% po sformatowaniu jako procent.
