Smjernice i primjeri vezani uz formule polja

Smjernice i primjeri vezani uz formule polja

Formula polja jest formula kojom je moguće izvršiti više izračuna na jednoj stavci ili više stavki unutar polja. Niz možete smatrati retkom ili stupcem vrijednosti ili kombinacijom redaka i stupaca vrijednosti. Formule polja mogu vratiti više rezultata ili jedan rezultat.

Počevši od ažuriranja za Microsoft 365 od rujna 2018., bilo koja formula koja može vratiti više rezultata automatski će ih preliti ili u susjedne stanice. Ovu promjenu u ponašanju prati i nekoliko novih funkcija dinamičkog niza. Formule dinamičkog niza, bez obzira koriste li postojeće funkcije ili funkcije dinamičkog niza, trebaju se unijeti samo u jednu ćeliju, a zatim potvrditi pritiskom na Enter. Ranije formule naslijeđenih nizova prvo trebaju odabrati cijeli raspon izlaza, a zatim potvrditi formulu Ctrl+Shift+Enter. Obično se nazivaju CSE formule.

Formule polja možete koristiti za izvršavanje složenih zadataka, kao što su:

  • Brzo stvorite ogledne skupove podataka.

  • Brojanje znakova sadržanih u rasponu ćelija.

  • Zbrajanje samo onih brojeva koji ispunjavaju određene uvjete, primjerice najnižih vrijednosti unutar raspona ili brojeva između gornje i donje granice.

  • Zbrajanje svake N-te vrijednosti unutar raspona vrijednosti.

Sljedeći primjeri pokazuju vam kako stvoriti formule niza s više ćelija i jednoćelija. Gdje je to moguće, uključili smo primjere s nekim funkcijama dinamičkog niza, kao i postojeće formule niza koje su unesene kao dinamički i naslijeđeni nizovi.

Preuzimanje naših primjera

Preuzmite primjer radne knjige sa svim primjerima formule polja u ovom članku..

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

    Funkcija polja s više ćelija u ćeliji H10 =F10:F19*G10:G19 za izračun broja automobila prodanih po jediničnoj cijeni

  • 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

    Formula polja s jednom ćelijom za izračun sveukupnog zbroja s formulom =SUM(F10:F19*G10:G19)

    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    (Consistency – C) 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 niza 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.

    • Učinkovitost    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).

    • Prelivanje    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.

    • #SPILL! error    Dinamična polja uvela su #SPILL! pogrešku, što znači da je predviđeni raspon izlijevanja iz nekog razloga blokiran. Kada riješite blokadu, formula će se automatski izliti.

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 ={"January"\"February"\"March"}

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:

    Stvori konstantu vodoravnog polja s formulom =SEQUENCE(1,5) ili ={1,2,3,4,5}

  • 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:

    Stvori konstantu vodoravnog polja s formulom =SEQUENCE(5) ili {1;2;3;4;5}

  • 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:

    Stvaranje konstante polja s 3 retka s 4 stupca s formulom =SEQUENCE(3;4)

    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.

Evo primjera koji koristi konstante niza kao dio veće formule. U uzorku radne knjige idite na Konstanta u 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.

Korištenje konstanti polja u formulama polja. U ovom smo primjeru koristili =SUM(D9:H(*SEQUENCE(1;5))

Funkcija SEQUENCE sastavlja ekvivalent konstante polja {1\2\3\4\5}. 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.

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:

Odaberite Formule > Definiraj nazive > Definiraj 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:

Dodavanje imenovane konstante polja iz formula > Definirani nazivi > Upravitelj naziva > Novo

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

Prikazuje se sljedeći rezultat:

U formuli koristite imenovanu konstantu polja, npr. =Quarter1, u kojoj je tromjesečje1 definirano kao ={"Siječanj","Veljača","Ožujak"}

Ako želite da se rezultati prelijevaju okomito, a ne vodoravno, možete upotrijebiti = 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 polja Named i Quick sample u radnoj knjizi.

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

Koristite kombinaciju funkcija TEXT, DATE, YEAR, TODAY i SEQUENCE, da biste sastavili dinamični popis od 12 mjeseci

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.

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.

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.

    Koristite operator prelijevanja raspona (#) za pozivanje na postojeće polje

  • 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 niza. 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.

    Prebrojite ukupan broj znakova u rasponu i drugih polja za rad s tekstualnim nizovima podataka

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

    Formula polja programa Excel za pronalaženje N-te najmanje vrijednosti: =SMALL(B9#,SEQUENCE(D9))

    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.

  • 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“:

    Koristite polja, da biste se riješili pogreške. Na primjer, =SUM(IF(ISERROR(Data),"",Data) zbraja raspon pod nazivom Podaci, čak i ako uključuje pogreške, kao što je #VALUE! ili #NA!.

  • =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.

Možda ćete morati zbrojiti vrijednosti na temelju uvjeta.

Polja možete koristiti za izračun na temelju određenih uvjeta. =SUM(IF(Prodaja>0;Prodaja)) zbrojiti će sve vrijednosti veće od 0 u rasponu Prodaje.

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. Na primjer, ova formula niza izračunava vrijednosti veće od 0 I manje od 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

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. Na primjer, možete zbrojiti vrijednosti koje su veće od 0 ILI manje od 2500:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

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.

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.

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}

Ukupan zbroj u stupcu E izračunava se pomoću formule polja

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

Važno: Pritisnite Ctrl+Shift+Enter kad god trebate unijeti formulu niza. To se odnosi i na formule s jednom ćelijom i na formule s više ćelija.

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.

    Konstanta vodoravnog polja u formuli

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.

    Konstanta okomitog polja u formuli polja u formuli

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:

    Konstanta dvodimenzionalnog polja u formuli polja

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.

    Formula polja s konstantom 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.

Sintaksa formule polja s konstantom polja

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:

    dijaloški okvir uređivanje naziva s formulom

  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.

    imenovano polje uneseno kao formula

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

Priznanje

Dijelovi ovog članka temeljili su se na nizu stupaca Excel Power User-a koje je napisao Colin Wilcox, a prilagođeni su poglavljima 14 i 15 programa Excel 2002 Formulas, knjizi koju je napisao John Walkenbach, bivši Excel MVP.

Je li vam potrebna dodatna pomoć?

Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednici za odgovore.

Pogledajte i sljedeće

Dinamička polja i prelijevanje polja

Dinamičke formule polja u odnosu na naslijeđene formule nizova CSE

Funkcija FILTER

Funkcija RANDARRAY

Funkcija SEQUENCE

Funkcija SORT

Funkcija SORTBY

Funkcija UNIQUE

Pogreške #SPILL! u programu Excel

Implicitni operator presjeka: @

Pregled formula

Potrebna vam je dodatna pomoć?

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

×