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.

Matrična formula je formula, ki lahko opravljajo več izračunov v enega ali več elementov v polju. Si lahko predstavljate matrike kot vrstico ali stolpec vrednosti ali kombinacijo vrstic in stolpcev vrednosti. Formule polja lahko vrne več rezultatov ali en rezultat.

Začetek s September 2018 posodobitev za Office 365, formulo, ki vrne več rezultatov bo samodejno razširijo jih navzdol ali čez v sosednje celice. Ta sprememba vedenje tudi spremlja več novih dinamične matričnih funkcij. Dinamično matrične formule, ali se uporabljajo obstoječi ali funkcije dinamične polja, morate le za vnos v eno celico in nato potrdi tako, da pritisnete tipko Enter. Prej, podedovanimi matrične formule zahtevajo najprej izberete obseg celotne proizvodnje, nato pa potrditev formula s Kombinacijo tipk Ctrl + Shift + Enter. Jih uporabljate pogosto imenujejo tudi formule CSE .

Formule polja lahko uporabite za izvajanje zapletenih opravil, kot so:

  • Hitro ustvarite vzorec nize podatkov.

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

  • Seštevanje vsake n-te vrednosti v obsegu vrednosti.

Tako je prikazano, kako ustvarite več in eno celico matrične formule. Če je mogoče, smo pripravili primeri z nekaterimi dinamične matričnih funkcij, kot tudi obstoječe matrične formule, ki so vneseni kot dinamično in podedovanimi matrike.

Prenesite naše primere

Prenos delovnega zvezka na primer vsa polja formule primere 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 Večcelične polja v celici H10 = F10:F19 * G10:G19 za izračun števila avtomobilov prodal cena enote

  • Tukaj si izračuna skupno prodajo kupejev in limuzin po posameznih prodajalcih z vnosom = F19:F19 * G10:G19 v celici H10.

    Ko boste pritisnili Enter, boste videli rezultate, ki se razširijo navzdol do celice H10:H19. Obvestilo, da obseg prelivanja označena z obrobo, ko izberete katero koli celico v območju prelivanja. Opazite lahko tudi, da formule v celicah H10:H19 so obarvane sivo. So na voljo le za uporabo, če želite prilagoditi formulo, morate izbrati celici H10, kje so shranjeni matrico formulo.

  • Enocelične formule

    Enocelične formule za izračun skupna vsota z =SUM(F10:F19*G10:G19)

    V celici H20 vzročni delovni zvezek, vnesite ali kopirajte in prilepite =SUM(F10:F19*G10:G19)in pritisnite tipko Enter.

    V tem primeru Excel pomnoži vrednosti v matriki (obseg celic F10 do G19) in nato uporablja funkcijo SUM, če želite dodati vsote skupaj. Rezultat je skupna vsota $1,590,000 prodaje.

    V tem primeru je prikazano, kako Zmogljiva je lahko ta vrsta formule. Recimo, da imate 1000 vrstice s podatki. Del ali vse te podatke lahko seštejete tako, da ustvarite formulo polja v eni celici namesto vlečenja formulo po vrsticah, 1000. Prav tako obvestilo enocelično formulo v celici H20, ki je popolnoma neodvisno od večcelično formulo (formula v celici H10 prek H19). To je druge prednosti formul polja – prilagodljivost. Druge formule v stolpcu H lahko spremenite, ne da bi vplivali na formulo v H20. Lahko tudi dobre prakse, da neodvisno vsote tako, kot je preverjanje točnosti rezultate pomaga.

  • Dinamični matrične formule prednosti:

    • Skladnost    Če kliknete katero koli od celic iz H10 navzdol, se prikaže isto formulo. To usklajenost lahko pomaga zagotoviti večjo natančnostjo.

    • Varnost    Komponenta v večcelično formulo polja ni mogoče prepisati. Na primer, kliknite celico H11 in pritisnite tipko Delete. Excel ne bo spremenilo v matriki izhod. Če želite spremeniti, morate izberite zgornjo levo celico v matriki ali v celici H10.

    • Manjša velikost datotek    Uporabite lahko pogosto eno matrično formulo namesto več vmesno formul. Na primer avto prodaje primer uporablja eno matrično formulo za izračun rezultati v stolpcu E. Če bi uporabili kot standardne formule = F10 * G10, F11 * G11, F12 * G12, itd., ste bi uporabili 11 različnih formul za izračun iste rezultate. To ni velik posel, toda kaj, če imate več tisoč vrstice skupaj? Nato lahko velika razlika.

    • Učinkovitost    Matrika funkcije lahko učinkovit način za ustvarjanje zapletenih formul. Polja formule =SUM(F10:F19*G10:G19) je enako, kot to: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Polil    Dinamični matrične formule se samodejno razširijo v izhodni obseg. Če vir podatkov v Excelovi tabeli, nato pa v dinamično matrične formule bo samodejno spreminjanje velikosti kot dodajanje ali odstranjevanje podatkov.

    • #SPILL! napake    Dinamično nizi, uveden v #SPILL! napake, kar pomeni, da prvotna prelivanja obseg blokirana zaradi neznanega razloga. Če ste odpravili blokado, bo formula samodejno razširijo.

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 jih ločite elemente z uporabo vejice, ustvarite vodoravno polje (vrstice). Če elemente ločite s podpičji, ustvarite navpično matriko (stolpcev). Če želite ustvariti dvodimenzionalno matriko, razmejitev elementov v vsaki vrstici z vejicami in razmejitev vsako vrstico s podpičji.

