Bendroji formulė
=MAX(0,MIN(A1,1))
Santrauka
Norėdami apriboti procentinę vertę, kad ji sumažėtų nuo 0% iki 100%, galite naudoti formulę, pagrįstą MIN ir MAX funkcijomis. Parodytame pavyzdyje C5 formulė, nukopijuota žemiau, yra:
=MAX(0,MIN(B5,1))
Rezultatas yra tas, kad neigiamos vertės yra priverstos nuliui, vertės, viršijančios 1, yra ribojamos ties 1 ir vertės nuo 0 iki 1 neturi įtakos.
Pastaba: visos vertės suformatuotos procentiniu skaičiaus formatu.
Paaiškinimas
Norėdami suprasti šią problemą, įsitikinkite, kad suprantate, kaip veikia procentinis skaičių formatavimas. Trumpai tariant, procentai yra dešimtainės vertės: 0,1 yra 10%, 0,2 yra 20% ir kt. Skaičius 1, suformatuotas procentais, yra 100%. Daugiau apie skaičių formatus rasite čia.
Šio pavyzdžio tikslas yra apriboti gaunamų procentų vertes, kad jos nepatektų į viršutinę ir apatinę ribas. Neigiamos vertės ir didesnės nei 100% vertės neleidžiamos, todėl galutinis rezultatas turi būti skaičius nuo nulio iki 1 (0–100%) imtinai.
Nors IF funkcija gali būti naudojama šiai problemai išspręsti (žr. Toliau), rezultatas bus šiek tiek ilgesnis ir nereikalingas. Vietoj to, pateiktame pavyzdyje naudojama MIN ir MAX funkcijų kombinacija labai kompaktiška formule:
=MAX(0,MIN(B5,1))
Tai yra lizdo pavyzdys - funkcija MIN yra įdėta į MAX funkciją. Lizdai yra pagrindinis sudėtingesnių formulių komponentas.
Dirbant iš vidaus, MIN funkcija naudojama gaunamoms vertėms apriboti iki 1:
MIN(B5,1) // get smaller value
Vertimas: grąžinkite mažesnįjį iš B5 ir 1. Jei reikšmė viršija 1, grąžinama B5 vertė. Pavyzdyje B5 yra -5% (-0,05), taigi MIN grąžina -0,05. Šis rezultatas grąžinamas tiesiai į MAX funkciją:
=MAX(0,-0.05) // get larger value
Čia matome, kad formulė atlieka savo darbą. Kadangi nulis yra didesnis (didesnis) nei -0,05, MAX kaip galutinį rezultatą pateikia nulį. Pradinė vertė atmetama.
IF funkcija
Kaip minėta pirmiau, IF funkcija taip pat gali būti naudojama šiai problemai išspręsti. Norėdami tai padaryti, mums reikia dviejų atskirų IF funkcijų. Vienas IF priverčia neigiamas vertes nuliui:
IF(B5<0,0,B5) // cap at zero
Antrasis IF nustato didesnes vertes ties 1:
=IF(B5>1,1,B5) // cap at 1
Kai mes įdėsime pirmąjį IF antrojo viduje, mes turime galutinę formulę:
=IF(B5>1,1,IF(B5<0,0,B5))
Tai yra įdėto IF pavyzdys. Jis pateikia lygiai tą patį rezultatą kaip aukščiau pateiktos MIN ir MAX formulės, tačiau yra šiek tiek sudėtingesnis ir nereikalingas. Atkreipkite dėmesį, pavyzdžiui, nuoroda į B5 pateikiama tris skirtingus kartus.
Esmė - kai reikia pasirinkti pagal mažesnes ar didesnes vertes, funkcijos MIN ir MAX gali būti protingas ir elegantiškas būdas išlaikyti formulę paprastą.
MEDIAN funkcija
Gerai, dabar, kai jau kalbėjome apie lizdą ir kalbėjome apie MIN su MAX eleganciją, turėčiau paminėti, kad šią problemą įmanoma išspręsti be jokio lizdo naudojant „MEDIAN“ funkciją. Bendroji formulės versija atrodo taip:
=MEDIAN(0,1,A1)
Tai veikia, nes funkcija MEDIAN grąžina vidurį (vidurinį skaičių) skaičių grupėje. Kai vertė yra neigiama, nulis tampa viduriniu skaičiumi. Kai skaičius yra didesnis nei 1, 1 tampa viduriniu skaičiumi. Protingas!
Tačiau atkreipkite dėmesį, kad MEDIAN grąžina vidurinį skaičių tik tada, kai bendras reikšmių skaičius yra nelyginis. Jei reikšmių skaičius yra lyginis, MEDIAN pateikia dviejų viduryje esančių skaičių vidurkį. Dėl to, jei tikslinė ląstelė (A1) tuščia, MEDIAN pateiks 1 ir nulio vidurkį, kuris yra 0,5 arba 50%, jei suformatuojamas procentais.
