Navodila in primeri za formule polja

Navodila in primeri za formule polja

Opomba: Najnovejšo vsebino pomoči v vašem jeziku vam želimo zagotoviti v najkrajšem možnem času. Ta stran je bila prevedena z avtomatizacijo in lahko vsebuje slovnične napake ali nepravilnosti. Naš namen je, da bi bila vsebina za vas uporabna. Ali nam lahko na dnu te strani sporočite, ali so bile informacije za vas uporabne? Tukaj je angleški članek za preprosto referenco.

Formula polja je formula, ki lahko izvede več izračunov v enem ali več elementih v matriki. Lahko si predstavljate matriko kot vrstico ali stolpec z vrednostmi ali kombinacijo vrstic in stolpcev vrednosti. Formule s polji lahko vrnejo več rezultatov ali en rezultat.

Če začnete s posodobitvijo septembra 2018 za Office 365, lahko katera koli formula, ki vrne več rezultatov, samodejno prelije vse navzdol ali v sosednje celice. To spremembo vedenja spremljajo tudi številne nove dinamične funkcije matrike. Dinamične formule s polji, ne glede na to, ali uporabljajo obstoječe funkcije ali dinamične matrične funkcije, morajo biti le vnos v eno celico, nato pa potrditi s pritiskom tipke Enter. Starejše formule za polja zahtevajo, da najprej izberejo celoten izhodni obseg, nato pa potrdite formulo s kombinacijo tipk CTRL + SHIFT + ENTER. Običajno se imenujejo» CSE «formule.

Z matričnimi formulami lahko izvajate zapletena opravila, na primer:

  • Hitro ustvarjanje vzorčnih naborov podatkov.

  • Preštejte število znakov, ki jih vsebuje obseg celic.

  • Seštejete lahko le števila, ki ustrezajo določenim pogojem, na primer najnižjim vrednostim v obsegu ali številom, ki sodijo med zgornjo in spodnjo mejo.

  • Seštej vsako n-to vrednost v obsegu vrednosti.

V teh primerih je prikazano, kako ustvarite formule s polji z več celicami in eno celico. Če je mogoče, smo vključili primere z nekaterimi dinamičnimi funkcijami matrike, kot tudi obstoječe formule s polji, ki so bile vnesene kot dinamične in podedovane matrike.

Prenesite naše primere

Prenesite primer delovnega zvezka z vsemi primeri polja formule v tem članku.

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

    Funkcija matrike z več celicami v celici H10 = F10: F19 * G10: G19 za izračun števila vozil, ki jih proda cena enote

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

    Ko pritisnete tipko Enter, se prikažejo rezultati, ki se razširijo na celice H10: H19. Opazite, da je obseg razlitja označen z obrobo, ko izberete katero koli celico v obsegu razlitja. Morda boste opazili tudi, da so formule v celicah H10: H19 zatemnjene. Če želite prilagoditi formulo, boste morali izbrati celico H10, v kateri je matrica formule, ki je na voljo.

  • Formula polja z eno celico

    Formula polja z enojno celico, če želite izračunati skupno vsoto s = SUM (F10: F19 * G10: G19)

    V celici H20 v primeru delovnega zvezka vnesite ali kopirajte in prilepite = sum (F10: F19 * G10: G19), nato pa pritisnite tipko Enter.

    V tem primeru Excel Zmnoži vrednosti v matriki (obseg celic F10 prek G19), nato pa uporabi funkcijo SUM, da sešteje skupno vsoto. 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. Poleg tega lahko opazite, da je formula za eno celico v celici H20 popolnoma neodvisna od formule v več celicah (formula v celicah H10 prek H19). To je še ena prednost uporabe formul s polji – fleksibilnost. Druge formule v stolpcu H lahko spremenite, ne da bi to vplivalo na formulo v H20. Prav tako je lahko dobra praksa, da ima neodvisne vsote, kot je ta, saj omogoča preverjanje točnosti rezultatov.

  • Dinamične matrične formule ponujajo tudi te prednosti:

    • Doslednost    Če kliknete katero koli od celic na strani H10 navzdol, se prikaže ista formula. Ta vrsta doslednosti zagotavlja večjo natančnost.

    • Varnost    Ne morete prepisati komponente formule s polji z več celicami. Kliknite na primer celico H11 in pritisnite DELETE. Excel ne bo spremenil izhoda polja. Če ga ž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. Primer prodaje v avtomobilu na primer uporablja eno formulo polja, da izračuna rezultate v stolpcu E. Če ste uporabili standardne formule, kot so = F10 * G10, F11 * G11, F12 * G12 itd., bi uporabili 11 različnih formul za izračun istih rezultatov. To ni velika stvar, toda kaj, če ste imeli na tisoče vrstic do vsote? Nato lahko naredi veliko razliko.

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

    • Segajo    Dinamične formule s polji se bodo samodejno razširile v izhodni obseg. Če so vaši izvorni podatki v Excelovi tabeli, se bodo formule dinamičnega polja samodejno popravile, ko boste dodali ali odstranili podatke.

    • #SPILL! Napaka    Dinamične matrike so uvedle #SPILL! Error, kar pomeni, da je predviden obseg razlitja blokiran iz neznanega razloga. Ko razrešite blokado, bo formula samodejno razlitje.

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 matriko, razmejite elemente v vsaki vrstici z vejicami in razmejite vsako vrstico s podpičji.

