Uputstva i primeri formula niza

Višećelijski i jednoćelijski nizovi

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
  

• Ovde izračunavamo ukupnu prodaju kupea i limuzina za svakog prodavca unošenjem =F10:F19*G10:G19 u ćeliju H10.
  Kada pritisnete Enter, videćete da se rezultati prelivaju na ćelije H10:H19. Obratite pažnju na to da je opseg prosipanja istaknut ivicom kada odaberete bilo koju ćeliju u opsegu prosipanja. Možda ćete primetiti da su formule u ćelijama H10:H19 sive. Oni su tu samo za referencu, pa ako želite da prilagodite formulu, moraćete da izaberete ćeliju H10, u kojoj živi glavna formula.

• Jednoćelijska formula niza
  
  U ćeliju H20 primera radne sveske 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 od F10 do G19), 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 H20 potpuno nezavisna od višećelijske formule (formula u ćelijama H10 do H19). Ovo je još jedna prednost korišćenja formula niza – fleksibilnost. Možete da promenite formule u koloni H ili da sasvim izbrišete tu kolonu bez uticaja na formulu u ćeliji H20. Takođe može biti dobra praksa imati ovakve nezavisne ukupne iznose, jer pomaže u potvrđivanju tačnosti vaših rezultata.

• Dinamičke formule niza takođe pružaju ove prednosti:

  • Doslednost Ako kliknete na bilo koju ćeliju iz ćelije H10, vidite istu formulu. Ta doslednost može da pomogne u osiguravanju veće preciznosti.
  • Bezbednost Ne možete zameniti komponentu višećelijske formule niza. 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 ćeliju 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. Da ste koristili standardne formule kao što su =F10*G10, F11*G11, F12*G12, itd., koristili biste 11 različitih formula za izračunavanje istih rezultata. To nije problem, ali šta ako imate ukupno hiljade redova? Tada to može napraviti veliku razliku.
  • Efikasnost Funkcije niza mogu biti efikasan način za izgradnju složenih formula. Formula niza =SUM(F10:F19*G10:G19) je ista kao i ova: =SUM(F10*G10,F11*G11,F12*G12,F 13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
  • Prelivanje Dinamičke formule niza automatski će se ukloniti u izlazni opseg. Ako su vaši izvorni podaci u Excel tabeli, tada će vaše formule dinamičkih nizova automatski promeniti veličinu dok dodajete ili uklanjate podatke.
  • #PRELIVANJE! greška Dinamički nizovi su uveli grešku #SPILL!, koja ukazuje na to da je predviđeni opseg udaljenih ćelija blokiran iz nekog razloga. Kada rešite blokadu, formula će se automatski razliti.

Kreirajte jednodimenzionalne konstante 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} = 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, stavke u svakom redu razdvajate zarezima, a svaki red tačkom i zarezom.

Sledeće procedure će vam pružiti vežbu za pravljenje horizontalnih, vertikalnih i dvodimenzionalnih konstanti. Prikazaćemo primere koji koriste funkciju SEQUENCE za automatsko 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 bilo koju praznu ćeliju i unesite =SEQUENCE(1,5). Funkcija SEQUENCE gradi niz od 1 reda sa 5 kolona isti kao ={1,2,3,4,5}. Prikazuje se sledeći rezultat:
  
• Kreiranje vertikalne konstante
  Izaberite bilo koju praznu ćeliju koja ima prostor ispod nje i unesite =SEQUENCE(5)ili ={1;2;3;4;5}. Prikazuje se sledeći rezultat:
  
• Kreiranje dvodimenzionalne konstante
  Izaberite bilo koju praznu ćeliju koja ima prostor sa desne strane i unesite =SEQUENCE(3,4) Vidite sledeći rezultat:
  
  Takođe možete uneti: ili ={1\2\3\4;5\6\7\8;9\10\11\12}, ali želećete da obratite pažnju gde stavljate tačku i zarez u odnosu na zareze.
  Kao što vidite, opcija SEQUENCE nudi značajne prednosti u odnosu na ručni unos konstantnih vrednosti niza. Ona vam prvenstveno štedi vreme, ali takođe može da smanji greške ručnim unosom. Takođe je lakše čitati, posebno jer je tačku i zarez teško razlikovati od separatora zareza.