Te postopke boste seznanili z ustvarjanjem vodoravnih, navpičnih in dvodimenzionalnih konstant. Smo pokazali primeri uporabi ZAPOREDJA funkcije za samodejno ustvarjanje konstante polja, kot tudi ročno vnesli konstant polja.

  • Ustvarjanje vodoravne konstante

    Uporaba delovnega zvezka iz prejšnjih primerov ali ustvarite nov delovni zvezek. Izberite katero koli prazno celico in vnesite =SEQUENCE(1,5). Funkcija zaporedje zgradi 1 vrstico z 5 polja stolpca enako = {1,2,3,4,5}. Prikaže ta rezultat:

    Ustvarite vodoravno konstanto polja s =SEQUENCE(1,5) ali = {1,2,3,4,5}

  • Ustvarjanje navpične konstante

    Izberite katero koli prazno celico prostor pod njo, in vnesite =SEQUENCE(5)ali = {1; 2; 3; 4; 5}. Prikaže ta rezultat:

    Ustvarjanje navpično konstanto polja s = SEQUENCE(5) ali = {1; 2; 3; 4; 5}

  • Ustvarjanje dvodimenzionalne konstante

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

    Ustvarjanje 3 vrstice s konstanto polja 4 stolpec z =SEQUENCE(3,4)

    Vnesete lahko tudi: ali = {1,2,3,4; 5,6,7,8; 9,10,11,12}, vendar boste želeli Bodite pozorni na kam podpičji v primerjavi z vejicami.

    Kot lahko vidite, zaporedje možnost zagotavlja pomembne prednosti v primerjavi z ročno vnesete svoje nespremenljive vrednosti polja. Primarno, prihranite čas, vendar jo lahko tudi zmanjšate napake od ročnega vnosa. Je tudi lažje brati, še posebej, kot je podpičji lahko težko razlikovati od ločila z vejico.

Tukaj je primer, da uporablja matričnih konstant kot del večje formulo. V vzorčnem delovnem zvezku, pojdite na delovnem listu konstante v formuli ali ustvarite nov delovni list.

V celico D9 smo vnesli =SEQUENCE(1,5,3,1), vendar lahko vnesete tudi 3, 4, 5, 6 in 7 v A9:H9 celice. Nič posebno o tem, da določeno število izbira, izbrali smo le nekaj, kar je različen od 1 do 5 za razlikovanje.

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

