Navodila in primeri za formule polja

Navodila in primeri za formule polja

Izkušen uporabnik Excela mora znati uporabljati formule s polji za izračunavanja, ki jih ne more izvesti s formulami brez polj. Članek temelji na nizu kolumn za zahtevne uporabnike Excela avtorja Colina Wilcoxa in je prilagojen 14. ter 15. poglavju Excel 2002 Formulas (Formule v Excelu 2002) knjige, ki jo je napisal John Walkenbach, strokovnjak MVP za Excel.

Več o formulah polja

Formule polja se pogosto imenujejo tudi formule CSE (Ctrl + Shift + Enter), ker je za dokončanje formule potrebno pritisniti Ctrl + Shift + Enter in ne le Enter.

Zakaj uporabiti formule polja?

Če ste v Excelu že delali s formulami, veste, da lahko izvajate precej zapletene operacije. Izračunate lahko na primer skupne stroške posojila za poljubno število let. Uporabljate jih lahko pri izvajanju zapletenih opravil, kot so:

  • štetje števila znakov v obsegu celic,

  • seštevanje številk, ki ustrezajo določenim pogojem, na primer najnižje vrednosti v obsegu ali številke med zgornjo in spodnjo mejo, ter

  • seštevanje vsake n-te vrednosti v obsegu vrednost.

Hiter uvod v polja in formule polja

Formula polja je formula, s katero lahko izvedete več izračunov v več elementih polja. Polja si lahko predstavljajte kot vrstico vrednosti, stolpec vrednosti ali kombinacijo vrstic in stolpcev vrednosti. Formule polja lahko vrnejo več rezultatov ali posamezen rezultat. Ustvarite jo lahko na primer v obsegu celic in jo uporabite, če želite izračunati stolpec ali vrstico z delno vsoto. Formulo polja lahko postavite tudi v posamezno celico in izračunate posamezno vrednost. Formula polja, ki vključuje več celic, se imenuje večcelična formula, če pa je v posamezni celici, pa enocelična formula.

V naslednjem razdellku je na primerih prikazano, kako ustvariti več- in enocelične formule polja.

Poskusite!

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

Tu je delovni zvezek, ki je vdelan v brskalnik. Čeprav vsebuje vzorčne podatke, morate vedeti, da formul polja ne morete ustvariti v vdelanem delovnem zvezku – za to potrebujete Excel. Odgovore in nekaj besedila, ki razlaga delovanje formule polja, lahko vidite v vdelanem delovnem zvezku, a če želite zares razumeti formule polja, si boste morali delovni zvezek ogledati v Excelu.

Ustvarjanje večcelične formule polja

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

    Prodajalec

    Vrsta
    avtomobila

    Prodanih

    Cena
    enote

    Skupna
    prodaja

    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)

  2. Če si želite ogledati skupno prodajo kupejev in limuzin po posameznih prodajalcih, izberite E2:E11, vnesite formulo =C2:C11*D2:D11 in pritisnite tipke Ctrl+Shift+Enter.

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

Delovni zvezek lahko prenesete s klikom na zeleni gumb Excel v črni vrstici na dnu delovnega zvezka. Datoteko lahko odprete v Excelu, izberete celice, ki vsebujejo formule polj, in za delovanje formule pritisnete Ctrl+Shift+Enter.

Če ob tem delate v Excelu, se prepričajte, da je List1 aktiven in nato izberite celice E2:E11. Pritisnite F2 in nato vnesite formulo =C2:C11*D2:D11 v trenutno celico E2. Če pritisnete tipko Enter, boste opazili, da vnese formulo le v celico E2 in prikaže 165000. Namesto tega po vnosu formule pritisnite tipke Ctrl+Shift+Enter namesto le Enter. Sedaj boste videli rezultate v celicah E2:E11. V vnosni vrstici boste opazili, da se formula prikaže kot {=C2:C11*D2:D11}. To vam pove, da gre za formulo polja, kot je prikazano v tabeli, ki sledi.

Ko pritisnete tipke Ctrl+Shift+Enter, Excel obda formulo z znaki za oklepaje ({ }) 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}.

Vsote v stolpcu E izračuna formula polja

Ustvarjanje enocelične formule polja

V celico F10 v delovnem zvezku vnesite to formulo in pritisnite tipke 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.

