Selitev

Pozdravljen bralec, no pozdravljena oba bralca tega bloga. Obveščam vaju, da sem svoje nebuloze preselil na naslov http://komunalc.net. Mogoče bo nov naslov pripomogel k aktivnejšemu in kakovostnejšemu pisanju.

Združevanje tabel

Današnji primer temelji na izmišljenih podatkih, izmišljenem primeru in je dejansko cel izmišljen. Le rešitev je prava in mogoče celo uporabna. Imamo eno excelovo datoteko z dvema listoma. Na prvem so podatki o osebah (šifra, ime in priimek), na drugem listu so naslovi teh oseb (šifra, ulica, poštna številka in pošta). Cilj je združitev podatkov iz obeh tabel v eno tabelo, ki jo bomo uporabili za..... npr. pošiljanje pošte.

Imamo torej datoteko (v mojem primeru query.xlsx), z dvema delovnima listoma. Na prvem delovnem listu imamo torej podatke o osebah

Na drugem listu pa podatke o naslovih:

Šifra je tisti podatek, ki povezuje osebo in njen naslov.

Odpremo nov excelov dokument, gremo na zavihek Podatki, izberemo Iz drugih virov in kliknemo na Iz Microsoft Querya. Odpre se nam novo okno, kjer kot vir podatkov izberemo Excel files in kliknemo V redu.

Poiščemo datoteko (query.xlsx), kjer imamo shranjene podatke o osebah in zopet kliknemo V redu. Velikokrat se zgodi, da se nam tabeli ne prikažeta:

V tem primeru kliknemo Možnosti in dodamo kljukico pri Sistemske tabele in kliknemo V redu.

Sedaj vidimo oba delovna lista v oknu Razpoložljive tabele in stolpci. Izbrana polja oziroma v mojem primeru kar obe tabeli, s pomočjo puščic, prenesemo v polje Stolpci v poizvedbi in kliknemo Naprej.

Čarovnik nas opozori, da tabel v poizvedbi ne more samodejno združiti, lahko pa to storimo kasneje, zato samo kliknemo v redu in s tem dokončno odpremo MS Query. V zgornjem oknu vidimo obe tabeli z imeni stolpcev, v spodnji pa vsa polja.

 Podatki seveda še niso urejeni. To storimo tako, da kliknemo na polje sifra v prvi tabele in ga potegnemo na polje sifra v drugi tabeli.

S tem smo uredili podatke. Lahko jih še filtriramo, združujemo po drugih kriterijih,... V našem primeru smo zadovoljni in lahko kliknemo na "vrata s puščico" (Vrni podatke) ali na Datoteka -> Vrni poadatke v Microsoft Office Excel. Excel nas vpraša kam in kako podatke postavi in če smo zadovoljni samo še kliknemo V redu.

In zadeva je končana.

Brisanje praznih celic

Najprej eno majhno priznanje: naravnost obožujem nered, ustvarjalni kaos ali kakorkoli že temu rečemo. Toda točno to ustvarja nesoglasja v tem ljubečem odnosu, ki ga vodiva z excelom. In ker očitno red mora bit, je fino&fajn, če lahko do tega pridemo hitro ter predvsem enostavno.

Primer take uporabe je brisnje praznih celic. Velikokrat se zgodi, da dobimo razne podatke, ki niso vnešeni v vsako vrstico, temveč je med njimi vrstica ali več prostora. Težavo bi lahko reševali z uporabo VBA, vendar nam je tokrat to prihranjeno. Hvala excelovemu bobu, ki je poskrbel za nas.

Predpostavimo, da imamo v stolpcu A nametanih kup podatkov, med katerimi so tudi prazne vrstice, ki se jih želimo znebiti. Najprej označimo cel stolpec in pritisnemo F5. Odpre se nam okno Pojdi na, kjer kliknemo ukaz Posebno.

Izberemo Prazne in kliknemo V redu.

Excel nam označi vse nezapolnjene celice. Sedaj samo še kliknemo na puščico pod ukazom Izbriši, izberemo Izbriši vrstice lista in rešeno.

Shranjevanje delovnih listov

