Navodila in primeri za formule polja

V tej vaji boste izvedeli, kako uporabiti več- in enocelične formule polja za izračun nabora prodaje. V prvem nizu navodil boste za izračun nabora delnih vsot uporabili večcelično formulo. V drugem naboru pa boste za izračun skupne vsote uporabili enocelično formulo.

• Večcelična formula polja

  

• Tukaj izračunamo skupno prodajo coups in sedans za vsakega prodajalca tako, da v celico H10 vnesete =F10:F19*G10:G19 v celico H10.

  Ko pritisnete tipko Enter, se bodo rezultati prelili navzdol v celice H10:H19. Ko izberete poljubno celico v obsegu prelivanja, je obseg prelivanja označen z obrobo. Morda boste opazili tudi, da so formule v celicah H10:H19 zatemnjene. Tukaj so le sklici. Če želite prilagoditi formulo, morate izbrati celico H10, kjer je glavna formula.

• Enovrstična formula s polji

  

  V celico H20 vzorčnega delovnega zvezka vnesite ali kopirajte in prilepite =SUM(F10:F19*G10:G19)in nato pritisnite Enter.

  V tem primeru Excel pomnoži vrednosti polja (obseg celic F10 do g19) in nato uporabi funkcijo SUM ter doda vsote. Rezultat je prodajna skupna vsota 1.590.000 USD.

  Na tem primeru je prikazano, kako zmogljiva je lahko ta vrsta formule. Denimo, da imate 1.000 vrstic s podatki. S formulo polja v posamezni celici lahko seštejete del ali vse podatke, zato vam ni potrebno povleči formule navzdol skozi 1.000 vrstic. Opazili boste tudi, da je enocelične formule v celici H20 popolnoma neodvisna od večcelične formule (formula v celicah od H10 do H19). To je še ena prednost uporabe formul s polji – prilagodljivost. Druge formule v stolpcu H lahko spremenite, ne da bi vplivali na formulo v H20. Prav tako je dobra praksa, da imate tako neodvisno skupno vsoto, saj pomaga preveriti natančnost rezultatov.

• Druge prednosti formul polja so:

  • Consistency (doslednost)    Če kliknete poljubno celico od H10 navzdol, opazite enako formulo. Ta vrsta doslednosti zagotavlja večjo natančnost.

  • Varnost    Sestavnega dela večcelične formule polja ni mogoče prepisati. Kliknite na primer celico H11 in pritisnite tipko Delete. Excel ne bo spremenil rezultata polja. Če jo želite spremeniti, morate izbrati zgornjo levo celico v matriki ali celico H10.

  • Manjša velikost datotek    Pogosto lahko namesto več vmesnih formul uporabite posamezno formulo polja. V delovnem zvezku je na primer uporabljena ena formulo polja za izračun rezultatov v stolpcu E. Če bi uporabili standardne formule (denimo ==F10*G10, F11*G11, F12*G12 …), bi morali uporabiti 11 različnih formul, če bi želeli dobiti enake rezultate. To ni velik posel, kaj pa, če ste imeli na tisoče vrstic? Nato lahko naredite veliko razliko.

  • Učinkovitost    Funkcije polja so lahko učinkovit način za ustvarjanje zapletenih formul. Formula s polji =SUM(F10:F19*G10:G19) je enaka kot ta: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

  • Razlitje    Dinamične formule s polji se bodo samodejno prelili v izhodni obseg. Če so pomožni podatki Excelova tabela, se bo velikost polja samodejno spremenila, ko boste dodali ali odstranili podatke iz obsega polja, če uporabljate strukturirane sklice.

  • #SPILL! Napaka    Dinamična polja so uvedla #SPILL! Napaka, kar pomeni, da je predvideni obseg prelivanja zaradi neznanega razloga blokiran. Ko odpravite blokado, se formula samodejno prelije.

Konstante polja so sestavni deli formul polja. Ustvarjate jih z vnašanjem seznama elementov ali ročno postavitvijo seznama v zavite oklepaje ({ }), na primer:

={1\2\3\4\5} ali ={"Januar"\"Februar"\"Marec"}

Če elemente ločite z vejicami, ustvarite vodoravno polje (vrstico). Če jih ločite s podpičji, ustvarite navpično polje (stolpec). Če želite ustvariti dvodimenzionalno polje, ločite elemente v vsaki vrstici z vejicami, vsako vrstico pa s podpičji.

V naslednjih postopkih se boste seznanili z ustvarjanjem vodoravnih, navpičnih in dvodimenzionalnih konstant. Primeri bodo prikazani s funkcijo SEQUENCE samodejno ustvarjanje konstant polja in ročno vnesene konstante polja.

• Ustvarjanje vodoravne konstante

  Uporabite delovni zvezek iz prejšnjih primerov ali ustvarite novega. Izberite poljubno prazno celico in vnesite =SEQUENCE(1,5). Funkcija SEQUENCE ustvari polje z 1 vrstico in 5 stolpci enako kot ={1\2\3\4\5}. Prikaže se ta rezultat:

  

• Ustvarjanje navpične konstante

  Izberite poljubno prazno celico s prostorom pod njo in vnesite =SEQUENCE(5)ali ={1;2;3;4;5}. Prikaže se ta rezultat:

  

• Ustvarjanje dvodimenzionalne konstante

  Izberite poljubno prazno celico s sobo desno in pod njo ter vnesite =SEQUENCE(3,4). Vidite ta rezultat:

  

  Vnesete lahko tudi: ali ={1\2\3\4;5\6\7\8;9\10\11\12}, vendar bodite pozorni na mesto, kamor ste vnesli podpičje v primerjavi z vejicami.

  Kot lahko vidite, možnost SEQUENCE ponuja pomembne prednosti pred ročnim vnosom konstantnih vrednosti polja. Predvsem vam prihrani čas, lahko pa tudi zmanjša napake zaradi ročnega vnosa. Prav tako je lažje brati, še posebej, ker je podpičje težko razlikovati od ločil z vejicami.

Tukaj je primer, ki uporablja konstante polja kot del večje formule. V vzorčnem delovnem zvezku se premaknite do konstante v formuli delovnem listu ali ustvarite nov delovni list.

V celico D9 smo vnesli =SEQUENCE(1,5,3,1), vendar lahko v celice A9:H9 vnesete tudi 3, 4, 5, 6 in 7. Pri tem izboru številk ni nič posebnega. Za različnost smo izbrali le nekaj drugega kot 1–5.

V celico E11 vnesite =SUM(D9:H9*SEQUENCE(1,5))ali =SUM(D9:H9*{1\2\3\4\5}). Formule vrnejo 85.

Funkcija SEQUENCE ustvari enakovredno konstanto polja {1\2\3\4\5}. Ker Excel najprej izvede operacije v izrazih, ki so v oklepajih, sta naslednja elementa, ki se predvajata, vrednosti celic v D9:H9 in operator množenja (*). Na tej točki formula pomnoži vrednosti v shranjeni matriki z ustreznimi vrednostmi v konstanti. To je enakovredno:

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

