Smjernice i primjeri vezani uz formule polja

Polja s više ćelija i jednom ćelijom

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
  

• Ovdje izračunavamo ukupnu prodaju kupea i limuzina za svakog prodavača unošenjem =F10: F19 * G10: G19 u ćeliju H10.
  Kada pritisnete tipku Enter, rezultati će se prelijevati do ćelija H10:H19. Primijetite da je područje izlijevanja označeno obrubom kada odaberete bilo koju ćeliju u rasponu izlijevanja. Također biste mogli primijetiti da su formule u stanicama H10:H19 sive. Oni su tu samo za referencu, pa ako želite prilagoditi formulu, morat ćete odabrati ćeliju H10, u kojoj živi glavna formula.

• Formula jednoćelijskog niza
  
  U ćeliju H20 primjera radne knjige unesite ili kopirajte i zalijepite =SUM(F10:F19*G10:G19), a zatim pritisnite Enter.
  U ovom slučaju, Excel množi vrijednosti u polju (raspon ćelija F10 do G19), a zatim koristi funkciju SUM za zbrajanje zbrojeva. 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 H20 potpuno neovisna od višestanične formule (formula u stanicama H10 do H19). To je dodatna prednost korištenja formula polja – fleksibilnost. Ostale formule u stupcu H možete promijeniti bez utjecaja na formulu u H20. Također bi mogla biti dobra praksa imati ovakve neovisne zbrojeve, jer pomaže u potvrđivanju točnosti vaših rezultata.

• Formule dinamičkog niza također nude ove prednosti:

  • Dosljednost Ako kliknete bilo koju ćeliju ispod ćelije H10, prikazat će vam se ista formula. Takva dosljednost jamči veću točnost.
  • Sigurnost Ne možete prebrisati komponentu formule polja s više ćelija. Na primjer, kliknite ćeliju H11 i pritisnite Izbriši. Excel neće promijeniti izlaz polja. Da biste ga promijenili, morate odabrati gornju lijevu ćeliju niza ili ćeliju H10.
  • manje veličine datoteka Umjesto nekoliko posrednih formula često možete koristiti jednu formulu niza. Na primjer, primjer prodaje automobila koristi jednu formulu niza za izračunavanje rezultata u stupcu E. Da ste koristili standardne formule poput =F10*G10, F11*G11, F12*G12, itd., Koristili biste 11 različitih formula za izračunavanje istih rezultata. To nije velika stvar, ali što ako imate ukupno tisuće redaka? Tada to može napraviti veliku razliku.
  • Efikasnost Funkcije niza mogu biti učinkovit način za izgradnju složenih formula. Formula polja =SUM(F10:F19*G10:G19) ista je kao ova: =SUM(F10*G10;F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
  • Prelijevanje Formule dinamičkih polja automatski će se preliti u izlazni raspon. Ako su vaši izvorni podaci u Excel tablici, tada će se vaše formule dinamičkih nizova automatski mijenjati dok dodajete ili uklanjate podatke.
  • Pogreške #SPILL! pogreška Dinamična polja uvela su pogrešku #SPILL!, što znači da je predviđeni raspon izlijevanja iz nekog razloga blokiran. Kada riješite blokadu, formula će se automatski izliti.

Stvorite jednodimenzionalne konstante niza

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} ili ={"Siječanj","Veljača","Ožujak"}

Ako razdvojite stavke zarezima, stvarate vodoravno polje (redak). Ako stavke razdvojite točkama sa zarezom, stvarate okomito polje (stupac). Da biste stvorili dvodimenzionalni niz, stavke u svakom retku razgraničite zarezima, a svaki redak zarezom.

Sljedećim postupcima steći ćete nešto prakse u stvaranju vodoravnih, okomitih i dvodimenzionalnih konstanti. Pokazat ćemo primjere korištenja funkcije SEQUENCE za automatsko generiranje konstanti niza, kao i ručno unesene konstante niza.

• Stvaranje vodoravne konstante
  Upotrijebite radnu knjigu iz prijašnjih primjera ili stvorite novu radnu knjigu. Odaberite bilo koju praznu ćeliju i unesite =SEQUENCE(1;5). Funkcija SEQUENCE sastavlja polje od 1 retka s 5 stupaca isto kao i ={1,2,3,4,5}. Prikazuje se sljedeći rezultat:
  
