Všeobecný vzorec
=MAX(0,MIN(A1,1))
Zhrnutie
Ak chcete obmedziť percentuálnu hodnotu na hodnotu medzi 0% a 100%, môžete použiť vzorec založený na funkciách MIN a MAX. V zobrazenom príklade je vzorec v C5, skopírovaný nadol,:
=MAX(0,MIN(B5,1))
Výsledkom je, že záporné hodnoty sú vynútené na nulu, hodnoty nad 1 sú obmedzené na 1 a hodnoty medzi 0 a 1 nie sú ovplyvnené.
Poznámka: všetky hodnoty formátované vo formáte percentuálneho čísla.
Vysvetlenie
Aby ste pochopili tento problém, musíte pochopiť, ako funguje formátovanie čísel v percentách. Stručne povedané, percentá sú desatinné hodnoty: 0,1 je 10%, 0,2 je 20% atď. Číslo 1 po formátovaní v percentách je 100%. Viac informácií o formátoch čísel nájdete tu.
Cieľom tohto príkladu je obmedziť prichádzajúce percentuálne hodnoty tak, aby spadali do hornej a dolnej hranice. Záporné hodnoty a hodnoty nad 100% nie sú povolené, takže konečným výsledkom musí byť číslo od 0 do 1 (0 - 100%) vrátane.
Aj keď na vyriešenie tohto problému možno použiť funkciu IF (pozri nižšie), výsledok bude o niečo dlhší a nadbytočný. Namiesto toho uvedený príklad používa kombináciu funkcií MIN a MAX vo veľmi kompaktnom vzorci:
=MAX(0,MIN(B5,1))
Toto je príklad vnorenia - funkcia MIN je vnorená do funkcie MAX. Vnorenie je kľúčovým stavebným prvkom pre pokročilejšie vzorce.
Pri práci zvnútra smerom von sa funkcia MIN používa na obmedzenie prichádzajúcich hodnôt na hodnotu 1 takto:
MIN(B5,1) // get smaller value
Preklad: vráťte menšiu z B5 a 1. Pri akejkoľvek hodnote nad 1 sa vráti hodnota v B5. V príklade obsahuje B5 -5% (-0,05), takže MIN vráti -0,05. Tento výsledok sa vráti priamo do funkcie MAX:
=MAX(0,-0.05) // get larger value
Tu vidíme, že vzorec robí svoju prácu. Pretože nula je väčšia (väčšia) ako -0,05, funkcia MAX vráti nulu ako konečný výsledok. Pôvodná hodnota je zahodená.
Funkcia IF
Ako bolo uvedené vyššie, na vyriešenie tohto problému je možné použiť aj funkciu IF. Potrebujeme na to dve samostatné funkcie IF. Jeden IF vynúti záporné hodnoty na nulu:
IF(B5<0,0,B5) // cap at zero
Druhý IF obmedzuje väčšie hodnoty na 1:
=IF(B5>1,1,B5) // cap at 1
Keď vnoríme prvý IF do druhého, máme konečný vzorec:
=IF(B5>1,1,IF(B5<0,0,B5))
Toto je príklad vnoreného IF. Vráti presne ten istý výsledok ako vyššie uvedený vzorec MIN a MAX, ale je o niečo zložitejší a nadbytočný. Všimnite si napríklad, že odkaz na B5 sa vyskytuje trikrát.
Záver - ak potrebujete urobiť výber na základe menších alebo väčších hodnôt, funkcie MIN a MAX môžu byť šikovným a elegantným spôsobom, ako udržať vzorec jednoduchý.
Funkcia MEDIAN
Dobre, teraz, keď sme hovorili o vnorení a hovorili sme o elegancii MIN s MAX, mal by som spomenúť, že je možné tento problém vyriešiť bez akéhokoľvek vnorenia pomocou funkcie MEDIAN. Všeobecná verzia vzorca vyzerá takto:
=MEDIAN(0,1,A1)
Funguje to preto, lebo funkcia MEDIAN vráti strednú hodnotu (stredné číslo) v skupine čísel. Ak je hodnota záporná, z nuly sa stane stredné číslo. Ak je číslo väčšie ako 1, z 1 sa stane stredné číslo. Chytré!
Poznámka: MEDIAN však vráti stredné číslo iba vtedy, keď je celkový počet hodnôt nepárny. Ak je počet hodnôt párny, funkcia MEDIAN vráti priemer z dvoch čísel v strede. V dôsledku toho, ak je cieľová bunka (A1) prázdna, program MEDIAN vráti priemer 1 a nulu, čo je 0,5 alebo 50% pri formátovaní v percentách.
