Smjernice i primjeri vezani uz formule polja

Smjernice i primjeri vezani uz formule polja

Da biste postali napredni korisnik programa Excel, morate znati kako koristiti formule polja koje mogu izvršavati izračune koje ne možete izvršiti pomoću drugih formula. Sljedeći je članak utemeljen na nizu kolumni za napredne korisnike programa Excel koje napisao Colin Wilcox i preuzet je iz poglavlja 14 i 15 knjige Formule programa Excel 2002 koju je napisao John Walkenbach, MVP za Excel.

Upoznavanje s formulama polja

Formule polja često se nazivaju i CSE (Ctrl+Shift+Enter) formulama jer ih umjesto pritiskom samo na Enter dovršavate pritiskom na Ctrl+Shift+Enter.

Zašto koristiti formule polja?

Ako ste već koristili formule u programu Excel, poznato vam je da pomoću njih možete izvršavati neke prilično sofisticirane operacije. Tako možete izračunati ukupni trošak kredita tijekom određenog broja godina, a pomoću formula polja možete obavljati i složene zadatke kao što su sljedeći:

  • 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

Kratko upoznavanje s rasponima i formulama polja

Formula polja jest formula kojom je moguće izvršiti više izračuna na jednoj stavci ili više stavki unutar polja. Polje zamislite kao redak vrijednosti, stupac vrijednosti ili pak kao kombinaciju redaka i stupaca vrijednosti. Formule polja mogu dati više rezultata ili jedan rezultat. Formulu polja tako možete stvoriti u rasponu ćelija te pomoću nje izračunati stupac ili redak podzbrojeva. Formulu polja možete smjestiti i u jednu ćeliju pa izračunati jedan iznos. Formula polja koja obuhvaća više ćelija naziva se formulom s više ćelija, a formula polja u jednoj ćeliji naziva se formulom s jednom ćelijom.

Na primjerima u sljedećem odjeljku prikazano je stvaranje formula polja s više ćelija i s jednom ćelijom.

Isprobajte!

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

Evo jedne radne knjige ugrađene u web-preglednik. Premda sadrži ogledne podatke, imajte na umu da u ugrađenoj radnoj knjizi ne možete stvarati ni mijenjati formule polje, već vam je za to potreban program Excel. U ugrađenoj radnoj knjizi možete vidjeti odgovore i tekst koji objašnjava funkcioniranje formule polja, no da biste doista shvatili moć formula polje, morate radnu knjigu otvoriti u programu Excel.

Stvaranje formule polja s više ćelija

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

    Prodajni
    predstavnik

    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)

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

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

Radnu knjigu možete preuzeti klikom na zeleni gumb programa Excel na crnoj traci pri dnu radne knjige. Zatim datoteku možete otvoriti u programu Excel, odabrati ćelije koje sadrže formule polja te pritisnuti Ctrl+Shift+Enter da bi formule profunkcionirale.

Ako istodobno radite u programu Excel, provjerite je li aktivan List1 te odaberite ćelije E2:E11. Pritisnite F2 i u trenutnu ćeliju E2 upišite formulu =C2:C11*D2:D11. Ako pritisnete Enter, vidjet ćete da je formula unesena samo u ćeliju E2 te da se kao rezultat prikazuje 165000. Zato nakon upisivanja formule nemojte pritisnuti Enter, već Ctrl+Shift+Enter. Sad ćete rezultate vidjeti u ćelijama E2:E11. Kao što vidite, formula u traci formule prikazuje se kao {=C2:C11*D2:D11}. Po tome znate da se radi o formuli polja, kao što je prikazano u tablici u nastavku.

Kada pritisnete Ctrl+Shift+Enter, Excel okružuje formulu vitičastim zagradama ({ }) te instancu formule umeće 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 F10 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.

