logotip web stranice ic.ims.hr
Home - počtna stranica  FORUM ACCESS 2003 EXCEL 2003 WORD 2003  
1 Pokretanje i zatvaranje Microsoft Excel-a 2013
2 Izgled prozora Microsoft Excela 2013
3 Alatne trake u MS Excelu 2013
4 Podešavanje Excela
5 Otvaranje nove radne knjige i radnog lista
6 Spremanje i zatvaranje Workbook u Excelu
7 Pregled radnog lista prije ispisa u Excelu 2013
8 Određivanje margina Radnog lista
9 Kreiranje Zaglavlja i Podnožja Radnog lista
10 Ispis radnog lista ili tablice ili područja u Excelu 2013
11 Kreiranje mapa u Excelu
12 Kretanje radnom tablicom u Excelu 2013
13 Označavanje ili selektiranje u Excelu
14 Operatori u Excelu
15 Kreiranje formule u Excelu
16 Uvjetno oblikovanje u Excelu 2013
17 Kako izraditi padajuću listu u Excelu - Data Validation
18 Imenovanje ćelija ili raspona podataka u excelu 2013
19 Poravnanje teksta
20 Promjena podataka i brisanje sadržaja ćelije u Excelu 2013
21 Spajanje i dijeljenje ćelija u Excelu 2013
22 Automatska ispuna - brzi unos podataka
23 Popunjavanje ćelija specijalnim znakovima i simbolima
24 Crtanje u Excelu 2013
25 Dodavanje komentara na ćelije u Excelu 2013
26 Naredbe poništi i ponovi u excelu 2013
27 Premještanje, izrezivanje i kopiranje sadržaja ćelije u Excelu 2013
28 Umetanje i brisanje Ćelija, Stupaca i Redova
29 Promjena veličine Ćelija, Stupaca i Redova u Excelu 2013
30 Umetanje slika u Excelu
31 Radnje vezane uz Radni List u Excelu 2013
32 Apsolutna i relativna adresa ćelija u Excelu
33 Izrada Grafikona u Excelu 2013
34 Oblikovanje radnih tablica u Excelu 2013
35 Sortiranje podataka u Excelu 2013
36 Filtriranje podataka u Excelu 2013
37 Kreiranje Pivot Table
38 Zamrzavanje dijela prozora radnog lista u Excelu 2013
39 Oblikovanje Datuma u Excelu 2013
40 Zaključavanje ćelija i radnog lista u Excelu 2013
41 Zaštita radne knjige u Excelu 2013
42 Greške u Excelu 2013
43 Popis svih funkcija u Excelu 2013
44 Tipkovni prečaci u Excelu (hot key, shortcut)
45 Ograničenja u Excelu 2013
46 Makronaredbe u Excelu
47 Razni primjeri u Excelu
   
1 Excel 2003 primjeri
1 Excel 2007 primjeri
1 VBA U EXCELU
   
 

MICROSOFT EXCEL 2013 tutoriali -HYPERLINK tutorijal, Hiperlink sa jedan ili više uvjeta, Jedan uvjet za Hyperlink, Dva uvjeta za Hiperlink u Excelu, Hiperlink uz uvjet, Kako kreirati hyperlink ako imamo jedan ili više uvjeta u ćelijama

HYPERLINK sa uvjetom u istoj radnoj knjizi




sponzor



Nastavak pregleda tutorijala za temu HYPERLINK u Excelu.

HYPERLINK uz 1 uvjet u Excelu

U Excelu možemo kreirati hiperlink ili link na više načina za više potreba Excel kalkulacija. Jedan od načina kreiranja linka je link na destinacijsku ćeliju u istoj radnoj knjizi na drugi radni list ali pod uvjetom u nekoj ćeliji.

Na slici ispod uočite radnu knjigu koja sadrži dva radna lista.

Radni list 'Sheet2' sadrži podatke koji u stupcu 'A' imaju nekakve podatke, bilo da je tekst, broj ili neka vrijednost. Na osnovu podataka iz stupca 'A' vršimo daljnje kalkulacije. Na radnom listu 'Sheet1' želimo unositi podatak koji je identičan podacima iz stupca 'A' a da nam Excel automatski kreira hyperlink koji će nas pozicionirati u destinacijsku ćeliju.