Funkcija SUM končno doda vrednosti, vsota 85 pa je prikazana v celici A3.

Če se želite izogniti uporabi shranjenega polja in obdržati celotno operacijo v pomnilniku, zamenjajte shranjeno polje z drugo konstanto polja:

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

Elementi, ki jih lahko uporabite v konstantah

• Konstante polja lahko vsebujejo števila, besedilo, logične vrednosti (na primer TRUE in FALSE) in vrednosti napak, kot #N/V. Številke lahko uporabite v decimalni in strokovni obliki ali v obliki celega števila. Če vključite besedilo, ga dajte v narekovaje ( ").

• V konstantah polja ni mogoče imeti dodatnih polj, formul ali funkcij. Povedano drugače \endash v njih je lahko le besedilo ali številke, ki so ločene z vejicami ali podpičji. Če v Excel vnesete formulo, kot je na primer {1\2\A1:D4} ali {1\2\SUM(Q2:Z8)}, program prikaže varnostno sporočilo. V številskih vrednostih ni mogoče imeti odstotkov, dolarskega znaka, vejic ali oklepajev.

Eden od najboljših načinov za uporabo konstant polja je, da jih poimenujete. Konstante z imeni je veliko lažje uporabiti, drugi pa tako ne vidijo del zapletenosti formul polja. Če želite poimenovati konstanto polja in jo uporabiti v formuli, naredite to:

Pojdite v razdelek , > določena imena > določite ime. V polje Ime vnesite Četrtletje1. V polje Se sklicuje na vnesite to konstanto (zavite oklepaje vnesite ročno):

={"Januar"\"Februar"\"Marec"}

Pogovorno okno bi moralo biti zdaj videti tako:

Kliknite V redu, izberite poljubno vrstico s tremi praznimi celicami in vnesite =Četrtletje1.

Prikaže se ta rezultat:

Če želite, da se rezultati prelijejo navpično namesto vodoravno, lahko uporabite =TRANSPOSE(četrtletje1).

Če želite prikazati seznam 12 mesecev, kot ga morda uporabljate pri ustvarjanju finančnega izpiska, lahko s funkcijo SEQUENCE ustvarite enega od trenutnega leta. Pri tej funkciji je videti, da je za to funkcijo veljaven datum, ki ga lahko uporabite v drugih izračunih, čeprav je prikazan le mesec. Te primere najdete na delovnih listih z imenom konstanta in hitri vzorčni delovnih listov v vzorčnem delovnem zvezku.

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

To uporablja funkcijo DATE za ustvarjanje datuma na podlagi trenutnega leta, SEQUENCE ustvari konstanto v obliki polja od 1 do 12 za januar do december, nato pa funkcija TEXT pretvori obliko prikaza v »mmm« (Jan, Februar, Mar itd.). Če želite prikazati polno ime meseca, na primer januar, uporabite »mmmm«.

Ko poimenovano konstanto uporabite kot formulo polja, ne pozabite vnesti enačaja, kot je =Četrtletje1, ne samo Četrtletje1. Če ga ne vnesete, Excel obravnava polje kot niz z besedilom in formula ne bo delovala po pričakovanjih. Ne pozabite, da lahko uporabite kombinacije besedila in številk. Vse je odvisno od tega, kako ustvarjalni želite biti.

Na teh primerih so prikazani različni načini, kako lahko konstante polja uporabite v formulah polja. V nekaterih primerih je za pretvarjanje vrstic v stolpce in obratno uporabljena funkcija TRANSPOSE.

• Množenje elementov v polju

  Vnesite =SEQUENCE(1,12)*2ali ={1\2\3\4;5\6\7\8;9\10\11\12}*2

  Delite lahko tudi z (/), seštejete z (+) in odštejete z (–).

• Kvadratni koren elementov v polju

  Vnesite =SEQUENCE(1,12)^2ali ={1\2\3\4;5\6\7\8;9\10\11\12}^2

• Iskanje kvadratnega korena elementov v kvadratu v matriki

  Vnesite =SQRT(SEQUENCE(1,12)^2)ali =SQRT({1\2\3\4;5\6\7\8;9\10\11\12}^2)

• Transponiranje enodimenzionalne vrstice

  Vnesite =TRANSPOSE(SEQUENCE(1,5))ali =TRANSPOSE({1\2\3\4\5})

  Čeprav ste vnesli vodoravno konstanto polja, funkcija TRANSPOSE pretvori konstanto polja v stolpec.

• Transponiranje enodimenzionalnega stolpca

  Vnesite =TRANSPOSE(SEQUENCE(5,1))ali =TRANSPOSE({1;2;3;4;5})

  Čeprav ste vnesli navpično konstanto polja, funkcija TRANSPOSE pretvori konstanto v vrstico.

• Transponiranje dvodimenzionalne konstante

  Vnesite =TRANSPOSE(SEQUENCE(3,4))ali =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

  Funkcija TRANSPOSE pretvori vse vrstice v niz stolpcev.

V tem razdelku najdete primere osnovnih formul polja.

• Ustvarjanje polja iz obstoječih vrednosti

  V spodnjem primeru je razloženo, kako s formulami s polji ustvarite novo polje iz obstoječega polja.

  Vnesite =SEQUENCE(3,6,10,10)ali ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}

  Prepričajte se, da najprej vnesete { (levi zaviti oklepaj) nato pa 10 in } (desni zaviti oklepaj), potem ko vnesete 180, ker ustvarjate polje s števili.

  Nato vnesite =D9#ali =D9:I11 v prazno celico. Prikaže se polje celic velikosti 3 x 6 z istimi vrednostmi, kot jih vidite v celici D9:D11. Znak # se imenuje operatorja prelitega obsega, excelov način pa je, da se sklicuje na celoten obseg celic, namesto da bi ga vnesel.

  

• Ustvarjanje konstante polja iz obstoječih vrednosti

  Dobite lahko rezultate prelite formule polja in jo pretvorite v njene dele komponent. Izberite celico D9, nato pa pritisnite F2, da preklopite v način urejanja. Nato pritisnite F9, da pretvorite sklice na celice v vrednosti, ki jih Excel nato pretvori v konstanto polja. Ko pritisnete Enter, bi morala biti formula =D9#, zdaj ={10\20\30;40\50\60;70\80\90}.

• Štetje znakov v obsegu celic

  Na tem primeru je prikazano štetje znakov, tudi presledkov, v obsegu celic. To vključuje presledke.

  

  =SUM(LEN(C9:C13))

  V tem primeru funkcija LEN vrne dolžino posameznega besedilnega niza v vsaki celici v obsegu. Funkcija SUM nato sešteje te vrednosti in prikaže rezultat 66. Če želite pridobiti povprečno število znakov, lahko uporabite:

  =AVERAGE(LEN(C9:C13))

