Obsah
Mnoho obchodných procesov (a dokonca celých podnikov) v tomto živote zahŕňa plnenie objednávok obmedzeným počtom účinkujúcich do daného termínu. Plánovanie v takýchto prípadoch prebieha, ako sa hovorí, „z kalendára“ a často vzniká potreba preniesť v ňom plánované udalosti (objednávky, stretnutia, dodávky) do Microsoft Excelu – na ďalšiu analýzu pomocou vzorcov, kontingenčných tabuliek, grafov, atď.
Samozrejme, rád by som takýto prenos zrealizoval nie hlúpym kopírovaním (čo jednoducho nie je náročné), ale s automatickou aktualizáciou údajov, aby sa v budúcnosti všetky zmeny v kalendári a nové objednávky za chodu zobrazovali v Excel. Takýto import môžete implementovať v priebehu niekoľkých minút pomocou doplnku Power Query zabudovaného do programu Microsoft Excel od verzie 2016 (pre Excel 2010-2013 si ho môžete stiahnuť z webovej stránky spoločnosti Microsoft a nainštalovať samostatne z odkazu) .
Predpokladajme, že na plánovanie používame bezplatný Google Kalendár, v ktorom som si pre pohodlie vytvoril samostatný kalendár (tlačidlo so znamienkom plus v pravom dolnom rohu vedľa Ostatné kalendáre) s názvom práca. Tu zadávame všetky objednávky, ktoré je potrebné vyplniť a doručiť zákazníkom na ich adresy:
Dvojitým kliknutím na ľubovoľnú objednávku môžete zobraziť alebo upraviť jej podrobnosti:
Poznač si to:
- Názov podujatia je riaditeľkto splní tento príkaz (Elena) a Číslo objednávky
- uvedené adresa dodanie
- Poznámka obsahuje (v samostatných riadkoch, ale v ľubovoľnej objednávke) parametre objednávky: typ platby, sumu, meno zákazníka atď. vo formáte Parameter=Hodnota.
Kvôli prehľadnosti sú príkazy každého manažéra zvýraznené vlastnou farbou, aj keď to nie je potrebné.
Krok 1. Získajte odkaz na Kalendár Google
Najprv musíme získať webový odkaz na náš kalendár objednávok. Ak to chcete urobiť, kliknite na tlačidlo s tromi bodkami Možnosti kalendára Práca vedľa názvu kalendára a vyberte príkaz Nastavenia a zdieľanie:
V okne, ktoré sa otvorí, môžete v prípade potreby zverejniť kalendár alebo otvoriť prístup k nemu pre jednotlivých používateľov. Potrebujeme aj odkaz na súkromný prístup ku kalendáru vo formáte iCal:
Krok 2. Načítajte údaje z kalendára do Power Query
Teraz otvorte Excel a na karte dátum (ak máte Excel 2010-2013, potom na karte mocenský dotaz) vyberte príkaz Z internetu (Údaje – z internetu). Potom prilepte skopírovanú cestu do kalendára a kliknite na tlačidlo OK.
iCal Power Query nepozná formát, ale dá sa s ním ľahko pomôcť. iCal je v podstate obyčajný textový súbor s dvojbodkou ako oddeľovačom a vo vnútri vyzerá asi takto:
Stačí teda kliknúť pravým tlačidlom myši na ikonu prevzatého súboru a vybrať formát, ktorý je významovo najbližší CSV – a naše údaje o všetkých objednávkach sa načítajú do editora dotazov Power Query a rozdelia sa do dvoch stĺpcov dvojbodkou:
Ak sa pozriete pozorne, môžete jasne vidieť, že:
- Informácie o každej udalosti (objednávke) sú zoskupené do bloku začínajúceho slovom BEGIN a končiaceho END.
- Počiatočný a koncový dátum a čas sú uložené v reťazcoch označených DTSTART a DTEND.
- Dodacia adresa je LOCATION.
- Poznámka k objednávke – pole DESCRIPTION.
- Názov udalosti (meno manažéra a číslo objednávky) — pole SUMMARY.
Zostáva extrahovať tieto užitočné informácie a premeniť ich na pohodlnú tabuľku.
Krok 3. Previesť na normálne zobrazenie
Ak to chcete urobiť, vykonajte nasledujúci reťazec akcií:
- Pred prvým príkazom BEGIN vymažeme prvých 7 riadkov, ktoré nepotrebujeme Domov — Odstrániť riadky — Odstrániť prvé riadky (Domov — Odstrániť riadky — Odstrániť horné riadky).
- Filtrujte podľa stĺpca Column1 riadky obsahujúce polia, ktoré potrebujeme: DTSTART, DTEND, DESCRIPTION, LOCATION a SUMMARY.
- Na karte Rozšírené Pridanie stĺpca vybrať Indexový stĺpec (Pridať stĺpec — Indexový stĺpec)na pridanie stĺpca s číslom riadka k našim údajom.
- Priamo na karte. Pridanie stĺpca vybrať tím Podmienený stĺpec (Pridať stĺpec – podmienený stĺpec) a na začiatku každého bloku (objednávky) zobrazíme hodnotu indexu:
- Vyplňte prázdne bunky vo výslednom stĺpci Blokovaťkliknutím pravým tlačidlom myši na jeho názov a výberom príkazu Vyplniť – nadol (Vyplniť – nadol).
- Odstráňte nepotrebný stĺpec index.
- Vyberte stĺpec Column1 a vykonajte konvolúciu údajov zo stĺpca Column2 pomocou príkazu Transformácia – kontingenčný stĺpec (Transformácia – otočný stĺpec). Nezabudnite vybrať v možnostiach Neagregovať (Nespájať)aby sa na údaje nepoužila žiadna matematická funkcia:
- Vo výslednej dvojrozmernej (krížovej) tabuľke vymažte spätné lomky v stĺpci adresy (kliknite pravým tlačidlom myši na hlavičku stĺpca – Nahradenie hodnôt) a odstráňte nepotrebný stĺpec Blokovať.
- Na otočenie obsahu stĺpcov DTSTART и DTEND v celom dátume a čase, zvýraznite ich a vyberte na karte Transformácia – Dátum – Spustiť analýzu (Transformácia – dátum – analýza). Potom opravíme kód v riadku vzorcov nahradením funkcie Dátum od on DateTime.Fromaby sa nestratili časové hodnoty:
- Potom kliknutím pravým tlačidlom myši na hlavičku stĺpec rozdelíme POPIS s parametrami objednávky podľa oddeľovača – symbolu n, ale zároveň v parametroch vyberieme rozdelenie do riadkov a nie do stĺpcov:
- Výsledný stĺpec ešte raz rozdelíme na dva samostatné – parameter a hodnotu, avšak znamienkom rovnosti.
- Výber stĺpca POPIS.1 vykonajte konvolúciu, ako sme to urobili predtým, pomocou príkazu Transformácia – kontingenčný stĺpec (Transformácia – otočný stĺpec). Stĺpec hodnoty v tomto prípade bude stĺpec s hodnotami parametrov - POPIS.2 Nezabudnite vybrať funkciu v parametroch Neagregovať (Nespájať):
- Zostáva nastaviť formáty pre všetky stĺpce a premenovať ich podľa potreby. A pomocou príkazu môžete výsledky nahrať späť do Excelu Domov — Zavrieť a načítať — Zavrieť a načítať v… (Domov — Zavrieť&Načítať — Zavrieť&Načítať do...)
A tu je náš zoznam objednávok načítaných do Excelu z Kalendára Google:
V budúcnosti pri zmene alebo pridávaní nových objednávok do kalendára bude stačiť len aktualizovať našu požiadavku príkazom Údaje – Obnoviť všetko (Údaje – Obnoviť všetko).
- Továrenský kalendár v Exceli aktualizovaný z internetu cez Power Query
- Transformácia stĺpca na tabuľku
- Vytvorte databázu v Exceli