Enovrstična formula (v celici G11) je popolnoma neodvisna od večcelične formule (formula v celicah E2 do E11), kar je še ena od prednosti formul polja – prilagodljivost. Formule v stolpcu E lahko spremenite, ali v celoti izbrišete ta stolpec, ne da bi vplivali na formulo v G11.

Druge prednosti formul polja so:

  • 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 Delete. Izbrati morate celoten obseg celic (od E2 do E11) in spremeniti formulo za celotno polje ali pa jo pustite nespremenjeno. Kot dodaten varnostni ukrep morate pritisniti tipke Ctrl+Shift+Enter, da potrdite spremembo 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.

Sintaksa formule polja

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. Ko uporabljate formulo polja, je glavna razlika ta, da morate za vnos formule pritisniti tipke Ctrl+Shift+Enter. 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.

Formule polja so zelo učinkovite pri sestavljanju 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).

Vnašanje in spreminjanje formul polja

Pomembno    Pritisnite tipke Ctrl+Shift+Enter, kadar koli želite vnesti ali urediti formulo polja. To velja tako za enocelične kot tudi za večcelične formule.

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 polja, izberite celotno formulo (na primer =C2:C11*D2:D11), pritisnite tipko Delete in nato tipke Ctrl+Shift+Enter.

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

Razširitev formule polja

Morda boste formulo polja morali razširiti. Postopek ni težaven, vendar sledite zgoraj navedenim navodilom.

V tem delovnem listu smo dodali nekaj vrstic o prodaji, in sicer v vrsticah 12 –17. Tu želimo posodobiti formulo polja, da bo vključevala te dodatne vrstice.

To naredite v namiznem Excelu (ko prenesete delovni zvezek v računalnik).

Razširitev formule polja

  1. Kopirajte celotno tabelo v celico A1 na Excelovem delovnem listu.

    Prodajalec

    Vrsta
    avtomobila

    Prodanih

    Cena
    enote

    Skupna
    prodaja

    Cajhen

    Limuzina

    5

    33000

    165000

    Kupe

    4

    37000

    148000

    Zajc

    Limuzina

    6

    24000

    144000

    Kupe

    8

    21000

    168000

    Brlek

    Limuzina

    3

    29000

    87000

    Kupe

    1

    31000

    31000

    Potokar

    Limuzina

    9

    24000

    216000

    Kupe

    5

    37000

    185000

    Kopač

    Limuzina

    6

    33000

    198000

    Kupe

    8

    31000

    248000

    Koklič

    Limuzina

    2

    27000

    Kupe

    3

    30000

    Kopač

    Limuzina

    4

    22000

    Kupe

    1

    41000

    Mirtič

    Limuzina

    5

    32000

    Kupe

    3

    36000

    Skupna vsota

  2. Izberite celico E18, vnesite formulo za skupno vsoto =SUM(C2:C17*D2:D17) v celico A20 in pritisnite tipke Ctrl+Shift+Enter.
    Rezultat bi moral znašati 2.131.000.

  3. Izberite obseg celic s trenutno formulo polja (E2:E11) in prazne celice ob novih podatkih (E12:E17). Izberite torej celice E2:E17.

  4. Če želite preklopiti na način urejanja, pritisnite F2.

  5. V vnosni vrstici celico C11 spremenite v C17, D11 pa v D17 in pritisnite tipke Ctrl+Shift+Enter.
    Excel bo posodobil formulo v celicah E2 do E11, primerek formule pa bo postavil v nove celice E12 do E17.

  6. Vnesite formulo polja = SUM(C2:C17*D2*D17) v celico F17, da se bo sklicevala na celice od vrstice 2 do vrstice 17, in pritisnite tipke Ctrl+Shift+Enter, če želite vnesti formulo polja.
    Nova skupna vsota bi morala biti 2.131.000.

Slabosti formul polja

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

Na vrh strani

Več o konstantah polja

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.

Na vrh strani

Ustvarjanje eno- in dvodimenzionalne konstante

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

Ustvarjanje vodoravne konstante

  1. Uporabite delovni zvezek iz prejšnjih primerov ali ustvarite novega.

  2. Izberite celice od A1 do E1.

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

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

    V tem primeru vnesite levi in desno zavito oklepaj ({ }).

    Prikaže se ta rezultat.

    Konastanta vodoravnega polja v formuli

