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