Microsoft Excel je vrlo moćan software kada je u pitanju izvođenje proračuna sa formulama, rad sa tabelama.
Excel nudi i širok spektar različitih tipova grafikona koje kreira na osnovu tabela.
Međutim, jedna vrsta grafikona je zapostavljena i izostavljena - gantovi dijagrami.
U članku Gantov dijagram u Excel-u sam već govorio o tome kako kreirati gantov dijagram u Excel-u.
Ovaj puta je riječ o grafički manje dotjeranom, ali vrlo praktičnom načinu kreiranja grafikona.
Naime, uz malo programiranja pomoću VBScript-a i manipulacije bojom pojedinih ćelija Excel-a moguće je kreirati dijagrame kao na slici ispod uz nekoliko klikova mišem.
Ovakvi dijagrami se najčešće koriste u uputstvima za elektronske komponente (vremenski releji, sekvenceri,...) gdje vizuelno prikazju način rada komponente. Inače, gantovi dijagrami se koriste za vizualno predstavljanje napretka projekta tako što je svaka aktivnost predstavljena segmentima odgovarajuće boje koji koriste zajedničku vremensku osu.
Uz objašnjenje kreiranja grafikona naučićemo se i neke naredbe VBScript-a karakteristične za Excel.
Prije svega, download-ovati primjer koji sam kreirao sa linka ispod.
Za otvaranje ovog dokumenta, koji je sa ekstenzijom .xlsm (macro enabled dokument) će vam biti potreban MS Excel 2010.
Kada otvorite dokument, morate dozvoliti korištenje macro-a unutar dokumenta, inače ništa neće raditi.
Prvo da pojasnim šta uopšte radi ovaj Excel dokument.
Nakon što otvorite dokument, trebate dobiti Excel sheet Kreiranje grafikona sa dijagramom na vrhu (kao na slici iznad) i tabelom ispod grafikona koja sadrži podatke za crtanje grafikona. Dvoklikom na neku od vrijednosti u tabeli, ta vrijednost se sa 0 promijeni u 1 i obratno. Istovremeno se na grafikonu odgovoarajuće polje oboji (za vrijednost 1) ili postane bijelo (za vrijednost 0). U prvoj koloni tabele su imena svakog segmenta grafikona. Upisom imena u tu kolonu, ta imena se automatski upisuju u prvu kolonu grafikona kao i u prvu kolonu tabele za izbor boja.
Ispod tabele sa podacima nalazi se tabela sa 4 vrste (dole lijevo) za izbor boja za pojedine segmente grafikona. Upisom broja boje u odgovarajuću vrstu i klikom na bilo koje drugo polje, mijenja se boja u polju pored onoga u koje je vrijednost upisana, zatim se, izabranom bojom, oboji odgovarajuća vrsta u tabeli sa podacima i istom bojom se oboje segmenti na već kreiranom grafikonu. Desno od te tabele je tabela sa 50 polja različitih boja. To je tabela uzoraka boja. Unutar svake ćelije je broj koji treba upisati u tabelu lijevo da se dobije boja segmenta kao u ćeliji tabele uzoraka boja.
U slučaju da želite prikazati manje od 4 vrste na grafikonu, onda u prvoj vrsti tabele izbrišite ime a sve vrijednosti u toj vrsti postavite na 0. Time će taj segmet grafikona jednostavno nestati.
Kako to sve navedeno uopšte funkcioniše?
Pa budući da sam uvijek bio ljubitelj Visual Basic-a, to sam i ovdje posegnuo za vještinom programiranja i pomoću VBScripta kreirao program koji obavlja željene funkcije.
Pa da krenemo sa objašnjavanjem VBScript kôda koji to sve što je iznad navedeno.
U proceduri Private Sub Worksheet_Activate() izvršiti inicijalizaciju sljedećih vrijednosti:
'Inicijalizacija boja
boja1 = Cells(38, 3).Value
boja2 = Cells(39, 3).Value
boja3 = Cells(40, 3).Value
boja4 = Cells(41, 3).Value
bijela = 2
'Prikaz boja za uzorak (od 1 do 50)
Cells(38, 10).Interior.ColorIndex = 1
Cells(38, 11).Interior.ColorIndex = 2
Cells(38, 12).Interior.ColorIndex = 3
Cells(38, 13).Interior.ColorIndex = 4
Cells(38, 14).Interior.ColorIndex = 5
Cells(38, 15).Interior.ColorIndex = 6
Cells(38, 16).Interior.ColorIndex = 7
Cells(38, 17).Interior.ColorIndex = 8
Cells(38, 18).Interior.ColorIndex = 9
Cells(38, 19).Interior.ColorIndex = 10
Cells(39, 10).Interior.ColorIndex = 11
Cells(39, 11).Interior.ColorIndex = 12
Cells(39, 12).Interior.ColorIndex = 13
Cells(39, 13).Interior.ColorIndex = 14
Cells(39, 14).Interior.ColorIndex = 15
Cells(39, 15).Interior.ColorIndex = 16
Cells(39, 16).Interior.ColorIndex = 17
Cells(39, 17).Interior.ColorIndex = 18
Cells(39, 18).Interior.ColorIndex = 19
Cells(39, 19).Interior.ColorIndex = 20
Cells(40, 10).Interior.ColorIndex = 21
Cells(40, 11).Interior.ColorIndex = 22
Cells(40, 12).Interior.ColorIndex = 23
Cells(40, 13).Interior.ColorIndex = 24
Cells(40, 14).Interior.ColorIndex = 25
Cells(40, 15).Interior.ColorIndex = 26
Cells(40, 16).Interior.ColorIndex = 27
Cells(40, 17).Interior.ColorIndex = 28
Cells(40, 18).Interior.ColorIndex = 29
Cells(40, 19).Interior.ColorIndex = 30
Cells(41, 10).Interior.ColorIndex = 31
Cells(41, 11).Interior.ColorIndex = 32
Cells(41, 12).Interior.ColorIndex = 33
Cells(41, 13).Interior.ColorIndex = 34
Cells(41, 14).Interior.ColorIndex = 35
Cells(41, 15).Interior.ColorIndex = 36
Cells(41, 16).Interior.ColorIndex = 37
Cells(41, 17).Interior.ColorIndex = 38
Cells(41, 18).Interior.ColorIndex = 39
Cells(41, 19).Interior.ColorIndex = 40
Cells(42, 10).Interior.ColorIndex = 41
Cells(42, 11).Interior.ColorIndex = 42
Cells(42, 12).Interior.ColorIndex = 43
Cells(42, 13).Interior.ColorIndex = 44
Cells(42, 14).Interior.ColorIndex = 45
Cells(42, 15).Interior.ColorIndex = 46
Cells(42, 16).Interior.ColorIndex = 47
Cells(42, 17).Interior.ColorIndex = 48
Cells(42, 18).Interior.ColorIndex = 49
Cells(42, 19).Interior.ColorIndex = 50
U prvom segmentu su inicijalizirane varijable boja1 do boja4 i bijela i to uzimanjem vrijednosti iz odgovarajućih polja excel dokumenta.
U drugom segmentu su u odgovarajuća polja excel dokumenta upisane vrijednosti od 1 do 50 koje će predstavljati vrijednosti boja za izbor u tabeli sa uzorcima. Izvršio sam inicijalizaciju pri pokretanju dokumenta da bi eliminisao eventualne ručne izmjene vrijednosti u samom dokumentu.
U donjem lijevom dijelu okvira prikazana je tabela za izbor boja pojedinih segmenata grafikona sa naslovom Izbor boja za pojedine grafikone (upis vrijednosti od 1 do 50). Pogledaj primjere ispod. U toj tabeli treba upisati vrijednosti za boje kojim će biti obojeni segmenti grafikona iznad. Vrijednosti boja je moguće pronaći u tabeli uzoraka desno. Upisom željene vrijednosti i klikom u neku drugu ćeliju pokreće se procedura uzmiBoje koja 'pročita' unesene vrijednosti i omogućava njihovu primjenu na grafikonu.
Procedura uzmiBoje je sljedeća:
Public Sub uzmiBoje()
'Uzimanje izabranih boja
boja1 = Cells(38, 3).Value
boja2 = Cells(39, 3).Value
boja3 = Cells(40, 3).Value
boja4 = Cells(41, 3).Value
bijela = 2
End Sub
Promjenom selektovane ćelije se izvršava sljedeća procedura koja prvo ofarba odgovarajuću vrstu u tabeli sa vrijednostima, a zatim i segment na grafikonu:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Uzimanje izabranih boja iz tabele boja
uzmiBoje
'Promjena boje kvadrata do polja za unos broja boje
Cells(38, 5).Interior.ColorIndex = Cells(38, 3).Value
Cells(39, 5).Interior.ColorIndex = Cells(39, 3).Value
Cells(40, 5).Interior.ColorIndex = Cells(40, 3).Value
Cells(41, 5).Interior.ColorIndex = Cells(41, 3).Value
'Promjena boje odgovarajuće vrste u tabeli
For kolone = 3 To 27
For vrste = 27 To 30
'Promjena aktivne boje
If vrste = 27 Then
'Uzimanje vrijednosti iz ćelije C38
boja = Cells(38, 3).Value
End If
If vrste = 28 Then
'Uzimanje vrijednosti iz ćelije C39
boja = Cells(39, 3).Value
End If
If vrste = 29 Then
'Uzimanje vrijednosti iz ćelije C40
boja = Cells(40, 3).Value
End If
If vrste = 30 Then
'Uzimanje vrijednosti iz ćelije C41
boja = Cells(41, 3).Value
End If
'Farbanje ćelija u tabeli izabranom bojom
Cells(vrste, kolone).Interior.ColorIndex = boja
Next vrste
Next kolone
'Bojenje segmenata grafikona
obojiSegmente
'Upis teksta u zaglavlje tabele za unos boja (na osnovu ručno upisanog teksta u zaglavlje tabele)
Cells(38, 2).Value = "Boja za " & Cells(27, 2).Value & "="
Cells(39, 2).Value = "Boja za " & Cells(28, 2).Value & "="
Cells(40, 2).Value = "Boja za " & Cells(29, 2).Value & "="
Cells(41, 2).Value = "Boja za " & Cells(30, 2).Value & "="
'Upis teksta u zaglavlje grafikona (na osnovu ručno upisanog teksta u zaglavlje tabele)
Cells(8, 2).Value = Cells(27, 2).Value
Cells(10, 2).Value = Cells(28, 2).Value
Cells(12, 2).Value = Cells(29, 2).Value
Cells(14, 2).Value = Cells(30, 2).Value
End Sub
Procedura obojiSegmente vrši farbanje/brisanje segmenata na grafikonu nakon dvoklika na segment u tabeli ispod grafikona.
Public Sub obojiSegmente()
'Promjena boje segmenta na grafikonu nakon dvoklika
For kolone = 3 To 28
For vrste = 27 To 30
'Promjena boje
If vrste = 27 Then
boja = boja1
pomak = 0
End If
If vrste = 28 Then
boja = boja2
pomak = 1
End If
If vrste = 29 Then
boja = boja3
pomak = 2
End If
If vrste = 30 Then
boja = boja4
pomak = 3
End If
If Cells(vrste, kolone).Value = 1 Then
Cells((vrste - 19 + pomak), kolone).Interior.ColorIndex = boja
Else
Cells((vrste - 19 + pomak), kolone).Interior.ColorIndex = bijela
End If
Next vrste
Next kolone
End Sub
Farbanje/brisanje segmenata grafikona se vrši nakon dvoklika na polje u tabeli i to izvršavanjem procedure Worksheet_BeforeDoubleClick:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Uzimanje izabranih boja iz tabele boja
uzmiBoje
'Promjena vrijednosti u tabeli nakon dvoklika
If Target.Column > 2 And Target.Column < 29 Then
If Target.Row > 26 And Target.Row < 31 Then
If Target.Value = 1 Then
Target.Value = 0
Else
Target.Value = 1
End If
End If
End If
'Bojenje segmenata grafikona
obojiSegmente
End Sub
To su bile sve VB procedure kreirane u ovom excel dokumentu koje obavljaju poslove iscrtavanja gantovog dijagrama pomoću VBScript-a.
Važno je napomenuti da su polja u excel dokumentu apsolutno adresirana što znači da nije dozvoljeno dodavanja/oduzimanje vrsta/kolona unutar uokvirenog dijela dokumenta jer će biti poremećen sistem uzimanja i upisa vrijednosti.
Kreirane skripte možete i poboljšati, ubrzati, popraviti i slično po svojoj želji, odvisno o vašem poznavanju VBScript-a ili ovaj dokument jednostavno možete iskoristiti za poičetak učenja VBScript-a.
Ako imate bilo kakve prijedloge, sugestije ili ideje, budite slobodni da ih podijelite sa svima u komentarima ispod.