Splošna formula
=MAX(0,MIN(A1,1))
Povzetek
Če želite omejiti odstotno vrednost, tako da ta pade med 0% in 100%, lahko uporabite formulo, ki temelji na funkcijah MIN in MAX. V prikazanem primeru je formula v C5, kopirana navzdol,:
=MAX(0,MIN(B5,1))
Rezultat je, da so negativne vrednosti prisiljene na nič, vrednosti nad 1 so omejene na 1 in vrednosti med 0 in 1 ne vplivajo.
Opomba: vse vrednosti so formatirane v obliki odstotka števila.
Pojasnilo
Če želite razumeti to težavo, se prepričajte, da razumete, kako deluje oblikovanje števila odstotkov. Na kratko, odstotki so decimalne vrednosti: 0,1 je 10%, 0,2 je 20% itd. Število 1, oblikovano v odstotkih, je 100%. Več o oblikah številk tukaj.
Cilj tega primera je omejiti dohodne vrednosti v odstotkih, tako da bodo znotraj zgornjega in spodnjega praga. Negativne vrednosti in vrednosti nad 100% niso dovoljene, zato mora biti končni rezultat število med nič in 1 (0-100%).
Čeprav je za reševanje te težave mogoče uporabiti funkcijo IF (glej spodaj), bo rezultat nekoliko daljši in odveč. Namesto tega se v prikazanem primeru uporablja kombinacija funkcij MIN in MAX v zelo kompaktni formuli:
=MAX(0,MIN(B5,1))
To je primer gnezdenja - funkcija MIN je ugnezdena znotraj funkcije MAX. Gnezdenje je ključni gradnik naprednejših formul.
Funkcija MIN se uporablja od znotraj navzven, da vhodne vrednosti omeji na 1, kot je ta:
MIN(B5,1) // get smaller value
Prevod: vrne manjše od B5 in 1. Za katero koli vrednost nad 1 se vrne vrednost v B5. V primeru B5 vsebuje -5% (-0,05), tako da MIN vrne -0,05. Ta rezultat se vrne neposredno v funkcijo MAX:
=MAX(0,-0.05) // get larger value
Tu vidimo, da formula deluje. Ker je nič večja (večja) od -0,05, MAX vrne nič kot končni rezultat. Prvotna vrednost se zavrže.
IF funkcija
Kot smo že omenili, lahko funkcijo IF uporabimo tudi za rešitev te težave. Za to potrebujemo dve ločeni funkciji IF. En IF prisili negativne vrednosti na nič:
IF(B5<0,0,B5) // cap at zero
Drugi IF omejuje večje vrednosti na 1:
=IF(B5>1,1,B5) // cap at 1
Ko prvi IF ugnezdimo znotraj drugega, imamo končno formulo:
=IF(B5>1,1,IF(B5<0,0,B5))
To je primer ugnezdenega IF. Vrne popolnoma enak rezultat kot zgornji formuli MIN in MAX, vendar je nekoliko bolj zapleten in odvečen. Upoštevajte, na primer, sklic na B5 se zgodi trikrat.
Bottom line - kadar se morate odločiti na podlagi manjših ali večjih vrednosti, sta funkciji MIN in MAX lahko pameten in eleganten način, da formula ostane enostavna.
MEDIAN Funkcija
V redu, zdaj, ko smo se že pogovarjali o gnezdenju in se z MAX-om pogovarjali o eleganci MIN, bi moral omeniti, da je to težavo mogoče rešiti brez kakršnega koli gnezdenja s funkcijo MEDIAN. Splošna različica formule je videti tako:
=MEDIAN(0,1,A1)
To deluje, ker funkcija MEDIAN vrne mediano (srednjo številko) v skupini števil. Ko je vrednost negativna, nič postane srednje število. Ko je število večje od 1, postane 1 srednje število. Pametno!
Opomba MEDIAN pa vrne srednjo številko le, če je skupno število vrednosti neparno. Če je število vrednosti sodo, vrne MEDIAN povprečje obeh števil na sredini. Če je ciljna celica (A1) prazna, bo MEDIAN vrnil povprečje 1 in nič, kar je 0,5 ali 50%, če je formatirano kot odstotek.
