Inhoud
Veel bedrijfsprocessen (en zelfs hele bedrijven) in dit leven omvatten de uitvoering van bestellingen door een beperkt aantal uitvoerders tegen een bepaalde deadline. Planning gebeurt in dergelijke gevallen, zoals ze zeggen, "van de kalender" en vaak is het nodig om de daarin geplande gebeurtenissen (bestellingen, vergaderingen, leveringen) over te brengen naar Microsoft Excel - voor verdere analyse door formules, draaitabellen, grafieken, enz.
Natuurlijk zou ik zo'n overdracht niet willen implementeren door dom te kopiëren (wat gewoon niet moeilijk is), maar met het automatisch bijwerken van gegevens, zodat in de toekomst alle wijzigingen aan de kalender en nieuwe bestellingen on-the-fly worden weergegeven in Excell. U kunt een dergelijke import in enkele minuten implementeren met behulp van de Power Query-invoegtoepassing die is ingebouwd in Microsoft Excel, vanaf de 2016-versie (voor Excel 2010-2013 kan deze worden gedownload van de Microsoft-website en afzonderlijk worden geïnstalleerd via de link) .
Stel dat we voor de planning de gratis Google Calendar gebruiken, waarin ik voor het gemak een aparte kalender heb aangemaakt (de knop met een plusteken in de rechter benedenhoek naast Andere kalenders) met de titel Mijn werk. Hier voeren we alle bestellingen in die moeten worden voltooid en afgeleverd bij klanten op hun adressen:
Door op een bestelling te dubbelklikken, kunt u de details bekijken of bewerken:
Merk op dat:
- De naam van het evenement is managerwie deze bestelling uitvoert (Elena) en Order ID
- Aangegeven adres levering
- De notitie bevat (in aparte regels, maar in willekeurige volgorde) de bestelparameters: betalingstype, bedrag, klantnaam, enz. in het formaat Parameter=Waarde.
Voor de duidelijkheid zijn de orders van elke manager in een eigen kleur gemarkeerd, hoewel dit niet nodig is.
Stap 1. Krijg een link naar Google Agenda
Eerst hebben we een weblink nodig naar onze bestelkalender. Klik hiervoor op de knop met drie puntjes Agenda-opties werken naast de naam van de kalender en selecteer de opdracht Instellingen en delen:
In het geopende venster kun je de agenda desgewenst openbaar maken of voor individuele gebruikers toegankelijk maken. We hebben ook een link nodig voor privétoegang tot de kalender in iCal-formaat:
Stap 2. Laad gegevens uit de agenda in Power Query
Open nu Excel en op het tabblad Data (als je Excel 2010-2013 hebt, dan op het tabblad Power Query) kies een commando Van het internet (Gegevens — Van internet). Plak vervolgens het gekopieerde pad naar de kalender en klik op OK.
De iCal Power Query herkent het formaat niet, maar het is gemakkelijk te helpen. In wezen is iCal een gewoon tekstbestand met een dubbele punt als scheidingsteken, en binnenin ziet het er ongeveer zo uit:
U kunt dus gewoon met de rechtermuisknop op het pictogram van het gedownloade bestand klikken en het formaat selecteren dat het dichtst in de buurt komt CSV – en onze gegevens over alle bestellingen worden in de Power Query-queryeditor geladen en per dubbele punt in twee kolommen verdeeld:
Als je goed kijkt, zie je duidelijk dat:
- Informatie over elke gebeurtenis (bestelling) is gegroepeerd in een blok dat begint met het woord BEGIN en eindigt met END.
- De begin- en einddatums worden opgeslagen in strings met het label DTSTART en DTEND.
- Het verzendadres is LOCATIE.
- Bestelnotitie – veld BESCHRIJVING.
- Naam evenement (naam manager en bestelnummer) — veld SAMENVATTING.
Het blijft om deze nuttige informatie te extraheren en om te zetten in een handige tabel.
Stap 3. Converteren naar normale weergave
Voer hiervoor de volgende reeks acties uit:
- Laten we de bovenste 7 regels verwijderen die we niet nodig hebben voor het eerste BEGIN-commando Home — Rijen verwijderen — Bovenste rijen verwijderen (Home — Rijen verwijderen — Bovenste rijen verwijderen).
- Filteren op kolom Column1 regels met de velden die we nodig hebben: DTSTART, DTEND, DESCRIPTION, LOCATION en SUMMARY.
- Op het tabblad Geavanceerd Een kolom toevoegen kiezen Indexkolom (Kolom toevoegen — Indexkolom)om een rijnummerkolom aan onze gegevens toe te voegen.
- Daar op het tabblad. Een kolom toevoegen kies een team Voorwaardelijke kolom (Kolom toevoegen — Voorwaardelijke kolom) en aan het begin van elk blok (volgorde) geven we de waarde van de index weer:
- Vul de lege cellen in de resulterende kolom in Blokdoor met de rechtermuisknop op de titel te klikken en de opdracht te selecteren Vullen – Omlaag (Vul — Omlaag).
- Verwijder onnodige kolom Index.
- Selecteer een kolom Column1 en voer een convolutie uit van de gegevens uit de kolom Column2 met behulp van de opdracht Transformeren – Draaikolom (Transformeren — Draaikolom). Zorg ervoor dat u selecteert in de opties Niet aggregeren (Niet aggregeren)zodat er geen wiskundige functie op de gegevens wordt toegepast:
- Wis in de resulterende tweedimensionale (kruis)tabel de backslashes in de adreskolom (klik met de rechtermuisknop op de kolomkop - Waarden vervangen) en verwijder de onnodige kolom Blok.
- De inhoud van de kolommen omdraaien DTSTART и DTEND in een volledige datum-tijd, markeer ze, selecteer op het tabblad Transformeren – Datum – Analyse uitvoeren (Transformeren — Datum — Ontleden). Vervolgens corrigeren we de code in de formulebalk door de functie te vervangen Datum vanaf on DatumTijd.Vanom geen tijdwaarden te verliezen:
- Vervolgens, door met de rechtermuisknop op de kop te klikken, splitsen we de kolom PRODUCTBESCHRIJVING met bestelparameters per scheidingsteken – symbool n, maar tegelijkertijd selecteren we in de parameters de verdeling in rijen en niet in kolommen:
- Nogmaals, we verdelen de resulterende kolom in twee afzonderlijke - de parameter en de waarde, maar door het gelijkteken.
- Een kolom selecteren BESCHRIJVING.1 voer de convolutie uit, zoals we eerder deden, met het commando Transformeren – Draaikolom (Transformeren — Draaikolom). De waardekolom is in dit geval de kolom met parameterwaarden BESCHRIJVING.2 Zorg ervoor dat u een functie selecteert in de parameters Niet aggregeren (Niet aggregeren):
- Het blijft om de formaten voor alle kolommen in te stellen en ze naar wens te hernoemen. En u kunt de resultaten terug uploaden naar Excel met de opdracht Home — Sluiten en laden — Sluiten en laden in… (Home — Sluiten&Laden — Sluiten&Laden naar…)
En hier is onze lijst met bestellingen die vanuit Google Agenda in Excel zijn geladen:
In de toekomst, bij het wijzigen of toevoegen van nieuwe bestellingen aan de kalender, volstaat het om ons verzoek bij te werken met het commando Gegevens – Alles vernieuwen (Gegevens — Alles vernieuwen).
- Fabriekskalender in Excel bijgewerkt vanaf internet via Power Query
- Een kolom omzetten in een tabel
- Een database maken in Excel