destinacijski radni list za jedan uvjet i hiperlink u Excelu

Na prvom radnom listu 'Sheet1' u stupcu 'A' uočite ćelije u koje upisujemo nekakav podatak i to je naš uvjet na koji se odnosi link u stupcu 'B'. Nakon unosa podataka u stupcu 'A' želimo da Excel automatski kreira hiperlink na 'Sheet2' na destinacijsku ćeliju koja sadrži upisani podatak iz stupca 'A'. Ovaj uvjet u stupcu 'A' možemo direktno unositi ili ga birati preko padajuće liste (padajućeg izbornika) preko Data Validation.

Hiperlink ili link sa jednim uvjetom iste radne knjige u Excelu

Formula u stupcu 'B' je slijedeća

=IF(A2="";"";HYPERLINK("["&MID(CELL("filename");SEARCH("[";CELL("filename"))+1; SEARCH("]";
CELL("filename"))-SEARCH("[";CELL("filename"))-1)
&"]'Sheet2'!A"&MATCH(A2;Sheet2!$A$2:$A$100;0)+1;"Link"))

Ako pogledate Evaluate Formula tada ćete uočiti da formula odrađuje redoslijedom slijedeće operacije. Za više detalja i objašnjenja pogledajte prethodni tutorijal vezan za temu Hyperlink.

Excel funkcija IF uspoređuje stanje ćelije koja je uvjet (u ovom slučaju je to Excel ćelija 'A2')
Excel funkcija Hyperlink kao rezultat vraća naziv radne knjige i spaja se sa nazivom radnog lista (koji smo definirali u formuli)
Excel funkcija Match uspoređuje podatke uvjeta iz ćelije 'A2' i raspona podataka na 'Sheet2' range '$A$2:$A$100' i kao rezultat vraća broj reda kada pronađe identičan podatak. Broj +1 iza funkcije Match određuje redak u kojem se nalazi prva ćelija raspona podataka. Znači, ako vam se podaci na 'Sheet2' nalaze u rasponu podataka '$A$50:$A$100' ovaj dotični broj bit će +49.

I na kraju rezime; Nakon unosa podatka (našeg uvjeta) u ćeliju 'A2' Excel kreira hyperlink. Klikom mišem na dotični hyperlink Excel nas automatski pozicionira na 'Sheet2' u ćeliju koja sadrži identičan podataka (naš uvjet).

HYPERLINK uz 2 uvjeta Excelu

U slijedećem primjeru ispod prikazat ću kako možemo iskoristiti Excel funkcije za kreiranje Hyperlinka ako imamo dva uvjeta. U tutorijalu ću pokazati dvije varijante formula jer su vezane za format Excel datoteke (verzija Excel 2003 i Excel 2007-2013)

U primjeru ispod radna knjiga ima slijedeće radne listove:

  • Radni list 'base': Radni list na kojem se nalazi automatski popis svih radnih listova koji se nalaze u radnoj knjizi
  • Radni list 'LINKS': Radni list na kojem imamo dva uvjeta pomoću koji kreiramo hiperlink na ostale radne listove, zavisno o uvjetima
  • Radni list Sheet2, Sheet3, Sheet5: Radni listovi na kojima se nalaze podaci sa kalkulacijama na koje se linkamo klikom na link sa radnog lista 'LINKS'. Cilj je klikom na link automatski se pozicionirati na destinacijsku ćeliju koja sadrži identičan podatak.

Izrada popisa svih radnih listova unutar radne knjige pomoću formule

Radni list 'base' sadrži listu ili popis svih radnih listova koji se nalaze u radnoj knjizi. Popis svih radnih listova može se izraditi koristeći VBA makronaredbu. Ovaj popis izrađen je pomoću formule.

Na slici ispod uočite imenovani raspon podataka 'SheetList' koji se nalazi u rasponu 'A4:A19'. Prilikom kreiranja novog radnog lista unutar aktivne radne knjige ovaj popis se automatski ažurira. Također uočita da se prva dva radna lista sa popisa ne koriste u daljnjim kalkulacijama (crvena slova)

Popis svih radnih listova u Excel radnoj knjizi

U ćeliji 'A2' nalazi se formula koja se kopira do određenog retka prema dolje.