Ljudske modrosti so super simpatična zadeva in ga ni junaka, ki bi jim lahko ubežal. Kako bi jim šele moja malenkost. Zgodilo se je nekako takole. V petek bi moral narediti nekaj poročil, ampak ker petek ni dan, ko bi človek šaril po kupu suhupornih podatkov, sem vso stvar potisnil v čudežni kup, kjer naj bi počakal na lepše čase. Ti so seveda nastopili takoj v ponedeljek zjutraj, ko se je našel nekdo, ki je hotel te podatke takoj na mizo.
Na tem mestu sledi sedaj zahvala bobom internetov, excelov in drugih podobnih kvazimističnočarobnihoseb, ki so mi pomagali.

Sedaj pa, kot je nekako navada, opis dejstev. Obstaja tabela kjer je kup podatkov o izdajnicah določenih artiklov. Vse skupaj samo štirje stolpci: šifra, masa, prevoznik, prevzemnik. Prvi del poročila je bil zelo preprost in zahteva samo malo klikanja.

Postavimo se v tabelo s podatki, kliknemo Vstavljanje in izberemo Vrtilna tabela.Excel nam sam ponudbi obseg podatkov, ki je ponavadi točen. Pogledamo še, če imamo izbrano opcijo Na nov delovni list, ki nam postavi vrtilno tabelo na nov delovni list in kliknemo V redu.

Odpre se nam nov delovni list, kjer imamo seda prazno vrtilno tabelo.

Na desni strani pa se nam pojavijo nazivi stolpcev iz izvorne tabele (polja) in okenca (območja), kamor jih lahko povlečemo. Tam jim tudi spremenimo atribute.

Polje prevzemnik povlečemo v območje Oznake vrstic, nato pod njega povlečemo še polje šifra. V območje Vrednosti povlečemo polje masa. Excel nam ponudi opcijo vsota. Če temu ni tako, kliknemo pa črno puščico in izberemo Nastavitve polja vrednosti... Odpre se nam novo okno, kjer lahko preimenujemo novo ime polja in določimo kaj naj excel počne s podaki v tem polju. Za naš primer, kot sem že predhodno dejal, izberemo Vrsta izračuna -> vsota. S tem smo izdelali vrtilno tabelo, ki vsebuje vse potrebne podatke za nadaljno analizo.

 Seveda to ni bilo dovolj in sem potreboval še podatke posameznih prevzemnikov, kaj so naredili s temi artikli. Zato sem podatke o vsakem prevzemniku kopiral na svoje list in ob tem se mi je utrnila super ideja, da je včasih potrebno različne liste iz ene excelove datoteke shranit vsakega v svojo datoteko. Tukaj pa spet ni šlo brez moje lenobe. Lahko bi preprosto vsak list kopiral v nov delovni zvezek, kopiral še širine stolpcev in vse skupaj rešil na star, peš, način. Poudarek je na lahko, ker tega seveda nisem storil. Najprej sem shranil datoteko z več list, nato pa odprl VBA in napisal zelo preprost modul:

Sub shrani_liste()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:=ws.Name
        ActiveWorkbook.Close
    Next ws
    
End Sub

Subruitna shrani vsak list iz delovnega zvezka (excelove datoteke) v nov delovni zvezek in to v isto mapo, kot je shranjen izvorni delovni zvezek.
Tako, to je to. Zadeva je že zdavnaj poslana naprej in če vam je napisano všeč, se priporočam za...

Sinhronizacija koledarja

Pametni telefoni so dejansko ena zelo pametna napravica. Moj Galaxy S je bil prejšnji teden prepričan, da preveč delam in je preprosto nehal sinhronizirat vse koledarje. Dobival sem samo še informacije o koledarju, ki je vseboval zabavne vsebine. Načeloma me zadeva ni motila, dokler nisem pozabil na nekaj službenih zadev. In ker je služba zelo pomembna, sem sklenil, da se morava z gadgetom pogovoriti na dve oči in zaslon.

Kot je že običaj mu samo resetiranje ni pomagalo, zato sem se poglobil malce globlje. Posumil sem celo rom in ker sem opazil, da je DarkyRom izdal novo različico sem sklenil posodobiti vse skupaj. Telefon ima sedaj nameščen android 2.3.6, kot se lepo vidi na sliki. No, da ne bo kdo mislil, da je koledar začel čudežno delat. Jok brate, odpade.

Ta koledarska zadeva me je tako jezila, da sem si odprl pivo in ob pivu se vedno odpirajo super ideje, ki so zapovrh vsega še strašno preproste. Ta je šla nekako tako:

Settings > Applications > Manage applications > Hit the Menu Button > Filter > All > Calendar Storage > Clear data > OK

Sedaj samo še zaženemo sinhronizacijo in v koledarju se nam pojavijo vsi koledarjih, ki so povezani z našim google računom.

Iz ene v več

Včasih je zabavno reševat izzive, ki pestijo druge. Tokratni je zanimiv problem nastal pri fajn osebi, ki ji sledim na twitterju. Zanimalo ga je, ali se da iz ene datoteke, ki ima 10.000 vrstic s podatki kopirat po 1.000 vrstic v 10 novih excelovh datototek. Seveda bi se vsega skupaj lahko lotil peš in kopiral teh 1.000 vrstic, ampak to je kršenje velikega načela, da je lenoba gibalo napredka. Verjetno je tudo res, da prevelika delavnost ne prinese nobene inovacija, toda o tem kdaj drugič.

Za sam primer sem vzel malo manjšo datoteko, ker se lahko hitro kaj zalomi in potem je potrebno na silo terminirat program. Cel procedura je podobna za kakršnokoli excelovo datoteko, ki jo želimo razdeliti na več novih datotek. Najlažje bo razumeljivo, če prebereš komentarje.

Sub razdeli()
 
    Dim i As Integer
    Dim x As Integer
    Dim wb As Workbook

    'preberemo ime trenutno odprte izvorne datoteke
    template_file = ActiveWorkbook.Name

    'določimo začetek in koliko vrstic hkrati želimo kopirati
    For i = 1 To 30 Step 5
    x = i + 5

        'v izvorni datoteki na zavihku podatki izberemo i število vrstic in 12 stolpcev
        Sheets("podatki").Select
        Range(Cells(i, 1), Cells(x, 12)).Select
        Selection.Copy


        'dodamo nov excelov zvezek
        Workbooks.Add

        'vanj prilepimo kopirane izvorne podatke
        ActiveSheet.Paste

        'vrnemo se v izvorno datoteko, kjer izberemo naslednjih i vrstic
        Windows(template_file).Activate

    Next i
End Sub

To je dejansko to. Ko zaključimo imamo 7 odprtih excelovih zvezkov; izvornega in 6 zaporednih, od katerih vsak vsebuje 5 vrstic podatkov iz izvornega zvezka. Po želji bi lahko dodali še avtomatsko shranjevanje, toda ne smemo pretiravat z delom. Pa še idej za objave mi lahko prehitro zmanjka.

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.

Tweetdeck v Ubuntuju

Navkljub temu, da dandanes uporabnik računalnikov ločimo na jabolkarje in oknarje, se občasno še najdejo ljudje, ki uporabljajo operacijske sisteme temelječe na Linux. In taki s(m)o velikokrat prikrajšani za programe, ki jih v drugih OS z lahkoto namestimo. Kljub strahu, da bo v Ubuntu-ju vse strašno zakomplicirano in neizvedljivo, je nameščanje twitter aplikacije Tweetdeck popolnoma enostavno in zahteva od uporabnika le malce tipkanja oz. prepisovanja.

Program Tweetdeck uporablja izvajalno okolje (runtime) AdobeAIR, ki ga najdemo na naslovu:

http://get.adobe.com/air/

Izberemo datoteko s končnico .bin in jo prenesemo na lokalni računalnik.

Ko je datoteka (AdobeAIRInstaller.bin) prenešena, kliknemo nanjo z desno miškino tipko, izberemo zavihek "Dovoljenja" in obkljukamo "Dovoli izvajanje datoteke kot programa".

Sledi "najzahtevnjši" korak, ki poleg uporabe miške, zahteva še uporabo tipkovnice. Odpremo "Terminal" in se premaknemo v mapo, kamor smo shranil datoteko "AdobeAIRInstaller.bin" in vnesemo naslednja ukaza:

 chmod +x AdobeAIRInstaller.bin

 sudo ./AdobeAIRInstaller.bin

S tem smo namestili izvajalno okolje AdobeAir.

Sedaj se moramo samo še odpraviti na spletni naslov http://tweetdeck.com in prenesti zadnjo različico programa Tweetdeck.

No, malo klikanja je še potrebno, ampak več kot "OK" menda res ne.

