Si të përdorni funksionet e referencës në Excel 2016

Funksionet e referencës së Excel 2016 në listën rënëse të butonit të komandës Kërko & Referenca në skedën Formulat e Shiritit janë krijuar për t'u marrë në mënyrë specifike me aspekte të ndryshme të referencave të qelizave në fletën e punës. Ky grup funksionesh përfshin:

• ADRESA për të kthyer një referencë qelize si një hyrje teksti në një qelizë të fletës së punës

• AREAS për të kthyer numrin e zonave në një listë vlerash ( zonat përcaktohen si një varg qelizash të afërta ose një qelizë e vetme në referencën e qelizës)

• COLUMN për të kthyer numrin që përfaqëson pozicionin e kolonës së një referencë qelize

• COLUMNS për të kthyer numrin e kolonave në një referencë

• FORMULATEXT për të kthyer formulën e referuar si një varg teksti

• GETPIVOTDATA për të kthyer të dhënat e ruajtura në një tabelë kryesore të Excel

• HYPERLINK për të krijuar një lidhje që hap një dokument tjetër të ruajtur në kompjuterin, rrjetin ose internetin tuaj (këtë mund ta bëni edhe me komandën Insert→Hyperlink)

• INDIRECT për të kthyer një referencë qelize të specifikuar nga një varg teksti dhe për të sjellë përmbajtjen në qelizën të cilës i referohet asaj qelize

• LOOKUP për të kthyer një vlerë nga një grup

• OFFSET për të kthyer një referencë në një varg qelizash që specifikohet nga numri i rreshtave dhe kolonave nga një qelizë ose një gamë qelizash

• ROW për të kthyer numrin e rreshtit të një referencë qelize

• ROWS për të kthyer numrin e rreshtave në një gamë ose grup qelizash

• RTD për të kthyer të dhënat në kohë reale nga një server që ekzekuton një program që mbështet automatizimin COM (Component Object Model)

• TRANSPOSE për të kthyer një grup vertikal si një grup horizontal dhe anasjelltas

Merrni të dobëtit në kolona dhe rreshta

Funksionet COLUMNS dhe ROWS kthejnë numrin e kolonave dhe rreshtave në një gamë ose grup të caktuar qelizash. Për shembull, nëse keni një gamë qelizash në spreadsheet me emrin product_mix, mund të zbuloni se sa kolona përmban ajo duke futur këtë formulë:

=COLUMNS(produkt_përzierje)

Nëse dëshironi të dini se sa rreshta përdor ky varg, atëherë shkruani këtë formulë:

=ROWS(përzierje_produkti)

Mund të përdorni funksionet COLUMNS dhe ROWS së bashku për të llogaritur numrin total të qelizave në një gamë të caktuar. Për shembull, nëse doni të dini numrin e saktë të qelizave të përdorura në gamën e qelizave product_mix, krijoni formulën e mëposhtme të thjeshtë të shumëzimit duke përdorur funksionet COLUMNS dhe ROWS:

=COLUMNS(përzierje_produkti)*ROWS(përzierje_produkti)

Mos e ngatërroni funksionin COLUMNS (shumës) me funksionin COLUMN (njëjës) dhe funksionin ROWS (shumës) me funksionin RRESHT (njëjës). Funksioni COLUMN kthen numrin e kolonës (sikur Excel të përdorte sistemin e referencës R1C1) për referencën e qelizës që specifikoni si argumentin e saj të vetëm. Po kështu, funksioni ROW kthen numrin e rreshtit për referencën e qelizës që ju specifikoni si argument të saj.

Transpozimi i gamës së qelizave

Funksioni TRANSPOSE ju mundëson të ndryshoni orientimin e një vargu qelizash (ose një grupi). Ju mund ta përdorni këtë funksion për të transpozuar një varg qelizash vertikale ku të dhënat shkojnë poshtë rreshtave të kolonave ngjitur në një ku të dhënat kalojnë nëpër kolonat e rreshtave ngjitur dhe anasjelltas. Për të përdorur me sukses funksionin TRANSPOSE, jo vetëm që duhet të zgjidhni një gamë që ka një numër të kundërt kolonash dhe rreshtash, por gjithashtu duhet ta futni atë si një formulë grupi.

