Smjernice i primjeri vezani uz formule polja

Smjernice i primjeri vezani uz formule polja

Napomena: Željeli bismo vam pružiti najnoviji sadržaj pomoći što je brže moguće i to na vašem jeziku. Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Možete li nam pri dnu ove stranice javiti jesu li vam ove informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku.

Formula polja formula je koja može obavljati više izračuna na jednoj ili više stavki u polju. Polje možete smisliti kao redak ili stupac vrijednosti ili kombinaciju redaka i stupaca vrijednosti. Formule polja mogu vratiti više rezultata ili jedan rezultat.

Počevši od rujna 2018 ažuriranje za Office 365, bilo koja formula koja može vratiti više rezultata automatski će ih proliti prema dolje ili u susjedne ćelije. Ta promjena u ponašanju popraćena je i s nekoliko novih funkcija dinamičke polja. Dinamičke formule polja, bez obzira na to koriste li postojeće funkcije ili dinamičke funkcije polja, potrebno je samo unijeti u jednu ćeliju, a zatim potvrditi pritiskom na Enter. Prethodno, naslijeđene formule polja moraju najprije odabrati cijeli izlazni raspon, a zatim potvrditi formulu CTRL + SHIFT + ENTER. Obično se spominju kao CSE formule.

Formule polja možete koristiti za izvođenje složenih zadataka, kao što su:

  • Brzo stvorite ogledne skupove podataka.

  • Prebrojavanje znakova sadržanih u rasponu ćelija.

  • Zbrajanje samo brojeva koji zadovoljavaju određene uvjete, kao što su najniže vrijednosti u rasponu ili brojevi koji padaju između gornje i donje granice.

  • Zbrajanje svake NTH vrijednosti u rasponu vrijednosti.

Sljedeći primjeri prikazuju vam kako stvoriti formule polja s više ćelija i jednostrukim ćelijama. Kada je to moguće, uvrstili smo primjere u neke dinamičke funkcije polja, kao i postojeće formule polja koje su unesene kao dinamičke i naslijeđene polja.

Preuzimanje naših primjera

Preuzmite oglednu radnu knjigu 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 više ćelija u ćeliji H10 = F10: F19 * G10: G19 da biste izračunali broj automobila koji se prodaju po jediničnom cijenom

  • Ovdje izračunavamo ukupnu prodaju kupee i limuzine za svakog prodavača unosom = F10: F19 * G10: G19 u ćeliji H10.

    Kada pritisnete Enter, prikazat će se rezultati u ćelijama H10: H19. Primjetite da je raspon prolijevanja istaknut obrubom kada odaberete bilo koju ćeliju unutar raspona prolijevanja. Mogli biste primijetiti i da su formule u ćelijama H10: H19 zasivljene. Oni su samo tu za referencu, pa ako želite prilagoditi formulu, morat ćete odabrati ćeliju H10, gdje živi matrica formula.

  • Formula polja s jednom ćelijom

    Formula polja s jednom ćelijom da bi izračunao ukupni zbroj sa = SUM (F10: F19 * G10: G19)

    U ćeliji H2O primjere radne knjige upišite 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 ukupnih 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. Primjetite i da je formula s jednom ćelijom u ćeliji H2O potpuno neovisna o formuli s više ćelija (formula u ćelijama od 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 programu H2O. Može biti i dobra praksa imati nezavisne zbrojeve kao što je ovaj, jer olakšava provjeru točnosti rezultata.

  • Dinamičke formule polja nude i ove prednosti:

    • Dosljednost    Ako kliknete bilo koju ćeliju iz programa H10 prema dolje, prikazat će se ista formula. Takva dosljednost jamči veću točnost.

    • Sigurnost    Ne možete prepisati komponentu formule polja s više ćelija. Primjerice, kliknite Cell H11, a zatim pritisnite DELETE. Excel neće promijeniti izlaz polja. Da biste ga promijenili, morate odabrati gornju lijevu ćeliju u polju ili ćeliju H10.

    • Manja veličina datoteka    Možete često koristiti jednu formulu polja umjesto nekoliko međuformulama. Na primjer, primjer prodaje automobila koristi jednu formulu polja da bi izračunao rezultate u stupcu E. Ako ste koristili standardne formule kao što su = F10 * G10, F11 * G11, F12 * G12 itd., koristili biste 11 formula za izračun istih rezultata. To nije velika stvar, ali što ako ste imali tisuće redaka do ukupno? Onda može napraviti veliku razliku.

    • Učinkovitost    Funkcija Array može biti učinkovit način izgradnje složenih formula. Formula polja = SUM (F10: F19 * G10: G19) ista je kao i ova: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * S14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19).

    • Prolijevanje    Dinamičke formule polja automatski će se proliti u izlazni raspon. Ako se izvorišni podaci nalaze u tablici programa Excel, vaše će se dinamičke formule polja automatski promijeniti dok dodajete ili uklanjate podatke.

    • #SPILL! pogreške    Dinamička polja predstavila su #SPILL! pogreška, što upućuje na to da je predviđen raspon prolijevanja blokiran iz nekog razloga. Kada riješite blokadu, formula će se automatski proliti.

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ču", "Ožujak"}

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 u svakom retku možete razvrstati zarezima, a svaki redak razvrstati točkom sa zarezom.

