10 najboljih funkcija u Excelu koje trebate znati

Čudno je što većina korisnika Excela već ima pristup alatima koji bi mogli prepoloviti njihov radni opterećenje—jednostavno nisu upoznali prave funkcije. Najbolje funkcije Excela ne služe samo za obračun brojeva; one razmišljaju, filtriraju, pretražuju i prilagođavaju se gotovo svakom podatkovnom izazovu koji im netko postavi.

Poznavanje kojih deset funkcija valja savladati mijenja sve. Ono što slijedi detaljno objašnjava svaku od njih jednostavnim riječima, pa svatko može početi koristiti ih s povjerenjem.

ZBRAJANJE: Trenutno Zbrajajte Vrijednosti Kroz Bilo Koji Raspon

SUM je temelj gotovo svakog Excel spreadsheet-a, i to je jedna od prvih funkcija koju bi svaki Excel korisnik trebao ovladati. Zbraja brojeve preko susjednih raspona poput =SUM(A2:A10) ili čak nesusjednih blokova poput =SUM(A2:A5, C2:C5), što je nevjerojatno fleksibilno.

Jedna stvar koju svi cijene: SUM tiho ignora tekst i prazne ćelije, tako da nema potrebe za brigom oko grešaka iz neuređenih podataka. Pri kopiranju formula preko listova, fiksiranje raspona s apsolutnim referencama (poput =SUM($B$2:$B$100)) čuva sve konzistentnim.

Za velike skupove podataka, korištenje eksplicitnih raspona (A2:A100000) umjesto referenci na cijele stupce (A:A) primjetno poboljšava performanse. SUM se također savršeno uklapa s SUMIFS ili poljima formula za uvjetne zbrojeve, dajući korisnicima još veću analitičku moć.

AKO: Učinite da vaš proračunski list razmišlja sam za sebe

Dok je SUM sve o obradi brojeva, IF je mjesto gdje Excel počinje djelovati kao da stvarno *razmišlja*. IF funkcija radi jednostavno: testira uvjet, zatim vraća jedan rezultat ako je točan, a drugi ako je netočan. Na primjer, `=IF(A2>100,”Iznad”,”Ispod”)` trenutno kategorizira bilo koju vrijednost.

Kada jedan uvjet nije dovoljan, korisnici mogu ugniježđiti više IF-a zajedno, iako čitljivost brzo postaje neuređena. Čistiji pristup koristi AND ili OR za simultano testiranje nekoliko kriterija, kao što je provjera podobnosti na temelju dobi i statusa odjednom. Za formule sklone greškama, omotavanje IF-a unutar IFERROR čini stvari urednom i prilagođenom korisnicima.

Moderni Excel korisnici imaju koristi od IFS (za više uvjeta) i LET (za nazivanje složenih izračuna), čineći naprednu logiku lakšom za sve da je prate.

SUMIFS i COUNTIFS: Zbrajanje i brojanje pod višestrukim uvjetima

Kada IF nauči proračunsku tablicu da *razmišlja*, SUMIFS i COUNTIFS je uče da istovremeno *sortira i računa*.

Ove dvije funkcije omogućavaju svakome da radi pametnije filtriranjem podataka prema više uvjeta istovremeno. SUMIFS zbraja vrijednosti koje zadovoljavaju nekoliko kriterija — na primjer, =SUMIFS(C2:C100, A2:A100, “North”, B2:B100, “>1000”) zbraja samo sjeverne prodaje koje prelaze 1.000. COUNTIFS umjesto toga broji redove koji se podudaraju.

Oba podržavaju operatore (>, <, ) i zamjenski znakove (* za djelomične podudarnosti), ali korisnici moraju nadovezati operatore s referencama stanica, kao što je “>=”&E1.

Za datume, funkcije DATE) osiguravaju točne usporedbe.

Jedno važno pravilo: svi raspon kriterija mora biti iste veličine — neusklađeni rasponi uzrokuju frustrirajuće #VALUE! greške. Savladajte ova dva, i analiza podataka postaje doista upravljiva.

VLOOKUP vs XLOOKUP: Koji biste trebali koristiti?

VLOOKUP i XLOOKUP obavljaju istu osnovnu zadaću—pronalaženje vrijednosti u tablici i vraćanje povezanih podataka—ali XLOOKUP to radi s puno manje frustracije, jer ne forsira da stupac za pretraživanje bude prvi, automatski zadanu vrijednost postavlja na točno podudaranje, a čak i nježno rukuje nedostajućim vrijednostima.