Imajte na umu i da je formula s jednom ćelijom (u ćeliji G11) u potpunosti neovisna o formuli s više ćelija (formuli u ćelijama od 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 G11.

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 Delete. Morat ćete odabrati cijeli raspon ćelija (od E2 do E11) i promijeniti formulu za čitav raspon ili ostaviti polje nepromijenjeno. Kao dodatnu sigurnosnu mjeru, za potvrdu promjene formule morate pritisnuti kombinaciju tipki Ctrl+Shift+Enter.

  • Manje datoteke    Jednom formulom polja često možete zamijeniti nekoliko međuformula. U ovoj radnoj knjizi, primjerice, koristi se jedna formula polja za izračun rezultata u stupcu E. Da ste koristili standardne formule (npr. =C2*D2, C3*D3, C4*D4...), za izračun istih rezultata trebali biste jedanaest različitih formula.

Sintaksa formule polja

U formulama polja u načelu se koristi standardna sintaksa formula. Sve započinju znakom jednakosti (=), a u njima možete koristiti većinu ugrađenih funkcija programa Excel. Ključna je razlika u tome što je pri korištenju formule polja za unos formule potrebno pritisnuti kombinaciju tipki Ctrl+Shift+Enter. Kada to učinite, Excel će okružiti formulu polja vitičastim zagradama. Ako vitičaste zagrade unesete ručno, vaša će se formula pretvoriti u tekstni niz te neće funkcionirati.

Funkcije polja doista su učinkovit način sastavljanja složene formule. 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

Važno    Uvijek kada želite unijeti ili urediti formulu polja, pritisnite Ctrl+Shift+Enter. 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 polja, odaberite čitavu formulu (npr. =C2:C11*D2:D11), pritisnite tipku Delete, a zatim pritisnite kombinaciju tipki Ctrl+Shift+Enter.

  • 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. To nije kompliciran postupak, ali se pritom svakako pridržavajte gore navedenih smjernica.

Na ovom smo radnom listu dodali još redaka s podacima o prodaji – u retke od 12 do 17. Sada želimo ažurirati formule polja da bi obuhvatile i te dodatne retke.

Ovaj postupak obavljajte u stolnoj verziji programa Excel (nakon što radnu knjigu preuzmete na računalo).

Proširivanje formule polja

  1. Kopirajte ovu cijelu tablicu u ćeliju A1 radnog lista programa Excel.

    Prodajni
    predstavnik

    Vrsta
    automobila

    Broj
    prodanih

    Jedinična
    cijena

    Ukupna
    prodaja

    Šašić

    limuzina

    5

    33000

    165000

    coupe

    4

    37000

    148000

    Makovac

    limuzina

    6

    24000

    144000

    coupe

    8

    21000

    168000

    Jurić-Sedić

    limuzina

    3

    29000

    87000

    coupe

    1

    31000

    31000

    Pavičić

    limuzina

    9

    24000

    216000

    coupe

    5

    37000

    185000

    Abrus

    limuzina

    6

    33000

    198000

    coupe

    8

    31000

    248000

    Grčić

    limuzina

    2

    27000

    coupe

    3

    30000

    Mihelčić

    limuzina

    4

    22000

    coupe

    1

    41000

    Miler

    limuzina

    5

    32000

    coupe

    3

    36000

    Sveukupno

  2. Odaberite ćeliju E18, u ćeliju A20 unesite formulu za sveukupni iznos =SUM(C2:C17*D2:D17) pa pritisnite Ctrl+Shift+Enter.
    Rezultat bi morao biti 2 131 000.

  3. Odaberite raspon ćelija koji sadrži trenutnu formulu polja (E2:E11) i prazne ćelije (E12:E17) pokraj novih podataka. Drugim riječima, odaberite ćelije E2:E17.

  4. Pritisnite tipku F2 da biste prešli u način za uređivanje.

  5. Na traci formule promijenite C11 u C17 i D11 u D17, a zatim pritisnite kombinaciju Ctrl+Shift+Enter.
    Excel ažurira formulu u ćelijama od E2 do E11 i smješta instancu formule u nove ćelije, od E12 do E17.

  6. U ćeliju F17 upišite formulu polja = SUM(C2:C17*D2*D17) tako da se odnosi na ćelije od retka 2 do 17, a zatim pritisnite Ctrl+Shift+Enter da biste unijeli formulu polja.
    Novi ukupni zbroj morao bi biti 2 131 000.

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 znate da će druge osobe morati mijenjati vaše radne knjige, trebali biste izbjegavati formule polja ili provjeriti jesu li ti korisnici upoznati s formulama polja te znaju li ih promijeniti ako bude potrebno.

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

Vrh stranice

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}, ovo je polje u jednom stupcu: {1;2;3;4}, a ovo je polje od dva retka i četiri stupca: {1,2,3,4;5,6,7,8}. U polju od dva retka prvi je redak 1, 2, 3 i 4, a drugi je redak 5, 6, 7 i 8. Ta su dva retka razdvojena jednom točkom sa zarezom koja se nalazi 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.

Vrh stranice

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. Upotrijebite radnu knjigu iz prijašnjih primjera ili stvorite novu radnu knjigu.

  2. Odaberite ćelije od A1 do E1.

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

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

    U ovom slučaju morate upisati lijevu i desnu vitičastu zagradu ({ }).

    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

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.

Sintaksa konstante polja

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

Elementi koje možete koristiti u konstantama

Konstante polja mogu sadržavati brojeve, tekst, logičke vrijednosti (kao što su TRUE i FALSE) te vrijednosti pogrešaka (npr. #N/A). Brojeve možete koristiti u cjelobrojnom, decimalnom i znanstvenom obliku. Ako uvrštavate tekst, morate ga staviti u navodnike (").

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:

    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.

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 točku sa zarezom ili ih pak stavite na pogrešno mjesto, konstanta polja neće se ispravno stvoriti ili će vam se prikazati poruka 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.

Vrh stranice

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.
    Formula polja i dalje bi morala biti = C8:E10.

  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}, baš kao i u ćelijama C8:E10.

  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.

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

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

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)