V naslednjih postopkih se boste seznanili z ustvarjanjem vodoravnih, navpičnih in dvodimenzionalnih konstant. S funkcijo» zaporedje «bomo pokazali primere, s katerimi boste samodejno ustvarili konstante polj, kot tudi ročno vnesene konstante polja.

  • Ustvarjanje vodoravne konstante

    Uporabite delovni zvezek iz prejšnjih primerov ali ustvarite novega. Izberite katero koli prazno celico in vnesite = zaporedje (1,5). Funkcija SEQUENCe zgradi 1 vrstico za 5 stolpčnega polja enako kot = {1, 2, 3, 4, 5}. Prikazan je naslednji rezultat:

    Ustvarjanje vodoravne konstante polja s = ZAPOREDJEm (1,5) ali = {1, 2, 3, 4, 5}

  • Ustvarjanje navpične konstante

    Izberite katero koli prazno celico s prostorom pod njo in vnesite = zaporedje (5)ali = {1; 2; 3; 4; 5}. Prikazan je naslednji rezultat:

    Ustvarjanje navpične konstante polja s = ZAPOREDJEm (5) ali = {1; 2; 3; 4; 5}

  • Ustvarjanje dvodimenzionalne konstante

    Izberite katero koli prazno celico s prostorom na desni in pod njo, nato pa vnesite = zaporedje (3, 4). Vidite ta rezultat:

    Ustvarjanje 3 vrstice za 4 stolpčne konstante stolpca s številom = zaporedje (3, 4)

    Vnesete lahko tudi: ali = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, vendar boste pozorni na mesto, kjer ste postavili podpičja v primerjavi z vejicami.

    Kot lahko vidite, je v možnosti» zaporedje «na voljo veliko prednosti pred ročnim vnosom vrednosti polja s konstanto. V prvi vrsti vam prihrani čas, lahko pa tudi zmanjšate napake iz ročnega vnosa. Prav tako je lažje brati, še posebej, ker je lahko delno dvopič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 pojdite na konstanto na delovnem listu formule ali pa ustvarite nov delovni list.

V celici D9 smo vnesli = zaporedje (1, 5, 3, 1), lahko pa vnesete tudi 3, 4, 5, 6 in 7 v celicah A9: H9. Na tem izbranem številu ni nič posebnega, Pravkar smo izbrali nekaj drugega kot 1-5 za razlikovanje.

V celico E11 vnesite = sum (D9: H9 * zaporedje (1, 5))ali = sum (D9: H9 * {1, 2; 3, 4, 5)). Formule vrnejo 85.

Uporabite konstante polja v formulah. V tem primeru smo uporabili = SUM (D9: H (* zaporedje (1, 5))

Funkcija SEQUENCe zgradi ekvivalent konstante polja {1, 2, 3, 4, 5}. Ker Excel izvede operacije na izrazih, ki so zaprti v oklepajih, je najprej na voljo naslednja dva elementa, ki se začnejo predvajati, vrednosti celic v D9: H9 in operator za množenje (*). V tem hipu 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)

Na koncu funkcija SUM sešteje vrednosti in vrne 85.