Najveća slabost VLOOKUPIA je njegova ovisnost o broju indeksa stupca, što se tiho prekida ako netko umetne ili obriše stupac (iznenađujuće čest problem u zajedničkim radnim knjigama).

Kada je sve rečeno, pravi izbor ovisi o situaciji: XLOOKUP pobjeđuje u gotovo svakoj modernoj primjeni, dok VLOOKUP i dalje zaslužuje svoje mjesto kada datoteka mora raditi u starijim verzijama Excela koje ne podržavaju novije funkcije.

Ključne razlike objašnjene

Tanto VLOOKUP kako XLOOKUP obavljaju istu temeljnu zadaću—pronalaženje vrijednosti u tablici i vraćanje povezanih podataka—ali rade to na prilično različite načine, i te razlike su važnije nego što bi se moglo misliti.

VLOOKUP zahtijeva da se stupac pretrage nalazi sasvim lijevo, koristi statički indeks stupca (koji se prekida kada se stupci pomaknu), te se zadano postavlja na približne podudarnosti—što je lukava izvor grešaka.

XLOOKUP, predstavljen 2021. godine, u potpunosti uklanja te probleme. Pretražuje bilo koji stupac, vraća rezultate u bilo kojem smjeru (da, čak i ulijevo), zadano je postavljen na točna podudaranja, te rukuje nedostajućim vrijednostima ugrađenim argumentom umjesto ružnih poruka o greškama. Također automatski širi dinamičke nizove.

Za sve koji koriste moderni Excel, XLOOKUP nije samo bolji—to je stvarno pametniji i sigurniji izbor koji cijeli tim zaslužuje.

Izbor Prave Funkcije

Izbor između VLOOKUP i XLOOKUP zapravo se svodi na jedno praktično pitanje: koju verziju Excela koristiš?

Ako netko koristi Excel 2021 ili Microsoft 365, XLOOKUP je gotovo uvijek bolji izbor. Pretražuje bilo koji stupac (ne samo krajnji lijevi), podrazumevano traži točne podudarnosti, elegantno rukuje nedostajućim rezultatima i neće se prekinuti kada se stupci umetnu ili izbrišu.

Samo taj posljednji aspekt štedi pune glavobolje.

VLOOKUP, međutim, još uvijek zaslužuje svoje mjesto kada korisnici dijele datoteke s kolegama koji koriste starije verzije Excela, budući da kompatibilnost bitna u timskom okruženju.

Razmisli o XLOOKUP kao o nadograđenom alatu prema kojem bi se svi trebali na kraju migrirati, i VLOOKUP kao o pouzdanoj rezervi za naslijeđene situacije. Poznavanje obojega čuva korisnike spremnim za bilo koji scenarij proračunske tablice koji će naići.

INDEX i MATCH: Pronađi podatke u bilo kojem smjeru

INDEX i MATCH funkcioniraju kao tim: MATCH pronalazi poziciju vrijednosti u rasponu (zamislite ga kao izvidnicu), a INDEX koristi tu poziciju kako bi vratio stvarni rezultat iz bilo kojeg stupca ili reda.

Ovaj kombini pobjeđuje VLOOKUP na važan način—nije ograničen na pretraživanje samo najljevijeg stupca, pa bez problema upravlja traženjem u bilo kojem smjeru.

U praksi, formula poput =IFERROR(INDEX(B2:B100, MATCH(“Smith”, A2:A100, 0)), “Nije pronađeno”) povlači plaću zaposlenika prema imenu dok elegantno upravlja slučajevima gdje ime ne postoji.

Kako INDEX MATCH funkcionira

Za razliku od VLOOKUP-a, ova kombinacija se ne prekida kada se stupci pomaknu—što je čini pouzdanim izborom kojem svi članovi tima mogu vjerovati pri radu s velikim, mijenjajućim se skupovima podataka.

Prednosti u odnosu na VLOOKUP

Iako VLOOKUP privlači puno pažnje kao glavna funkcija za pretraživanje, dolazi s frustrirajućim ograničenjem: može pretražiti samo krajnji lijevi stupac tablice i vratiti vrijednosti desno od njega.

INDEX/MATCH u potpunosti uklanja to ograničenje, omogućavajući korisnicima pretraživanje podataka u bilo kojem smjeru — lijevo, desno, gore ili dolje.

Također je otporniji kada se stupci dodaju ili brišu, budući da INDEX referira raspon umjesto fiksnog broja stupca (na koji se VLOOKUP oslanja i što se lako prekida).

