Indhold
Mange forretningsprocesser (og endda hele virksomheder) i dette liv involverer opfyldelse af ordrer af et begrænset antal kunstnere inden for en given deadline. Planlægning i sådanne tilfælde sker, som de siger, "fra kalenderen", og ofte er der behov for at overføre de planlagte begivenheder i den (ordrer, møder, leverancer) til Microsoft Excel - for yderligere analyse ved hjælp af formler, pivottabeller, diagrammer, etc.
Jeg vil selvfølgelig gerne implementere en sådan overførsel ikke ved dum kopiering (hvilket bare ikke er svært), men med automatisk opdatering af data, så alle ændringer i kalenderen og nye ordrer i farten i fremtiden vil blive vist i Excel. Du kan implementere en sådan import i løbet af få minutter ved hjælp af Power Query-tilføjelsesprogrammet indbygget i Microsoft Excel, startende fra 2016-versionen (for Excel 2010-2013 kan den downloades fra Microsofts websted og installeres separat fra linket) .
Antag at vi bruger den gratis Google Kalender til planlægning, hvor jeg for nemheds skyld har lavet en separat kalender (knappen med et plustegn i nederste højre hjørne ved siden af Andre kalendere) med titlen Arbejde. Her indtaster vi alle ordrer, der skal gennemføres og leveres til kunderne på deres adresser:
Ved at dobbeltklikke på en ordre kan du se eller redigere dens detaljer:
Noter det:
- Navnet på arrangementet er lederhvem opfylder denne ordre (Elena) og Bestillingsnummer
- Angivet adresse levering
- Notatet indeholder (i separate linjer, men i enhver rækkefølge) ordreparametrene: betalingstype, beløb, kundenavn osv. i formatet Parameter=Værdi.
For klarhedens skyld er ordrerne fra hver leder fremhævet i deres egen farve, selvom dette ikke er nødvendigt.
Trin 1. Få et link til Google Kalender
Først skal vi have et weblink til vores ordrekalender. For at gøre dette skal du klikke på knappen med tre prikker Kalenderindstillinger arbejde ud for navnet på kalenderen, og vælg kommandoen Indstillinger og deling:
I vinduet, der åbnes, kan du, hvis det ønskes, gøre kalenderen offentlig eller åbne adgang til den for individuelle brugere. Vi har også brug for et link til privat adgang til kalenderen i iCal-format:
Trin 2. Indlæs data fra kalenderen i Power Query
Åbn nu Excel og på fanen data (hvis du har Excel 2010-2013, så på fanen Strømforespørgsel) vælg en kommando Fra internettet (Data - fra internettet). Indsæt derefter den kopierede sti til kalenderen, og klik på OK.
iCal Power Query genkender ikke formatet, men det er nemt at hjælpe. I det væsentlige er iCal en almindelig tekstfil med et kolon som afgrænsning, og indeni ser det sådan ud:
Så du kan bare højreklikke på ikonet for den downloadede fil og vælge det format, der er nærmest i betydningen CSV – og vores data om alle ordrer vil blive indlæst i Power Query-forespørgselseditoren og opdelt i to kolonner efter kolon:
Hvis du kigger godt efter, kan du tydeligt se, at:
- Information om hver begivenhed (rækkefølge) er grupperet i en blok, der starter med ordet BEGIN og slutter med END.
- Start- og slutdatoerne gemmes i strenge mærket DTSTART og DTEND.
- Leveringsadressen er LOCATION.
- Ordrebemærkning – BESKRIVELSE feltet.
- Hændelsesnavn (ledernavn og ordrenummer) — RESUMÉ-feltet.
Det er tilbage at udtrække denne nyttige information og omdanne den til en praktisk tabel.
Trin 3. Konverter til normal visning
For at gøre dette skal du udføre følgende kæde af handlinger:
- Lad os slette de øverste 7 linjer, vi ikke har brug for, før den første BEGIN-kommando Hjem — Slet rækker — Slet øverste rækker (Hjem — Fjern rækker — Fjern øverste rækker).
- Filtrer efter kolonne Column1 linjer, der indeholder de felter, vi har brug for: DTSTART, DTEND, BESKRIVELSE, LOCATION og SUMMARY.
- På fanen Avanceret Tilføjelse af en kolonne vælge Indeks kolonne (Tilføj kolonne — Indekskolonne)at tilføje en rækkenummerkolonne til vores data.
- Lige der på fanen. Tilføjelse af en kolonne vælge et hold Betinget kolonne (Tilføj kolonne — Betinget kolonne) og i begyndelsen af hver blok (ordre) viser vi værdien af indekset:
- Udfyld de tomme celler i den resulterende kolonne Blokerved at højreklikke på dens titel og vælge kommandoen Fyld – ned (Fyld - ned).
- Fjern unødvendig kolonne Indeks.
- Vælg en kolonne Column1 og udføre en foldning af dataene fra kolonnen Column2 ved hjælp af kommandoen Transform – Pivot-kolonne (Transformer – Pivot-kolonne). Sørg for at vælge i mulighederne Må ikke aggregeres (Sæt ikke sammen)så der ikke anvendes matematikfunktion på dataene:
- I den resulterende todimensionelle (kryds) tabel skal du fjerne skråstregene i adressekolonnen (højreklik på kolonneoverskriften – Udskiftning af værdier) og fjern den unødvendige kolonne Bloker.
- For at vende indholdet af kolonnerne DTSTART и DTEND i en fuld dato-tid, fremhæve dem, vælg på fanen Transform – Dato – Kør analyse (Transform — Dato — Parse). Så retter vi koden i formellinjen ved at erstatte funktionen Dato fra on DatoTid.Frafor ikke at miste tidsværdier:
- Derefter, ved at højreklikke på overskriften, opdeler vi kolonnen BESKRIVELSE med rækkefølge parametre efter separator – symbol n, men på samme tid, i parametrene, vil vi vælge opdelingen i rækker og ikke i kolonner:
- Endnu en gang deler vi den resulterende kolonne i to separate - parameteren og værdien, men med lighedstegnet.
- Valg af en kolonne BESKRIVELSE.1 udfør foldningen, som vi gjorde tidligere, med kommandoen Transform – Pivot-kolonne (Transformer – Pivot-kolonne). Værdikolonnen i dette tilfælde vil være kolonnen med parameterværdier - BESKRIVELSE.2 Sørg for at vælge en funktion i parametrene Må ikke aggregeres (Sæt ikke sammen):
- Det er tilbage at indstille formaterne for alle kolonner og omdøbe dem som ønsket. Og du kan uploade resultaterne tilbage til Excel med kommandoen Hjem — Luk og indlæs — Luk og indlæs... (Hjem — Luk&indlæs — Luk&indlæs til...)
Og her er vores liste over ordrer indlæst i Excel fra Google Kalender:
I fremtiden, når du ændrer eller tilføjer nye ordrer til kalenderen, vil det kun være nok at opdatere vores anmodning med kommandoen Data – Opdater alle (Data – Opdater alle).
- Fabrikskalender i Excel opdateret fra internettet via Power Query
- At omdanne en kolonne til en tabel
- Opret en database i Excel