Pobarvajmo vikende

Decembra in januarja vsi hitimo s pripravami letnih koledarjev, planov in podobnih zadevščin. Zadeve se lahko lotimo na več načinov, od katerih je meni najljubši tale, malce bolj lenobni. Postopek ja zelo preprost, hiter in mogoče celo uporaben.
Odpremo nov excelov dokument, v celico A1 vpišemo začetni datum (v mojem primeru 1.1.2012).

Zopet se postavimo v celico A1, kliknemo Polnilo in izberemo Nizi.

Odpre se nam novo okno, kjer izberemo Nizi v: Stolpce, Vrsta niza: Datumski, Enota datuma: Dan. Vrednost koraka nam avtomatsko ponudi 1, preostane nam samo še, da vnesemo končni datum v polje Ustavitvena vrednost.

Kliknemo V redu in gremo Excel nam samodejno zapolne stolpec A.

Sledi še oblikovanje stolpca oziroma "barvanje vikendov". Kliknemo na črko A, da izberemo stolpec A.

Kliknemo na Pogojno oblikovanje in izberemo Novo pravilo...

V okencu, ki se nam odpre, izberemo Uporabi formulo za določanje celic za oblikovanje.

V polje Oblikuj vrednosti, kjer velja ta formula vnesemo =WEEKDAY(A1;2)>5

Določimo obliko. V mojem primeru sem izbral samo rdeče polnilo.

Kliknemo samo V redu in rešeno.

Pogojno oblikovanje

Pogojno oblikovanje je zelo uporabne zadeva, ker lažje preletimo podtke, ki so lepo barvno urejeni. V Office 2007 najdemo Pogojno oblikovanje na kartici osnovno.

Predpostavimo, da imamo urejene podatke kot so na spodnji sliki:

V teh podatkih želimo označiti podvojene vrednosti ter jih obarvati.
Glede na to, da so vrednosti res pravilno urejene (ime, priimek, naslov, poštna številka in pošta), lahko dodamo nov stolpec "Preverka" in v celico F2 vnesemo formulo "=CONCATENATE(A2;B2;C2;D2;E2)". Dvokliknemo na rob celice (odebeljen kvadratek), da s formulo zapolnemo celice do F7.

Nato označimo stolpec F in na kartici "Osnovno" izberemo "Pogojno oblikovanje ->Pravila za označevanje celic -> Podvojene vrednosti".

 

Podvojene vrednoti v stolpcu F se obarvajo: "svetlo rdeče polnilo s temno rdečim besedilom". Lahko pa izberemo tudi svoje nastavitve za barvo, polnilo,...

Glede na to, da so celice obarvane glede na formulo in ne kot oblikovanje tabele, ne moremo uporabiti barve pisave ali celice (iz prejšnjega posta), lahko pa vseeno dodatno označimo celico (v mojem primeru so hoteli, da imajo podvojene vrednosti v stolpcu G dodano *). 

To storimo s pomočjo filtrov, ki jih vklopimo na kartici "Osnovno" (-> Razvrsti in filtriraj -> Filter)

 

 V prvi vrstici se poleg besedila prikaže kvadrate s puščico navzdol. V stolpcu F kliknemo na puščico, izberemo "Filtriraj po barvi" in nato "Filtriraj po barvi celice" (v našem primeru lahko tudi po barvi besedila).

Na zaslonu ostanejo vidne samo tiste celice, ki imajo svetlo rdeče polnilo. V celico G2 vpišemo *, dvokliknemo črn kvadratek, da zapolnemo še preostale celice.

Izklopimo filter in dobimo končni rezultat.

Kaksne barve sta besedilo in celica?

Excel vsebuje ziljon funkcij, ampak včasih si res zaželimo, da bi imeli čisto svojo. Potrebujemo samo idejo in 3 minute časa. Zaženemo Excel, pritisnemo uporabno kombinacijo Alt+F11 in smo v VBA-ju. Vstavimo nov Modul in namesto Sub karneki(), vpišemo Function karneki().

Današnji funkciji sta nastali zaradi čudne excelove tabele v kateri so bile celice z različnimi barvami polnil in različnimi barvami besedila. In ker mi seveda ni padlo nič pametnega na misel, sem si filtriranje zamislil po svoje.
Funkciji sta:

Function txtColor(rng As Range)

'funkcija ki vrne številko barve besedila
    txtColor = rng.Font.ColorIndex
End Function

Function backColor(rng As Range)

''funkcija ki vrne številko barve polnila
    backColor = rng.Cells.Interior.ColorIndex
End Function

To preprosto vnesemo v VBA in se vrnemo v Excelovo datoteko ter izvedemo preizkus. V celico A2 vnesemo besedilo in ga pobarvamo rdeče, za polnilo pa izberemo rumeno barvo. Nato se postavimo v celico B2 in vpišemo "=txtColor(A2)", kar nam da rezultat 3. V celico C3 vpišemo "=backColor(A2)" in dobimo 6.