Na velikim skupovima podataka, INDEX/MATCH radi brže i koristi manje memorije jer obavlja samo stupce koji su mu zapravo potrebni. Uparivanje MATCH-a sa sortiranim rasponima čak omogućava brzinu binarnog pretraživanja, što ga čini vidljivo bržim.

Jednostavno rečeno, INDEX/MATCH radi sve što radi VLOOKUP, samo bolje.

Primjeri iz stvarnog svijeta

Najjednostavniji način da se INDEX/MATCH koristi je sparivanje da bi se izvukla vrijednost iz bilo kojeg stupca u tablici. Recimo da tim trebala zaposlenikovu odjelu na osnovu njegovog ID-a—`=INDEX(C2:C100, MATCH(E2, A2:A100, 0))` to rješava trenutačno.

Omotavanje svega u `IFERROR` čini rezultate čistima kada su podaci nepotpuni—nešto što svaka zajednička tablica zaista trebala.

IFERROR: Zaustavite Ružne Poruke o Greškama na Mjestu

Sintaksa je jednostavna: =IFERROR(value, value_if_error). Umotajte je oko VLOOKUP-a ili XLOOKUP-a (=IFERROR(XLOOKUP(…),”Not found”)), i poruke o greškama nestaju trenutno.

Trebate čiste kontrolne ploče? Pokušajte =IFERROR(A2/B2,0) kako biste spriječili #DIV/0! da prekine izračune.

Važna napomena—ako korisnici trebaju hvatiti samo #N/A specifično, IFNA je pametniji izbor, jer IFERROR skriva *sve* greške, što može slučajno maskirati prave probleme koje bi svi trebali znati.

FILTER: Izvucite točno podatke koji vam trebaju

Excel’s FILTER funkcija radi točno ono što obećava njezino ime: skenira raspon (poput A2:D100) i vraća samo retke koji zadovoljavaju određeni uvjet, primjerice sve prodaje označene kao “Završeno” ili sve vrijednosti iznad 500.

Postavljanje kriterija je jednostavno — jedan uvjet poput `C2:C100=”Završeno”` upravlja jednostavnim filteriranjem, dok množenje uvjeta (`*` za AND) ili zbrajanje (`+` za OR) omogućuje korisnicima da se nose sa složenijim potrebama filteriranja bez ikakvih problema.

FILTER se dobro slaže s drugima, čisto se парuje sa SORT za organiziranje rezultata, UNIQUE za uklanjanje duplikata ili INDEX za izvlačenje određenih stupaca — što ga čini jednim od najsvestranijih alata u Excelu’s modernoj funkcijskoj ponudi.

Kako FILTER funkcionira

Izvlačenje točno onih redaka koje trebate iz skupa podataka značilo je pisanje kompliciranih formula ili ručno filtriranje i nadu da niste ništa propustili—ali FILTER to potpuno mijenja.

Funkcionira tako što uzima tri sastojka: raspon (vaši podaci), argumenta uključivanja (Boolean izraz kao što je A2:A100=”NY”), i opcionalnu if_empty rezervu (kao što je “Nema podudaranja”) za kada ništa ne kvalificira.

Rezultati automatski se proširuju u susjedne ćelije i ažuriraju se kad god se izvorni podaci promijene—bez potrebe za ručnim ponovnim učitavanjem.

Trebate li kombinirati kriterije? Pomnožite Boolean nizove za AND logiku ili ih zbrojite za OR logiku.

FILTER je na svojoj najboljeoj strani kada trebate svaki redak koji se podudara, ne samo jedan.

Za pojedine pretraživanja, XLOOKUP ili INDEX/MATCH i dalje služe zajednici dobro, ali FILTER doista pripada kada je cjelovitost važna.

Postavljanje Kriterija Filtera

Postavljanje kriterija FILTERA je gdje funkcija zarađuje svoju reputaciju—ispravno postavi argument include, i moći ćeš izvući točno redake koji ti trebaju u jednoj formuli. Argument include se evaluira na TRUE ili FALSE za svaki redak, zadržavajući samo podudaranja.

Množenje (*) upravlja logikom I; zbrajanje (+) upravlja logikom ILI—zamislite to kao matematiku koja odlučuje koji će redaci preživjeti. Svi koji koriste FILTER brzo otkrivaju da kombiniranje uvjeta oslobađa pravu moć. Uvijek dodaj treći argument (kao “No results”) kako bi se formula graciozno nosila s praznim podudaranjima umjesto da izbaci grešku.

Kombiniranje FILTER-a s drugim funkcijama