Për shembull, nëse po përdorni funksionin TRANSPOSE për të transpozuar një gamë qelizash 2 x 5 (d.m.th., një diapazon që përfshin dy rreshta ngjitur dhe pesë kolona ngjitur), duhet të zgjidhni një gamë qelizash 5 x 2 bosh (d.m.th. , një varg që merr pesë rreshta ngjitur dhe dy kolona ngjitur) në fletën e punës përpara se të përdorni butonin Fut Funksionin për të futur funksionin TRANSPOSE në qelizën e parë. Më pas, pasi të zgjidhni gamën e qelizave 2 x 5 që përmban të dhënat që dëshironi të transpozoni në kutinë e tekstit Array të kutisë së dialogut Argumentet e Funksionit, duhet të shtypni Ctrl+Shift+Enter për të mbyllur këtë kuti dialogu dhe për të futur funksionin TRANSPOSE. në të gjithë gamën e qelizave të zgjedhura si një formulë grupi (të mbyllura në kllapa kaçurrelë).

Supozoni se doni të transpozoni të dhënat e futura në diapazonin e qelizave A10:C11 (një grup 2 x 3) në diapazonin e qelizave bosh E10:F12 (një grup 3 x 2) të fletës së punës. Kur shtypni Ctrl+Shift+Enter për të kompletuar formulën e grupit, pasi zgjidhni diapazonin e qelizave A10:C11 si argument të grupit , Excel vendos formulën e mëposhtme të grupit në çdo qelizë të diapazonit:

{=TRANSPOSE(A10:C11)}

Figura e mëposhtme ilustron përdorimin e funksionit TRANSPOSE. Gama e qelizave B2:C4 përmban një grup 3 x 2. Për ta kthyer këtë grup 3 x 2 në diapazonin e qelizave B2:C4 në një grup 2 x 3 në diapazonin B6:D7, ndiqni këto hapa:

Zgjidhni gamën e qelizave bosh B6:D7 në fletën e punës.

Klikoni butonin e komandës Lookup & Reference në skedën Formulat e Ribbon dhe më pas zgjidhni opsionin TRANSPOSE nga menyja rënëse e butonit.

Excel fut =TRANSPOSE() në shiritin e Formulës dhe hap kutinë e dialogut Argumentet e Funksionit ku zgjidhet kutia e tekstit të argumentit Array.

Tërhiqni nëpër intervalin e qelizave B2:C4 në fletën e punës në mënyrë që kutia e tekstit të argumentit Array të përmbajë B2:C4 dhe formula në shiritin e Formulës tani lexon =TRANSPOSE(B2:C4).

Shtypni Ctrl+Shift+Enter për të mbyllur kutinë e dialogut Insert Arguments (mos klikoni OK) dhe për të futur formulën e grupit TRANSPOSE në intervalin e qelizave B6:D7 siç tregohet këtu.

Përdorimi i funksionit TRANSPOSE për të ndryshuar orientimin e një grupi të thjeshtë.

Klikimi i butonit OK në kutinë e dialogut Argumentet e Funksionit fut funksionin TRANSPOSE në qelizën aktive të përzgjedhjes aktuale të qelizës. Të bësh këtë kthen #VLERËN! vlera e gabimit në qelizë. Duhet të mbani mend të shtypni Ctrl+Shift+Enter për të mbyllur kutinë e dialogut dhe për të vendosur formulën në të gjithë gamën e qelizave.

Nëse gjithçka që dëshironi të bëni është të transpozoni titujt e rreshtave dhe të kolonave ose një tabelë të thjeshtë të dhënash, nuk keni pse të kaloni nëpër rigmarole të krijimit të një formule grupi duke përdorur funksionin TRANSPOSE. Thjesht kopjoni gamën e qelizave që do të transpozohen me butonin e komandës Copy në skedën Home të Shiritit. Poziciononi kursorin e qelizës në qelizën e parë të zbrazët ku do të ngjitet diapazoni i transpozuar përpara se të klikoni opsionin Transpozoni në menunë rënëse të butonit të komandës Paste.