Obsah
Mnoho obchodních procesů (a dokonce celých podniků) v tomto životě zahrnuje plnění objednávek omezeným počtem účinkujících v daném termínu. Plánování v takových případech probíhá, jak se říká, „z kalendáře“ a často je potřeba přenést v něm plánované události (objednávky, schůzky, dodávky) do Microsoft Excelu – pro další analýzu pomocí vzorců, kontingenčních tabulek, grafů, atd.
Samozřejmě bych chtěl takový přenos realizovat ne stupidním kopírováním (což prostě není těžké), ale s automatickou aktualizací dat tak, aby se v budoucnu všechny změny v kalendáři a nové objednávky za běhu zobrazovaly v Vynikat. Takový import můžete implementovat během několika minut pomocí doplňku Power Query zabudovaného do Microsoft Excelu od verze 2016 (pro Excel 2010-2013 jej lze stáhnout z webu Microsoftu a nainstalovat samostatně z odkazu) .
Předpokládejme, že k plánování používáme bezplatný Google Calendar, ve kterém jsem si pro pohodlí vytvořil samostatný kalendář (tlačítko se znaménkem plus v pravém dolním rohu vedle Ostatní kalendáře) s názvem Práce. Zde zadáváme všechny objednávky, které je potřeba dokončit a doručit zákazníkům na jejich adresy:
Dvojitým kliknutím na libovolnou objednávku můžete zobrazit nebo upravit její podrobnosti:
Všimněte si, že:
- Název akce je manažerkdo plní tento příkaz (Elena) a Číslo objednávky
- Uvedeno adresa dodání
- Poznámka obsahuje (na samostatných řádcích, ale v libovolné objednávce) parametry objednávky: typ platby, částku, jméno zákazníka atd. ve formátu Parametr=Hodnota.
Pro přehlednost jsou příkazy každého manažera zvýrazněny vlastní barvou, i když to není nutné.
Krok 1. Získejte odkaz na Kalendář Google
Nejprve potřebujeme získat webový odkaz na náš kalendář objednávek. Chcete-li to provést, klikněte na tlačítko se třemi tečkami Možnosti kalendáře Práce vedle názvu kalendáře a vyberte příkaz Nastavení a sdílení:
V okně, které se otevře, můžete v případě potřeby kalendář zveřejnit nebo k němu otevřít přístup pro jednotlivé uživatele. Potřebujeme také odkaz pro soukromý přístup do kalendáře ve formátu iCal:
Krok 2. Načtěte data z kalendáře do Power Query
Nyní otevřete Excel a na kartě Data (pokud máte Excel 2010-2013, pak na záložce Dotaz na napájení) vyberte příkaz Z internetu (Data – z internetu). Poté vložte zkopírovanou cestu do kalendáře a klikněte na OK.
iCal Power Query nerozpozná formát, ale je snadné mu pomoci. V podstatě je iCal prostý textový soubor s dvojtečkou jako oddělovačem a uvnitř vypadá asi takto:
Stačí tedy kliknout pravým tlačítkem na ikonu staženého souboru a vybrat formát, který je významově nejbližší CSV – a naše data o všech objednávkách budou načtena do editoru dotazů Power Query a rozdělena do dvou sloupců dvojtečkou:
Když se podíváte pozorně, můžete jasně vidět, že:
- Informace o každé události (zakázce) jsou seskupeny do bloku začínajícího slovem BEGIN a končícího END.
- Počáteční a koncové datum a čas jsou uloženy v řetězcích označených DTSTART a DTEND.
- Doručovací adresa je LOCATION.
- Poznámka k objednávce – pole DESCRIPTION.
- Název události (jméno manažera a číslo objednávky) — pole SUMMARY.
Zbývá extrahovat tyto užitečné informace a přeměnit je na pohodlnou tabulku.
Krok 3. Převeďte do normálního zobrazení
Chcete-li to provést, proveďte následující řetězec akcí:
- Před prvním příkazem BEGIN smažeme prvních 7 řádků, které nepotřebujeme Domů — Odstranit řádky — Odstranit první řádky (Domů — Odebrat řádky — Odebrat horní řádky).
- Filtrujte podle sloupce Column1 řádky obsahující pole, která potřebujeme: DTSTART, DTEND, DESCRIPTION, LOCATION a SUMMARY.
- Na kartě Upřesnit Přidání sloupce vybrat Indexový sloupec (Přidat sloupec — Indexový sloupec)k přidání sloupce s číslem řádku k našim datům.
- Přímo na kartě. Přidání sloupce vybrat tým Podmíněný sloupec (Přidat sloupec — Podmíněný sloupec) a na začátku každého bloku (objednávky) zobrazíme hodnotu indexu:
- Vyplňte prázdné buňky ve výsledném sloupci Blokovatkliknutím pravým tlačítkem na jeho název a výběrem příkazu Vyplnit – Dolů (Vyplnit – dolů).
- Odstraňte nepotřebný sloupec index.
- Vyberte sloupec Column1 a provést konvoluci dat ze sloupce Column2 pomocí příkazu Transformace – Pivot Column (Transformace – kontingenční sloupec). Nezapomeňte vybrat v možnostech Neagregovat (Neshrnovat)aby na data nebyla aplikována žádná matematická funkce:
- Ve výsledné dvourozměrné (křížové) tabulce vymažte zpětná lomítka ve sloupci adresy (klikněte pravým tlačítkem na záhlaví sloupce – Výměna hodnot) a odstraňte nepotřebný sloupec Blokovat.
- Pro otočení obsahu sloupců DTSTART и DTEND v plném datovém čase, zvýrazněte je a vyberte na kartě Transformace – Datum – Spustit analýzu (Transformace — Datum — Analýza). Poté opravíme kód v řádku vzorců nahrazením funkce Datum od on DateTime.Fromaby nedošlo ke ztrátě časových hodnot:
- Poté kliknutím pravým tlačítkem na záhlaví sloupec rozdělíme POPIS s parametry objednávky oddělovačem – symbolem n, ale zároveň v parametrech vybereme rozdělení do řádků, nikoli do sloupců:
- Výsledný sloupec opět rozdělíme na dva samostatné – parametr a hodnotu, ale rovnítko.
- Výběr sloupce POPIS.1 proveďte konvoluci, jako jsme to udělali dříve, pomocí příkazu Transformace – Pivot Column (Transformace – kontingenční sloupec). Sloupec hodnoty v tomto případě bude sloupec s hodnotami parametrů - POPIS.2 Nezapomeňte vybrat funkci v parametrech Neagregovat (Neshrnovat):
- Zbývá nastavit formáty pro všechny sloupce a libovolně je přejmenovat. A pomocí příkazu můžete výsledky nahrát zpět do Excelu Domů — Zavřít a načíst — Zavřít a načíst v… (Domů — Zavřít&Načíst — Zavřít&Načíst do…)
A zde je náš seznam objednávek načtených do Excelu z Kalendáře Google:
V budoucnu při změně nebo přidávání nových objednávek do kalendáře bude stačit pouze aktualizovat náš požadavek příkazem Data – Obnovit vše (Data – Obnovit vše).
- Tovární kalendář v Excelu aktualizovaný z internetu přes Power Query
- Transformace sloupce do tabulky
- Vytvořte databázi v Excelu