Innehåll
Många affärsprocesser (och till och med hela företag) i det här livet innebär att ett begränsat antal utförare utför beställningar inom en given deadline. Planering i sådana fall sker, som de säger, "från kalendern" och ofta finns det ett behov av att överföra de planerade händelserna i den (ordrar, möten, leveranser) till Microsoft Excel - för vidare analys med formler, pivottabeller, diagram, etc.
Naturligtvis skulle jag vilja implementera en sådan överföring inte genom dum kopiering (vilket bara inte är svårt), utan med automatisk uppdatering av data så att i framtiden alla ändringar som gjorts i kalendern och nya beställningar i farten skulle visas i Excel. Du kan implementera en sådan import på några minuter med Power Query-tillägget som är inbyggt i Microsoft Excel, från och med 2016-versionen (för Excel 2010-2013 kan den laddas ner från Microsofts webbplats och installeras separat från länken) .
Anta att vi använder den kostnadsfria Google Kalender för planering, där jag för bekvämlighets skull skapade en separat kalender (knappen med plustecken i det nedre högra hörnet bredvid Andra kalendrar) med titeln Arbete. Här anger vi alla beställningar som behöver slutföras och levereras till kunderna på deras adresser:
Genom att dubbelklicka på valfri beställning kan du se eller redigera dess detaljer:
Anteckna det:
- Namnet på evenemanget är chefvem uppfyller denna order (Elena) och Ordernummer
- Indikerad adress leverans
- Anteckningen innehåller (på separata rader, men i valfri ordning) orderparametrarna: betalningstyp, belopp, kundnamn etc. i formatet Parameter=Värde.
För tydlighetens skull är varje chefs order markerade i sin egen färg, även om detta inte är nödvändigt.
Steg 1. Skaffa en länk till Google Kalender
Först måste vi få en webblänk till vår beställningskalender. För att göra detta, klicka på knappen med tre punkter Kalenderalternativ Arbete bredvid namnet på kalendern och välj kommandot Inställningar och delning:
I fönstret som öppnas kan du, om så önskas, göra kalendern offentlig eller öppna åtkomst till den för enskilda användare. Vi behöver också en länk för privat åtkomst till kalendern i iCal-format:
Steg 2. Ladda data från kalendern till Power Query
Öppna nu Excel och på fliken Data (om du har Excel 2010-2013, sedan på fliken Strömfråga) välj ett kommando Från internet (Data – från internet). Klistra sedan in den kopierade sökvägen i kalendern och klicka på OK.
iCal Power Query känner inte igen formatet, men det är lätt att hjälpa. I huvudsak är iCal en vanlig textfil med ett kolon som avgränsare, och inuti ser den ut ungefär så här:
Så du kan bara högerklicka på ikonen för den nedladdade filen och välja det format som ligger närmast i betydelse CSV – och vår data om alla beställningar kommer att laddas in i Power Query-frågeredigeraren och delas upp i två kolumner efter kolon:
Om du tittar noga kan du tydligt se att:
- Information om varje händelse (ordning) grupperas i ett block som börjar med ordet BEGIN och slutar med END.
- Start- och slutdatumen lagras i strängar märkta DTSTART och DTEND.
- Leveransadressen är LOCATION.
- Beställningsanteckning – BESKRIVNING fält.
- Händelsenamn (chefens namn och ordernummer) — SAMMANFATTNING-fältet.
Det återstår att extrahera denna användbara information och omvandla den till en bekväm tabell.
Steg 3. Konvertera till normal vy
För att göra detta, utför följande kedja av åtgärder:
- Låt oss ta bort de 7 översta raderna vi inte behöver före det första BEGIN-kommandot Hem — Ta bort rader — Ta bort översta rader (Hem — Ta bort rader — Ta bort översta raderna).
- Filtrera efter kolumn Column1 rader som innehåller de fält vi behöver: DTSTART, DTEND, BESKRIVNING, PLATS och SUMMARY.
- På fliken Avancerat Lägger till en kolumn välja Indexkolumn (Lägg till kolumn — Indexkolumn)för att lägga till en radnummerkolumn till vår data.
- Precis där på fliken. Lägger till en kolumn välja ett lag Villkorlig kolumn (Lägg till kolumn — villkorlig kolumn) och i början av varje block (order) visar vi indexvärdet:
- Fyll i de tomma cellerna i den resulterande kolumnen Blockeragenom att högerklicka på dess titel och välja kommandot Fyll ner (Fyll ner).
- Ta bort onödig kolumn index.
- Välj en kolumn Column1 och utför en faltning av data från kolumnen Column2 med kommandot Transform – Pivot kolumn (Transformera — Pivot kolumn). Se till att välja i alternativen Aggregera inte (Sammanställ inte)så att ingen matematisk funktion tillämpas på data:
- I den resulterande tvådimensionella (kors)tabellen, rensa bort snedstreck i adresskolumnen (högerklicka på kolumnrubriken - Ersätter värden) och ta bort den onödiga kolumnen Blockera.
- För att vända innehållet i kolumnerna DTSTART и DTEND i en fullständig datum-tid, markera dem, välj på fliken Transform – Datum – Kör analys (Transformera — Datum — Parse). Sedan korrigerar vi koden i formelfältet genom att ersätta funktionen Datum från on DatumTid.Frånför att inte tappa tidsvärden:
- Sedan, genom att högerklicka på rubriken, delar vi upp kolumnen BESKRIVNING med beställningsparametrar efter avgränsare – symbol n, men samtidigt, i parametrarna, väljer vi uppdelningen i rader och inte i kolumner:
- Återigen delar vi den resulterande kolumnen i två separata - parametern och värdet, men med likhetstecknet.
- Välja en kolumn BESKRIVNING.1 utför faltningen, som vi gjorde tidigare, med kommandot Transform – Pivot kolumn (Transformera — Pivot kolumn). Värdekolumnen i detta fall kommer att vara kolumnen med parametervärden - BESKRIVNING.2 Se till att välja en funktion i parametrarna Aggregera inte (Sammanställ inte):
- Det återstår att ställa in formaten för alla kolumner och byta namn på dem efter önskemål. Och du kan ladda upp resultaten tillbaka till Excel med kommandot Hem — Stäng och ladda — Stäng och ladda in... (Hem — Stäng&Ladda — Stäng&Ladda till...)
Och här är vår lista över beställningar som laddas in i Excel från Google Kalender:
I framtiden, när du ändrar eller lägger till nya beställningar i kalendern, kommer det bara att räcka med att uppdatera vår begäran med kommandot Data – Uppdatera alla (Data – Uppdatera alla).
- Fabrikskalender i Excel uppdaterad från internet via Power Query
- Omvandla en kolumn till en tabell
- Skapa en databas i Excel