Kuidas kasutada Excel 2016 viitefunktsioone

Excel 2016 viitefunktsioonid käsunupu Otsi ja viide ripploendis lindi vahekaardil Valemid on loodud spetsiaalselt töölehe lahtriviidete erinevate aspektide käsitlemiseks. See funktsioonide rühm sisaldab:

• ADDRESS, et tagastada lahtriviide töölehe lahtri tekstikirjena

• ALAD, et tagastada väärtuste loendis olevate alade arv ( alad on defineeritud kui külgnevate lahtrite vahemik või üks lahter lahtri viites)

• COLUMN, et tagastada number, mis tähistab lahtri viite veeru asukohta

• COLUMNS, et tagastada viite veergude arv

• FORMULATEXT tekstistringina viidatud valemi tagastamiseks

• GETPIVOTDATA Exceli liigendtabelisse salvestatud andmete tagastamiseks

• HÜPERLINK lingi loomiseks, mis avab mõne muu teie arvutisse, võrku või Internetti salvestatud dokumendi (saate seda teha ka käsuga Lisa → Hüperlink)

• KAUDNE, et tagastada tekstistringiga määratud lahtriviide ja tuua selle lahtri sisu, millele see viitab

• LOOKUP, et tagastada väärtus massiivist

• OFFSET, et tagastada viide lahtrivahemikule, mis on määratud lahtri või lahtrivahemiku ridade ja veergude arvuga

• ROW, et tagastada lahtri viite reanumber

• ROWS, et tagastada ridade arv lahtrivahemikus või massiivis

• RTD reaalajas andmete tagastamiseks serverist, kus töötab programm, mis toetab COM (Component Object Model) automatiseerimist

• TRANSPOSE, et tagastada vertikaalne massiiv horisontaalmassiivina ja vastupidi

Võtke veergudel ja ridadel kõhn

Funktsioonid COLUMNS ja ROWS tagastavad veergude ja ridade arvu konkreetses lahtrivahemikus või massiivis. Näiteks kui teil on arvutustabelis lahtrite vahemik nimega product_mix, saate teada, mitu veergu see sisaldab, sisestades järgmise valemi:

=VEERUD(toote_mix)

Kui soovite teada, mitu rida see vahemik kasutab, sisestage järgmine valem:

=RIAD(toote_mix)

Konkreetse vahemiku lahtrite koguarvu arvutamiseks saate kasutada funktsioone COLUMNS ja ROWS koos. Näiteks kui soovite teada lahtrivahemikus product_mix kasutatud lahtrite täpset arvu, loote funktsioonide COLUMNS ja ROWS abil järgmise lihtsa korrutusvalemi:

=VEERUD(toote_mix)*RIAD(toote_mix)

Ärge ajage segi funktsiooni COLUMNS (mitmus) funktsiooniga COLUMN (ainsuses) ja funktsiooni ROWS (mitmus) funktsiooniga ROW (ainsuses). Funktsioon COLUMN tagastab ainsa argumendina määratud lahtriviite veeru numbri (nagu kasutaks Excel viitesüsteemi R1C1). Samamoodi tagastab funktsioon ROW lahtri viite rea numbri, mille argumendina määrate.

Lahtrivahemike transponeerimine

Funktsioon TRANSPOSE võimaldab teil muuta lahtrivahemiku (või massiivi) orientatsiooni. Selle funktsiooni abil saate üle kanda vertikaalse lahtrivahemiku, kus andmed jooksevad mööda külgnevate veergude ridu allapoole, selliseks, kus andmed jooksevad üle külgnevate ridade veergude ja vastupidi. Funktsiooni TRANSPOSE edukaks kasutamiseks ei pea te mitte ainult valima vahemikku, millel on vastupidine arv veerge ja ridu, vaid peate selle sisestama ka massiivivalemina.

Näiteks kui kasutate funktsiooni TRANSPOSE 2 x 5 lahtrivahemiku (st vahemiku, mis võtab enda alla kaks kõrvuti asetsevat rida ja viis külgnevat veergu) transponeerimiseks, peate valima tühja 5 x 2 lahtrivahemiku (see tähendab , vahemik, mis võtab töölehel viis kõrvuti asetsevat rida ja kaks külgnevat veergu), enne kui kasutate nuppu Lisa funktsioon funktsiooni TRANSPOSE lisamiseks esimesse lahtrisse. Pärast seda, kui olete valinud 2 x 5 lahtrivahemiku, mis sisaldab andmeid, mida soovite üle kanda, dialoogiboksi Funktsiooni argumendid tekstikastis Array, peate selle dialoogiboksi sulgemiseks vajutama Ctrl+Shift+Enter ja sisestama funktsiooni TRANSPOSE. kogu valitud lahtrivahemikku massiivivalemina (sulgudes).

Oletame, et soovite lahtrivahemikku A10:C11 (2 x 3 massiiv) sisestatud andmed üle kanda töölehe tühja lahtrivahemikku E10:F12 (3 x 2 massiiv). Kui vajutate Ctrl + Shift + Enter, et lõpetada massiivivalemi pärast valides lahtrivahemikku A10: C11 nagu massiivi argument, Excel paneb järgmise massiivivalemi iga raku vahemikus:

{=TRANSPOSE(A10:C11)}

Järgmine joonis illustreerib funktsiooni TRANSPOSE kasutamist. Lahtrivahemik B2:C4 sisaldab 3 x 2 massiivi. Selle 3 x 2 massiivi lahtrivahemikus B2:C4 teisendamiseks 2 x 3 massiiviks vahemikus B6:D7 toimige järgmiselt.

Valige töölehel tühi lahtrivahemik B6:D7.

Klõpsake lindi vahekaardil Valemid nuppu Otsi ja viide ja seejärel valige nupu rippmenüüst suvand TRANSPOSE.

Excel lisab valemiribale =TRANSPOSE() ja avab dialoogiboksi Funktsiooni argumendid, kus on valitud Array tekstikast.

Lohistage töölehel läbi lahtrivahemiku B2:C4, nii et massiivi argumendi tekstikast sisaldab B2:C4 ja valemiribal olev valem on nüüd =TRANSPOSE(B2:C4).

Dialoogiboksi Argumentide lisamine sulgemiseks vajutage klahvikombinatsiooni Ctrl+Shift+Enter (ärge klõpsake nuppu OK) ja massiivivalem TRANSPOSE sisestamiseks lahtrivahemikku B6:D7, nagu siin näidatud.

Funktsiooni TRANSPOSE kasutamine lihtsa massiivi orientatsiooni muutmiseks.

Klõpsates nuppu OK dialoogiboksis Funktsiooni argumendid, lisatakse funktsioon TRANSPOSE praeguse lahtrivaliku aktiivsesse lahtrisse. Seda tehes tagastatakse #VALUE! lahtri veaväärtus. Peate meeles pidama, et dialoogiboksi sulgemiseks ja valemi sisestamiseks kogu lahtrivahemikku peate vajutama Ctrl+Shift+Enter.

Kui soovite ainult ridade ja veergude pealkirju või lihtsat andmetabelit transponeerida, ei pea te massiivivalemi loomisel funktsiooni TRANSPOSE abil läbi tegema. Lihtsalt kopeerige ülekantavate lahtrite vahemik lindi vahekaardi Avaleht käsunupuga Kopeeri. Asetage lahtri kursor esimesse tühja lahtrisse, kuhu transponeeritud vahemik tuleb kleepida, enne kui klõpsate käsunupu Kleebi rippmenüüs suvandit Transponeeri.