Smjernice i primjeri vezani uz formule polja

U ovoj vježbi prikazano je korištenje formula polja s više ćelija i s jednom ćelijom za izračun skupa prodajnih iznosa. Prvi skup koraka koristi formulu s više ćelija za izračun skupa podzbrojeva. Drugi skup koristi formulu s jednom ćelijom za izračun ukupnog zbroja.

• Formula polja s više ćelija

Kopirajte cijelu tablicu u nastavku i zalijepite je u ćeliju A1 u prazan radni list.

Prodavač	Vrsta  automobila	Broj  prodanih	Jedinična cijena	Ukupna prodaja
Šašić	limuzina	5	33000
	coupe	4	37000
Makovac	limuzina	6	24000
	coupe	8	21000
Jurić-Sedić	limuzina	3	29000
	coupe	1	31000
Pavičić	limuzina	9	24000
	coupe	5	37000
Abrus	limuzina	6	33000
	coupe	8	31000
Formula (sveukupno)			Sveukupno
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. Da biste vidjeli ukupnu prodaju coupea i limuzina za svakog prodavača, odaberite ćelije E2:E11, unesite formulu =C2:C11*D2:D11 pa pritisnite Ctrl+Shift+Enter.

2. Da biste vidjeli sveukupni iznos cjelokupne prodaje, odaberite ćeliju F11, unesite formulu =SUM(C2:C11*D2:D11), pa pritisnite Ctrl+Shift+Enter.

Kada pritisnete Ctrl+Shift+Enter, Excel će formulu okružiti vitičastim zagradama ({ }) i umetnuti instancu formule u svaku ćeliju odabranog raspona. To se odvija vrlo brzo pa u stupcu E vidite ukupan iznos prodaje za svaku vrstu automobila i za svakog prodavača. Ako odaberete E2, zatim E3, E4 i tako dalje, vidjet ćete da se prikazuje ista formula: {=C2:C11*D2:D11}. 

• Stvaranje formule polja s jednom ćelijom

U ćeliju D13 radne knjige upišite sljedeću formulu, a zatim pritisnite Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

U ovom primjeru Excel množi vrijednosti polja (raspon ćelija od C2 do D11), a zatim pomoću funkcije SUM zbraja dobivene ukupne rezultate. Rezultat je ukupni zbroj prodaje u iznosu od 7 950 000 kn. Ovaj primjer ilustrira snagu te vrste formule. Pretpostavimo da imate 1000 redaka s podacima. Dio tih podataka ili sve podatke možete zbrojiti tako da umjesto povlačenja formule preko 1000 redaka u jednoj jedinoj ćeliji stvorite formulu polja.

Također, primijetite da je jednostanična formula u stanici D13 potpuno neovisna od višestanične formule (formula u stanicama E2 do E11). To je dodatna prednost korištenja formula polja – fleksibilnost. Možete promijeniti formule u stupcu E ili izbrisati čitav taj stupac, a da to ne utječe na formulu u ćeliji D13.

Formule polja imaju i sljedeće prednosti:

• Dosljednost    (Consistency – C) Ako kliknete bilo koju ćeliju ispod ćelije E2, prikazat će vam se ista formula. Takva dosljednost jamči veću točnost.

• Sigurnost    Nije moguće prebrisati komponentu formule polja s više ćelija. Pokušajte kliknuti ćeliju E3, a zatim pritisnuti tipku Izbriši. Morat ćete odabrati cijeli raspon ćelija (od E2 do E11) i promijeniti formulu za čitav raspon ili ostaviti polje nepromijenjeno. Kao dodatnu sigurnosnu mjeru morate pritisnuti kombinaciju Ctrl+Shift+Enter da biste potvrdili bilo kakvu promjenu formule.

• Manje veličine datoteka    Umjesto nekoliko posrednih formula često možete koristiti jednu formulu niza. Na primjer, radna knjiga koristi jednu formulu polja za izračunavanje rezultata u stupcu E. Da ste koristili standardne formule (kao što su =C2*D2, C3*D3, C4*D4…), koristili biste 11 različitih formula za izračunavanje istih rezultata.

