Uputstva i primeri formula niza

Uputstva i primeri formula niza

Da biste postali iskusni korisnik programa Excel, morate znati da koristite formule niza, koje mogu da vrše izračunavanja koja ne biste mogli da izvršite pomoću drugih tipova formula. Sledeći članak je zasnovan na seriji kolumni „Iskusni korisnik programa Excel“ koje je napisao Kolin Vilkoks i adaptiran je iz 14. i 15. poglavlja knjige Excel 2002 formule koju je napisao Džon Valkenbah, Excel MVP.

Više informacija o formulama niza

Formule niza se često nazivaju CSE formulama (Ctrl+Shift+Enter) zato što umesto pritiska samo na taster Enter morate da pritisnete kombinaciju tastera Ctrl+Shift+Enter da biste dovršili formulu.

Zašto koristiti formule niza?

Ako imate iskustva u korišćenju formula u programu Excel, znate da možete izvršiti neke prilično složene operacije. Na primer, možete izračunati ukupan trošak kredita za bilo koji dati broj godina. Formule niza možete koristiti za izvršavanje složenih zadataka, na primer:

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

Brz uvod u nizove i formule niza

Formula niza je formula koja može da izvrši više izračunavanja na nekim stavkama u nizu. Niz možete posmatrati kao red vrednosti, kolonu vrednosti ili kombinaciju redova i kolona vrednosti. Formule niza mogu da vrate ili više rezultata ili jedan rezultat. Na primer, možete da napravite formulu niza u opsegu ćelija i da koristite formulu niza za izračunavanje međuvrednosti kolone ili reda. Takođe možete postaviti formulu niza u jednu ćeliju, a zatim izračunati jedan iznos. Formula niza koja uključuje više ćelija naziva se višećelijska formula, a formula niza u jednoj ćeliji naziva se jednoćelijska formula.

Primeri u sledećem odeljku prikazuju kako da kreirate višećelijske i jednoćelijske formule niza.

Pokušajte!

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

Evo radne sveske koja je ugrađena u pregledač. Iako sadrži uzorke podataka, treba da znate da ne možete da kreirate ili promenite formule niza u ugrađenoj radnoj svesci – potreban vam je program Excel. U ugrađenoj radnoj svesci možete videti odgovore i tekst koji objašnjava kako formula niza funkcioniše, ali da biste zaista razumeli značaj formula niza, potrebno je da vidite radnu svesku u programu Excel.

Kreiranje višećelijske formule niza
  1. Kopirajte celu dolenavedenu tabelu i nalepite je u ćeliju A1 praznog radnog lista u programu Excel.

    Prodavac

    Tip
    automobila

    Broj
    prodatih

    Cena po
    komadu

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

  2. Da biste videli ukupan iznos prodaje kupea i limuzina za svakog prodavca izaberite ćelije E2:E11, unesite formulu =C2:C11*D2:D11, a zatim pritisnite kombinaciju tastera Ctrl+Shift+Enter.

  3. Da biste videli sveukupan iznos prodaje izaberite ćeliju F11, unesite formulu =SUM(C2:C11*D2:D11), a zatim pritisnite kombinaciju tastera Ctrl+Shift+Enter.

Ovu radnu svesku možete da preuzmete klikom na zeleno dugme „Excel“ na crnoj traci na dnu radne sveske. Tada možete da otvorite radnu svesku u programu Excel, izaberete ćelije koje sadrže formule niza i pritisnete kombinaciju tastera Ctrl+Shift+Enter da bi formula proradila.

Ako uz to radite u programu Excel, proverite da li je List1 aktivan, a zatim izaberite ćelije E2:E11. Pritisnite taster F2 i otkucajte formulu =C2:C11*D2:D11 u trenutnoj ćeliji, E2. Ako pritisnete taster Enter, videćete da se formula unosi samo u ćeliju E2 i prikazuje se 165000. Umesto toga, kada otkucate formulu, pritisnite kombinaciju tastera Ctrl+Shift+Enter, a ne samo Enter. Sada ćete videti rezultate u ćelijama E2:E11. Obratite pažnju na to da se formula u polju za formulu prikazuje kao {=C2:C11*D2:D11}. To vam govori da je u pitanju formula niza kao što je prikazano u sledećoj tabeli.

Kada pritisnete kombinaciju tastera Ctrl+Shift+Enter, Excel oko formule stavlja velike zagrade ({ }) i umeće instancu formule u svaku ćeliju 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 F10 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 od C2 do D11), a zatim koristi funkciju SUM da bi zajedno dodao zbirove. 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 jednoćelijska formula u ćeliji G11 potpuno nezavisna od višećelijske formule (formula u ćelijama E2 do E11). Ovo je još jedna prednost korišćenja formula niza – fleksibilnost. Možete da promenite formule u koloni E ili da sasvim izbrišete tu kolonu bez uticaja na formulu u ćeliji G11.

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 meru bezbednosti, možete pritisnuti 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.