Sintaksa konstante niza

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

U ćeliju D9 uneli smo =SEQUENCE (1,5,3,1), ali takođe možete uneti 3, 4, 5, 6 i 7 u ćelije A9: H9. Nema ničeg posebnog u vezi sa tim određenim odabirom brojeva, mi smo samo izabrali nešto drugo osim 1-5 za razlikovanje.

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

Funkcija SEQUENCE gradi rezultat konstante {1,2,3,4,5}niza. Budući da Excel prvo izvršava operacije nad izrazima zatvorenim u zagradama, sledeća dva elementa koja dolaze u obzir su vrednosti ćelija u D9: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,H9*5), ili =SUM(3*1,4*2,5*3,6*4,7*5)

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

Da biste izbegli upotrebu uskladištenog niza i operaciju zadržali u potpunosti u memoriji, možete je zameniti 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 konstantama niza

• Konstante niza mogu sadržati brojeve, tekst, logičke vrednosti (kao što su TRUE i FALSE) i vrednosti grešaka poput # N/A. Brojeve možete koristiti u formatu celog broja, decimalnog broja i naučnom formatu. Ako uvrstite tekst, morate ga okružiti navodnicima („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.

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:

Idite do formule Definisana>imena>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 sada trebalo da izgleda ovako:

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

Prikazuje se sledeći rezultat:

Ako želite da se rezultati prelivaju vertikalno, a ne horizontalno, možete da koristite =TRANSPOSE(Kvartal1).

Ako želite da prikažete listu od 12 meseci, kakvu biste mogli da koristite prilikom sastavljanja finansijskog izveštaja, možete je bazirati na tekućoj godini pomoću funkcije SEQUENCE. Zgodna stvar ove funkcije je da, iako se prikazuje samo mesec, iza nje stoji važeći datum koji možete koristiti u drugim proračunima. Ove primere ćete pronaći na radnim listovima sa nazivom konstante niza i radnim listovima brzog uzorka skupa podataka u primeru radne sveske.

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

Ovo koristi funkciju DATE za kreiranje datuma na osnovu tekuće godine, SEQUENCE kreira konstantu niza od 1 do 12 od januara do decembra, a zatim funkcija TEXT pretvara format prikaza u „mmm“ (januar, februar, mart itd.). Ako želite da prikažete puni naziv meseca, kao što je januar, koristili biste „mmmm“.

Kada koristite imenovanu konstantu kao formulu niza, ne zaboravite da unesete znak jednakosti, kao u =Kvartal1, a 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 koristiti kombinacije funkcija, teksta i brojeva. Sve zavisi od toga koliko kreativno želite da dobijete.

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
  Unesite =SEKVENCA(1,12)*2ili ={1\2\3\4;5\6\7\8;9\10\11\12}*2
  Takođe možete podeliti sa (/), dodati sa (+) i oduzeti sa (-).
• Izračunavanje kvadratnog korena za stavke u nizu
  Unesite =SEKVENCA(1,12)^2ili ={1\2\3\4;5\6\7\8;9\10\11\12}^2