Općenito govoreći, formule polja koriste standardnu sintaksu formule. Svi počinju znakom jednakosti (=), a većinu ugrađenih Excel funkcija možete koristiti u formulama polja. Ključna je razlika u tome što kada koristite formulu niza, pritisnite Ctrl+Shift+Enter da biste unijeli formulu. Kada to učinite, Excel okružuje vašu formulu niza zagradama – ako ručno ukucate zagrade, vaša će se formula pretvoriti u tekstualni niz i neće raditi.

Funkcije niza mogu biti učinkovit način za izgradnju složenih formula. Formula polja =SUM(C2:C11*D2:D11) jednaka je ovoj formuli: =SUM(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Prilikom rada s formulama s više ćelija imajte na umu i sljedeće:

• Raspon ćelija koje će sadržavati rezultate odaberite prije unošenja formule. To ste učinili prilikom stvaranja formule s više ćelija kada ste odabrali ćelije od E2 do E11.

• Ne možete promijeniti sadržaj pojedinačne ćelije u formuli polja. Da biste to isprobali, u radnoj knjizi odaberite ćeliju E3 pa pritisnite Delete. Excel će prikazati poruku kojom vas obavještava da ne možete promijeniti dio polja.

• Možete premjestiti ili izbrisati čitavu formulu polja, ali ne i samo jedan njezin dio. Drugim riječima, da biste skratili formulu polja, najprije izbrišite postojeću formulu, a zatim počnite ispočetka.

• Da biste izbrisali formulu niza, odaberite cijeli raspon formula (na primjer, E2: E11), a zatim pritisnite Izbriši.

• U formulu polja s više ćelija nije moguće umetnuti prazne ćelije ni brisati ćelije iz te formule.

Katkad ćete možda morati proširiti formulu polja. Odaberite prvu ćeliju u postojećem rasponu polja i nastavite dok ne odaberete cijeli raspon na koji želite proširiti formulu. Pritisnite F2 biste uredili formulu, a zatim CTRL+SHIFT+ENTER da biste potvrdili formulu nakon prilagodbe raspona formule. Ključ je odabir cijelog raspona, počevši od gornje lijeve ćelije niza. Gornja lijeva ćelija ona je koja se uređuje.

Formule polja sjajna su stvar, no ipak imaju neke nedostatke:

• Ponekad se može dogoditi da zaboravite pritisnuti kombinaciju tipki Ctrl+Shift+Enter. To se može dogoditi i najiskusnijim korisnicima programa Excel. Svakako pritisnite tu kombinaciju tipki pri unošenju i uređivanju formule polja.

• Drugi korisnici radne knjige možda neće razumjeti vaše formule. Formule polja u praksi se obično ne objašnjavaju na radnom listu. Prema tome, ako drugi ljudi trebaju izmijeniti vaše radne knjige, trebali biste ili izbjegavati formule niza ili osigurati da ti ljudi znaju bilo koje formule niza i da razumiju kako ih mijenjati, ako trebaju.

• Ovisno o brzini obrade i memoriji računala, velike formule polja mogu usporiti izračune.

Konstante polja komponenta su formula polja. Konstante polja stvarate tako da unesete popis stavki, a zatim ga ručno okružite vitičastim zagradama ({ }), ovako:

={1\2\3\4\5}

Dosad ste već shvatili važnost pritiskanja kombinacije tipki Ctrl+Shift+Enter pri stvaranju formula polja. Budući da su konstante polja komponenta formula polja, konstante ćete okružiti vitičastim zagradama tako da ih ručno upišete. Zatim pomoću kombinacije Ctrl+Shift+Enter možete unijeti čitavu formulu.

Ako razdvojite stavke zarezima, stvarate vodoravno polje (redak). Ako stavke razdvojite točkama sa zarezom, stvarate okomito polje (stupac). Da biste stvorili dvodimenzionalno polje, stavke unutar retka razdvojite zarezima, a retke razdvojite točkama sa zarezom.

Ovo je polje u jednom retku: {1\2\3\4}. Evo niza u jednom stupcu: {1;2;3;4}. Ovo je polje od dva retka i četiri stupca: {1\2\3\4;5\6\7\8}. U polju s dva retka prvi je redak 1, 2, 3 i 4, a drugi 5, 6, 7 i 8. Jedna točka i zarez razdvaja dva reda, između 4 i 5.

Kao i formule polja, konstante polja možete koristiti s većinom ugrađenih funkcija programa Excel. U sljedećim je odjeljcima objašnjeno stvaranje svih vrsta konstanti i korištenje tih konstanti s funkcijama programa Excel.

Sljedećim postupcima steći ćete nešto prakse u stvaranju vodoravnih, okomitih i dvodimenzionalnih konstanti.

Stvaranje vodoravne konstante

1. Na praznom radnom listu odaberite ćelije od A1 do E1.

2. U traku formule unesite sljedeću formulu, a zatim pritisnite kombinaciju tipki Ctrl+Shift+Enter:

   ={1\2\3\4\5}

   U tom biste slučaju trebali upisati zagrade za otvaranje i zatvaranje ({ }), a Excel će dodati drugi skup umjesto vas.

   Prikazuje se sljedeći rezultat.

   

Stvaranje okomite konstante

1. U radnoj knjizi odaberite stupac s pet ćelija.

2. U traku formule unesite sljedeću formulu, a zatim pritisnite kombinaciju tipki Ctrl+Shift+Enter:

   ={1;2;3;4;5}

   Prikazuje se sljedeći rezultat.

   

Stvaranje dvodimenzionalne konstante

1. U radnoj knjizi odaberite blok ćelija širine četiri stupca i visine tri retka.

2. U traku formule unesite sljedeću formulu, a zatim pritisnite kombinaciju tipki Ctrl+Shift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}

   Prikazat će vam se sljedeći rezultat:

   