• Vsebina najdaljše celice v obsegu C9:C13

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

  Formula deluje, če je v obsegu podatkov le en stolpec s celicami.

  Podrobneje si oglejmo formulo – začeli bomo pri notranjih elementih in nadaljevali pot navzven. Funkcija LEN vrne dolžino vsakega od elementov v obsegu celic D2:D6. Funkcija MAX izračuna največjo vrednost med temi elementi, ki ustreza najdaljšim besedilnim nizom, ki je v celici D3.

  Tukaj so stvari nekoliko zapletene. Funkcija MATCH izračuna odmik (relativni položaj) celice, ki vsebuje najdaljši besedilni niz. To naredite tako, da zahteva tri argumente: iskano vrednost, polje za iskanje in vrsto ujemanja. Funkcija MATCH v polju za iskanje poišče navedeno iskano vrednost. V tem primeru je iskana vrednost najdaljši besedilni niz:

  MAX(LEN(C9:C13)

  ki je v tem polju:

  LEN(C9:C13)

  Argument vrste ujemanja v tem primeru je 0. Vrsta ujemanja je lahko vrednost 1, 0 ali -1.

  • 1 – vrne največjo vrednost, ki je manjša ali enaka vrednosti za iskanje

  • 0 – vrne prvo vrednost, ki je popolnoma enaka iskani vrednosti

  • -1 – vrne najmanjšo vrednost, ki je večja ali enaka navedeni iskani vrednosti

  • Če izpustite vrsto ujemanja, Excel predvideva 1.

  Funkcija INDEX ima te argumente: polje, številko vrstice in stolpca v polju. Obseg celic C9:C13 zagotovi polje, funkcija MATCH naslov celice, končni argument ( 1) pa določi, da je vrednost iz prvega stolpca v polju.

  Če želite dobiti vsebino najmanjšega besedilnega niza, zamenjajte max v zgornjem primeru z MIN.

• Iskanje najmanjših vrednosti n v obsegu

  V tem primeru je prikazano, kako poiščete tri najmanjše vrednosti v obsegu celic, kjer je bila ustvarjena matrika vzorčnih podatkov v celicah B9:B18has: =INT(RANDARRAY(10,1)*100). Funkcija RANDARRAY je spremenljiva funkcija, zato boste vsakič, ko Excel izračuna, dobili nov nabor naključnih števil.

  

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

  Ta formula s konstanto v obliki polja trikrat ovrednoti funkcijo SMALL vrne najmanjše 3 člane v matriki, ki je v celicah B9:B18, kjer je 3 spremenljiva vrednost v celici D9. Če želite poiskati več vrednosti, lahko povečate vrednost v funkciji SEQUENCE ali konstanti dodate več argumentov. S to formulo lahko uporabite tudi dodatne funkcije, na primer SUM ali AVERAGE. Primer:

  =SUM(SMALL(B9#,SEQUENCE(D9))

  =AVERAGE(SMALL(B9#,SEQUENCE(D9))

• Iskanje največjih vrednosti n v obsegu

  Če želite poiskati največje vrednosti v obsegu, lahko funkcijo SMALL zamenjate z LARGE. Poleg tega sta v naslednjem primeru uporabljeni še funkciji ROW in INDIRECT.

  Vnesite =LARGE(B9#,ROW(INDIRECT("1:3")))ali =LARGE(B9:B18,ROW(INDIRECT("1:3")))

  Na tej stopnji si bomo pobližje ogledali funkciji ROW in INDIRECT. Funkcijo ROW uporabite, če želite ustvariti polje zaporednih celih števil. Izberite na primer prazno polje in vnesite:

  =ROW(1:10)

  Formula ustvari stolpec 10 zaporednih celih števil. Če se želite soočiti z morebitno težavo, vnesite vrstico nad obseg s formulo polja (nad vrstico1). Excel prilagodi sklice na vrstico, formula pa ustvari cela števila od 2 do 11. Če želite odpraviti to težavo, v formulo dodajte funkcijo INDIRECT:

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

  Funkcija INDIRECT uporablja besedilne nize kot argumente (obseg 1:10 je zato v dvojnih narekovajih). Ko vstavite vrstice ali premaknete formulo polja, Excel ne prilagodi besedilne vrednosti. Zato funkcija ROW vedno ustvari polje želenih celih števil. Prav tako preprosto lahko uporabite funkcijo SEQUENCE:

  =SEQUENCE(10)

  Oglejmo si formulo, ki ste jo uporabili prej – =LARGE(B9#,ROW(INDIRECT("1:3"))) – začnite z notranjimi oklepaji in delate navzven: Funkcija INDIRECT vrne nabor besedilnih vrednosti, v tem primeru vrednosti od 1 do 3. Funkcija ROW pa ustvari polje s tremi celicami stolpca. Funkcija LARGE uporablja vrednosti v obsegu celic B9:B18 in je trikrat ovrednotena, enkrat za vsak sklic, ki ga vrne funkcija ROW. Če želite poiskati več vrednosti, funkciji INDIRECT dodajte večji obseg celic. To formulo lahko zdaj uporabljate tudi z drugimi funkcijami, denimo s SUM in AVERAGE.

• Vsota obsega, v katerem so vrednosti napak

  Funkcija SUM v Excelu ne deluje, če želite dobiti vsoto obsega, v katerem je vrednost napake, na primer #N/V. ali #N/A. Na tem primeru je prikazano, kako seštejete vrednosti v obsegu z imenom »Podatki«, v katerem je prišlo do napak:

  

• =SUM(IF(ISERROR(Podatki),"",Podatki))

  Formula ustvari novo polje, ki vsebuje izvirne vrednosti minus vse vrednosti napak. Funkcija ISERROR od notranjih funkcij naprej išče napake v obsegu celic (Podatki). IF funkcija vrne eno vrednost, če se pogoj, ki ste ga določili, ovrednoti kot TRUE, in drugo vrednost, če se ovrednoti kot FALSE. V tem primeru vrne prazne nize ("") za vse vrednosti napak, ker so ovrednotene kot TRUE, in vrne preostale vrednosti iz obsega (Podatki), ker so ovrednotene kot FALSE, kar pomeni, da ne vsebujejo vrednosti napak. Funkcija SUM nato izračuna vsoto filtriranega polja.

• Štetje vrednosti napak v obsegu

  Ta primer je podoben prejšnji formuli, vendar namesto filtriranja vrne število vrednosti napak v obsegu z imenom »Podatki«:

  =SUM(IF(ISERROR(Podatki),1,0))

  Ta formula ustvari polje, v kateri je vrednost 1 za celice z napakami in vrednost 0 za celice brez napak. Formulo lahko poenostavite in dosežete enake rezultate tako, da odstranite tretji argument funkcije IF, in sicer:

  =SUM(IF(ISERROR(Podatki),1))

  Če ne določite argumenta in v celici ni vrednosti napake, funkcija IF vrne FALSE. Formulo lahko še bolj poenostavite:

  =SUM(IF(ISERROR(Podatki)*1))

  Ta različica je pravilna, ker TRUE*1=1 in FALSE*1=0.

Morda boste morali sešteti vrednosti, ki temeljijo na pogojih.

Ta formula s polji na primer sešteje le pozitivna cela števila v obsegu z imenom »Prodaja«, ki predstavlja celice E9:E24 v zgornjem primeru:

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

Funkcija IF ustvari polje pozitivnih in neveljavnih vrednosti. Funkcija SUM prezre neveljavne vrednosti, ker je 0+0=0. V obsegu celic, ki ga uporabite za to formulo, so lahko poljubne številke vrstic in stolpcev.

Seštejete lahko tudi vrednosti, ki ustrezajo več pogojem. Ta formula s polji na primer izračuna vrednosti, večje od 0 IN manjše od 2500:

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

Ne pozabite, da ta formula vrne napako, če je v obsegu več neštevilskih celic.

Ustvarite lahko tudi formule polja, ki uporabljajo vrsto pogoja OR. Seštejete lahko na primer vrednosti, ki so večje od 0 ALI manjše od 2500:

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

Funkcij AND in OR ni mogoče neposredno uporabiti v formulah polja, ker vrneta posamezen rezultat TRUE ali FALSE, funkcije polja pa zahtevajo polja rezultatov. Težavi se logično izognete tako, kot je prikazano v prejšnji formuli. Povedano drugače, za vrednosti, ki ustrezajo pogoju OR ali AND, lahko izvedete matematične operacije, denimo seštevanje ali množenje.

Na tem primeru boste izvedeli, kako odstraniti ničle iz obsega, ko morate izračunati povprečje vrednosti v obsegu. V formuli je uporabljen obseg podatkov z imenom »Prodaja«:

=AVERAGE(IF(Prodaja<>0,Prodaja))

Funkcija IF ustvari polje vrednosti, ki niso enake 0, in jih poda funkciji AVERAGE.

Ta formula polja primerja vrednosti v dveh obsegih celic z imenom »MojiPodatki« in »VašiPodatki« ter vrne število razlik med njima. Če je vsebina obeh obsegov enaka, formula vrne vrednost 0. Če jo želite uporabiti, morajo biti obsegi celic enake velikosti in mer. Na primer, če je obseg »MojiPodatki« obseg 3 vrstic in 5 stolpcev, mora imeti tudi obseg »VašiPodatki« 3 vrstice in 5 stolpcev:

=SUM(IF(MojiPodatki=VašiPodatki,0,1))

Formula ustvari novo polje v enaki velikosti kot so obsegi, ki jih primerjate. Funkcija IF zapolni polje z vrednostjo 0 in 1 (0 za različne in 1 za enake celice). Funkcija SUM nato vrne vsoto vrednosti v polju.

Formulo lahko poenostavite, in sicer tako:

=SUM(1*(MojiPodatki<>VašiPodatki))

Podobno kot formula, ki šteje vrednosti napak v obsegu, deluje tudi ta formula, saj je TRUE*1=1 in FALSE*1=0.

Ta formula polja vrne številko vrstice največje vrednosti v obsegu z enim stolpcem z imenom »Podatki«

=MIN(IF(Podatki=MAX(Podatki),ROW(Podatki),""))

Funkcija IF ustvari novo polje, ki ustreza obsegu »Podatki«. Če je v ustrezni celici največja vrednost obsega, je v polju številka vrstice. V nasprotnem primeru je v polju prazen niz (""). Funkcija MIN za drugi argument uporabi novo polje in vrne najmanjšo vrednost, ki ustreza številki vrstice z največjo vrednostjo v »Podatki«. Če so v obsegu »Podatki« največje vrednosti enake, formula vrne vrstico prve vrednosti.

Če se želite vrniti na dejanski naslov celice z največjo vrednostjo, uporabite to formulo:

=ADDRESS(MIN(IF(Podatki=MAX(Podatki),ROW(Podatki),"")),COLUMN(Podatki))

Podobne primere najdete v vzorčnem delovnem zvezku na delovnem listu razlike med nabori podatkov delovnem listu.

V tej vaji boste izvedeli, kako uporabiti več- in enocelične formule polja za izračun nabora prodaje. V prvem nizu navodil boste za izračun nabora delnih vsot uporabili večcelično formulo. V drugem naboru pa boste za izračun skupne vsote uporabili enocelično formulo.

• Večcelična formula polja

Kopirajte celotno spodnjo tabelo in jo prilepite v celico A1 na praznem delovnem listu v Excelu.

Prodajna oseba	Vrsta avtomobila	Število prodanih	Cena enote enoto	Skupna prodaje
Cajhen	Limuzina	5	33000
	Kupe	4	37000
Zajc	Limuzina	6	24000
	Kupe	8	21000
Brlek	Limuzina	3	29000
	Kupe	1	31000
Potokar	Limuzina	9	24000
	Kupe	5	37000
Kopač	Limuzina	6	33000
	Kupe	8	31000
Formula (skupna vsota)			Skupna vsota
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. Če si želite ogledati skupno prodajo coups in sedans za vsakega prodajalca, izberite celice E2:E11, vnesite formulo =C2:C11*D2:D11, nato pa pritisnite Ctrl + Shift + Enter.

2. Če si želite ogledati skupno vsoto celotne prodaje, izberite celico F11, vnesite formulo =SUM(C2:C11*D2:D11) in pritisnite tipke Ctrl+Shift+Enter.

Ko pritisnete Ctrl+Shift+Enter, Excel formulo obda z zavitimi oklepaji ({ }) in vstavi primerek formule v vsako celico izbranega obsega. To se zgodi zelo hitro, zato so v stolpcu E skupne prodajne količine za vsako vrsto avta vsakega prodajalca. Če izberete E2, nato pa E3, E4 in tako dalje, boste videli, da je prikazana enaka formula: {=C2:C11*D2:D11}. 

• Ustvarjanje enocelične formule polja

V celico D13 delovnega zvezka vnesite to formulo in pritisnite Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

V tem primeru Excel pomnoži vrednosti polja (obseg celic C2 do D11) in nato uporabi funkcijo SUM ter doda vsote. Rezultat je prodajna skupna vsota 1.590.000 USD. Na tem primeru je prikazano, kako zmogljiva je lahko ta vrsta formule. Denimo, da imate 1.000 vrstic s podatki. S formulo polja v posamezni celici lahko seštejete del ali vse podatke, zato vam ni potrebno povleči formule navzdol skozi 1.000 vrstic.

Opazili boste tudi, da je enocelične formule v celici D13 popolnoma neodvisna od večcelične formule (formula v celicah od E2 do E11). To je še ena prednost uporabe formul s polji – prilagodljivost. Formule v stolpcu E lahko spremenite ali v celoti izbrišete ta stolpec, ne da bi vplivali na formulo v celici D13.

Druge prednosti formul polja so:

• Consistency (doslednost)    Če kliknete poljubno celico od E2 navzdol, opazite enako formulo. Ta vrsta doslednosti zagotavlja večjo natančnost.

• Varnost    Sestavnega dela večcelične formule polja ni mogoče prepisati. Kliknite na primer celico E3 in pritisnite tipko Izbriši. Izbrati morate celoten obseg celic (od E2 do E11) in spremeniti formulo za celotno polje ali pa jo pustite nespremenjeno. Kot dodatni varnostni ukrep morate pritisniti Ctrl+Shift+Enter, da potrdite morebitne spremembe formule.

• Manjša velikost datotek    Pogosto lahko namesto več vmesnih formul uporabite posamezno formulo polja. V delovnem zvezku je na primer uporabljena ena formulo polja za izračun rezultatov v stolpcu E. Če bi uporabili standardne formule (denimo =C2*D2, C3*D3, C4*D4 …), bi morali uporabiti 11 različnih formul, če bi želeli dobiti enake rezultate.

Po navadi je v formulah polja uporabljena standardna sintaksa. Vse se začnejo z enačajem (=), poleg tega pa vanje lahko vključite večino funkcije, vgrajenih v Excel. Ključna razlika je v tem, da pri uporabi formule s polji pritisnete tipke Ctrl+ Shift + Enter, da vnesete formulo. Ko to naredite, Excel postavi formulo v zavite oklepaje  – če jih vnesete ročno, bo formula pretvorjena v niz z besedilom in ne bo delovala.

Funkcije polja so lahko učinkovit način za ustvarjanje zapletenih formul. Formula polja =SUM(C2:C11*D2:D11) je enaka kot ta: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Ko delate z večceličnimi formulami, upoštevajte tudi:

• Izberite obseg celic, na podlagi katerih bo izračunan rezultat, preden vnesete formulo. To ste storili, ko ste ustvarili večcelično formulo polja, ko ste izbrali celice od E2 do E11.

• V formuli polja ni mogoče spreminjati vsebine posameznih celic. Če želite, izberite celico E3 v delovnem zvezku in pritisnite tipko Delete. Excel prikaže sporočilo, ki vam pove, da ne morete spremeniti dela polja.

• Premaknete ali izbrišete lahko celotno formulo polja, njenega dela pa ne. Povedano drugače – če želite skrčiti formulo polja, najprej izbrišite obstoječo formulo in nato začnite znova.

• Če želite izbrisati formulo s polji, izberite celoten obseg formule (na primer E2:E11), nato pa pritisnite Izbriši.

• V večcelično formulo polja ni mogoče vstavljati praznih celic ali brisati celice iz nje.

Morda boste formulo polja morali razširiti. Izberite prvo celico v obstoječem obsegu polja in nadaljujte, dokler ne izberete celotnega obsega, do katerega želite razširiti formulo. Pritisnite F2, da uredite formulo, nato pa pritisnite CTRL+SHIFT+ENTER, da potrdite formulo, ko prilagodite obseg formule. Ključ do tega je, da izberete celoten obseg, tako da začnete z zgornjo levo celico v polju. Zgornja leva celica je celica, ki jo je mogoče urejati.

Formule polja so odlične, vendar imajo lahko tudi nekaj slabosti:

• Občasno boste morda pozabili pritisniti tipke Ctrl+Shift+Enter. To se lahko zgodi tudi najbolj veščim uporabnikom Excela. Te kombinacije tipk pritisnite vedno, ko vnašate ali spreminjate formule polja.

• Drugi uporabniki delovnega zvezka morda ne bodo razumeli vaših formul. V praksi formule s polji običajno niso razložene na delovnem listu. Drugi uporabniki delovnega zvezka morda ne bodo razumeli vaših formul. Če bodo drugi spreminjali vaše delovne zvezke, se uporabi formul polja raje izogibajte, ker te v praksi običajno niso razložene na delovnem listu, ali pa poskrbite, da bodo uporabniki poznali formule polja in jih znali spremeniti, če bo to potrebno.

• Velike formule polja lahko upočasnijo izračune, kar je odvisno od hitrosti obdelovanja in pomnilnika v računalniku.

Konstante polja so sestavni deli formul polja. Ustvarjate jih z vnašanjem seznama elementov ali ročno postavitvijo seznama v zavite oklepaje ({ }), na primer:

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

Sedaj že veste, da morate pritisniti tipke Ctrl+Shift+Enter, ko ustvarjate nove formule polja. Ker so konstante polja sestavni del formul polja, jih vključite v zavite oklepaje. Nato pritisnete tipke Ctrl+Shift+Enter, da vnesete celotno formulo.

Če elemente ločite z vejicami, ustvarite vodoravno polje (vrstico). Če jih ločite s podpičji, ustvarite navpično polje (stolpec). Če želite ustvariti dvodimenzionalno polje, ločite elemente v vsaki vrstici z vejicami, vsako vrstico pa s podpičji.

Tu je polje v enojni vrstici: {1\2\3\4}. Tu je polje v enojnem stolpcu: {1;2;3;4}. In tu je polje dveh vrstic in štirih stolpcev: {1\2\3\4;5\6\7\8}. V polju z dvema vrsticama je prva vrstica 1, 2, 3 in 4, druga vrstica pa 5, 6, 7 in 8. Vrstici ločuje eno podpičje med 4 in 5.

Podobno kot s formulami lahko konstante polja uporabljate z večino vgrajenimi funkcijami Excela. V teh razdelkih je razloženo, kako ustvariti vse vrste konstant in kako jih uporabiti s funkcijami v Excelu.

V naslednjih postopkih se boste seznanili z ustvarjanjem vodoravnih, navpičnih in dvodimenzionalnih konstant.

Ustvarjanje vodoravne konstante

1. Na praznem delovnem listu izberite celice od A1 do E1.

2. V vnosno vrstico vnesite to formulo in pritisnite Ctrl+Shift+Enter:

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

   V tem primeru morate vtipkati zavite oklepaje ({ }), Excel pa bo dodal drugi nabor namesto vas.

   Prikaže se ta rezultat.

   

Ustvarjanje navpične konstante

1. V delovnem zvezku izberite stolpec s petimi celicami.

2. V vnosno vrstico vnesite to formulo in pritisnite Ctrl+Shift+Enter:

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

   Prikaže se ta rezultat.

   

Ustvarjanje dvodimenzionalne konstante

1. V delovnem zvezku izberite blok celic, ki je štiri stolpce širok in tri vrstice visok

2. V vnosno vrstico vnesite to formulo in pritisnite Ctrl+Shift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}

   Vidite ta rezultat:

   

Uporaba konstant v formulah

Tu je preprost primer, v katerem so uporabljene konstante.

1. V vzorčnem delovnem zvezku ustvarite nov delovni list.

2. V celico A1 vnesite 3, v B1 vnesite 4, v C1 vnesite 5, v D1 6 in v E1 7.

3. V celico A3 vnesite to formulo in pritisnite Ctrl+Shift+Enter:

   =SUM(A1:E1*{1\2\3\4\5})

   Excel obda konstanto še z enim naborom zavitih oklepajev, ker ste jo vnesli kot formulo polja.

   

   Vrednost 85 se pojavi v celici A3.

V naslednjem razdelku boste izvedeli, kako formula deluje.

V formuli, ki ste jo pravkar uporabili, je več delov.

1. Funkcija

2. Shranjeno polje

3. Operator

4. Konstanta polja

Zadnji element znotraj oklepajev je matrična konstanta: {1\2\3\4\5}. Ne pozabite, da Excel ne obdaja konstant polja z zavitimi oklepaji; jih dejansko vnesete. Ko v formulo s polji dodate konstanto, pritisnete tipke Ctrl+Shift+Enter, vnesete formulo.

Ker Excel najprej izvede operacije na izrazih, obdanih v oklepaje, sta naslednja elementa, ki ju moramo upoštevati, vrednosti, shranjene v delovnem zvezku (A1:E1) in upravljalcu. Na tej točki formula pomnoži vrednosti v shranjeni matriki z ustreznimi vrednostmi v konstanti. To je enakovredno:

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

Funkcija SUM končno doda vrednosti, vsota 85 pa je prikazana v celici A3.

Če se želite izogniti uporabi shranjenega polja in obdržati celotno operacijo v pomnilniku, zamenjajte shranjeno polje z drugo konstanto polja:

=SUM({3\4\5\6\7}*{1\2\3\4\5})

To poskusite tako, da kopirate funkcijo, izberete prazno celico v delovnem zvezku, prilepite formulo v vnosno vrstico in nato pritisnete Ctrl+Shift+Enter. Pojavi se enak rezultat kot v prejšnji vaji, v kateri je bila uporabljena formula:

=SUM(A1:E1*{1\2\3\4\5})

V konstantah polja so lahko številke, besedilo, logične vrednosti (na primer TRUE in FALSE) in vrednosti napak (denimo #N/V). Številke lahko uporabite v decimalni in strokovni obliki ali v obliki celega števila. Če vključite besedilo, ga dajte v narekovaje ( ").

V konstantah polja ni mogoče imeti dodatnih polj, formul ali funkcij. Povedano drugače \endash v njih je lahko le besedilo ali številke, ki so ločene z vejicami ali podpičji. Če v Excel vnesete formulo, kot je na primer {1\2\A1:D4} ali {1\2\SUM(Q2:Z8)}, program prikaže varnostno sporočilo. V številskih vrednostih ni mogoče imeti odstotkov, dolarskega znaka, vejic ali oklepajev.

Eden od najboljših načinov za uporabo konstant polja je, da jih poimenujete. Konstante z imeni je veliko lažje uporabiti, drugi pa tako ne vidijo del zapletenosti formul polja. Če želite poimenovati konstanto polja in jo uporabiti v formuli, naredite to:

1. Na zavihku Formule v skupini Definirana imena kliknite Določi ime.
   Prikaže pogovorno okno »Določi ime«.

2. V polje Ime vnesiteČetrtletje1.

3. V polje Se sklicuje na vnesite to konstanto (zavite oklepaje vnesite ročno)

   ={"Januar"\"Februar"\"Marec"}

   Vsebina pogovornega okna je zdaj videti tako:

   

4. Kliknite v redu in nato izberite vrstico s tremi praznimi celicami.

5. Vnesite to formulo in pritisnite tipke Ctrl+Shift+Enter.

   =Četrtletje1

   Prikaže se ta rezultat.

   

Ko poimenovano konstanto uporabite kot formulo polja, ne pozabite vnesti enačaja. Če ga ne vnesete, Excel obravnava polje kot niz z besedilom in formula ne bo delovala po pričakovanjih. Ne pozabite, da lahko uporabite kombinacije besedila in številk.

Če konstante polja ne delujejo pravilno, si oglejte te težave:

• Nekateri elementi morda niso ločeni z ustreznim znakom. Če spustite vejico ali podpičje, ali če jo postavite na napačno mesto, konstanta polja ne bo ustvarjena pravilno oz. bo prikazano opozorilno sporočilo.

• Morda ste izbrali obseg celic, ki se ne ujema s številom elementov v konstanti. Če na primer izberete stolpec s šestimi celicami za uporabo s konstanto s petimi celicami, #N/V prikaže vrednost napake v prazni celici. Če pa izberete premalo celic, Excel izpusti vrednosti, ki nimajo ustrezne celice.

Na teh primerih so prikazani različni načini, kako lahko konstante polja uporabite v formulah polja. V nekaterih primerih je za pretvarjanje vrstic v stolpce in obratno uporabljena funkcija TRANSPOSE.

Množenje elementov v polju

1. Ustvarite nov delovni list nato pa izberite blok praznih celic, ki je širok štiri stolpce in visok tri vrstice.

2. Vnesite to formulo, nato pa pritisnite Ctrl+Shift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}*2

Kvadratni koren elementov v polju

1. Izberite blok praznih celic, ki je širok štiri stolpce in visok tri vrstice.

2. Vnesite to formulo s polji in 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}

   Lahko pa vnesete formulo polja, v kateri je operator (^):

   ={1\2\3\4;5\6\7\8;9\10\11\12}^2

Transponiranje enodimenzionalne vrstice

1. Izberite stolpec s petimi praznimi celicami.

2. Vnesite to formulo, nato pa pritisnite Ctrl+Shift+Enter:

   =TRANSPOSE({1\2\3\4\5})

   Čeprav ste vnesli vodoravno konstanto polja, funkcija TRANSPOSE pretvori konstanto polja v stolpec.

Transponiranje enodimenzionalnega stolpca

1. Izberite vrstico s petimi praznimi celicami.

2. Vnesite to formulo in pritisnite Ctrl+Shift+Enter:

   =TRANSPOSE({1;2;3;4;5})

Čeprav ste vnesli navpično konstanto polja, funkcija TRANSPOSE pretvori konstanto v vrstico.

Transponiranje dvodimenzionalne konstante

1. Izberite blok celic, ki je širok tri stolpce in visok štiri vrstice.

2. Vnesite to konstanto in pritisnite Ctrl+Shift+Enter:

   =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

   Funkcija TRANSPOSE pretvori vse vrstice v niz stolpcev.

V tem razdelku najdete primere osnovnih formul polja.

Ustvarjanje polj in konstant polja iz obstoječih vrednosti

Na tem primeru je razloženo, kako uporabiti formule polja, če želite ustvariti povezave med obsegi celic in različnimi delovnimi listi. Izvedeli boste tudi, kako iz istega nabora vrednosti ustvariti konstanto polja.

Ustvarjanje polja iz obstoječih vrednosti

1. Na delovnem listu v Excelu izberite celice C8:E10 in vnesite to formulo:

   ={10\20\30;40\50\60;70\80\90}

   Prepričajte se, da najprej vnesete { (levi zaviti oklepaj) nato pa 10 in } (desni zaviti oklepaj), potem ko vnesete 90, ker ustvarjate polje s števili.

2. Pritisnite Ctrl+ Shift + Enter, ki to polje števil vnese v obseg celic C8:E10 s formulo s polji. Na vašem delovnem listu bi celice od C8 do E10 morale izgledati tako:

   10	20	30
   40	50	60
   70	80	90

3. Izberite obseg celic od C1 do E3.

4. V vnosno vrstico vnesite to formulo in pritisnite Ctrl+Shift+Enter:

   =C8:E10

   V celicah C1–E3 se pojavi polje s celicami 3 x 3, ki vsebuje enake vrednosti, kot jih najdete v celicah C8–E10.

Ustvarjanje konstante polja iz obstoječih vrednosti

1. Ko so izbrane celice C1:C3, pritisnite F2, želite preklopiti v način urejanja. 

2. Pritisnite F9 da pretvorite sklice na celice v vrednosti. Excel pretvori vrednosti v konstanto polja. Formula mora biti zdaj ={10\20\30;40\50\60;70\80\90}.

3. Pritisnite Ctrl+ Shift + Enter, da vnesete konstanto polja kot formulo polja.

Štetje znakov v obsegu celic

Na tem primeru je prikazano štetje znakov, tudi presledkov, v obsegu celic.

1. Kopirajte celotno tabelo in jo prilepite v celico A1 na delovnem listu.

   Podatki
   To je
   množica celic, ki
   je združena za
   oblikovanje
   enega stavka.
   Skupaj znakov v A2:A6
   =SUM(LEN(A2:A6))
   Vsebina največje celice (A3)
   =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

2. Izberite celico A8 in nato pritisnite Ctrl+Shift+Enter, da prikažete skupno število znakov v celicah A2:A6 (66).

3. Izberite celico A10 in nato pritisnite Ctrl+Shift+Enter, da si ogledate vsebino najdaljših celic A2:A6 (celica A3).

Naslednja formula je uporabljena v celici A8, ki prešteje skupno število znakov (66) v celicah od A2 do A6.

=SUM(LEN(A2:A6))

V tem primeru funkcija LEN vrne dolžino vsakega besedilnega niza celic v obsegu. Funkcija SUM sešteje te vrednosti in prikaže rezultat (66).

Iskanje najmanjših vrednosti n v obsegu

S pomočjo tega primera se boste naučili, kako poiskati tri najmanjše vrednosti v obsegu celic.

1. Vnesite nekaj naključnih števil v celice A1:A11.

2. Izberite celice od C1 do C3. Na osnovi teh celic bo izračunan rezultat, ki ga vrne formula polja.

3. Vnesite to formulo in pritisnite Ctrl+Shift+Enter:

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

Ta formula uporablja konstanto v obliki polja za trikratno ovrednotenje funkcije SMALL in vrne najmanjše (1), druge najmanjše (2) in tretje najmanjše (3) člane v matriki, ki je v celicah A1:A10 Če želite poiskati več vrednosti, konstanti dodajte več argumentov. S to formulo lahko uporabite tudi dodatne funkcije, na primer SUM ali AVERAGE. Primer:

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

=AVERAGE(MALI(A1:A10,{1\2\3})

Iskanje največjih vrednosti n v obsegu

Če želite v obsegu poiskati največje vrednosti, zamenjajte funkcijo SMALL s funkcijo LARGE. Poleg tega sta v naslednjem primeru uporabljeni še funkciji ROW in INDIRECT

1. Izberite celice od D1 do D3.

2. V vnosno vrstico vnesite to formulo, nato pa pritisnite Ctrl+Shift+Enter:

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

Na tej stopnji si bomo pobližje ogledali funkciji ROW in INDIRECT. Funkcijo ROW uporabite, če želite ustvariti polje zaporednih celih števil. Izberite na primer prazen stolpec z 10 celicami v delovnem zvezku za vajo, vnesite to formulo polja in pritisnite Ctrl+Shift+Enter:

=ROW(1:10)

Formula ustvari stolpec 10 zaporednih celih števil. Če se želite soočiti z morebitno težavo, vnesite vrstico nad obseg s formulo polja (nad vrstico1). Excel prilagodi sklice na vrstico, formula pa ustvari cela števila od 2 do 11. Če želite odpraviti to težavo, v formulo dodajte funkcijo INDIRECT.

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

Funkcija INDIRECT uporablja besedilne nize kot argumente (obseg 1:10 je zato v dvojnih narekovajih). Ko vstavite vrstice ali premaknete formulo polja, Excel ne prilagodi besedilne vrednosti. Zato funkcija ROW vedno ustvari polje želenih celih števil.

Oglejmo si formulo, ki ste jo uporabili prej – =LARGE(A5:A14,ROW(INDIRECT("1:3"))) – začnite z notranjimi oklepaji in delate navzven: Funkcija INDIRECT vrne nabor besedilnih vrednosti, v tem primeru vrednosti od 1 do 3. Funkcija ROW pa ustvari tricelični stolpični niz. Funkcija LARGE uporablja vrednosti v obsegu celic A5:A14 in je ovrednotena trikrat, enkrat za vsak sklic, ki ga vrne funkcija ROW. Vrednosti 3200, 2700 in 2000 so vrnjene v tricelični stolpični niz. Če želite poiskati več vrednosti, v funkcijo INDIRECT obseg celic.

Kot pri prejšnjih primerih lahko to formulo uporabljate z drugimi funkcijami, kot sta SUM in AVERAGE.

Iskanje najdaljšega besedilnega niza v obsegu celic

Vrnite se na prejšnji primer besedilnega niza, vnesite to formulo v prazno celico in pritisnite Ctrl+Shift+Enter:

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

Prikaže se besedilo »kup celic, ki«.

Podrobneje si oglejmo formulo – začeli bomo pri notranjih elementih in nadaljevali pot navzven. Funkcija LEN vrne dolžino vsakega od elementov v obsegu celic A2:A6. Funkcija MAX izračuna največjo vrednost med temi elementi, ki ustreza najdaljšim besedilnim nizom, ki je v celici A3.

Tukaj so stvari nekoliko zapletene. Funkcija MATCH izračuna odmik (relativni položaj) celice, ki vsebuje najdaljši besedilni niz. To naredite tako, da zahteva tri argumente: iskano vrednost, polje za iskanjein ujemanje. Funkcija MATCH iskalno polje poišče navedeno iskano vrednost. V tem primeru je iskana vrednost najdaljši besedilni niz:

(MAX(LEN(A2:A6))

ki je v tem polju:

LEN(A2:A6)

Argument vrste ujemanja je 0. Vrsta ujemanja je lahko sestavljena iz vrednosti 1, 0 ali -1. Če navedete 1, funkcija MATCH vrne največjo vrednost, ki je manjša ali enaka iskani vrednosti. Če navedete 0, funkcija MATCH vrne prvo vrednost, ki je popolnoma enaka iskani vrednosti. Če navedete -1, MATCH najde najmanjšo vrednost, ki je večja ali enaka navedeni iskani vrednosti. Če izpustite vrsto ujemanja, Excel predvideva 1.

Funkcija INDEX ima te argumente: polje, številko vrstice in stolpca v polju. Obseg celic A2:A6 zagotavlja matriko, funkcija MATCH zagotovi naslov celice, končni argument (1) pa določa, da je vrednost iz prvega stolpca v matriki.

V tem razdelku najdete primere dodatnih formul polja.

Vsota obsega, v katerem so vrednosti napak

Funkcija SUM v Excelu ne deluje, če želite dobiti vsoto obsega, v katerem je vrednost napake, na primer #N/V. Na tem primeru je prikazano, kako seštejete vrednosti v obsegu z imenom »Podatki«, v katerem je prišlo do napak.

=SUM(IF(ISERROR(Podatki),"",Podatki))

Formula ustvari novo polje, ki vsebuje izvirne vrednosti minus vse vrednosti napak. Z notranjimi funkcijami in delom navzven funkcija ISERROR v obsegu celic (Podatki) išče napake. Funkcija IF vrne določeno vrednost, če je pogoj, ki ga določite, ovrednoten kot TRUE, in drugo vrednost, če je ovrednoten kot FALSE. V tem primeru vrne prazne nize ("") za vse vrednosti napak, ker so ovrednotene kot TRUE, in vrne preostale vrednosti iz obsega (Data), ker so ovrednotene kot FALSE, kar pomeni, da ne vsebujejo vrednosti napak. Funkcija SUM izračuna skupno vsoto filtriranega polja.

Štetje vrednosti napak v obsegu

Ta primer je podoben prejšnji formuli, vendar namesto filtriranja vrne število vrednosti napak v obsegu z imenom »Podatki«.

=SUM(IF(ISERROR(Podatki),1,0))

Ta formula ustvari polje, v kateri je vrednost 1 za celice z napakami in vrednost 0 za celice brez napak. Formulo lahko poenostavite in dosežete enake rezultate tako, da odstranite tretji argument funkcije IF, in sicer:

=SUM(IF(ISERROR(Podatki),1))

Če ne določite argumenta in v celici ni vrednosti napake, funkcija IF vrne FALSE. Formulo lahko še bolj poenostavite:

=SUM(IF(ISERROR(Podatki)*1))

Ta različica je pravilna, ker TRUE*1=1 in FALSE*1=0.

Vsota vrednosti, ki temeljijo na pogojih

Morda boste morali sešteti vrednosti, ki temeljijo na pogojih. Ta formula polja na primer sešteje le pozitivna cela števila v obsegu z imenom »Prodaja«:

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

Funkcija IF ustvari polje pozitivnih in neveljavnih vrednosti. Funkcija SUM prezre neveljavne vrednosti, ker je 0+0=0. V obsegu celic, ki ga uporabite za to formulo, so lahko poljubne številke vrstic in stolpcev.

Seštejete lahko tudi vrednosti, ki ustrezajo več pogojem. Ta formula polja na primer izračuna vrednosti, večje od 0 in manjše ali enake 5:

=SUM((Prodaja>0)*(Prodaja<=5)*(Prodaja))

Ne pozabite, da ta formula vrne napako, če je v obsegu več neštevilskih celic.

Ustvarite lahko tudi formule polja, ki uporabljajo vrsto pogoja OR. Seštejte na primer vrednosti, manjše od 5 in večje od 15:

=SUM(IF((Prodaja<5)+(Prodaja>15),Prodaja))

Funkcija IF poišče vse vrednosti, manjše od 5 in večje od 15, in jih preda funkciji SUM.

Funkcij AND in OR ni mogoče neposredno uporabiti v formulah polja, ker vrneta posamezen rezultat TRUE ali FALSE, funkcije polja pa zahtevajo polja rezultatov. Težavi se logično izognete tako, kot je prikazano v prejšnji formuli. Povedano drugače, za vrednosti, ki ustrezajo pogoju OR ali AND, lahko izvedete matematične operacije, denimo seštevanje ali množenje.

Izračunavanje povprečja brez ničel

Na tem primeru boste izvedeli, kako odstraniti ničle iz obsega, ko morate izračunati povprečje vrednosti v obsegu. V formuli je uporabljen obseg podatkov z imenom »Prodaja«:

=AVERAGE(IF(Prodaja<>0,Prodaja))

Funkcija IF ustvari polje vrednosti, ki niso enake 0, in jih poda funkciji AVERAGE.

Štetje razlik med dvema obsegoma celic

Ta formula polja primerja vrednosti v dveh obsegih celic z imenom »MojiPodatki« in »VašiPodatki« ter vrne število razlik med njima. Če je vsebina obeh obsegov enaka, formula vrne vrednost 0. Če jo želite uporabiti, morajo biti obsegi celic enake velikosti in mer (na primer če je obseg »MojiPodatki« obseg 3 vrstic in 5 stolpcev, mora imeti tudi obseg »VašiPodatki« 3 vrstice in 5 stolpcev):

=SUM(IF(MojiPodatki=VašiPodatki,0,1))

Formula ustvari novo polje v enaki velikosti kot so obsegi, ki jih primerjate. Funkcija IF zapolni polje z vrednostjo 0 in 1 (0 za različne in 1 za enake celice). Funkcija SUM nato vrne vsoto vrednosti v polju.

Formulo lahko poenostavite, in sicer tako:

=SUM(1*(MojiPodatki<>VašiPodatki))

Podobno kot formula, ki šteje vrednosti napak v obsegu, deluje tudi ta formula, saj je TRUE*1=1 in FALSE*1=0.

Iskanje lokacije največje vrednosti v obsegu

Ta formula polja vrne številko vrstice največje vrednosti v obsegu z enim stolpcem z imenom »Podatki«

=MIN(IF(Podatki=MAX(Podatki),ROW(Podatki),""))

Funkcija IF ustvari novo polje, ki ustreza obsegu »Podatki«. Če je v ustrezni celici največja vrednost obsega, je v polju številka vrstice. V nasprotnem primeru je v polju prazen niz (""). Funkcija MIN za drugi argument uporabi novo polje in vrne najmanjšo vrednost, ki ustreza številki vrstice z največjo vrednostjo v »Podatki«. Če so v obsegu »Podatki« največje vrednosti enake, formula vrne vrstico prve vrednosti.

Če se želite vrniti na dejanski naslov celice z največjo vrednostjo, uporabite to formulo:

=ADDRESS(MIN(IF(Podatki=MAX(Podatki),ROW(Podatki),"")),COLUMN(Podatki))