vsebina
Številni poslovni procesi (in celo celotna podjetja) v tem življenju vključujejo izpolnjevanje naročil omejenega števila izvajalcev v določenem roku. Načrtovanje v takih primerih poteka, kot pravijo, "iz koledarja" in pogosto je treba načrtovane dogodke (naročila, sestanke, dostave) prenesti v Microsoft Excel - za nadaljnjo analizo s formulami, vrtilnimi tabelami, grafikoni, itd.
Takšen prenos bi seveda rad izvedel ne z neumnim kopiranjem (kar pač ni težko), ampak z avtomatskim posodabljanjem podatkov, da bi se v prihodnje vse spremembe koledarja in sprotna naročila prikazovala v Excel. Takšen uvoz lahko izvedete v nekaj minutah z uporabo dodatka Power Query, vgrajenega v Microsoft Excel, od različice 2016 (za Excel 2010-2013 ga je mogoče prenesti s spletnega mesta Microsoft in namestiti ločeno s povezave) .
Recimo, da za načrtovanje uporabljamo brezplačen Google Koledar, v katerem sem za udobje ustvaril ločen koledar (gumb z znakom plus v spodnjem desnem kotu poleg Drugi koledarji) z naslovom delo. Tukaj vnesemo vsa naročila, ki jih je potrebno izpolniti in dostaviti strankam na njihove naslove:
Z dvoklikom katerega koli naročila si lahko ogledate ali uredite njegove podrobnosti:
Upoštevajte, da:
- Ime dogodka je upraviteljki izpolnjuje ta ukaz (Elena) in Zaporedna številka
- Navedeno Naslov dostava
- Opomba vsebuje (v ločenih vrsticah, vendar v poljubnem vrstnem redu) parametre naročila: vrsto plačila, znesek, ime stranke itd. v obliki Parameter=Vrednost.
Zaradi jasnosti so ukazi vsakega upravitelja označeni s svojo barvo, čeprav to ni potrebno.
1. korak. Pridobite povezavo do Google Koledarja
Najprej moramo pridobiti spletno povezavo do našega koledarja naročil. Če želite to narediti, kliknite na gumb s tremi pikami Možnosti koledarja delujejo poleg imena koledarja in izberite ukaz Nastavitve in skupna raba:
V oknu, ki se odpre, lahko po želji naredite koledar javen ali odprete dostop do njega za posamezne uporabnike. Potrebujemo tudi povezavo za zasebni dostop do koledarja v formatu iCal:
2. korak. Naložite podatke iz koledarja v Power Query
Zdaj odprite Excel in na zavihku datum (če imate Excel 2010-2013, potem na zavihku poizvedba o moči) izberite ukaz Iz interneta (Podatki — iz interneta). Nato prilepite kopirano pot v koledar in kliknite V redu.
iCal Power Query ne prepozna formata, vendar je enostavno pomagati. V bistvu je iCal datoteka z navadnim besedilom z dvopičjem kot ločilom, znotraj pa je videti nekako takole:
Tako lahko samo z desno miškino tipko kliknete ikono prenesene datoteke in izberete obliko, ki je po pomenu najbližja CSV – in naši podatki o vseh naročilih bodo naloženi v urejevalnik poizvedb Power Query in razdeljeni v dva stolpca z dvopičjem:
Če pogledate natančno, lahko jasno vidite, da:
- Podatki o posameznem dogodku (naročilu) so združeni v blok, ki se začne z besedo BEGIN in konča z END.
- Začetni in končni datum sta shranjena v nizih z oznako DTSTART in DTEND.
- Naslov za dostavo je LOCATION.
- Opomba naročila – polje OPIS.
- Ime dogodka (ime upravitelja in številka naročila) — polje SUMMARY.
Ostaja, da izvlečemo te koristne informacije in jih pretvorimo v priročno tabelo.
Korak 3. Pretvori v običajni pogled
Če želite to narediti, izvedite naslednjo verigo dejanj:
- Izbrišite zgornjih 7 vrstic, ki jih ne potrebujemo pred prvim ukazom BEGIN Domov — Izbriši vrstice — Izbriši zgornje vrstice (Domov — Odstrani vrstice — Odstrani zgornje vrstice).
- Filtriraj po stolpcu Column1 vrstice, ki vsebujejo polja, ki jih potrebujemo: DTSTART, DTEND, DESCRIPTION, LOCATION in SUMMARY.
- Na zavihku Napredno Dodajanje stolpca izberite Indeksni stolpec (Dodaj stolpec — stolpec Indeks)da našim podatkom dodamo stolpec s številko vrstice.
- Prav tam na zavihku. Dodajanje stolpca izberite ekipo Pogojni stolpec (Dodaj stolpec — Pogojni stolpec) in na začetku vsakega bloka (naročila) prikažemo vrednost indeksa:
- Izpolnite prazne celice v nastalem stolpcu Blocktako, da z desno tipko miške kliknete na njen naslov in izberete ukaz Polnjenje – dol (Izpolni — dol).
- Odstranite nepotreben stolpec Kazalo.
- Izberite stolpec Column1 in izvede konvolucijo podatkov iz stolpca Column2 z uporabo ukaza Transformacija – vrtilni stolpec (Pretvorba — vrtilni stolpec). Bodite prepričani, da izberete v možnostih Ne združevati (Ne seštevaj)tako da za podatke ni uporabljena nobena matematična funkcija:
- V nastali dvodimenzionalni (križni) tabeli počistite poševnice nazaj v naslovnem stolpcu (z desnim klikom na glavo stolpca – Zamenjava vrednosti) in odstranite nepotreben stolpec Block.
- Za obračanje vsebine stolpcev DTSTART и DTEND v celotnem datumu in času, jih označite in izberite na zavihku Transformacija – Datum – Zaženi analizo (Pretvorba — Datum — Razčlenitev). Nato popravimo kodo v vrstici formule z zamenjavo funkcije Datum, od on DatumUra.Odda ne izgubite časovnih vrednosti:
- Nato z desnim klikom na glavo razdelimo stolpec OPIS s parametri naročila z ločilom – simbolom n, hkrati pa bomo v parametrih izbrali delitev na vrstice in ne na stolpce:
- Še enkrat razdelimo dobljeni stolpec na dva ločena - parameter in vrednost, vendar z znakom enakosti.
- Izbira stolpca OPIS.1 izvedite konvolucijo, kot smo storili prej, z ukazom Transformacija – vrtilni stolpec (Pretvorba — vrtilni stolpec). Stolpec vrednosti bo v tem primeru stolpec z vrednostmi parametrov − OPIS.2 Ne pozabite izbrati funkcije v parametrih Ne združevati (Ne seštevaj):
- Ostaja še nastaviti formate za vse stolpce in jih po želji preimenovati. Z ukazom lahko rezultate naložite nazaj v Excel Domov — Zapri in naloži — Zapri in naloži v… (Domov — Zapri&Naloži — Zapri&Naloži na…)
In tukaj je naš seznam naročil, naloženih v Excel iz Google Koledarja:
V prihodnje bo pri spreminjanju ali dodajanju novih naročil v koledar dovolj le posodobitev naše zahteve z ukazom Podatki – Osveži vse (Podatki — Osveži vse).
- Tovarniški koledar v Excelu, posodobljen iz interneta prek Power Queryja
- Preoblikovanje stolpca v tabelo
- Ustvari bazo podatkov v Excelu