Vispārēja formula
=MAX(0,MIN(A1,1))
Kopsavilkums
Lai ierobežotu procentuālo vērtību tā, lai tā būtu no 0% līdz 100%, varat izmantot formulu, kuras pamatā ir MIN un MAX funkcijas. Parādītajā piemērā C5 formula, kas nokopēta uz leju, ir:
=MAX(0,MIN(B5,1))
Rezultāts ir tāds, ka negatīvās vērtības tiek piespiestas nullei, vērtības, kas pārsniedz 1, ir ierobežotas ar vērtību 1 un vērtības starp 0 un 1 netiek ietekmētas.
Piezīme: visas vērtības formatētas ar skaitļu procentuālo formātu.
Paskaidrojums
Lai saprastu šo problēmu, pārliecinieties, ka saprotat, kā darbojas skaitļu formatēšana procentos. Īsāk sakot, procenti ir decimāldaļas: 0,1 ir 10%, 0,2 ir 20% utt. Skaitlis 1, formatēts procentos, ir 100%. Vairāk par numuru formātiem šeit.
Šī piemēra mērķis ir ierobežot ienākošās procentuālās vērtības tā, lai tās nonāktu augšējā un apakšējā sliekšņa robežās. Negatīvas vērtības un vērtības, kas pārsniedz 100%, nav atļautas, tāpēc gala rezultātam jābūt skaitlim no nulles līdz 1 (0–100%) ieskaitot.
Lai gan IF problēmu var izmantot, lai atrisinātu šo problēmu (skatīt zemāk), rezultāts būs nedaudz garāks un lieks. Tā vietā parādītajā piemērā tiek izmantota MIN un MAX funkciju kombinācija ļoti kompaktā formulā:
=MAX(0,MIN(B5,1))
Šis ir ligzdošanas piemērs - funkcija MIN ir ievietota MAX funkcijas iekšpusē. Ligzdošana ir galvenā sastāvdaļa modernākām formulām.
Strādājot no iekšpuses uz āru, funkcija MIN tiek izmantota, lai ienākošās vērtības ierobežotu līdz 1:
MIN(B5,1) // get smaller value
Tulkojums: atgrieziet mazāko no B5 un 1. Ja vērtība pārsniedz 1, tiek atgriezta vērtība B5. Piemērā B5 satur -5% (-0,05), tāpēc MIN atgriež -0,05. Šis rezultāts tiek atgriezts tieši funkcijai MAX:
=MAX(0,-0.05) // get larger value
Mēs redzam, ka formula veic savu darbu. Tā kā nulle ir lielāka (lielāka) par -0,05, MAX gala rezultātā atgriež nulli. Sākotnējā vērtība tiek izmesta.
IF funkcija
Kā minēts iepriekš, šīs problēmas risināšanai var izmantot arī funkciju IF. Lai to izdarītu, mums ir nepieciešamas divas atsevišķas IF funkcijas. Viens IF piespiež negatīvās vērtības līdz nullei:
IF(B5<0,0,B5) // cap at zero
Otrais IF ierobežo lielākas vērtības pie 1:
=IF(B5>1,1,B5) // cap at 1
Kad mēs ievietojam pirmo IF otrā iekšpusē, mums ir galīgā formula:
=IF(B5>1,1,IF(B5<0,0,B5))
Šis ir ligzdota IF piemērs. Tas atgriež tieši tādu pašu rezultātu kā iepriekš minētā MIN un MAX formula, bet ir nedaudz sarežģītāka un liekāka. Ievērojiet, piemēram, atsauce uz B5 notiek trīs atsevišķas reizes.
Apakšējā līnija - kad jums jāizdara izvēle, pamatojoties uz mazākām vai lielākām vērtībām, MIN un MAX funkcijas var būt gudrs un elegants veids, kā saglabāt formulu vienkāršu.
MEDIAN funkcija
Labi, tagad, kad mēs esam runājuši par ligzdošanu un runāju par MIN eleganci ar MAX, man jāpiemin, ka ar MEDIAN funkciju šo problēmu ir iespējams atrisināt bez jebkādas ligzdošanas. Formulas vispārīgā versija izskatās šādi:
=MEDIAN(0,1,A1)
Tas darbojas, jo funkcija MEDIAN atgriež mediānu (vidējo skaitli) skaitļu grupā. Ja vērtība ir negatīva, nulle kļūst par vidējo skaitli. Kad skaitlis ir lielāks par 1, 1 kļūst par vidējo skaitli. Gudrs!
Tomēr ņemiet vērā, ka MEDIAN atgriež vidējo skaitli tikai tad, ja kopējais vērtību skaits ir nepāra. Ja vērtību skaits ir vienāds, MEDIAN atgriež vidējo no diviem skaitļiem, kas atrodas vidū. Tā rezultātā, ja mērķa šūna (A1) ir tukša, MEDIAN atgriezīs vidējo vērtību 1 un nulli, kas ir 0,5 vai 50%, formatējot procentos.
