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.

Formule polja se formula koja se može izračunavati više na jednu ili više stavki u polju. Možete shvatiti polja kao redak ili stupac vrijednosti ili kombinacije redaka i stupaca s vrijednostima. Formule polja može vratiti više rezultata ili jedan rezultat.

Počevši od 2018 rujan ažuriranja za Office 365, bilo koju formulu koju može vratiti više rezultata će automatski spill ih prema dolje ili uzduž u susjednom ćelije. Nekoliko nove dinamičkog polja funkcijei praćeni tu promjenu ponašanje. Formula dinamički polja, hoće li se koriste postojeće ili funkcije dinamički polja, samo moraju biti unos u jednu ćeliju, a zatim potvrdili pritiskom na tipku Enter. Formule polja ranije, naslijeđene potreban je najprije odaberete cijeli izlazni raspon, a zatim potvrđivanje formula s Ctrl + Shift + Enter. Mogu se često se nazivaju CSE formulama.

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

  • Brzo stvorite uzorka skupova podataka.

  • Brojanje znakova sadržanih u rasponu ćelija.

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

  • Zbrajanje svaka ti vrijednost u skupu vrijednosti.

Sljedeći primjeri pokazuju kako stvoriti formula polja s više ćelija i jednom ćelijom. Gdje je to moguće, ne možemo stvaraju primjere s nekim funkcije dinamički polja, kao i postojeće formule polja koje se unijeti kao dinamički i naslijeđenih polja.

Preuzimanje naših primjera

Preuzmite primjer radnu knjigu s sva polja formule primjerima 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 da biste izračunali broj automobilima prodao Jedinična cijena

  • Ne možemo se ovdje izračun ukupnu prodaju coupea i limuzina za svakog prodavača unosom = F19:F19 * G10:G19 u ćeliji H10.

    Kada pritisnete tipku Enter, prikazat će vam se rezultati spill prema dolje do H10:H19 ćelije. Imajte na umu da se raspon spill istaknut s obrubom kada odaberete bilo koju ćeliju u rasponu spill. Mogli biste primijetiti da su zasivljene formula u ćelijama H10:H19. U kojoj se tamo samo za referencu, pa ako želite da biste prilagodili formulu, morat ćete odabrati ćeliji H10, gdje se nalaze matrice formulu.

  • Formula polja s jednom ćelijom

    Polja s jednom ćelijom formula za izračun ukupnog zbroja s =SUM(F10:F19*G10:G19)

    U ćeliji H20 oglednoj radnoj knjizi upišite ili kopirajte i zalijepite =SUM(F10:F19*G10:G19)i pritisnite tipku 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 zajedno. Rezultat je ukupni zbroj $1,590,000 Prodaja.

    Ovaj primjer pokazuje kako Napredna ta vrsta formule mogu. Ako, na primjer, pretpostavimo da ste 1000 redaka podataka. Možete zbrajati dio ili cijeli tih podataka pomoću formule polja u jednoj ćeliji umjesto povlačenja formulu prema dolje do 1000 redaka. Primijetite da je s jednom ćelijom formulom u ćeliji H20 potpuno neovisno o više ćelija formula (formula u ćelijama H10 do H19). Ovo je druga prednost korištenja formula polja – fleksibilnost. Formule u stupcu H može se promijeniti bez utjecaja formula u H20. Dobro imati neovisno ukupni zbrojevi kao što je ovaj može biti i kao što je lakše provjerili točnost rezultate.

  • Formula dinamički polja imaju i sljedeće prednosti:

    • Dosljednost    Ako kliknete ćelije iz H10 prema dolje, vidjet ćete istu formulu. Ta dosljednost možete osigurati veće preciznosti.

    • Sigurnost    Komponenta formule polja s više ćelija ne može se prebrisati. Na primjer, kliknite ćeliju H11 i pritisnite Delete. Excel neće se promijeniti izlazna polja. Da biste promijenili, morate odaberite gornju lijevu ćeliju u polju ili ćeliji H10.

    • Manje datoteke    Umjesto nekoliko Srednja formula možete koristiti često jedinstvenom formulom polja. Ako, na primjer, u automobila prodaje se primjeru koristi jednu formulu polja da biste izračunali rezultatima u stupcu E. Ako ste koristili standardne formule kao što su = F10 * G10, F11 * G11, F12 * G12, itd., bi ste koristili 11 različitih formula za izračun iste rezultate. Koja nije veliki dijeljenje, ali ne i ako ste imali tisuće retke zbroja? Zatim ga možete napraviti velike razlike.

    • Učinkovitosti    Funkcije polja može biti učinkovit način da biste sastavili složene formule. Polje formule =SUM(F10:F19*G10:G19) je isti kao ovo: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Ne prelaze    Formula polja za dinamičku će automatski spill u izlazni raspon. Ako je izvor podataka u tablici programa Excel, zatim formulama polja za dinamičku automatski mijenja veličinu kao što je dodavanje i uklanjanje podataka.

    • #SPILL! pogreške    Dinamični polja uvedene u #SPILL! pogreške, što znači raspon svrhu spill blokiran zbog nekog razloga. Kad riješite na začepljenje, formula će automatski spill.

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

