Kako uporabljati referenčne funkcije v Excelu 2016

Referenčne funkcije programa Excel 2016 na spustnem seznamu ukaznega gumba Iskanje in sklicevanje na zavihku Formule na traku so zasnovane tako, da obravnavajo posebej različne vidike sklicevanj na celice na delovnem listu. Ta skupina funkcij vključuje:

• NASLOV, da vrnete sklic na celico kot vnos besedila v celico delovnega lista

• OBMOČJA za vrnitev števila območij na seznamu vrednosti ( območja so opredeljena kot obseg sosednjih celic ali ena celica v referenci celice)

• COLUMN za vrnitev številke, ki predstavlja položaj stolpca sklicevanja na celico

• COLUMNS za vrnitev števila stolpcev v sklicu

• FORMULATEXT za vrnitev formule, ki je navedena kot besedilni niz

• GETPIVOTDATA za vrnitev podatkov, shranjenih v Excelovi vrtilni tabeli

• HYPERLINK, da ustvarite povezavo, ki odpre drug dokument, shranjen v vašem računalniku, omrežju ali internetu (to lahko storite tudi z ukazom Vstavi→Hiperpovezava)

• INDIRECT vrne sklic na celico, določeno z besedilnim nizom, in pripelje vsebino v celico, na katero se nanaša na to celico

• LOOKUP za vrnitev vrednosti iz matrike

• OFFSET, da vrnete sklic na obseg celic, ki je določen s številom vrstic in stolpcev iz celice ali obsega celic

• ROW za vrnitev številke vrstice sklicevanja na celico

• ROWS za vrnitev števila vrstic v obsegu celic ali matriki

• RTD za vračanje podatkov v realnem času iz strežnika, ki izvaja program, ki podpira avtomatizacijo COM (Component Object Model)

• TRANSPOSE, da vrnete navpični niz kot vodoravni niz in obratno

Naredite skinny na stolpce in vrstice

Funkciji COLUMNS in ROWS vrneta število stolpcev in vrstic v določenem obsegu celic ali matriki. Na primer, če imate obseg celic v preglednici z imenom product_mix, lahko ugotovite, koliko stolpcev vsebuje, tako da vnesete to formulo:

=STOPCI(mešanica_izdelkov)

Če želite vedeti, koliko vrstic uporablja ta obseg, vnesite to formulo:

=ROWS(miks_izdelkov)

Funkcije COLUMNS in ROWS lahko uporabite skupaj, da izračunate skupno število celic v določenem obsegu. Na primer, če želite izvedeti natančno število celic, uporabljenih v obsegu celic product_mix, ustvarite naslednjo preprosto formulo za množenje z uporabo funkcij COLUMNS in ROWS:

=STOPCI(mešanica_izdelkov)*VRSTICI(mešanica_izdelkov)

Ne zamenjujte funkcije COLUMNS (množina) s funkcijo COLUMN (ednina) in funkcije ROWS (množina) s funkcijo ROW (ednina). Funkcija COLUMN vrne številko stolpca (kot da bi Excel uporabljal referenčni sistem R1C1) za referenco celice, ki jo podate kot edini argument. Podobno funkcija ROW vrne številko vrstice za sklic na celico, ki jo podate kot njen argument.

Transponiranje obsegov celic

Funkcija TRANSPOSE vam omogoča spreminjanje orientacije obsega celic (ali matrike). To funkcijo lahko uporabite za transponiranje navpičnega obsega celic, kjer podatki tečejo po vrsticah sosednjih stolpcev, v tistega, kjer podatki tečejo čez stolpce sosednjih vrstic in obratno. Za uspešno uporabo funkcije TRANSPOSE ne morate samo izbrati obsega, ki ima nasprotno število stolpcev in vrstic, temveč ga morate vnesti tudi kot formulo matrike.

Če na primer uporabljate funkcijo TRANSPOSE za transponiranje obsega celic 2 x 5 (to je obseg, ki zavzema dve sosednji vrstici in pet sosednjih stolpcev), morate izbrati prazen obseg celic 5 x 2 (tj. , obseg, ki zavzame pet sosednjih vrstic in dva sosednja stolpca) na delovnem listu, preden uporabite gumb Vstavi funkcijo za vstavljanje funkcije TRANSPOSE v prvo celico. Potem, ko izberete obseg celic 2 x 5, ki vsebuje podatke, ki jih želite prenesti v polje z besedilom Array pogovornega okna Argumenti funkcije, morate pritisniti Ctrl+Shift+Enter, da zaprete to pogovorno okno in vnesete funkcijo TRANSPOSE v celoten izbrani obseg celic kot formula matrike (zaprto v zavitih oklepajih).

Recimo, da želite prenesti podatke, vnesene v obseg celic A10:C11 (matrika 2 x 3), v prazno območje celic E10:F12 (matrika 3 x 2) delovnega lista. Ko pritisnete Ctrl+Shift+Enter, da dokončate formulo matrike, potem ko izberete obseg celic A10:C11 kot argument matrike , Excel v vsako celico obsega postavi naslednjo formulo matrike:

{=PRENOSI (A10:C11)}

Naslednja slika prikazuje uporabo funkcije TRANSPOSE. Obseg celic B2:C4 vsebuje niz 3 x 2. Če želite pretvoriti to matriko 3 x 2 v obsegu celic B2:C4 v matriko 2 x 3 v obsegu B6:D7, sledite tem korakom:

Izberite prazen obseg celic B6:D7 na delovnem listu.

Kliknite ukazni gumb Iskanje in sklicevanje na zavihku Formule traku in nato v spustnem meniju gumba izberite možnost TRANSPOSE.

Excel vstavi =TRANSPOSE() v vrstico s formulo in odpre pogovorno okno Argumenti funkcije, kjer je izbrano besedilno polje argumenta matrike.

Povlecite skozi obseg celic B2:C4 na delovnem listu, tako da besedilno polje Array Array vsebuje B2:C4, formula v vrstici Formule pa se zdaj glasi =TRANSPOSE(B2:C4).

Pritisnite Ctrl+Shift+Enter, da zaprete pogovorno okno Vstavi argumente (ne kliknite V redu) in da vstavite formulo matrike TRANSPOSE v obseg celic B6:D7, kot je prikazano tukaj.

Uporaba funkcije TRANSPOSE za spremembo orientacije preprostega niza.

S klikom na gumb V redu v pogovornem oknu Argumenti funkcije se funkcija TRANSPOSE vstavi v aktivno celico trenutnega izbora celice. S tem se vrne #VALUE! vrednost napake v celico. Ne pozabite pritisniti Ctrl+Shift+Enter, da zaprete pogovorno okno in vnesete formulo v celoten obseg celic.

Če želite samo prenesti naslove vrstic in stolpcev ali preprosto tabelo s podatki, vam ni treba iti skozi zapletenost ustvarjanja formule matrike s funkcijo TRANSPOSE. Preprosto kopirajte obseg celic, ki jih želite transponirati, z ukaznim gumbom Kopiraj na zavihku Domov na traku. Postavite kazalec celice v prvo prazno celico, kamor želite prilepiti transponirani obseg, preden kliknete možnost Transpose v spustnem meniju ukaznega gumba Prilepi.