Korištenje konstanti u formulama

Evo jednostavnog primjera u kojem se koriste konstante:

1. Stvorite novi radni list u oglednoj radnoj knjizi.

2. U ćeliju A1 upišite 3, a zatim upišite 4 u ćeliju B1, 5 u ćeliju C1, 6 u D1 te 7 u E1.

3. U ćeliju A3 upišite sljedeću formulu, a zatim pritisnite kombinaciju Ctrl+Shift+Enter:

   =SUM(A1:E1*{1\2\3\4\5})

   Primijetit ćete da Excel okružuje konstantu dodatnim skupom vitičastih zagrada jer ste je unijeli kao formulu polja.

   

   U ćeliji A3 pojavljuje se vrijednost 85.

U sljedećem je odjeljku objašnjeno kako formula funkcionira.

Formula koju ste upravo upotrijebili sadrži nekoliko dijelova.

1. Funkcija

2. Pohranjeno polje

3. Operator

4. Konstanta polja

Posljednji element unutar zagrade konstanta je polja: {1\2\3\4\5}. Imajte na umu da Excel ne okružuje konstante polja vitičastim zagradama, već ih morate sami upisati. Upamtite i da nakon dodavanja konstante u formulu polja morate pritisnuti kombinaciju tipki Ctrl+Shift+Enter da biste unijeli formulu.

Budući da Excel najprije izvršava operacije na izrazima koji su uvršteni u zagrade, sljedeća dva elementa koja dolaze na red su vrijednosti pohranjene u radnoj knjizi (A1:E1) i operator. U toj fazi formula množi vrijednosti iz pohranjenog polja s odgovarajućim vrijednostima u konstanti. To odgovara sljedećem:

=SUM(A1*1;B1*2;C1*3;D1*4;E1*5)

Funkcija SUM naposljetku zbraja vrijednosti, a u ćeliji A3 pojavljuje se zbroj 85.

Da biste izbjegli korištenje pohranjenog polja i samo u potpunosti zadržali operaciju u memoriji, zamijenite pohranjeno polje konstantom nekog drugog polja:

=SUM({3\4\5\6\7}*{1\2\3\4\5})

Da biste to isprobali, kopirajte funkciju, u radnoj knjizi odaberite praznu ćeliju, zalijepite formulu u traku formule, a zatim pritisnite kombinaciju tipki Ctrl+Shift+Enter. Vidjet ćete jednake rezultate kao i u prethodnoj vježbi u kojoj je korištena formula polja:

=SUM(A1:E1*{1\2\3\4\5})

Konstante niza mogu sadržavati brojeve, tekst, logičke vrijednosti (poput TRUE i FALSE) i vrijednosti pogrešaka (poput # N/A). Brojeve možete koristiti u cjelobrojnim, decimalnim i znanstvenim formatima. Ako uvrstite tekst, tekst morate okružiti navodnicima („).

Konstante polja ne mogu sadržavati dodatna polja, formule i funkcije. Drugim riječima, mogu sadržavati samo tekst ili brojeve razdvojene zarezima ili točkama sa zarezom. Ako unesete formulu kao što je {1\2\A1:D4} ili {1\2\SUM(Q2:Z8)}, Excel će prikazati poruku s upozorenjem. Osim toga, numeričke vrijednosti ne mogu sadržavati znak postotka, znak valute, zareze i zagrade.

Konstante polja vjerojatno je najbolje upotrebljavati tako da im dodijelite nazive. Imenovane je konstante znatno jednostavnije koristiti, a drugim korisnicima mogu i sakriti dio složenosti formula polja. Da biste konstanti polja dodijelili naziv i koristili je u formuli, učinite sljedeće:

1. Na kartici Formule u grupi Definirani nazivi kliknite Definiraj naziv.
   Pojavljuje se dijaloški okvir Definiranje naziva.

2. U okvir Naziv upišite Tromjesečje1.

3. U okvir Odnosi se na unesite sljedeću konstantu (ne zaboravite ručno upisati vitičaste zagrade):

   ={"siječanj"\"veljača"\"ožujak"}

   Sadržaj dijaloškog okvira sada izgleda ovako:

   

4. Kliknite U redu, a zatim odaberite tri prazne ćelije u retku.

5. Upišite sljedeću formulu pa pritisnite Ctrl+Shift+Enter.

   =Tromjesečje1

   Prikazuje se sljedeći rezultat.

   

Pri korištenju imenovane konstante kao formule polja svakako unesite znak jednakosti. Ako to ne učinite, Excel će polje protumačiti kao niz teksta te formula neće funkcionirati na očekivani način. Naposljetku, imajte na umu da možete koristiti kombinacije teksta i brojeva.

Ako konstante polja ne funkcioniraju, treba provjeriti postoje li sljedeći problemi:

• Neki elementi možda nisu razdvojeni odgovarajućim znakom. Ako izostavite zarez ili zarez ili ako ih stavite na pogrešno mjesto, konstanta niza možda neće biti pravilno stvorena ili ćete možda vidjeti poruku upozorenja.

• Možda ste odabrali raspon ćelija koji ne odgovara broju elementa u konstanti. Ako ste, primjerice, odabrali stupac koji se sastoji od šest ćelija, a koristite konstantu koja ima pet ćelija, u praznoj se ćeliji prikazuje vrijednost pogreške #N/A. Nasuprot tome, ako odaberete premalo ćelija, Excel izostavlja vrijednosti koje nemaju odgovarajuću ćeliju.

Sljedeći primjeri prikazuju nekoliko načina na koje možete iskoristiti konstante polja u formulama polja. U nekim primjerima reci se pretvaraju u stupce i obrnuto pomoću funkcije TRANSPOSE.

Množenje svake stavke u polju

1. Stvorite novi radni list, a zatim odaberite blok praznih ćelija širine četiri stupca i visine tri retka.

2. Upišite sljedeću formulu pa pritisnite Ctrl+Shift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}*2

Kvadriranje stavki u polju

1. Odaberite blok praznih ćelija širine četiri stupca i visine tri retka.

2. Upišite sljedeću formulu polja pa pritisnite Ctrl+Shift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}*{1\2\3\4;5\6\7\8;9\10\11\12}

   Možete i unijeti ovu formulu polja koja koristi operator karet (^):

   ={1\2\3\4;5\6\7\8;9\10\11\12}^2

Transponiranje jednodimenzionalnog retka

1. Odaberite stupac koji se sastoji od pet praznih ćelija.

2. Upišite sljedeću formulu pa pritisnite Ctrl+Shift+Enter:

   =TRANSPOSE({1,2,3,4,5})

   Iako ste unijeli konstantu vodoravnog polja, funkcija TRANSPOSE konstantu polja pretvara u stupac.