Funkcije LEN, LEFT in DATEDIF

Lep deževni pozdrav, slučajnemu mimoidočemu. Uspelo mi je sestavit še eno objavo, pa čeprav sem v teh toplih dneh veliko raje na kolesu po Katarini in okolici, kot pa pred "kišto". No, služba je služba in služba je denar, zato moramo tudi delat. In med delom sem se spomnil, še dveh pametnih zadev.

1. LEFT & LEN
Pa gremo lepo po vrsti. Prva težava, če ji lahko tako rečemo, je bila, ko sem sem moral sešteti porabo prenosa podatkov na mobilnem telefonu. Podatki so zapisani v, za Excel, neuporabni obliki; npr. 180,0MB. Lahko bi sicer preprosto uporabil Ctrl+H (najdi in zamenjaj), ter vse MB zamenjal s praznim besedilom. Toda to nebi bilo tako zanimivo, zato sem raje uporabil funkcijo LEN() in LEFT(). Postopek gre pa nekako tako:
v prvem stolpcu so podatki napisani v zgoraj omenjeni obliki. Decimalna vejica je uporabljena samo ko je decimalka različna od 0. Vedno pa velja, da sta zadnja dva znaka MB. Formula se torej glasi:

=LEFT(A2;LEN(A2)-2)

torej najprej preberemo število znakov v nizu z LEN(A2), odštejemo 2 znaka (MB) in nazadnje preberemo LEN()-2 znaka, šteto z leve strani.


Če nam dobljenega rezultata še vedno noče seštevati, ga označimo, kopiramo in uporabimo ukaz: "Pretvori v število".

2. DATEDIF
Datedif je uporaben predvsem v kadrovski službi in prav od tam je prišlo vprašanje če Excel zna in kako bi se naredilo, da bi iz datuma rojstva določenega zaposlenega izpisalo starost na današnji dan. Rešitev je pravzaprav zelo preprosta, uporabimo funkcijo DATEDIF, malo &, malo "" in to je to.

DATEDIF je dejansko sestavljena tako:
=DATEDIF(Datum1, Datum2, Interval)

V našem primeru jo uporabimo za razliko med danes NOW() in rojstnim datumom. Y, YM in MD uporabimo za leta, mesece in dneve.

Torej v celico D2 smo vnesli rojstni datum 25.6.1991 (Dan državnosti) v celico E2
=DATEDIF(D2;NOW(); "Y") & " let, " & DATEDIF(D2;NOW(); "YM") & " mesecev in " & DATEDIF(D2;NOW(); "MD") & " dni."

in na današnji dan je rezultat: 20 let, 0 mesecev in 10 dni.

VBA in SQL drugi del

Takole, od zadnjič sem celo vso stvar malce nadgradil in sedaj lahko z veseljem povem, da deluje iskanje kot se spodobi. Poleg tega, sem dodal kopiranje podatkov v nov excelov dokument, brisanje rezultatov,... Malce pa sem dodelal tudi sam obrazec za iskanje in sedaj izgleda tako:

Iskalno okno se avtomatsko zažene ob zagonu Excelove datoteke. To storimo z ukazom:


Private Sub Workbook_Open()
    UserForm1.Show
End Sub

Ukaz vnesemo tako, da v VBA-ju dvakrat kliknemo ThisWorkBook v VBAProject in potem iz padnih menijev izberemo: Workbook in Open.

Ostalo kodo vnesemo v obrazec. Izgleda pa tako:


Private Sub CmdIsci_Click()


