Generička formula
=IF(logical_test,"P","")
Sažetak
Da biste prikazali kvačicu ako je vrijednost "dopuštena" na temelju postojećeg popisa dopuštenih vrijednosti, možete upotrijebiti formulu koja se temelji na funkciji IF zajedno s funkcijom COUNTIF. U primjeru emisije formula u C5 je:
=IF(COUNTIF(allowed,B5),"✓","")
gdje je dopušteno imenovani raspon E5: E9.
Obrazloženje
Ova je formula dobar primjer gniježđenja jedne funkcije unutar druge. U osnovi, ova formula koristi IF funkciju postavljenu za vraćanje kvačice (✓) kada logički test vrati TRUE:
=IF(logical_test,"✓","")
Ako test vrati FALSE, formula vraća prazan niz (""). Za logički test koristimo funkciju COUNTIF poput ove:
COUNTIF(allowed,B5)
COUNTIF broji pojave vrijednosti u B5 u imenovanom dopuštenom rasponu (E5: E9). To vam se može činiti "unatrag", ali ako razmislite, to ima smisla. Ako je vrijednost u B5 pronađena na dopuštenom popisu vrijednosti, COUNTIF će vratiti pozitivan broj (u ovom slučaju 1). Ako nije, COUNTIF će vratiti nulu. Excel će bilo koji broj koji nije nula procijeniti kao TRUE, tako da ovo savršeno funkcionira kao logički test za IF.
IF će vratiti vrijednost TRUE samo ako je vrijednost pronađena na dopuštenom popisu, a ako je tako, konačni rezultat je kvačica (✓). Ako vrijednost nije pronađena na dopuštenom popisu, COUNTIF vraća nulu, što se procjenjuje kao FALSE. U tom je slučaju konačni rezultat prazan niz (""), koji ne prikazuje ništa.
S fiksnim vrijednostima
Gornji primjer prikazuje dopuštene vrijednosti u rasponu ćelija, ali dopuštene vrijednosti također se mogu čvrsto kodirati u formule kao konstanta niza poput ove:
=IF(COUNTIF(("red","blue","green"),B5),"✓","")
Znak kvačice (✓)
Umetanje znaka kvačice u Excel može biti iznenađujuće izazovno, a na internetu ćete pronaći mnogo članaka koji objašnjavaju različite pristupe. Znak kvačice (✓) koji se koristi u ovoj formuli najlakši je način u Excel jednostavno kopirati i zalijepiti. Ako kopirate s ove web stranice, zalijepite u traku s formulama kako biste izbjegli povlačenje neželjenog oblikovanja. Također možete kopirati i zalijepiti izravno iz priloženog radnog lista.
Ako imate problema s kopiranjem i lijepljenjem, isprobajte ovu varijaciju. Sam znak je Unicode 2713 (U + 2713), a također se može unijeti u Excel pomoću funkcije UNICHAR ovako:
=UNICHAR(10003) // returns "✓"
Dakle, izvorna formula može se napisati ovako:
=IF(COUNTIF(allowed,B5),UNICHAR(10003),"")
Napomena: funkcija UNICHAR uvedena je u programu Excel 2013.
Proširivanje formule
Osnovna ideja u ovoj formuli može se proširiti na mnogo pametnih načina. Na primjer, snimka zaslona u nastavku prikazuje formulu koja vraća kvačicu samo kada su svi rezultati testa najmanje 65:
Formula u G5 je:
=IF(NOT(COUNTIF(B5:F5,"<65")),"✓","")
Funkcija NOT poništava rezultat iz COUNTIF. Ako vam je ovo zbunjujuće, možete naizmjenično restrukturirati formulu IF ovako:
=IF(COUNTIF(B5:F5,"<65"),"","✓")
U inačici formule logika je sličnija izvornoj formuli gore. Međutim, premjestili smo kvačicu u argument value_if_false, pa će se kvačica pojaviti samo ako je broj od COUNTIF jednak nuli. Drugim riječima, kvačica će se pojaviti samo kad se ne pronađu vrijednosti manje od 65.
Napomena: možete upotrijebiti i uvjetno oblikovanje da biste istaknuli valjani ili nevaljani unos, a provjeru valjanosti podataka kako biste ograničili unos kako biste omogućili samo važeće podatke.
