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.

Formule niza je formula koja može da izvrši više izračunavanja na jednu ili više stavki u nizu. Možete da niza kao red ili kolonu sa vrednostima ili kombinacije redova i kolona vrednosti. Formula niza može da vrati više rezultata ili jedan rezultat.

Počevši od septembra 2018 ispravke za Office 365, formule koje može da vrati više rezultata će automatski reci ih vertikalno ili horizontalno u susednim ćelijama. Ova promena ponašanja je takođe uz nekoliko nove funkcije dinamičke niza. Formule dinamičke niza, da li koriste postojeće dinamičke niza funkcije, ili samo treba da se ubacim u jednu ćeliju, a zatim potvrdili pritiskom na taster Enter. Formule niza ranije, zastarele zahtevaju prvi put izabrati celu izlazni opseg, a zatim potvrđuje formulu sa Kombinaciju tastera Ctrl + Shift + Enter. Oni se često nazivaju CSE formulama.

Možete da koristite formule niza za izvršavanje složenih zadataka, kao što su:

  • Brzo kreiranje uzorka skupova podataka.

  • Izračunavanje broja znakova koji se nalaze u opsegu ćelija.

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

  • Saberite svaku n vrednost u opsegu vrednosti.

Sledeći primeri prikazuju kako da kreirate višećelijske i jednoćelijske formule niza. Gde je to moguće, ste uključeni primeri sa nekim od funkcije dinamičke niza, kao i postojeće formule niza uneta kao dinamičke i zastareli niza.

Preuzmite naše primere

Preuzimanje primer radne sveske sa svim niza formule primerima 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 višećelijske u ćeliji H10 = F10:F19 * G10:G19 da biste izračunali broj automobila prodao tako što ćete cena po jedinici

  • Ovde su izračunava ukupan iznos prodaje kupea i limuzina za svakog prodavca tako što ćete uneti = F19:F19 * G10:G19 u ćeliji H10.

    Kada pritisnete taster Enter, videćete rezultate reci nadole do ćelije H10:H19. Imajte u vidu da pada opsega je istaknut ivice kada izaberete bilo koju ćeliju u opsegu pada. Možda ćete primetiti takođe su neaktivne formule u ćelijama H10:H19. Su samo za referencu, tako da ako želite da prilagodite formulu, moraćete da biste izabrali ćeliju H10, gde je master formulu živi.

  • Formule niza pojedinačnih ćelija

    Formule niza pojedinačnih ćelija da biste izračunali ukupnu sa =SUM(F10:F19*G10:G19)

    U ćeliji H20 primer radne sveske, otkucajte ili kopirajte i nalepite =SUM(F10:F19*G10:G19)i pritisnite taster Enter.

    U ovom slučaju, Excel množi vrednosti u nizu (opseg ćelija F10 do G19) i zatim koristi funkciju SUM za dodavanje ukupne vrednosti zajedno. Rezultat je sveukupne vrednosti od $1,590,000 prodaje.

    Ovaj primer prikazuje kako moćne ovaj tip formule mogu biti. Na primer, pretpostavimo da imate 1000 redova podataka. Možete da saberete delimično ili potpuno tih podataka tako što ćete kreirati formule niza u jednoj ćeliji umesto prevlačenja formulu kroz 1000 redove. Takođe, primetiti da jednoćelijske formule u ćeliji H20 potpuno nezavisna višećelijske formule (formule u ćelijama H10 do H19). Ovo je drugi prednost korišćenja formula niza — fleksibilnost. Možete da promenite drugim formulama u koloni H bez uticaja na formulu u H20. To može biti dobru praksu da bi nezavisni zbirovi ovako, i pomaže provere valjanosti tačnost rezultata.

  • Formula dinamičke niza takođe pružaju ove prednosti:

    • Provera usaglašenosti    Ako kliknete na neku od ćelija iz H10 nadole, videćete istu formulu. Ta doslednost mogu da obezbede dodeljivani.

    • Bezbednost    Ne možete da zamenite komponentu višećelijske formule. Na primer, kliknite na ćeliju H11 i pritisnite taster Delete. Excel neće promeniti u nizu izlaz. Da biste ga promenili, morate da biste izabrali gornju levu ćeliju u nizu ili u ćeliji H10.

    • Manje veličine datoteke    Možete da koristite često jednom formulom niza umesto nekoliko posredno formula. Na primer, automobil prodaje primer koristi jednu formulu niza za izračunavanje rezultati u koloni E. Ako ste koristili kao što su standardne formule = F10 * zemlje članice G10, F11 * G11, F12 * G12, itd, možete da koriste 11 različitih formula za izračunavanje iste rezultate. To nije bitno, ali ako ste imali hiljada redova zbiru? Zatim ga možete promeniti.

    • Efikasnosti    Funkcije niza može biti efikasan način za pravljenje složene formule. Niz formule =SUM(F10:F19*G10:G19) je isto kao ovo: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Koja    Dinamičke niza formula će automatski reci u izlazni opseg. Ako su izvorni podaci u Excel tabeli, zatim dinamičke niza formula će automatski promeniti veličinu kao što je dodavanje ili uklanjanje podataka.

    • #SPILL! grešku    Dinamički nizovi uvela na #SPILL! grešku, koja ukazuje na to da predviđeno pada opsega blokiran iz nekog razloga. Kada otklonite je blokiran, formula će automatski reci.

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 pomoću zareza, kreirajte horizontalni niz (red). Ako razdvojite stavke koristeći tačku i zarez, kreirajte vertikalni niz (kolona). Da biste kreirali dvodimenzionalnu, Razgraničite stavke u svakom redu zarezima, a Razgraničite svaki red tačkom i zarezom.