Če se želite izogniti uporabi shranjene matrike in obdržati operacijo v celoti v pomnilniku, jo lahko zamenjate z drugo konstanto polja:

= Sum (zaporedje (1, 5, 3; 1) * zaporedje (1,5))ali = sum ({3, 4, 5, 6, 7} * {1; 2; 3; 4; 5))

Elementi, ki jih lahko uporabite v konstantah polja

  • Konstante polja lahko vsebujejo števila, besedilo, logične vrednosti (na primer TRUE in FALSE) in vrednosti napak, kot je #N/A. Številke lahko uporabite v celoštevilskih, desetiških in znanstvenih oblikah. Če vključite besedilo, ga morate obkrožiti z narekovaji (» besedilo «).

  • 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 najboljših načinov za uporabo polj s konstantami 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 na formule _GT_ določena imena > definirajte ime. V polje ime vnesite vnesitečetrtletje1. V polje Se sklicuje na vnesite to konstanto (zavite oklepaje vnesite ročno)

={"Januar","Februar","Marec"}

Pogovorno okno bi moralo biti videti tako:

Dodajanje imenovane konstante polja iz formul > določena imena > ime upravitelja > novo

Kliknite v redu, nato pa izberite poljubno vrstico s tremi praznimi celicami in vnesite = vnesitečetrtletje1.

Prikazan je naslednji rezultat:

Uporabite imenovano konstanto polja v formuli, Like = Vnesitečetrtletje1, kjer je bil Vnesitečetrtletje1 opredeljen kot = {"januar", "februar", "marec"}

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

Če želite prikazati seznam 12 mesecev, kot bi ga morda uporabili pri ustvarjanju finančnega izkaza, lahko eno od trenutnih let ustvarite s funkcijo SEQUENCe. Ta funkcija je gladka, čeprav je prikazan le mesec, vendar je za to veljaven datum, ki ga lahko uporabite v drugih izračunih. Te primere najdete v poimenovanih konstantah polj in hitrih vzorčnih delovnih listih v primeru delovnega zvezka.

= TEXT (datum (leto (danes ()), zaporedje (1, 12), 1), "Mmm")

Uporaba kombinacije BESEDILnih, DATUMskih, letnih, DANAŠNJih in ZAPOREDNIH funkcij za ustvarjanje dinamičnega seznama 12 mesecev

S tem uporabite funkcijo DATE , da ustvarite datum, ki temelji na trenutnem letu, zaporedje ustvari konstanto polja od 1 do 12 za januar do decembra, nato pa funkcija TEXT pretvori obliko prikaza v» mmm «(Jan, Feb, Mar itd.). Če želite prikazati celotno ime meseca, kot je na primer januar, uporabite» mmmm «.

Ko uporabite imenovano konstanto kot matrično formulo, ne pozabite vnesti znaka za enačaj, kot je v = Vnesitečetrtletje1, ne le Vnesitečetrtletje1. Če ga ne vnesete, Excel obravnava polje kot niz z besedilom in formula ne bo delovala po pričakovanjih. Na koncu upoštevajte, da lahko uporabite kombinacije funkcij, besedila in številk. Vse je odvisno od tega, kako kreativno želite dobiti.

