Kako koristiti referentne funkcije u programu Excel 2016

Referentne funkcije programa Excel 2016 na padajućem popisu gumba za naredbe Lookup & Reference na kartici Formule na vrpci osmišljene su za posebno rješavanje različitih aspekata referenci ćelija u radnom listu. Ova grupa funkcija uključuje:

• ADRESA za vraćanje reference ćelije kao unos teksta u ćeliju radnog lista

• PODRUČJA za vraćanje broja područja na popisu vrijednosti ( područja su definirana kao raspon susjednih ćelija ili jedna ćelija u referenci ćelije)

• COLUMN za vraćanje broja koji predstavlja položaj stupca reference ćelije

• COLUMNS za vraćanje broja stupaca u referenci

• FORMULATEXT za vraćanje formule referencirane kao tekstualni niz

• GETPIVOTDATA za vraćanje podataka pohranjenih u Excel zaokretnoj tablici

• HYPERLINK za stvaranje veze koja otvara drugi dokument pohranjen na vašem računalu, mreži ili Internetu (to možete učiniti i naredbom Insert→Hyperlink)

• INDIRECT za vraćanje reference ćelije određene tekstualnim nizom i dovođenje sadržaja u ćeliju na koju se odnosi na tu ćeliju

• LOOKUP za vraćanje vrijednosti iz niza

• OFFSET za vraćanje reference na raspon ćelija koji je određen brojem redaka i stupaca iz ćelije ili raspona ćelija

• ROW za vraćanje broja retka reference ćelije

• ROWS za vraćanje broja redaka u rasponu ćelija ili nizu

• RTD za vraćanje podataka u stvarnom vremenu s poslužitelja koji izvodi program koji podržava automatizaciju COM (Component Object Model)

• TRANSPOSE da biste vratili okomiti niz kao horizontalni niz i obrnuto

Nabavite mršav na stupce i redove

Funkcije COLUMNS i ROWS vraćaju broj stupaca i redaka u određenom rasponu ćelija ili nizu. Na primjer, ako imate raspon ćelija u proračunskoj tablici pod nazivom product_mix, možete saznati koliko stupaca sadrži unosom ove formule:

=COLUMNS(miks_proizvoda)

Ako želite znati koliko redaka koristi ovaj raspon, tada unesite ovu formulu:

=REDOVI(mješavina_proizvoda)

Funkcije COLUMNS i ROWS možete koristiti zajedno za izračunavanje ukupnog broja ćelija u određenom rasponu. Na primjer, ako želite znati točan broj ćelija korištenih u rasponu ćelija product_mix, izradite sljedeću jednostavnu formulu za množenje pomoću funkcija COLUMNS i ROWS:

=COLUMNS(miks_proizvoda)*REDOVI(mješavina_proizvoda)

Nemojte brkati funkciju COLUMNS (množina) s funkcijom COLUMN (singular) i ROWS (množina) s funkcijom ROW (singular). Funkcija COLUMN vraća broj stupca (kao da Excel koristi referentni sustav R1C1) za referencu ćelije koju navedete kao jedini argument. Isto tako, funkcija ROW vraća broj retka za referencu ćelije koju navedete kao argument.

Transponiranje raspona stanica

Funkcija TRANSPOSE omogućuje vam promjenu orijentacije raspona ćelija (ili niza). Ovu funkciju možete koristiti za transponiranje okomitog raspona ćelija u kojem se podaci spuštaju niz retke susjednih stupaca u onaj gdje se podaci kreću preko stupaca susjednih redaka i obrnuto. Da biste uspješno koristili funkciju TRANSPOSE, ne samo da morate odabrati raspon koji ima suprotan broj stupaca i redaka, već ga morate unijeti i kao formulu polja.

Na primjer, ako koristite funkciju TRANSPOSE za transponiranje raspona ćelija 2 x 5 (tj. raspona koji zauzima dva susjedna retka i pet susjednih stupaca), morate odabrati prazan raspon ćelija 5 x 2 (tj. , raspon koji zauzima pet susjednih redaka i dva susjedna stupca) na radnom listu prije nego što upotrijebite gumb Umetni funkciju za umetanje funkcije TRANSPOSE u prvu ćeliju. Zatim, nakon što odaberete raspon ćelija 2 x 5 koji sadrži podatke koje želite transponirati u tekstualni okvir Polje dijaloškog okvira Argumenti funkcije, trebate pritisnuti Ctrl+Shift+Enter da zatvorite ovaj dijaloški okvir i unesete funkciju TRANSPOSE u cijeli odabrani raspon ćelija kao formula polja (zatvorena u vitičaste zagrade).

Pretpostavimo da želite transponirati podatke unesene u raspon ćelija A10:C11 (niz 2 x 3) u prazni raspon ćelija E10:F12 (niz 3 x 2) radnog lista. Kada pritisnete Ctrl+Shift+Enter da dovršite formulu polja, nakon odabira raspona ćelija A10:C11 kao argumenta polja , Excel stavlja sljedeću formulu polja u svaku ćeliju raspona:

{=TRANSPOSE(A10:C11)}

Sljedeća slika ilustrira korištenje funkcije TRANSPOSE. Raspon ćelija B2:C4 sadrži niz 3 x 2. Za pretvaranje ovog polja 3 x 2 u rasponu ćelija B2:C4 u polje 2 x 3 u rasponu B6:D7, slijedite ove korake:

Odaberite prazni raspon ćelija B6:D7 na radnom listu.

Kliknite gumb naredbe Lookup & Reference na kartici Formule vrpce, a zatim odaberite opciju TRANSPOSE s padajućeg izbornika gumba.

Excel umeće =TRANSPOSE() na traku formule i otvara dijaloški okvir Argumenti funkcije u kojem je odabran tekstualni okvir argumenta niza.

Povucite kroz raspon ćelija B2:C4 na radnom listu tako da tekstni okvir argumenta polja sadrži B2:C4, a formula na traci formule sada glasi =TRANSPOSE(B2:C4).

Pritisnite Ctrl+Shift+Enter da zatvorite dijaloški okvir Umetanje argumenata (nemojte kliknuti OK) i umetnuti formulu polja TRANSPOSE u raspon ćelija B6:D7 kao što je ovdje prikazano.

Korištenje funkcije TRANSPOSE za promjenu orijentacije jednostavnog niza.

Klikom na gumb U redu u dijaloškom okviru Argumenti funkcije umeće se funkcija TRANSPOSE u aktivnu ćeliju trenutnog odabira ćelije. Ovim se vraća #VRIJEDNOST! vrijednost pogreške u ćeliju. Ne zaboravite pritisnuti Ctrl+Shift+Enter kako biste zatvorili dijaloški okvir i stavili formulu u cijeli raspon ćelija.

Ako sve što želite učiniti je transponirati naslove redaka i stupaca ili jednostavnu tablicu podataka, ne morate prolaziti kroz rigmarole stvaranja formule polja pomoću funkcije TRANSPOSE. Jednostavno kopirajte raspon ćelija za transponiranje pomoću gumba naredbe Kopiraj na kartici Početak na vrpci. Postavite pokazivač ćelije u prvu praznu ćeliju u koju treba zalijepiti transponirani raspon prije nego kliknete opciju Transponiranje na padajućem izborniku gumba Zalijepi.