Sledeća procedura će vam pružiti vežbu u kreiranju horizontalnih, vertikalnih i dvodimenzionalnih konstanti. Nismo ćete prikazali primere koristi niz funkcija za Automatsko generisanje konstanti niza, kao i ručno unetih konstante niza.

  • Kreiranje horizontalne konstante

    Koristite radnu svesku iz prethodnih primera ili kreirajte novu radnu svesku. Izaberite bilo koju praznu ćeliju i unesite =SEQUENCE(1,5). SEKVENCA funkcija pravi red 1 do 5 kolonu niza isti kao = {1,2,3,4,5}. Prikazuje sledeći rezultat:

    Kreiranje horizontalnu konstantu niza sa =SEQUENCE(1,5) ili = {1,2,3,4,5}

  • Kreiranje vertikalne konstante

    Izaberite bilo koju praznu ćeliju sa sobom ispod njega i unesite =SEQUENCE(5)ili = {1; 2; 3; 4; 5}. Prikazuje sledeći rezultat:

    Kreiranje vertikalna konstanta niza sa = SEQUENCE(5) ili = {1; 2; 3; 4; 5}

  • Kreiranje dvodimenzionalne konstante

    Izaberite bilo koju praznu ćeliju sa sobom desno i ispod njega i unesite =SEQUENCE(3,4). Vidite sledeći rezultat:

    Kreiranje 3 reda tako što ćete konstanta niza 4 kolone sa =SEQUENCE(3,4)

    Možete da unesete: ili = {1,2,3,4 5,6,7,8; 9,10,11,12}, ali treba obratiti pažnju na gde smeštate tačkom i zarezom u odnosu na zareze.

    Kao što vidite, niz opcija nudi značajne prednosti u odnosu na ručno unošenje vrednosti konstante niza. Pre svega, on čuva vreme, ali on takođe može pomoći da smanjite greške od ručnog unosa. Takođe je lakši za čitanje, naročito kao tačkom i zarezom može biti teško razlikovati od zarez za razdvajanje.

Evo primera koji koristi niza konstanti kao deo veći formule. U uzorku radne sveske, idite na radnom listu konstante u formuli ili kreirajte novi radni list.

U ćeliji D9 ulaska =SEQUENCE(1,5,3,1), ali takođe možete uneti 3, 4, 5, 6 i 7 u ćelijama A9:H9. Ništa posebno u vezi sa tom određeni broj izbora, odabrali smo nešto koji je različit od 1-5 za razlika.

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