Uporaba konstant polj v formulah. V tem primeru smo uporabili = SUM (D9:H(*SEQUENCE(1,5))

Funkcija zaporedje zgradi ekvivalent polja konstante {1,2,3,4,5}. Ker izvajanja operacij izrazov, najprej med oklepaji, naslednjih dveh elementov, ki pridejo v poštev so vrednosti celic v D9:H9 in za množenje (*). Na tej točki formula pomnoži vrednosti v shranjenega polja tako, da ustreznih vrednosti v konstanti. To je:

=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 želite izogniti uporabi shranjenega polja in operacijo v pomnilniku, ga lahko zamenjate 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})

Elemente, ki jih lahko uporabite v konstantah polja

  • Konstante polja lahko vsebuje številke, besedilo, logične vrednosti (na primer TRUE in FALSE) in vrednosti napak, na primer # n/v. Uporabite lahko števil v cela števila, decimalna in znanstvenih oblikah. Če vključite besedilo, morate obdajte 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.

Je ena od najboljši načini uporabe konstant polja njihovo ime. Poimenovane konstante lahko veliko lažje, če želite uporabiti, in lahko skrijete, nekatere zapletenost formul polja od drugih. Imenovanje konstante polja in jo uporabite v formuli, naredite to:

Pojdite na formule > določena imena > določi ime. V polje ime vnesitečetrtletje1. V polju se sklicuje na vnesite to konstanto (ne pozabite, da zavite oklepaje vnesite ročno):

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

Pogovorno okno bi morala biti videti tako:

Dodaj imenovani matrična konstanta iz formule > določena imena > upravitelj imen > novo

Kliknite v redu, nato pa izberite vse vrstice s tremi praznih celic in vnesite = četrt1.

Prikaže ta rezultat:

Uporaba poimenovano polje konstante v formuli, kot so na primer = četrt1, kjer četrt1 ste ga določili kot = {»Januar« , »Februar« , »marec ««}

Če želite rezultate razširijo navpično, namesto vodoravno, uporabite =TRANSPOSE(četrt1).

Če želite prikazati seznam 12 mesecev, kot jih uporabljate pri ustvarjanju finančni izkaz, lahko osnovni enkratnih trenutno leto s funkcijo zaporedje. Lepo stvar ta funkcija je, da kljub temu, da prikazuje le v mesecu, je veljaven datum, za njim, ki jih lahko uporabite v drugih izračunih. V teh primerih boste našli na delovnih listih, imenovan konstanto polja in hitro vzorčni nabor podatkov v delovnem zvezku, na primer.

=TEXT(Date(year(Today()),Sequence(1,12),1),"Mmm")

Uporabite kombinacijo besedila, datum, leto, danes, in zaporedje funkcij za ustvarjanje dinamične seznam 12 mesecev

To funkcijo DATE uporabi za ustvarjanje glede na trenutno leto datuma, zaporedje ustvari matrično konstanto od 1 do 12 za januar do December, potem funkcija TEXT pretvori oblika prikaza za "mmm" (Jan, Feb., Mar, itd.). Če želite prikazati celotno ime, na primer januar, bi lahko uporabite »mmmm« .

Če uporabljate poimenovana konstanta kot matrično formulo, ne pozabite vnesti enačaj, kot na = četrt1, ne le četrt1. Če ne, Excel obravnava matrike kot besedilni niz in formula ne deluje po pričakovanjih. Na koncu, ne pozabite, da uporabite kombinacijo funkcij, besedila in števil. Vse je odvisno od tega, kako ustvarjalni, ki jim želite poslati.