= {1,2,3,4,5} ili = {"Siječanj"; "Veljača"; "Ožujak"}

Ako odvojite stavke pomoću zareza, stvorite vodoravnog polja (retka). Ako stavke razdvojite točkom sa zarezom, stvorite okomitog polja (stupaca). Da biste stvorili dvodimenzionalnog polja, razdijelite stavke u svakom retku zarezima, a razdijelite svaki redak točkom sa zarezom.

U sljedećim postupcima steći ćete nešto prakse u stvaranju vodoravnih, okomitih i dvodimenzionalnih konstanti. Ne možemo vam pokazati primjerima pomoću funkcije NIZA za automatsko generiranje konstante polja, kao i ručno unijeli konstante polja.

  • Stvaranje vodoravne konstante

    Upotrijebite radnu knjigu iz prijašnjih primjera ili stvorite novu radnu knjigu. Odaberite bilo koju praznu ćeliju, a zatim unesite =SEQUENCE(1,5). Funkcija NIZA sastavlja 1 redak po 5 polja stupca jednaki = {1,2,3,4,5}. Prikazuje se sljedeći rezultat:

    Stvaranje konstanta vodoravnog polja s =SEQUENCE(1,5) ili = {1,2,3,4,5}

  • Stvaranje okomite konstante

    Odaberite bilo koju praznu ćeliju ima mjesta ispod njega, a zatim unesite =SEQUENCE(5)ili = {1; 2; 3; 4; 5}. Prikazuje se sljedeći rezultat:

    Stvaranje okomita konstanta polja s funkcijom = SEQUENCE(5) ili = {1; 2; 3; 4; 5}

  • Stvaranje dvodimenzionalne konstante

    Odaberite bilo koju praznu ćeliju pretpostavljenom s desne strane i ispod njega, a zatim unesite =SEQUENCE(3,4). Pogledajte sljedeći rezultat:

    Stvaranje 3 retka prema konstanta polja 4 stupca s =SEQUENCE(3,4)

    Možete unijeti: ili = {1,2,3,4 5,6,7,8; 9,10,11,12}, ali želite obratite pažnju na kojem se nalazi točkom sa zarezom nasuprot zarezima.

    Kao što vidite, mogućnost SLIJED nudi značajan prednosti u odnosu na ručno unositi vrijednosti konstanti polja. Prije svega, štedi vrijeme, ali ga mogu pomoći smanjiti broj pogrešaka iz Ručni unos. Preporučuje se i lakše čitati, osobito kao točkom sa zarezom može biti teško razlikovati od razdjelnici zarez.

Slijedi primjer koristi polja konstanti kao dio veći formule. U oglednoj radnoj knjizi, idite na radnom listu konstante u formuli ili stvorite novi radni list.

U ćeliju D9 ćemo unijeli =SEQUENCE(1,5,3,1), ali nije moguće unijeti i 3, 4, 5, 6 i 7 u A9:H9 ćelije. Nema ničega posebno o određenog broja odabira, ne možemo brojem koji nije 1-5 za razlikovanje upravo odabrali.

