Üldine valem
=MAX(0,MIN(A1,1))
Kokkuvõte
Protsendiväärtuse piiramiseks nii, et see langeks vahemikku 0% kuni 100%, võite kasutada funktsioone MIN ja MAX põhinevat valemit. Näidatud näites on allpool kopeeritud valem C5-s järgmine:
=MAX(0,MIN(B5,1))
Tulemuseks on see, et negatiivsed väärtused sunnitakse nulli, väärtus üle 1 piirdub väärtusega 1 ja väärtused vahemikus 0 kuni 1 ei muutu.
Märkus: kõik väärtused on vormindatud protsentarvude vorminguga.
Selgitus
Selle probleemi mõistmiseks veenduge, et saate aru, kuidas protsentarvude vormindamine töötab. Lühidalt öeldes on protsendid kümnendarvud: 0,1 on 10%, 0,2 on 20% jne. Protsendina vormindatuna on number 1 100%. Lisateavet numbrivormingute kohta leiate siit.
Selle näite eesmärk on piirata sissetulevaid protsendiväärtusi nii, et need jäävad ülemise ja alumise künnise piiridesse. Negatiivsed ja üle 100% väärtused ei ole lubatud, seega peab lõpptulemuseks olema arv vahemikus null kuni 1 (0–100%) (kaasa arvatud).
Kuigi funktsiooni IF saab kasutada selle probleemi lahendamiseks (vt allpool), on tulemus mõnevõrra pikem ja üleliigne. Selle asemel kasutatakse näidises MIN ja MAX funktsioonide kombinatsiooni väga kompaktses valemis:
=MAX(0,MIN(B5,1))
See on pesitsemise näide - funktsioon MIN pesitseb funktsiooni MAX sisse. Pesitsemine on võtmekonstruktsioon arenenumate valemite jaoks.
Seestpoolt töötades kasutatakse funktsiooni MIN sissetulevate väärtuste piiramiseks ühele järgmiselt:
MIN(B5,1) // get smaller value
Tõlge: tagastage väiksem kui B5 ja 1. Kui väärtus ületab 1, tagastatakse väärtuse B5 väärtus. Näites sisaldab B5 -5% (-0,05), seega MIN tagastab -0,05. See tulemus tagastatakse otse funktsioonile MAX:
=MAX(0,-0.05) // get larger value
Siin näeme, et valem teeb oma töö. Kuna null on suurem (suurem) kui -0,05, tagastab MAX lõpptulemuseks nulli. Algne väärtus visatakse ära.
KUI funktsioon
Nagu eespool mainitud, saab selle probleemi lahendamiseks kasutada ka funktsiooni IF. Selleks vajame kahte eraldi IF-funktsiooni. Üks IF sunnib negatiivsed väärtused nulli:
IF(B5<0,0,B5) // cap at zero
Teine IF piirab suuremaid väärtusi väärtusega 1:
=IF(B5>1,1,B5) // cap at 1
Kui pesitseme esimese IF teise sisse, on meil lõplik valem:
=IF(B5>1,1,IF(B5<0,0,B5))
See on näide pesastatud IF-st. See annab täpselt sama tulemuse kui ülaltoodud valem MIN ja MAX, kuid on veidi keerukam ja üleliigne. Pange tähele, näiteks viide B5-le toimub kolmel korral.
Alumine rida - kui peate valiku tegema väiksemate või suuremate väärtuste põhjal, võivad funktsioonid MIN ja MAX olla nutikad ja elegantsed viisid valemi lihtsana hoidmiseks.
KESKMINE funktsioon
OK, nüüd, kui oleme rääkinud pesitsemisest ja rääkinud MIN-i elegantsusest MAX-iga, peaksin mainima, et funktsiooni MEDIAN abil on võimalik see probleem lahendada ilma pesitsuseta. Valemi üldine versioon näeb välja selline:
=MEDIAN(0,1,A1)
See töötab, kuna funktsioon MEDIAN tagastab arvude rühmas mediaani (keskmise arvu). Kui väärtus on negatiivne, saab null keskmiseks numbriks. Kui arv on suurem kui 1, saab 1 keskmiseks numbriks. Nutikas!
Pange tähele, et MEDIAN tagastab keskmise numbri ainult siis, kui väärtuste koguarv on paaritu. Kui väärtuste arv on paaris, tagastab MEDIAN kahe keskel oleva arvu keskmise. Selle tagajärjel tagastab MEDIAN, kui sihtrakk (A1) on tühi, keskmise 1 ja nulli, mis on protsendina vormindatuna 0,5 või 50%.