=IF(ROW(A2)-ROW($A$2)+1<=COUNTA(All_Sheets);INDEX(All_Sheets;ROW(A2)-ROW($A$2)+1);"")

Da bi ova formula iznad funkcionirala potrebno je u Name Manageru kreirati dodatnu formulu koja je vezana za dotičnu formulu iznad. Kliknite na 'Formulas => Name Manager => New' i upišite u slijededća polja

  • Name: All_Sheets
  • Refers to: =REPLACE(GET.WORKBOOK(1)&T(NOW());1;FIND("]";GET.WORKBOOK(1)&T(NOW()));"") (autor: Krishnakumar)

Name Manager u Excelu

U Name Manageru na slici ispod uočite sve imenovane raspone podataka.

  • All_Sheets: vidi formulu iznad
  • Sheet2Range: raspon podataka na 'Sheet2' => '$A$2:$A$10
  • Sheet3Range: raspon podataka na 'Sheet3' => '$A$2:$A$10
  • Sheet5Range: raspon podataka na 'Sheet5' => '$A$2:$A$10
  • SheetList: raspon podataka na radnom listu 'base' => '$A$4:$A$19

Name manager u Excelu i kreirana formula

Radni listovi na koje treba upućivati hyperlink i pozicionirati se u željenu ćeliju nakon klika

Radni listovi Sheet2, Sheet3 i Sheet5 su destinacijski radni listovi na koje nas Excel treba automatski pozicionirati u zavisnosti na koji hyperlink smo kliknuli na radnom listu 'LINKS'.

Uočite raspon podataka koji sam imenovao kao 'Sheet2Range' => '$A$2:$A$10'. Pitate se zašto baš ovakvo ime raspona podataka. Da bi mi bilo lakše spajati uvjete, Ipak vidjet ćete objašnjenje u nastavku tutorijala.

Destinacijski radni list preko hiperlinka

Na slici ispod uočite raspon podataka koji sam imenovao kao 'Sheet3Range' => '$A$2:$A$10'.

hyperlink na drugi radni list u Excelu

Uočite na slici ispod raspon podataka koji sam imenovao kao 'Sheet5Range' => '$A$2:$A$10'.

Hyperlink i uvjeti za hyperlink u Excelu

U prethodna tri navedena radna lista postavio sam Hyper link 'Home' koji upućuje na startnu poziciju a to je radni list 'LINKS'. Formula u 'F1' za link 'Home' na sva tri radna lista je slijedeća: =HYPERLINK("#"&CELL("address";LINKS!A1);"Home")

Znak 'ljestvice' (eng: hash, pound ili sharp) određuje aktivnu radnu knjigu.

Hiperlink sa dva uvjeta u Excelu

sada dolazimo da najvažnijeg radnog lista u kojem kreiramo hyperlink u zavisnosti od dva uvjeta. Na slici ispod uočite situaciju na radnom listu 'LINKS'. Ovo je naš radni list na kojem želimo automatski kreirati hiperlink na drugi radni list (drugi uvjet - stupac B) točno u destinacijsku ćeliju koja sadrži identičan podatak (prvi uvjet - stupac A).

Dakle u stupcu 'A' unosimo ime, broj ili neki podatak

U stupcu 'B' Excel automatski kao rezultat formule vraća ime radnog lista uz uvjet iz stupca 'A'. ARRAY formula u ćeliji 'B2' koja se kopira prema dolje je slijedeća (formulu završavate sa CTRL+SHIFT+ENTER):

Za Excel 2003

=IF(ISERROR(VLOOKUP(A2;INDIRECT("'"&INDEX(SheetList;MATCH(1;--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$B$10")
;A2)>0);0))&"'!$A$1:$B$10");2;FALSE));"";VLOOKUP(A2;INDIRECT("'"&INDEX(SheetList;MATCH(1;--(COUNTIF(INDIRECT
("'"&SheetList&"'!$A$1:$B$10");A2)>0);0))&"'!$A$1:$B$10");2;FALSE))

Za Excel 2007, 2010, 2013