Sljedeća formula u ćeliji A9 broji ukupan broj znakova (48) 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 (48) u ćeliji A9, koja sadrži formulu.

Traženje n najmanjih vrijednosti unutar raspona

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

  1. Odaberite ćelije od A16 do A18.
    U tom će se skupu ćelija nalaziti rezultati koje dobije formula polja.

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

    =SMALL(A5:A14,{1;2;3})

U ćelijama od A16 do A18 redom se pojavljuju vrijednosti 400, 475 i 500.

Ova formula koristi konstantu polja za trostruki izračun funkcije SMALL te vraća najmanji (1), drugi najmanji (2) i treći najmanji (3) član polja koje je sadržano u ćelijama A1:A10. Da biste pronašli još vrijednosti, u konstantu morate dodati još argumenata, kao i odgovarajući broj ćelija s rezultatima u raspon A12:A14. S tom formulom možete koristiti i dodatne funkcije, kao što su SUM ili AVERAGE. Na primjer:

=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ,{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 A1 do A3.

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

    =LARGE(A5:A14,ROW(INDIRECT("1:3")))

U ćelijama od A1 do A3 redom se pojavljuju vrijednosti 3200, 2700 i 2000.

U ovoj je fazi korisno znati ponešto o funkcijama ROW i INDIRECT. Pomoću funkcije ROW možete stvoriti polje uzastopnih cijelih brojeva. Primjerice, u radnoj knjizi za vježbu odaberite prazan stupac koji se sastoji od deset ćelija, unesite ovu formulu polja u ćelije A5:A14, 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.

Proučit ćemo formulu koju ste prije koristili – =LARGE(A5:A14;ROW(INDIRECT("1:3"))) – počevši od unutarnje zagrade prema van: funkcija INDIRECT vraća skup tekstnih vrijednosti, u ovom slučaju vrijednosti od 1 do 3. Funkcija ROW zatim generira polje organizirano u stupce koje se sastoji od tri ćelije. Funkcija LARGE koristi vrijednosti iz raspona ćelija A5:A14, a izračunavaju se tri puta, jedanput za svaku referencu koju vraća funkcija ROW. Vrijednosti 3200, 2700 i 2000 vraćaju se u polje organizirano u stupce koje se sastoji od tri ćelije. Ako želite pronaći još vrijednosti, u funkciju INDIRECT morate dodati veći raspon ćelija.

Naposljetku, tu formulu možete koristiti i s drugim funkcijama, kao što su SUM i AVERAGE.

Traženje najduljeg tekstnog niza unutar raspona ćelija

Ta formula funkcionira samo ako raspon podataka sadrži jedan stupac ćelija. Na Listu3 unesite sljedeću formulu u ćeliju A16, a zatim pritisnite Ctrl+Shift+Enter:

=INDEX(A6:A9,MATCH(MAX(LEN(A6:A9)),LEN(A6:A9),0),1)

U ćeliji A16 pojavljuje se tekst "skup ćelija koji".

Sad ćemo analizirati formulu počevši od unutarnjih elemenata prema van. Funkcija LEN vraća duljinu svake od stavki unutar raspona ćelija A6:A9. Funkcija MAX izračunava najveću vrijednost među tim stavkama koja odgovara najduljem tekstnom nizu koji se nalazi u ćeliji A7.

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( A6 : A9 ))

koji se nalazi u ovom polju:

LEN( A6:A9 )

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 A6:A9 daje to polje, funkcija MATCH daje adresu ćelije, a završni argument (1) navodi da vrijednost dolazi iz prvog stupca polja.

Vrh stranice

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 funkcionira ako pokušavate zbrojiti raspon koji sadrži vrijednost pogreške, npr. #N/A. U ovom je primjeru prikazano zbrajanje vrijednosti u rasponu 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.

U formulama polja ne možete izravno koristiti funkcije AND i OR jer te funkcije vraćaju jedan rezultat, TRUE ili FALSE, a za funkcije polja potrebna su čitava polja s rezultatima. Taj problem možete zaobići pomoću logike prikazane u prethodnoj formuli. Drugim riječima, na vrijednostima koje ispunjavaju uvjete OR ili AND izvršavate matematičke operacije, npr. zbrajanje ili množenje.

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 polja uspoređuje vrijednosti dvaju raspona ćelija, MojiPodaci i VašiPodaci, te vraća broj razlika između tih raspona. Ako je sadržaj ta dva raspona identičan, formula vraća 0. Da biste koristili formulu, rasponi ćelija moraju biti jednake veličine i jednakih dimenzija (npr. ako raspon MojiPodaci obuhvaća tri retka i pet stupaca, VašiPodaci također mora obuhvaćati tri retka i pet 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))

Vrh stranice

Je li vam potrebna dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Dodatni sadržaji

Pregled formula

Proširite svoje vještine
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×