'spuca vse podatke na listu
List1.Cells.Clear


    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DATABASE=zzzzz;DRIVER={MySQL ODBC 3.51 Driver};OPTION=0;;PORT=0;SERVER=xxx.xxx.x.xx;UID=XXX;PASSWORD=YYY" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
                "SELECT inkasso_2010_om_odvpr_0.OM, inkasso_2010_om_odvpr_0.OM_NAZIV AS 'NAZIV', inkasso_2010_obcine_odvpr_0.OBCINA_NAZIV AS 'OBČINA', inkasso_2010_sif_kraj_sk_odvpr_0.KRAJ_SK_NAZIV AS 'NASELJE', inkas" _
        , _
        "so_2010_sif_ulic_odvpr_0.ULICA_NAZIV AS 'ULICA', CONCAT(inkasso_2010_om_odvpr_0.OM_HS,inkasso_2010_om_odvpr_0.OM_HSD) AS 'Hst', inkasso_2010_om_posode_odvpr_0.POSODE_ODPADEK AS 'FRAKCIJA', inkasso_201" _
        , _
        "0_posode_odvpr_0.POSODA_VOLUMEN AS 'VOL', inkasso_2010_om_posode_odvpr_0.POSODE_FREKVENCA AS 'FREK', inkasso_2010_om_posode_odvpr_0.POSODE_IDENTST AS 'INVENTARNA', inkasso_2010_om_pogodbe_odvpr_0.POGO" _
        , _
        "DBA_STEVILKA AS 'POGODBA'" & Chr(13) & "" & Chr(10) & "FROM sezana.inkasso_2010_obcine_odvpr inkasso_2010_obcine_odvpr_0, sezana.inkasso_2010_om_odvpr inkasso_2010_om_odvpr_0, sezana.inkasso_2010_om_pogodbe_odvpr inkasso_2010_om" _
        , _
        "_pogodbe_odvpr_0, sezana.inkasso_2010_om_posode_odvpr inkasso_2010_om_posode_odvpr_0, sezana.inkasso_2010_posode_odvpr inkasso_2010_posode_odvpr_0, sezana.inkasso_2010_sif_kraj_sk_odvpr inkasso_2010_s" _
        , _
        "if_kraj_sk_odvpr_0, sezana.inkasso_2010_sif_ulic_odvpr inkasso_2010_sif_ulic_odvpr_0" & Chr(13) & "" & Chr(10) & "WHERE inkasso_2010_om_odvpr_0.OM = inkasso_2010_om_posode_odvpr_0.OM AND inkasso_2010_om_odvpr_0.KRAJ_SK_SIFRA = i" _
        , _
        "nkasso_2010_sif_kraj_sk_odvpr_0.KRAJ_SK_SIFRA AND inkasso_2010_om_odvpr_0.ULICA_SIFRA = inkasso_2010_sif_ulic_odvpr_0.ULICA_SIFRA AND inkasso_2010_om_odvpr_0.OM = inkasso_2010_om_pogodbe_odvpr_0.OM AN" _
        , _
        "D inkasso_2010_om_odvpr_0.OBCINA_SIFRA = inkasso_2010_obcine_odvpr_0.OBCINA_SIFRA AND inkasso_2010_om_pogodbe_odvpr_0.POGODBA_STEVILKA = inkasso_2010_om_posode_odvpr_0.POGODBA_STEVILKA AND inkasso_201" _
        , _
        "0_om_posode_odvpr_0.POSODA_SIFRA = inkasso_2010_posode_odvpr_0.POSODA_SIFRA AND ((inkasso_2010_om_pogodbe_odvpr_0.POGODBA_AKTIVNA='T') AND (inkasso_2010_om_posode_odvpr_0.POSODE_ZARACUNLJIVOST=2) AND " _
        , "(inkasso_2010_om_odvpr_0.OM_AKTIVEN='T'))" _
        , _
        " AND inkasso_2010_om_odvpr_0.OM LIKE '%" & UserForm1.TxtOm.Text & "%' AND inkasso_2010_om_odvpr_0.OM_NAZIV LIKE '%" & UserForm1.TxtNaziv.Text & "%' AND inkasso_2010_obcine_odvpr_0.OBCINA_NAZIV LIKE '%" & UserForm1.TxtObcina.Text & "%'", _
        " AND inkasso_2010_sif_kraj_sk_odvpr_0.KRAJ_SK_NAZIV LIKE '%" & UserForm1.TxtNaselje.Text & "%' AND inkasso_2010_sif_ulic_odvpr_0.ULICA_NAZIV LIKE '%" & UserForm1.TxtUlica.Text & "%' AND inkasso_2010_om_odvpr_0.OM_HS LIKE '%" & UserForm1.TxtHisna.Text & "%'", _
        " AND inkasso_2010_om_odvpr_0.OM_HSD LIKE '%" & UserForm1.TxtDod.Text & "%' AND inkasso_2010_om_posode_odvpr_0.POSODE_ODPADEK LIKE '%" & UserForm1.TxtFrakcija.Text & "%' AND inkasso_2010_om_posode_odvpr_0.POSODE_IDENTST LIKE '%" & UserForm1.TxtInventarna.Text & "%' AND inkasso_2010_om_posode_odvpr_0.POGODBA_STEVILKA LIKE '%" & UserForm1.txtPogodba.Text & "%'" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabela_izpis"
        .Refresh BackgroundQuery:=False
    End With
