Sadržaj
Mnogi poslovni procesi (pa čak i cijeli biznisi) u ovom životu uključuju ispunjavanje narudžbi od strane ograničenog broja izvođača u zadanom roku. Planiranje se u takvim slučajevima događa, kako se kaže, “iz kalendara” i često postoji potreba da se događaji planirani u njemu (narudžbe, sastanci, isporuke) prenesu u Microsoft Excel – za daljnju analizu formulama, pivot tablicama, grafikonima, itd.
Naravno, volio bih provesti takav prijenos ne glupim kopiranjem (što jednostavno nije teško), već automatskim ažuriranjem podataka kako bi se ubuduće sve promjene u kalendaru i nove narudžbe u hodu prikazivale u Excel. Takav uvoz možete implementirati u 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 web stranice Microsofta i instalirati zasebno s poveznice) .
Pretpostavimo da za planiranje koristimo besplatni Google kalendar, u kojem sam, radi praktičnosti, kreirao zaseban kalendar (gumb sa znakom plus u donjem desnom kutu pored Ostali kalendari) s naslovom Raditi. Ovdje unosimo sve narudžbe koje je potrebno ispuniti i dostaviti kupcima na njihovu adresu:
Duplim klikom na bilo koju narudžbu možete pregledati ili urediti njezine detalje:
Imajte na umu da:
- Naziv događaja je menadžerkoji ispunjava ovaj nalog (Elena) i Broj narudžbe
- naznačeno adresa dostava
- Bilješka sadrži (u posebnim redovima, ali proizvoljnim redoslijedom) parametre naloga: vrstu plaćanja, iznos, ime kupca itd. u formatu Parametar=Vrijednost.
Radi jasnoće, nalozi svakog upravitelja istaknuti su svojom bojom, iako to nije nužno.
Korak 1. Nabavite vezu na Google kalendar
Prvo moramo dobiti web poveznicu na naš kalendar narudžbi. Da biste to učinili, kliknite na gumb s tri točke Opcije kalendara rade pored naziva kalendara i odaberite naredbu Postavke i dijeljenje:
U prozoru koji se otvori možete, ako želite, učiniti kalendar javnim ili otvoriti pristup za pojedinačne korisnike. Također nam je potrebna poveznica za privatni pristup kalendaru u iCal formatu:
Korak 2. Učitajte podatke iz kalendara u Power Query
Sada otvorite Excel i na kartici Datum (ako imate Excel 2010-2013, onda na kartici Upit snage) odaberite naredbu S interneta (Podaci — s Interneta). Zatim zalijepite kopirani put u kalendar i kliknite OK.
iCal Power Query ne prepoznaje format, ali lako mu je pomoći. U biti, iCal je obična tekstualna datoteka s dvotočkom kao razdjelnikom, a unutra izgleda otprilike ovako:
Dakle, možete samo kliknuti desnom tipkom miša na ikonu preuzete datoteke i odabrati format koji je najbliži značenju CSV – i naši podaci o svim narudžbama bit će učitani u uređivač upita Power Query i podijeljeni u dva stupca dvotočkom:
Ako bolje pogledate, jasno možete vidjeti da:
- Informacije o svakom događaju (narudžbi) grupirane su u blok koji počinje s riječju BEGIN i završava s END.
- Vrijeme početka i završetka pohranjeno je u nizovima označenim kao DTSTART i DTEND.
- Adresa za dostavu je LOCATION.
- Napomena o narudžbi – polje OPIS.
- Naziv događaja (ime upravitelja i broj naloga) — polje SUMMARY.
Ostaje izdvojiti ove korisne informacije i pretvoriti ih u prikladnu tablicu.
Korak 3. Pretvorite u normalni prikaz
Da biste to učinili, izvršite sljedeći niz radnji:
- Idemo izbrisati prvih 7 redaka koji nam ne trebaju prije prve naredbe BEGIN Početna — Izbriši retke — Izbriši gornje retke (Početna — Ukloni redove — Ukloni gornje redove).
- Filtriraj po stupcu Column1 retke koji sadrže polja koja su nam potrebna: DTSTART, DTEND, DESCRIPTION, LOCATION i SUMMARY.
- Na kartici Napredno Dodavanje stupca izabrati Indeksni stupac (Dodaj stupac — stupac Indeks)da bismo našim podacima dodali stupac s brojem retka.
- Upravo tamo na kartici. Dodavanje stupca izabrati tim Uvjetni stupac (Dodaj stupac — Uvjetni stupac) a na početku svakog bloka (reda) ispisujemo vrijednost indeksa:
- Ispunite prazna polja u dobivenom stupcu Blokiratidesnim klikom na njegov naslov i odabirom naredbe Popuniti (Popuniti).
- Uklonite nepotreban stupac indeks.
- Odaberite stupac Column1 i izvršiti konvoluciju podataka iz stupca Column2 pomoću naredbe Transformacija – zaokretni stupac (Transformacija — zaokretni stupac). Obavezno odaberite u opcijama Ne spajati (Ne zbrajati)tako da se na podatke ne primjenjuje nikakva matematička funkcija:
- U dobivenoj dvodimenzionalnoj (križnoj) tablici obrišite obrnute kose crte u stupcu adrese (desnom tipkom miša kliknite zaglavlje stupca – Zamjena vrijednosti) i uklonite nepotrebni stupac Blokirati.
- Za okretanje sadržaja stupaca DTSTART и DTEND u punom datumu-vremenu, označite ih, odaberite na kartici Pretvori – Datum – Pokreni analizu (Transformacija — Datum — Raščlanjivanje). Zatim ispravljamo kod u traci formule zamjenom funkcije Datum od on DatumVrijeme.Odkako ne bismo izgubili vremenske vrijednosti:
- Zatim desnim klikom na zaglavlje dijelimo stupac OPIS s parametrima naloga separatorom – simbolom n, ali pritom ćemo u parametrima odabrati podjelu na retke, a ne na stupce:
- Ponovno dijelimo rezultirajući stupac u dva odvojena stupca – parametar i vrijednost, ali znakom jednakosti.
- Odabir stupca OPIS.1 izvršite konvoluciju, kao što smo učinili ranije, s naredbom Transformacija – zaokretni stupac (Transformacija — zaokretni stupac). Stupac vrijednosti u ovom slučaju bit će stupac s vrijednostima parametara − OPIS.2 Obavezno odaberite funkciju u parametrima Ne spajati (Ne zbrajati):
- Ostalo je postaviti formate za sve stupce i preimenovati ih po želji. A 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 u…)
A ovdje je naš popis narudžbi učitanih u Excel iz Google kalendara:
Ubuduće, 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).
- Tvornički kalendar u Excelu ažuriran s interneta putem Power Queryja
- Pretvaranje stupca u tablicu
- Napravite bazu podataka u Excelu