Transponiranje jednodimenzionalnog stupca

1. Odaberite redak koji se sastoji od pet praznih ćelija.

2. Unesite sljedeću formulu pa pritisnite Ctrl+Shift+Enter:

   =TRANSPOSE({1;2;3;4;5})

Iako ste unijeli konstantu okomitog polja, funkcija TRANSPOSE konstantu pretvara u redak.

Transponiranje dvodimenzionalne konstante

1. Odaberite blok ćelija širine tri stupca i visine četiri retka.

2. Unesite sljedeću konstantu, a zatim pritisnite Ctrl+Shift+Enter:

   =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

   Funkcija TRANSPOSE pretvara svaki redak u niz stupaca.

U ovom se odjeljku nalaze primjeri osnovnih formula polja.

Stvaranje polja i konstanti polja iz postojećih vrijednosti

U sljedećem primjeru objašnjeno je stvaranje veza između raspona ćelija na različitim radnim listovima pomoću formula polja. Prikazano je i stvaranje konstante polja iz istog skupa vrijednosti.

Stvaranje polja iz postojećih vrijednosti

1. Na radnom listu programa Excel odaberite ćelije C8:E10 pa unesite ovu formulu:

   ={10\20\30;40\50\60;70\80\90}

   Obavezno upišite { (otvorenu vitičastu zagradu) ispred 10 i } (zatvorenu vitičastu zagradu) iza 90 jer stvarate polje brojeva.

2. Pritisnite Ctrl+Shift+Enter, čime ćete to polje brojeva unijeti u raspon ćelija C8:E10 pomoću formule polja. Na radnom listu ćelije C8 do E10 morale bi izgledati ovako:

   10	20	30
   40	50	60
   70	80	90

3. Odaberite raspon ćelija od C1 do E3.

4. U traku formule unesite sljedeću formulu, a zatim pritisnite Ctrl+Shift+Enter:

   =C8:E10

   U ćelijama od C1 do E3 pojavit će se polje ćelija veličine 3x3 koje sadrži jednake vrijednosti kao i polja od C8 do E10.

Stvaranje konstante polja iz postojećih vrijednosti

1. Odaberite ćelije C1:C3 pa pritisnite F2 da biste se prebacili na način rada za uređivanje. 

2. Pritisnite tipku F9 da biste reference ćelija pretvorili u vrijednosti. Excel će pretvoriti vrijednosti u konstantu polja. Formula bi sada trebala biti = {10\20\30;40\50\60;70\80\90}.

3. Pritisnite kombinaciju tipki Ctrl+Shift+Enter da biste konstantu polja unijeli kao formulu polja.

Brojanje znakova u rasponu ćelija

U sljedećem primjeru prikazano je brojanje znakova u rasponu ćelija, uključujući razmake.

1. Kopirajte cijelu tablicu te je zalijepite u ćeliju A1 radnog lista.

   Podaci
   Ovo je
   skup ćelija koji
   zajedno
   čini
   jednu rečenicu.
   Ukupan broj znakova u ćelijama A2:A6
   =SUM(LEN(A2:A6))
   Sadržaj najdulje ćelije (A3)
   =INDEX(A2:A6;MATCH(MAX(LEN(A2:A6));LEN(A2:A6);0);1)

2. Odaberite ćeliju A8, a zatim pritisnite Ctrl+Shift+Enter da biste vidjeli ukupan broj znakova u ćelijama A2:A6 (66).

3. Odaberite ćeliju A10, a zatim pritisnite Ctrl+Shift+Enter da biste vidjeli sadržaj najdulje ćelije u rasponu A2:A6 (ćelija A3).

Sljedeća formula u ćeliji A8 broji ukupan broj znakova (66) u ćelijama od A2 do A6.

=SUM(LEN(A2:A6))

U ovom slučaju funkcija LEN vraća duljinu svakog od nizova teksta unutar svake ćelije raspona. Funkcija SUM zatim zbraja te vrijednosti i prikazuje rezultat (66).

Traženje n najmanjih vrijednosti unutar raspona