Sljedećim postupcima steći ćete nešto prakse u stvaranju vodoravnih, okomitih i dvodimenzionalnih konstanti. Prikazat će nam se primjeri pomoću funkcije slijed za automatsko generiranje konstanti polja, kao i ručno unesene konstante polja.

  • Stvaranje vodoravne konstante

    Upotrijebite radnu knjigu iz prijašnjih primjera ili stvorite novu radnu knjigu. Odaberite bilo koju praznu ćeliju i unesite = slijed (1, 5). Funkcija slijed stvara 1 redak za 5 stupaca, isto kao = {1; 2; 3; 4; 5}. Prikazat će se sljedeći rezultat:

    Stvorite vodoravnu konstantu polja uz = slijed (1, 5) ili = {1; 2; 3; 4; 5}

  • Stvaranje okomite konstante

    Odaberite praznu ćeliju s prostorom ispod nje, a zatim unesite = slijed (5)ili = {1; 2; 3; 4; 5}. Prikazat će se sljedeći rezultat:

    Stvaranje okomite konstante polja uz = slijed (5) ili = {1; 2; 3; 4; 5}

  • Stvaranje dvodimenzionalne konstante

    Odaberite praznu ćeliju s desnom i ispod nje, a zatim unesite = slijed (3; 4). Prikazat će vam se sljedeći rezultat:

    Stvaranje 3 retka s 4 stupaca konstante polja s = slijed (3; 4)

    Možete i unijeti: ili = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, ali ćete htjeti obratiti pozornost na to gdje ste postavili polu-kolons naspram zareza.

    Kao što možete vidjeti, mogućnost sekvenca nudi značajne prednosti u odnosu na ručnu unos konstantnih vrijednosti polja. Prvenstveno će vam uštedjeti vrijeme, ali može pomoći i smanjenju pogrešaka iz ručnog unosa. Moguće je i jednostavnije čitati, osobito ako se u razdjelnicima zarezima teško razlikovati polutočke.

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

U ćeliji D9 unijeli smo = slijed (1, 5; 3; 1), ali možete unijeti i 3, 4, 5, 6 i 7 u ćelije A9: devete. Nema ništa posebno u toj određenoj odabiru brojeva, samo smo odabrali nešto drugo osim 1-5 za diferencijaciju.

U ćeliji E11 unesite = Sum (D9: devete * slijed (1; 5))ili = Sum (D9: devete * {1; 2; 3; 4; 5}). Formule vraćaju 85.