VBA AutoFilter

Nov teden, nov problem oziroma nov izziv. Ko ljudi naučis uporabljat Filter (Razvrsti in filtriraj -> Filter), začnejo takoj razgljabljat, da je v nekaterih tabelah preveč podatkov, preveč klikanja, preveč napak. Skratka vsega je preveč. Vse to je pripeljajo da ideje o preprostem Obrazcu (UserForm), kjer bi uporabnik vnesel pogoje za filtriranje, pritisnik OK in stvar bi delovala.

Najprej potrebujemo sestavine:
- 1x UserForm (UserForm1)
- 2x TextBox (txtFrakcija in txtNaselje)
- 2x Label (Frakcija in Naselje)
- 1x Button (cmdIsci)

Naredimo obrazec

Ko smo z izgledom obrazca približno zadovoljni, dvakrat kliknemo na gum išči in odpre se nam urejevalnik kode. Vnesemo nekaj preprostih vrstic:

Private Sub cmdIsci_Click()
    Selection.AutoFilter
        ActiveSheet.Range("$A$1:$E$631").AutoFilter Field:=2, Criteria1:="*" & txtFrakcija.Value & "*"
        ActiveSheet.Range("$A$1:$E$631").AutoFilter Field:=4, Criteria1:="*" & txtNaselje.Value & "*"
End Sub

Še kratka razlaga. Na aktivnem listu, kjer imamo seveda vklopljen Filter, lahko filtriramo podatke po 2. in 4. stolpcu. Poleg tega sem dodal še "*" na začetek in konec.

Na excelov delovni list sem dodal še gumb za zagon obrazca in ga povezal z:

Sub odpri()
    UserForm1.Show
End Sub


Zanimivo je, da lahko uporabimo txtFrakcija.Value ali txtFrakcija.Text. V obeh primerih stvar deluje.

Weeknum

Vsi vemo, da ima leto 52 tednov, ko pa ugotavljamo v katerem tednu se je kaj zgodilo smo pa malce zmedeni. V Excelu lahko uporabimo funkcijo WEEKNUM(serijska_številka; vrsta rezultata), ki nam vrne številko tedna.

Funkcija ima dva argumenta:
- serijska številka
- vrsta rezultata

Serijska številka je datum, ki ga zapišemo kot DATE(leto;mesec;dan), vrsta rezultata pa nam pove s katerim dnem hočemo, da se začne teden. Privzeto je to nedelja (1). Lahko pa določimo tudi katerikoli drugi dan

1 ali izpuščeno -> nedelja
2 ->   ponedeljek
11 -> ponedeljek
12 -> torek
13 -> sreda
14 -> četrtek
15 -> petek
16 -> sobota
17 -> nedelja
21 -> ponedeljek

 
Primeri:

V celico A1 napišemo datum. V celico A2 napišemo
=WEEKNUM(A1;2)

In dobim številko tedna v letu ob upoštevnju, da se teden začne s ponedeljkom.

Lahko napišemo tudi:
=WEEKNUM(DATE(leto;mesec;dan), npr.

=WEEKNUM(DATE(2011;9;1);2)


Za izpis trenutne tedna v letu, pa kombiniramo uporabo funkcij WEEKNUM, DATE in NOW



=WEEKNUM(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW()));2)

Bližnjice

To pa sploh ne bo objava, ampak si bom sproti beležil bližnjice, ki so mi zanimive/uporabe:

Ctrl+R -> ustvari tabelo

Ctrl+Shift+% -> oblikovanje kot število z %, brez decimalk

Ctrl+- -> Pobriši (stolpec, vrstico,...)

Desni klik na list, "Izberi vse", Ctrl+F -> Išče podatek na vseh listh

Barvanje presečišča

Veliko ljudi uporablja Excel le za kratke izračune, izdelavo računov, ipd. Vendar je program namenjen delu z veliko količino podatkov, kar privede do velike zmede na zaslonu. Velikokrat ne vemo kam določen podatek spada. V takem primeru je zelo uporabna naslednja kratka koda:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub


    Application.ScreenUpdating = False
    Cells.Interior.ColorIndex = 0
    
    With Target
        .EntireColumn.Interior.Color = vbCyan
        .EntireRow.Interior.Color = vbCyan
    End With
    
    Application.ScreenUpdating = True


End Sub


Kodo skopiramo (Ctrl+C), v delovnem zvezku kliknemo z desno miškino tipko na zavihek lista, v katerem jo želimo uporabiti in izberemo opcijo "Ogled kode". 

Odpre se urejevalnik VBA, kamor prilepimo zgornjo kodo (Ctrl+V) in okno zapremo (Alt+Q).

Ko sedaj kliknemo v celico, se na zaslonu obarva križ, katerega presečišče je izbrana celica.