sadržaj
Mnogi poslovni procesi (pa čak i čitavi poslovi) u ovom životu podrazumevaju ispunjavanje naloga ograničenog broja izvođača do određenog roka. Planiranje se u takvim slučajevima dešava, kako kažu, „iz kalendara“ i često postoji potreba da se planirani događaji u njemu (porudžbine, sastanci, isporuke) prenesu u Microsoft Excel – radi dalje analize po formulama, pivot tabelama, grafikonima, itd.
Naravno, želio bih da implementiram takav prijenos ne glupim kopiranjem (što jednostavno nije teško), već automatskim ažuriranjem podataka kako bi se u budućnosti sve promjene u kalendaru i nove narudžbe u hodu prikazivale u Excel. Takav uvoz možete implementirati za nekoliko minuta pomoću dodatka Power Query ugrađenog u Microsoft Excel, počevši od verzije 2016. (za Excel 2010-2013, može se preuzeti s Microsoft web stranice i instalirati odvojeno s linka) .
Pretpostavimo da koristimo besplatni Google kalendar za planiranje, u kojem sam, radi praktičnosti, napravio poseban kalendar (dugme sa znakom plus u donjem desnom uglu pored Ostali kalendari) sa naslovom rad. Ovdje unosimo sve narudžbe koje je potrebno izvršiti i isporučiti kupcima na njihove adrese:
Dvoklikom na bilo koju narudžbu možete pogledati ili urediti njene detalje:
Zapiši to:
- Naziv događaja je menadžerko ispunjava ovu naredbu (Elena) i Broj narudžbe
- Navedeno adresa isporuka
- Bilješka sadrži (u zasebnim redovima, ali bilo kojim redoslijedom) parametre narudžbe: vrstu plaćanja, iznos, ime kupca itd. u formatu Parametar=Vrijednost.
Radi jasnoće, nalozi svakog menadžera su istaknuti svojom bojom, iako to nije neophodno.
Korak 1. Nabavite link do Google kalendara
Prvo moramo dobiti web vezu do našeg kalendara narudžbi. Da biste to učinili, kliknite na dugme sa tri tačke Opcije kalendara rade pored naziva kalendara i izaberite komandu Postavke i dijeljenje:
U prozoru koji se otvori možete, po želji, učiniti kalendar javnim ili otvoriti pristup njemu za pojedinačne korisnike. Također nam je potreban link za privatni pristup kalendaru u iCal formatu:
Korak 2. Učitajte podatke iz kalendara u Power Query
Sada otvorite Excel i na kartici podaci (ako imate Excel 2010-2013, onda na kartici power query) izaberite komandu Sa interneta (Podaci — sa interneta). Zatim zalijepite kopiranu putanju do kalendara i kliknite OK.
iCal Power Query ne prepoznaje format, ali je lako pomoći. U suštini, iCal je običan tekstualni fajl sa dvotočkom kao graničnikom, a unutra izgleda otprilike ovako:
Dakle, možete samo kliknuti desnim klikom na ikonu preuzete datoteke i odabrati format koji je po značenju najbliži CSV – i naši podaci o svim narudžbama bit će učitani u Power Query uređivač upita i podijeljeni u dvije kolone dvotočkom:
Ako pažljivo pogledate, jasno možete vidjeti da:
- Informacije o svakom događaju (narudžbi) grupišu se u blok koji počinje riječju BEGIN i završava se sa END.
- Vrijeme početka i završetka pohranjeno je u nizovima označenim DTSTART i DTEND.
- Adresa za dostavu je LOCATION.
- Napomena o narudžbi – polje OPIS.
- Naziv događaja (ime menadžera i broj naloga) — polje SAŽETAK.
Ostaje izdvojiti ove korisne informacije i pretvoriti ih u prikladnu tablicu.
Korak 3. Pretvorite u normalan prikaz
Da biste to učinili, izvršite sljedeći lanac radnji:
- Izbrišemo prvih 7 redova koji nam nisu potrebni prije prve naredbe BEGIN Početna — Izbriši redove — Izbriši gornje redove (Početna — Ukloni redove — Ukloni gornje redove).
- Filtrirajte po koloni Column1 linije koje sadrže polja koja su nam potrebna: DTSTART, DTEND, OPIS, LOKACIJA i SAŽETAK.
- Na kartici Napredno Dodavanje kolone izabrati Indeksni stupac (Dodaj kolonu — Indeksni stupac)da dodate kolonu sa brojem reda našim podacima.
- Tamo na kartici. Dodavanje kolone izabrati tim Uslovna kolona (Dodaj kolonu — Uslovna kolona) a na početku svakog bloka (reda) prikazujemo vrijednost indeksa:
- Popunite prazne ćelije u rezultujućoj koloni Blokdesnim klikom na njen naslov i odabirom naredbe Ispuna – Dolje (Popuni — dolje).
- Uklonite nepotreban stupac indeks.
- Odaberite kolonu Column1 i izvrši konvoluciju podataka iz kolone Column2 koristeći naredbu Transformacija – zaokretna kolona (Transformacija — zaokretni stupac). Obavezno odaberite u opcijama Nemojte agregirati (Nemoj agregirati)tako da se nijedna matematička funkcija ne primjenjuje na podatke:
- U rezultujućoj dvodimenzionalnoj (unakrsnoj) tabeli, obrišite obrnute kose crte u koloni adrese (desni klik na zaglavlje kolone – Zamjena vrijednosti) i uklonite nepotreban stupac Blok.
- Za okretanje sadržaja kolona DTSTART и DTEND u punom datumu, označavajući ih, odaberite na kartici Transformacija – Datum – Pokreni analizu (Transformacija — datum — raščlanjivanje). Zatim ispravljamo kod u traci formule zamjenom funkcije Datum.Od on DateTime.Fromkako ne bi gubili vremenske vrijednosti:
- Zatim, desnim klikom na zaglavlje, dijelimo kolonu OPIS sa parametrima narudžbe po separatoru – simbolu n, ali ćemo istovremeno u parametrima odabrati podjelu na redove, a ne na stupce:
- Još jednom, rezultujuću kolonu dijelimo na dva odvojena - parametar i vrijednost, ali znakom jednakosti.
- Odabir kolone OPIS.1 izvršite konvoluciju, kao što smo ranije radili, sa komandom Transformacija – zaokretna kolona (Transformacija — zaokretni stupac). Kolona vrijednosti u ovom slučaju bit će kolona s vrijednostima parametara − OPIS.2 Obavezno odaberite funkciju u parametrima Nemojte agregirati (Nemoj agregirati):
- Ostaje postaviti formate za sve kolone i preimenovati ih po želji. I možete prenijeti rezultate natrag u Excel pomoću naredbe Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj na…)
A evo i naše liste narudžbi učitanih u Excel iz Google kalendara:
U budućnosti, prilikom promjene ili dodavanja novih narudžbi u kalendar, bit će dovoljno samo ažurirati naš zahtjev naredbom Podaci – Osvježi sve (Podaci — Osvježi sve).
- Fabrički kalendar u Excel-u ažuriran sa interneta putem Power Queryja
- Transformacija kolone u tabelu
- Kreirajte bazu podataka u Excel-u