Ustvarjanje navpične konstante

  1. V delovnem zvezku izberite stolpec s petimi celicami.

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

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

    Prikaže se ta rezultat.

    Konstanta navpičnega polja v formuli

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 tipke Ctrl+Shift+Enter:

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

    Vidite ta rezultat:

    Dvodimenzionalna konstanta polja v formuli polja

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

    Formula polja s konstanto polja

    Vrednost 85 se pojavi v celici A3.

V naslednjem razdelku boste izvedeli, kako formula deluje.

Sintaksa konstante polja

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

Sintaksa formule polja s konstanto polja

1. Funkcija

2. Shranjeno polje

3. Operator

4. Konstanta polja

Zadnji element v oklepajih je konstanta polja: {1,2,3,4,5}. Ne pozabite, da Excel konstante polja ne obda z zavitimi oklepaji. Vi jih morate vnesti. Ko dodate konstanto v formulo polja, ne pozabite pritisniti tipk Ctrl+Shift+Enter, da 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 operator. Na tej stopnji formula pomnoži vrednosti v shranjenem polju z ustreznimi vrednostmi v konstanti, kar 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})

Če to želite preskusiti, kopirajte funkcijo, izberite prazno celico v delovnem zvezku, prilepite formulo v vnosno vrstico in nato pritisnite tipke 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})

Elementi, ki jih lahko uporabite v konstantah

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.

Poimenovanje konstant polja

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 se pogovorno okno Novo 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:

    Pogovorno okno »Urejanje imena« s formulo

  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.

    Poimenovano polje kot formula

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.

Odpravljanje težav s konstantami polja

Č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 z obsegom elementov v konstanti. Če na primer izberete stolpec s šestimi celicami, v katerem uporabite petcelično konstanto, bo v prazni celici prikazana vrednost napake #N/V. In obratno, če izberete preveč celic, Excel izpusti vrednosti, ki nimajo ustreznih celic.

Konstante polja v praksi

V teh primerih so prikazani različni načini, kako lahko konstante v obliki polja uporabite v formulah s polji. 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 in pritisnite tipke 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 polja in pritisnite tipke 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 s polji, 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 in pritisnite tipke Ctrl+Shift+Enter

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

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

Transponiranje enodimenzionalnega stolpca

  1. Izberite vrstico s petimi praznimi celicami.

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

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

Čeprav ste vnesli navpično konstanto v obliki 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 nato pa pritisnite tipke Ctrl+Shift+Enter:

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

Funkcija TRANSPOSE pretvori vse vrstice v niz stolpcev.

Na vrh strani

Delovanje osnovnih formul polja

V tem razdelku najdete primere osnovnih formul polja.

Ustvarjanje polj in konstant polja iz obstoječih vrednosti

V tem primeru je razloženo, kako uporabiti formule s polji, če želite ustvariti povezave med obsegi celic in različnimi delovnimi listi. Izvedeli boste tudi, kako iz istega nabora vrednosti ustvariti konstanto v obliki 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 tipke Ctrl+Shift+Enter, kar bo to polje s števili vneslo v obseg celic C8:E10 z uporabo formule polja.
    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 tipke 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 tipko F2, da preklopite v način za urejanje.
    Formula polja mora biti še vedno: = C8:E10.

  2. Če želite pretvoriti sklice na celice v vrednosti, pritisnite F9. Excel pretvori vrednosti v konstanto polja. Formula mora zdaj znašati »={10,20,30;40,50,60;70,80,90}«, podobno kot C8:E10.

  3. Če želite vnesti konstanto polja kot formulo polja, pritisnite tipke Ctrl+Shift+Enter.

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

  2. Izberite celico A9 in nato pritisnite tipke Ctrl+Shift+Enter, da si ogledate skupno število znakov v celicah A2:A6 (66).

  3. Izberite celico A12 in nato pritisnite tipke Ctrl+Shift+Enter, da si ogledate vsebino največje celice v obsegu A2:A6 (celica A3).

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)

Formula, ki je uporabljena v celici A9, sešteva skupno število znakov (66) v celicah A2–A6.

=SUM(LEN(A2:A6))

V tem primeru funkcija LEN vrne dolžino vsakega besedilnega niza celic v obsegu. Funkcija SUM nato sešteje te vrednosti in prikaže rezultat v celici C7, v kateri je formula.

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. Izberite celice od A16 do A18.
    Na osnovi teh celic bo izračunan rezultat, ki ga vrne formula polja.

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

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

