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 EXCE 2013 tutoriali - Izračun udaljenosti između dva aerodroma, Kako izračunati udaljenost između aerodroma od startnog do destinacijskog grada, Kalkulacija zračne udaljenosti između dva grada

Kalkulacija udaljenosti između dva aerodroma




sponzor



Kako na brz način prikazati udaljenost između dva aerodroma

U ovom Excel tutorijalu pokazat ću, kako možemo koristiti Excel i izračunati udaljenost između dva aerodroma u svijetu. Da bi mogli kalkulirati udaljenost potrebna nam je zemljopisna širina i zemljopisna dužina na kojoj se nalazi određeni aerodrom/grad. U stvari oda dva parametra su koordinate određenog aerodroma. Zato zapamtite ove pojmove ispod.

Latitude -> Zemljopisna širina
Longitude -> Zemljopisna dužina

Baziran na koordinatama zemljopisne širine i zemljopisne dužine pomoću Haversine formule možemo izračunati zračnu udaljenost između dva aerodroma. Na slici ispod uočite elemente Haversine formule. U daljnjem tekstu tutorijala vidjet ćete kako ta formula izgleda u Excelu.

Haversine formula

Kalkulacija udaljenosti između dva grada

Na slici ispod uočite raspored padajućih izbornika od polazne točke do odredišta. U ćelijama 'A2', 'B2', 'C2', 'A5', 'B5' i 'C5' nalaze se padajući izbornici. U ćelijama 'B2', 'B5' i 'C2', 'C5' nalaze se zavisni padajući izbornici, jer zavise o prethodnom podatku ispred ćelije. To znači ako izaberete zemlju 'Croatia' u ćeliji 'B2'/'B5' ćete moći izabrati samo gradove koji se nalaze u Hrvatskoj. Isto tako u ćeliji 'C2'/'C5' moći ćete izabrati samo imena aerodroma koji se nalaze u dotičnom gradu (naravno ako ih ima više od jednog).

U osmom redu nalaze se formule koje automatski vraćaju rezultate za uvjete postavljene u prethodno navedenim ćelijama.

udaljenost između dva aerodroma

Radni list "WorldCountries"

Ovaj radni list sadrži popis zemalja u svijetu sa pripadajućim kodovima, oznakama kontinenata i web linkovima za pojedinosti o izabranoj zemlji (državi). Ovaj radni list nije važan i možete ga obrisati ako želite.

radna knjiga sadrži slijedeće listove

Kreiranje padajućih izbornika za zemlju, grad i ime aerodroma u startu

Na radnom listu "Distance Between Airports" postoji slijedeće:

Padajući izbornik 'from Country'

Kao što vidite na slici ispod, u ćeliji 'A2' nalazi se padajući izbornik u kojem možete izabrati jednu od 237 zemalja (država) koje se nalaze u bazi podataka.

padajuća lista od aerodroma

Postavljanje 'Data Validation' u ćeliji 'A2' učinite prema uputama sa linka a u "Source polje" postavite formulu

=helper!$A$2:$A$238

validacija podataka od drzave

Zavisni padajući izbornik 'from City'

Kao što vidite na slici ispod, u ćeliji 'B2' nalazi se padajući izbornik u kojem možete izabrati jedan od 6679 gradova u kojima postoji jedan ili više aerodroma a nalaze se u bazi podataka.

zavisna padajuća lista od grada

Postavljanje 'Data Validation' u ćeliji 'B2' učinite prema uputama sa linka a u "Source polje" postavite formulu ispod. (Napomena imenovane formule nalaze se u tekstu ovog tutorijala u poglavlju Name Manager).

=OFFSET(StartCell;1;MATCH($A2;Countries;0)-1;COUNTA(OFFSET(StartCell;;MATCH($A2;Countries;0)-1;10000;1))-1;1)

validacija podataka od grada

Zavisni padajući izbornik 'from Airport Name'

Kao što vidite na slici ispod, u ćeliji 'C2' nalazi se padajući izbornik u kojem možete izabrati jedan od aerodroma za prethodno izabrani grad, naravno ako grad ima samo jedan aerodrom tada će samo on biti na popisu.

padajući izbornik od aerodroma

Postavljanje 'Data Validation' u ćeliji 'C2' učinite prema uputama sa linka a u "Source polje" postavite formulu ispod. Ovaj padajući izbornik povlači stavke iz pomoćnog stupca 'J' (objašnjenje u poglavlju 'pomoćni stupci' u nastavku tutorijala).

=$J$2:$J$7

validacija podataka za startni aerodrom

Kreiranje padajućih izbornika za odredište: zemlju, grad i ime aerodroma

