Formula generică
=MAX(0,MIN(A1,1))
rezumat
Pentru a limita o valoare procentuală astfel încât să scadă între 0% și 100% puteți utiliza o formulă bazată pe funcțiile MIN și MAX. În exemplul prezentat, formula din C5, copiată, este:
=MAX(0,MIN(B5,1))
Rezultatul este că valorile negative sunt forțate la zero, valorile peste 1 sunt limitate la 1 și valorile între 0 și 1 nu sunt afectate.
Notă: toate valorile formatate cu formatul numărului procentual.
Explicaţie
Pentru a înțelege această problemă, asigurați-vă că înțelegeți cum funcționează formatarea numărului procentual. Pe scurt, procentele sunt valori zecimale: 0,1 este 10%, 0,2 este 20% și așa mai departe. Numărul 1, formatat procentual, este de 100%. Mai multe despre formatele numerice aici.
Scopul acestui exemplu este de a limita valorile procentuale primite, astfel încât acestea să se încadreze într-un prag superior și inferior. Valorile negative și valorile de peste 100% nu sunt permise, astfel încât rezultatul final trebuie să fie un număr între zero și 1 (0-100%), inclusiv.
Deși funcția IF poate fi utilizată pentru a rezolva această problemă (vezi mai jos), rezultatul va fi ceva mai lung și redundant. În schimb, exemplul prezentat folosește o combinație a funcțiilor MIN și MAX într-o formulă foarte compactă:
=MAX(0,MIN(B5,1))
Acesta este un exemplu de imbricare - funcția MIN este imbricată în funcția MAX. Cuibărirea este un element cheie pentru formule mai avansate.
Funcționând din interior spre exterior, funcția MIN este utilizată pentru a limita valorile de intrare la 1 astfel:
MIN(B5,1) // get smaller value
Traducere: returnează cel mai mic dintre B5 și 1. Pentru orice valoare peste 1, se returnează valoarea din B5. În exemplu, B5 conține -5% (-0,05), deci MIN returnează -0,05. Acest rezultat este returnat direct la funcția MAX:
=MAX(0,-0.05) // get larger value
Aici, vedem că formula își face treaba. Deoarece zero este mai mare (mai mare) decât -0,05, MAX returnează zero ca rezultat final. Valoarea inițială este eliminată.
Funcția IF
După cum s-a menționat mai sus, funcția IF poate fi utilizată și pentru rezolvarea acestei probleme. Pentru a face acest lucru, avem nevoie de două funcții IF separate. Un IF forțează valorile negative la zero:
IF(B5<0,0,B5) // cap at zero
Al doilea IF limitează valori mai mari la 1:
=IF(B5>1,1,B5) // cap at 1
Când cuibărim primul IF în interiorul celui de-al doilea, avem formula finală:
=IF(B5>1,1,IF(B5<0,0,B5))
Acesta este un exemplu de IF imbricat. Întoarce exact același rezultat ca formula MIN și MAX de mai sus, dar este puțin mai complex și mai redundant. Observați, de exemplu, referința la B5 apare de trei ori separat.
Concluzia - atunci când trebuie să faceți o alegere bazată pe valori mai mici sau mai mari, funcțiile MIN și MAX pot fi un mod inteligent și elegant de a păstra o formulă simplă.
Funcția MEDIAN
OK, acum că am vorbit despre cuibărit și am vorbit despre eleganța MIN cu MAX, ar trebui să menționez că este posibil să rezolvăm această problemă fără niciun fel de cuibărire cu funcția MEDIAN. Versiunea generică a formulei arată astfel:
=MEDIAN(0,1,A1)
Acest lucru funcționează deoarece funcția MEDIAN returnează mediana (numărul de mijloc) într-un grup de numere. Când o valoare este negativă, zero devine numărul de mijloc. Când un număr este mai mare de 1, 1 devine numărul de mijloc. Inteligent!
Cu toate acestea, nota MEDIAN returnează numărul de mijloc numai atunci când numărul total de valori este impar. Dacă numărul de valori este par, MEDIAN returnează media celor două numere din mijloc. În consecință, dacă celula țintă (A1) este goală, MEDIAN va returna media 1 și zero, care este 0,5 sau 50% atunci când este formatat ca procent.