• Pronađite kvadratni koren kvadratnih stavki 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(SEKVENCA(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.

Stavljanje osnovnih formula niza u funkciju

Ovaj odeljak obezbeđuje primere osnovnih formula niza.

• Kreiranje niza iz postojećih vrednosti
  Sledeći primer objašnjava kako koristiti formule niza za kreiranje novog niza od postojećeg niza.
  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 { (otvorenu veliku zagradu) pre nego što otkucate 10 i } (zatvorenu veliku zagradu) pošto otkucate 180, jer pravite niz brojeva.
  Dalje, unesite =D9#ili =D9:I11 u praznu ćeliju. Pojavljuje se niz od 3 x 6 ćelija sa istim vrednostima koje vidite u ćelijama D9:D11. Znak # naziva se operator razlivenog opsega i to je način referenciranja programa Excel čitavog opsega niza, umesto da ga treba otkucati.
  

• Kreiranje konstante niza iz postojećih vrednosti
  Možete uzeti rezultate formule prolivenog niza i pretvoriti ih u njegove sastavne delove. Izaberite ćeliju D9, a zatim pritisnite taster F2 da biste se prebacili u režim uređivanja. Zatim pritisnite taster F9 da biste konvertovali reference ćelije u vrednosti, koje Excel zatim konvertuje u konstantu niza. Kada pritisnete taster 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 da u opsegu ćelija izbrojite broj znakova. Ovo uključuje razmake.
  
  =SUM(LEN(C9:C13))
  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). Ako ste želeli da dobijete prosečan broj znakova, mogli biste 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 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 vraća dužinu svake stavke u opsegu ćelija D2:D6. Funkcija MAX izračunava najveću vrednost među tim stavkama, koja odgovara najdužem tekstualnom nizu, koji 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 tipa podudaranja u ovom slučaju je 0. Tip podudaranja može biti vrednosti 1, 0 ili -1.

  • 1 – vraća najveću vrednost koja je manja ili jednaka vrednosti za pretraživanje.
  • 0 – vraća prvu vrednost u potpunosti jednaku vrednosti za pretraživanje
  • 1 – vraća 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 C9:C13 pruža niz, funkcija MATCH pruža adresu ćelije, a završni argument (1) navodi da vrednost dolazi iz prve kolone u nizu.
  Ako želite da dobijete sadržaj najmanjeg tekstualnog niza, zamenili biste MAX u gornjem primeru sa MIN.

• Pronalaženje n najmanjih vrednosti u opsegu
  Ovaj primer pokazuje kako pronaći tri najmanje vrednosti u rasponu ćelija, gde je stvoren niz podataka uzorka u ćelijama B9:B18 sa: =INT(RANDARRAY(10,1)*100). Imajte na umu da je RANDARRAY isparljiva funkcija, pa ćete svaki put kada Excel izračuna izračunati novi skup slučajnih brojeva.
  
  Unesite =SMALL(B9#,SEKVENCA(D9), =SMALL(B9:B18,{1;2;3})
  Ova formula koristi konstantu niza da tri puta proceni funkciju SMALL 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 povećati vrednost u funkciji SEQUENCE ili dodati više argumenata konstanti. Takođe možete koristiti dodatne funkcije sa ovom formulom, kao što su SUM ili AVERAGE. 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će vrednosti u opsegu, možete zameniti funkciju SMALL funkcijom LARGE. Pored toga, sledeći primeri koriste funkcije ROW i INDIRECT.
  Unesite =LARGE(B9#,ROW(INDIRECT("1:3"))), ili =LARGE(B9:B18,ROW(INDIRECT("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 prazan tekst 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 reda, a formula sada 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 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. Isto tako lako možete da koristite funkciju SEQUENCE:
  =SEQUENCE(10)
  Ispitajmo formulu koju ste ranije koristili – =LARGE(B9#,ROW(INDIRECT("1:3"))) – počevši od unutrašnjih zagrada i radeći prema vani: funkcija INDIRECT vraća skup tekstualnih vrednosti, u ovom slučaju vrednosti od 1 do 3. Funkcija ROW za to vreme generiše niz sa tri ćelije. Funkcija LARGE koristi vrednosti u opsegu ćelija B9:B18 i procenjuje se tri puta, jednom za svaku referencu vraćenu funkcijom ROW. Ako želite da pronađete više vrednosti, dodajete veći opseg ćelija u funkciju INDIRECT. Na kraju, kao i kod MALIH primera, ovu formulu možete koristiti i sa drugim funkcijama, kao što su SUM i AVERAGE.

Rešavanje grešaka

• 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 #VALUE! ili #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 kao prethodna formula, 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 sumira samo pozitivne cele brojeve u opsegu nazvanom Prodaja, koji 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((Prodaja>0)*(Prodaja<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 sabrati vrednosti koje su veće od 0 ILI manje od 2500:

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

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.

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 je MyData raspon od 3 reda sa 5 kolona, YourData takođe moraju biti 3 reda sa 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čne primere naći ćete u primerku radne sveske o radnom listu Razlike između skupova podataka.

Višećelijske i jednoćelijske formule

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 je u ćeliju A1 praznog radnog lista.

1. Da biste videli ukupnu prodaju kupea i limuzina za svakog prodavca, izaberite ćelije E2:E11, unesite formulu =C2:C11*D2:D11, a zatim pritisnite Ctrl+Shift+Enter.
2. Da biste videli sveukupan iznos prodaje 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 zagradama ({ }) i ubacuje primerak 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}. 

• 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 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 D13 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 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 meru bezbednosti morate da pritisnete Ctrl+Shift+Enter da biste potvrdili bilo kakvu 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.

Funkcije niza mogu biti efikasan način za izgradnju 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).

Unošenje i promena formula niza

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.
• 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. Izaberite prvu ćeliju u postojećem opsegu niza i nastavite dok ne odaberete čitav 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 nakon što prilagodite opseg formule. Ključ je da izaberete ceo opseg, počevši od gornje leve ćelije u nizu. Gornja leva ćelija je ona koja se uređuje.

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 drugi ljudi treba da izmene vaše radne sveske, trebalo bi da izbegavate formule niza ili da se uverite da ti ljudi znaju za bilo koje formule niza i da razumeju kako da ih promene, ako je potrebno.
• U zavisnosti od brzine i memorije obrade računara, velike formule niza mogu usporiti izračunavanja.

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.

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. U praznom radnom listu izaberite ćelije od A1 do E1.
2. U polje za formulu unesite sledeću formulu i pritisnite kombinaciju tastera Ctrl+Shift+Enter:
   ={1,2,3,4,5}
   U ovom slučaju, trebalo bi da otkucate otvorenu i zatvorenu vitičastu zagradu ({ }), a Excel će dodati drugi skup umesto vas.
   Prikazuje se sledeći rezultat.
   

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.
   

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 i pritisnite kombinaciju tastera Ctrl+Shift+Enter:
   ={1\2\3\4;5\6\7\8;9\10\11\12}
   Vidite sledeći rezultat:
   

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

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

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 ih stavite na pogrešno mesto, konstanta niza možda neće biti pravilno 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. Unesite 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.

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}
   Uverite se da ste otkucali { (otvorena vitičasta zagrada) pre nego što otkucate 10 i } (zatvorena vitičasta zagrada) nakon što otkucate 90, zato što 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 u traku sa formulama 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. 
2. Pritisnite taster F9 da biste konvertovali reference ćelije u vrednosti. Excel konvertuje vrednosti u konstantu niza. Formula bi sada 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).