Prethodno opisani postupci kreiranja Data Validaion vrijede i za odredišne padajuće izbornike.

Padajući izbornik 'to Country'

Kao što vidite na slici ispod, u ćeliji 'A5' nalazi se padajući izbornik u kojem možete izabrati jednu od 237 zemalja (država) koje se nalaze u bazi podataka.

padajuća lista do zemlje u kojoj se nalazi aerodrom

Postavljanje 'Data Validation' u ćeliji 'A5' učinite prema uputama sa linka a u "Source polje" postavite formulu

=helper!$A$2:$A$238

validacija podataka do odredišne zemlje

Zavisni padajući izbornik 'to City'

Kao što vidite na slici ispod, u ćeliji 'B5' nalazi se padajući izbornik u kojem možete izabrati jedan od 6679 gradova u kojima postoji jedan ili više aerodroma a nalaze se u bazi podataka.

padajući izbornik za destinaciju grad

Postavljanje 'Data Validation' u ćeliji 'B5' učinite prema uputama sa linka a u "Source polje" postavite formulu ispod. (Napomena imenovane formule nalaze se u tekstu ovog tutorijala u poglavlju Name Manager).

=OFFSET(StartCell;1;MATCH($A2;Countries;0)-1;COUNTA(OFFSET(StartCell;;MATCH($A2;Countries;0)-1;10000;1))-1;1)

validacija podataka do grada

Zavisni padajući izbornik 'to Airport Name'

Kao što vidite na slici ispod, u ćeliji 'C5' nalazi se padajući izbornik u kojem možete izabrati jedan od aerodroma za prethodno izabrani grad, naravno ako grad ima samo jedan aerodrom tada će samo on biti na popisu.

popis aerodroma koji pripadaju gradu

Postavljanje 'Data Validation' u ćeliji 'C2' učinite prema uputama sa linka a u "Source polje" postavite formulu ispod. Ovaj padajući izbornik povlači stavke iz pomoćnog stupca 'L' (objašnjenje u poglavlju 'pomoćni stupci' u nastavku tutorijala).

=$L$2:$L$7

validacija podataka do specifičnog aerodroma

Pomoćni stupci za izradu zavisne padajuće liste naziva aerodroma

Na slici ispod uočite dva pomoćna stupca na radnom listu "Distance Between Airports". Ova dva stupca sadrže popis svih imena aerodroma u gradu koji ste izabrali u ćeliji 'B2'/'C5' i vraća popis svih aerodroma koji pripadaju relevantnom gradu.

pomoćni stupci

