Uputstva i primeri formula niza

Uputstva i primeri formula niza

Napomena: Želimo da vam što pre pružimo najnoviji sadržaj pomoći čim na vašem jeziku. Ova stranica je prevedena automatski i može da sadrži gramatičke greške ili netačnosti. Naš cilj je da ovaj sadržaj bude koristan. Možete li nam na dnu ove stranice saopštiti da li su vam informacije bile od koristi? Ovo je članak na engleskom jeziku za brzu referencu.

Formula niza je formula koja može da obavi više izračunavanja na jednoj ili više stavki u nizu. Niz možete da pomislite kao red ili kolonu sa vrednostima ili kombinaciju redova i kolona sa vrednostima. Formule niza mogu da vrate više rezultata ili jedan rezultat.

Počevši od 2018 Office septembra za Office 365, sve formule koje mogu da vrate više rezultata automatski će ih prosuti ili na susedne ćelije. Ova promena ponašanja takođe je praćena nekoliko novih funkcija niza dinamičkih niza. Dinamičke formule niza, bez obzira na to da li koriste postojeće funkcije ili dinamičke funkcije niza, treba da budu samo unos u jednu ćeliju, a zatim je potvrđena pritiskom na taster ENTER. Ranije, zastarele formule niza zahtevaju prvi izbor celokupnog izlaznog opsega, a zatim potvrdu formule pomoću kombinacije tastera CTRL + SHIFT + ENTER. One se obično nazivaju CSE formulama.

Možete da koristite formule niza da biste izvodili složenije zadatke, na primer:

  • Brzo kreiranje uzoraka dataseta.

  • Prebrojavanje znakova sadržanih u opsegu ćelija.

  • Sabiranje samo brojeva koji ispunjavaju određene uslove, kao što su najniže vrednosti u opsegu ili brojevi koji spadaju između gornje i donje ivice.

  • Sabiranje svake Nth vrednosti u opsegu vrednosti.

Sledeći primeri pokazuju kako da kreirate višestruke ćelije i formule niza. Gde je to moguće, uključili smo primere sa neke od dinamičkih niza funkcija, kao i postojeće formule niza unete kao dinamičko i zastarelo.

Preuzmite naše primere

Preuzmite primer radne sveske sa svim primerima formule niza u ovom članku.

Ovo vežbanje vam pokazuje kako da koristite višećelijske i jednoćelijske formule niza da biste izračunali skup podataka o prodaji. Prvi skup koraka koristi višećelijsku formulu za izračunavanje skupa međuvrednosti. Drugi skup koristi jednoćelijsku formulu za izračunavanje ukupnog zbira.

  • Višećelijska formula niza

    Funkcija niza ćelija u ćeliji H10 = F10: F19 * G10: G19 da biste izračunali broj automobila prodatog po jedinici cene

  • Ovde izračunavamo ukupnu prodaju kupova i limuzina za svakog prodavca tako što će uneti = F10: F19 * G10: G19 u ćeliji H10.

    Kada pritisnete taster ENTER, videćete da se rezultati u ćelijama H10: H19. Obratite pažnju na to da je opseg istaknut uz ivicu kada izaberete bilo koju ćeliju unutar opsega prosipanja. Možete da primetite i da formule u ćelijama H10: H19 su zasivljene. One su samo za referenca, pa ako želite da prilagodite formulu, moraćete da izaberete ćeliju H10, gde živi formula master.

  • Formula niza ćelija sa jednom ćelijama

    Formula niza ćelija jedne ćelije za izračunavanje ukupnog zbira sa slovom = SUM (F10: F19 * G10: G19)

    U ćeliji u kojoj se nalazi primer, otkucajte ili kopirajte i nalepite = SUM (F10: F19 * G10: G19), a zatim pritisnite taster ENTER.

    U ovom slučaju, Excel množi vrednosti u nizu (opseg ćelija F10 G19), a zatim koristi funkciju SUM za sabiranje ukupnih vrednosti. Rezultat je ukupni zbir od 1,590,000 USD u prodaji.

    Ovaj primer prikazuje kako ovaj tip formule može biti moćan. Na primer, pretpostavimo da imate 15.000 redova podataka. Možete sabrati deo ili sve te podatke kreiranjem formule niza u jednoj ćeliji umesto da prevlačite formulu naniže kroz 1000 redova. Takođe, obratite pažnju na to da je formula jedne ćelije u ćeliji H2o potpuno nezavisna od formule sa više ćelija (formula u ćelijama H10 kroz H19). Ovo je još jedna prednost korišćenja formula niza – fleksibilnost. Možete da promenite druge formule u koloni H bez uticaja na formulu u H2o. Preporučuje se i da imate nezavisne ukupne vrednosti kao što je ova, pošto pruža valjanost preciznosti rezultata.

  • Dinamičke formule niza takođe nude ove prednosti:

    • Doslednost    Ako kliknete na bilo koju od ćelija sa H10 nadole, videćete istu formulu. Ta doslednost može da pomogne u osiguravanju veće preciznosti.

    • Bezbednost    Ne možete da zamenite komponentu formule niza od više ćelija. Na primer, kliknite na ćeliju H11 i pritisnite taster DELETE. Excel neće promeniti izlaz niza. Da biste je promenili, morate da izaberete gornju levu ćeliju u nizu ili ćelija H10.

    • Manje veličine datoteke    Često možete koristiti jednu formulu niza umesto nekoliko srednjih formula. Na primer, primer prodaje automobila koristi jednu formulu niza za izračunavanje rezultata u koloni E. Ako ste koristili standardne formule kao što su = F10 * G10, F11 * G11, F12 * G12 itd, koristili biste 11 formula za izračunavanje istih rezultata. To nije velika stvar, ali šta ako imate tisuće redova do ukupnog broja? To može da bude velika razlika.

    • Efikasnost    Funkcije niza mogu da budu efikasan način za pravljenje složenih formula. Formula niza = SUM (F10: F19 * G10: G19) je ista kao: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19).

    • Vene    Dinamičke formule niza će se automatski prosuti u izlazni opseg. Ako se izvorni podaci nalaze u Excel tabeli, formula dinamičkih niza će automatski promeniti veličinu prilikom dodavanja ili uklanjanja podataka.

    • #SPILL! greške    Dinamičke grupe su uvedene #SPILL! greška, što ukazuje na to da je namenjen opseg prospe blokiran iz nekog razloga. Kada rešite blokadu, formula će se automatski prosuti.

Konstante niza su komponente formula niza. Konstante niza kreirate unošenjem liste stavki, a zatim listu ručno stavljate u velike zagrade ({ }), na sledeći način:

= {1, 2, 3, 4, 5} ili = {"januar", "februar", "Mart"}

Ako razdvojite stavke korišćenjem zareza, pravite horizontalni niz (red). Ako razdvojite stavke korišćenjem tače i zareza, pravite vertikalni niz (kolonu). Da biste kreirali dvodimenzionalni niz, možete da koristite stavke u svakom nizu, kao i da ih razdvojite tačkom i zarezom.