End Sub


Private Sub cmdKopiraj_Click()
        
    ActiveWorkbook.Sheets(1).Activate
    Range("A1:J1, Tabela_izpis").Select
    Selection.Copy
        
    Workbooks.Add
    ActiveWorkbook.Sheets(1).Activate
    ActiveSheet.Cells(1, 1).PasteSpecial
  
End Sub


Private Sub cmdNovo_Click()
    Workbooks("iskanje_sql.xlsm").Activate
End Sub


Private Sub CommandButton1_Click()
    List1.Cells.Clear

End Sub

VBA in SQL

Živimo, baje, v času recesije, zato je potrebno včasih kakšno stvar narediti po težji poti. Ena od takih, se mi je zgodila nekaj dni nazaj. Gre sicer za precej banalen primer, ki bi se ga dalo preprosto rešiti s pomočjo MS Accessa ali pa bi se preprosto programerjem plačalo, da naredijo poročilo v trenutne programu. No, ničesar od tega ni bilo, zato sem se držal reka: "pomagaj si sam in bog ti bo pomagal".

Opis problema

Program za vodenje katastrov temelji na bazi MySql, vendar nima pripravljenih določenih izpisov. Z uporabo Excela lahko sicer naredimo query, a se pri malo manj ukih uporabnikih zatakne pri nastavljanju parametrov izpisa. Navadno pride do tega, da kliknejo na bližnjico za zagon poizvedbe in potem pobrišejo vse nepotrebne vrstice (mogoče celo stolpce). Tukaj pa nam pride hitro v pomoč VBA.

Celotna skripta še ni dodelana, ampa za prvo silo deluje. Sestavljena je iz preprostega obrazca s tremi besedilnimi polji (TextBox) in dveh gumbov.

Nato pa seveda dodamo še malo besedila, ki bo naredilo nekaj pametnega.

Za gum "Išči" sledi naslednje:


Private Sub CommandButton1_Click()


'Spucamo vse podatke na delovnem listu
List1.Cells.Clear


'povezemo se z MySQL bazo
'ce dopisemo ;PASSWORD=xxxxxx;, ne rabimo vpisovat gesla po zagonu skripte
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DATABASE=komunala;DRIVER={MySQL ODBC 3.51 Driver};OPTION=0;;PORT=0;UID=danijel;" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT bass_pogodbe_0.ID, bass_pogodbe_0.OBCINA_SIFRA, bass_pogodbe_0.OBCINA_NAZIV, bass_pogodbe_0.KRAJ_SK_SIFRA, bass_pogodbe_0.KRAJ_SK_NAZIV, bass_pogodbe_0.ULICA_SIFRA, bass_pogodbe_0.ULICA_NAZIV, " _
        , _
        "bass_pogodbe_0.OM_HS, bass_pogodbe_0.OM_HSD, bass_pogodbe_0.OM, bass_pogodbe_0.OM_NAZIV, bass_pogodbe_0.OM_PLACNIK, bass_pogodbe_0.NAZIV, bass_pogodbe_0.NASLOV, bass_pogodbe_0.PTT, bass_pogodbe_0.KRAJ" _
        , _
        ", bass_pogodbe_0.POGODBA_STEVILKA" & Chr(13) & "" & Chr(10) & "FROM komunala.bass_pogodbe bass_pogodbe_0" & Chr(13) & "" & Chr(10) & _


'pa se pogoji, ki se nanasajo na textboxe
        "WHERE (bass_pogodbe_0.ULICA_NAZIV LIKE '%" & UserForm1.txtUlica.Text & "%' AND bass_pogodbe_0.OBCINA_SIFRA LIKE '%" & UserForm1.txtObcina.Text & "%' AND bass_pogodbe_0.OM_NAZIV LIKE '%" & UserForm1.txtOmNaziv.Text & "%' )")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabela_Poizvedba_iz_mysql"
        .Refresh BackgroundQuery:=False
    End With


End Sub

Z drugim gumbom natisnemo vsebino lista:


Private Sub CommandButton2_Click()

     With ActiveSheet.PageSetup

        .PrintTitleRows = "$1:$1"

        .PrintTitleColumns = ""

    End With

Application.Dialogs(xlDialogPrint).Show

End Sub

To je nekako osnovno, kar je bilo narejeno. Po željah in zmožnostih, bomo pa dodajali tudi nove stvari.

Obrazec za vnos

Excel je zelo uporaben ko se soočimo z vnosom podatkov. Uporabimo lahko vgrajen obrazec, lahko pa si seveda izdelamo svojega. Vse skupaj je zelo preprosto in velikokrat nam precej olajša delo.

Kot vedno zaženemo VBA (Alt+F11) in vstavimo obrazec (Insert -> UserForm). Najprej se nam prikaže samo ozadje obrazca, ki ga oblikujemo po želji; vstavimo Tekstovna polja (textbox), gumbe (button), Imena (label),...

V naslednjem primeru je prikazana izdelava obrazca za vnos podatkov o prometni signalizaciji v določenem naselju.

Sama oblika obrazca je dokaj preprosta in pregledna, ker se mi ni dalo preveč ukvarjati z lepotičenjem.

Ko zaključimo z oblikovanjem obrazca, pa je potrebno dodati tudi nekaj programske kode, ki poskrbi, da postane obrazec uporaben. V oknu Project Explorer (na zgornji sliki je to levo zgornje okno) poiščemo naš obrazec (UserForm1), kliknemo nanj z desno tipko in izberemo View Code.

Vpišemo kodo, ki naj se izvede:

Private Sub Initialize()
'ob zagonu pobrišemo vsa polja v obrazcu.
    txtOdsek.Value = ""
    txtOznaka.Value = ""
    txtNaziv.Value = ""
    txtStacionaza = ""
    txtVisina = ""
    txtOpomba = ""
End Sub

Private Sub cmdIzhod_Click()
'gumb za izhod zapre obrazec
    Unload Me
End Sub

Private Sub cmdVnos_Click()
'ko kliknemo na gumb vnos, naj se vsebina obrazca vnese v prvo prazno vrstico
'na  delovnem listu "podatki"
          
    ActiveWorkbook.Sheets("podatki").Activate

    Range("A1").Select

    Do

    If IsEmpty(ActiveCell) = False Then

        ActiveCell.Offset(1, 0).Select

    End If

    Loop Until IsEmpty(ActiveCell) = True

    ActiveCell.Value = txtOdsek.Value

    ActiveCell.Offset(0, 1) = txtOznaka.Value

    ActiveCell.Offset(0, 2) = txtNaziv.Value

    ActiveCell.Offset(0, 3) = txtStacionaza.Value
  
    ActiveCell.Offset(0, 4) = txtVisina.Value
  
    ActiveCell.Offset(0, 5) = txtOpomba.Value

  
    'Range("A1").Select
    ActiveCell.Offset(1, 0).Select
End Sub

In zaženemo s tipko F5 ali pa kliknemo na  Run -> Run Sub/User Form.

Povprečje, min, max

Danes pa nekaj, kar sploh ni bilo narejeno s pomočjo VBA. Gre za kombinacijo uporaba funkcije IF in AVERAGE.

Težava, ki je iskala rešitev, je bila v tem, da so podatki lahko v 1 ali pa več stolpcev. Ker je maksimalno število stolpcev znano, sem vse skupaj naredil bolj po partizansko; samo da dela.

Glava poročila je vedno enaka in v celicah D26 do M26 je potrebno izračunat povprečje vrednosti. Ampak. Ne vemo koliko vrstic podatkov, pa tudi vsi stolpci nimajo vedno podatkov (senzor mrtev). Torej vedno preverimo, če je v dotični celici sploh zapisana vrednost, izračunamo povprečje za tisto število celic, ki imajo številske podatke. Če pa podatkov ni, se v poročilni tabeli izpiše "Ni podatkov".

=IF(N30<>0;AVERAGE(N30:(INDEX(N30:N27201; COUNT(N30:N27201))));"Ni podatkov")

Vse skupaj je dejansko preprosto kot znana srbska nacionalna jed.

Odpiranje, zapiranje, rangi,...

Naslov je čuden, pa naj bo še vsebina. Sicer nič posebnega, mi je pa vzelo nekaj uric prijetnega dela.