ARRAY formula u ćeliji 'J2' je: (Ova formula vraća popis svih imena aerodroma za grad koji je postavljen u ćeliji 'B2'.

=IFERROR(INDEX(tbl;SMALL(IF(COUNTIF($A$2;AirportsDatabase2017!$B$2:$B$7185)*COUNTIF($B$2;AirportsDatabase2017!
$C$2:$C$7185);ROW(tbl)-MIN(ROW(tbl))+1);ROW(C1));COLUMN(C1));"")

ARRAY formula u ćeliji 'L2' je: (Ova formula vraća popis svih imena aerodroma za grad koji je postavljen u ćeliji 'B5'.

=IFERROR(INDEX(tbl;SMALL(IF(COUNTIF($A$5;AirportsDatabase2017!$B$2:$B$7185)*COUNTIF($B$5;AirportsDatabase2017!
$C$2:$C$7185);ROW(tbl)-MIN(ROW(tbl))+1);ROW(C1));COLUMN(C1));"")

Imenovanje formula u Name Manageru

Name Manager nalazi se na Formulas Tab ribonu. U njemu su imenovane formule koje su ugniježđene u OFFSET formulu. Tu se također nalazi imenovani raspon podataka "tbl" koji nam služi kao baza podataka za VLOOKUP formulu.

imenovane formule u Name Manageru

Imenovane formule u name Manageru

Name: Countries
Refers to: =OFFSET(StartCell;;;1;COUNTA(OFFSET(StartCell;;;;100)))

Name: StartCell
Refers to: =helper!$B$1

Name: tbl
Refers to: =AirportsDatabase2017!$B$2:$L$7185

Automatski prikaz IATA međunarodne šifre za određeni aerodrom

Na slici ispod u osmom redu nalaze se formule koje vraćaju rezultate za postavljene uvjete u drugom i petom redu (A:C). Uočite da u ćelijama 'A8' i 'B8' vraćeni rezultat je IATA međunarodni kod koji je jedinstven za svaki aerodrom. U 'C8' rezultat je udaljenost između dva aerodroma. U ćelijama 'D8:G8' nalaze se zemljopisne širine i dužine za dotične gradove/aerodrome.

iata međunarodne šifre za aerodrome

Na radnom listu "Distance Between Airports" formule su slijedeće:

Formula u ćeliji 'A8' je: (Ova formula vraća rezultat iz radnog lista 'AirDatabase2017' iz drugog stupca za uvjet iz ćelije 'C2').
=IFERROR(VLOOKUP($C$2;AirportsDatabase2017!$D$2:$E$7185;2;FALSE);"")

Formula u ćeliji 'B8' je: (Ova formula vraća rezultat iz radnog lista 'AirDatabase2017' iz drugog stupca za uvjet iz ćelije 'C5').
=IFERROR(VLOOKUP($C$5;AirportsDatabase2017!$D$2:$E$7185;2;FALSE);"")

Formula u ćeliji 'C8' je: (Ova formula koristi Haversine formulu za izračun udaljenosti između dva aerodroma, formula se bazira na zemljopisne širine i zemljopisne dužine koje se nalaze u stupcima 'D8:E8' i 'F8:G8').
=IFERROR(ACOS(COS(RADIANS(90-D8))*COS(RADIANS(90-F8))+SIN(RADIANS(90-D8))*SIN(RADIANS(90-F8))*COS(RADIANS(E8-G8)))*6371;"")

Formula u ćeliji 'D8' je: (Ova formula vraća zemljopisnu širinu iz radnog lista 'AirDatabase2017' za uvjet iz ćelije 'A8').
=IFERROR(VLOOKUP($A8;AirportsDatabase2017!$E$2:$H$7185;3;0);"")

Formula u ćeliji 'E8' je: (Ova formula vraća zemljopisnu dužinu iz radnog lista 'AirDatabase2017' za uvjet iz ćelije 'A8).
=IFERROR(VLOOKUP($A8;AirportsDatabase2017!$E$2:$H$7185;4;0);"")

Formula u ćeliji 'F8' je: (Ova formula vraća zemljopisnu širinu iz radnog lista 'AirDatabase2017' za uvjet iz ćelije 'B8').
=IFERROR(VLOOKUP($B8;AirportsDatabase2017!$E$2:$H$7185;3;0);"")

Formula u ćeliji 'G8' je: (Ova formula vraća zemljopisnu dužinu iz radnog lista 'AirDatabase2017' za uvjet iz ćelije 'B8).
=IFERROR(VLOOKUP($B8;AirportsDatabase2017!$E$2:$H$7185;4;0);"")

Radni list "Airports DataBase"

Na ovom radnom listu nalaze se svi podaci koji su potrebni za izračun udaljenosti između dva aerodroma. Ova baza je iz 2017 godine a izvor je "https://ourairports.com/data/#excel"

baza podataka međunarodnih aerodroma

Radni list "Helper"

Na ovom radnom listu nalaze se jedinstveni popisi svih zemalja svijeta (država) i pripadajućih gradova koji imaju aerodrom. Svi popisi su sortirani po abecednom redu. U stupcu 'A' nalazi se popis svih zemalja/država a u stupcima 'B1:ID1' nalazi se ime zemlje/države a ispod pripadajući gradovi. Ovaj "helper" radni list je izvor podataka za drugi zavisni padajući izbornik koji se nalazi u ćelijama 'B2' i 'B5' na radnom listu "Distance Between Airports".

popis zemalja i gradova u svijetu koji imaju aerodrom

Automatsko uklanjanje zavisnih padajući lista prilikom promjene podataka

Kao što znate prilikom izmjene podataka u padajućim izbornicima kada želite promijeniti startnu zemlju ili grad, poželjno je da se zavisni padajući izbornici uklone, tako da imate vizualno upozorenje da trebate izabrati novu stavku sa popisa. U tu svrhu možete koristit VBA makronaredbu ispod.

Ovu VBA makronaredbu kopirajte u 'Sheet Module' vašeg VBE za Excel

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then
Sheets("Distance Between Airports").Range("B2:C2").Value = ""
End If
If Not Intersect(Target, Range("B2")) Is Nothing Then
Sheets("Distance Between Airports").Range("C2").Value = ""
End If
If Not Intersect(Target, Range("A5")) Is Nothing Then
Sheets("Distance Between Airports").Range("B5:C5").Value = ""
End If
If Not Intersect(Target, Range("B5")) Is Nothing Then
Sheets("Distance Between Airports").Range("C5").Value = ""
End If
End Sub

Da bi vam ova makronaredba bila funkcionalna, spremite radnu knjigu u formatu *.xls ili *.xlsm ili *.xlsb

I na kraju u prilogu ovog tutorijala imate mogućnost Download datoteke na kojoj sam radio ovaj tutorijal.




   

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