Sledeće procedure će vam pružiti vežbu za pravljenje horizontalnih, vertikalnih i dvodimenzionalnih konstanti. Pokaznićemo primere pomoću funkcije Array za automatsku generisanje konstanti niza, kao i ručno unete konstante niza.

  • Kreiranje horizontalne konstante

    Koristite radnu svesku iz prethodnih primera ili kreirajte novu radnu svesku. Izaberite praznu ćeliju i unesite = sekvenca (1, 5). Funkcija sekvenca pravi broj 1 reda po 5 kolona, isti kao = {1, 2, 3, 4, 5}. Prikazuje se sledeći ishod:

    Kreiranje horizontalne konstante niza sa slovom = sekvenca (1, 5) ili = {1, 2, 3, 4, 5}

  • Kreiranje vertikalne konstante

    Izaberite praznu ćeliju sa sobom ispod i unesite = sekvenca (5)ili = {1; 2; 3; 4; 5}. Prikazuje se sledeći ishod:

    Kreiranje vertikalne konstante niza uz = Array (5) ili = {1; 2; 3; 4; 5}

  • Kreiranje dvodimenzionalne konstante

    Izaberite praznu ćeliju sa sobom desno i ispod nje i unesite = sekvenca (3, 4). Vidite sledeći rezultat:

    Kreiranje 3 reda pomoću 4 konstante kolone sa = sekvenca (3, 4)

    Takođe možete da unesete: ili = {1, 2, 3, 4; 5, 6, 7, 8 (5, 6, 7, 8; 9, 10, 11, 12}, ali ćete želeti da obratite pažnju na to gde ste stavili jednobojne i zarezima.

    Kao što možete da vidite, opcija sekvence pruža značajne prednosti u odnosu na ručno unošenje konstanti niza. Prvenstveno vam uštedi vreme, ali može da pomogne i smanjenje grešaka od ručno unosa. Takođe je lakše čitati, naročito zato što polukoliće može biti teško razlikovati od znakova za razdvajanje zarezima.

Evo primera koji koristi konstante niza kao deo veće formule. U uzorku radne sveske idite na konstantu u radnom listu formula ili kreirajte novi radni list.

U ćeliji D9 smo uneli = sekvenca (1, 5, 3, 1), ali ste takođe mogli da unesete 3, 4, 5, 6 i 7 u ćelijama a9: do H9. Nema ničega posebnog u vezi sa tim određenim izborom brojeva, upravo smo odabrali nešto drugo osim 1-5 za diferencijanje.

U ćeliji E11 unesite = SUM (D9: do H9 * sekvenca (1, 5))ili = SUM (D9: do H9 * {1, 2, 3, 4, 5}). Formule vraćaju 85.

Koristite konstante niza u formulama. U ovom primeru smo koristili = SUM (D9: H (* sekvenca (1, 5))

Funkcija sekvenca gradi ekvivalentu niza konstanti {1, 2, 3, 4, 5}. Budući da Excel obavlja operacije na izrazima u zagradama, sledeće dve elemente koji se prikazuju su vrednosti ćelija u D9: do H9 i operator množenja (*). Na ovoj tački, formula množi vrednosti u uskladištenom nizu odgovarajućim vrednostima u konstanti. To je jednako:

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

Na kraju, funkcija SUM dodaje vrednosti i daje 85.

Da biste izbegli korišćenje uskladištenog niza i držali operaciju u potpunosti u memoriji, možete da je zamenite drugim konstantom:

= SUM (sekvenca (1, 5, 3, 1) * sekvenca (1, 5))ili = SUM ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

Elementi koje možete da koristite u konstantama niza

  • Konstante niza mogu da sadrže brojeve, tekst, logičke vrednosti (kao što su TRUE i FALSE) i vrednosti grešaka kao što je #N/A. Brojeve možete da koristite u svim brojevima, decimala i naučni formatima. Ako uključujete tekst, morate da ga okružite znacima navoda ("tekst").

  • Konstante niza ne mogu sadržati dodatne nizove, formule ili funkcije. Drugim rečima, mogu sadržati samo tekst ili brojeve koji su razdvojeni zarezom ili tačkom i zarezom. Excel prikazuje poruku upozorenja kada unesete formulu kao što je {1,2,A1:D4} ili {1,2,SUM(Q2:Z8)}. Takođe, numeričke vrednosti ne mogu sadržati znakove procenta, dolara, zareze ili zagrade.

Jedan od najboljih načina za korišćenje konstanti niza jeste da ih imenujete. Imenovane konstante mogu biti mnogo lakše za korišćenje i mogu sakriti neke složenosti formula niza od drugih osoba. Da biste imenovali konstantu niza i koristili je u formuli, uradite sledeće:

Idite na stavke formule _ gtve koje su definisana imena za _gt_ Definiљi ime. U polju ime otkucajte Kvartal1. U polje Odnosi se na unesite sledeću konstantu (ne zaboravite da ručno otkucate velike zagrade):

={"Januar","Februar","Mart"}

Dijalog bi trebalo da izgleda ovako:

Dodavanje imenovane konstante niza iz formula _Gt[definisanih imena za Gtve ime Manager za > novo

Kliknite na dugme u redu, a zatim izaberite bilo koji redu sa tri prazne ćelije i unesite = Kvartal1.

Prikazuje se sledeći ishod:

Koristite imenovani spektar niza u formuli, kao što je = Kvartal1, gde je Kvartal1 definisan kao = {"januar", "februar", "Mart"}

Ako želite da se rezultati prospu vertikalno umesto horizontalno, možete da koristite =trans(Kvartal1).

Ako želite da prikazujete listu od 12 meseci, kao što biste mogli da koristite prilikom pravljenja finansijskog izvoda, možete da ga zasnijete na trenutnoj godini pomoću funkcije sekvenca. Dobra stvar u vezi sa ovom funkcijom je da, iako se samo mesec prikazuje, postoji važeći datum koji možete da koristite u drugim izračunavanjima. Ove primere ćete pronaći na imenovanu konstanti po imenu niz i brzi izbor uzorka podataka u radnoj svesci.

= TEXT (datum ((danas), sekvenca (1, 12), 1), "Mmm")

Korišćenje kombinacija teksta, DATUMA, godine, danas i sekvence za pravljenje dinamičke liste od 12 mjeseci

Ovo koristi funkciju date da biste kreirali datum zasnovan na trenutnoj godini, sekvenca kreira konstantu niza od 1 do 12 za januar do decembra, a zatim Funkcija TEXT konvertuje format prikaza u "Mmm" (Jan, Feb, Mar itd.). Ako ste želeli da prikazujete pun naziv meseca, na primer januar, koristili biste "Mmmm".

Kada koristite imenovani konstantu kao formulu niza, ne zaboravite da unesete znak jednakosti, kao u = Kvartal1, ne samo Kvartal1. Ako to ne uradite, Excel tretira niz kao tekstualnu nisku i formula ne radi na očekivani način. Na kraju, imajte na umu da možete da koristite kombinacije funkcija, teksta i brojeva. Sve zavisi od toga koliko kreativan želite da dobijete.

Sledeći primeri pokazuju nekoliko načina na koje možete staviti konstante niza za upotrebu u formulama niza. Neki od primera koriste funkciju trans za konvertovanje redova u kolone i obratno.

  • Više svake stavke u nizu

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

    Možete i da delite (/), dodajte sa (+) i da biste ga oduzeli (-).

  • Izračunavanje kvadratnog korena za stavke u nizu

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

  • Pronalaženje kvadratnog korena kvadrata stavki u nizu

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

  • Prebacivanje jednodimenzionalnog reda

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

    Iako ste uneli horizontalnu konstantu niza, funkcija TRANSPOSE konvertuje konstantu niza u kolonu.

  • Prebacivanje jednodimenzionalne kolone

    Unesite = TRANSPOSE (niz (5, 1))ili = TRANSPOSE ({1; 2; 3; 4; 5})

    Iako ste uneli vertikalnu konstantu niza, funkcija TRANSPOSE konvertuje konstantu u red.

  • Prebacivanje dvodimenzionalne konstante

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

    Funkcija TRANSPOSE konvertuje svaki red u niz kolona.

Ovaj odeljak obezbeđuje primere osnovnih formula niza.

  • Kreiranje niza iz postojećih vrednosti

    Sledeći primer objašnjava kako se koriste formule niza za kreiranje novog niza iz postojećeg niza.

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

    Obavezno otkucajte {(otvaranje proteze) pre nego što otkucate 10, a} (zatvorena proteza) nakon kucanja 180, jer pravite niz brojeva.

    Zatim unesite = D9 #, ili = D9: I11 u praznu ćeliju. Broj ćelija sa 3 x 6 se pojavljuje sa istim vrednostima koje vidite u D9: D11. Znak # ime se zove operator za prosutu opsegi Excelov način upućivanja na ceo opseg niza umesto da ga upišu.

    Korišćenje operatora za prosutu opseg (#) za referenca postojećeg niza

  • Kreiranje konstante niza iz postojećih vrednosti

    Rezultate prosule formule niza možete da uzmete i konvertujete u delove komponente. Izaberite ćeliju D9, a zatim pritisnite taster F2 da biste se prebacili na režim uređivanja. Zatim pritisnite taster F9 da biste konvertovali reference ćelija u vrednosti, koje Excel zatim konvertuje u konstantu niza. Kada pritisnete ENTER, formula = D9 #, sada bi trebalo da bude = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  • Brojanje znakova u opsegu ćelija

    Sledeći primer vam pokazuje kako se broji broj znakova u opsegu ćelija. To uključuje razmake.

    Prebrojavanje ukupnog broja znakova u opsegu i drugih niza za rad sa tekstualnim niski

    = SUM (LEN (C9: C13))

    U ovom slučaju, funkcija Len daje dužinu svake tekstualne niske u svakoj od ćelija u opsegu. Funkcija SUM zatim sabira te vrednosti i prikazuje ishod (66). Ako želite da dobijete prosečan broj znakova, možete da koristite:

    = AVERAGE (LEN (C9: C13))

  • Sadržaj duže ćelije u opsegu C9: C13

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

    Ova formula funkcioniše samo kada opseg podataka sadrži jednu kolonu ćelija.

    Hajde da pažljivije pogledamo formulu, počevši od unutrašnjih elemenata i radeći od spolja. Funkcija Len daje dužinu svake od stavki u opsegu ćelija D2: D6. Maksimalna funkcija izračunava najveću vrednost između tih stavki, koje odgovaraju najduži tekstualnoj niski, koja se nalazi u ćeliji D3.

    Ovo je mesto na kojem stvari postaju malo složenije. Funkcija MATCH izračunava pomak (relativnu poziciju) ćelije koja sadrži najdužu tekstualnu nisku. Da biste to uradili, to zahteva tri argumenta: vrednost za pretraživanje, niz za pretraživanje i tip podudaranja. Funkcija MATCH pretražuje niz za pretraživanje za navedenu vrednost za pretraživanje. U ovom slučaju, vrednost za pretraživanje je najduža tekstualna niska:

    MAX (LEN (C9: C13)

    i niska se nalazi u ovom nizu:

    LEN (C9: C13)

    Argument Type tip podudaranja u ovom slučaju je 0. Tip podudaranja može biti vrednost 1, 0 ili 1.

    • 1 – daje najveću vrednost koja je manja ili jednaka za argument LOOKUP.

    • 0 – daje prvu vrednost koja je jednaka vrednosti za pronalaženje

    • -1 – daje najmanju vrednost koja je veća od ili jednaka navedenoj vrednosti za pronalaženje

    • Ako izostavite tip podudaranja, Excel pretpostavlja da je to 1.

    Konačno, funkcija INDEX uzima ove argumente: niz i broj reda i kolone u tom nizu. Opseg ćelija C9: C13 pruža niz, funkcija MATCH pruža adresu ćelije, a konačni argument (1) navodi da vrednost potiče iz prve kolone u nizu.

    Ako želite da dobijete sadržaj najmanje tekstualne niske, zamijenićete Maxa u gorenavedenom primeru sa uslugom min.

  • Pronalaženje n najmanjih vrednosti u opsegu

    Ovaj primer pokazuje kako da pronađete tri najmanje vrednosti u opsegu ćelija, gde se niz uzoraka podataka u ćelijama B9: B18has kreiran sa: = and (Randarray(10, 1) * 100). Imajte na umu da je RANDARRAY funkcija nepostojane, tako da ćete dobiti novi skupa nasumičnih brojeva svaki put kada se Excel izračunava.

    Excel formula niza za pronalaženje Nth najmanje vrednosti: = SMALL (B9 #, sekvenca (D9))

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

    Ova formula koristi niz konstanti da proceni malu funkciju tri puta i vrati najmanje 3 člana u niz koji se nalazi u ćelijama B9: B18, gde je 3 promenljiva vrednost u ćeliji D9. Da biste pronašli više vrednosti, možete da povećate vrednost u funkciji sekvenca ili da dodate još argumenata konstanti. Takođe možete koristiti dodatne funkcije sa ovom formulom, kao što su SUM ili AVERAGE. Na primer:

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

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

  • Pronalaženje n najvećih vrednosti u opsegu

    Da biste pronašli najveće vrednosti u opsegu, možete da zamenite malu funkciju sa velikom funkcijom. Pored toga, sledeći primeri koriste funkcije ROW i INDIRECT.

    ENTER = velika (B9 #, redove (indirektni ("1:3")))ili = veliki (B9: B18, rokom (indirektan ("1:3")))))

    Na ovom mestu, biće vam korisno da znate nešto o funkcijama ROW i INDIRECT. Funkciju ROW možete koristiti da biste kreirali niz od uzastopnih celih brojeva. Na primer, izaberite stavku prazno i unesite:

    =ROW(1:10)

    Formula kreira kolonu od 10 uzastopnih celih brojeva. Da biste videli potencijalni problem, umetnite red iznad opsega koji sadrži formulu niza (to jest, iznad reda 1). Excel podešava reference na redove, a formula sada generiše brojeve sa 2 na 11. Da biste rešili taj problem, u formulu dodajete funkciju INDIRECT:

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

    Funkcija INDIREKTNIH koristi tekstualne niske kao svoje argumente (zbog čega je opseg 1:10 okružen navodnicima). Excel ne podešava tekstualne vrednosti kada umetnete redove ili na neki drugi način premestite formulu niza. Kao rezultat, funkcija ROW uvek generiše niz celih brojeva koje želite. Možete lako da koristite SEKVENCU:

    = SEKVENCA (10)

    Hajde da ispitamo formulu koju ste ranije koristili – = LARGE (B9 #, redu (INDIREKTAN ("1:3"))) – počevši od unutrašnje zagrade i rade spolja: INDIREKTNA funkcija vraća skupu tekstualnih vrednosti, u ovom slučaju vrednosti od 1 do 3. Funkcija red se uključuje i generiše niz od tri ćelije. Velika funkcija koristi vrednosti u opsegu ćelija B9: B18, a procenjuje se tri puta, jednom za svaku karticu koju vraća funkcija Rou. Ako želite da pronađete još vrednosti, dodajte veći opseg ćelija u indirektnu funkciju. Na kraju, kao i sa malim primerima, možete da koristite ovu formulu sa drugim funkcijama, kao što su SUM i AVERAGE.

  • Zbir opsega koji sadrži vrednosti greške

    Funkcija SUM u programu Excel ne funkcioniše kada pokušate da saberete opseg koji sadrži grešku, na primer #VALUE! ili #N/A. Ovaj primer vam pokazuje kako da saberete vrednosti u opsegu pod imenom podaci koji sadrže greške:

    Koristite nizove da se bave greškama. Na primer, = SUM (IF (ISERROR (podaci), "", podaci) sabraće opseg imenovani za podatke čak i ako uključuje greške, kao što je #VALUE! ili #NA!.

  • =SUM(IF(ISERROR(Data),"",Podaci))

    Formula kreira novi niz koji sadrži originalne vrednosti manje vrednosti greške. Počevši od unutrašnjih funkcija i radeći od spolja, funkcija ISERROR pretražuje opseg ćelija (Podaci) radi grešaka. Funkcija IF vraća određenu vrednost ako uslov koji navedete daje rezultat TRUE, i drugu vrednost ako daje rezultat FALSE. U ovom slučaju, vraća prazne niske ("") za sve vrednosti greške zato što daju rezultat TRUE i vraća preostale vrednosti iz opsega (Podaci) zato što je rezultat FALSE, što znači da ne sadrže vrednosti greške. Funkcija SUM zatim izračunava ukupan zbir za filtrirani niz.

  • Brojanje vrednosti greške u opsegu

    Ovaj primer je kao prethodna formula, ali vraća broj vrednosti greške u opsegu po imenu podaci umesto da ih filtrira:

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

    Ova formula kreira niz koji sadrži vrednost 1 za ćelije koje sadrže greške i vrednost 0 za ćelije koje ne sadrže greške. Formule možete pojednostaviti i postići isti rezultat uklanjanjem trećeg argumenta za funkciju IFna sledeći način:

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

    Ako ne navedete argument, funkcija IF vraća vrednost FALSE ako ćelija ne sadrži vrednost greške. Formulu možete još više pojednostaviti:

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

    Ova verzija funkcioniše zato što je TRUE*1=1 i FALSE*1=0.

Vrednosti ćete možda morati da saberete na osnovu uslova.

Možete da koristite nizove da biste izračunali na osnovu određenih uslova. = SUM (IF (Sales>0, prodaja)) sabraće sve vrednosti veće od 0 u opsegu pod nazivom prodaja.

Na primer, ova formula niza sabira samo pozitivne brojeve u opsegu pod imenom "Prodaja", koje predstavlja ćelije E9: E24 u gorenavedenom primeru:

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

Funkcija IF kreira niz pozitivnih i netačnih vrednosti. Funkcija SUM u suštini zanemaruje netačne vrednosti zato što je 0+0=0. Opseg ćelija koji koristite u ovoj formuli može da se sastoji od bilo kojeg broja redova i kolona.

Takođe možete sabrati vrednosti koje ispunjavaju više uslova. Na primer, ova formula niza izračunava vrednosti veće od 0 i manje od 2500:

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

Imajte na umu da ova formula vraća grešku ako opseg sadrži neke ćelije koje nisu numeričke.

Takođe možete kreirati formule niza koje koriste tip OR uslova. Na primer, možete da saberete vrednosti koje su veće od 0 ili manje od 2500:

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

Funkcije AND i OR nije moguće koristiti direktno u formulama niza zato što ove funkcije vraćaju jedan rezultat, ili TRUE ili FALSE, a funkcije niza zahtevaju nizove rezultata. Ovaj problem možete izbeći korišćenjem logike prokazane u prethodnoj formuli. Drugim rečima, obavljate operacije iz matematike, kao što je sabiranje ili umnožavanje vrednosti koje ispunjavaju uslove.

Ovaj primer prikazuje kako da uklonite nule iz opsega kada morate da izračunate prosečne vrednosti u tom opsegu. Formula koristi podatke koji se zovu „Prodaja“:

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

Funkcija IF kreira niz vrednosti koji nije jednak 0, a zatim prosleđuje te vrednosti u funkciju AVERAGE.

Ova formula niza upoređuje vrednosti u dva opsega ćelija koji se zovu „MyData“ i „YourData“ i vraća broj razlika između ta dva. Ako je sadržaj ova dva opsega identičan, formula vraća vrednost 0. Da biste koristili ovu formulu, opsezi ćelija treba da budu iste veličine i iste dimenzije. Na primer, ako su moji podaci opseg od 3 reda po 5 kolona, podaci moraju da budu 3 reda po 5 kolona:

=SUM(IF(MyData=YourData,0,1))

Formula kreira novi niz iste veličine kao opsezi koje upoređujete. Funkcija IF popunjava niz vrednošću 0 i vrednošću 1 (0 za nepodudaranja i 1 za identične ćelije). Funkcija SUM zatim vraća zbir vrednosti u nizu.

Formulu možete ovako pojednostaviti:

= SUM (1 * (MyData<>YourData))

Poput formule koja broji vrednosti greške u opsegu, ova formula funkcioniše zato što je TRUE*1=1, a FALSE*1=0.

Ova formula niza vraća broj reda maksimalne vrednosti u opsegu sa jednom kolonom koji se zove „Podaci“:

=MIN(IF(Data=MAX(Podaci),ROW(Podaci),""))

Funkcija IF kreira novi niz koji odgovara opsegu po imenu „Podaci“. Ako odgovarajuća ćelija sadrži maksimalnu vrednost u opsegu, niz sadrži broj reda. U suprotnom, niz sadrži praznu nisku (""). Funkcija MIN koristi novi niz kao drugi argument i vraća najmanju vrednost, koja odgovara broju reda maksimalne vrednosti u opsegu „Podaci“. Ako opseg po imenu „Podaci“ sadrži identične maksimalne vrednosti, formula vraća red prve vrednosti.

Ako želite da vratite stvarnu adresu ćelije maksimalne vrednosti, koristite ovu formulu:

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

Slični primeri pronaći ćete u uzorku radne sveske o razlikama između radnog lista "skup podataka".

Ovo vežbanje vam pokazuje kako da koristite višećelijske i jednoćelijske formule niza da biste izračunali skup podataka o prodaji. Prvi skup koraka koristi višećelijsku formulu za izračunavanje skupa međuvrednosti. Drugi skup koristi jednoćelijsku formulu za izračunavanje ukupnog zbira.

  • Višećelijska formula niza

Kopirajte celu tabelu ispod i nalepite je u ćeliju a1 u praznom radnom listu.

Prodaja Osoba

Automobilska Otkucajte

Broj Prodato

Jedinična Prajs

Ukupan broj Prodaja

Blagojević

Limuzina

5

33000

Kupe

4

37000

Ivić

Limuzina

6

24000

Kupe

8

21000

Jordanović

Limuzina

3

29000

Kupe

1

31000

Tica

Limuzina

9

24000

Kupe

5

37000

Sandić

Limuzina

6

33000

Kupe

8

31000

Formula (Sveukupno)

Sveukupno

'=SUM(C2:C11*D2:D11)

=SUM(C2:C11*D2:D11)

  1. Da biste videli ukupnu prodaju kupova i limuzina za svakog prodavca, izaberite ćelije E2: E11, unesite formulu = C2: C11 * D2: D11, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER.

  2. Da biste videli ukupan zbir svih prodaja, izaberite ćeliju F11, unesite formulu = SUM (C2: C11 * D2: D11), a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER.

Kada pritisnete kombinaciju tastera CTRL + SHIFT + ENTER, Excel okružuje formulu pomoću proteza ({}) i umeće instancu formule u svakoj ćeliji izabranog opsega. Ovo se dešava veoma brzo, tako da je ono što vidite u koloni E ukupni iznos prodaje za svaki tip automobila za svakog prodavca. Ako izaberete E2, zatim E3, E4 i tako dalje, videćete da se prikazuje ista formula: {=C2:C11*D2:D11}

Ukupne vrednosti u koloni E izračunava formula niza

  • Kreiranje jednoćelijske formule niza

U ćeliji D13 u radnoj svesci otkucajte sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

=SUM(C2:C11*D2:D11)

U ovom slučaju, Excel množi vrednosti u nizu (opseg ćelija C2 kroz D11), a zatim koristi funkciju SUMza sabiranje ukupnih vrednosti. Rezultat je ukupni zbir od 1,590,000 USD u prodaji. Ovaj primer prikazuje kako ovaj tip formule može biti moćan. Na primer, pretpostavimo da imate 15.000 redova podataka. Možete sabrati deo ili sve te podatke kreiranjem formule niza u jednoj ćeliji umesto da prevlačite formulu naniže kroz 1000 redova.

Takođe, obratite pažnju na to da je formula jednoćelija u ćeliji D13 potpuno nezavisna od formule sa više ćelija (formula u ćelijama E2 putem E11). Ovo je još jedna prednost korišćenja formula niza – fleksibilnost. Možete da promenite formule u koloni E ili da izbrišete tu kolonu bez uticaja na formulu u D13.

Formule niza takođe pružaju ove prednosti:

  • Doslednost    Ako kliknete na bilo koju ćeliju iz ćelije E2, vidite istu formulu. Ta doslednost može da pomogne u osiguravanju veće preciznosti.

  • Bezbednost    Nije moguće zameniti komponentu višećelijske formule niza. Na primer, kliknite na ćeliju E3 i pritisnite taster Delete. Morate izabrati ceo opseg ćelija (od E2 do E11) i promeniti formulu za ceo niz ili je ostaviti kakvu jeste. Kao dodatnu bezbednu meru, morate da pritisnete kombinaciju tastera CTRL + SHIFT + ENTER da biste potvrdili promenu formule.

  • Manje veličine datoteke    Često možete koristiti jednu formulu niza umesto nekoliko srednjih formula. Na primer, radna sveska koristi jednu formulu niza za izračunavanje rezultata u koloni E. Ako ste koristili standardne formule (na primer =C2*D2, C3*D3, C4*D4…), koristili biste 11 različitih formula za izračunavanje istih rezultata.

Uopšte uzevši, formule niza koriste standardnu sintaksu formule. One sve počinju znakom jednakosti (=) i možete koristiti većinu ugrađenih Excel funkcija u formulama niza. Ključna razlika je u tome što prilikom korišćenja formule niza, pritisnite kombinaciju tastera CTRL + SHIFT + ENTER da biste uneli formulu. Kada ovo uradite, Excel stavlja vašu formulu niza u velike zagrade – ako velike zagrade otkucate ručno, formula će biti konvertovana u tekstualnu nisku i neće raditi.

Funkcije niza mogu da budu efikasan način za pravljenje složenih formula. Formula niza =SUM(C2:C11*D2:D11) ista je kao sledeća: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Važno: Pritisnite kombinaciju tastera CTRL + SHIFT + ENTER kad god treba da unesete formulu niza. Ovo se odnosi i na jednoćelijske i na višećelijske formule.

Pri radu sa višećelijskim formulama imajte u vidu i sledeće:

  • Izaberite opseg ćelija koji će sadržati rezultate pre nego što unesete formulu. Ovo ste uradili kada ste kreirali višećelijsku formulu niza kada ste izabrali ćelije E2 do E11.

  • Nije moguće promeniti sadržaj pojedinačne ćelije u formuli niza. Da biste ovo pokušali, izaberite ćeliju E3 u radnoj svesci i pritisnite taster Delete. Excel prikazuje poruku koja vam saopštava da ne možete da promenite deo niza.

  • Formulu niza možete da premestite ili izbrišete u celini, ali ne možete premestiti ili izbrisati njen deo. Drugim rečima, da biste skupili formulu niza, prvo izbrišite postojeću formulu, a zatim počnite ponovo.

  • Da biste izbrisali formulu niza, izaberite ceo opseg formula (na primer, E2: E11), a zatim pritisnite taster Delete.

  • Ne možete da umetnete prazne ćelije u ili da izbrišete ćelije iz formule niza ćelija sa više ćelija.

Ponekad će vam zatrebati da proširite formulu niza. Izaberite prvu ćeliju u postojećem opsegu niza i nastavite dok ne izaberete ceo opseg na koji želite da proširite formulu. Pritisnite taster F2 da biste uredili formulu, a zatim pritisnite kombinaciju TASTERA CTRL + SHIFT + ENTER da biste potvrdili formulu kada prilagodite opseg formula. Ključ je da izaberete ceo opseg počevši od gornje leve ćelije u nizu. Gornja leva ćelija je ona koja se uređuje.

Formule niza su sjajne, ali imaju neke nedostatke:

  • Možda ćete povremeno zaboraviti da pritisnete kombinaciju tastera CTRL + SHIFT + ENTER. To može da se dogodi i najiskusnijim korisnicima programa Excel. Zapamtite da pritisnete ovu kombinaciju tastera uvek kada unosite ili uređujete formulu niza.

  • Drugi korisnici radne sveske možda neće razumeti vaše formule. U praksi, formule niza obično nisu objašnjene u radnom listu. Stoga, ako treba da izmenite druge osobe, trebalo bi da izbegnete formule niza ili da se uverite da te osobe znaju za bilo koje formule niza i razumeju kako da ih promene ako je to potrebno.

  • U zavisnosti od brzine i memorije obrade računara, velike formule niza mogu usporiti izračunavanja.

Konstante niza su komponente formula niza. Konstante niza kreirate unošenjem liste stavki, a zatim listu ručno stavljate u velike zagrade ({ }), na sledeći način:

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

Do sada znate da treba da pritisnete kombinaciju tastera CTRL + SHIFT + ENTER kada kreirate formule niza. Pošto su konstante niza komponente formula niza, ove konstante ručno stavljate u velike zagrade tako što ih otkucavate. Zatim koristite kombinaciju tastera CTRL + SHIFT + ENTER da biste uneli celu formulu.

Ako razdvojite stavke korišćenjem zareza, pravite horizontalni niz (red). Ako razdvojite stavke korišćenjem tače i zareza, pravite vertikalni niz (kolonu). Da biste napravili dvodimenzionalni niz, razgraničite stavke u svakom redu pomoću zareza, a svaki red razgraničite korišćenjem tačke i zareza.

Evo niza u jednom nizu: {1, 2, 3, 4}. Evo niza u jednoj koloni: {1;2;3;4}. A evo niza od dva reda i četiri kolone: {1,2,3,4;5,6,7,8}. U okviru dva polja, prvi broj je 1, 2, 3 i 4, a drugi je 5, 6, 7 i 8. Jedan znak tačke i zareza razdvaja ta dva reda između vrednosti 4 i 5.

Kao i formule niza, konstante niza možete koristiti sa većinom ugrađenih funkcija koje Excel pruža. Sledeći članci objašnjavaju kako da kreirate svaku vrstu konstante i kako da koristite ove konstante sa funkcijama u programu Excel.

Sledeće procedure će vam pružiti vežbu za pravljenje horizontalnih, vertikalnih i dvodimenzionalnih konstanti.

Kreiranje horizontalne konstante

  1. U praznom radnom listu izaberite ćelije od a1 do E1.

  2. U polje za formulu unesite sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

    U ovom slučaju, trebalo bi da otkucate otvaranje i zatvaranje proteza ({}), a Excel će dodati drugi za vas.

    Prikazuje se sledeći rezultat.

    Horizontalna konstanta niza u formuli

Kreiranje vertikalne konstante

  1. U radnoj svesci izaberite kolonu sa pet ćelija.

  2. U polje za formulu unesite sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

    Prikazuje se sledeći rezultat.

    Vertikalna konstanta niza u formuli niza

Kreiranje dvodimenzionalne konstante

  1. U radnoj svesci izaberite blok ćelija širok četiri kolone i visok tri reda.

  2. U polje za formulu unesite sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

    Vidite sledeći rezultat:

    Dvodimenzionalna konstanta niza u formuli niza

Korišćenje konstanti u formulama

Evo jednostavnog primera koji koristi konstante:

  1. U uzorku radne sveske napravite novi radni list.

  2. U ćeliji A1 otkucajte 3, a zatim otkucajte 4 u ćeliji B1, 5 u ćeliji C1, 6 u ćeliji D1 i 7 u ćeliji E1.

  3. U ćeliji a3 otkucajte sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

    Primetićete da Excel stavlja konstantu u drugi skup velikih zagrada, zato što ste je uneli kao formulu niza.

    Formula niza sa konstantom niza

    U ćeliji A3 pojavljuje se vrednost 85.

Sledeći odeljak objašnjava kako formula funkcioniše.

Formula koju ste upravo koristili sadrži nekoliko delova.

Sintaksa formule niza sa konstantom niza

1. Funkcija

2. Uskladišteni niz

3. Operator

4. Konstanta niza

Poslednji element unutar zagrada je konstanta niza: {1,2,3,4,5}. Zapamtite da Excel ne stavlja konstante niza u velike zagrade; vi ih kucate. Takođe, imajte u vidu da kada dodate konstantu formuli niza, pritisnite kombinaciju tastera CTRL + SHIFT + ENTER da biste uneli formulu.

S obzirom da Excel prvo izvršava operacije na izrazima stavljenim u zagrade, sledeća dva elementa koja stupaju na snagu su vrednosti uskladištene u radnoj svesci (A1:E1) i operator. Na ovoj tački, formula množi vrednosti u uskladištenom nizu odgovarajućim vrednostima u konstanti. To je jednako:

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

Na kraju, funkcija SUM dodaje vrednosti i zbir od 85 se pojavljuje u ćeliji A3:

Da biste izbegli korišćenje uskladištenog niza i zadržali operaciju u potpunosti u memoriji, zamenite uskladišteni niz sa drugom konstantom niza:

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

Da biste ovo pokušali, kopirajte funkciju, izaberite praznu ćeliju u radnoj svesci, nalepite formulu u polje za formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER. Videćete iste rezultate koje ste videli u ranijoj vežbi koja je koristila formulu niza:

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

Konstante niza mogu sadržati brojeve, tekst, logičke vrednosti (na primer TRUE i FALSE) i vrednosti greške (na primer #N/A). Brojeve možete koristiti u formatu celog broja, decimalnog broja i naučnom formatu. Ako uključite tekst, morate ga staviti pod navodnike (").

Konstante niza ne mogu sadržati dodatne nizove, formule ili funkcije. Drugim rečima, mogu sadržati samo tekst ili brojeve koji su razdvojeni zarezom ili tačkom i zarezom. Excel prikazuje poruku upozorenja kada unesete formulu kao što je {1,2,A1:D4} ili {1,2,SUM(Q2:Z8)}. Takođe, numeričke vrednosti ne mogu sadržati znakove procenta, dolara, zareze ili zagrade.

Jedan od najboljih načina za korišćenje konstanti niza jeste da ih imenujete. Imenovane konstante mogu biti mnogo lakše za korišćenje i mogu sakriti neke složenosti formula niza od drugih osoba. Da biste imenovali konstantu niza i koristili je u formuli, uradite sledeće:

  1. Na kartici Formule, u grupi Definisana imena kliknite na dugme Definiši ime.
    Pojaviće se dijalog Definisanje imena .

  2. U polju Ime otkucajte Kvartal1.

  3. U polje Odnosi se na unesite sledeću konstantu (ne zaboravite da ručno otkucate velike zagrade):

    ={"Januar","Februar","Mart"}

    Sadržaj dijaloga sada izgleda ovako:

    Dijalog „Uređivanje imena“ sa formulom

  4. Kliknite na dugme U redu, a zatim izaberite red od tri prazne ćelije.

  5. Otkucajte sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER.

    =Kvartal1

    Prikazuje se sledeći rezultat.

    Imenovani niz unet kao formula

Kada koristite imenovanu konstantu kao formulu niza, ne zaboravite da unesete znak jednakosti. Ako to ne uradite, Excel tretira niz kao tekstualnu nisku i formula ne radi na očekivani način. Na kraju, imajte na umu da možete koristiti kombinacije teksta i brojeva.

Potražite sledeće probleme kada konstante niza ne funkcionišu:

  • Neki elementi možda nisu razdvojeni odgovarajućim znakom. Ako izostavite zarez ili tačku i zarez ili ako stavite jednu na pogrešno mesto, konstanta niza možda nije ispravno kreirana ili ćete možda videti poruku upozorenja.

  • Možda ste izabrali opseg ćelija koji ne odgovara broju elemenata u konstanti. Na primer, ako izaberete kolonu od šest ćelija za korišćenje sa konstantom od pet ćelija, u praznoj ćeliji se pojavljuje vrednost greške #N/A. S druge strane, ako izaberete premalo ćelija, Excel izostavlja vrednosti koje nemaju odgovarajuću ćeliju.

Sledeći primeri pokazuju nekoliko načina na koje možete staviti konstante niza za upotrebu u formulama niza. Neki od primera koriste funkciju trans za konvertovanje redova u kolone i obratno.

Množenje svake vrednosti u nizu

  1. Napravite novi radni list, a zatim izaberite blok praznih ćelija širok četiri kolone i visok tri reda.

  2. Otkucajte sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

Izračunavanje kvadratnog korena za stavke u nizu

  1. Izaberite blok praznih ćelija širok četiri kolone i visok tri reda.

  2. Otkucajte sledeću formulu niza, a zatim pritisnite kombinaciju tastera 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}

    Kao drugu mogućnost unesite ovu formulu niza koja koristi operator karet (^):

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

Prebacivanje jednodimenzionalnog reda

  1. Izaberite kolonu od pet praznih ćelija.

  2. Otkucajte sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

    Iako ste uneli horizontalnu konstantu niza, funkcija TRANSPOSE konvertuje konstantu niza u kolonu.

Prebacivanje jednodimenzionalne kolone

  1. Izaberite red od pet praznih ćelija.

  2. Unesite sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

Iako ste uneli vertikalnu konstantu niza, funkcija TRANSPOSE konvertuje konstantu u red.

Prebacivanje dvodimenzionalne konstante

  1. Izaberite blok ćelija širok tri kolone i visok četiri reda.

  2. Unesite sledeću konstantu i pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

    Funkcija TRANSPOSE konvertuje svaki red u niz kolona.

Ovaj odeljak obezbeđuje primere osnovnih formula niza.

Kreiranje nizova i konstanti nizova iz postojećih vrednosti

Sledeći primer objašnjava kako da koristite formule niza radi kreiranja veza između opsega ćelija u različitim radnim listovima. Takođe vam pokazuje kako da kreirate konstantu niza iz istog skupa vrednosti.

Kreiranje niza iz postojećih vrednosti

  1. Na radnom listu u programu Excel izaberite ćelije C8:E10 i unesite ovu formulu:

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

    Obavezno otkucajte { (otvorenu veliku zagradu) pre nego što otkucate 10 i } (zatvorenu veliku zagradu) pošto otkucate 90, jer pravite niz brojeva.

  2. Pritisnite kombinaciju tastera CTRL + SHIFT + ENTER, koja unosi ovaj niz brojeva u opsegu ćelija C8: E10 pomoću formule niza. Od C8 do E10 na radnom listu, trebalo bi da izgleda ovako:

    10

    20-30

    deseto

    40

    50

    60

    70

    80

    90

  3. Izaberite opseg ćelija od C1 do E3.

  4. U polje za formulu unesite sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

    =C8:E10

    Niz ćelija od tri x3 se pojavljuje u ćelijama C1 kroz E3 sa istim vrednostima koje vidite u C8 putem E10.

Kreiranje konstante niza iz postojećih vrednosti

  1. Sa ćelijama C1: C3 izabrano, pritisnite F2 da biste se prebacili na režim uređivanja. 

  2. Pritisnite taster F9 da biste konvertovali reference ćelija u vrednosti. Excel konvertuje vrednosti u konstantu niza. Formula bi trebalo da bude = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  3. Pritisnite kombinaciju tastera CTRL + SHIFT + ENTER da biste uneli konstantu niza kao formulu niza.

Brojanje znakova u opsegu ćelija

Sledeći primer vam pokazuje kako da u opsegu ćelija izbrojite broj znakova, uključujući razmake.

  1. Kopirajte celu ovu tabelu i nalepite je u ćeliju A1 na radnom listu.

    Podaci

    Ovo je

    grupa ćelija koje

    se nastavljaju

    da bi formirale

    jednu rečenicu.

    Ukupan broj znakova u ćelijama A2:A6

    =SUM(LEN(A2:A6))

    Sadržaj najduže ćelije (A3)

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

  2. Izaberite ćeliju a8, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER da biste videli ukupan broj znakova u ćelijama a2: A6 (66).

  3. Izaberite ćeliju, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER da biste videli sadržaj najduže ćelije od a2: a6 (ćelija a3).

Sledeća formula se koristi u ćeliji a8 broji ukupan broj znakova (66) u ćelijama od a2 do a6.

=SUM(LEN(A2:A6))

U ovom slučaju, funkcija LEN vraća dužinu svake tekstualne niske u svaku ćeliju u opsegu. Funkcija Sum zatim sabira te vrednosti i prikazuje ishod (66).

Pronalaženje n najmanjih vrednosti u opsegu

Ovaj primer vam pokazuje kako da pronađete tri najmanje vrednosti u opsegu ćelija.

  1. Unesite neke nasumične brojeve u ćelijama a1: a11.

  2. Izaberite ćelije C1 kroz C3. Ovaj skup ćelija će zadržati rezultate koje je vratila formula niza.

  3. Unesite sledeću formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

Ova formula koristi niz konstanti da proceni malu funkciju tri puta i vrati najmanji (1), drugi najmanji (2) i treći broj (3) članova u nizu koji se nalazi u ćelijama a1: da biste pronašli više vrednosti, dodajete još argumenata u neprestano. Takođe možete koristiti dodatne funkcije sa ovom formulom, kao što su SUM ili AVERAGE. Na primer:

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

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

Pronalaženje n najvećih vrednosti u opsegu

Da biste pronašli najveće vrednosti u opsegu, možete zameniti funkciju SMALL funkcijom LARGE. Pored toga, sledeći primeri koriste funkcije ROW i INDIRECT.

  1. Izaberite ćelije D1 kroz D3.

  2. U polje za formulu unesite ovu formulu, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

    = VELIKA (A1: A10, REDOVE (INDIREKTNI ("1:3"))))

Na ovom mestu, biće vam korisno da znate nešto o funkcijama ROW i INDIRECT. Funkciju ROW možete koristiti da biste kreirali niz od uzastopnih celih brojeva. Na primer, izaberite praznu kolonu od 10 ćelija u praksi, unesite ovu formulu niza, a zatim pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

=ROW(1:10)

Formula kreira kolonu od 10 uzastopnih celih brojeva. Da biste videli potencijalni problem, umetnite red iznad opsega koji sadrži formulu niza (to jest, iznad reda 1). Excel podešava reference reda a formula generiše cele brojeve od 2 do 11. Da biste rešili taj problem, u formulu dodajete funkciju INDIRECT:

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

Formula INDIRECT koristi tekstualne niske kao svoje argumente (što je razlog zbog kojeg je opseg 1:10 stavljen pod dvostruke navodnike). Excel ne podešava tekstualne vrednosti kada umetnete redove ili na neki drugi način premestite formulu niza. Kao rezultat, funkcija ROW uvek generiše niz celih brojeva koje želite.

Hajde da pogledamo formulu koju ste ranije koristili – = velika (a5: a14, redu (INDIREKTAN ("1:3")))) – počevši od unutrašnje zagrade i rada spolja: funkcija indirektna daje broj tekstualnih vrednosti, u ovom slučaju vrednosti od 1 do 3. Funkcija Red se kreira sa tri ćelije kolone sa kolonom. Velika funkcija koristi vrednosti u opsegu ćelija a5: a14, a procenjuje se tri puta, jednom za svaku karticu koju vraća funkcija Rou . Vrednosti 3200, 2700 i 2000 vraćaju se u troćelijama kolumne kolone. Ako želite da pronađete još vrednosti, dodajte veći opseg ćelija u indirektnu funkciju.

Kao i sa ranijim primerima, možete da koristite ovu formulu sa drugim funkcijama, kao što su Sum i Average.

Pronalaženje najduže tekstualne niske u opsegu ćelija

Vratite se na raniji primer niske za tekst, unesite sledeću formulu u praznu ćeliju i pritisnite kombinaciju tastera CTRL + SHIFT + ENTER:

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

Pojavljuje se tekst "grupa ćelija koje".

Hajde da pažljivije pogledamo formulu, počevši od unutrašnjih elemenata i radeći od spolja. Funkcija Len daje dužinu svake od stavki u opsegu ćelija a2: a6. Maksimalna funkcija izračunava najveću vrednost između tih stavki, koje odgovaraju najduži tekstualnoj niski, koja se nalazi u ćeliji a3.

Ovo je mesto na kojem stvari postaju malo složenije. Funkcija MATCH izračunava pomak (relativnu poziciju) ćelije koja sadrži najdužu tekstualnu nisku. Da biste to uradili, to zahteva tri argumenta: vrednost za pretraživanje, niz za pretraživanje i tip podudaranja. Funkcija MATCH pretražuje niz za pretraživanje za navedenu vrednost za pretraživanje. U ovom slučaju, vrednost za pretraživanje je najduža tekstualna niska:

(MAX (BROJ A2: A6))

i niska se nalazi u ovom nizu:

LEN (A2: A6)

Argument tipa podudaranja je 0. Tip podudaranja se može sastojati od vrednosti 1, 0 ili -1. Ako navedete 1, MATCH vraća najveću vrednost koja je manja ili jednaka vrednosti za pretraživanje. Ako navedete 0, MATCH vraća prvu vrednost u potpunosti jednaku vrednosti za pretraživanje. Ako navedete -1, MATCH pronalazi najmanju vrednost koja je veća ili jednaka navedenoj vrednosti za pronalaženje. Ako izostavite tip podudaranja, Excel pretpostavlja da je to 1.

Konačno, funkcija INDEX uzima ove argumente: niz i broj reda i kolone u okviru tog niza. Opseg ćelija a2: a6 pruža niz, funkcija MATCH pruža adresu ćelije, a konačni argument (1) navodi da vrednost potiče iz prve kolone u nizu.

Ovaj odeljak obezbeđuje primere naprednih formula niza.

Zbir opsega koji sadrži vrednosti greške

Funkcija SUM u programu Excel ne funkcioniše kada pokušate da saberete opseg koji sadrži vrednost greške, na primer #N/A. Ovaj primer pokazuje kako da saberete vrednosti u opsegu koji se zove „Podaci koji sadrže greške“.

=SUM(IF(ISERROR(Data),"",Podaci))

Formula kreira novi niz koji sadrži originalne vrednosti manje vrednosti greške. Počevši od unutrašnjih funkcija i radeći od spolja, funkcija ISERROR pretražuje opseg ćelija (Podaci) radi grešaka. Funkcija IF vraća određenu vrednost ako uslov koji navedete daje rezultat TRUE, i drugu vrednost ako daje rezultat FALSE. U ovom slučaju, vraća prazne niske ("") za sve vrednosti greške zato što daju rezultat TRUE i vraća preostale vrednosti iz opsega (Podaci) zato što je rezultat FALSE, što znači da ne sadrže vrednosti greške. Funkcija SUM zatim izračunava ukupan zbir za filtrirani niz.

Brojanje vrednosti greške u opsegu

Ovaj primer je sličan prethodnoj formuli, ali vraća broj vrednosti greške u opsegu koji se zove „Podaci“ umesto da ih filtrira:

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

Ova formula kreira niz koji sadrži vrednost 1 za ćelije koje sadrže greške i vrednost 0 za ćelije koje ne sadrže greške. Formule možete pojednostaviti i postići isti rezultat uklanjanjem trećeg argumenta za funkciju IFna sledeći način:

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

Ako ne navedete argument, funkcija IF vraća vrednost FALSE ako ćelija ne sadrži vrednost greške. Formulu možete još više pojednostaviti:

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

Ova verzija funkcioniše zato što je TRUE*1=1 i FALSE*1=0.

Sabiranje vrednosti na osnovu uslova

Vrednosti ćete možda morati da saberete na osnovu uslova. Na primer, ova formula niza sabira samo pozitivne cele brojeve u opsegu koji se zove „Prodaja“:

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

Funkcija IF kreira niz pozitivnih vrednosti i netačnih vrednosti. Funkcija SUM u suštini zanemaruje netačne vrednosti zato što je 0+0=0. Opseg ćelija koji koristite u ovoj formuli može da se sastoji od bilo kojeg broja redova i kolona.

Takođe možete sabrati vrednosti koje ispunjavaju više uslova. Na primer, ova formula niza izračunava vrednosti veće od 0 i manje ili jednake 5:

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

Imajte na umu da ova formula vraća grešku ako opseg sadrži neke ćelije koje nisu numeričke.

Takođe možete kreirati formule niza koje koriste tip OR uslova. Na primer, možete sabrati vrednosti koje su manje od 5 i veće od 15:

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

Funkcija IF pronalazi sve vrednosti manje od 5 i veće od 15, a zatim prosleđuje te vrednosti u funkciju SUM.

Funkcije AND i OR nije moguće koristiti direktno u formulama niza zato što ove funkcije vraćaju jedan rezultat, ili TRUE ili FALSE, a funkcije niza zahtevaju nizove rezultata. Ovaj problem možete izbeći korišćenjem logike prokazane u prethodnoj formuli. Drugim rečima, vršite matematičke operacije, kao što su dodavanje ili množenje, na vrednostima koje ispunjavaju OR ili AND uslov.

Izračunavanje prosečne vrednosti koja isključuje nule

Ovaj primer prikazuje kako da uklonite nule iz opsega kada morate da izračunate prosečne vrednosti u tom opsegu. Formula koristi podatke koji se zovu „Prodaja“:

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

Funkcija IF kreira niz vrednosti koji nije jednak 0, a zatim prosleđuje te vrednosti u funkciju AVERAGE.

Izračunavanje broja razlika između dva opsega ćelija

Ova formula niza upoređuje vrednosti u dva opsega ćelija koji se zovu „MyData“ i „YourData“ i vraća broj razlika između ta dva. Ako je sadržaj ova dva opsega identičan, formula vraća vrednost 0. Da biste koristili ovu formulu, opsezi ćelija moraju biti iste veličine i iste dimenzije (na primer, ako „MyData“ predstavlja opseg od 3 reda i 5 kolona, „YourData“ takođe mora da bude opseg od 3 reda i 5 kolona):

=SUM(IF(MyData=YourData,0,1))

Formula kreira novi niz iste veličine kao opsezi koje upoređujete. Funkcija IF popunjava niz vrednošću 0 i vrednošću 1 (0 za nepodudaranja i 1 za identične ćelije). Funkcija SUM zatim vraća zbir vrednosti u nizu.

Formulu možete ovako pojednostaviti:

= SUM (1 * (MyData<>YourData))

Poput formule koja broji vrednosti greške u opsegu, ova formula funkcioniše zato što je TRUE*1=1, a FALSE*1=0.

Pronalaženje lokacije maksimalne vrednosti u opsegu

Ova formula niza vraća broj reda maksimalne vrednosti u opsegu sa jednom kolonom koji se zove „Podaci“:

=MIN(IF(Data=MAX(Podaci),ROW(Podaci),""))

Funkcija IF kreira novi niz koji odgovara opsegu po imenu „Podaci“. Ako odgovarajuća ćelija sadrži maksimalnu vrednost u opsegu, niz sadrži broj reda. U suprotnom, niz sadrži praznu nisku (""). Funkcija MIN koristi novi niz kao drugi argument i vraća najmanju vrednost, koja odgovara broju reda maksimalne vrednosti u opsegu „Podaci“. Ako opseg po imenu „Podaci“ sadrži identične maksimalne vrednosti, formula vraća red prve vrednosti.

Ako želite da vratite stvarnu adresu ćelije maksimalne vrednosti, koristite ovu formulu:

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

Priznanje

Delovi ovog članka zasnovani su na nizu Excel Power User kolona koje je napisao Colin Wilcox i koji su prilagodili iz poglavlja 14 i 15 od Excel 2002 formule, knjigom koju je napisao Džon Volkenbah, bivši Excel MVP.

Potrebna vam je dodatna pomoć?

Možete uvek da postavite pitanje stručnjaku u zajednici Excel Tech Community, dobijete podršku u zajednici Answers community ili predložite novu funkciju ili poboljšanje na sajtu Excel User Voice.

Takođe pogledajte

Dinamički nizovi i ponašanje prelivenog niza

Dinamičke formule niza naspram formula niza

Funkcija FILTER

Funkcija RANDARRAY

Funkcija SEQUENCE

Funkcija SINGLE

Funkcija SORT

Funkcija SORTBY

Funkcija UNIQUE

#SPILL! greške u programu Excel

Pregled formula

Razvijte Office veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Office Insider korisnicima

Da li su vam ove informacije koristile?

Hvala vam na povratnim informacijama!

Hvala za povratne informacije! Izgleda da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×