Pomoću konstanti polja u formulama. U ovom smo primjeru koristili = SUM (,

Funkcija slijed stvara ekvivalent konstante polja {1, 2, 3, 4, 5}. Budući da Excel izvršava operacije na izrazima koji su prvi obuhvaćeni zagradama, sljedeća dva elementa koja se prikazuju jesu vrijednosti ćelija u programu,, Devete 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 (, da * 1; E9 * 2; F9 * 3; G9 * 4; devete * 5)ili = Sum (3 * 1; 4 * 2; 5 * 3; 6 * 4; 7 * 5)

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

Da biste izbjegli korištenje pohranjenog polja i zadržavanje operacije u cijelosti u memoriji, možete je zamijeniti drugim konstantom polja:

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

Elementi koje možete koristiti u konstantama polja

  • Konstante polja mogu sadržavati brojeve, tekst, logičke vrijednosti (kao što su TRUE i FALSE) te vrijednosti pogrešaka, kao što je #N/A. Možete koristiti brojeve u cijeli broj, decimalni i znanstveni oblici. 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 korištenja konstanti polja jest njihovo ime. 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:

Idite na formule _Gt_ Definirani nazivi _ gt_ Definiranje naziva. 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 naziva konstante polja iz formula > Definirani nazivi _ Gt_ Upravitelj naziva _ Gt_ novo

Kliknite u redu, a zatim odaberite bilo koji redak s tri prazne ćelije, a zatim unesite = tromjesečje1.

Prikazat će se sljedeći rezultat:

Korištenje naziva konstante polja u formuli, kao što je = Tromjesečje1, gdje je Tromjesečje1 definiran kao = {"Siječanj", "Veljača", "Ožujak"}

Ako želite da se rezultati okomito prelijevaju, a ne vodoravno, možete koristiti =trans(tromjesečje1).

Ako želite prikazati popis od 12 mjeseci, kao što biste mogli koristiti prilikom izgradnje financijskog izvješća, možete ga temeljiti na tekućoj godini uz funkciju slijed. Uredna stvar o ovoj funkciji jest da se, iako samo mjesec prikazuje, postoji valjani datum iza njega koji možete koristiti u drugim izračunima. Ove primjere prikazat će vam se na popisu naziva konstante polja i Brzi ogledni skup podataka u radnoj knjizi primjera.

= TEXT (Datum (godina (TODAY ()), slijed (1, 12), 1), "Mmm")

Korištenje kombinacije funkcija tekst, Datum, godina, danas i slijed za stvaranje dinamičkog popisa od 12 mjeseci

Time se funkcija Datum koristi za stvaranje datuma na temelju tekuće godine, slijed stvara konstantu polja od 1 do 12 za Siječanj do prosinca, a zatim funkcija TEXT pretvara oblik prikaza u "Mmm" (siječanj, Feb, Mar itd.). Ako ste željeli prikazati puni naziv mjeseca, kao što je siječanj, koristili biste "Mmmm".

Kada koristite imenovanu konstantu kao formulu polja, ne zaboravite unijeti znak jednakosti, kao u = Tromjesečje1, a ne samo Tromjesečje1. Ako to ne učinite, Excel će polje protumačiti kao niz teksta te formula neće funkcionirati na očekivani način. Na kraju, imajte na umu da možete koristiti kombinacije funkcija, teksta i brojeva. Sve ovisi o tome kako kreativno želite dobiti.

Sljedeći primjeri prikazuju nekoliko načina na koje možete iskoristiti konstante polja u formulama polja. Neki od primjera koriste funkciju Transpose za pretvaranje redaka u stupce i obratno.

  • Više svake stavke u polju

    ENTER = slijed (1; 12) * 2ili = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} * 2

    Možete i podijeliti s (/), dodati uz (+) i oduzeti (-).

  • Kvadriranje stavki u polju

    Unesite = slijed (1; 12) ^ 2ili = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2

  • Pronalaženje kvadrata korijena kvadratne stavke u polju

    ENTER =Sqrt(slijed (1, 12) ^ 2)ili = Sqrt ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2)

  • Transponiranje jednodimenzionalnog retka

    ENTER = Transpose (slijed (1, 5))ili = trans spose ({1; 2; 3; 4; 5})

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

  • Transponiranje jednodimenzionalnog stupca

    ENTER = Transpose (slijed (5; 1))ili = trans spose ({1; 2; 3; 4; 5})

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

  • Transponiranje dvodimenzionalne konstante

    ENTER = Transpose (slijed (3; 4))ili = trans spose ({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

    U sljedećem se primjeru objašnjava kako koristiti formule polja za stvaranje novog polja iz postojećeg polja.

    ENTER = slijed (3, 6, 10, 10)ili = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    Obavezno upišite {(otvaranje brace) prije nego što upišete 10, a} (zatvaranje brace) nakon upisa 180, jer stvarate polje brojeva.

    Zatim unesite = D9 #ili = D9: I11 u praznom ćeliji. Prikazuje se polje od 3 x 6 ćelija s istim vrijednostima koje se prikazuju u programu D9: D11. Natpis # zove se " prosuti raspon" operatori to je način na koji se prikazuje cijeli raspon polja, a ne da ga morate upisati.

    Korištenje operatora prolijevanje raspona (#) za referencu na postojeće polje

  • Stvaranje konstante polja iz postojećih vrijednosti

    Možete iskoristiti rezultate prolivene formule polja i pretvoriti ga u sastavni dio. Odaberite ćeliju, a zatim tipku F2 da biste prešli u način uređivanja. Zatim pritisnite F9 za pretvorbu referenci ćelija na vrijednosti, koje će Excel zatim pretvoriti 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 se primjeru prikazuje kako prebrojavati broj znakova u rasponu ćelija. To obuhvaća razmake.

    Brojanje ukupnog broja znakova u rasponu i drugih polja za rad s tekstnim nizovima

    = SUM (LEN (, OD: C13))

    U ovom slučaju funkcija Len vraća duljinu svakog tekstnog niza u svakoj ćeliji u rasponu. Funkcija SUM potom zbraja te vrijednosti i prikazuje rezultat (66). Ako ste željeli dobiti prosječni broj znakova, možete koristiti sljedeće:

    = AVERAGE (LEN (NA: C13))

  • Sadržaj najduže ćelije u rasponu.

    = INDEKS (,, JE: C13, MATCH (MAX (IN (OD: C13)), LEN (NA: 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, što odgovara najdužem tekstnom 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 najduži 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 ((OD: C13)

    koji se nalazi u ovom polju:

    LEN (OD: C13)

    Argument vrsta podudaranja u ovom slučaju jest 0. Vrsta podudaranja može biti vrijednost 1, 0 ili 1.

    • 1 – vraća najveću vrijednost koja je manja od ili jednaka valnom pretraživanju.

    • 0 – vraća prvu vrijednost točno jednaku vrijednosti pretraživanja.

    • -1-vraća najmanju vrijednost koja je veća ili jednaka određenoj vrijednosti pretraživanja.

    • Ako izostavite vrstu podudaranja, Excel pretpostavlja da je 1.

    Na kraju, funkcija index uzima ove argumente: polje i broj retka i stupca unutar tog polja. Raspon ćelija je.: C13 sadrži polje, funkcija MATCH sadrži adresu ćelije, a konačni argument (1) određuje da vrijednost dolazi iz prvog stupca u polju.

    Ako ste željeli nabaviti sadržaj najmanjeg tekstnog niza, možete zamijeniti Maxa u gornjem primjeru s min.

  • Traženje n najmanjih vrijednosti unutar raspona

    U ovom se primjeru prikazuje kako pronaći tri najmanje vrijednosti u rasponu ćelija, gdje je polje oglednih podataka u ćelijama B9: B18has stvoreno pomoću: = int (Randarray(10; 1) * 100). Imajte na čemu da je RANDARRAY nepromjenjiva funkcija, pa ćete dobiti novi skup slučajnih brojeva svaki put kada Excel izračunava.

    Formula polja programa Excel da biste pronašli NTH najmanju vrijednost: = SMALL (B9 #, slijed (D9))

    Unesite = Small (B9 #, slijed (D9), = Small (B9: B18; {1; 2; 3})

    Ova formula koristi konstantu polja za ocjenu male funkcije tri puta i vraća najmanje 3 člana u polje koje je sadržano u ćelijama B9: B18, gdje je 3 vrijednost varijable u ćeliji D9. Da biste pronašli dodatne vrijednosti, možete povećati vrijednost u funkciji slijed ili dodati još argumenata u konstantu. S tom formulom možete koristiti i dodatne funkcije, kao što su SUM ili AVERAGE. Na primjer:

    = SUM (SMALL (B9 #; SLIJED (D9))

    = AVERAGE (MALA (B9 #, SLIJED (D9))

  • Traženje n najvećih vrijednosti unutar raspona

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

    Unesite = Large (je B9 #, Row (indirektna ("1:3")))ili = Large (B9: B18; Row (neizravni ("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. Primjerice, odaberite praznu 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 redaka, 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"))

    INDIREKTNA funkcija koristi tekstne nizove kao argumente (zbog čega 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 slijed:

    = SLIJED (10)

    Ispitajte formulu koju ste ranije koristili – = LARGE (B9 #; redak (indirektno ("1:3"))) – Počevši od unutrašnjih zagrada i rada prema van: indirektna funkcija vraća skup tekstnih vrijednosti, u ovom slučaju vrijednosti od 1 do 3. Funkcija ROW zauzvrat generira polje stupca s tri ćelije. Funkcija LARGE koristi vrijednosti u rasponu ćelija B9: B18, a procjenjuje se tri puta, jednom za svaku referencu koju vraća funkcija ROW. Ako želite pronaći više vrijednosti, u indirektnu funkciju dodajte veći raspon ćelija. Na kraju, kao i s malim primjerima, ovu formulu možete koristiti s drugim funkcijama, kao što su SUM i AVERAGE.

  • Zbrajanje raspona koji sadrži vrijednosti pogreške

    Funkcija SUM u programu Excel ne funkcionira kada pokušate zbrajati raspon koji sadrži vrijednost pogreške, kao što je #VALUE! ili #N/A. U ovom se primjeru prikazuje kako zbrajati vrijednosti u rasponu naziva podataka koji sadrži pogreške:

    Koristite polja da biste se dogovorili s pogreškama. Primjerice, = SUM (IF (ISERROR (podaci), "", podaci) zbrajati će raspon imenovani podaci, čak i ako sadrži 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 je primjer kao prethodna formula, ali vraća broj vrijednosti pogrešaka u rasponu naziva podataka 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.

Pomoću polja možete izračunati na temelju određenih uvjeta. = SUM (IF (Sales>0; prodaja)) zbrajati će sve vrijednosti veće od 0 u rasponu naziva prodaja.

Ova formula polja, primjerice, zbraja samo pozitivne cijele brojeve u rasponu pod nazivom Prodaja, što predstavlja ćelije E9: E24 u gornjem primjeru:

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

Funkcija IF stvara niz pozitivnih i lažnih vrijednosti. 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 polja izračunava vrijednosti veće od 0 i manje od 2500:

= SUM ((Sales>0) * (Sales<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. Možete, primjerice, zbrajati vrijednosti koje su veće od 0 ili manje od 2500:

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

Funkcije AND i OR ne možete koristiti u formulama polja izravno jer te funkcije vraćaju jedan rezultat, TRUE ili FALSE, a funkcije polja zahtijevaju polja rezultata. Problem možete zaobići pomoću logike prikazane u prethodnoj formuli. Drugim riječima, izvodite matematičke operacije, kao što su zbrajanje ili množenje na vrijednosti koje zadovoljavaju uvjete ili ili i uvjet.

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 polja uspoređuje vrijednosti u dva raspona ćelija s nazivom MyData i YourData i vraća broj razlika između dva. Ako je sadržaj dvaju raspona identičan, formula vraća 0. Da biste koristili ovu formulu, rasponi ćelija moraju biti iste veličine i iste dimenzije. Primjerice, ako je MyData raspon od 3 retka prema 5 stupaca, YourData mora biti tri retka prema 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 * (MyData<>YourData))

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

U oglednoj radnoj knjizi prikazat će vam se slični primjeri o razlikama između radnog lista 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 na praznom radnom listu.

Prodaja Osoba

Auto Vrsta

Broj Prodano

Unit Cijena

Ukupni zbroj 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 kupee i limuzina za svakog prodavača, odaberite ćelije E2: E11, unesite formulu = C2: C11 * D2: D11, a zatim pritisnite CTRL + SHIFT + ENTER.

  2. Da biste vidjeli ukupni zbroj svih prodaja, odaberite ćeliju F11, unesite formulu = Sum (C2: C11 * D2: D11), a zatim pritisnite CTRL + SHIFT + ENTER.

Kada pritisnete CTRL + SHIFT + ENTER, Excel će formulu okruћiti vitičastih aparatića ({}) 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 slučaju Excel Množi vrijednosti u polju (raspon ćelija C2 do D11), a zatim koristi funkciju Sumza zbrajanje ukupnih 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.

Primjetite i da je formula s jednom ćelijom u ćeliji D13 potpuno neovisna o formuli s više ćelija (formula u ćelijama E2 do E11). To je dodatna prednost korištenja formula polja – fleksibilnost. Formule možete promijeniti u stupcu E ili izbrisati taj stupac, a da ne utječe na formulu na servisu 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. Kliknite, primjerice, u ćeliji E3, a zatim pritisnite Delete. Morat ćete odabrati cijeli raspon ćelija (od E2 do E11) i promijeniti formulu za čitav raspon ili ostaviti polje nepromijenjeno. Kao dodana mjera sigurnosti, morate pritisnuti CTRL + SHIFT + ENTER da biste potvrdili promjenu formule.

  • Manja veličina datoteka    Možete često koristiti jednu formulu polja umjesto nekoliko međuformulama. Radna knjiga, primjerice, koristi jednu formulu polja da bi izračunao rezultate u stupcu E. Ako ste koristili standardne formule (primjerice = C2 * D2, C3 * D3, C4 * D4...), koristili biste 11 raznih formula za izračun istih rezultata.

Općenito, formule polja koriste standardnu sintaksu formule. Svi započinju znakom jednakosti (=), a većina ugrađenih funkcija programa Excel možete koristiti u formulama polja. Ključna je razlika u tome što kada koristite formulu polja, pritisnite CTRL + SHIFT + ENTER da biste unijeli formulu. Kada to učinite, Excel okružuje formulu polja s vitičastih aparatića – ako ručno upišete aparatić, formula će se pretvoriti u tekstni niz i neće funkcionirati.

Funkcija Array može biti učinkovit način izgradnje 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 je potrebno unijeti formulu polja. 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 cijeli raspon formula (na primjer, E2: E11), a zatim pritisnite Delete.

  • Ne možete umetnuti prazne ćelije ni brisati ćelije iz formule polja s više ćelija.

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

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

  • Povremeno možete zaboraviti pritisnuti 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. Dakle, ako drugi korisnici moraju izmijeniti vaše radne knjige, trebali biste izbjegavati formule polja ili provjeriti jesu li osobe koje znaju o formulama polja i razumiju kako ih promijeniti, ako im je potrebno.

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

Do sada, znate da morate pritisnuti CTRL + SHIFT + ENTER kada stvarate formule 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 koristite CTRL + SHIFT + ENTER da biste unijeli cijelu 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.

Evo polja u jednom retku: {1, 2, 3, 4}. Evo polja u jednom stupcu: {1; 2; 3; 4}. A ovdje je niz dvaju redaka i četiri stupca: {1, 2, 3, 4; 5, 6, 7, 8}. U polju dva retka, prvi redak je 1, 2, 3 i 4, a drugi redak je 5, 6, 7 i 8. Jedna točka-zarez razdvaja dva retka, 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. Na traku formule unesite sljedeću formulu, a zatim pritisnite CTRL + SHIFT + ENTER:

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

    U ovom slučaju unesite aparatić za otvaranje i zatvaranje ({}), a Excel će dodati drugi skup za vas.

    Prikazuje se sljedeći rezultat.

    Konstanta vodoravnog polja u formuli

Stvaranje okomite konstante

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

  2. Na traku formule unesite sljedeću formulu, a zatim pritisnite 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. Na traku formule unesite sljedeću formulu, a zatim pritisnite 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 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. Zapamtite i da nakon dodavanja konstante u formulu polja pritisnite 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 isprobali ovo, kopirajte funkciju, odaberite praznu ćeliju u radnoj knjizi, zalijepite formulu u traku formule, a zatim pritisnite 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 polja mogu sadržavati brojeve, tekst, logičke vrijednosti (kao što su TRUE i FALSE) te vrijednosti pogrešaka (primjerice #N/A). Brojeve možete koristiti u cijeli broj, decimalni i znanstveni oblici. 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.

Jedan od najboljih načina korištenja konstanti polja jest njihovo ime. 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.
    Prikazat će 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, a zatim 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 izstavite zarez ili točku sa zarezom ili ako ga postavite na pogrešno mjesto, konstantu polja možda se neće ispravno stvarati 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.

Sljedeći primjeri prikazuju nekoliko načina na koje možete iskoristiti konstante polja u formulama polja. Neki od primjera koriste funkciju Transpose za pretvaranje redaka u stupce i obratno.

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, a zatim 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, a zatim 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, a zatim 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, a zatim 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, koji unosi polje brojeva u rasponu ć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 C1 do E3 prikazuje se polje od 3x3 ćelija s istim vrijednostima koje se prikazuju na od C8 do E10.

Stvaranje konstante polja iz postojećih vrijednosti

  1. Ako su ćelije C1: C3 odabrano, pritisnite F2 da biste prešli u način uređivanja. 

  2. Pritisnite F9 za pretvorbu referenci ćelija na vrijednosti. Excel će pretvoriti vrijednosti u konstantu polja. Formula sada bi trebala biti = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  3. Pritisnite 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 Cell 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 najdužih ćelija a2: A6 (ćelija A3).

Sljedeća formula koristi se 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 potom 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 slučajne brojeve u ćelije a1: A11.

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

  3. Unesite sljedeću formulu, a zatim pritisnite CTRL + SHIFT + ENTER:

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

Ova formula koristi konstantu polja da bi tri puta procijenila malu funkciju i vratila najmanju (1), drugu najmanju (2) i treću najmanju (3) članove u polje koje je sadržano u ćelijama a1: A10 da biste pronašli dodatne vrijednosti, dodajte još argumenata u Stalno. 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 (MALA (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 D1 do D3.

  2. U traku formule unesite ovu formulu, a zatim pritisnite CTRL + SHIFT + ENTER:

    = LARGE (A1: A10, REDAK (INDIREKTNA ("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. Primjerice, odaberite prazan stupac od 10 ćelija u radnoj knjizi prakse, 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: je, red (neizravno ("1:3"))) – Počevši od unutrašnjih zagrada i rada prema van: indirektna funkcija vraća skup tekstnih vrijednosti, u ovom slučaju vrijednosti od 1 do 3. Funkcija Row zauzvrat generira polje stupca s tri ćelije. Funkcija Large koristi vrijednosti u rasponu ćelija a5:, a procjenjuje se tri puta, jednom za svaku referencu koju vraća funkcija Row . Vrijednosti 3200, 2700 i 2000 vraćaju se u polje troćelijski kolumnar. Ako želite pronaći više vrijednosti, u indirektnu funkciju dodajte veći raspon ćelija.

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 primjer starijeg tekstnog niza, u praznu ćeliju unesite sljedeću formulu, a zatim pritisnite CTRL + SHIFT + ENTER:

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

Prikazuje se tekst "hrpa ćelija koje".

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, što odgovara najdužem tekstnom 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 sadrži polje, funkcija Match sadrži 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 funkcionira kada pokušate zbrajati raspon koji sadrži vrijednost pogreške, kao što je #N/A. U ovom se primjeru prikazuje kako zbrajati vrijednosti u rasponu naziva podataka 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.

Funkcije and i or ne možete koristiti u formulama polja izravno jer te funkcije vraćaju jedan rezultat, True ili FALSE, a funkcije polja zahtijevaju polja rezultata. Problem možete zaobići pomoću logike prikazane u prethodnoj formuli. Drugim riječima, možete obavljati matematičke operacije, kao što su zbrajanje ili množenje, na vrijednosti koje zadovoljavaju uvjet or ili i.

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 u dva raspona ćelija s nazivom MyData i YourData i vraća broj razlika između dva. Ako je sadržaj dvaju raspona identičan, formula vraća 0. Da biste koristili ovu formulu, rasponi ćelija moraju biti iste veličine i iste dimenzije (primjerice, ako je MyData raspon od 3 retka prema 5 stupaca, YourData mora biti tri retka prema 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 * (MyData<>YourData))

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

Šaljući

Dijelovi ovog članka utemeljeni su na nizu stupaca dodatka Power user koje je napisao Colin Wilcox, a adaptiran je iz poglavlja 14 i 15 formula programa Excel 2002, knjige koju je napisao John Walkenbach, bivši MVP programa Excel.

Treba li vam 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.

Pogledajte i sljedeće

Dinamička polja i prelijevanje polja

Dinamičke formule polja nasuprot naslijeđenim formulama CSE polja

Funkcija FILTER

Funkcija RANDARRAY

Funkcija SEQUENCE

Funkcija SINGLE

Funkcija SORT

Funkcija SORTBY

Funkcija UNIQUE

Pogreške #SPILL! u programu Excel

Pregled formula

Proširite svoje vještine korištenja sustava Office
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.

×