• Stvaranje okomite konstante
  Odaberite bilo koju praznu ćeliju s prostorom ispod nje i unesite =SEQUENCE(5)ili ={1;2;3;4;5}. Prikazuje se sljedeći rezultat:
  
• Stvaranje dvodimenzionalne konstante
  Odaberite bilo koju praznu ćeliju s prostorom desno i ispod nje i unesite =SEQUENCE(3,4). Prikazat će vam se sljedeći rezultat:
  
  Također možete unijeti: ili ={1\2\3\4;5\6\7\8;9\10\11\12}, ali morat ćete obratiti pažnju na to gdje stavljate zarez i zarez.
  Kao što vidite, mogućnost SEQUENCE nudi značajne prednosti u odnosu na ručni unos vrijednosti konstante polja. Prvenstveno vam štedi vrijeme, ali također može pomoći u smanjenju pogrešaka zbog ručnog unosa. Također je lakše čitati, pogotovo jer je točku i zarez teško razlikovati od separatora zareza.

Sintaksa konstante polja

Evo primjera koji koristi konstante niza kao dio veće formule. U oglednoj radnoj knjizi prijeđite na konstantu na radnom listu formule ili stvorite novi radni list.

U ćeliju D9 unijeli smo =SEQUENCE(1;5;3;1), ali mogli ste unijeti i 3, 4, 5, 6 i 7 u ćelije A9:H9. U tom određenom odabiru broja nema ništa posebno, mi smo samo odabrali nešto drugo osim 1-5 za razlikovanje.

U ćeliju E11 unesite =SUM(D9:H9*SEQUENCE(1;5)), ili =SUM(D9:H9*{1,2,3,4,5}). Formule vraćaju 85.

Funkcija SEQUENCE sastavlja ekvivalent konstante {1,2,3,4,5}polja. Budući da Excel prvo izvršava operacije nad izrazima zatvorenim u zagradama, sljedeća dva elementa koja dolaze u obzir su vrijednosti ćelija u D9:H9 i operator množenja (*). U toj fazi formula množi vrijednosti iz pohranjenog polja s odgovarajućim vrijednostima u konstanti. To odgovara sljedećem:

=SUM(D9*1,E9*2;F9*3;G9*4,H9*5)ili =SUM(3*1;4*2,5*3,6*4,7*5)

Konačno, funkcija SUM dodaje vrijednosti i vraća 85.

Da biste izbjegli upotrebu pohranjenog niza i operaciju zadržali u potpunosti u memoriji, možete je zamijeniti drugom konstantom niza:

=SUM(SEQUENCE(1;5;3;1)*SEQUENCE(1;5))) ili =SUM({3,4,5,6,7}*{1,2,3,4,5})

Elementi koje možete koristiti u konstantama polja

• 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, morate ga okružiti navodnicima („tekst“).
• 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.

Dodjela naziva konstantama polja

Jedan od najboljih načina upotrebe konstanti niza je imenovanje. 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:

Prelazak na formule Definirani nazivi>>definiraju naziv. U okvir Naziv upišite Tromjesečje1. U okvir Odnosi se na unesite sljedeću konstantu (ne zaboravite ručno upisati vitičaste zagrade):

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

Dijaloški okvir sada bi trebao izgledati ovako:

Kliknite U redu, a zatim odaberite bilo koji redak s tri prazne ćelije i unesite =Quarter1.

Prikazuje se sljedeći rezultat:

Ako želite da se rezultati prelijevaju okomito, a ne vodoravno, koristite =funkciju TRANSPOSE(Quarter1).

Ako želite prikazati popis od 12 mjeseci, kakav biste mogli koristiti prilikom izrade financijskog izvješća, možete ga temeljiti na tekućoj godini pomoću funkcije SEQUENCE. Zgodna stvar ove funkcije je da, iako se prikazuje samo mjesec, iza nje stoji valjani datum koji možete koristiti u drugim izračunima. Ove ćete primjere pronaći na radnim listovima konstante imenovanog polja i brzog oglednog skupa podataka u oglednoj radnoj knjizi.

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

