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 -Kako pronaći zonu korištenja određene adrese u rasponu IP adresa, Pronađi korisnika za određenu IP adresu u rasponu između dvije IP adrese, Pronađi IP adresu u rasponu i vrati korisnika određene grupe za raspon između dvije IP adrese

IP address in range




sponzor



Kako pronaći zonu korištenja određene IP adrese u rasponu između dvije IP adrese

U ovom tutorijalu prikazati ću vam kako možemo za određenu IP adresu pronaći korisnika (security zone) između dvije IP adrese ili u rasponu IP adresa. Na slici ispod uočite situaciju.

U gornjoj tablici imamo popis IP adresa Start/End. U stupcu 'B' imamo definirano koji IP range adresa pripada određenoj sigurnosnoj grupi IP adresa (Security zone). U tablici ispod korisnik Excel aplikacije upisuje određenu IP adresu.

Naš cilj je kreirati formulu koja će automatski za određenu IP adresu (IP Address) vratiti sigurnosnu grupu (Security zone). Rezultate možete vidjeti u ćelijama C11.....C20

Pronađi IP adresu u rasponu IP adresa i vrati korisnika dotične IP adrese

Pronađi IP adresu u rasponu i vrati korisnika određene grupe za raspon između dvije IP adrese

Prvi način rješavanja ovog zadatka je korištenjem pomoćnih stupaca koje možemo sakriti da nisu vidljivi i da nam ne smetaju. Na slici ispod uočite da sam ja dodao tri stupca.

Prvi stupac 'F' sadrži IP adrese iz stupca 'C' koje su konvertirane iz "dot formata" u klasičan tekst/broj.
Drugi stupac 'G' sadrži IP adrese iz stupca 'D' koje su konvertirane iz "dot formata" u klasičan tekst/broj.
Treći stupac sadrži kopirane iz stupca 'B' nazive grupa korisnika (Security zone). Ovaj raspored i konvertiranje podataka potreban nam je da bi mogli koristiti funkciju VLOOKUP. Formule pogledajte ispod slike.

Pronađi IP adresu u rasponu i vrati korisnika određene grupe za raspon između dvije IP adrese

Rješenje zadatka pomoću VLOOKUP formule

Formula u ćeliji 'F3' konvertira IP adresu (broj sa točkama) iz "dot formata" u tekst pa zatim opet konvertira u broj. S obzirom da imamo četiri grupe brojeva između točaka, koje mogu biti jednoznamenkaste, dvoznamenkaste i troznamenkaste, formula automatski formatira svaku grupu u tri znamenke. Formula je slijedeća i treba biti u jednoj liniji koda. Formulu kopirajte do zadnjeg reda.

=VALUE(TEXT(LEFT(C3;FIND(".";C3)-1);"000")&TEXT(MID(C3;FIND(".";C3)+1;FIND(".";C3;FIND(".";C3)+1)-1-FIND(".";C3));"000")&TEXT(MID(C3;FIND(".";C3;FIND(".";C3)+1)+1;FIND(".";C3;FIND(".";C3;FIND(".";C3)+1)+1)-1-FIND(".";C3;FIND(".";C3)+1));"000")&TEXT(MID(C3;FIND(".";C3;FIND(".";C3;FIND(".";C3)+1)+1)+1;LEN(C3));"000"))

Formula u ćeliji 'G3' konvertira IP adresu (broj sa točkama) iz "dot formata" u tekst pa zatim opet konvertira u broj. S obzirom da imamo četiri grupe brojeva između točaka, koje mogu biti jednoznamenkaste, dvoznamenkaste i troznamenkaste, formula automatski formatira svaku grupu u tri znamenke. Formula je slijedeća i treba biti u jednoj liniji koda. Formulu kopirajte do zadnjeg reda.

=VALUE(TEXT(LEFT(D3;FIND(".";D3)-1);"000")&TEXT(MID(D3;FIND(".";D3)+1;FIND(".";D3;FIND(".";D3)+1)-1-FIND(".";D3));"000")&TEXT(MID(D3;FIND(".";D3;FIND(".";D3)+1)+1;FIND(".";D3;FIND(".";D3;FIND(".";D3)+1)+1)-1-FIND(".";D3;FIND(".";D3)+1));"000")&TEXT(MID(D3;FIND(".";D3;FIND(".";D3;FIND(".";D3)+1)+1)+1;LEN(D3));"000"))

Formula u ćeliji 'G3' kopira podatak iz ćelije B3. Formulu kopirajte do zadnjeg reda.

=B3

Formula u ćeliji 'C11' traži uvjet iz ćelije 'B11' u rasponu F3:F7 i vraća ekvivalentan podatak iz stupca 'H' iz istog reda (stupac H je broj 3). Ako formula ne pronađe uvjet iz ćelije 'B11' tada traži prvu manju vrijednost i vraća opet ekvivalentan podatak iz istog reda u kojem pronađe dotičnu manju vrijednost. Ova ispod VLOOKUP formula ne koristi zadnji argument, što je ključno za pronalaženje najmanjeg broja. Ipak ako želite, vi možete postaviti zadnji argument na TRUE (iza broja 3 postavite ;true).