U ćeliju E11, unesite = SUM (D9:H9*SEQUENCE(1,5)), or = SUM (D9:H9* {1,2,3,4,5}). Formula vraća 85.

Korištenje konstanti u formulama. U ovom se primjeru koristi = SUM (D9:H(*SEQUENCE(1,5))

Funkcija NIZA sastavlja ekvivalent polja konstante {1,2,3,4,5}. Budući da Excel izvodi postupke u izrazima zatvoren u zagradama, sljedeća dva elemente koji se isporučuju u Reproduciraj su vrijednosti u ćelijama u D9:H9 i operator množenja (*). U ovom trenutku formula množi vrijednosti u pohranjenog polja tako da odgovarajućih vrijednosti u konstanti. Ekvivalent je:

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

Na kraju, funkcija SUM zbraja vrijednosti i vraća 85.

Da biste izbjegli korištenje pohranjenog polja i u potpunosti zadržali operaciju u memoriji, možete je zamijeniti konstantom nekog drugog polja:

=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 polja mogu sadržavati brojeve, tekst, logičke vrijednosti (kao što su TRUE i FALSE) i vrijednosti pogrešaka kao što je # n/d. Možete koristiti brojeve u cijeli broj, decimalni i znanstvenih oblicima. Ako sadrži tekst, morate ga okružuju navodnike ("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.

Jedna od najboljih načina za korištenje konstanti polja je naziv ih. Imenovane konstante može biti puno su jednostavniji da biste koristili pa ih možete sakriti neke složenost formulama polja od drugih korisnika. Dodjela naziva konstanti polja i koristiti u formuli, učinite sljedeće:

Idite na formule > definirane nazive > Definiraj naziv. U okvir naziv upišite tromjesečje1. U okviru odnosi se na unesite sljedeću konstantu (ne zaboravite ručno upisati vitičaste zagrade):

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

Dijaloški okvir trebala izgledati ovako:

Novi dodavanje imenovani konstanti s formulama > definirani nazivi > > Upravitelj naziva

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

Prikazuje se sljedeći rezultat:

Korištenje imenovanih konstanti u formuli, poput = tromjesečje1, gdje tromjesečje1 definirana kao = {"Siječanj", "Veljača", "ožujak"}

Ako želite rezultata da biste spill okomito umjesto vodoravno, možete koristiti =TRANSPONIRATI(tromjesečje1).

Ako želite da bi se prikazao popis 12 mjeseci, kao što je možete koristiti kada sastavljanjem financijsko izvješće, možete ga temeljiti isključivanje trenutne godine s funkcijom NIZ. Navigacijskom stvar o Ova funkcija je čak i ako se prikazuju samo u mjesecu, ima li ispravni datumi iza koje možete koristiti u ostalim izračunima. Pronaći ćete u ovim se primjerima na radnim listovima s nazivom konstanta polja i skup brzi uzorak podataka u oglednoj radnoj knjizi.

=Text(Date(Year(TODAY()),SEQUENCE(1,12),1),"mmm")

Koristite kombinaciju funkcija TEKST, datum, godine, DANAS, i REDOSLIJED da biste sastavili dinamički popis 12 mjeseci

To se koristi funkcije DATE za stvaranje datuma na temelju trenutne godine, SLIJED stvara konstanta polja od 1 do 12 za siječnja do prosinca pa Funkcija TEXT pretvara oblik prikaza za "mmm" (Sij, Vlj, ožujak itd.). Ako želite prikazati na puni naziv mjeseca, kao što su siječanj, koristit ćete "mmmm".

Kada koristite imenovana konstanta kao formulu polja, imajte na umu da biste unijeli znak jednakosti, kao u = tromjesečje1, ne samo tromjesečje1. Ako ne, Excel interpretira polja kao niz teksta i formula neće funkcionirati u skladu s očekivanjima. Na kraju, imajte na umu pomoću kombinacije funkcija, teksta i brojeva. Sve ovisi kako kreativni želite dohvatiti.

Sljedeći primjeri prikazuju nekoliko načina na koje možete pohraniti konstanti u formulama polja. Neke od primjera koriste TRANSPOSE (funkcija) da biste pretvorili redaka u stupce i obrnuto.

  • Više svake stavke u polju

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

    Možete i dijeljenje s (/), dodavanje s (+) i oduzimanje s (-).

  • Kvadriranje stavki u polju

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

  • Pronalaženje kvadratni korijen od kvadrata stavki u polju

    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 pomoću formule polja da biste stvorili novi polja iz postojećih polja.

    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) nakon što upišete 180 jer stvarate polje brojeva.

    Nakon toga unesite = D9 #ili = D9:I11 u praznu ćeliju. Pojavit će se 3 x 6 raspon ćelija sadrži jednake vrijednosti koje vidite u D9:D11. Znak # jest spilled operatora raspona, a to je način programa Excel pozivanju raspon cijelu polja umjesto da ga upišite.

    Korištenje operatora spilled raspona (#) referentni postojećih polja

  • Stvaranje konstante polja iz postojećih vrijednosti

    Možete koristiti rezultate formule spilled polja, a koji pretvoriti njegove dijelove komponente. Odaberite ćeliju D9, a zatim pritisnite tipku F2 da biste prešli u način za uređivanje. Nakon toga pritisnite F9 da biste pretvorili reference na vrijednosti, Excel će se zatim pretvara u konstanti polja. Kada pritisnete tipku Enter, formulu = D9 #, trebali biste = sada {10,20,30 40,50,60; 70,80,90}.

  • Brojanje znakova u rasponu ćelija

    Sljedeći primjer prikazuje način izbrojite broj znakova u rasponu ćelija. To obuhvaća razmake.

    Brojanje ukupan broj znakova u rasponu i drugih polja u radu s tekstnih nizova

    = SUM (LEN(C9:C13))

    U ovom slučaju funkcija LEN vraća duljinu svaki tekstni niz u svakoj od ćelije u rasponu. Funkcija SUM zbraja vrijednosti pa prikazuje rezultat (66). Ako ste željeli da biste dobili Prosječan broj znakova, možete koristiti:

    = AVERAGE (LEN(C9:C13))

  • Sadržaj najdulje ćelije u rasponu C9:C13

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    Ova formula funkcionira samo ako raspon podataka sadrži jedan stupac ćelija.

    Pogledajmo detaljnije Upoznavanje formulu, počevši od unutarnji elemente i radi prema van. Funkcija LEN vraća duljinu svih stavki u rasponu ćelija D2:D6. Funkcija MAX izračunava najveću vrijednost među te stavke koja odgovara najdulje tekstnog niza, koje se nalazi u ćeliji D3.

    Evo gdje pronaći malo složene stvari. Funkcija MATCH izračunava offset (relativni položaj) ćeliju koja sadrži najdulje tekstnog niza. Da biste to učinili, bit će potrebno tri argumenta: vrijednosti za traženje, vrijednosti polja i vrstu podudaranja. Funkcija MATCH pretražuje polja pretraživanja za navedene vrijednosti. U ovom slučaju vrijednosti je najdulje tekstni niz:

    MAX(LEN(C9:C13)

    koji se nalazi u ovom polju:

    LEN(C9:C13)

    Argument Vrsta podudaranja u tom slučaju je 0. Vrsta podudaranja može biti na 1, 0 ili vrijednost-1.

    • 1 – vraća najveću vrijednost koja je manja od ili jednaka pretraživanja val

    • 0 - vraća prvu vrijednost jednaka vrijednosti

    • -1 - Vraća najmanju vrijednost koja nije veća od ili jednaka vrijednosti navedeni pretraživanja

    • Ako izostavite vrstu podudaranja, Excel pretpostavlja da 1.

    Na kraju, funkcija INDEX otvara ove argumente: polja i broj redaka i stupaca iz tog polja. Raspon ćelija C9:C13 omogućuje polja, funkcija MATCH sadrži adresa ćelije, a konačni argument (1) određuje vrijednost dolazi iz prvog stupca u polju.

    Ako ste željeli da biste sadržaj najmanju tekstnog niza, ne želite zamijeniti MAX u primjeru iznad MIN.

  • Traženje n najmanjih vrijednosti unutar raspona

    U ovom je primjeru prikazano traženje triju najmanjih vrijednosti u rasponu ćelija, pri čemu je polje s oglednim podacima u ćelijama B9:B18has stvorena: = INT (RANDARRAY(10,1) * 100). Imajte na umu da RANDARRAY promjenjive funkcije, pa će se svaki put kada Excel izračunava novi skup slučajne brojeve.

    Formule polja da biste pronašli ti najmanju vrijednost u programu Excel: =SMALL(B9#,SEQUENCE(D9))

    Unesite =SMALL(B9#,SEQUENCE(D9)= MALE (B9:B18, {1; 2; 3})

    Ova formula koristi konstanta polja da biste vrednovati funkcije SMALL triput i vratili najmanje 3 članova u polju koji je sadržan u B9:B18 ćelije, pri čemu je 3 varijable vrijednost u ćeliji D9. Da biste pronašli više vrijednosti, možete povećati vrijednosti u funkciji NIZ ili više argumenata dodati konstantu. Možete koristiti i dodatne funkcije koja sadrži formulu, kao što su SUM i AVERAGE. Ako, 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ćih vrijednosti u rasponu, možete zamijeniti funkcije SMALL pomoću funkcije LARGE. Osim toga, u sljedećem primjeru pomoću funkcije retka i INDIRECT .

    Unesite = LARGE (B9 # retka (INDIRECT ("1:3"))), ili = LARGE (B9:B18,ROW(INDIRECT("1:3")))

    Sada se mogu pomoći malo informacije o funkcijama retka i INDIRECT. Funkcija ROW možete koristiti da biste stvorili polje uzastopnih cijelih brojeva. Na primjer, odaberite prazan i unesite:

    =ROW(1:10)

    Ova formula stvara stupac s 10 uzastopnih cijelim brojevima. Da biste vidjeli potencijalnih problema, umetanje retka iznad raspona koji sadrži formulu polja (to jest, iznad retka 1). Excel prilagođava retka reference, a formula sada generira cijeli brojevi od 2 do 11. Da biste riješili taj problem, dodajte funkcije INDIRECT u formuli:

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

    Funkcije INDIRECT koristi tekstne nizove kao argumenti (koji je Zašto raspon 1:10 unutar navodnika). Excel prilagodite tekstualne vrijednosti kad Umetanje redaka ili u suprotnom premjestite formulu polja. Kao rezultat funkcije ROW uvijek generira polja cijelih brojeva koje želite. Jednostavno možete koristiti NIZU:

    =SEQUENCE(10)

    Provjerimo formula koje ste koristili ranije – = LARGE (B9 # retka (INDIRECT ("1:3"))) – počevši od unutarnji zagradama i radi prema van: U INDIRECT funkcija vraća skup tekstne vrijednosti u ovom slučaju vrijednosti od 1 do 3. Funkcija ROW shodno generira stupac tri ćelije polja. Funkcije LARGE koristi vrijednosti u rasponu ćelija B9:B18 i vrednuje se triput jednom za svaki referencu koju vraća funkcija ROW. Ako želite da biste pronašli dodatne vrijednosti, dodajte raspon ćelija veći INDIRECT (funkcija). Na kraju, kao i u slučaju SMALL Primjeri koristite ovu formulu s drugim funkcijama, kao što su SUM i AVERAGE.

  • Zbrajanje raspona koji sadrži vrijednosti pogreške

    Funkcija SUM u programu Excel ne funkcionira prilikom pokušaja zbroj raspona koji sadrži vrijednosti pogreške, kao što su #VALUE!. ili # n/d. U ovom se primjeru pokazuje kako se zbrajaju vrijednosti u rasponu podataka koji sadrže pogreške:

    Postupanje s pogreškama pomoću polja. Na primjer, =SUM(IF(ISERROR(Data),"",Data) će zbroj raspona s nazivom podaci, čak i ako se uključuje pogreške, kao što su #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

    U ovom se primjeru je kao što 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.

Morat ćete Zbrajanje vrijednosti na temelju uvjeta.

Polja možete koristiti da biste izračunali ovisno o određenim uvjetima. =SUM(IF(Sales>0,Sales)) će Zbrajanje svih vrijednosti veće od 0 u rasponu Prodaja.

Na primjer, ova formula polja zbraja samo na pozitivni cijeli brojevi u rasponu Prodaja, što predstavlja E9:E24 ćelije u gornjem primjeru:

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

Funkcija IF stvara polje s vrijednostima pozitivne i false. Funkcija SUM zapravo zanemaruje vrijednosti false jer je 0 + 0 = 0. Raspon ćelija koje koristite u ovoj formuli može se sastojati od bilo koji broj 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 manji 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 koja koriste vrstu ili uvjeta. 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 koristiti AND i OR funkcija u formulama polja izravno jer te funkcije dobiju jedan rezultat TRUE ili FALSE, a polja funkcije zahtijevaju polja rezultata. Problem možete riješiti pomoću logike prikazana u prethodnoj formuli. Drugim riječima, izvođenje matematičkih operacija poput zbrajanja ili množenja na vrijednosti koje ispunjavaju u 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 pod nazivom Mojipodaci i Vašipodaci i vraća broj razlike između njih. Ako se isti su sadržaj dva raspona, formula vraća 0. Da biste koristili ovu formulu, raspona ćelija moraju biti iste veličina i istu dimenziju. Na primjer, ako Mojipodaci je raspon 3 retka prema 5 stupcima, Vašipodaci mora biti 3 retka prema 5 stupcima:

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

Primjeri slične nalaze se u oglednoj radnoj knjizi na 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 ih u ćeliju A1 praznog radnog lista.

Prodaja Osobe

Automobila Vrsta

Broj Prodaje

Jedinica Cijena

Ukupno 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 ćelije E2: e11, unesite formulu = C2: C11 * D2: D11, a zatim pritisnite Ctrl + Shift + Enter.

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

Kada pritisnete Ctrl + Shift + Enter, Excel okružuje formula s vitičaste zagrade ({}) i umeće instance komponente formulu u svakoj ćeliji odabranog raspona. To se događa vrlo brzo, pa je ono što vidite u stupcu E ukupni iznos prodaje za svaku vrstu automobila za svakog prodavača. Ako ste odabrali E2, a zatim odaberite E3 i E4 ostalo, vidjet ćete se prikazuje u istu formulu: {= 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 funkcija SUMza zbrajanje ukupnih zbrojeva zajedno. Rezultat je ukupni zbroj $1,590,000 Prodaja. Ovaj primjer pokazuje kako Napredna ta vrsta formule mogu. Ako, na primjer, pretpostavimo da ste 1000 redaka podataka. Možete zbrajati dio ili cijeli tih podataka pomoću formule polja u jednoj ćeliji umjesto povlačenja formulu prema dolje do 1000 redaka.

Primijetite da je s jednom ćelijom formulom u ćeliji D13 potpuno neovisno o više ćelija formula (formula u ćelijama E2 do E11). Ovo je druga prednost korištenja formula polja – fleksibilnost. Nije moguće promijeniti formule u stupcu E ili izbrisati taj stupac potpuno, bez utjecaja formula u 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    Komponenta formule polja s više ćelija ne može se prebrisati. Na primjer, kliknite ćeliju E3 i pritisnite Delete. Morate odabrati cijeli raspon ćelija (E2 putem E11) i promijenite formulu za cijelo polje ili ga ostavite polja kao što je. Kao mjeru dodatnu sigurnost imate pritisnite Ctrl + Shift + Enter da biste potvrdili promjene u formuli.

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

Općenito govoreći, formule polja pomoću standardnih sintaksa formule. Svi oni započinju znakom jednakosti (=), a većina ugrađene funkcije programa Excel možete koristiti u formulama polja. Ključne razlike je da prilikom korištenja formula polja, pritisnete Ctrl + Shift + Enter da biste unijeli formulu. Kada to učinite, Excel okružuje formula polja s vitičaste zagrade – ako ručno upisati vitičaste zagrade, formula će se pretvoriti u tekstnom nizu i neće funkcionirati.

Funkcije polja može biti učinkovit način da biste sastavili složene formule. Polje formulu =SUM(C2:C11*D2:D11) je isti kao ovo: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Važno: Kad god morat ćete unijeti formulu polja, pritisnite Ctrl + Shift + Enter . To se odnosi na jednom ćelijom i više ćelija formule.

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 formule raspon (na primjer, ćelije E2: e11), a zatim pritisnite Delete.

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

Ponekad ćete morati proširiti formulu polja. Odaberite prvu ćeliju u rasponu postojećeg polja, a zatim nastavite sve dok ne odaberete cijeli raspon koji želite formulu da biste proširili. Pritisnite F2 da biste uredite formulu, a zatim pritisnite CTRL + SHIFT + ENTER da biste potvrdili formulu kad ste prilagođeni raspon formule. Ključno je da biste odabrali cijeli raspon, počevši od gornju lijevu ćeliju u polju. Gornju lijevu ćeliju je onaj koji uređivanja.

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

  • Ponekad može zaboraviti pritisnite Ctrl + Shift + Enter. Može se dogoditi da biste čak i najčešće iskusnih korisnika programa Excel. Imajte na umu da pritisnite ovu kombinaciju tipki kad god unijeli ili uredili formule polja.

  • Drugi korisnici radne knjige mogu razumjeti formula. U praksi formule polja su obično ne objašnjene u radnom listu. Zbog toga ako drugim korisnicima potrebna za izmjenu radne knjige, koje treba izbjegli formule polja ili provjerite je li osobe zna sve formule polja i znati kako promijeniti ih, ako im je to 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}

Tako da sada, znate da ćete morati kad je stvaranje formula polja, pritisnite Ctrl + Shift + Enter . Budući da konstante polja dio formule polja, stavite konstante sa zagradama upisivanjem ručno. Zatim koristite Kombinaciju tipki Ctrl + Shift + Enter da biste unijeli cijele formule.

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.

Ovdje je polje u jednom retku: {1,2,3,4}. Ovdje je polje u jednom stupcu: {1; 2; 3; 4}. Evo dva redaka i stupaca četiri polja: {1,2,3,4; 5,6,7,8}. U polju dva retka prvog retka 1, 2, 3 i 4, a drugi redak 5, 6, 7 i 8. Jedan zarez odvaja 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. U praznom radnom listu odaberite ćelije od A1 do E1.

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

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

    U ovom slučaju, trebali biste upisati lijevu i desnu vitičastu zagradu ({}), 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 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 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 u zagradama je konstanta polja: {1,2,3,4,5}. Imajte na umu da Excel okružuju konstante polja sa zagradama; zapravo ih upisujete. Imajte na umu i da nakon što dodate konstante u formuli polja, pritisnete 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})

Isprobajte ovo, kopirajte funkciju, odaberite praznu ćeliju u radnoj knjizi, zalijepite formulu u traku formule i zatim pritisnite Ctrl + Shift + Enter. Vidjet ćete isti rezultat kao i u starijim vježbu koji se koristi u formuli:

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

Jedna od najbolji način za korištenje konstanti polja je naziv ih. Imenovane konstante može biti puno su jednostavniji da biste koristili pa ih možete sakriti neke složenost formulama polja od drugih korisnika. Dodjela naziva konstanti polja i koristiti u formuli, učinite sljedeće:

  1. Na kartici formule u grupi Definirani nazivi kliknite Definiraj naziv.
    Pojavit će se dijaloški okvir Definiraj naziv .

  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:

  • Neke elemente može biti odvojeni znakom proper. Ako izostavite zarezom ili točka sa zarezom ili ako ih umetnete u krivom mjestu, konstante polja možda neće biti ispravno stvoren ili možda će 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 pohraniti konstanti u formulama polja. Neke od primjera koriste TRANSPOSE (funkcija) da biste pretvorili redaka u stupce i obrnuto.

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 se unosi u ovom polju brojeva u rasponu ćelija C8: E10 pomoću formule polja. Na radnom listu C8 do E10 trebao bi izgledati ovako:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Odaberite raspon ćelija od C1 do E3.

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

    =C8:E10

    Pojavit će se 3 x 3 raspon ćelija u rasponu ćelija od C1 do E3 sadrži jednake vrijednosti koje vidite u C8 do E10.

Stvaranje konstante polja iz postojećih vrijednosti

  1. Pomoću ćelija C1: C3 odabran, pritisnite F2 da biste prešli u način za uređivanje.

  2. Pritisnite F9 da biste pretvorili reference ćelija s vrijednostima. Excel pretvara vrijednosti u konstanta 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 ćeliju A8, a zatim pritisnite Ctrl + Shift + Enter da biste vidjeli ukupan broj znakova u ćelijama a2: a6 (66).

  3. Odaberite ćelije od A10, a zatim pritisnite Ctrl + Shift + Enter da biste vidjeli sadržaj najdulje ćelije rasponu a2: a6 (ćelija A3).

Koristi se 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 svaki tekstni niz u svakoj od ćelije u rasponu. Funkcija SUM zbraja vrijednosti pa prikazuje rezultat (66).

Traženje n najmanjih vrijednosti u rasponu

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

  1. Unesite neki slučajne brojeve u ćelijama A1:A11.

  2. Odaberite ćelije od C1 do C3. Ovaj skup ćelija će držite rezultata vratio formulu polja.

  3. Unesite sljedeću formulu, a zatim pritisnite Ctrl + Shift + Enter:

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

Ova formula koristi konstanta polja da biste procijeniti funkciju SMALL triput i vratili najmanju (1), drugi najmanji (2) i treći najmanju (3) članova u polju koja se nalazi u ćelijama A1:A10 da biste pronašli dodatne vrijednosti, dodajte dodatne argumente u Konstanta. Možete koristiti i dodatne funkcije koja sadrži formulu, kao što su SUM i AVERAGE. Ako, na primjer:

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

= AVERAGE (MALE (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 sljedeću formulu, a zatim pritisnite Ctrl + Shift + Enter:

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

Sada se mogu pomoći zna malo retka i funkcije INDIRECT . Funkcija ROW možete koristiti da biste stvorili polje uzastopnih cijelih brojeva. Ako, na primjer, odaberite prazni stupac 10 ćelija u radnoj knjizi vježbe, unesite ovu formulu polja i 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 u formulu koju ste koristili ranije – = LARGE (A5:A14,ROW(INDIRECT("1:3"))) – počevši od unutarnji zagradama i radi prema van: funkcija INDIRECT vraća skup tekstne vrijednosti u ovom slučaju vrijednosti od 1 do 3. Funkcija ROW shodno generira polja za stupčastu tri ćelije. Funkcija LARGE koristi vrijednosti u rasponu ćelija A5:A14 i vrednuje se triput jednom za svaki referencu koju vraća funkcija ROW . Vrijednosti 3200, 2700 i 2000 vraćaju polja stupčastu tri ćelije. Ako želite da biste pronašli dodatne vrijednosti, dodajte raspon ćelija veći funkcije INDIRECT .

Kao starijim primjerima, koristite ovu formulu s drugim funkcijama, kao što su SUM i AVERAGE.

Traženje najduljeg tekstnog niza unutar raspona ćelija

Prijeđite natrag u starijim tekstni niz primjeru u praznu ćeliju unesite sljedeću formulu pa pritisnite Ctrl + Shift + Enter:

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

Tekst "skup ćelija koji" će se prikazati.

Pogledajmo detaljnije Upoznavanje formulu, počevši od unutarnji elemente i radi prema van. Funkcija LEN vraća duljinu svih stavki u rasponu ćelija a2: a6. Funkcija MAX izračunava najveću vrijednost među te stavke koja odgovara najdulje tekstnog niza, koje 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.

Na kraju, funkcija INDEX otvara ove argumente: polja i broj redaka i stupaca iz tog polja. Raspon ćelija od a2: a6 omogućuje polja, funkcija MATCH sadrži adresa ćelije, a konačni argument (1) određuje 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 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))

Potvrđivanje

Dijelovi ovog članka temelje se na niz stupaca Excel napredni korisnik napisao Neven Šokec i prilagođen iz poglavlja 14 i 15 formulama 2002, adresara napisao Nevena Walkenbach, bivši MVP za Excel.

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.

Pogledajte i sljedeće

Dinamična polja i prelijevanje polja

Formula polja za dinamičku nasuprot naslijeđene CSE formulama 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.

×