Sintaksa formule niza

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 da kada koristite formulu niza, morate pritisnuti 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.

Formule niza predstavljaju veoma efikasan način građenja složene formule. 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).

Unošenje i promena formula niza

Važno    Pritisnite kombinaciju tastera Ctrl+Shift+Enter uvek kada morate da unesete ili uredite 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 celu formulu (na primer, =C2:C11*D2:D11), pritisnite taster Delete, a zatim pritisnite kombinaciju tastera Ctrl+Shift+Enter.

  • Nije moguće umetati prazne ćelije u višećelijsku formulu niza niti brisati ćelije iz nje.

Proširivanje formule niza

Ponekad će vam zatrebati da proširite formulu niza. Proces nije komplikovan, ali obavezno pratite gorenavedena uputstva.

Na ovom radnom listu dodali smo još redova podataka o prodaji, redove od 12 do 17. Sada želimo da ažuriramo formule niza tako da uključuju i te dodatne redove.

Ne zaboravite da ovo treba da radite u programu Excel za računare (nakon što ste preuzeli radnu svesku na računar).

Proširivanje formule niza
  1. Kopirajte ovu celu tabelu u ćeliju A1 u Excel radnom listu.

    Prodavac

    Tip
    automobila

    Broj
    prodatih

    Cena po
    komadu

    Ukupna
    prodaja

    Blagojević

    Limuzina

    5

    33000

    165000

    Kupe

    4

    37000

    148000

    Ivić

    Limuzina

    6

    24000

    144000

    Kupe

    8

    21000

    168000

    Jordanović

    Limuzina

    3

    29000

    87000

    Kupe

    1

    31000

    31000

    Tica

    Limuzina

    9

    24000

    216000

    Kupe

    5

    37000

    185000

    Sandić

    Limuzina

    6

    33000

    198000

    Kupe

    8

    31000

    248000

    Todorović

    Limuzina

    2

    27000

    Kupe

    3

    30000

    Vanić

    Limuzina

    4

    22000

    Kupe

    1

    41000

    Janjić

    Limuzina

    5

    32000

    Kupe

    3

    36000

    Sveukupno

  2. Izaberite ćeliju E18, unesite formulu za sveukupnu vrednost =SUM(C2:C17*D2:D17) u ćeliju A20 i pritisnite kombinaciju tastera Ctrl+Shift+Enter.
    Rezultat bi trebalo da bude 2.131.000.

  3. Izaberite opseg ćelija koji sadrži trenutnu formulu niza (E2:E11), plus prazne ćelije (E12:E17) koje se nalaze pored novih podataka. Drugim rečima, izaberite ćelije E2:E17.

  4. Pritisnite taster F2 da biste se prebacili u režim uređivanja.

  5. U polju za formulu, promenite C11 u C17, promenite D11 u D17, a zatim pritisnite kombinaciju tastera Ctrl+Shift+Enter.
    Excel ažurira formulu u ćelijama od E2 do E11 i postavlja instancu formule u nove ćelije, od E12 do E17.

  6. Otkucajte formulu niza = SUM(C2:C17*D2*D17) u ćeliji F17 tako da upućuje na ćelije od reda 2 do reda 17, a zatim pritisnite kombinaciju tastera Ctrl+Shift+Enter da biste uneli formulu niza.
    Nova sveukupna vrednost bi trebalo da bude 2.131.000 USD.

Nedostaci korišćenja formula niza

Formule niza su sjajne, ali imaju neke nedostatke:

  • Povremeno možete 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 osobe moraju da izmene radne sveske, trebalo bi da izbegavate formule niza ili da proverite da li ti ljudi poznaju formule niza i razumeju kako da ih promene po potrebi.

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

Vrh stranice

Više informacija o konstantama niza

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 ste naučili da treba da pritisnete kombinaciju tastera Ctrl+Shift+Enter kada pravite 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 za unos cele formule.

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 jednoj koloni: {1;2;3;4}. A evo niza od dva reda i četiri kolone: {1,2,3,4;5,6,7,8}. U nizu od dva reda prvi red je 1, 2, 3 i 4, a drugi red 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.

Vrh stranice

Kreiranje jednodimenzionalnih i dvodimenzionalnih konstanti

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

Kreiranje horizontalne konstante

  1. Koristite radnu svesku iz prethodnih primera ili kreirajte novu radnu svesku.

  2. Izaberite ćelije od A1 do E1.

  3. 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 otvorene i zatvorene velike zagrade ({ }).

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

Sintaksa konstante niza

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 zapamtite da pošto dodate konstantu u formulu 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})

Elementi koje možete koristiti u konstanti

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.

Imenovanje konstanti niza

Jedan od najboljih načina za korišćenje konstanti niza je njihovo imenovanje. 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.
    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 i 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.

Rešavanje problema u vezi sa konstantama niza

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 neki na pogrešno mesto, konstanta niza možda neće biti ispravno kreirana ili ć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.

Konstante niza na delu

