Yleinen kaava
=MAX(0,MIN(A1,1))
Yhteenveto
Voit rajoittaa prosentuaalista arvoa siten, että se putoaa välillä 0% - 100%, käyttämällä MIN- ja MAX-funktioihin perustuvaa kaavaa. Esitetyssä esimerkissä C5: n kaava, kopioitu alaspäin, on:
=MAX(0,MIN(B5,1))
Tuloksena on, että negatiiviset arvot pakotetaan nollaan, arvot yli 1 rajataan arvoon 1 ja arvot 0: n ja 1 välillä eivät vaikuta.
Huomaa: kaikki arvot on muotoiltu prosentuaalisena lukumuotona.
Selitys
Tämän ongelman ymmärtämiseksi varmista, että ymmärrät kuinka prosenttilukujen muotoilu toimii. Lyhyesti sanottuna prosentit ovat desimaaliarvoja: 0,1 on 10%, 0,2 on 20% ja niin edelleen. Numero 1 on muotoiltu prosentteina 100%. Lisää numeromuotoista täältä.
Tämän esimerkin tavoitteena on rajoittaa saapuvia prosenttiosuuksia siten, että ne kuuluvat ylä- ja alarajaan. Negatiivisia ja yli 100%: n arvoja ei sallita, joten lopputuloksen on oltava luku nollan ja 1: n välillä (0-100%), mukaan lukien.
Vaikka IF-toimintoa voidaan käyttää tämän ongelman ratkaisemiseen (katso alla), tulos on jonkin verran pidempi ja tarpeeton. Sen sijaan esitetty esimerkki käyttää MIN- ja MAX-funktioiden yhdistelmää erittäin pienessä kaavassa:
=MAX(0,MIN(B5,1))
Tämä on esimerkki pesinnästä - MIN-toiminto on sisäkkäin MAX-toiminnon sisällä. Pesiminen on avainrakenne edistyneemmille kaavoille.
Työskentelemällä sisältä ulospäin, MIN-toimintoa käytetään rajoittamaan saapuvat arvot yhdelle näin:
MIN(B5,1) // get smaller value
Käännös: palauta pienempi B5: stä ja 1: stä. Jos arvo on yli 1, B5: n arvo palautetaan. Esimerkissä B5 sisältää -5% (-0,05), joten MIN palauttaa -0,05. Tämä tulos palautetaan suoraan MAX-toimintoon:
=MAX(0,-0.05) // get larger value
Tässä näemme, että kaava tekee työnsä. Koska nolla on suurempi (suurempi) kuin -0,05, MAX palauttaa nollan lopputuloksena. Alkuperäinen arvo hylätään.
JOS-toiminto
Kuten yllä mainittiin, IF-toimintoa voidaan käyttää myös tämän ongelman ratkaisemiseen. Tätä varten tarvitsemme kaksi erillistä IF-toimintoa. Yksi IF pakottaa negatiiviset arvot nollaan:
IF(B5<0,0,B5) // cap at zero
Toinen IF rajoittaa suurempia arvoja arvoon 1:
=IF(B5>1,1,B5) // cap at 1
Kun pesimme ensimmäisen IF: n toisen sisälle, meillä on lopullinen kaava:
=IF(B5>1,1,IF(B5<0,0,B5))
Tämä on esimerkki sisäkkäisestä IF: stä. Se palauttaa täsmälleen saman tuloksen kuin yllä oleva MIN- ja MAX-kaava, mutta on hieman monimutkaisempi ja tarpeeton. Huomaa esimerkiksi, että viittaus B5: een tapahtuu kolme erillistä kertaa.
Tärkeintä - kun sinun on tehtävä valinta pienempien tai suurempien arvojen perusteella, MIN- ja MAX-toiminnot voivat olla fiksu ja tyylikäs tapa pitää kaava yksinkertaisena.
MEDIAN-toiminto
OK, nyt kun olemme puhuneet pesinnästä ja puhuneet MINin eleganssista MAX: lla, minun on mainittava, että tämä ongelma on mahdollista ratkaista ilman pesimistä MEDIAN-toiminnolla. Kaavan yleinen versio näyttää tältä:
=MEDIAN(0,1,A1)
Tämä toimii, koska MEDIAN-funktio palauttaa mediaanin (keskiluvun) numeroryhmässä. Kun arvo on negatiivinen, nollasta tulee keskiluku. Kun luku on suurempi kuin 1, yhdestä tulee keskiluku. Älykäs!
Huomaa kuitenkin, että MEDIAN palauttaa keskimmäisen luvun vasta, kun arvojen kokonaismäärä on pariton. Jos arvojen määrä on parillinen, MEDIAN palauttaa keskellä olevien kahden luvun keskiarvon. Tämän seurauksena, jos kohdesolu (A1) on tyhjä, MEDIAN palauttaa keskiarvon 1 ja nolla, joka on 0,5 tai 50% alustettuna prosentteina.
