Obecný vzorec
=MAX(0,MIN(A1,1))
souhrn
Chcete-li omezit procentní hodnotu tak, aby klesla mezi 0% a 100%, můžete použít vzorec založený na funkcích MIN a MAX. V zobrazeném příkladu je vzorec v C5, zkopírovaný dolů, následující:
=MAX(0,MIN(B5,1))
Výsledkem je, že záporné hodnoty jsou vynuceny na nulu, hodnoty nad 1 jsou omezeny na 1 a hodnoty mezi 0 a 1 nejsou ovlivněny.
Poznámka: všechny hodnoty formátované ve formátu procentního čísla.
Vysvětlení
Abychom tomuto problému porozuměli, ujistěte se, že rozumíte tomu, jak funguje formátování čísel v procentech. Stručně řečeno, procenta jsou desetinná čísla: 0,1 je 10%, 0,2 je 20% atd. Číslo 1, pokud je formátováno v procentech, je 100%. Více o formátech čísel zde.
Cílem tohoto příkladu je omezit příchozí procentní hodnoty tak, aby spadaly do horní a dolní mezní hodnoty. Záporné hodnoty a hodnoty nad 100% nejsou povoleny, takže konečným výsledkem musí být číslo mezi nulou a 1 (0-100%) včetně.
Ačkoli lze k řešení tohoto problému použít funkci IF (viz níže), výsledek bude poněkud delší a nadbytečný. Ukázaný příklad místo toho používá kombinaci funkcí MIN a MAX ve velmi kompaktním vzorci:
=MAX(0,MIN(B5,1))
Toto je příklad vnoření - funkce MIN je vnořená uvnitř funkce MAX. Vnoření je klíčovým stavebním kamenem pro pokročilejší vzorce.
Při práci zevnitř ven se funkce MIN používá k omezení příchozích hodnot na 1 takto:
MIN(B5,1) // get smaller value
Překlad: vraťte menší z B5 a 1. U jakékoli hodnoty nad 1 se vrátí hodnota v B5. V příkladu B5 obsahuje -5% (-0,05), takže MIN vrací -0,05. Tento výsledek se vrací přímo do funkce MAX:
=MAX(0,-0.05) // get larger value
Zde vidíme, jak vzorec funguje. Protože nula je větší (větší) než -0,05, MAX vrací nulu jako konečný výsledek. Původní hodnota je zahozena.
Funkce IF
Jak bylo uvedeno výše, lze k řešení tohoto problému použít také funkci IF. K tomu potřebujeme dvě samostatné funkce IF. Jeden IF vynutí záporné hodnoty na nulu:
IF(B5<0,0,B5) // cap at zero
Druhý IF omezuje větší hodnoty na 1:
=IF(B5>1,1,B5) // cap at 1
Když vnoříme první IF do druhého, máme konečný vzorec:
=IF(B5>1,1,IF(B5<0,0,B5))
Toto je příklad vnořeného IF. Vrátí přesně stejný výsledek jako výše uvedený vzorec MIN a MAX, ale je o něco složitější a redundantní. Všimněte si například, že odkaz na B5 se vyskytuje třikrát odděleně.
Sečteno a podtrženo - když potřebujete provést výběr na základě menších nebo větších hodnot, funkce MIN a MAX mohou být chytrým a elegantním způsobem, jak udržet vzorec jednoduchý.
Funkce MEDIAN
Dobře, teď, když jsme hovořili o vnoření a hovořili jsme o eleganci MIN s MAX, měl bych zmínit, že je možné tento problém vyřešit bez jakéhokoli vnoření pomocí funkce MEDIAN. Obecná verze vzorce vypadá takto:
=MEDIAN(0,1,A1)
Funguje to, protože funkce MEDIAN vrací medián (střední číslo) ve skupině čísel. Když je hodnota záporná, stane se nula prostředním číslem. Když je číslo větší než 1, stane se z 1 prostřední číslo. Chytrý!
Všimněte si však, že MEDIAN vrací střední číslo pouze tehdy, když je celkový počet hodnot lichý. Pokud je počet hodnot sudý, vrátí MEDIAN průměr dvou čísel uprostřed. V důsledku toho, pokud je cílová buňka (A1) prázdná, vrátí funkce MEDIAN průměr 1 a nulu, což je 0,5 nebo 50% při formátování v procentech.