Na teh primerih so prikazani različni načini, kako lahko konstante polja uporabite v formulah polja. Nekateri primeri uporabljajo funkcijo prenosa za pretvorbo vrstic v stolpce in obratno.

  • Več posameznih elementov v matriki

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

    Razdelite lahko tudi z (/), dodate z (+) in odštejete (-).

  • Kvadratni koren elementov v polju

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

  • Iskanje kvadratnega korena kvadratnih elementov v matriki

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

  • Transponiranje enodimenzionalne vrstice

    ENTER = prenos (zaporedje (1, 5))ali = prenos ({1, 2, 3, 4, 5})

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

  • Transponiranje enodimenzionalnega stolpca

    ENTER = prenos (zaporedje (5, 1))ali = prenos ({1; 2; 3; 4; 5))

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

  • Transponiranje dvodimenzionalne konstante

    ENTER = prenos (zaporedje (3, 4))ali = prenos ({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 tem primeru je razloženo, kako s formulami polja ustvarite novo matriko iz obstoječega polja.

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

    Vnesite {(odpiranje oklepaja), preden vnesete 10, in} (zapiranje oklepaja), potem ko vnesete 180, ker ustvarjate polje števil.

    Nato vnesite = D9 #, OR = D9: i11 v prazno celico. V polju z enakimi vrednostmi, ki jih vidite v D9: D11, se prikaže niz celic 3 x 6. Znak» # «se imenuje operator za razlitje obsegain je Excel's način za sklicevanje na celoten obseg matrike, namesto da bi ga morali vnesti.

    Uporabite operator razliti obseg (#), da se sklicujete na obstoječo matriko

  • Ustvarjanje konstante polja iz obstoječih vrednosti

    Izvedete lahko rezultate razlite formule s polji in jih pretvorite v njene sestavne dele. Izberite Cell D9 in nato pritisnite tipko F2 , da preklopite v način urejanja. Nato pritisnite F9 , da pretvorite sklice celic na vrednosti, ki jih Excel nato pretvori v konstanto polja. Ko pritisnete tipko Enter, je formula, = D9 #, zdaj = {10, 20, 30; 40, 50, 60; 70, 80; 90}.

  • Štetje znakov v obsegu celic

    V tem primeru je prikazano, kako preštejete število znakov v obsegu celic. To vključuje presledke.

    Štetje skupnega števila znakov v obsegu in drugih matrik za delo z besedilnimi nizi

    = SUM (LEN (C9: C13))

    V tem primeru funkcija len vrne dolžino posameznega besedilnega niza v posameznih celicah v obsegu. Funkcija SUM nato sešteje te vrednosti in prikaže rezultat (66). Če ste želeli 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 elementa v obsegu celic D2: D6. Funkcija Maks izračuna največjo vrednost med temi elementi, ki ustreza najdaljši besedilni niz, 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: Iskana vrednost, iskalno polje in vrsto ujemanja. Funkcija MATCH poišče iskalno polje za določeno vrednost iskanja. V tem primeru je Iskana vrednost najdaljša besedilni niz:

    MAKS (LEN (C9: C13)

    ki je v tem polju:

    LEN (C9: C13)

    Argument» Vrsta ujemanja «v tem primeru je 0. Vrsta ujemanja je lahko 1, 0 ali 1 vrednost.

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

    • 0-vrne prvo vrednost, ki je popolnoma enaka vrednosti za iskanje.

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

    • Če izpustite vrsto ujemanja, Excel prevzame 1.

    Na koncu ima funkcija index te argumente: matriko in številko vrstice ter stolpca znotraj tega polja. Obseg celic C9: C13 določa matriko, Funkcija MATCH zagotavlja naslov celice in končni argument (1) določa, da vrednost izhaja iz prvega stolpca v matriki.

    Če ste želeli pridobiti vsebino najmanjšega besedilnega niza, boste v zgornjem primeru nadomestili največ 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 matrika vzorčnih podatkov v celicah B9: B18has bila ustvarjena z: = int (RANDARRAY(10; 1) * 100). Upoštevajte, da je RANDARRAY funkcija hlapne, zato boste vsakič prejeli nov nabor naključnih števil.

    Excelova formula polja, če želite poiskati n-to najmanjšo vrednost: = SMALL (B9 #, zaporedje (D9))

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

    Ta formula uporablja konstanto polja, da trikrat oceni majhno funkcijo in vrne najmanjše 3 člane v polje, ki je vsebovano v celicah B9: B18, kjer je 3 spremenljivka vrednost v celici D9. Če želite poiskati več vrednosti, lahko povečate vrednost v funkciji SEQUENCe ali pa dodate več argumentov v konstanto. Uporabite lahko tudi dodatne funkcije s to formulo, na primer sum ali povprečje. Na primer:

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

    = AVERAGE (MAJHNA (B9 #, ZAPOREDJE (D9))

  • Iskanje največjih vrednosti n v obsegu

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

    ENTER = Large (B9 #, Row (posredno ("1:3")))ali = Large (B9: B18, Row (posreden ("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 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 vrstic in formula zdaj ustvari celo število 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 (zato je obseg 1:10 obkrožen z narekovaji). Ko vstavite vrstice ali premaknete formulo polja, Excel ne prilagodi besedilne vrednosti. Zato funkcija ROW vedno ustvari polje želenih celih števil. ZAPOREDJE lahko preprosto uporabite:

    = ZAPOREDJE (10)

    Preglejmo formulo, ki ste jo prej uporabili – = LARGe (B9 #, ROW (POSREDNi ("1:3"))) – začenši z notranjimi oklepaji in delom navzven: funkcija INDIRECT vrne nabor besedilnih vrednosti, v tem primeru vrednosti od 1 do 3. Funkcija ROW nato ustvari polje stolpca s tremi celicami. Funkcija LARGe uporablja vrednosti v obsegu celic B9: B18 in je ocenjena trikrat, enkrat za vsak sklic, ki ga vrne funkcija ROW. Če želite poiskati več vrednosti, dodajte večji obseg celic v posredno funkcijo. Na koncu lahko z MALIMI primeri uporabite to formulo z drugimi funkcijami, kot sta SUM in POVPREČJE.

  • Vsota obsega, v katerem so vrednosti napak

    Funkcija SUM v Excelu ne deluje, ko poskušate sešteti obseg, ki vsebuje vrednost napake, na primer #VALUE! ali #N/A. V tem primeru je prikazano, kako seštejete vrednosti v obsegu, imenovanem podatki, ki vsebujejo napake:

    Uporabite matrike za obravnavo napak. Na primer = SUM (IF (ISERROR (Data), "", Data) sešteje obseg z imenom podatki, tudi če vključuje napake, na primer #VALUE! ali #NA!.

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

    Formula ustvari novo matriko z izvirnimi vrednostmi, ki so zmanjšane za vse vrednosti napak. Če začnete z notranjimi funkcijami in delate navzven, funkcija ISERROR išče napake v obsegu celic (podatki). Funkcija IF vrne določeno vrednost, če pogoj, ki ste ga določili, ovrednoti 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 za filtrirano matriko.

  • Štetje vrednosti napak v obsegu

    Ta primer je podoben prejšnji formuli, vendar vrne število vrednosti napak v obsegu, ki je imenovan za podatke, namesto da bi jih filtriral:

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

S polji lahko izračunate na podlagi določenih pogojev. = SUM (IF (Sales>0; Sales)) sešteje vse vrednosti, večje od 0 v obsegu, imenovanem» prodaja «.

Ta formula polja na primer sešteje le pozitivna cela števila v obsegu, imenovanem» prodaja «, ki predstavlja celice E9: E24 v zgornjem primeru:

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

Funkcija IF ustvari matriko pozitivnih in lažnih 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ša od 2500:

= SUM ((Sales>0) * (Sales<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 od 2500:

= SUM (IF ((Sales>0) + (Sales<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. Z drugimi besedami, izvajate matematične operacije, kot so seštevanje ali množenje na vrednostih, ki ustrezajo pogoju» ali «ali» in «.

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 želite uporabiti to formulo, morajo obsegi celic ustrezati enaki velikosti in isti dimenziji. Če je argument» MojiPodatki «na primer obseg 3 vrstic za 5 stolpcev, morajo biti Vašipodatki tudi 3 vrstice za 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 * (MyData<>YourData))

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 razlikah med delovnim listom naborov podatkov.

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 tabelo spodaj in jo prilepite v celico a1 na praznem delovnem listu.

Prodaja Oseba

Car Type (vrsta )

Številka Prodan

Enota Cena

Skupno število 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)

  1. Če si želite ogledati skupno prodajo coupes in sedans za posameznega prodajalca, izberite celice E2: E11, vnesite formulo = C2: C11 * D2: D11in nato pritisnite tipke CTRL + SHIFT + ENTER.

  2. Če si želite ogledati skupno vsoto celotne prodaje, izberite celico F11, vnesite formulo = sum (C2: C11 * D2: D11)in nato pritisnite tipke CTRL + SHIFT + ENTER.

Ko pritisnete CTRL + SHIFT + ENTER, Excel obdaja formulo s 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}

Vsote v stolpcu E izračuna formula polja

  • Ustvarjanje enocelične formule polja

V celico D13 delovnega zvezka vnesite to formulo in pritisnite tipke CTRL + SHIFT + ENTER:

=SUM(C2:C11*D2:D11)

V tem primeru Excel Zmnoži vrednosti v matriki (obseg celic od C2 do D11), nato pa uporabi funkcijo sum, da sešteje skupno vsoto. 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.

Poleg tega lahko opazite, da je formula za eno celico v celici D13 popolnoma neodvisna od formule v več celicah (formula v celicah od E2 do E11). To je še ena prednost uporabe formul s polji – fleksibilnost. Formule v stolpcu E lahko spremenite ali izbrišete stolpec v celoti, ne da bi vplivali na formulo v D13.

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 delete. Izbrati morate celoten obseg celic (od E2 do E11) in spremeniti formulo za celotno polje ali pa jo pustite nespremenjeno. Kot dodan varnostni ukrep morate pritisniti CTRL + SHIFT + ENTER , da potrdite katero koli 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.

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

Pomembno: Če želite vnesti formulo polja, pritisnite CTRL + SHIFT + ENTER . 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 celoten obseg formul (na primer E2: E11), nato pa pritisnite tipko delete.

  • Praznih celic ne morete vstavljati v celice ali jih izbrisati iz formule s polji z več celicami.

Morda boste formulo polja morali razširiti. Izberite prvo celico v obstoječem obsegu matrike in nadaljujte, dokler ne izberete celotnega obsega, za katerega želite razširiti formulo. Pritisnite tipko F2 , da uredite formulo, in nato pritisnite CTRL + SHIFT + ENTER , da potrdite formulo, ko prilagodite obseg formul. Ključno je, da izberete celoten obseg, ki se začne z zgornjo levo celico v matriki. Zgornja leva celica je tista, ki postane urejena.

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

  • Morda boste občasno pozabili pritisniti 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 vašega delovnega zvezka morda ne bodo razumeli vaših formul. V praksi polja formule na splošno niso razložene na delovnem listu. Če morajo druge osebe spremeniti vaše delovne zvezke, se morate izogibati formulam polj ali pa se prepričajte, da te osebe poznajo vse formule s polji in razumejo, kako jih spremeniti, če jih potrebujejo.

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

Do zdaj morate pritisniti CTRL + SHIFT + ENTER , ko ustvarjate formule s polji. Ker so konstante polja sestavni del formul polja, jih vključite v zavite oklepaje. Nato uporabite CTRL + SHIFT + ENTER , če želite vnesti 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.

Tukaj je polje v eni 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 dve vrstici je prva vrstica 1, 2, 3 in 4, druga vrstica pa je 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 tipke CTRL + SHIFT + ENTER:

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

    V tem primeru morate vnesti oklepaje za odpiranje in zapiranje ({}), Excel pa bo dodal drugi nabor za vas.

    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.

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 oklepaju je konstanta polja: {1, 2, 3, 4, 5}. Ne pozabite, da Excel ne obkroža konstant s polji s zavitimi oklepaji; dejansko jih vnašate. Upoštevajte tudi, da po tem, ko dodate konstanto v matrično formulo, pritisnite CTRL + SHIFT + ENTER , da vnesete formulo.

Ker Excel izvede operacije na izrazih, ki so zaprti v oklepajih, je najprej na voljo naslednja dva elementa, ki se začnejo predvajati, so vrednosti, shranjene v delovnem zvezku (a1: E1) in operatorju. V tem hipu 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})

Če želite preskusiti to, 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})

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 najboljših načinov za uporabo polj s konstantami 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» define name «.

  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.

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

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

  • Morda ste izbrali obseg celic, ki se ne ujemajo s številom elementov v konstanti. Če na primer izberete stolpec šestih celic za uporabo s konstanto s petimi celicami, se v prazni celici prikaže vrednost napake» #N/A «. Č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. Nekateri primeri uporabljajo funkcijo prenosa za pretvorbo vrstic v stolpce in obratno.

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

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 vnese to polje števil v obseg celic C8: E10 z uporabo formule 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 tipke CTRL + SHIFT + ENTER:

    =C8:E10

    3x3 polje celic se prikaže v celicah od C1 do E3 z enakimi vrednostmi, ki jih vidite v C8 s E10.

Ustvarjanje konstante polja iz obstoječih vrednosti

  1. Če ste izbrali celice C1: C3, pritisnite F2 , da preklopite v način urejanja. 

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

  3. Pritisnite CTRL + SHIFT + ENTER , če želite vnesti konstanto polja kot formulo s polji.

Š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 celica A8 in pritisnite CTRL + SHIFT + ENTER , da si ogledate skupno število znakov v celicah a2: A6 (66).

  3. Izberite celico A10 in nato pritisnite tipke CTRL + SHIFT + ENTER , da si ogledate vsebino najdaljših celic a2: A6 (celica A3).

Naslednja formula se uporablja v celici A8 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 nato 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 tipke CTRL + SHIFT + ENTER:

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

Ta formula uporablja konstanto polja, da trikrat oceni majhno funkcijo in vrne najmanjše (1), druge najmanjše (2) in tretje najmanjše (3) člane v matriki, ki je vsebovano v celicah a1: A10, če želite poiskati več vrednosti, dodate več argumentov v konstanto. Uporabite lahko tudi dodatne funkcije s to formulo, na primer sum ali povprečje. Na primer:

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

= AVERAGE (MAJHNA (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 in pritisnite tipke CTRL + SHIFT + ENTER:

    = LARGE (A1: A10; ROW (POSREDNO ("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 10 celic v delovnem zvezku za prakso, 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 prej uporabili – = Large (A5: A14, Row (posredni ("1:3"))) – začenši z notranjimi oklepaji in delom navzven: funkcija INDIRECT vrne nabor besedilnih vrednosti, v tem primeru vrednosti od 1 do 3. Funkcija Row nato ustvari columnar polje s tremi celicami. 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 se vrnejo v columnar polje s tremi celicami. Če želite poiskati več vrednosti, dodajte večji obseg celic v posredno funkcijo.

S prejšnjimi primeri lahko uporabite to formulo z drugimi funkcijami, kot sta sum in povprečje.

Iskanje najdaljšega besedilnega niza v obsegu celic

Vrnite se v primer prejšnjega 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)

Besedilo» gruča celic, ki se prikaže «.

Podrobneje si oglejmo formulo – začeli bomo pri notranjih elementih in nadaljevali pot navzven. Funkcija len vrne dolžino vsakega elementa v obsegu celic a2: A6. Funkcija maks izračuna največjo vrednost med temi elementi, ki ustreza najdaljši besedilni niz, 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: Iskana vrednost, iskalno poljein vrsto ujemanja. Funkcija Match poišče iskalno polje za določeno vrednost iskanja. V tem primeru je Iskana vrednost najdaljša besedilni niz:

(MAKS (LEN (A2: A6))

ki je v tem polju:

LEN (A2: A6)

Argument Vrsta ujemanja je 0. Vrsta ujemanja lahko obsega 1, 0 ali 1 vrednost. Če določite 1, funkcija Match vrne največjo vrednost, ki je manjša ali enaka vrednosti iskanja. Če določite 0, funkcija Match vrne prvo vrednost, ki je popolnoma enaka vrednosti za iskanje. Če navedete – 1, funkcija Match poišče najmanjšo vrednost, ki je večja ali enaka navedeni vrednosti iskanja. Če izpustite vrsto ujemanja, Excel prevzame 1.

Funkcija INDEX ima te argumente: polje, številko vrstice in stolpca v polju. Obseg celic a2: A6 zagotavlja matriko, funkcija Match zagotavlja naslov celice in končni argument (1) določa, da vrednost izhaja 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 matriko z izvirnimi vrednostmi, ki so zmanjšane za vse vrednosti napak. Če začnete z notranjimi funkcijami in delate navzven, funkcija ISERROR išče napake v obsegu celic (podatki). Funkcija if vrne določeno vrednost, če pogoj, ki ste ga določili, ovrednoti 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 za filtrirano matriko.

Š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 * (MyData<>YourData))

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

Potrdilo

Deli tega članka temeljijo na nizu Excelovih uporabniških stolpcev, ki jih je napisal Colin Wilcox in so prilagojeni iz poglavij 14 in 15 formul za Excel 2002, ki jih je napisala knjiga Johna Walkenbach, Nekdanja Excelova MVP.

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

Delovanje dinamičnih obsegov celic in prelitega polja

Dinamične formule s polji vs podedovane formule s polji

Funkcija FILTER

Funkcija RANDARRAY

Funkcija SEQUENCE

Funkcija SINGLE

Funkcija SORT

Funkcija SORTBY

Funkcija UNIQUE

Napake #PRELIVANJE! v Excelu

Pregled formul

Razširite poznavanje Officea
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.

×