Általános képlet
=MAX(0,MIN(A1,1))
Összegzés
A százalékos érték korlátozásához, hogy az 0% és 100% közé essen, használhat egy MIN és MAX függvényen alapuló képletet. A bemutatott példában a C5 képlete, lemásolva:
=MAX(0,MIN(B5,1))
Ennek eredményeként a negatív értékeket nullára kényszerítik, az 1 feletti értékeket 1-re korlátozzák, és a 0 és 1 közötti értékeket ez nem befolyásolja.
Megjegyzés: az összes érték százalékos számformátumban van formázva.
Magyarázat
A probléma megértése érdekében feltétlenül értse meg a százalékos számformázás működését. Dióhéjban a százalékok tizedesértékek: 0,1 10%, 0,2 20% stb. Az 1. szám százalékban formázva 100%. További információ a számformátumokról itt.
Ennek a példának a célja a bejövő százalékos értékek korlátozása, hogy azok egy felső és alsó küszöbérték alá esjenek. Negatív értékek és 100% feletti értékek nem megengedettek, ezért a végeredménynek nulla és 1 (0-100%) közötti számnak kell lennie (beleértve).
Bár az IF függvény használható a probléma megoldására (lásd alább), az eredmény valamivel hosszabb és felesleges lesz. Ehelyett a bemutatott példa a MIN és a MAX funkciók kombinációját használja nagyon kompakt képletben:
=MAX(0,MIN(B5,1))
Ez egy példa a fészkelésre - a MIN funkció be van ágyazva a MAX funkcióba. A fészkelés a fejlettebb képletek kulcsfontosságú építőköve.
Belülről kifelé haladva a MIN funkció a bejövő értékek 1-re korlátozására szolgál:
MIN(B5,1) // get smaller value
Fordítás: adja meg a B5 és az 1 közül a kisebbik értékét. Bármely 1 feletti érték esetén a B5 értéket adjuk vissza. A példában a B5 -5% -ot (-0,05) tartalmaz, így a MIN értéke -0,05. Ez az eredmény közvetlenül a MAX funkcióba kerül:
=MAX(0,-0.05) // get larger value
Itt látjuk, hogy a képlet elvégzi a munkáját. Mivel a nulla nagyobb (nagyobb) mint -0,05, a MAX végeredményként nulla értéket ad vissza. Az eredeti értéket elvetjük.
IF függvény
Mint fentebb említettük, az IF függvény szintén használható a probléma megoldására. Ehhez két külön IF függvényre van szükségünk. Egy IF nullára kényszeríti a negatív értékeket:
IF(B5<0,0,B5) // cap at zero
A második IF nagyobb értékeket határoz meg 1-nél:
=IF(B5>1,1,B5) // cap at 1
Amikor az első IF-t a másodikba fészkeljük, megvan a végső képlet:
=IF(B5>1,1,IF(B5<0,0,B5))
Ez egy beágyazott IF példa. Pontosan ugyanazt az eredményt adja, mint a fenti MIN és MAX képlet, de kissé összetettebb és felesleges. Figyelem, például a B5-re történő hivatkozás három külön alkalommal fordul elő.
Lényeg - ha kisebb vagy nagyobb értékek alapján kell választania, a MIN és a MAX funkciók okos és elegáns módon szolgálják a képlet egyszerűségét.
MEDIAN funkció
Rendben, most, amikor beszéltünk a fészkelésről, és beszéltünk a MIN eleganciájáról a MAX-szal, meg kell említenem, hogy a MEDIAN funkcióval mindenféle fészkelés nélkül megoldható ez a probléma. A képlet általános változata így néz ki:
=MEDIAN(0,1,A1)
Ez azért működik, mert a MEDIAN függvény a medián (középső szám) számcsoportban adja vissza. Ha egy érték negatív, a nulla lesz a középső szám. Ha egy szám nagyobb, mint 1, akkor az 1 lesz a középső szám. Okos!
A MEDIAN azonban csak akkor adja vissza a középső számot, ha az értékek száma páratlan. Ha az értékek száma páros, a MEDIAN visszaadja a középen lévő két szám átlagát. Ennek eredményeként, ha a célsejt (A1) üres, a MEDIAN az 1 és a nulla átlagot adja vissza, ami százalékos formázás esetén 0,5 vagy 50%.