NAPOMENA! Uočite da su u gornjoj tablici IP adrese sortirane uzlazno (A-Z) po stupcu 'C'. S obzirom da je zadnji argument VLOOKUP funkcije izostavljen (ili može biti TRUE) tada podaci OBAVEZNO moraju biti SORTIRANI UZLAZNO (A-Z) . Formula mora biti u jednoj liniji koda. Formulu kopirajte dolje do zadnjeg reda.

=VLOOKUP(VALUE(TEXT(LEFT(B11;FIND(".";B11)-1);"000")&TEXT(MID(B11;FIND(".";B11)+1;FIND(".";B11;FIND(".";B11)
+1)-1-FIND(".";B11));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11)+1)+1;FIND(".";B11;FIND(".";B11;FIND(".";B11)
+1)+1)-1-FIND(".";B11;FIND(".";B11)+1));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11;FIND(".";B11)+1)+1)+1;
LEN(B11));"000"));$F$3:$H$7;3)

Rješenje zadatka pomoću INDEX/MATCH formule

Na slici ispod uočite sličnu situaciju kao u prethodnom primjeru. U ovom slučaju ja sam dodao dva pomoćna stupca i iskoristio formule iz prethodnog primjera. Ovaj drugi način rješenja zadatka izvršio sam koristeći funkcije INDEX/MATCH koje su u stvari bolje izvedba i zamjena za Vlookup funkciju. Zadatak je isti. Za uvjet IP adrese iz ćelije 'B11' treba kao rezultat vratiti grupu korisnika za određeni raspon ćelija. NAPOMENA! Uočite na slici ispod, IP adrese NISU sortirane.

Traži IP adresu u IP rasponu adresa i kao rezultat vrati korisnika

Prvi stupac 'F' sadrži IP adrese iz stupca 'C' koje su konvertirane iz "dot formata" u klasičan tekst/broj.
Drugi stupac 'G' sadrži IP adrese iz stupca 'D' koje su konvertirane iz "dot formata" u klasičan tekst/broj.

Formula u ćeliji 'F3' je slijedeća. Formulu kopirajte do zadnjeg reda.

=VALUE(TEXT(LEFT(C3;FIND(".";C3)-1);"000")&TEXT(MID(C3;FIND(".";C3)+1;FIND(".";C3;FIND(".";C3)+1)-1-FIND(".";C3));"000")&TEXT(MID(C3;FIND(".";C3;FIND(".";C3)+1)+1;FIND(".";C3;FIND(".";C3;FIND(".";C3)+1)+1)-1-FIND(".";C3;FIND(".";C3)+1));"000")&TEXT(MID(C3;FIND(".";C3;FIND(".";C3;FIND(".";C3)+1)+1)+1;LEN(C3));"000"))

Formula u ćeliji 'G3' je slijedeća. Formulu kopirajte do zadnjeg reda.

=VALUE(TEXT(LEFT(D3;FIND(".";D3)-1);"000")&TEXT(MID(D3;FIND(".";D3)+1;FIND(".";D3;FIND(".";D3)+1)-1-FIND(".";D3));"000")&TEXT(MID(D3;FIND(".";D3;FIND(".";D3)+1)+1;FIND(".";D3;FIND(".";D3;FIND(".";D3)+1)+1)-1-FIND(".";D3;FIND(".";D3)+1));"000")&TEXT(MID(D3;FIND(".";D3;FIND(".";D3;FIND(".";D3)+1)+1)+1;LEN(D3));"000"))

sponzor


Formula u ćeliji 'C11' traži uvjet iz ćelije 'B11' u rasponu 'F3:F7' i vraća ekvivalentan podatak iz stupca 'B' (B3:B7) iz istog reda. Nakon kopiranja u svoju Excel aplikaciju postavite formulu u jednu liniju.

=INDEX($B$3:$B$7; SUMPRODUCT(--(VALUE(TEXT(LEFT(B11;FIND(".";B11)-1);"000")&TEXT(MID(B11;FIND(".";B11)+1;
FIND(".";B11;FIND(".";B11)+1)-1-FIND(".";B11));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11)+1)+1;FIND(".";B11;
FIND(".";B11;FIND(".";B11)+1)+1)-1-FIND(".";B11;FIND(".";B11)+1));"000")&TEXT(MID(B11;FIND(".";B11; FIND(".";B11;
FIND(".";B11)+1)+1)+1;LEN(B11));"000"))<=$G$3:$G$7); --(VALUE(TEXT(LEFT(B11;FIND(".";B11)-1);"000")&TEXT(MID(B11;
FIND(".";B11)+1;FIND(".";B11;FIND(".";B11)+1)-1-FIND(".";B11));"000")&TEXT(MID(B11;FIND(".";B11;FIND(".";B11)+1)+1;
FIND(".";B11; FIND(".";B11;FIND(".";B11)+1)+1)-1-FIND(".";B11;FIND(".";B11)+1));"000")&TEXT(MID(B11;FIND(".";B11;
FIND(".";B11;FIND(".";B11)+1)+1)+1;LEN(B11));"000"))>=$F$3:$F$7);ROW($A$1:$A$5)))

Nadam se da ste shvatili kako ovaj zadatak možemo riješiti na dva načina. Postoji još načina a da ne govorim i o verzijama koristeći VBA ili UDF. No za Excel početnike od mene dovoljno je i ovo.




   

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