Обща формула
=MAX(0,MIN(A1,1))
Обобщение
За да ограничите процентната стойност, така че тя да пада между 0% и 100%, можете да използвате формула, базирана на функциите MIN и MAX. В показания пример формулата в C5, копирана надолу, е:
=MAX(0,MIN(B5,1))
Резултатът е, че отрицателните стойности са принудени към нула, стойностите над 1 са ограничени на 1 и стойностите между 0 и 1 не са засегнати.
Забележка: всички стойности са форматирани с процентно число.
Обяснение
За да разберете този проблем, не забравяйте да разберете как работи форматирането на процентно число. Накратко, процентите са десетични стойности: 0,1 е 10%, 0,2 е 20% и т.н. Числото 1, форматирано като процент, е 100%. Повече за форматите с числа тук.
Целта на този пример е да ограничи входящите процентни стойности, така че те да попаднат в горния и долния праг. Отрицателни стойности и стойности над 100% не са позволени, така че крайният резултат трябва да бъде число между нула и 1 (0-100%) включително.
Въпреки че функцията IF може да се използва за решаване на този проблем (вижте по-долу), резултатът ще бъде малко по-дълъг и излишен. Вместо това показаният пример използва комбинация от функциите MIN и MAX в много компактна формула:
=MAX(0,MIN(B5,1))
Това е пример за влагане - функцията MIN е вложена във функцията MAX. Влагането е ключов градивен елемент за по-усъвършенствани формули.
Работейки отвътре навън, функцията MIN се използва за ограничаване на входящите стойности на 1 по следния начин:
MIN(B5,1) // get smaller value
Превод: връща по-малкото от B5 и 1. За всяка стойност над 1 се връща стойността в B5. В примера B5 съдържа -5% (-0.05), така че MIN връща -0.05. Този резултат се връща директно към функцията MAX:
=MAX(0,-0.05) // get larger value
Тук виждаме, че формулата си върши работата. Тъй като нулата е по-голяма (по-голяма) от -0,05, MAX връща нула като краен резултат. Оригиналната стойност се изхвърля.
IF функция
Както бе споменато по-горе, функцията IF също може да се използва за решаване на този проблем. За целта ни трябват две отделни IF функции. Един IF принуждава отрицателните стойности към нула:
IF(B5<0,0,B5) // cap at zero
Вторият IF ограничава по-големи стойности на 1:
=IF(B5>1,1,B5) // cap at 1
Когато вложим първия IF във втория, имаме окончателната формула:
=IF(B5>1,1,IF(B5<0,0,B5))
Това е пример за вложен IF. Той връща точно същия резултат като формулата MIN и MAX по-горе, но е малко по-сложен и излишен. Забележете, например, позоваването на B5 се среща три отделни пъти.
Долната линия - когато трябва да направите избор въз основа на по-малки или по-големи стойности, функциите MIN и MAX могат да бъдат умен и елегантен начин за опростяване на формулата.
Функция MEDIAN
Добре, сега, когато говорихме за влагане и говорихме за елегантността на MIN с MAX, трябва да спомена, че е възможно да се реши този проблем, без изобщо да се влага с функцията MEDIAN. Общата версия на формулата изглежда така:
=MEDIAN(0,1,A1)
Това работи, защото функцията MEDIAN връща медианата (средното число) в група от числа. Когато дадена стойност е отрицателна, нулата става средното число. Когато числото е по-голямо от 1, 1 става средното число. Умно!
Забележка MEDIAN обаче връща средното число само когато общият брой стойности е нечетен. Ако броят на стойностите е четен, MEDIAN връща средната стойност на двете числа в средата. В резултат на това, ако целевата клетка (A1) е празна, MEDIAN ще върне средната стойност от 1 и нула, което е 0,5 или 50%, когато се форматира като процент.
