Innhold
Mange forretningsprosesser (og til og med hele virksomheter) i dette livet involverer oppfyllelse av bestillinger av et begrenset antall utøvere innen en gitt frist. Planlegging i slike tilfeller skjer, som de sier, "fra kalenderen", og ofte er det behov for å overføre hendelsene som er planlagt i den (bestillinger, møter, leveranser) til Microsoft Excel - for videre analyse ved hjelp av formler, pivottabeller, kartlegging, etc.
Selvfølgelig vil jeg gjerne implementere en slik overføring ikke ved dum kopiering (som bare ikke er vanskelig), men med automatisk oppdatering av data slik at i fremtiden vil alle endringer som er gjort i kalenderen og nye bestillinger i farten vises i Utmerke. Du kan implementere en slik import i løpet av få minutter ved å bruke Power Query-tillegget innebygd i Microsoft Excel, fra og med 2016-versjonen (for Excel 2010-2013 kan det lastes ned fra Microsofts nettsted og installeres separat fra lenken) .
Tenk deg at vi bruker den gratis Google-kalenderen til planlegging, der jeg for enkelhets skyld opprettet en egen kalender (knappen med plusstegn i nedre høyre hjørne ved siden av Andre kalendere) med tittelen Arbeid. Her legger vi inn alle bestillinger som skal gjennomføres og leveres til kundene på deres adresser:
Ved å dobbeltklikke på en bestilling kan du se eller redigere detaljene for den:
Noter det:
- Navnet på arrangementet er ledersom oppfyller denne ordren (Elena) og Ordrenummer
- indikert adresse levering
- Notatet inneholder (i separate linjer, men i hvilken som helst rekkefølge) ordreparametrene: betalingstype, beløp, kundenavn osv. i formatet Parameter=Verdi.
For klarhetens skyld er ordrene til hver leder uthevet i sin egen farge, selv om dette ikke er nødvendig.
Trinn 1. Få en lenke til Google Kalender
Først må vi få en nettlenke til vår bestillingskalender. For å gjøre dette, klikk på knappen med tre prikker Kalenderalternativer Arbeid ved siden av navnet på kalenderen og velg kommandoen Innstillinger og deling:
I vinduet som åpnes kan du om ønskelig gjøre kalenderen offentlig eller åpne tilgang til den for enkeltbrukere. Vi trenger også en lenke for privat tilgang til kalenderen i iCal-format:
Trinn 2. Last inn data fra kalenderen til Power Query
Åpne nå Excel og på fanen Data (hvis du har Excel 2010-2013, så på fanen Strømforespørsel) velg en kommando Fra Internett (Data – fra Internett). Deretter limer du inn den kopierte banen til kalenderen og klikker OK.
iCal Power Query gjenkjenner ikke formatet, men det er enkelt å hjelpe. I hovedsak er iCal en ren tekstfil med et kolon som skilletegn, og inni det ser omtrent slik ut:
Så du kan bare høyreklikke på ikonet for den nedlastede filen og velge formatet som er nærmest i betydning CSV – og våre data om alle bestillinger vil bli lastet inn i Power Query-spørringseditoren og delt inn i to kolonner etter kolon:
Hvis du ser nøye etter, kan du tydelig se at:
- Informasjon om hver hendelse (rekkefølge) er gruppert i en blokk som starter med ordet BEGIN og slutter med END.
- Start- og sluttdatoene lagres i strenger merket DTSTART og DTEND.
- Leveringsadressen er LOCATION.
- Ordrenotat – BESKRIVELSE-feltet.
- Hendelsesnavn (ledernavn og ordrenummer) — SAMMENDRAG-feltet.
Det gjenstår å trekke ut denne nyttige informasjonen og forvandle den til en praktisk tabell.
Trinn 3. Konverter til normalvisning
For å gjøre dette, utfør følgende handlingskjede:
- La oss slette de 7 øverste linjene vi ikke trenger før den første BEGIN-kommandoen Hjem — Slett rader — Slett øverste rader (Hjem — Fjern rader — Fjern øverste rader).
- Filtrer etter kolonne Column1 linjer som inneholder feltene vi trenger: DTSTART, DTEND, BESKRIVELSE, PLASSERING og SAMMENDRAG.
- I kategorien Avansert Legger til en kolonne velge Indekskolonne (Legg til kolonne — Indekskolonne)for å legge til en radnummerkolonne i dataene våre.
- Rett der på fanen. Legger til en kolonne velge et lag Betinget kolonne (Legg til kolonne – betinget kolonne) og i begynnelsen av hver blokk (ordre) viser vi verdien av indeksen:
- Fyll ut de tomme cellene i den resulterende kolonnen Blokkerved å høyreklikke på tittelen og velge kommandoen Fyll – ned (Fyll - ned).
- Fjern unødvendig kolonne Index.
- Velg en kolonne Column1 og utføre en konvolusjon av dataene fra kolonnen Column2 ved å bruke kommandoen Transform – Pivot-kolonne (Transform – Pivot-kolonne). Pass på å velge i alternativene Ikke aggregere (Ikke samle)slik at ingen matematisk funksjon brukes på dataene:
- I den resulterende todimensjonale (kryss) tabellen, fjern skråstrekene i adressekolonnen (høyreklikk på kolonneoverskriften – Erstatter verdier) og fjern den unødvendige kolonnen Blokker.
- For å snu innholdet i kolonnene DTSTART и DTEND i en fullstendig dato-klokkeslett, fremhev dem, velg på fanen Transform – Dato – Kjør analyse (Transform – Dato – Parse). Deretter retter vi koden i formellinjen ved å erstatte funksjonen Dato fra on DatoTid.Frafor ikke å miste tidsverdier:
- Deretter, ved å høyreklikke på overskriften, deler vi kolonnen BESKRIVELSE med rekkefølgeparametere etter skilletegn – symbol n, men samtidig, i parametrene, vil vi velge inndelingen i rader, og ikke i kolonner:
- Nok en gang deler vi den resulterende kolonnen i to separate - parameteren og verdien, men med likhetstegnet.
- Velge en kolonne BESKRIVELSE.1 utfør konvolusjonen, som vi gjorde tidligere, med kommandoen Transform – Pivot-kolonne (Transform – Pivot-kolonne). Verdikolonnen i dette tilfellet vil være kolonnen med parameterverdier - BESKRIVELSE.2 Pass på å velge en funksjon i parameterne Ikke aggregere (Ikke samle):
- Det gjenstår å angi formatene for alle kolonner og gi dem nytt navn etter ønske. Og du kan laste opp resultatene tilbake til Excel med kommandoen Hjem — Lukk og last — Lukk og last inn... (Hjem — Lukk&last — Lukk&last til...)
Og her er listen vår over bestillinger lastet inn i Excel fra Google Kalender:
I fremtiden, når du endrer eller legger til nye bestillinger i kalenderen, vil det bare være nok å oppdatere forespørselen vår med kommandoen Data – Oppdater alle (Data – Oppdater alle).
- Fabrikkkalender i Excel oppdatert fra internett via Power Query
- Forvandle en kolonne til en tabell
- Lag en database i Excel