Sledeća formula koja se koristi 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 svake tekstualne niske u svaku ćeliju u opsegu. Zatim 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 ćelije A1:A11.
2. Izaberite ćelije od C1 do C3. Ovaj skup ćelija će zadržati rezultate koje je vratila formula niza.
3. Unesite sledeću formulu i pritisnite kombinaciju tastera Ctrl+Shift+Enter:
   =SMALL(A1:A11,{1;2;3})

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. 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 od D1 do D3.
2. U polje za formulu unesite sledeću formulu, a zatim pritisnite kombinaciju tastera Ctrl+Shift+Enter:
   =LARGE(A1:A10,ROW(INDIRECT("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 radnoj svesci za vežbanje, unesite ovu formulu niza, a zatim pritisnite 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.

Pogledajmo formulu koju ste ranije koristili – =LARGE(A5:A14,ROW(INDIRECT("1:3"))) – počevši od unutrašnjih zagrada i radeći prema vani: funkcija INDIRECT vraća skup tekstualnih vrednosti, u ovom slučaju vrednosti od 1 do 3. Funkcija ROW zauzvrat generiše niz od tri ćelije po kolonama. Funkcija LARGE koristi vrednosti u opsegu ćelija A5:A14 i procenjuje se tri puta, jednom za svaku referencu koju vraća funkcija ROW. Vrednosti 3200, 2700 i 2000 vraćaju se u niz tri ćelije po koloni. Ako želite da pronađete više vrednosti, dodajete veći opseg ćelija u funkciju INDIRECT.

Kao i u ranijim primerima, ovu formulu možete koristiti i sa drugim funkcijama, kao što su SUM i AVERAGE.

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

Vratite se na raniji primer tekstualnog niza, u praznu ćeliju unesite sledeću formulu i pritisnite 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 vraća dužinu svake stavke u opsegu ćelija A2:A6. Funkcija MAX izračunava najveću vrednost među tim stavkama, koja odgovara najdužem tekstualnom nizu, koji 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.

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 završni argument (1) navodi da vrednost dolazi iz prve kolone u nizu.

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((Sales<5)+(Sales>15),Sales))

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