To koristi funkciju DATE za stvaranje datuma na temelju tekuće godine, SEQUENCE stvara konstantu niza od 1 do 12 za razdoblje od siječnja do prosinca, a zatim funkcija TEXT pretvara format prikaza u „mmm“ (siječanj, veljača, ožujak itd.). Ako želite prikazati puni naziv mjeseca, npr. siječanj, upotrijebite „mmmm“.

Kada koristite imenovanu konstantu kao formulu niza, ne zaboravite unijeti znak jednakosti, kao u =Quarter1, a ne samo Quarter1. 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 funkcije, teksta i brojeva. Sve ovisi o tome koliko želite biti kreativni.

Konstante polja na djelu

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
  Unesite =SEQUENCE(1;12)*2ili ={1\2\3\4;5\6\7\8;9\10\11\12}*2
  Možete i podijeliti s (/), zbrajati s (+) i oduzimati s (-).
• Kvadriranje stavki u polju
  Unesite =SEQUENCE(1,12)^2ili ={1\2\3\4;5\6\7\8;9\10\11\12}^2
• Pronađite kvadratni korijen kvadratnih stavki u nizu
  Unesite =SQRT(SEQUENCE(1,12)^2) ili =SQRT({1,2,3,4; 5,6,7,8; 9,10,11,12}^2)
• Transponiranje jednodimenzionalnog retka
  Unesite =TRANSPOSE(SEQUENCE(1;5)) ili =TRANSPOSE({1,2,3,4,5})
  Iako ste unijeli konstantu vodoravnog polja, funkcija TRANSPOSE konstantu polja pretvara u stupac.
• Transponiranje jednodimenzionalnog stupca
  Unesite =TRANSPOSE(SEQUENCE(5,1)), ili =TRANSPOSE({1;2;3;4;5})
  Iako ste unijeli konstantu okomitog polja, funkcija TRANSPOSE konstantu pretvara u redak.
• Transponiranje dvodimenzionalne konstante
  Unesite =TRANSPOSE(SEQUENCE(3,4)), ili =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})
  Funkcija TRANSPOSE pretvara svaki redak u niz stupaca.

Pokretanje osnovnih formula polja

U ovom se odjeljku nalaze primjeri osnovnih formula polja.

• Stvaranje polja iz postojećih vrijednosti
  Sljedeći primjer objašnjava kako koristiti formule niza za stvaranje novog niza od postojećeg niza.
  Unesite =SEQUENCE(3,6,10,10), ili ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}
  Obavezno upišite { (otvorenu vitičastu zagradu) ispred 10 i } (zatvorenu vitičastu zagradu) iza 180 jer stvarate polje brojeva.
  Zatim unesite =D9#ili =D9:I11 u praznu ćeliju. Pojavljuje se niz stanica 3 x 6 s istim vrijednostima koje vidite u D9: D11. Znak # naziva se operator prolivenog raspona i to je način na koji Excel referencira cijeli raspon polja, umjesto da ga morate upisati.
  

• Stvaranje konstante polja iz postojećih vrijednosti
  Možete uzeti rezultate formule prolivenog niza i pretvoriti ih u njegove sastavne dijelove. Odaberite ćeliju D9, a zatim pritisnite F2 za prelazak u način uređivanja. Zatim pritisnite F9 da biste reference ćelija pretvorili u vrijednosti, koje Excel zatim pretvara u konstantu polja. Kada pritisnete Enter, formula =D9#, sada bi trebala biti ={10\20\30;40\50\60;70\80\90}.

• Brojanje znakova u rasponu ćelija
  U sljedećem primjeru prikazano je brojanje znakova u rasponu ćelija. To obuhvaća razmake.
  
  =SUM(LEN(C9:C13))
  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). Ako želite dobiti prosječan broj znakova, možete koristiti:
  =AVERAGE(LEN(C9:C13))