=IFERROR(VLOOKUP(A2;INDIRECT("'"&INDEX(SheetList;MATCH(1;--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$B$100");A2)>0);0))&"'!$A$1:$B$100");2;FALSE);"")

Startna pozicija su prazne ćelije raspona podataka na slici označene obrubom Excel ćelija. U stupac 'A' unosimo podatke. Kada unesemo neki jedinstveni podatak za koji znamo da se nalazi na nekom od radnih listova (može ih biti više desetaka) formula u stupcu 'B' (vidi iznad) automatski prikazuje naziv radnog lista na kojem se dotični jedinstveni podatak nalazi. Formula u stupcu 'C' automatski kreira hyperlink na osnovu podataka iz stupaca 'A i B'. Mali rezime: Kada upišemo u 'A2' ćeliju podatak 'John Doe' Excel automatski prikaže naziv radnog lista na kojem se dotični podatak nalazi i u 'C2' ćeliji kreira hyperlink na destinacijsku ćeliju na radnom listu imena iz 'B2' ćelije.

  • Stupac 'A': Unos jedinstvenog podataka
  • Stupac 'B': Automatski prikaz radnog lista na kojem se nalazi podatak iz stupca A u istom retku
  • Stupac 'C': Automatsko kreiranje hyperlinka na osnovu podataka iz stupaca A i B

Hiperlink sa dva uvjeta u Excelu

I na kraju formula koja automatski kreira hyperlink u zavisnosti od prethodna dva uvjeta iz stupaca A i B je slijedeće:

Za Excel 2003

=IF(ISERROR(HYPERLINK("#"&B2&"!"&ADDRESS(MATCH(A2;INDIRECT(B2&"Range");0)+1;1);A2&" Link"));"";HYPERLINK("#"&B2&"!"&ADDRESS(MATCH(A2;INDIRECT(B2&"Range");0)+1;1);A2&" Link"))

Za Excel 2007, 2010, 2013

=IFERROR(HYPERLINK("#"&B2&"!"&ADDRESS(MATCH(A2;INDIRECT(B2&"Range");0)+1;1);A2&" Link");"")

Sada vam mogu pojasniti zašto sam na radnim listovima Sheet2, Sheet3 i Sheet5 imenovao raspone podataka imenom Sheet?Range. Uočite u formulama iznad izraz INDIRECT(B2&"Range"). Unutar funkcije Indirect spajaju se podatak iz ćelije 'B2' i tekst 'Range'. Na ovaj način omogućeno je kopiranje formule prema dolje jer se kopiranjem mijenja adresa ćelije B2 pa se tako kopiranjem prema dolje kao rezultat vraća imenovani naziv za raspon podataka (Sheet2Range, Sheet3Range, Sheet5Range).

sponzor


Alternativne formule za kreiranje hyperlinka

Umjesto gore navedenih formula možemo korisititi i slijedeće formule za kreiranje hyperlinka u Excelu unutar aktivne radne knjige. Napomena: Formula koja sadrži IFERROR funkciju koristi se u Excelu 2007 i novijem a za Excel 2003 koristi se IF/ISERROR.

  • Alternativna ARRAY formula u stupcu 'B' za vraćanje naziva radnog lista u odnosu na uvjet iz stupca 'A' je slijedeća

=VLOOKUP(A2;INDIRECT("'"&INDEX(SheetList;MATCH(1;--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$B$10");A2)>0);0))&"'!$A$1:$B$10");{2\3};FALSE)

  • Alternativna formula u stupcu 'C' za kreiranje Hyperlinka u odnosu na uvjete iz stupca 'A i B' može biti i slijedeća

=IFERROR(HYPERLINK("[Book1.xlsm]"&B2&"!"&ADDRESS(MATCH(A2;INDIRECT(B2&"Range");0)+1;1);A2&" Link");"")

NAPOMENA: Ako kopirate formule sa ove web stranice tutorijala tada obratite pažnju da formula bude u jednom retku ili jednoj liniji.

Razni tutorijali vezani za tematiku HYPERLINK u Excelu

Za pregled prethodnog tutorijala vezanog za tematiku Hyperlink kliknite na BACK button.




  Prethodna stranica tutorijala

sponzor



COPYRIGHT © - 2005 - 2020 IvanC - Sva prava pridržana - Ova web domena/web stranica koristi cookies.
Prema međunarodnom zakonu o autorskom djelu zabranjeno je kopiranje, reproduciranje, prosljeđivanje i publiciranje cijelog sadržaja ili bilo kojeg njegova dijela