U ovom je primjeru prikazano traženje triju najmanjih vrijednosti u rasponu ćelija.

1. Unesite neke slučajne brojeve u ćelije A1:A11.

2. Odaberite ćelije od C1 do C3. U tom će se skupu ćelija nalaziti rezultati koje dobije formula polja.

3. Unesite sljedeću formulu pa pritisnite Ctrl+Shift+Enter:

   =SMALL(A1:A11,{1;2;3})

Ova formula koristi konstantu niza da tri puta procijeni funkciju SMALL i vrati najmanji (1), drugi najmanji (2) i treći najmanji (3) član u niz koji se nalazi u ćelijama A1: A10 konstanti dodate još argumenata. S tom formulom možete koristiti i dodatne funkcije, kao što su SUM ili AVERAGE. Na primjer:

=SUM(SMALL(A1:A10,{1\2\3})

=AVERAGE(SMALL(A1:A10,{1\2\3})

Traženje n najvećih vrijednosti unutar raspona

Da biste pronašli najveće vrijednosti unutar raspona, funkciju SMALL možete zamijeniti funkcijom LARGE. Uz to, u sljedećem su primjeru upotrijebljene funkcije ROW i INDIRECT.

1. Odaberite ćelije od D1 do D3.

2. U traku formule unesite ovu formulu, a zatim pritisnite kombinaciju tipki Ctrl+Shift+Enter:

   =LARGE(A1:A10,ROW(INDIRECT("1:3")))

U ovoj je fazi korisno znati ponešto o funkcijama ROW i INDIRECT. Pomoću funkcije ROW možete stvoriti polje uzastopnih cijelih brojeva. Na primjer, odaberite prazan stupac od 10 ćelija u radnoj knjižici za vježbanje, unesite ovu formulu polja, a zatim pritisnite Ctrl+Shift+Enter:

=ROW(1:10)

Formula stvara stupac koji se sastoji od deset uzastopnih cijelih brojeva. Da biste vidjeli jedan od mogućih problema, umetnite redak iznad raspona koji sadrži formulu polja (dakle, iznad prvog retka). Excel prilagođava reference na retke, a formula generira cijele brojeve od 2 do 11. Da biste riješili taj problem, u formulu morate dodati funkciju INDIRECT:

=ROW(INDIRECT("1:10"))

Funkcija INDIRECT kao argumente koristi tekstne nizove (zato je raspon 1:10 okružen dvostrukim navodnicima). Excel pri umetanju redaka ili nekoj drugoj vrsti premještanja formule polja ne prilagođava tekstne vrijednosti. Funkcija ROW zbog toga uvijek generira polje sa željenim cijelim brojevima.

Pogledajmo formulu koju ste ranije koristili – =LARGE(A5:A14,ROW(INDIRECT("1:3"))) – počevši od unutarnjih zagrada i radeći prema van: funkcija INDIRECT vraća skup tekstualnih vrijednosti, u ovom slučaju vrijednosti od 1 do 3. Funkcija ROW zauzvrat generira stupčasti niz s tri stanice. Funkcija LARGE koristi vrijednosti u rasponu ćelija A5: A14 i procjenjuje se tri puta, jednom za svaku referencu koju vraćafunkcija ROW. Vrijednosti 3200, 2700 i 2000 vraćaju se u stupčasto polje s tri ćelije. Ako želite pronaći više vrijednosti, dodajte veći raspon ćelija funkciji INDIRECT.

Kao i u ranijim primjerima, ovu formulu možete koristiti s drugim funkcijama, kao što su SUM i AVERAGE.

Traženje najduljeg tekstnog niza unutar raspona ćelija

Vratite se na raniji primjer tekstualnog niza, u praznu ćeliju unesite sljedeću formulu i pritisnite Ctrl+Shift+Enter:

=INDEX(A2:A6;MATCH(MAX(LEN(A2:A6));LEN(A2:A6);0);1)

Pojavljuje se tekst „skup ćelija koji“.

Sad ćemo analizirati formulu počevši od unutarnjih elemenata prema van. Funkcija LEN vraća duljinu svake stavke u rasponu ćelija A2:A6. Funkcija MAX izračunava najveću vrijednost među tim stavkama koja odgovara najdužem tekstualnom nizu koji se nalazi u ćeliji A3.

Ovdje situacija postaje nešto složenija. Funkcija MATCH izračunava pomak (relativni položaj) ćelije koja sadrži najdulji tekstni niz. Da bi to učinila, potrebna su joj tri argumenta: tražena vrijednost, traženo polje i vrsta podudaranja. Funkcija MATCH pretražuje traženo polje u potrazi za konkretnom traženom vrijednošću. U ovom je slučaju tražena vrijednost najdulji tekstni niz:

(MAX(LEN(A2:A6))

koji se nalazi u ovom polju:

LEN(A2:A6)

Argument vrste podudaranja jest 0. Vrsta podudaranja može se sastojati od vrijednosti 1, 0 ili -1. Ako navedete 1, MATCH vraća najveću vrijednost koja je manja od tražene vrijednosti ili jednaka toj vrijednosti. Ako navedete 0, MATCH vraća prvu vrijednost koja je jednaka traženoj vrijednosti. Ako navedete -1, MATCH vraća najmanju vrijednost koja je veća od navedene tražene vrijednosti ili jednaka toj vrijednosti. Ako izostavite vrstu podudaranja, Excel pretpostavlja da je 1.

Naposljetku, funkcija INDEX preuzima te argumente: polje te broj redaka i stupaca unutar tog polja. Raspon ćelija A2: A6 pruža niz, funkcija MATCH pruža adresu ćelije, a konačni argument (1) određuje da vrijednost dolazi iz prvog stupca u polju.

U ovom se odjeljku nalaze primjeri naprednih formula polja.

Zbrajanje raspona koji sadrži vrijednosti pogreške

Funkcija SUM u programu Excel ne radi kada pokušate zbrojiti raspon koji sadrži vrijednost pogreške, poput # N/A. Ovaj primjer pokazuje kako zbrojiti vrijednosti u rasponu nazvanom „Podaci koji sadrži pogreške“.

=SUM(IF(ISERROR(Podaci);"";Podaci))

Ova formula stvara novo polje koje sadrži izvorne vrijednosti, ali bez vrijednosti pogrešaka. Počevši od unutarnjih funkcija prema van, funkcija ISERROR traži pogreške u rasponu ćelija (Podaci). Funkcija IF vraća određenu vrijednost ako se uvjet koji navedete procijeni kao TRUE, a drugu vrijednost ako se procijeni kao FALSE. U ovom slučaju vraća prazne nizove ("") za sve vrijednosti pogrešaka jer su procijenjene kao TRUE te vraća preostale vrijednosti iz raspona (Podaci) jer su procijenjene kao FALSE, što znači da ne sadrže vrijednosti pogreške. Funkcija SUM zatim izračunava ukupan zbroj filtriranog polja.

Prebrojavanje broja pojavljivanja vrijednosti pogreške unutar raspona

Ovaj primjer sličan je prethodnoj formuli, ali vraća broj pojavljivanja vrijednosti pogreške u rasponu Podaci umjesto da ih filtrira:

=SUM(IF(ISERROR(Podaci);1;0))

Ova formula stvara polje koje sadrži vrijednost 1 za ćelije koje sadrže pogreške, a vrijednost 0 za ćelije koje ne sadrže pogreške. Formulu možete pojednostavniti i postići isti rezultat tako da iz funkcije IF uklonite treći argument, i to ovako:

=SUM(IF(ISERROR(Podaci);1))

Ako ne navedete argument, funkcija IF vraća FALSE ako ćelija ne sadrži vrijednost pogreške. Formulu možete dodatno pojednostavniti:

=SUM(IF(ISERROR(Podaci)*1))

Ova verzija funkcionira jer je TRUE*1=1, a FALSE*1=0.

Zbrajanje vrijednosti na temelju uvjeta

Možda ćete morati zbrojiti vrijednosti na temelju uvjeta. Ova formula polja, primjerice, zbraja samo pozitivne cijele brojeve u rasponu Prodaja:

=SUM(IF(Prodaja>0;Prodaja))

Funkcija IF stvara polje s pozitivnim vrijednostima i netočnim vrijednostima. Funkcija SUM u načelu zanemaruje netočne vrijednosti jer je 0+0=0. Raspon ćelija koje koristite u ovoj formuli može se sastojati od bilo kojeg broja redaka i stupaca.

Možete zbrajati i vrijednosti koje ispunjavaju više uvjeta. Primjerice, ova formula polja izračunava vrijednosti veće od 0, a manje od 5 ili jednake 5:

=SUM((Prodaja>0)*(Prodaja<=5)*(Prodaja))

Imajte na umu da ova formula vraća pogrešku ako raspon sadrži jednu ili više ćelija koje nisu numeričke.

Možete stvoriti i formule polja koje koriste vrstu uvjeta OR. Primjerice, možete zbrojiti vrijednosti manje od 5 i vrijednosti veće od 15:

=SUM(IF((Prodaja<5)+(Prodaja>15);Prodaja))

Funkcija IF pronalazi sve vrijednosti manje od 5 i veće od 15, a zatim prosljeđuje te vrijednosti funkciji SUM.

Ne možete izravno koristiti funkcije AND i OR u formulama polja jer te funkcije vraćaju jedan rezultat, TRUE ili FALSE, a funkcije niza zahtijevaju nizove rezultata. Problem možete zaobići koristeći se logikom prikazanom u prethodnoj formuli. Drugim riječima, izvodite matematičke operacije, poput zbrajanja ili množenja vrijednosti koje zadovoljavaju uvjet ILI ili AND.

Izračunavanje prosjeka u kojem su izostavljene nule

U ovom je primjeru prikazano uklanjanje nula iz raspona kada morate izračunati prosjek vrijednosti unutar raspona. Formula koristi raspon podataka Prodaja:

=AVERAGE(IF(Prodaja<>0;Prodaja))

Funkcija IF stvara polje s vrijednostima koje nisu jednake 0, a zatim prosljeđuje te vrijednosti funkciji AVERAGE.

Brojanje razlika između dvaju raspona ćelija

Ova formula niza uspoređuje vrijednosti u dva raspona ćelija nazvanih MyData i YourData i vraća broj razlika između njih. Ako su sadržaji dva raspona identični, formula vraća 0. Da biste koristili ovu formulu, rasponi ćelija moraju biti iste veličine i iste dimenzije (na primjer, ako je MyData raspon od 3 retka s 5 stupaca, YourData moraju biti i 3 retka s 5 stupaca):

=SUM(IF(MojiPodaci=VašiPodaci,0,1))

Formula stvara novo polje iste veličine kao rasponi koje uspoređujete. Funkcija IF ispunjava polje vrijednošću 0 i vrijednošću 1 (0 za nepodudaranja, a 1 za identične ćelije). Funkcija SUM zatim vraća zbroj vrijednosti u polju.

Formulu možete pojednostavniti ovako:

=SUM(1*(MojiPodaci<>VašiPodaci))

Baš kao i formula kojom se prebrojavaju vrijednosti pogreške unutar raspona, ova formula funkcionira jer je TRUE*1=1, a FALSE*1=0.

Traženje maksimalne vrijednosti unutar raspona

Ova formula polja vraća broj retka u kojem se nalazi maksimalna vrijednost unutar raspona koji se sastoji od jednog stupca s nazivom Podaci:

=MIN(IF(Podaci=MAX(Podaci);ROW(Podaci);""))

Funkcija IF stvara novo polje koje odgovara rasponu Podaci. Ako odgovarajuća ćelija sadrži maksimalnu vrijednost unutar raspona, polje sadrži broj retka. U suprotnom, polje sadrži prazan niz (""). Funkcija MIN koristi novo polje kao drugi argument i vraća najmanju vrijednost, koja odgovara broju retka s maksimalnom vrijednošću u rasponu Podaci. Ako raspon Podaci sadrži identične maksimalne vrijednosti, formula vraća redak prve vrijednosti.

Ako kao rezultat želite dobiti adresu ćelije s maksimalnom vrijednošću, upotrijebite ovu formulu:

=ADDRESS(MIN(IF(Podaci=MAX(Data);ROW(Podaci);""));COLUMN(Podaci))