Kada jednom savladate logiku kriterija, pravo se zabava počinje—kombiniranje FILTER s drugim funkcijama za izgradnju formula koje obavljaju ozbiljan posao. Razmislite o FILTER kao o temelju na kojem ostale funkcije grade.

Umotavanje FILTER unutar SORT ili SORTBY korisnicima omogućava vraćanje filtriranih rezultata u određenom redoslijedu—kao sortiranje otvorenih tiketa po prioritetu, opadajuće. Ugniježđivanje FILTER s XLOOKUP prvo pronalazi odgovarajući ključ, a zatim povlači svaki povezani zapis za njega.

Za zbrajanje filtriranih podataka, umotavanje FILTER unutar SUM (na primjer, `=SUM(FILTER(Amount,Customer=H2,0))`) čisto obrađuje agregaciju. UNIQUE kombiniran s FILTER uklanja duplikate iz filtriranih rezultata, što je stvarno korisno pri izgradnji padajućih listi ili sažetnih izvještaja. Ove kombinacije FILTER pretvaraju iz jednostavnog sita u nešto na što se cijeli tim oslanja svakodnevno.

AVERAGEIFS: Mjerenje Prosjeka Koji Ispunjavaju Specifične Kriterije

Kada jedan filter nije dovoljan, AVERAGEIFS korak po korak prosječuje samo ćelije koje istodobno ispunjavaju *nekoliko* uvjeta. Njegova sintaksa slijedi jasan obrazac: `AVERAGEIFS(raspon_prosječivanja, raspon_kriterija1, kriterij1, raspon_kriterija2, kriterij2, …)`.

Svi koji rade s podacima o prodaji mogu, na primjer, prosječiti prihod prvog tromjesečja samo za regiju “Sjever” postavljanjem dva kriterija jedan na drugi.

Nekoliko važnih pravila održava sve glatko — svi rasponi kriterija moraju se poklapati s rasponom prosječivanja po veličini (neusklađeni rasponi izazivaju `#VALUE!` greške), a logički operatori kao što su `”>100″` moraju se pojaviti kao tekst. AVERAGEIFS se također savršeno uklapa s `DATE` funkcijama za dinamičke raspone datuma.

Ako niti jedna ćelija nije kvalificirana, vraća `#DIV/0!`, što je Excelov ljubazan način da kaže: “Ništa se nije poklopilo — ponovno provjeri te kriterije.”

LET: Napišite čistije Excel formule bez ponavljanja

Svatko tko je ikad napisao formulu koja ponavlja isti sub-izračun tri ili četiri puta zna za frustraciju — promijeni jednu stvar, i odjednom loviš svaki primjer tog ponavljajućeg dijela.

LET to rješava omogućujući korisnicima dodjelu imena srednjim rezultatima direktno unutar formule. Struktura je jednostavna: =LET(ime1, vrijednost1, ime2, vrijednost2, …, konačan izračun). Excel procjenjuje svaku названu vrijednost samo jednom, čuvajući je za ponovnu upotrebu — što također poboljšava performanse u velikim proračunskim tablicama.

Kombiniranje LET-a s LAMBDA-om ili MAP-om kreira modularne, čitljive formule koje gotovo djeluju kao mini-programi.

Jedna važna napomena koju je vrijedno podijeliti sa zajednicom: LET zahtijeva Microsoft 365 ili novije verzije Excel-a, tako da starije verzije ga neće prepoznati.

LAMBDA: Izgradite Svoje Prilagođene Excel Funkcije

LAMBDA je gdje Excel počinje djelovati manje kao alatka za proračunske tablice, a više kao pravo okruženje za programiranje — jer omogućava korisnicima da grade vlastite ponovno iskoristive prilagođene funkcije bez da dodirnu ni jedan redak VBA koda. Svi koji su ikad željeli pripadati klubu “Ja sam to izgradio formulu” konačno mogu.

Evo što čini LAMBDA vrijednim učenja:

• Umotajte bilo koju validnu formulu u `LAMBDA(parametri, izračun)` za čistiju, ponovno iskoristivu logiku
• Spremi je preko Name Manager (Formulas > Name Manager) kako bi radila kao nativna funkcija
• Gradiće rekurzivne funkcije (faktorijel, Fibonaccijev niz) koristeći tehnike samoreferenca
• Upariti LAMBDA s MAP, BYROW, REDUCE ili SCAN za dinamičku obradu cijelog niza
• Testirajte u liniji prvo — `=LAMBDA(x, x^2)(5)` — prije nego što se obavežete na nešto u Name Manager