Začetna želja je bila, da iz podatkov nekih sond (10 temperaturnih sond), naredimo poročilo, ki vsebuje minimalno, maksimalno in povprečno vrednost odčitka ter seveda nariše graf. Nič posebnega, lahko se naredi tudi "na roke". Toda življenje ni zanimivo, če ne malo kompliciramo.

V spodnjem nakladanju manjka samo še malo lepote. V izdelku je seveda še datoteka, z logotipom, opisom, gumbom za zagon...

No, da še malo opišem, kaj sploh je to. Ko zaženemo proceduro, se odpre okno za odpiranje datoteke (s podatki), odpremo datoteko in vpraša nas po območju, ki vsebuje potrebne podatke. Označimo, kliknemo OK in že sledi drugo odpiranje datoteke. Tokrat izberemo datoteko, ki vsebuje vzorec poročila in nekaj malega funkcij (min, max, average, if...malce, nam popravi izgled). V to datoteko se kopirajo podatki, ki smo jih izbrali v prejšnji datoteki. Nato procedura še nariše graf, nas vpraša kam želimo shraniti novo poročilo ter zapre datoteko s podatki in datoteko z vzorčnim poročilom.

Sub vnos()

Dim sPorocilo As String
Dim sPodatki As String
Dim wbPodatki As Variant


'odpremo datoteko s podatki

sPodatki = Application.GetOpenFilename(fileFilter:="Excel FIles (*.xls), *.xls", _
                                        Title:="Prosim izberi datoteko s podatki")
            If sPodatki = "False" Then Exit Sub
          
Workbooks.Open Filename:=sPodatki
wbPodatki = Application.ActiveWorkbook.Name

 
'V datoteki s podatki iz termometrov izberem obseg podatkov: od ure do zadnjega temperaturnega senzorja 'oz po želji

Dim rRange As Range

On Error Resume Next
    Application.DisplayAlerts = False

    Set rRange = Application.InputBox(Prompt:="Prosim izberi obseg vhodnih podatkov", _
    Title:="Določi obseg", Type:=8)

On Error GoTo 0

    Application.DisplayAlerts = True

    If rRange Is Nothing Then
        Exit Sub

    Else
        'podatke kopiramo
        rRange.Copy

    End If

'Odpiranje izbrane xls datoteke z vzorcem porocila

sPorocilo = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls), *.xls", _
                                        Title:="Prosim izberi datoteko z vzorcem poročila")
    If sPorocilo = "False" Then Exit Sub

Workbooks.Open Filename:=sPorocilo

'aktiviramo delovni zvezek in nato delovni list ter skočimo v celico C30
ActiveWorkbook.Sheets("podatki").Activate
ActiveSheet.Cells(30, 3).Activate

'prilepimo podatke
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


'pobrišemo odložišče
Application.CutCopyMode = False


'narišemo graf
Dim povp As Range
Dim ura As Range
Dim vrstica As Integer
Dim vrs As String
Dim vrsx As String


ActiveWorkbook.Sheets("podatki").Activate

vrstica = WorksheetFunction.Count(Range("D30", "D25000")) + 29
vrs = "N" & vrstica
vrsx = "O" & vrstica

Set povp = Range("N30", vrs)
Set ura = Range("O30", vrsx)

    ActiveWorkbook.Sheets("podatki").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
'seveda bo grafikon poimenovan grafikon in bo na svojem listu
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Grafikon"
    ActiveWorkbook.Charts("Grafikon").Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "='podatki'!$N$29"
    ActiveChart.SeriesCollection(2).Values = povp
    ActiveChart.SeriesCollection(2).XValues = ura
    ActiveChart.SeriesCollection(1).Delete

'in shranimo datoteko

   fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Excel Files (*.xls), *.xls")

    If fileSaveName = False Then
        Exit Sub
    End If

' Shrani datoteko v format xls
ActiveWorkbook.SaveAs Filename:= _
        fileSaveName, FileFormat:=xlExcel8, _
        CreateBackup:=False

'lepo je, če uporabniku kaj povemo
    file_name_saved = ActiveWorkbook.FullName
    MsgBox "Datototeka je shranjena: " & vbCr & vbCr & file_name_saved

'zapremo datoteko z vzorčnim poročilom in s podatki
    ActiveWorkbook.Close
    Workbooks(wbPodatki).Close


End Sub