• Sadržaj najduže ćelije u rasponu C9:C13
  =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)
  Ta formula funkcionira samo ako raspon podataka sadrži jedan stupac ćelija.
  Sad ćemo analizirati formulu počevši od unutarnjih elemenata prema van. Funkcija LEN vraća duljinu svake stavke u rasponu ćelija D2:D6. Funkcija MAX izračunava najveću vrijednost među tim stavkama koja odgovara najdužem tekstualnom nizu koji se nalazi u ćeliji D3.
  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(C9:C13)
  koji se nalazi u ovom polju:
  LEN(C9:C13)
  Argument vrste podudaranja u ovom je slučaju 0. Vrsta podudaranja može biti vrijednost 1, 0 ili -1.

  • 1 – vraća najveću vrijednost koja je manja od tražene vrijednosti ili jednaka toj vrijednosti
  • 0 – vraća prvu vrijednost koja je jednaka traženoj vrijednosti.
  • 1 – 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.

  Napokon, funkcija INDEX uzima ove argumente: niz i broj retka i stupca unutar tog polja. Raspon ćelija C9:C13 pruža niz, funkcija MATCH pruža adresu ćelije, a završni argument (1) određuje da vrijednost dolazi iz prvog stupca u polju.
  Ako želite dobiti sadržaj najmanjeg tekstnog niza, zamijenili biste MAX u gornjem primjeru s MIN.

• Traženje n najmanjih vrijednosti unutar raspona
  Ovaj primjer pokazuje kako pronaći tri najmanje vrijednosti u rasponu ćelija, gdje je stvoren niz podataka uzorka u ćelijama B9: B18 sa: =INT (RANDARRAY(10,1)* 100). Imajte na umu da je RANDARRAY hlapljiva funkcija, pa ćete svaki put kada Excel izračuna izračunati dobiti novi skup slučajnih brojeva.
  
  Unesite =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})
  Ova formula koristi konstantu niza za tri puta procjenu SMALL funkcije i vraćanje najmanje 3 člana u nizu koji se nalazi u stanicama B9:B18, gdje je 3 promjenljiva vrijednost u ćeliji D9. Da biste pronašli više vrijednosti, možete povećati vrijednost u funkciji SEQUENCE ili dodati više argumenata konstanti. S tom formulom možete koristiti i dodatne funkcije, kao što su SUM ili AVERAGE. Na primjer:
  =SUM(SMALL(B9#;SEQUENCE(D9))
  =AVERAGE(SMALL(B9#,SEQUENCE(D9))

• 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, sljedeći primjer koristi funkcije ROW i INDIRECT.
  Unesite =LARGE(B9#,ROW(INDIRECT("1:3")))) ili =LARGE(B9:B18,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 prazno i unesite:
  =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 sada 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 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. Možete jednostavno koristiti sequence:
  =SEQUENCE(10)
  Ispitajmo formulu koju ste ranije koristili – LARGE(B9#,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 generira polje s tri ćelije. Funkcija LARGE koristi vrijednosti u rasponu ćelija B9: B18 i procjenjuje se tri puta, jednom za svaku referencu koju vraća funkcija ROW. Ako želite pronaći više vrijednosti, dodajte veći raspon ćelija funkciji INDIRECT. I na kraju, kao i kod MALIH primjera, ovu formulu možete koristiti i s drugim funkcijama, poput SUM i AVERAGE.

Rješavanje pogrešaka

• 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, kao što je #VALUE! ili #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 kao prethodna formula, 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.

Na primjer, ova formula niza zbraja samo pozitivne cijele brojeve u rasponu nazvanom Prodaja, koji predstavlja ćelije E9:E24 u gornjem primjeru:

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

Funkcija IF stvara polje s pozitivnim 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 I manje od 2500:

=SUM((Prodaja>0)*(Prodaja<2500)*(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 veće od 0 OR manje od 2500:

=SUM(IF((Prodaja>0)+(Prodaja<2500);Prodaja))

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.

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.

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))

Slične ćete primjere pronaći u uzorku radne knjige o radnom listu Razlike između skupova podataka.

Formule polja s više ćelija i s jednom ćelijom

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.

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 ({ }) te instancu formule umetnuti 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 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.

Sintaksa formule polja

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).

Unos i mijenjanje formula polja

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.

Proširivanje formule polja

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 da 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.

Nedostaci korištenja formula polja

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.

Upoznavanje s konstantama polja

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.

Stvaranje jednodimenzionalnih i dvodimenzionalnih konstanti

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 slučaju trebate upisati lijevu i desnu vitičastu zagradu ({ }), 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.

Sintaksa konstante polja

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})

Elementi koje možete koristiti u konstantama

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.

Dodjela naziva konstantama polja

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.

Otklanjanje poteškoća s konstantama polja

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.

Konstante polja na djelu

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.

Pokretanje osnovnih formula polja

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.

Pokretanje naprednih formula polja

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))