Ti primeri prikazujejo nekaj načinov, v katerem lahko postavite konstant polja, če želite uporabiti v matričnih formulah. Nekaj primerov s katerim, funkcija TRANSPOSE pretvori vrstic v stolpce in obratno.

  • Več vsak element matrike

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

    Lahko tudi delite z (/), dodajte z (+) in odštevanje z (-).

  • Kvadratni koren elementov v polju

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

  • Poiščite kvadratni koren kvadratnih elementov v polju

    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 v obliki polja, funkcija TRANSPOSE pretvori konstanto v obliki polja v stolpec.

  • Transponiranje enodimenzionalnega stolpca

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

    Čeprav ste vnesli navpično konstanto v obliki 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 naslednjem primeru je razloženo, kako uporabiti formule polja, če želite ustvariti novega polja iz obstoječih 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}

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

    Nato vnesite = D9 #ali = D9:I11 v prazno celico. 3 x 6 matrike celic, ki se prikaže enake vrednosti, ki se prikaže v D9: D11. Znak # se imenuje izteče operator obsega, in je Excelovimi način za sklicevanje na celotno polja obseg, namesto da bi ga vnesite.

    Uporabite operator razlijejo obsega (#) v sklic obstoječega polja

  • Ustvarjanje konstante polja iz obstoječih vrednosti

    Izvedete lahko rezultate razlijejo matrično formulo in ki pretvorite v svojih sestavnih delov. Izberite celico D9, nato pa pritisnite F2 , da preklopite v način za urejanje. Nato pritisnite tipko F9 , da pretvorite sklice na celice z vrednostmi, ki Excel nato pretvori v matrično konstanto. Ko boste pritisnili Enter, formula, = D9 #, naj zdaj = {10,20,30; 40,50,60; 70,80,90}.

  • Štetje znakov v obsegu celic

    Naslednji primer prikazuje, kako za štetje znakov v obsegu celic. To vključuje presledkov.

    Štetje skupno število znakov v obsegu in drugih polj za delo s besedilni nizi

    = SUM (LEN(C9:C13))

    V tem primeru funkcija LEN vrne dolžino vsak besedilni niz v vseh celic v obsegu. Funkcija SUM nato sešteje te vrednosti in prikaže rezultat (66). Če se povprečno število znakov, lahko uporabite:

    = AVERAGE (LEN(C9:C13))

  • Vsebina največje celice v obsegu C9:C13

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

    Ta formula deluje le, če je obseg podatkov, ki vsebuje en stolpec celice.

    Poglejmo si pobližje formulo, od notranje elemente in delo navzven. Funkcija LEN vrne dolžino vsak element v obsegu D2:D6. Funkcija MAX izračuna največjo vrednost med te elemente, ki ustreza najdaljšega besedilnega niza, ki je v celici D3.

    Tukaj je, kjer dobite nekoliko zapletene stvari. Funkcija MATCH izračuna odmik (relativni položaj) celice, ki vsebuje najdaljšega besedilnega niza. To narediti, to zahteva tri argumente: vrednost za iskanje, v polja za iskanje in vrsto rezultata. Funkcija MATCH išče polja za iskanje za vrednost, navedeno za iskanje. V tem primeru je vrednost za iskanje najdaljšega besedilnega niza:

    MAX(LEN(C9:C13)

    ki je v tem polju:

    LEN(C9:C13)

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

    • 1 - vrne največjo vrednost, ki je manjša ali enaka iskanjem val

    • 0 - vrne prvo vrednost enaka vrednosti za iskanje

    • -1 - vrne najmanjšo vrednost, ki je večja od ali enaka vrednosti, določene za iskanje

    • Če izpustite vrsto ujemanja, Excel predvideva 1.

    Na koncu funkcija INDEX ima te argumente: matrike in število vrstic in stolpcev v ta polja. Obseg celic C9:C13 ponuja polja, Funkcija MATCH ponuja naslov celice in končne argument (1) določa, da vrednost prihaja iz prvega stolpca v matriki.

    Če bi želeli vsebino najmanjše besedilni niz, bi zamenjajte MAX v zgornjem primeru z MIN.

  • Iskanje najmanjših vrednosti n v obsegu

    Ta primer prikazuje, kako poiskati tri najmanjše vrednosti v obsegu celic, kjer matriko z vzorčnimi podatki v celicah B9:B18has ustvarjen z: = INT (RANDARRAY(10,1) * 100). Upoštevajte, da RANDARRAY spremenljive funkcije, tako da boste dobili nov nabor naključna števila vsakič, ko se Excel izračuna.

    Formula polja, če želite poiskati n-to najmanjšo vrednost za Excel: =SMALL(B9#,SEQUENCE(D9))

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

    Ta formula uporablja matrično konstanto za majhne funkcija ovrednotena trikrat in se vrniti najmanjše 3 člani v matriki, ki je v celicah B9:B18, kjer je 3 spremenljivke vrednost v celici D9. Če želite poiskati več vrednosti, lahko povečate vrednost v ZAPOREDJU funkcije ali dodate več argumentov konstanta. Uporabite lahko tudi dodatne funkcije s to formulo, na primer vsota ali POVPREČJE. Na 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 zamenjate funkcija SMALL s funkcije LARGE. Poleg tega je ta primer uporablja vrstico in INDIRECT funkcije.

    Vnesite = velikih (B9 #, vrstico (posredno (»1:3 «»))), ali = velikih (B9:B18,ROW(INDIRECT("1:3")))

    Na tej točki, lahko pomaga nekoliko vedeti o funkciji vrstice in INDIRECT. Če želite ustvariti polje zaporednih celih števil, lahko s funkcijo ROW. Na primer, izberite prazen in vnesite:

    =ROW(1:10)

    Formula ustvari stolpec z 10 zaporednih cela števila. Če si želite ogledati morebitne težave, vstaviti vrstico nad obseg, ki vsebuje formulo polja (torej nad vrstico 1). Excel prilagodi sklice na vrstico in formula ustvari cela števila od 2 do 11. Če želite odpraviti to težavo, dodajte funkcije INDIRECT formuli:

    = VRSTICE (POSREDNO (»1:10 «»))

    Funkcija INDIRECT uporablja besedilne nize kot argumenti (ki je zato obseg 1:10 obkrožajo narekovaji). Excel ne popravi besedilne vrednosti, ko vstavite vrstice ali drugače premikate matrično formulo. Kot rezultat, Funkcija ROW vedno ustvari polja celih števil, ki jih želite. Uporabite lahko prav tako lahko ZAPOREDJU:

    =SEQUENCE(10)

    Formula, ki ste jih uporabili prej si oglejmo – = LARGE (B9 #, vrstico (INDIRECT (»1:3 ««))) – od notranje oklepaji in delo navzven: The INDIRECT funkcija vrne nabor besedilnih vrednosti v tem primeru vrednosti 1 do 3. Funkcija ROW ustvarja tri celico stolpca polja. Funkcija LARGE uporablja vrednosti v obsegu B9:B18 in je ovrednotena trikrat, enkrat za vsako sklic, ki jo vrne funkcija ROW. Če želite poiskati več vrednosti, funkcija INDIRECT dodate večjo obseg celic. Na koncu kot pri majhnih primerih, lahko uporabite to formulo z drugimi funkcijami, kot so SUM in AVERAGE.

  • Vsota obsega, v katerem so vrednosti napak

    Funkcija SUM v Excelu ne deluje, ko boste poskusili vsota obsega, v katerem so vrednosti napake, na primer #VALUE! ali # n/v. V tem primeru je prikazano za seštevanje vrednosti v obsegu z imenom podatkov, ki vsebuje napake:

    Uporabite polja za obravnavanje napak. Na primer, bo =SUM(IF(ISERROR(Data),"",Data) sešteje obseg, imenovan podatkov, tudi če vključuje napake, kot so #VALUE! ali #NA!.

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

    V tem primeru je kot prejšnji formuli, vendar vrne število vrednosti napak v obsegu z imenom podatkov namesto filtriranja:

    =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števanje vrednosti na podlagi pogojev.

Polja lahko uporabite za izračun glede na določene pogoje. =SUM(IF(Sales>0,Sales)), bo vsota vseh vrednosti, večje od 0 v obseg, imenovan prodaje.

Ta matrična formula sešteje le pozitivna cela števila v obsegu z imenom prodaje, ki predstavlja E9:E24 celice v zgornjem primeru:

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

Funkcija IF ustvari polje vrednosti, ki so pozitivna in false. Funkcija SUM v bistvu prezre vrednosti false, ker 0 + 0 = 0. Obseg celic, ki jih uporabljate v tej formuli so lahko sestavljena iz poljubnega števila vrstic in stolpcev.

Lahko tudi seštevanje vrednosti, ki ustrezajo več pogojem. Na primer, ta matrična formula izračuna vrednosti, večje od 0 in manjše od 2500:

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

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

Ustvarite lahko tudi matrične formule, ki uporabljajo vrsto ali pogoj. Na primer, lahko seštejete vrednosti, ki so večje od 0 ali manj kot 2500:

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

Ne morete uporabiti AND in ali funkcij v formulah polj neposredno, ker so te funkcije vrnejo en rezultat, bodisi TRUE ali FALSE in polja funkcije zahtevajo matrike rezultatov. Težavo lahko odpravite tako, da uporabite logiko, ki je prikazano na prejšnji formuli. Z drugimi besedami, izvedete matematičnih operacij, kot so seštevanje ali množenje na vrednosti, ki ustrezajo ali ali in stanju.

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 obsegov celic, imenovan MojiPodatki in Vašipodatki in vrne število razlike med dvema. Če vsebine dveh obsegov so enake, formula vrne 0. Če želite uporabiti ta formula, obsege celic morajo biti enako velikost in iste dimenzije. Na primer, če MojiPodatki obseg 3 vrstic in stolpcev 5, Vašipodatki tudi mora biti 3 vrsticami 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))

V vzorčnem delovnem zvezku na delovnem listu razlike med nize podatkov boste našli podobne primere.

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.

Prodaja Oseba

Avtomobila Vrsta

Število Prodanih

Enote Cena

Skupno Prodaja

Rožič

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 kupejev in limuzin po posameznih prodajalcih, izberite celice E2: E11, vnesite formulo = C2: C11 * D2: D11, in nato 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 Ctrl + Shift + Enter.

Ko pritisnete Ctrl + Shift + Enter, Excel obdaja formula z zavite oklepaje ({}) in vstavi primerek formule v posamezne celice izbranega obsega. To se zgodi zelo hitro tako, da vidite, v stolpcu E skupni znesek prodaje za vsako vrsto avto po posameznih prodajalcih. Če izberete E2, nato pa izberite E3, E4 in tako dalje, boste videli, da so prikazani isti formuli: {= C2: C11 * D2: D11}.

Vsote v stolpcu E izračuna formula polja

  • Ustvarjanje enocelične formule polja

V celico D13 v delovnem zvezku vnesite to formulo in pritisnite Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

V tem primeru Excel pomnoži vrednosti v matriki (obseg celic C2 do D11) in nato uporablja funkcijo SUMza dodajanje vsote skupaj. Rezultat je skupna vsota $1,590,000 prodaje. V tem primeru je prikazano, kako Zmogljiva je lahko ta vrsta formule. Recimo, da imate 1000 vrstice s podatki. Del ali vse te podatke lahko seštejete tako, da ustvarite formulo polja v eni celici namesto vlečenja formulo po vrsticah, 1000.

Prav tako opazili, da enocelično formulo v celici D13 je popolnoma neodvisno od večcelično formulo (formule v celicah E2 – E11). To je druge prednosti formul polja – prilagodljivost. Lahko spremenite formule v stolpcu E ali brisanje tega stolpca v celoti odstranite, 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    Komponenta v večcelično formulo polja ni mogoče prepisati. Na primer, kliknite celico E3 in pritisnite Izbriši. Imate bodisi izberite celoten obseg celic (E2 – E11) in spremenite formulo za celotno polja ali pustite polja, kot je. Kot mera večjo varnost, morate pritisnite Ctrl + Shift + Enter , da potrdite spremembe v formulo.

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

Na splošno matrične formule uporabite standardno sintakso formule. Vsi začnejo z enačajem (=) in uporabite lahko največ vgrajene funkcije Excela v formulah s polji. Ključna razlika je, da če uporabljate formulo polja, pritisnete Ctrl + Shift + Enter , če želite vnesti formulo. Ko to storite, Excel, ki obdaja vašo formulo polja z oklepaji, če jih zavite oklepaje vnesite ročno, formulo, bo pretvorjena v besedilnem nizu, in ne deluje.

Matrika funkcije lahko učinkovit način za ustvarjanje zapletenih formul. Polja formule =SUM(C2:C11*D2:D11) je enako, kot to: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Pomembno: Ko želite vnesti matrično formulo, pritisnite Ctrl + Shift + Enter . To velja za eno celico in 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 formule (na primer E2: E11), nato pa pritisnite tipko Izbriši.

  • Ni mogoče vstavljati praznih celic ali brisati celice iz v večcelično formulo polja.

Včasih boste morali razširiti formulo polja. Izberite prvo celico v obstoječi obseg za polja, in nadaljujte, dokler ne izberete celoten obseg, da želite razširiti formulo. Pritisnite F2 , da uredite formulo in pritisnite CTRL + SHIFT + ENTER , da potrdite formulo, ko ste prilagojena formule obseg. Ključ je, da izberete celoten obseg, začne z zgornjo levo celico v matriki. Zgornjo levo celico je ena, ki urejen.

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

  • Morda občasno pozabite, da pritisnete Ctrl + Shift + Enter. Se lahko zgodi, da tudi najbolj izkušeni uporabniki Excela. Ne pozabite, da pritisnite te tipke, ko vnesete ali uredite formulo polja.

  • Drugi uporabniki delovnega zvezka ne bi razumeli formul. V praksi formule s polji po navadi niso razloženi na delovnem listu. Zato, če drugi morate spremeniti delovne zvezke, jih bodisi izogniti matrične formule ali preverite, ali te osebe vem, katere koli formule s polji in razumeti, kako spremenite, če so potrebni za.

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

Zdaj, veste, da boste morali pritisniti Ctrl + Shift + Enter pri ustvarjanju formul polja. Konstante polja so sestavni del matrične formule, zato ga obkrožajo konstante z oklepaji tako, da ročno vnesete jih. Jih nato uporabite 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.

Tukaj je matrike v eni vrstici: {1,2,3,4}. Tukaj je matrike v enem stolpcu: {1; 2; 3; 4}. In tu je matrika dveh vrstic in stolpcev, štiri: {1,2,3,4; 5,6,7,8}. V dve vrstici polja, v prvi vrstici je 1, 2, 3 in 4 in druge vrstice je 5, 6, 7 in 8. V enem podpičja dveh vrstic, 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 jih morate vnesite odpiranje in zapiranje zavite oklepaje ({}) in bo Excel dodal drugi niz 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 oklepajih je konstanto polja: {1,2,3,4,5}. Ne pozabite, da Excel ne obkrožajo konstant polja z oklepaji; dejansko jih vnesete. Prav tako ne pozabite, da ko dodate konstanto v formulo polja, uporabite kombinacijo Ctrl + Shift + Enter , če želite vnesti 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})

Preberite ta članek, kopirate funkcijo, izberite prazno celico v delovnem zvezku, prilepite formulo v vnosno vrstico, in nato pritisnite Ctrl + Shift + Enter. Vidite isti rezultat kot ste prej telesne dejavnosti, ki uporabljajo matrično formulo:

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

Je ena od najboljši način za uporabo konstant polja njihovo ime. Poimenovane konstante lahko veliko lažje, če želite uporabiti, in lahko skrijete, nekatere zapletenost formul polja od drugih. Imenovanje konstante polja in jo uporabite v formuli, naredite to:

  1. Na zavihku formule v skupini Definirana imena kliknite Določi ime.
    Prikaže se 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:

    Pogovorno okno »Urejanje imena« s formulo

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

  5. Vnesite to formulo in pritisnite 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 ne ločene s pravilno znakov. Če izpustite vejica ali podpičje, če vnesete v napačnem mestu konstanto polja morda ni pravilno ustvarili, ali, boste morda videli opozorilo.

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

Ti primeri prikazujejo nekaj načinov, v katerem lahko postavite konstant polja, če želite uporabiti v matričnih formulah. Nekaj primerov s katerim, funkcija TRANSPOSE pretvori 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 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 nato 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 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 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 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 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

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 Ctrl + Shift + Enter, ki vnese ta matriko števil v obsegu celice C8: E10 tako, da uporabite formulo polja. Na delovnem listu, C8 – E10 bi morala biti videti 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

    3 x 3 matrike celic, ki se prikaže v celicah C1 – E3 enake vrednosti, ki so prikazane v celicah C8 – E10.

Ustvarjanje konstante polja iz obstoječih vrednosti

  1. S celicami C1:C3 izbrana, pritisnite F2, da preklopite v način za urejanje.

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

  3. Pritisnite Ctrl + Shift + Enter , če želite vnesti 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 si ogledate skupno število znakov v celicah a2: A6 (66).

  3. Izberite celici A10, in nato pritisnite Ctrl + Shift + Enter , da si ogledate vsebino največje v celicah a2: A6 (celica A3).

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

=SUM(LEN(A2:A6))

V tem primeru funkcija LEN vrne dolžino vsak besedilni niz v vseh celic v obsegu. Funkcija SUM nato sešteje te vrednosti in prikaže rezultat (66).

Iskanje n najmanjše vrednosti 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 celicah A1:A11.

  2. Izberite celice od C1 do C3. Ta nabor celic, bo vsebovala rezultate, ki jih vrne formula polja.

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

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

Ta formula uporablja matrično konstanto za majhne funkcija ovrednotena trikrat in vrne najmanjšo (1), drugo najmanjše (2) in tretji najmanjše članov (3) v polja, ki so vključene v celicah a1: A10 za iskanje več vrednosti, dodate več argumentov, da je konstanta. Uporabite lahko tudi dodatne funkcije s to formulo, na primer vsota ali POVPREČJE. Na primer:

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

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

Iskanje n največje vrednosti 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 D1 do D3.

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

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

Na tej točki, lahko pomaga nekoliko pozna vrstice in funkcije INDIRECT . Če želite ustvariti polje zaporednih celih števil, lahko s funkcijo ROW . Na primer, izberite prazen stolpec 10 celic v delovnem zvezku vaja, vnesite to formulo polja in nato 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 jih uporabili prej – = LARGE (A5:A14,ROW(INDIRECT("1:3"))) – od notranje oklepaji in delo navzven: INDIRECT , funkcija vrne nabor besedilnih vrednosti v tem primeru vrednosti 1 do 3. Funkcija ROW ustvarja celico tremi stolpci matrike. Funkcija LARGE uporablja vrednosti v obsegu A5:A14 in je ovrednotena trikrat, enkrat za vsako sklic, ki jo vrne funkcija ROW . Vrednosti 3200, 2700 in 2000 se vrnejo v celico tremi stolpci matrike. Če želite poiskati več vrednosti, funkcija INDIRECT dodate večjo obseg celic.

Kot z prejšnjih primerov, lahko uporabite to formulo z drugimi funkcijami, na primer vsote in POVPREČJA.

Iskanje najdaljšega besedilnega niza v obsegu celic

Pojdite nazaj na prejšnje besedilo niz primer, 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 »kup celic, ki« se prikaže.

Poglejmo si pobližje formulo, od notranje elemente in delo navzven. Funkcija LEN vrne dolžino vsak element v obsegu A2: A6. Funkcija MAX izračuna največjo vrednost med te elemente, ki ustreza najdaljšega besedilnega niza, ki je v celici A3.

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(A2:A6))

ki je v tem polju:

LEN(A2:A6)

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.

Na koncu funkcija INDEX ima te argumente: matrike in število vrstic in stolpcev v ta polja. Obseg celic a2: A6 ponuja polja, funkcija MATCH ponuja naslov celice in končne argument (1) določa, da vrednost prihaja 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 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))

Potrditev

Dele v tem članku so bili glede na niz stolpcev Excel Power uporabnik napisal Rok Palčič in prilagojeno iz poglavja 14 in 15 Excel 2002 formul, knjigo napisal John Walkenbach, nekdanji Excel 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 polj in prelitega polja

Dinamične matrične formule v primerjavi s podedovanimi CSE matrične formule

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.

×