Korišćenje konstanti niza u formulama. U ovom primeru koristili smo = SUM (D9:H(*SEQUENCE(1,5))

SEKVENCA funkcija pravi u ekvivalentnu vrednost u nizu konstantu {1,2,3,4,5}. Zato što Excel izvršava operacije u izrazima prvo unutar zagrada, sledeća dva elemente koji stupaju su vrednosti u ćelijama u D9:H9 i operator za množenje (*). U ovom trenutku, formula množi vrednosti u uskladišteni niz tako što ćete odgovarajuće vrednosti u konstanti. To je za:

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

Na kraju, funkcija SUM sabira vrednosti i vraća 85.

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

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

Elementi koje možete koristiti u konstanti niza

  • Konstante niza može da sadrži brojeve, tekst, logičke vrednosti (kao što su TRUE i FALSE) i vrednosti greške kao što su #N/A. Možete da koristite brojeve u ceo broj, decimalni i naučnog formata. Ako uključite tekst, morate da ga stavite pod navodnike („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.

Najbolji način za korišćenje konstanti niza je da im imena. Imenovane konstante može biti mnogo lakše da koristite, a mogu da sakriju neke od složenost formulama niza sa drugima. Imenovanje konstante niza i koristite u formuli, uradite sledeće:

Idite na formule > definisana imena > Definiši ime. U polju ime otkucajte Kvartal1. U okviru odnosi se na unesite sledeću konstantu (ne zaboravite da ručno otkucate velike zagrade):

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

Dijalog sada trebalo da izgleda ovako:

Dodavanje imenovani konstanti sa formulama > definisana imena > Menadžer imena > novog

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

Prikazuje sledeći rezultat:

Koristite imenovani konstanti u formuli, kao što je = Kvartal1, gde je Kvartal1 definisana kao = {„Januar”, „Februar”, „mart”}

Ako želite rezultata na reci vertikalno umesto horizontalno, možete da koristite =TRANSPONUJETE(Kvartal1).

Ako želite da prikažete listu od 12 meseci, kao što možete da koristite kada izgradnja finansijski izveštaj, možete da zasnujete van trenutnu godinu sa funkcijom niz. Fino stvar o ova funkcija je da, iako se prikazuju samo u mesecu, postoji važeći datum iza toga koje možete koristiti u drugim izračunavanjima. Ovi primeri ćete pronaći na imenovane konstante niza i brzo uzorak skup podataka radnih listova u primer radne sveske.

=TEXT(date(year(Today()),SEQUENCE(1,12),1),"Mmm")

Koristite kombinaciju funkcije tekst, datum, godine, danas, i niz da biste napravili dinamičke liste od 12 meseci

Ovo koristi funkciju za datum da biste kreirali datuma na osnovu trenutnu godinu, niz kreira konstante niza od 1 do 12 za od januara do decembra, a zatim Funkcija TEXT konvertuje format prikaza u „mmm” (Jan, Feb., Mar, itd.). Ako biste želeli da prikažete punog imena, kao što je januar, možete da koristite „mmmm”.

Kada koristite imenovana konstanta kao formulu niza, ne zaboravite da unesete znaka jednakosti, kao u = Kvartal1, ne samo Kvartal1. Ako ne, Excel tumači niza kao niska teksta i formule ne rade kao što je očekivano. Na kraju, imajte na umu da možete da koristite kombinaciju funkcija, tekst i brojeve. Sve zavisi od toga kako kreativni koji želite da dobijete.

Sledeći primeri ilustruju neke načine u kojem možete da koristite konstante niza se koriste u formulama niza. Neki od primera koriste Funkcija TRANSPOSE da biste konvertovali redova u kolone i obrnuto.

  • Više svake stavke u nizu

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

    Takođe, možete da se deli sa (/), dodajte sa (+) i oduzimanje sa (-).

  • Izračunavanje kvadratnog korena za stavke u nizu

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

  • Pronalaženje kvadratni koren od kvadrata stavke u nizu

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

  • Prebacivanje jednodimenzionalnog reda

    Unesite =TRANSPOSE(SEQUENCE(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(SEQUENCE(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(SEQUENCE(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 da koristite formule niza za kreiranje nove niza iz postojećih niz.

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

    Obavezno otkucajte {(otvorena velika zagrada) pre nego što otkucate 10 i} (zatvorena velika zagrada) nakon što otkucate 180, jer pravite niz brojeva.

    Dalje, unesite = D9 #ili = D9:I11 u praznu ćeliju. 3 x 6 niza ćelija se pojavljuje sa istim vrednostima koje vidite u D9:D11. Znak # se zove rekla operator opsega, a je Excel način upućivanja na ceo niz opsega umesto da ga otkucate.

    Koristite operator prolivene opsega (#) da biste uputili postojeće niz

  • Kreiranje konstante niza iz postojećih vrednosti

    Možete da izvršite rezultate formule niza prosuo i da konvertujete u sastavne delove. Izaberite ćeliju D9, a zatim pritisnite taster F2 da biste se prebacili u režim uređivanja. Dalje, pritisnite taster F9 da biste konvertovali reference ćelija vrednosti, koje Excel zatim konvertuje u niz konstanti. Kada pritisnete taster Enter, formulu = D9 #, trebalo bi da bude = {10,20,30; 40,50,60; 70,80,90}.

  • Brojanje znakova u opsegu ćelija

    Sledeći primer vam pokazuje kako da biste izračunali broj znakova u opsegu ćelija. Ovo uključuje razmake.

    Prebrojavanje ukupnog broja znakova u opsegu, a drugi nizovi za rad sa tekstualne niske

    = SUM (LEN(C9:C13))

    U ovom slučaju, Funkcija LEN vraća dužinu svaku tekstualnu nisku u svakoj od ćelije u opsegu. Funkcija SUM sabira te vrednosti i prikazuje rezultat (66). Ako biste želeli da biste dobili prosečan broj znakova, možete da koristite:

    = AVERAGE (LEN(C9:C13))

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

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

    Ova formula radi samo kada opseg podataka sadrži jednu kolonu ćelija.

    Hajde da detaljnije pogledali formulu, počevši od unutrašnje elemente i rad spolja. Funkcija LEN vraća dužinu svakog stavki u opsegu ćelija D2:D6. Funkcija MAX izračunava najveću vrednost među te stavke koja odgovara najduže tekstualne niske koje se nalazi u ćeliji D3.

    Evo stvari postaju malo složene. Funkcija MATCH izračunava pomak (relativni položaj) na ćeliju koja sadrži najduže tekstualne niske. Da biste to uradili, to zahteva tri argumenta: za pronalaženje vrednosti za pronalaženje niza i tip podudaranja. Funkcija MATCH pretražuje niza za pronalaženje za navedenu za pronalaženje vrednost. U ovom slučaju, je vrednost za pronalaženje najduže tekstualne niske:

    MAX(LEN(C9:C13)

    i niska se nalazi u ovom nizu:

    LEN(C9:C13)

    Argument tip podudaranja u ovom slučaju ima vrednost 0. Podudaranje tipa može biti 1, vrednosti-1 ili 0.

    • 1 – vraća najveću vrednost koja je manja ili jednaka za pronalaženje val

    • 0 - vraća prvu vrednost potpuno jednaka vrednosti za pronalaženje

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

    • Ako izostavite tip podudaranja, Excel pretpostavlja 1.

    Na kraju, funkcija INDEX vodi sledeće argumente: niza i broj redova i kolona u tom nizu. Opseg ćelija C9:C13 pruža niza, funkcija MATCH pruža adresu ćelije i konačno argument (1) navodi da vrednost potiče iz prve kolone u nizu.

    Ako biste želeli da se sadržaj najmanje tekstualne niske, možete da zamenite MAX u gorenavedenom primeru MIN.

  • Pronalaženje n najmanjih vrednosti u opsegu

    Ovaj primer prikazuje kako da pronađete tri najmanje vrednosti u opsegu ćelija, gde je niz uzorak podataka iz ćelija B9:B18has kreirana pomoću: = INT (RANDARRAY(10,1) * 100). Imajte na umu da RANDARRAY nepostojane funkcije, tako da ćete dobiti novi skup nasumične brojeve svaki put kada se Excel izračunava.

    Formule niza za pronalaženje n najmanju vrednost u programu Excel: =SMALL(B9#,SEQUENCE(D9))

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

    Ova formula koristi konstante niza da bi funkcija SMALL izračunava tri puta i vratili najmanje 3 člana u nizu koji se nalazi u ćelijama B9:B18, gde je 3 je promenljive vrednosti u ćeliji D9. Da biste pronašli više vrednosti, možete da povećate vrednost u nizu funkcija ili da dodate više argumenata konstanta. Takođe možete da koristite dodatne funkcije uz ovu formulu, kao što je zbir ili prosek. Na primer:

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

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

  • Pronalaženje n najvećih vrednosti u opsegu

    Da biste pronašli najvećih vrednosti u opsegu, možete da zamenite funkcije SMALL funkcija LARGE. Pored toga, sledeći primer koristi funkcije reda i funkcija INDIRECT .

    Unesite = velike (B9 #, red (INDIRECT ("1:3 ))), ili = velike (B9:B18,ROW(INDIRECT("1:3")))

    U ovom trenutku, to vam mogu pomoći da znate malo o funkcije reda i funkcija INDIRECT. Možete da koristite funkciju reda da biste kreirali niz uzastopne cele brojeve. Na primer, izaberite prazne i unesite:

    =ROW(1:10)

    Formula kreira kolonu sa 10 uzastopne cele brojeve. Da biste videli potencijalni problem, Umetanje reda iznad opsega koji sadrži formulu niza (to jest, iznad red 1). Excel prilagođava red reference, a formula odmah generiše celih brojeva 2 na 11. Da biste rešili taj problem, možete dodati funkcije INDIRECT formulu:

    = REDA (INDIRECT ("1:10 ))

    Funkcija INDIRECT koristi tekstualne niske kao argumenti (zbog čega je opseg 1:10 znacima navoda). Excel ne prilagodite tekstualne vrednosti kada Umetanje redova ili ili premestite formulu niza. Rezultat funkcije ROW uvek generiše niza sa celim brojevima koje želite. Lako mogli da koristite sekvence:

    =SEQUENCE(10)

    Prođimo kroz formulu koju ste ranije koristili – = LARGE (B9 #, red (funkcija INDIRECT („1:3 ))) – počevši od unutrašnje zagrade i radu spolja: funkcija za INDIRECT daje skup tekstualne vrednosti, u ovom slučaju vrednosti 1 do 3. Funkcija ROW naizmenično generiše 3 ćelije kolone niza. Funkcija LARGE koristi vrednosti u opsegu ćelija B9:B18 i on se procenjuje tri puta, jednom za svaku referencu vratio funkcije ROW. Ako želite da biste pronašli više vrednosti, možete dodati opseg ćelija veći funkcija INDIRECT. Na kraju, kao i sa malim primere, 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 zbir opsega koji sadrži vrednosti greške, kao što je #VALUE! ili #N/A. Ovaj primer vam pokazuje kako da saberete vrednosti u opsegu koji se zove podataka koji sadrže greške:

    Koristite nizovi da se ophodite prema grešaka. Na primer, =SUM(IF(ISERROR(Data),"",Data) će sabiranje u opsegu koji se zove podataka, čak i ako ona sadrži 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 ga daje broj vrednosti greške u opsegu koji se zove podatke 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.

Možda ćete morati da sabiranje vrednosti na osnovu uslova.

Nizovi možete da koristite da biste izračunali na osnovu određenih uslova. =SUM(IF(Sales>0,Sales)) će sabrati sve vrednosti veće od 0 u opsegu koji se zove prodaja.

Na primer, ova formula niza sabira pozitivni celi brojevi u opsegu koji se zove prodaje, što predstavlja E9:E24 ćelije u gorenavedenom primeru:

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

Funkcija IF kreira niz vrednosti pozitivni i false. Funkcija SUM u suštini zanemaruje vrednosti false jer 0 + 0 = 0. Opseg ćelija koje se koriste u ova formula može da se sastoji od bilo kog broja redova i kolona.

Takođe možete da saberete vrednosti koje zadovoljavaju više od jednog uslova. Na primer, ova formula niza izračunava vrednosti veće od 0 i manji od 2 500:

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

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

Takođe možete da kreirate formule niza koje se koriste tip OR uslova. Na primer, možete da saberete vrednosti koje su veće od 0 ili manja od 2 500:

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

Ne možete da koristite funkcije AND i OR funkcija u formulama niza direktno zato što te funkcije vraćaju jedan rezultat TRUE ili FALSE i niza funkcije zahtevaju nizovi rezultata. Možete da rešite problem pomoću logike prikazane u prethodnoj formuli. Drugim rečima, izvršite matematičke operacije, kao što su sabiranje ili množenje na vrednosti koje zadovoljavaju polju ili ili i uslova.

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 u odnosu na vrednosti u dva opsega ćelija po imenu MyData i YourData i vraća broj razlike između dve. Ako se sadržaj dva opsega identične, formula vraća 0. Da biste koristili ovu formulu, opsege ćelija moraju biti iste veličine i iste dimenzije. Na primer, ako je MyData opseg 3 reda po 5 kolona, YourData takođe mora da 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))

Pronaći ćete sličnih primera u uzorku radne sveske na radnom listu razlike između skupova 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 dolenavedenu tabelu i nalepite ga u ćeliju A1 u praznom radnom listu.

Prodaja Osoba

Automobil Tip

Broj Prodate

Po jedinici Cena

Ukupno Prodaja

Branković

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 ukupan iznos prodaje kupea i limuzina za svakog prodavca, izaberite ćelije ćelije E2: E11, unesite formulu = C2: C11 * D2: d11, a zatim pritisnite Kombinaciju tastera Ctrl + Shift + Enter.

  2. Da biste videli sveukupna vrednost prodaje izaberite ćeliju F11, unesite formulu =SUM(C2:C11*D2:D11)i pritisnite Kombinaciju tastera Ctrl + Shift + Enter.

Kada pritisnete Kombinaciju tastera Ctrl + Shift + Enter, Excel okružuje formulu sa velike zagrade ({}) i umeće instancu formulu u svakoj ćeliji izabranog opsega. To se dešava veoma brzo, tako da možete videti u koloni E ukupan iznos prodaje za svaki tip automobila za svakog prodavca. Ako izaberete E2, a zatim izaberite E3, E4 i tako dalje, videćete da se prikazuju istu formulu: {= C2: C11 * D2: d11}.

Ukupne vrednosti u koloni E izračunava formula niza

  • Kreiranje jednoćelijske formule niza

U ćeliji D13 radne sveske, otkucajte sledeću formulu i pritisnite Kombinaciju tastera Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

U ovom slučaju, Excel množi vrednosti u nizu (opseg ćelija C2 do D11) i zatim koristi funkciju SUMza dodavanje ukupne vrednosti zajedno. Rezultat je sveukupne vrednosti od $1,590,000 prodaje. Ovaj primer prikazuje kako moćne ovaj tip formule mogu biti. Na primer, pretpostavimo da imate 1000 redova podataka. Možete da saberete delimično ili potpuno tih podataka tako što ćete kreirati formule niza u jednoj ćeliji umesto prevlačenja formulu kroz 1000 redove.

Takođe, primetiti da jednoćelijske formule u ćeliji D13 potpuno nezavisna višećelijske formule (formulu u ćelije E2 do E11). Ovo je drugi prednost korišćenja formula niza — fleksibilnost. Da promenite formule u koloni E ili brisanje toj koloni zajedno, 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    Ne možete da zamenite komponentu višećelijske formule. Na primer, kliknite na ćeliju E3 i pritisnite taster Delete. Morate da izaberete ceo opseg ćelija (E2 kroz E11) i promenite formulu za ceo niz ili ostavite niza kao. Kao dodatnu bezbednost meru, morate da pritisnete Kombinaciju tastera Ctrl + Shift + Enter da biste potvrdili promene u formulu.

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

Opšte, formula niza koristite standardnu sintaksu formule. Svi oni počinju znakom jednakosti (=), a veći deo ugrađene funkcije programa Excel možete koristiti u formulama niza. Ključni razlika je da kada koristite formulu niza, pritisnite Kombinaciju tastera Ctrl + Shift + Enter da biste uneli formulu. Kada to uradite, Excel stavlja vašu formulu niza sa zagradama – ako ručno otkucate velike zagrade, formula će biti konvertovana u tekstualnoj niski i neće funkcionisati.

Funkcije niza može biti efikasan način za pravljenje složene formule. Niz formulu =SUM(C2:C11*D2:D11) je isto kao ovo: =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 svaki put kada treba da unesete formulu niza. Ovo se odnosi na pojedinačnih ćelija i 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 formule (na primer, ćelije E2: E11), a zatim pritisnite taster Delete.

  • Nije moguće umetati prazne ćelije u ili brisati ćelije iz višećelijske formule.

Ponekad, možda ćete morati da biste proširili formulu niza. Izaberite prvu ćeliju u opsegu postojeće niza i nastavite dok ne izaberete ceo opseg koji želite da produžite da formula. Pritisnite taster F2 da biste uredili formulu, a zatim pritisnite Kombinaciju tastera CTRL + SHIFT + ENTER da biste potvrdili formulu kada ste prilagodili formule opsega. Ključ je da biste izabrali ceo opseg, počevši od gornju levu ćeliju u nizu. Gornju levu ćeliju je uređen.

Formule niza su sjajne, ali imaju neke nedostatke:

  • Povremeno može da zaboravite da pritisnete Kombinaciju tastera Ctrl + Shift + Enter. To se može dogoditi čak i iskusnih korisnika programa Excel. Ne zaboravite da pritisnete ove kombinacije svaki put kada unosite ili uređujete formule niza.

  • Drugi korisnici radne sveske ne mogu razumeti formula. U praksi, formule niza se obično ne objašnjeno u radnom listu. Stoga, ako drugim osobama je potrebna da biste izmenili radnih svezaka, trebalo bi da izbegnete formule niza ili uverite se da te osobe znaju za sve formule niza i naučite kako da promenite ih, ako treba da.

  • 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 komponenta formule niza, okružuju konstanti sa zagradama tako što ćete ih ručno otkucate. 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 redu: {1,2,3,4}. Evo niza u kolonu: {1; 2; 3; 4}. A evo niz dva reda i četiri kolone: {1,2,3,4; 5,6,7,8}. U nizu dva reda, prvi red predstavlja 1, 2, 3 i 4, a drugi red je 5, 6, 7 i 8. Jedan zarez razdvaja dva reda između 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 polju 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 otvorene i zatvorene velike zagrade ({}) i Excel će dodati drugi skup 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 polju 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 polju 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 konstantu niza: {1,2,3,4,5}. Ne zaboravite da Excel ne okružuju konstante niza sa zagradama; Zapravo ih otkucate. Takođe imajte na umu da kada dodate konstante u 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 isprobali ovo, kopirajte funkcija Izaberite praznu ćeliju u radnoj svesci, nalepite formulu u polje za formulu i pritisnite Kombinaciju tastera Ctrl + Shift + Enter. Videćete isti rezultat kao u stariju vežbu koji koristi 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.

Jedna od najbolji način za korišćenje konstanti niza jeste da im imena. Imenovane konstante može biti mnogo lakše da koristite, a mogu da sakriju neke od složenost formulama niza sa drugima. Imenovanje konstante niza i koristite u formuli, uradite sledeće:

  1. Na kartici formule , u grupi Definisana imena kliknite na dugme Definiši ime.
    Pojavljuje 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:

  • Neke elemente ne mogu da budu odvojeni odgovarajuća znakom. Ako izostavite zarezom ili tačkom i zarezom, ako stavite nešto na pogrešnom mestu, konstantu niza možda neće biti ispravno kreirana ili možda ćete 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 ilustruju neke načine u kojem možete da koristite konstante niza se koriste u formulama niza. Neki od primera koriste Funkcija TRANSPOSE da biste konvertovali redova u kolone i obrnuto.

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, koji unosi ovaj niz brojeva u opsegu ćelija C8: E10 pomoću formule niza. Na radnom listu, C8 do E10 bi trebalo da izgleda ovako:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Izaberite opseg ćelija od C1 do E3.

  4. U polju za formulu unesite sledeću formulu, a zatim pritisnite Kombinaciju tastera Ctrl + Shift + Enter:

    =C8:E10

    3 x 3 niza ćelija se pojavljuje u ćelijama od C1 do E3 sa istim vrednostima koje vidite u ćelijama od C8 do E10.

Kreiranje konstante niza iz postojećih vrednosti

  1. Sa ćelija C1: C3 izabran, pritisnite taster F2 da biste se prebacili u režim uređivanja.

  2. Pritisnite taster F9 da biste konvertovali reference za ćelije u vrednosti. Excel konvertuje vrednosti u niz konstanti. Formula sada 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 A10, a zatim pritisnite Kombinaciju tastera Ctrl + Shift + Enter da biste videli sadržaj najduže od ćelija a2: a6 (ćelija A3).

Koristi se sledeća formula u ćeliji A8 izračunava ukupan broj znakova (66) u ćelijama od A2 do A6.

=SUM(LEN(A2:A6))

U ovom slučaju, funkcija LEN vraća dužinu svaku tekstualnu nisku u svakoj od ćelije u opsegu. Funkcija SUM sabira te vrednosti i prikazuje rezultat (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 od C1 do C3. Ovaj skup ćelija će sadržati rezultate dobijene formulu niza.

  3. Unesite sledeću formulu, a zatim pritisnite Kombinaciju tastera Ctrl + Shift + Enter:

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

Ova formula koristi konstante niza da procenite funkcije SMALL tri puta i vratili se najmanja (1), drugi najmanji (2) i treće najmanje članovi (3) u nizu koji se nalazi u ćelijama A1:A10 za pronalaženje više vrednosti, možete da dodate više argumenata u konstanta. Takođe možete da koristite dodatne funkcije uz ovu formulu, kao što je zbir ili prosek. Na primer:

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

= AVERAGE (MALA (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 do D3.

  2. U polje za formulu unesite sledeću formulu i pritisnite Kombinaciju tastera Ctrl + Shift + Enter:

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

U ovom trenutku, to vam mogu pomoći da znate malo o reda i funkcija INDIRECT . Možete da koristite funkciju reda da biste kreirali niz uzastopne cele brojeve. Na primer, izaberite praznu kolonu 10 ćelija u radnoj svesci praksu, unesite ovu formulu niza i 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 u formulu koju ste ranije koristili – = LARGE (A5:A14,ROW(INDIRECT("1:3"))) – počevši od unutrašnje zagrade i radu spolja: funkcija INDIRECT daje skup tekstualne vrednosti, u ovom slučaju vrednosti 1 do 3. Funkcija ROW naizmenično generiše tri ćelije stubasta niza. Funkcija LARGE koristi vrednosti u opsegu ćelija A5:A14 i on se procenjuje tri puta, jednom za svaku referencu vratio funkcije ROW . Vrednosti 3200, 2700 i 2000 se vraćaju stubasta nizu tri ćelije. Ako želite da biste pronašli više vrednosti, možete dodati opseg ćelija veći funkcije INDIRECT .

Kao sa prethodnim 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

Idite nazad na starijim tekst niska primer, 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)

Tekst „grupa ćelija koje” se pojavljuje.

Hajde da detaljnije pogledali formulu, počevši od unutrašnje elemente i rad spolja. Funkcija LEN vraća dužinu svake od stavki u opsegu ćelija a2: a6. Funkcija MAX izračunava najveću vrednost među te stavke koja odgovara najduže tekstualne niske koje 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 (LEN(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.

Na kraju, funkcija INDEX vodi sledeće argumente: niza i broj redova i kolona u tom nizu. Opseg ćelija a2: a6 pruža niza, funkcija MATCH pruža adresu ćelije i konačno 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))

Pretplate

Delovi ovog članka su na osnovu niz kolona Excel Power korisnik upisuje Colin Wilcox i prilagođen iz poglavlja 14 i 15 Excel 2002 formula, na koju je napisao John Walkenbach, bivšeg 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

Formula dinamičke niza protiv zastarelog CSE formule 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! Zvuči da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×