V celicah A12–A14 so prikazane vrednosti 400, 475 in 500.

Pri vrednotenju funkcije SMALL formula trikrat uporabi konstanto polja in vrne najmanjšo (1), drugo najmanjšo (2) in tretjo najmanjšo (3) vrednost polja, ki je v celicah A1:A10. Če želite poiskati več vrednosti, v obseg A12:A14 dodajte konstanto in enakovredno število celic z rezultati. V tej formuli lahko uporabite tudi dodatne funkcije, na primer SUM ali AVERAGE Na primer:

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

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

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

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

V celicah A1–A3 so prikazane vrednosti 3200, 2700 in 2000.

Na tej stopnji si bomo pobližje ogledali funkciji ROW in INDIRECT. Funkcijo ROW uporabite, če želite ustvariti polje zaporednih celih števil. V delovnem zvezku izberite na primer prazen stolpec z 10 celicami, vnesite to formulo polja v celice A5:A14 in pritisnite tipke 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.

Pobližje si oglejmo formulo, ki ste ji uporabili prej –=LARGE(A5:A14,ROW(INDIRECT("1:3"))) . Začeli bomo pri notranjem oklepaju in nadaljevali navzven: funkcija INDIRECT vrne nabor besedilnih vrednosti, v tem primeru vrednosti 1–3. Funkcija ROWnato ustvari polje stolpca s tremi celicami. Funkcija LARGE uporabi vrednosti v obsegu celic A5:A14 in je ovrednotena trikrat – za vsak sklic, ki ga vrne funkcijaROW enkrat. Vrednosti 3200, 2700 in 2000 so vrnjene v polje stolpca s tremi vrsticami. Če želite poiskati več vrednosti, dodajte večji obseg celic v funkcijo INDIRECT.

To formulo lahko zdaj uporabljate tudi z drugimi funkcijami, denimo s SUM in AVERAGE.

Iskanje najdaljšega besedilnega niza v obsegu celic

Formula deluje, če je v obsegu podatkov le en stolpec s celicami. Na Listu3 vnesite sledečo formulo v celico A16 in pritisnite tipke Ctrl+Shift+Enter:

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

Besedilo »kup celic, ki« se prikaže v celici A16.

Podrobneje si oglejmo formulo – začeli bomo pri notranjih elementih in nadaljevali pot navzven. Funkcija LEN vrne dolžino vseh elementov v obsegu celic A6–A9. Funkcija MAX izračuna največjo vrednost med temi elementi, ki ustreza najdaljšemu besedilnemu nizu – celici A7.

Tu pa se stvari nekoliko zapletejo. Funkcija MATCH izračuna odmik (relativni položaj) celice, v kateri je najdaljši besedilni niz, za kar potrebuje tri argumente: iskano vrednost, iskano polje in vrsto ujemanja. Funkcija MATCH išče iskano polje za določeno iskano vrednost. V tem primeru je iskana vrednost najdaljši besedilni niz.

(MAX(LEN( A6 : A9 ))

ki je v tem polju:

LEN( A6:A9 )

Argument za vrsto ujemanja je 0. Vrsta ujemanja je lahko vrednosti 1, 0 ali -1. Če določite -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 enaka iskani vrednosti. Če določite -1, funkcija MATCH najde najmanjšo vrednost. ki je večja ali enaka navedeni iskani vrednosti. Če izpustite vrsto ujemanja, Excel domneva, da je 1.

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

Na vrh strani

Delovanje dodatnih formul polja

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 z izvirnimi vrednostmi brez vrednosti napak. Če začnemo pri notranjih funkcijah in svojo pot nadaljujemo navzven, potem funkcija ISERROR pregleda obseg celic (Podatki), ali so v njem napake. Funkcija IF vrne določeno vrednost, če se pogoj, ki ste ga določili, ovrednoti kot TRUE, in drugo vrednost, če se ovrednoti kot FALSE. V tem primeru funkcija vrne prazne nize (»«) za vse vrednosti napak, ker so ovrednotene kot TRUE, in preostale vrednosti obsega (Podatki), ker so ovrednotene kot FALSE. To pomeni, da v njih ni 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.

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

Na vrh strani

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel, pridobite podporo skupnosti Answers ali predlagate novo funkcijo oziroma izboljšavo na spletnem mestu Excel User Voice.

Glejte tudi

Pregled formul

Razširite svoja znanja
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×