Sledeći primeri pokazuju nekoliko načina na koje možete staviti konstante niza za upotrebu u formulama niza. Neki od primera koriste funkciju TRANSPOSE radi konvertovanja 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 i 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 i 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 i 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. Otkucajte sledeću formulu i 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.

Vrh stranice

Stavljanje osnovnih formula niza u funkciju

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 taj niz brojeva u opseg ćelija C8: E10 korišćenjem formule niza.
    Od C8 do E10 na radnom listu, trebalo bi da izgleda ovako:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Izaberite opseg ćelija od C1 do E3.

  4. Unesite sledeću formulu i pritisnite kombinaciju tastera Ctrl+Shift+Enter:

    =C8:E10

    U ćelijama od C1 do E3 pojavljuje se niz od 3x3 ćelije sa istim vrednostima kao u ćelijama od C8 do E10.

Kreiranje konstante niza iz postojećih vrednosti

  1. Dok su izabrane ćelije C1:C3, pritisnite taster F2 da biste se prebacili u režim za uređivanje.
    Formula niza trebalo bi da i dalje glasi = C8:E10.

  2. Pritisnite taster F9 da biste konvertovali reference ćelije u vrednosti. Excel konvertuje vrednosti u konstantu niza. Formula bi sada trebalo da glasi ={10,20,30;40,50,60;70,80,90}, kao i C8:E10

  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.

  2. Izaberite ćeliju A9, a zatim pritisnite kombinaciju tastera Ctrl+Shift+Enter da biste videli ukupan broj znakova u ćelijama A2:A6 (66).

  3. Izaberite ćeliju A12, a zatim pritisnite kombinaciju tastera Ctrl+Shift+Enter da biste videli sadržaj najduže od ćelija A2:A6 (ćelija A3).

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)

Sledeća formula koja se koristi u ćeliji A9 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 svake tekstualne niske u svaku ćeliju u opsegu. Funkcija SUM zatim dodaje te vrednosti i prikazuje rezultat (66) u ćeliji koja sadrži formulu, A9.

Pronalaženje n najmanjih vrednosti u opsegu

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

  1. Izaberite ćelije od A16 do A18.
    Ovaj skup ćelija će zadržati rezultate koje je vratila formula niza.

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

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

Vrednosti 400, 475 i 500 se pojavljuju u ćelijama od A16 do A18, tim redom.

Ova formula koristi konstantu niza radi procene funkcije SMALL tri puta i vraća najmanji (1), drugi najmanji (2) i treći najmanji broj (3) članova u nizu koji se nalazi u ćelijama A1:A10. Da biste pronašli više vrednosti, dodajte više argumenata u konstantu i ekvivalentan broj ćelija rezultata u opseg A12:A14. Takođe možete koristiti dodatne funkcije sa ovom formulom, kao što su SUM ili AVERAGE. Na primer:

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

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

  2. U polje za formulu unesite sledeću formulu, a zatim pritisnite kombinaciju tastera Ctrl+Shift+Enter:

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

Vrednosti 3200, 2700 i 2000 pojavljuju se u ćelijama od A1 do A3, tim redom.

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 radnoj svesci za vežbanje, unesite ovu formulu niza u ćelije A5:A14, 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 istražimo formulu koju ste već koristili – =LARGE(A5:A14,ROW(INDIRECT("1:3"))) – počevši od unutrašnje zagrade i radeći od spolja: funkcija INDIRECT vraća skup tekstualnih vrednosti, u ovom slučaju vrednosti od 1 do 3. Funkcija ROW generiše troćelijski niz po kolonama. Funkcija LARGE koristi vrednosti u opsegu ćelija A5:A14 i proverava se tri puta, jednom za svaku referencu koju vrati funkcija ROW. Vrednosti 3200, 2700 i 2000 se vraćaju u troćelijski niz po kolonama. Ako želite da pronađete više vrednosti, dodajete veći opseg ćelija u funkciju INDIRECT.

Na kraju, možete koristiti ovu formulu sa drugim funkcijama, kao što su SUM i AVERAGE.

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

Ova formula funkcioniše samo kada opseg podataka sadrži jednu kolonu ćelija. Na listu List3 unesite sledeću formulu u ćeliju A16 i pritisnite kombinaciju tastera Ctrl+Shift+Enter:

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

U ćeliji A16 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 vraća dužinu svake stavke u opsegu ćelija A6:A9. Funkcija MAX izračunava najveću vrednost među ovim stavkama, koje odgovaraju najvećoj tekstualnoj nisci, koja je u ćeliji A7.

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

i niska se nalazi u ovom nizu:

LEN( A6:A9 )

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 A6:A9 obezbeđuje niz, funkcija MATCH obezbeđuje adresu ćelije i krajnji argument (1) navodi da vrednost dolazi iz prve kolone u nizu.

Vrh stranice

Stavljanje naprednih formula niza u funkciju

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

Vrh stranice

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

Pregled formula

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

×