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.